fn-registry agent
77 lines
3.6 KiB
SQL
77 lines
3.6 KiB
SQL
-- data_factory init schema (issue 0097).
|
|
-- Idempotente. Aplicado al arrancar via embed.FS (Go side / bridge a C++).
|
|
--
|
|
-- Tablas:
|
|
-- nodes — extractors/transformers/databases/sinks declarados localmente
|
|
-- connections — aristas (lineage) entre nodes
|
|
-- runs — historico de ejecuciones (rows/kb/duration por run)
|
|
-- databases — registro de DBs externas que sirven como sink/source
|
|
--
|
|
-- Datos NUNCA viajan entre PCs por sync (per-PC, igual que dag_engine.db).
|
|
|
|
CREATE TABLE IF NOT EXISTS nodes (
|
|
id TEXT PRIMARY KEY,
|
|
kind TEXT NOT NULL CHECK(kind IN ('extractor','transformer','database','sink','validator')),
|
|
name TEXT NOT NULL,
|
|
function_id TEXT NOT NULL DEFAULT '', -- FK logico a registry.functions.id (sin enforcement)
|
|
description TEXT NOT NULL DEFAULT '',
|
|
config_json TEXT NOT NULL DEFAULT '{}',
|
|
schedule_cron TEXT NOT NULL DEFAULT '', -- '' = manual, sino expresion cron (parseable con parse_cron_expr_go_core)
|
|
enabled INTEGER NOT NULL DEFAULT 1,
|
|
tags_csv TEXT NOT NULL DEFAULT '',
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_nodes_kind ON nodes(kind);
|
|
CREATE INDEX IF NOT EXISTS idx_nodes_function_id ON nodes(function_id);
|
|
CREATE INDEX IF NOT EXISTS idx_nodes_enabled ON nodes(enabled);
|
|
|
|
CREATE TABLE IF NOT EXISTS connections (
|
|
id TEXT PRIMARY KEY,
|
|
src_node TEXT NOT NULL REFERENCES nodes(id) ON DELETE CASCADE,
|
|
dst_node TEXT NOT NULL REFERENCES nodes(id) ON DELETE CASCADE,
|
|
payload_schema TEXT NOT NULL DEFAULT '{}', -- JSON schema del dato que viaja
|
|
created_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_connections_src ON connections(src_node);
|
|
CREATE INDEX IF NOT EXISTS idx_connections_dst ON connections(dst_node);
|
|
CREATE UNIQUE INDEX IF NOT EXISTS uniq_connection_edge ON connections(src_node, dst_node);
|
|
|
|
CREATE TABLE IF NOT EXISTS runs (
|
|
id TEXT PRIMARY KEY,
|
|
node_id TEXT NOT NULL REFERENCES nodes(id) ON DELETE CASCADE,
|
|
started_at TEXT NOT NULL,
|
|
finished_at TEXT,
|
|
status TEXT NOT NULL DEFAULT 'running' CHECK(status IN ('running','success','failed','cancelled')),
|
|
rows_in INTEGER NOT NULL DEFAULT 0,
|
|
rows_out INTEGER NOT NULL DEFAULT 0,
|
|
kb_in INTEGER NOT NULL DEFAULT 0,
|
|
kb_out INTEGER NOT NULL DEFAULT 0,
|
|
duration_ms INTEGER NOT NULL DEFAULT 0,
|
|
trigger TEXT NOT NULL DEFAULT 'manual' CHECK(trigger IN ('manual','cron','dag','api')),
|
|
error TEXT NOT NULL DEFAULT '',
|
|
notes TEXT NOT NULL DEFAULT ''
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_runs_node ON runs(node_id);
|
|
CREATE INDEX IF NOT EXISTS idx_runs_status ON runs(status);
|
|
CREATE INDEX IF NOT EXISTS idx_runs_started ON runs(started_at DESC);
|
|
|
|
CREATE TABLE IF NOT EXISTS databases (
|
|
id TEXT PRIMARY KEY,
|
|
kind TEXT NOT NULL CHECK(kind IN ('sqlite','postgres','bigquery','duckdb','mongo','parquet','csv','other')),
|
|
label TEXT NOT NULL,
|
|
uri TEXT NOT NULL DEFAULT '', -- connection string redacted (no creds)
|
|
description TEXT NOT NULL DEFAULT '',
|
|
tags_csv TEXT NOT NULL DEFAULT '',
|
|
last_seen_at TEXT NOT NULL DEFAULT '',
|
|
table_count INTEGER NOT NULL DEFAULT 0,
|
|
size_bytes INTEGER NOT NULL DEFAULT 0,
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_databases_kind ON databases(kind);
|