Something went wrong. Try again.
prefect server in zig
Something went wrong. Try again.
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753const std = @import("std");const backend = @import("../backend.zig");const log = @import("../../logging.zig");
// PostgreSQL schema: native UUID + TIMESTAMPTZ throughout.// Mirrors python prefect's `UUID` + `Timestamp` TypeDecorators// (src/prefect/server/utilities/database.py).// SQLite uses CHAR(36)/TEXT in schema/sqlite.zig — same wire format// (ISO 8601 strings + dashed UUIDs) at the API boundary.
/// Magic number for the schema-init advisory lock. Chosen so it's/// recognizable in `pg_locks` (the high bits spell "prefSCHM" in ASCII/// approximation). Two replicas booting simultaneously serialize on this/// lock — the second waits until the first finishes CREATE TABLE IF NOT/// EXISTS, so a partially-initialized schema can't be observed.const SCHEMA_INIT_LOCK: i64 = 0x70_72_65_66_53_43_48_4d; // "prefSCHM"
pub fn init() !void { // pg_advisory_lock blocks until acquired and is released on session close // or explicit unlock. Bracket the whole schema init. try backend.db.exec("SELECT pg_advisory_lock($1)", .{SCHEMA_INIT_LOCK}); defer backend.db.exec("SELECT pg_advisory_unlock($1)", .{SCHEMA_INIT_LOCK}) catch |err| { log.warn("database", "schema-init advisory unlock failed: {}", .{err}); };
try backend.db.exec( \\CREATE TABLE IF NOT EXISTS flow ( \\ id UUID PRIMARY KEY, \\ created TIMESTAMPTZ DEFAULT NOW(), \\ updated TIMESTAMPTZ DEFAULT NOW(), \\ name TEXT NOT NULL UNIQUE, \\ tags JSONB DEFAULT '[]' \\) , .{});
try backend.db.exec( \\CREATE TABLE IF NOT EXISTS flow_run ( \\ id UUID PRIMARY KEY, \\ created TIMESTAMPTZ DEFAULT NOW(), \\ updated TIMESTAMPTZ DEFAULT NOW(), \\ flow_id UUID REFERENCES flow(id), \\ name TEXT NOT NULL, \\ parameters JSONB DEFAULT '{}', \\ tags JSONB DEFAULT '[]', \\ state_id UUID, \\ state_type TEXT, \\ state_name TEXT, \\ state_timestamp TIMESTAMPTZ, \\ state_message TEXT, \\ state_details JSONB DEFAULT '{}', \\ run_count BIGINT DEFAULT 0, \\ expected_start_time TIMESTAMPTZ, \\ next_scheduled_start_time TIMESTAMPTZ, \\ start_time TIMESTAMPTZ, \\ end_time TIMESTAMPTZ, \\ total_run_time DOUBLE PRECISION DEFAULT 0.0, \\ deployment_id UUID, \\ deployment_version TEXT, \\ work_queue_name TEXT, \\ work_queue_id UUID, \\ auto_scheduled INTEGER DEFAULT 0, \\ idempotency_key TEXT, \\ empirical_policy TEXT DEFAULT '{}', \\ state_transition_id UUID, \\ parent_task_run_id UUID, \\ job_variables JSONB DEFAULT '{}', \\ labels JSONB DEFAULT '{}', \\ flow_version TEXT, \\ infrastructure_pid TEXT, \\ deployment_concurrency_slot UUID \\) , .{}); // Databases created before these columns existed. Nullable and // default-less, so postgres takes a catalog-only lock — no table rewrite. backend.db.exec("ALTER TABLE flow_run ADD COLUMN IF NOT EXISTS flow_version TEXT", .{}) catch {}; backend.db.exec("ALTER TABLE flow_run ADD COLUMN IF NOT EXISTS infrastructure_pid TEXT", .{}) catch {}; // The limit a run holds a deployment concurrency slot on, null when it // holds none. Runs already active when the column arrives took their // slot under the old anonymous counter, so they are marked as holders // once: only when the column was missing, and `deployment` can only // exist at this point on a database being upgraded. const had_slot_column = blk: { var r = backend.db.row( "SELECT 1 FROM information_schema.columns WHERE table_name = 'flow_run' AND column_name = 'deployment_concurrency_slot'", .{}, ) catch break :blk true; if (r) |*found| { found.deinit(); break :blk true; } break :blk false; }; if (!had_slot_column) { try backend.db.exec("ALTER TABLE flow_run ADD COLUMN IF NOT EXISTS deployment_concurrency_slot UUID", .{}); backend.db.exec( \\UPDATE flow_run SET deployment_concurrency_slot = \\ (SELECT concurrency_limit_id FROM deployment WHERE deployment.id = flow_run.deployment_id) \\WHERE state_type IN ('PENDING', 'RUNNING', 'CANCELLING') AND deployment_id IS NOT NULL , .{}) catch {}; }
try backend.db.exec( \\CREATE TABLE IF NOT EXISTS flow_run_state ( \\ id UUID PRIMARY KEY, \\ created TIMESTAMPTZ DEFAULT NOW(), \\ flow_run_id UUID REFERENCES flow_run(id) ON DELETE CASCADE, \\ type TEXT NOT NULL, \\ name TEXT NOT NULL, \\ timestamp TIMESTAMPTZ DEFAULT NOW(), \\ message TEXT, \\ state_details JSONB DEFAULT '{}' \\) , .{});
try backend.db.exec( \\CREATE TABLE IF NOT EXISTS flow_run_input ( \\ id UUID PRIMARY KEY, \\ created TIMESTAMPTZ DEFAULT NOW(), \\ updated TIMESTAMPTZ DEFAULT NOW(), \\ flow_run_id UUID NOT NULL REFERENCES flow_run(id) ON DELETE CASCADE, \\ key TEXT NOT NULL, \\ value TEXT NOT NULL, \\ sender TEXT, \\ UNIQUE(flow_run_id, key) \\) , .{}); try backend.db.exec( \\CREATE INDEX IF NOT EXISTS ix_flow_run_input__flow_run_id_key ON flow_run_input(flow_run_id, key) , .{});
try backend.db.exec( \\CREATE TABLE IF NOT EXISTS task_run ( \\ id UUID PRIMARY KEY, \\ created TIMESTAMPTZ DEFAULT NOW(), \\ updated TIMESTAMPTZ DEFAULT NOW(), \\ flow_run_id UUID REFERENCES flow_run(id) ON DELETE CASCADE, \\ name TEXT NOT NULL, \\ task_key TEXT NOT NULL, \\ dynamic_key TEXT NOT NULL, \\ cache_key TEXT, \\ cache_expiration TIMESTAMPTZ, \\ task_version TEXT, \\ empirical_policy JSONB DEFAULT '{}', \\ task_inputs JSONB DEFAULT '{}', \\ tags JSONB DEFAULT '[]', \\ labels JSONB DEFAULT '{}', \\ state_id UUID, \\ state_type TEXT, \\ state_name TEXT, \\ state_timestamp TIMESTAMPTZ, \\ state_message TEXT, \\ state_details JSONB DEFAULT '{}', \\ state_data JSONB, \\ run_count BIGINT DEFAULT 0, \\ flow_run_run_count BIGINT DEFAULT 0, \\ expected_start_time TIMESTAMPTZ, \\ next_scheduled_start_time TIMESTAMPTZ, \\ start_time TIMESTAMPTZ, \\ end_time TIMESTAMPTZ, \\ total_run_time DOUBLE PRECISION DEFAULT 0.0 \\) , .{});
try backend.db.exec( \\CREATE TABLE IF NOT EXISTS task_run_state ( \\ id UUID PRIMARY KEY, \\ created TIMESTAMPTZ DEFAULT NOW(), \\ task_run_id UUID REFERENCES task_run(id) ON DELETE CASCADE, \\ type TEXT NOT NULL, \\ name TEXT NOT NULL, \\ timestamp TIMESTAMPTZ DEFAULT NOW(), \\ message TEXT, \\ state_details JSONB DEFAULT '{}' \\) , .{});
// events.resource_id stays TEXT — it's a structured string like // "prefect.flow-run.{uuid}", not a UUID itself. try backend.db.exec( \\CREATE TABLE IF NOT EXISTS events ( \\ id UUID PRIMARY KEY, \\ created TIMESTAMPTZ DEFAULT NOW(), \\ updated TIMESTAMPTZ DEFAULT NOW(), \\ occurred TIMESTAMPTZ NOT NULL, \\ event TEXT NOT NULL, \\ resource_id TEXT NOT NULL, \\ resource JSONB NOT NULL DEFAULT '{}', \\ related_resource_ids JSONB DEFAULT '[]', \\ related JSONB DEFAULT '[]', \\ payload JSONB DEFAULT '{}', \\ received TIMESTAMPTZ NOT NULL, \\ recorded TIMESTAMPTZ NOT NULL, \\ follows UUID \\) , .{});
try backend.db.exec( \\CREATE TABLE IF NOT EXISTS block_type ( \\ id UUID PRIMARY KEY, \\ created TIMESTAMPTZ DEFAULT NOW(), \\ updated TIMESTAMPTZ DEFAULT NOW(), \\ name TEXT NOT NULL, \\ slug TEXT NOT NULL UNIQUE, \\ logo_url TEXT, \\ documentation_url TEXT, \\ description TEXT, \\ code_example TEXT, \\ is_protected INTEGER DEFAULT 0 \\) , .{});
try backend.db.exec( \\CREATE TABLE IF NOT EXISTS block_schema ( \\ id UUID PRIMARY KEY, \\ created TIMESTAMPTZ DEFAULT NOW(), \\ updated TIMESTAMPTZ DEFAULT NOW(), \\ checksum TEXT NOT NULL, \\ fields JSONB NOT NULL DEFAULT '{}', \\ capabilities JSONB NOT NULL DEFAULT '[]', \\ version TEXT NOT NULL DEFAULT '1', \\ block_type_id UUID NOT NULL REFERENCES block_type(id) ON DELETE CASCADE, \\ UNIQUE(checksum, version) \\) , .{});
try backend.db.exec( \\CREATE TABLE IF NOT EXISTS block_document ( \\ id UUID PRIMARY KEY, \\ created TIMESTAMPTZ DEFAULT NOW(), \\ updated TIMESTAMPTZ DEFAULT NOW(), \\ name TEXT, \\ data JSONB NOT NULL DEFAULT '{}', \\ is_anonymous INTEGER DEFAULT 0, \\ block_type_id UUID NOT NULL REFERENCES block_type(id), \\ block_type_name TEXT, \\ block_schema_id UUID NOT NULL REFERENCES block_schema(id), \\ UNIQUE(block_type_id, name) \\) , .{});
try backend.db.exec( \\CREATE TABLE IF NOT EXISTS variable ( \\ id UUID PRIMARY KEY, \\ created TIMESTAMPTZ DEFAULT NOW(), \\ updated TIMESTAMPTZ DEFAULT NOW(), \\ name TEXT NOT NULL UNIQUE, \\ value JSONB DEFAULT 'null', \\ tags JSONB DEFAULT '[]' \\) , .{});
// filters is the JSON list of python's `SavedSearchFilter` objects try backend.db.exec( \\CREATE TABLE IF NOT EXISTS saved_search ( \\ id UUID PRIMARY KEY, \\ created TIMESTAMPTZ DEFAULT NOW(), \\ updated TIMESTAMPTZ DEFAULT NOW(), \\ name TEXT NOT NULL UNIQUE, \\ filters JSONB NOT NULL DEFAULT '[]' \\) , .{});
// generic singleton-key-value store for server configuration. mirrors // python's `configuration` table (prefect.server.models.configuration). try backend.db.exec( \\CREATE TABLE IF NOT EXISTS configuration ( \\ id UUID PRIMARY KEY, \\ created TIMESTAMPTZ DEFAULT NOW(), \\ updated TIMESTAMPTZ DEFAULT NOW(), \\ key TEXT NOT NULL UNIQUE, \\ value JSONB NOT NULL \\) , .{});
try backend.db.exec( \\CREATE TABLE IF NOT EXISTS work_pool ( \\ id UUID PRIMARY KEY, \\ created TIMESTAMPTZ DEFAULT NOW(), \\ updated TIMESTAMPTZ DEFAULT NOW(), \\ name TEXT NOT NULL UNIQUE, \\ description TEXT, \\ type TEXT NOT NULL DEFAULT 'process', \\ base_job_template JSONB DEFAULT '{}', \\ is_paused INTEGER DEFAULT 0, \\ concurrency_limit BIGINT, \\ default_queue_id UUID, \\ status TEXT DEFAULT 'NOT_READY' \\) , .{});
try backend.db.exec( \\CREATE TABLE IF NOT EXISTS work_queue ( \\ id UUID PRIMARY KEY, \\ created TIMESTAMPTZ DEFAULT NOW(), \\ updated TIMESTAMPTZ DEFAULT NOW(), \\ name TEXT NOT NULL, \\ description TEXT DEFAULT '', \\ is_paused INTEGER DEFAULT 0, \\ concurrency_limit BIGINT, \\ priority BIGINT DEFAULT 1, \\ work_pool_id UUID NOT NULL REFERENCES work_pool(id) ON DELETE CASCADE, \\ last_polled TIMESTAMPTZ, \\ status TEXT DEFAULT 'NOT_READY', \\ UNIQUE(work_pool_id, name) \\) , .{});
try backend.db.exec( \\CREATE TABLE IF NOT EXISTS worker ( \\ id UUID PRIMARY KEY, \\ created TIMESTAMPTZ DEFAULT NOW(), \\ updated TIMESTAMPTZ DEFAULT NOW(), \\ name TEXT NOT NULL, \\ work_pool_id UUID NOT NULL REFERENCES work_pool(id) ON DELETE CASCADE, \\ last_heartbeat_time TIMESTAMPTZ, \\ heartbeat_interval_seconds BIGINT, \\ status TEXT DEFAULT 'OFFLINE', \\ UNIQUE(work_pool_id, name) \\) , .{});
// concurrency_limit must precede deployment — deployment has a FK // (concurrency_limit_id REFERENCES concurrency_limit(id)) and postgres // (unlike sqlite) requires the target table to exist when the FK is // declared. try backend.db.exec( \\CREATE TABLE IF NOT EXISTS concurrency_limit ( \\ id UUID PRIMARY KEY, \\ created TIMESTAMPTZ DEFAULT NOW(), \\ updated TIMESTAMPTZ DEFAULT NOW(), \\ name TEXT NOT NULL UNIQUE, \\ "limit" INTEGER NOT NULL, \\ active BOOLEAN DEFAULT TRUE, \\ active_slots INTEGER DEFAULT 0, \\ denied_slots INTEGER DEFAULT 0, \\ slot_decay_per_second DOUBLE PRECISION DEFAULT 0.0, \\ avg_slot_occupancy_seconds DOUBLE PRECISION DEFAULT 2.0 \\) , .{});
try backend.db.exec( \\CREATE TABLE IF NOT EXISTS deployment ( \\ id UUID PRIMARY KEY, \\ created TIMESTAMPTZ DEFAULT NOW(), \\ updated TIMESTAMPTZ DEFAULT NOW(), \\ name TEXT NOT NULL, \\ flow_id UUID NOT NULL REFERENCES flow(id) ON DELETE CASCADE, \\ version TEXT, \\ description TEXT, \\ paused INTEGER DEFAULT 0, \\ status TEXT DEFAULT 'NOT_READY', \\ last_polled TIMESTAMPTZ, \\ parameters JSONB DEFAULT '{}', \\ parameter_openapi_schema TEXT, \\ enforce_parameter_schema INTEGER DEFAULT 1, \\ tags JSONB DEFAULT '[]', \\ labels JSONB DEFAULT '{}', \\ path TEXT, \\ entrypoint TEXT, \\ job_variables JSONB DEFAULT '{}', \\ pull_steps TEXT, \\ work_pool_name TEXT, \\ work_queue_name TEXT, \\ work_queue_id UUID REFERENCES work_queue(id) ON DELETE SET NULL, \\ storage_document_id UUID, \\ infrastructure_document_id UUID, \\ concurrency_limit_id UUID REFERENCES concurrency_limit(id) ON DELETE SET NULL, \\ concurrency_options JSONB, \\ UNIQUE(flow_id, name) \\) , .{}); backend.db.exec("ALTER TABLE deployment ADD COLUMN IF NOT EXISTS concurrency_options JSONB", .{}) catch {};
try backend.db.exec( \\CREATE TABLE IF NOT EXISTS deployment_schedule ( \\ id UUID PRIMARY KEY, \\ created TIMESTAMPTZ DEFAULT NOW(), \\ updated TIMESTAMPTZ DEFAULT NOW(), \\ deployment_id UUID NOT NULL REFERENCES deployment(id) ON DELETE CASCADE, \\ schedule TEXT NOT NULL, \\ active INTEGER DEFAULT 1, \\ max_scheduled_runs BIGINT, \\ parameters JSONB DEFAULT '{}', \\ slug TEXT, \\ UNIQUE(deployment_id, slug) \\) , .{});
try backend.db.exec( \\CREATE TABLE IF NOT EXISTS log ( \\ id UUID PRIMARY KEY, \\ created TIMESTAMPTZ DEFAULT NOW(), \\ updated TIMESTAMPTZ DEFAULT NOW(), \\ name TEXT NOT NULL, \\ level INTEGER NOT NULL, \\ message TEXT NOT NULL, \\ timestamp TIMESTAMPTZ NOT NULL, \\ flow_run_id UUID REFERENCES flow_run(id) ON DELETE CASCADE, \\ task_run_id UUID \\) , .{});
try backend.db.exec( \\ALTER TABLE log DROP CONSTRAINT IF EXISTS log_task_run_id_fkey , .{});
try backend.db.exec( \\CREATE TABLE IF NOT EXISTS automation ( \\ id UUID PRIMARY KEY, \\ created TIMESTAMPTZ DEFAULT NOW(), \\ updated TIMESTAMPTZ DEFAULT NOW(), \\ name TEXT NOT NULL, \\ description TEXT DEFAULT '', \\ enabled INTEGER DEFAULT 1, \\ tags JSONB DEFAULT '[]', \\ trigger JSONB NOT NULL, \\ actions JSONB NOT NULL, \\ actions_on_trigger JSONB DEFAULT '[]', \\ actions_on_resolve JSONB DEFAULT '[]' \\) , .{});
try backend.db.exec( \\CREATE TABLE IF NOT EXISTS automation_bucket ( \\ id UUID PRIMARY KEY, \\ created TIMESTAMPTZ DEFAULT NOW(), \\ updated TIMESTAMPTZ DEFAULT NOW(), \\ automation_id UUID NOT NULL REFERENCES automation(id) ON DELETE CASCADE, \\ trigger_id UUID NOT NULL, \\ bucketing_key JSONB DEFAULT '[]', \\ start_time TIMESTAMPTZ NOT NULL, \\ end_time TIMESTAMPTZ NOT NULL, \\ count INTEGER DEFAULT 0, \\ last_event JSONB, \\ triggered_at TIMESTAMPTZ, \\ UNIQUE(automation_id, trigger_id, bucketing_key) \\) , .{});
try backend.db.exec( \\CREATE TABLE IF NOT EXISTS composite_trigger_child_firing ( \\ id UUID PRIMARY KEY, \\ created TIMESTAMPTZ DEFAULT NOW(), \\ updated TIMESTAMPTZ DEFAULT NOW(), \\ automation_id UUID NOT NULL REFERENCES automation(id) ON DELETE CASCADE, \\ parent_trigger_id UUID NOT NULL, \\ child_trigger_id UUID NOT NULL, \\ child_fired_at TIMESTAMPTZ NOT NULL, \\ UNIQUE(automation_id, parent_trigger_id, child_trigger_id) \\) , .{});
try backend.db.exec( \\CREATE TABLE IF NOT EXISTS automation_related_resource ( \\ id UUID PRIMARY KEY, \\ created TIMESTAMPTZ DEFAULT NOW(), \\ updated TIMESTAMPTZ DEFAULT NOW(), \\ automation_id UUID NOT NULL REFERENCES automation(id) ON DELETE CASCADE, \\ resource_id TEXT NOT NULL, \\ automation_owned_by_resource INTEGER DEFAULT 0, \\ UNIQUE(automation_id, resource_id) \\) , .{});
try backend.db.exec( \\CREATE TABLE IF NOT EXISTS artifact ( \\ id UUID PRIMARY KEY, \\ created TIMESTAMPTZ DEFAULT NOW(), \\ updated TIMESTAMPTZ DEFAULT NOW(), \\ key TEXT, \\ task_run_id UUID, \\ flow_run_id UUID, \\ type TEXT, \\ data JSONB, \\ description TEXT, \\ metadata_ JSONB \\) , .{});
try backend.db.exec( \\CREATE TABLE IF NOT EXISTS artifact_collection ( \\ id UUID PRIMARY KEY, \\ created TIMESTAMPTZ DEFAULT NOW(), \\ updated TIMESTAMPTZ DEFAULT NOW(), \\ key TEXT NOT NULL UNIQUE, \\ latest_id UUID NOT NULL, \\ task_run_id UUID, \\ flow_run_id UUID, \\ type TEXT, \\ data JSONB, \\ description TEXT, \\ metadata_ JSONB \\) , .{});
// indexes try backend.db.exec("CREATE INDEX IF NOT EXISTS ix_flow_run__state_type ON flow_run(state_type)", .{}); try backend.db.exec("CREATE INDEX IF NOT EXISTS ix_flow_run__flow_id ON flow_run(flow_id)", .{}); try backend.db.exec("CREATE INDEX IF NOT EXISTS ix_flow_run__next_scheduled_start_time ON flow_run(next_scheduled_start_time)", .{}); try backend.db.exec("CREATE UNIQUE INDEX IF NOT EXISTS uq_flow_run__flow_id_idempotency_key ON flow_run(flow_id, idempotency_key) WHERE idempotency_key IS NOT NULL", .{}); try backend.db.exec("CREATE UNIQUE INDEX IF NOT EXISTS uq_flow_run_state__id ON flow_run_state(id)", .{}); try backend.db.exec("CREATE INDEX IF NOT EXISTS ix_flow_run_state__flow_run_id ON flow_run_state(flow_run_id)", .{}); try backend.db.exec("CREATE UNIQUE INDEX IF NOT EXISTS uq_task_run_state__id ON task_run_state(id)", .{}); try backend.db.exec("CREATE INDEX IF NOT EXISTS ix_task_run_state__task_run_id ON task_run_state(task_run_id)", .{}); try backend.db.exec("CREATE INDEX IF NOT EXISTS ix_task_run__flow_run_id ON task_run(flow_run_id)", .{}); try backend.db.exec("CREATE UNIQUE INDEX IF NOT EXISTS uq_task_run__flow_run_id_task_key_dynamic_key ON task_run(flow_run_id, task_key, dynamic_key)", .{}); try backend.db.exec("CREATE INDEX IF NOT EXISTS ix_task_run__task_key_dynamic_key ON task_run(task_key, dynamic_key)", .{}); // Filter endpoint default ORDER BY created DESC — without this index, // every /task_runs/filter degrades to scan+sort over the whole table. try backend.db.exec("CREATE INDEX IF NOT EXISTS ix_task_run__created ON task_run(created DESC)", .{}); try backend.db.exec("CREATE INDEX IF NOT EXISTS ix_flow_run__created ON flow_run(created DESC)", .{}); try backend.db.exec("CREATE INDEX IF NOT EXISTS ix_events__occurred ON events(occurred)", .{}); try backend.db.exec("CREATE INDEX IF NOT EXISTS ix_events__event__id ON events(event, id)", .{}); try backend.db.exec("CREATE INDEX IF NOT EXISTS ix_events__event_resource_id_occurred ON events(event, resource_id, occurred)", .{}); try backend.db.exec("CREATE INDEX IF NOT EXISTS ix_events__occurred_id ON events(occurred, id)", .{}); try backend.db.exec("CREATE INDEX IF NOT EXISTS ix_block_schema__block_type_id ON block_schema(block_type_id)", .{}); try backend.db.exec("CREATE INDEX IF NOT EXISTS ix_block_schema__checksum ON block_schema(checksum)", .{}); try backend.db.exec("CREATE INDEX IF NOT EXISTS ix_block_document__block_type_id ON block_document(block_type_id)", .{}); try backend.db.exec("CREATE INDEX IF NOT EXISTS ix_block_document__block_schema_id ON block_document(block_schema_id)", .{}); try backend.db.exec("CREATE INDEX IF NOT EXISTS ix_block_document__name ON block_document(name)", .{}); try backend.db.exec("CREATE INDEX IF NOT EXISTS ix_variable__name ON variable(name)", .{}); try backend.db.exec("CREATE INDEX IF NOT EXISTS ix_work_pool__name ON work_pool(name)", .{}); try backend.db.exec("CREATE INDEX IF NOT EXISTS ix_work_pool__type ON work_pool(type)", .{}); try backend.db.exec("CREATE INDEX IF NOT EXISTS ix_work_queue__work_pool_id ON work_queue(work_pool_id)", .{}); try backend.db.exec("CREATE INDEX IF NOT EXISTS ix_work_queue__priority ON work_queue(priority)", .{}); try backend.db.exec("CREATE INDEX IF NOT EXISTS ix_worker__work_pool_id ON worker(work_pool_id)", .{}); try backend.db.exec("CREATE INDEX IF NOT EXISTS ix_flow_run__deployment_id ON flow_run(deployment_id)", .{}); try backend.db.exec("CREATE INDEX IF NOT EXISTS ix_flow_run__parent_task_run_id ON flow_run(parent_task_run_id) WHERE parent_task_run_id IS NOT NULL", .{}); try backend.db.exec("CREATE INDEX IF NOT EXISTS ix_deployment__flow_id ON deployment(flow_id)", .{}); try backend.db.exec("CREATE INDEX IF NOT EXISTS ix_deployment__work_queue_id ON deployment(work_queue_id)", .{}); try backend.db.exec("CREATE INDEX IF NOT EXISTS ix_deployment__work_pool_name ON deployment(work_pool_name)", .{}); try backend.db.exec("CREATE INDEX IF NOT EXISTS ix_deployment__created ON deployment(created)", .{}); try backend.db.exec("CREATE INDEX IF NOT EXISTS ix_deployment__updated ON deployment(updated)", .{}); try backend.db.exec("CREATE INDEX IF NOT EXISTS ix_deployment_schedule__deployment_id ON deployment_schedule(deployment_id)", .{}); try backend.db.exec("CREATE INDEX IF NOT EXISTS ix_deployment_schedule__active ON deployment_schedule(active)", .{}); try backend.db.exec("CREATE INDEX IF NOT EXISTS ix_concurrency_limit__name ON concurrency_limit(name)", .{}); try backend.db.exec("CREATE INDEX IF NOT EXISTS ix_log__flow_run_id ON log(flow_run_id)", .{}); try backend.db.exec("CREATE INDEX IF NOT EXISTS ix_log__task_run_id ON log(task_run_id)", .{}); try backend.db.exec("CREATE INDEX IF NOT EXISTS ix_log__level ON log(level)", .{}); try backend.db.exec("CREATE INDEX IF NOT EXISTS ix_log__timestamp ON log(timestamp)", .{}); try backend.db.exec("CREATE INDEX IF NOT EXISTS ix_log__flow_run_id_timestamp ON log(flow_run_id, timestamp)", .{}); try backend.db.exec("CREATE INDEX IF NOT EXISTS ix_automation__name ON automation(name)", .{}); try backend.db.exec("CREATE INDEX IF NOT EXISTS ix_automation__enabled ON automation(enabled)", .{}); try backend.db.exec("CREATE INDEX IF NOT EXISTS ix_automation__updated ON automation(updated)", .{}); try backend.db.exec("CREATE INDEX IF NOT EXISTS ix_automation_bucket__automation_id ON automation_bucket(automation_id)", .{}); try backend.db.exec("CREATE INDEX IF NOT EXISTS ix_automation_bucket__end_time ON automation_bucket(end_time)", .{}); try backend.db.exec("CREATE UNIQUE INDEX IF NOT EXISTS uq_automation_related_resource__automation_id_resource_id ON automation_related_resource(automation_id, resource_id)", .{}); try backend.db.exec("CREATE INDEX IF NOT EXISTS ix_automation_related_resource__automation_id ON automation_related_resource(automation_id)", .{}); try backend.db.exec("CREATE INDEX IF NOT EXISTS ix_automation_related_resource__resource_id ON automation_related_resource(resource_id)", .{}); try backend.db.exec("CREATE INDEX IF NOT EXISTS ix_artifact__key ON artifact(key)", .{}); try backend.db.exec("CREATE INDEX IF NOT EXISTS ix_artifact__task_run_id ON artifact(task_run_id)", .{}); try backend.db.exec("CREATE INDEX IF NOT EXISTS ix_artifact__flow_run_id ON artifact(flow_run_id)", .{}); try backend.db.exec("CREATE INDEX IF NOT EXISTS ix_artifact__key_created_desc ON artifact(key, created DESC)", .{}); // CREATE TABLE IF NOT EXISTS does not add the inline UNIQUE constraint to // databases created by an older schema. The artifact upsert relies on this // conflict target, so reconcile the invariant explicitly at startup. try backend.db.exec("CREATE UNIQUE INDEX IF NOT EXISTS uq_artifact_collection__key ON artifact_collection(key)", .{}); try backend.db.exec("CREATE INDEX IF NOT EXISTS ix_artifact_collection__key_latest_id ON artifact_collection(key, latest_id)", .{});
reconcilePrimaryKeys(); reconcileForeignKeys();
log.info("database", "postgres schema initialized", .{});}
/// Tables whose CREATE TABLE declares `id UUID PRIMARY KEY`.const primary_key_tables = [_][]const u8{ "artifact", "artifact_collection", "automation", "automation_bucket", "automation_related_resource", "block_document", "block_schema", "block_type", "concurrency_limit", "configuration", "deployment", "deployment_schedule", "events", "flow", "flow_run", "flow_run_input", "flow_run_state", "log", "task_run", "task_run_state", "variable", "work_pool", "work_queue", "worker", "composite_trigger_child_firing",};
/// Restore PRIMARY KEY constraints the live database is missing.////// Same drift as the foreign keys below, one level deeper: a production/// database was found with `pg_constraint` completely empty — no primary keys,/// no unique constraints, nothing — even though every CREATE TABLE declares/// `id UUID PRIMARY KEY`. Foreign keys cannot be added until this is fixed;/// postgres rejects them with "there is no unique constraint matching given/// keys for referenced table", which is exactly how this was found.////// Builds the unique index first and promotes it with `ADD CONSTRAINT .../// PRIMARY KEY USING INDEX`, so the expensive part is the index build and the/// promotion is instant. The build takes a write lock on that table; it is a/// one-time cost on first startup after upgrading, and a no-op afterwards./// Duplicate ids would make the index build fail — that is logged and skipped/// rather than fatal, and the dependent foreign keys then log their own/// failure instead of silently misbehaving.fn reconcilePrimaryKeys() void { for (primary_key_tables) |table| { var existing = backend.db.row( "SELECT 1 FROM pg_constraint WHERE contype = 'p' AND conrelid = ?::regclass", .{table}, ) catch |err| { log.warn("database", "primary key probe failed for {s}: {}", .{ table, err }); continue; }; if (existing) |*r| { r.deinit(); continue; }
var idx_buf: [128]u8 = undefined; const idx = std.fmt.bufPrint(&idx_buf, "pk_{s}__id", .{table}) catch continue;
var create_buf: [256]u8 = undefined; const create_sql = std.fmt.bufPrint( &create_buf, "CREATE UNIQUE INDEX IF NOT EXISTS {s} ON {s}(id)", .{ idx, table }, ) catch continue; backend.db.exec(create_sql, .{}) catch |err| { log.warn("database", "could not build unique index for {s}.id: {}", .{ table, err }); continue; };
var alter_buf: [256]u8 = undefined; const alter_sql = std.fmt.bufPrint( &alter_buf, "ALTER TABLE {s} ADD CONSTRAINT {s}_pkey PRIMARY KEY USING INDEX {s}", .{ table, table, idx }, ) catch continue; backend.db.exec(alter_sql, .{}) catch |err| { log.warn("database", "could not promote {s}.id to primary key: {}", .{ table, err }); continue; }; log.info("database", "restored missing primary key on {s}.id", .{table}); }}
/// Referential integrity the CREATE TABLE statements above declare inline./// `CREATE TABLE IF NOT EXISTS` is a no-op on an existing table, and the/// migration runner was removed in 5763f1b, so a database created by an older/// schema keeps its original definition forever. A live deployment ran for/// months with `pg_constraint` holding zero rows of `contype='f'`: deleting a/// flow_run stranded its task_runs and states instead of cascading, leaving/// 233,758 orphaned task_runs behind. Fresh databases were always correct —/// only pre-existing ones drifted, and nothing detected it.const ForeignKey = struct { child: []const u8, column: []const u8, parent: []const u8, /// CASCADE | SET NULL | NO ACTION — must match the inline declaration. on_delete: []const u8,};
const foreign_keys = [_]ForeignKey{ .{ .child = "flow_run", .column = "flow_id", .parent = "flow", .on_delete = "NO ACTION" }, .{ .child = "flow_run_state", .column = "flow_run_id", .parent = "flow_run", .on_delete = "CASCADE" }, .{ .child = "task_run", .column = "flow_run_id", .parent = "flow_run", .on_delete = "CASCADE" }, .{ .child = "task_run_state", .column = "task_run_id", .parent = "task_run", .on_delete = "CASCADE" }, .{ .child = "log", .column = "flow_run_id", .parent = "flow_run", .on_delete = "CASCADE" }, .{ .child = "block_schema", .column = "block_type_id", .parent = "block_type", .on_delete = "CASCADE" }, .{ .child = "block_document", .column = "block_type_id", .parent = "block_type", .on_delete = "NO ACTION" }, .{ .child = "block_document", .column = "block_schema_id", .parent = "block_schema", .on_delete = "NO ACTION" }, .{ .child = "work_queue", .column = "work_pool_id", .parent = "work_pool", .on_delete = "CASCADE" }, .{ .child = "worker", .column = "work_pool_id", .parent = "work_pool", .on_delete = "CASCADE" }, .{ .child = "deployment", .column = "flow_id", .parent = "flow", .on_delete = "CASCADE" }, .{ .child = "deployment", .column = "work_queue_id", .parent = "work_queue", .on_delete = "SET NULL" }, .{ .child = "deployment", .column = "concurrency_limit_id", .parent = "concurrency_limit", .on_delete = "SET NULL" }, .{ .child = "deployment_schedule", .column = "deployment_id", .parent = "deployment", .on_delete = "CASCADE" }, .{ .child = "flow_run_input", .column = "flow_run_id", .parent = "flow_run", .on_delete = "CASCADE" }, .{ .child = "automation_bucket", .column = "automation_id", .parent = "automation", .on_delete = "CASCADE" }, .{ .child = "automation_related_resource", .column = "automation_id", .parent = "automation", .on_delete = "CASCADE" }, .{ .child = "composite_trigger_child_firing", .column = "automation_id", .parent = "automation", .on_delete = "CASCADE" },};
/// Add any declared foreign key the live database is missing.////// Two-step on purpose. `ADD CONSTRAINT ... NOT VALID` takes a brief/// ACCESS EXCLUSIVE lock and does NOT scan the table, so it is safe on a large/// live table; `VALIDATE CONSTRAINT` then scans under a weaker/// SHARE UPDATE EXCLUSIVE lock that readers and writers can work alongside.////// Validation fails if legacy orphans predate the constraint. That is not/// fatal and deliberately not treated as such: a NOT VALID foreign key still/// enforces on every new INSERT/UPDATE and still cascades on DELETE — it only/// declines to vouch for rows written before it existed. The orphan vacuum/// passes clear those, after which a later restart validates cleanly.fn reconcileForeignKeys() void { for (foreign_keys) |fk| { // `?` placeholders, rewritten to $n by dialect.zig — writing $1 // directly bypasses that rewrite and the probe silently matches // nothing. Match on (table, column) rather than constraint name so an // FK postgres named itself (`task_run_flow_run_id_fkey` on a fresh // database) still counts and we don't add a duplicate. var existing = backend.db.row( \\SELECT c.conname, CASE WHEN c.convalidated THEN 1 ELSE 0 END \\FROM pg_constraint c \\JOIN pg_attribute a ON a.attrelid = c.conrelid AND a.attnum = ANY(c.conkey) \\WHERE c.contype = 'f' AND c.conrelid = ?::regclass AND a.attname = ? , .{ fk.child, fk.column }) catch |err| { log.warn("database", "foreign key probe failed for {s}.{s}: {}", .{ fk.child, fk.column, err }); continue; }; // Release before any DDL: a live Row holds its pooled connection, and // the ALTER below would then wait on a lock that only this loop can // release — it timed out on every table until this deinit existed. if (existing) |*r| { var retry_buf: [128]u8 = undefined; const conname = std.fmt.bufPrint(&retry_buf, "{s}", .{r.text(0)}) catch ""; const validated = r.int(1) == 1; r.deinit();
// A constraint left NOT VALID by an earlier startup (legacy // orphans at the time) would otherwise stay that way forever, // since it exists and the probe skips it. Retry now that the // orphan sweeps have had a chance to run. if (!validated and conname.len > 0) { var v_buf: [256]u8 = undefined; const v_sql = std.fmt.bufPrint(&v_buf, "ALTER TABLE {s} VALIDATE CONSTRAINT {s}", .{ fk.child, conname }) catch continue; if (backend.db.exec(v_sql, .{})) |_| { log.info("database", "validated previously NOT VALID constraint {s}", .{conname}); } else |_| { log.debug("database", "{s} still NOT VALID; orphans remain", .{conname}); } } continue; }
var name_buf: [128]u8 = undefined; const name = std.fmt.bufPrint(&name_buf, "fk_{s}__{s}", .{ fk.child, fk.column }) catch continue;
var add_buf: [512]u8 = undefined; const add_sql = std.fmt.bufPrint( &add_buf, "ALTER TABLE {s} ADD CONSTRAINT {s} FOREIGN KEY ({s}) REFERENCES {s}(id) ON DELETE {s} NOT VALID", .{ fk.child, name, fk.column, fk.parent, fk.on_delete }, ) catch continue;
backend.db.exec(add_sql, .{}) catch |err| { log.warn("database", "could not add foreign key {s}: {}", .{ name, err }); continue; }; log.info("database", "added missing foreign key {s} ON DELETE {s}", .{ name, fk.on_delete });
var validate_buf: [256]u8 = undefined; const validate_sql = std.fmt.bufPrint(&validate_buf, "ALTER TABLE {s} VALIDATE CONSTRAINT {s}", .{ fk.child, name }) catch continue; backend.db.exec(validate_sql, .{}) catch |err| { log.warn( "database", "{s} stays NOT VALID ({}) — it enforces new writes and cascades; " ++ "orphan vacuum will clear the legacy rows so a later restart can validate", .{ name, err }, ); }; }}