const std = @import("std"); const backend = @import("../backend.zig"); const log = @import("../../logging.zig"); fn hasColumn(comptime table: []const u8, column_name: []const u8) !bool { var rows = try backend.db.query("PRAGMA table_info(" ++ table ++ ")", .{}); defer rows.deinit(); while (try rows.nextChecked()) |row| { if (std.mem.eql(u8, row.text(1), column_name)) return true; } return false; } /// Initialize SQLite schema pub fn init() !void { // flow table try backend.db.exec( \\CREATE TABLE IF NOT EXISTS flow ( \\ id TEXT PRIMARY KEY, \\ created TEXT DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')), \\ updated TEXT DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')), \\ name TEXT NOT NULL UNIQUE, \\ tags TEXT DEFAULT '[]' \\) , .{}); // flow_run table try backend.db.exec( \\CREATE TABLE IF NOT EXISTS flow_run ( \\ id TEXT PRIMARY KEY, \\ created TEXT DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')), \\ updated TEXT DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')), \\ flow_id TEXT REFERENCES flow(id), \\ name TEXT NOT NULL, \\ parameters TEXT DEFAULT '{}', \\ tags TEXT DEFAULT '[]', \\ state_id TEXT, \\ state_type TEXT, \\ state_name TEXT, \\ state_timestamp TEXT, \\ state_message TEXT, \\ state_details TEXT DEFAULT '{}', \\ run_count INTEGER DEFAULT 0, \\ expected_start_time TEXT, \\ next_scheduled_start_time TEXT, \\ start_time TEXT, \\ end_time TEXT, \\ total_run_time REAL DEFAULT 0.0, \\ deployment_id TEXT, \\ deployment_version TEXT, \\ work_queue_name TEXT, \\ work_queue_id TEXT, \\ auto_scheduled INTEGER DEFAULT 0, \\ idempotency_key TEXT, \\ empirical_policy TEXT DEFAULT '{}', \\ state_transition_id TEXT, \\ parent_task_run_id TEXT, \\ job_variables TEXT DEFAULT '{}', \\ labels TEXT DEFAULT '{}', \\ flow_version TEXT, \\ infrastructure_pid TEXT, \\ deployment_concurrency_slot TEXT \\) , .{}); // Existing local DBs predate these two. Same idempotent-ALTER approach as // deployment.concurrency_options below. if (!(try hasColumn("flow_run", "flow_version"))) { try backend.db.exec("ALTER TABLE flow_run ADD COLUMN flow_version TEXT", .{}); } if (!(try hasColumn("flow_run", "infrastructure_pid"))) { try backend.db.exec("ALTER TABLE flow_run ADD COLUMN infrastructure_pid TEXT", .{}); } // 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, here; `deployment` exists by now only on a database being // upgraded, which is the only time there is anything to mark. if (!(try hasColumn("flow_run", "deployment_concurrency_slot"))) { try backend.db.exec("ALTER TABLE flow_run ADD COLUMN deployment_concurrency_slot TEXT", .{}); 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 {}; } // flow_run_state table try backend.db.exec( \\CREATE TABLE IF NOT EXISTS flow_run_state ( \\ id TEXT PRIMARY KEY, \\ created TEXT DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')), \\ flow_run_id TEXT REFERENCES flow_run(id) ON DELETE CASCADE, \\ type TEXT NOT NULL, \\ name TEXT NOT NULL, \\ timestamp TEXT DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')), \\ message TEXT, \\ state_details TEXT DEFAULT '{}' \\) , .{}); // task_run table try backend.db.exec( \\CREATE TABLE IF NOT EXISTS flow_run_input ( \\ id TEXT PRIMARY KEY, \\ created TEXT DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')), \\ updated TEXT DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')), \\ flow_run_id TEXT 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 TEXT PRIMARY KEY, \\ created TEXT DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')), \\ updated TEXT DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')), \\ flow_run_id TEXT 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 TEXT, \\ task_version TEXT, \\ empirical_policy TEXT DEFAULT '{}', \\ task_inputs TEXT DEFAULT '{}', \\ tags TEXT DEFAULT '[]', \\ labels TEXT DEFAULT '{}', \\ state_id TEXT, \\ state_type TEXT, \\ state_name TEXT, \\ state_timestamp TEXT, \\ state_message TEXT, \\ state_details TEXT DEFAULT '{}', \\ state_data TEXT, \\ run_count INTEGER DEFAULT 0, \\ flow_run_run_count INTEGER DEFAULT 0, \\ expected_start_time TEXT, \\ next_scheduled_start_time TEXT, \\ start_time TEXT, \\ end_time TEXT, \\ total_run_time REAL DEFAULT 0.0 \\) , .{}); // task_run_state table try backend.db.exec( \\CREATE TABLE IF NOT EXISTS task_run_state ( \\ id TEXT PRIMARY KEY, \\ created TEXT DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')), \\ task_run_id TEXT REFERENCES task_run(id) ON DELETE CASCADE, \\ type TEXT NOT NULL, \\ name TEXT NOT NULL, \\ timestamp TEXT DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')), \\ message TEXT, \\ state_details TEXT DEFAULT '{}' \\) , .{}); // events table try backend.db.exec( \\CREATE TABLE IF NOT EXISTS events ( \\ id TEXT PRIMARY KEY, \\ created TEXT DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')), \\ updated TEXT DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')), \\ occurred TEXT NOT NULL, \\ event TEXT NOT NULL, \\ resource_id TEXT NOT NULL, \\ resource TEXT NOT NULL DEFAULT '{}', \\ related_resource_ids TEXT DEFAULT '[]', \\ related TEXT DEFAULT '[]', \\ payload TEXT DEFAULT '{}', \\ received TEXT NOT NULL, \\ recorded TEXT NOT NULL, \\ follows TEXT \\) , .{}); // block_type table try backend.db.exec( \\CREATE TABLE IF NOT EXISTS block_type ( \\ id TEXT PRIMARY KEY, \\ created TEXT DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')), \\ updated TEXT DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', '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 \\) , .{}); // block_schema table try backend.db.exec( \\CREATE TABLE IF NOT EXISTS block_schema ( \\ id TEXT PRIMARY KEY, \\ created TEXT DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')), \\ updated TEXT DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')), \\ checksum TEXT NOT NULL, \\ fields TEXT NOT NULL DEFAULT '{}', \\ capabilities TEXT NOT NULL DEFAULT '[]', \\ version TEXT NOT NULL DEFAULT '1', \\ block_type_id TEXT NOT NULL REFERENCES block_type(id) ON DELETE CASCADE, \\ UNIQUE(checksum, version) \\) , .{}); // block_document table try backend.db.exec( \\CREATE TABLE IF NOT EXISTS block_document ( \\ id TEXT PRIMARY KEY, \\ created TEXT DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')), \\ updated TEXT DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')), \\ name TEXT, \\ data TEXT NOT NULL DEFAULT '{}', \\ is_anonymous INTEGER DEFAULT 0, \\ block_type_id TEXT NOT NULL REFERENCES block_type(id), \\ block_type_name TEXT, \\ block_schema_id TEXT NOT NULL REFERENCES block_schema(id), \\ UNIQUE(block_type_id, name) \\) , .{}); // variable table try backend.db.exec( \\CREATE TABLE IF NOT EXISTS variable ( \\ id TEXT PRIMARY KEY, \\ created TEXT DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')), \\ updated TEXT DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')), \\ name TEXT NOT NULL UNIQUE, \\ value TEXT DEFAULT 'null', \\ tags TEXT DEFAULT '[]' \\) , .{}); // filters is the JSON list of python's `SavedSearchFilter` objects try backend.db.exec( \\CREATE TABLE IF NOT EXISTS saved_search ( \\ id TEXT PRIMARY KEY, \\ created TEXT DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')), \\ updated TEXT DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')), \\ name TEXT NOT NULL UNIQUE, \\ filters TEXT NOT NULL DEFAULT '[]' \\) , .{}); // generic singleton-key-value store for server configuration. python uses // an equivalent `configuration` table (prefect.server.models.configuration) // for things like `server-default-result-storage`. value is JSON text. try backend.db.exec( \\CREATE TABLE IF NOT EXISTS configuration ( \\ id TEXT PRIMARY KEY, \\ created TEXT DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')), \\ updated TEXT DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')), \\ key TEXT NOT NULL UNIQUE, \\ value TEXT NOT NULL \\) , .{}); // work_pool table try backend.db.exec( \\CREATE TABLE IF NOT EXISTS work_pool ( \\ id TEXT PRIMARY KEY, \\ created TEXT NOT NULL, \\ updated TEXT NOT NULL, \\ name TEXT NOT NULL UNIQUE, \\ description TEXT, \\ type TEXT NOT NULL DEFAULT 'process', \\ base_job_template TEXT DEFAULT '{}', \\ is_paused INTEGER DEFAULT 0, \\ concurrency_limit INTEGER, \\ default_queue_id TEXT, \\ status TEXT DEFAULT 'NOT_READY' \\) , .{}); // work_queue table try backend.db.exec( \\CREATE TABLE IF NOT EXISTS work_queue ( \\ id TEXT PRIMARY KEY, \\ created TEXT NOT NULL, \\ updated TEXT NOT NULL, \\ name TEXT NOT NULL, \\ description TEXT DEFAULT '', \\ is_paused INTEGER DEFAULT 0, \\ concurrency_limit INTEGER, \\ priority INTEGER DEFAULT 1, \\ work_pool_id TEXT NOT NULL REFERENCES work_pool(id) ON DELETE CASCADE, \\ last_polled TEXT, \\ status TEXT DEFAULT 'NOT_READY', \\ UNIQUE(work_pool_id, name) \\) , .{}); // worker table try backend.db.exec( \\CREATE TABLE IF NOT EXISTS worker ( \\ id TEXT PRIMARY KEY, \\ created TEXT NOT NULL, \\ updated TEXT NOT NULL, \\ name TEXT NOT NULL, \\ work_pool_id TEXT NOT NULL REFERENCES work_pool(id) ON DELETE CASCADE, \\ last_heartbeat_time TEXT, \\ heartbeat_interval_seconds INTEGER, \\ status TEXT DEFAULT 'OFFLINE', \\ UNIQUE(work_pool_id, name) \\) , .{}); // deployment table try backend.db.exec( \\CREATE TABLE IF NOT EXISTS deployment ( \\ id TEXT PRIMARY KEY, \\ created TEXT NOT NULL, \\ updated TEXT NOT NULL, \\ name TEXT NOT NULL, \\ flow_id TEXT NOT NULL REFERENCES flow(id) ON DELETE CASCADE, \\ version TEXT, \\ description TEXT, \\ paused INTEGER DEFAULT 0, \\ status TEXT DEFAULT 'NOT_READY', \\ last_polled TEXT, \\ parameters TEXT DEFAULT '{}', \\ parameter_openapi_schema TEXT, \\ enforce_parameter_schema INTEGER DEFAULT 1, \\ tags TEXT DEFAULT '[]', \\ labels TEXT DEFAULT '{}', \\ path TEXT, \\ entrypoint TEXT, \\ job_variables TEXT DEFAULT '{}', \\ pull_steps TEXT, \\ work_pool_name TEXT, \\ work_queue_name TEXT, \\ work_queue_id TEXT REFERENCES work_queue(id) ON DELETE SET NULL, \\ storage_document_id TEXT, \\ infrastructure_document_id TEXT, \\ concurrency_limit_id TEXT REFERENCES concurrency_limit(id) ON DELETE SET NULL, \\ concurrency_options TEXT, \\ UNIQUE(flow_id, name) \\) , .{}); // Existing local DBs predate deployment.concurrency_options. Keep schema // init idempotent without introducing a migration framework here. // // Note the asymmetry with postgres: `schema/postgres.zig` reconciles // missing FOREIGN KEY constraints at startup, but sqlite has no // `ALTER TABLE ... ADD CONSTRAINT` — adding one means rebuilding the table // and copying the rows. A sqlite database created before these FKs were // declared therefore keeps its constraint-free tables, and relies on the // orphan sweeps in `services/db_vacuum.zig` the same way postgres does // before its reconcile lands. Fresh sqlite databases are fine: the FKs are // inline in the CREATE TABLE statements and backend.zig enables // `PRAGMA foreign_keys=ON` per connection. if (!(try hasColumn("deployment", "concurrency_options"))) { try backend.db.exec("ALTER TABLE deployment ADD COLUMN concurrency_options TEXT", .{}); } // deployment_schedule table try backend.db.exec( \\CREATE TABLE IF NOT EXISTS deployment_schedule ( \\ id TEXT PRIMARY KEY, \\ created TEXT NOT NULL, \\ updated TEXT NOT NULL, \\ deployment_id TEXT NOT NULL REFERENCES deployment(id) ON DELETE CASCADE, \\ schedule TEXT NOT NULL, \\ active INTEGER DEFAULT 1, \\ max_scheduled_runs INTEGER, \\ parameters TEXT DEFAULT '{}', \\ slug TEXT, \\ UNIQUE(deployment_id, slug) \\) , .{}); // concurrency_limit table (v2 only - no v1 support) try backend.db.exec( \\CREATE TABLE IF NOT EXISTS concurrency_limit ( \\ id TEXT PRIMARY KEY, \\ created TEXT DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')), \\ updated TEXT DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')), \\ name TEXT NOT NULL UNIQUE, \\ "limit" INTEGER NOT NULL, \\ active INTEGER DEFAULT 1, \\ active_slots INTEGER DEFAULT 0, \\ denied_slots INTEGER DEFAULT 0, \\ slot_decay_per_second REAL DEFAULT 0.0, \\ avg_slot_occupancy_seconds REAL DEFAULT 2.0 \\) , .{}); // log table — persistent log storage (previously in-memory) try backend.db.exec( \\CREATE TABLE IF NOT EXISTS log ( \\ id TEXT PRIMARY KEY, \\ created TEXT DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')), \\ updated TEXT DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')), \\ name TEXT NOT NULL, \\ level INTEGER NOT NULL, \\ message TEXT NOT NULL, \\ timestamp TEXT NOT NULL, \\ flow_run_id TEXT REFERENCES flow_run(id) ON DELETE CASCADE, \\ task_run_id TEXT \\) , .{}); // automation tables — event-driven triggers (EventTrigger + actions) try backend.db.exec( \\CREATE TABLE IF NOT EXISTS automation ( \\ id TEXT PRIMARY KEY, \\ created TEXT DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')), \\ updated TEXT DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')), \\ name TEXT NOT NULL, \\ description TEXT DEFAULT '', \\ enabled INTEGER DEFAULT 1, \\ tags TEXT DEFAULT '[]', \\ trigger TEXT NOT NULL, \\ actions TEXT NOT NULL, \\ actions_on_trigger TEXT DEFAULT '[]', \\ actions_on_resolve TEXT DEFAULT '[]' \\) , .{}); try backend.db.exec( \\CREATE TABLE IF NOT EXISTS automation_bucket ( \\ id TEXT PRIMARY KEY, \\ created TEXT DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')), \\ updated TEXT DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')), \\ automation_id TEXT NOT NULL REFERENCES automation(id) ON DELETE CASCADE, \\ trigger_id TEXT NOT NULL, \\ bucketing_key TEXT DEFAULT '[]', \\ start_time TEXT NOT NULL, \\ end_time TEXT NOT NULL, \\ count INTEGER DEFAULT 0, \\ last_event TEXT, \\ triggered_at TEXT, \\ UNIQUE(automation_id, trigger_id, bucketing_key) \\) , .{}); try backend.db.exec( \\CREATE TABLE IF NOT EXISTS composite_trigger_child_firing ( \\ id TEXT PRIMARY KEY, \\ created TEXT DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')), \\ updated TEXT DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')), \\ automation_id TEXT NOT NULL REFERENCES automation(id) ON DELETE CASCADE, \\ parent_trigger_id TEXT NOT NULL, \\ child_trigger_id TEXT NOT NULL, \\ child_fired_at TEXT NOT NULL, \\ UNIQUE(automation_id, parent_trigger_id, child_trigger_id) \\) , .{}); try backend.db.exec( \\CREATE TABLE IF NOT EXISTS automation_related_resource ( \\ id TEXT PRIMARY KEY, \\ created TEXT DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')), \\ updated TEXT DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')), \\ automation_id TEXT 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 TEXT PRIMARY KEY, \\ created TEXT DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')), \\ updated TEXT DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')), \\ key TEXT, \\ task_run_id TEXT, \\ flow_run_id TEXT, \\ type TEXT, \\ data TEXT, \\ description TEXT, \\ metadata_ TEXT \\) , .{}); try backend.db.exec( \\CREATE TABLE IF NOT EXISTS artifact_collection ( \\ id TEXT PRIMARY KEY, \\ created TEXT DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')), \\ updated TEXT DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')), \\ key TEXT NOT NULL UNIQUE, \\ latest_id TEXT NOT NULL, \\ task_run_id TEXT, \\ flow_run_id TEXT, \\ type TEXT, \\ data TEXT, \\ description TEXT, \\ metadata_ TEXT \\) , .{}); // 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 INDEX IF NOT EXISTS ix_flow_run_state__flow_run_id ON flow_run_state(flow_run_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 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)", .{}); log.info("database", "sqlite schema initialized", .{}); } test "column probes distinguish absent columns from query failures" { backend.db = try backend.Backend.initSqlite(std.testing.allocator, ":memory:"); defer backend.db.deinit(); try backend.db.exec("CREATE TABLE probe (id TEXT)", .{}); try std.testing.expect(try hasColumn("probe", "id")); try std.testing.expect(!try hasColumn("probe", "missing")); try std.testing.expect(!try hasColumn("missing_table", "id")); try std.testing.expectError(error.Error, hasColumn("invalid table syntax", "id")); }