zzstoatzz.io
prefect server in zig
1const std = @import("std");
2const backend = @import("../backend.zig");
3const log = @import("../../logging.zig");
4
5/// Initialize SQLite schema
6pub fn init() !void {
7 // flow table
8 try backend.db.exec(
9 \\CREATE TABLE IF NOT EXISTS flow (
10 \\ id TEXT PRIMARY KEY,
11 \\ created TEXT DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')),
12 \\ updated TEXT DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')),
13 \\ name TEXT NOT NULL UNIQUE,
14 \\ tags TEXT DEFAULT '[]'
15 \\)
16 , .{});
17
18 // flow_run table
19 try backend.db.exec(
20 \\CREATE TABLE IF NOT EXISTS flow_run (
21 \\ id TEXT PRIMARY KEY,
22 \\ created TEXT DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')),
23 \\ updated TEXT DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')),
24 \\ flow_id TEXT REFERENCES flow(id),
25 \\ name TEXT NOT NULL,
26 \\ parameters TEXT DEFAULT '{}',
27 \\ tags TEXT DEFAULT '[]',
28 \\ state_id TEXT,
29 \\ state_type TEXT,
30 \\ state_name TEXT,
31 \\ state_timestamp TEXT,
32 \\ run_count INTEGER DEFAULT 0,
33 \\ expected_start_time TEXT,
34 \\ next_scheduled_start_time TEXT,
35 \\ start_time TEXT,
36 \\ end_time TEXT,
37 \\ total_run_time REAL DEFAULT 0.0,
38 \\ deployment_id TEXT,
39 \\ deployment_version TEXT,
40 \\ work_queue_name TEXT,
41 \\ work_queue_id TEXT,
42 \\ auto_scheduled INTEGER DEFAULT 0,
43 \\ idempotency_key TEXT,
44 \\ empirical_policy TEXT DEFAULT '{}'
45 \\)
46 , .{});
47
48 // flow_run_state table
49 try backend.db.exec(
50 \\CREATE TABLE IF NOT EXISTS flow_run_state (
51 \\ id TEXT PRIMARY KEY,
52 \\ created TEXT DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')),
53 \\ flow_run_id TEXT REFERENCES flow_run(id) ON DELETE CASCADE,
54 \\ type TEXT NOT NULL,
55 \\ name TEXT NOT NULL,
56 \\ timestamp TEXT DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))
57 \\)
58 , .{});
59
60 // task_run table
61 try backend.db.exec(
62 \\CREATE TABLE IF NOT EXISTS task_run (
63 \\ id TEXT PRIMARY KEY,
64 \\ created TEXT DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')),
65 \\ updated TEXT DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')),
66 \\ flow_run_id TEXT REFERENCES flow_run(id),
67 \\ name TEXT NOT NULL,
68 \\ task_key TEXT NOT NULL,
69 \\ dynamic_key TEXT NOT NULL,
70 \\ cache_key TEXT,
71 \\ tags TEXT DEFAULT '[]',
72 \\ state_id TEXT,
73 \\ state_type TEXT,
74 \\ state_name TEXT,
75 \\ state_timestamp TEXT,
76 \\ run_count INTEGER DEFAULT 0,
77 \\ expected_start_time TEXT,
78 \\ start_time TEXT,
79 \\ end_time TEXT,
80 \\ total_run_time REAL DEFAULT 0.0
81 \\)
82 , .{});
83
84 // task_run_state table
85 try backend.db.exec(
86 \\CREATE TABLE IF NOT EXISTS task_run_state (
87 \\ id TEXT PRIMARY KEY,
88 \\ created TEXT DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')),
89 \\ task_run_id TEXT REFERENCES task_run(id) ON DELETE CASCADE,
90 \\ type TEXT NOT NULL,
91 \\ name TEXT NOT NULL,
92 \\ timestamp TEXT DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))
93 \\)
94 , .{});
95
96 // events table
97 try backend.db.exec(
98 \\CREATE TABLE IF NOT EXISTS events (
99 \\ id TEXT PRIMARY KEY,
100 \\ created TEXT DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')),
101 \\ updated TEXT DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')),
102 \\ occurred TEXT NOT NULL,
103 \\ event TEXT NOT NULL,
104 \\ resource_id TEXT NOT NULL,
105 \\ resource TEXT NOT NULL DEFAULT '{}',
106 \\ related_resource_ids TEXT DEFAULT '[]',
107 \\ related TEXT DEFAULT '[]',
108 \\ payload TEXT DEFAULT '{}',
109 \\ received TEXT NOT NULL,
110 \\ recorded TEXT NOT NULL,
111 \\ follows TEXT
112 \\)
113 , .{});
114
115 // block_type table
116 try backend.db.exec(
117 \\CREATE TABLE IF NOT EXISTS block_type (
118 \\ id TEXT PRIMARY KEY,
119 \\ created TEXT DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')),
120 \\ updated TEXT DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')),
121 \\ name TEXT NOT NULL,
122 \\ slug TEXT NOT NULL UNIQUE,
123 \\ logo_url TEXT,
124 \\ documentation_url TEXT,
125 \\ description TEXT,
126 \\ code_example TEXT,
127 \\ is_protected INTEGER DEFAULT 0
128 \\)
129 , .{});
130
131 // block_schema table
132 try backend.db.exec(
133 \\CREATE TABLE IF NOT EXISTS block_schema (
134 \\ id TEXT PRIMARY KEY,
135 \\ created TEXT DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')),
136 \\ updated TEXT DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')),
137 \\ checksum TEXT NOT NULL,
138 \\ fields TEXT NOT NULL DEFAULT '{}',
139 \\ capabilities TEXT NOT NULL DEFAULT '[]',
140 \\ version TEXT NOT NULL DEFAULT '1',
141 \\ block_type_id TEXT NOT NULL REFERENCES block_type(id) ON DELETE CASCADE,
142 \\ UNIQUE(checksum, version)
143 \\)
144 , .{});
145
146 // block_document table
147 try backend.db.exec(
148 \\CREATE TABLE IF NOT EXISTS block_document (
149 \\ id TEXT PRIMARY KEY,
150 \\ created TEXT DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')),
151 \\ updated TEXT DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')),
152 \\ name TEXT,
153 \\ data TEXT NOT NULL DEFAULT '{}',
154 \\ is_anonymous INTEGER DEFAULT 0,
155 \\ block_type_id TEXT NOT NULL REFERENCES block_type(id),
156 \\ block_type_name TEXT,
157 \\ block_schema_id TEXT NOT NULL REFERENCES block_schema(id),
158 \\ UNIQUE(block_type_id, name)
159 \\)
160 , .{});
161
162 // variable table
163 try backend.db.exec(
164 \\CREATE TABLE IF NOT EXISTS variable (
165 \\ id TEXT PRIMARY KEY,
166 \\ created TEXT DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')),
167 \\ updated TEXT DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')),
168 \\ name TEXT NOT NULL UNIQUE,
169 \\ value TEXT DEFAULT 'null',
170 \\ tags TEXT DEFAULT '[]'
171 \\)
172 , .{});
173
174 // work_pool table
175 try backend.db.exec(
176 \\CREATE TABLE IF NOT EXISTS work_pool (
177 \\ id TEXT PRIMARY KEY,
178 \\ created TEXT NOT NULL,
179 \\ updated TEXT NOT NULL,
180 \\ name TEXT NOT NULL UNIQUE,
181 \\ description TEXT,
182 \\ type TEXT NOT NULL DEFAULT 'process',
183 \\ base_job_template TEXT DEFAULT '{}',
184 \\ is_paused INTEGER DEFAULT 0,
185 \\ concurrency_limit INTEGER,
186 \\ default_queue_id TEXT,
187 \\ status TEXT DEFAULT 'NOT_READY'
188 \\)
189 , .{});
190
191 // work_queue table
192 try backend.db.exec(
193 \\CREATE TABLE IF NOT EXISTS work_queue (
194 \\ id TEXT PRIMARY KEY,
195 \\ created TEXT NOT NULL,
196 \\ updated TEXT NOT NULL,
197 \\ name TEXT NOT NULL,
198 \\ description TEXT DEFAULT '',
199 \\ is_paused INTEGER DEFAULT 0,
200 \\ concurrency_limit INTEGER,
201 \\ priority INTEGER DEFAULT 1,
202 \\ work_pool_id TEXT NOT NULL REFERENCES work_pool(id) ON DELETE CASCADE,
203 \\ last_polled TEXT,
204 \\ status TEXT DEFAULT 'NOT_READY',
205 \\ UNIQUE(work_pool_id, name)
206 \\)
207 , .{});
208
209 // worker table
210 try backend.db.exec(
211 \\CREATE TABLE IF NOT EXISTS worker (
212 \\ id TEXT PRIMARY KEY,
213 \\ created TEXT NOT NULL,
214 \\ updated TEXT NOT NULL,
215 \\ name TEXT NOT NULL,
216 \\ work_pool_id TEXT NOT NULL REFERENCES work_pool(id) ON DELETE CASCADE,
217 \\ last_heartbeat_time TEXT,
218 \\ heartbeat_interval_seconds INTEGER,
219 \\ status TEXT DEFAULT 'OFFLINE',
220 \\ UNIQUE(work_pool_id, name)
221 \\)
222 , .{});
223
224 // deployment table
225 try backend.db.exec(
226 \\CREATE TABLE IF NOT EXISTS deployment (
227 \\ id TEXT PRIMARY KEY,
228 \\ created TEXT NOT NULL,
229 \\ updated TEXT NOT NULL,
230 \\ name TEXT NOT NULL,
231 \\ flow_id TEXT NOT NULL REFERENCES flow(id) ON DELETE CASCADE,
232 \\ version TEXT,
233 \\ description TEXT,
234 \\ paused INTEGER DEFAULT 0,
235 \\ status TEXT DEFAULT 'NOT_READY',
236 \\ last_polled TEXT,
237 \\ parameters TEXT DEFAULT '{}',
238 \\ parameter_openapi_schema TEXT,
239 \\ enforce_parameter_schema INTEGER DEFAULT 1,
240 \\ tags TEXT DEFAULT '[]',
241 \\ labels TEXT DEFAULT '{}',
242 \\ path TEXT,
243 \\ entrypoint TEXT,
244 \\ job_variables TEXT DEFAULT '{}',
245 \\ pull_steps TEXT,
246 \\ work_pool_name TEXT,
247 \\ work_queue_name TEXT,
248 \\ work_queue_id TEXT REFERENCES work_queue(id) ON DELETE SET NULL,
249 \\ storage_document_id TEXT,
250 \\ infrastructure_document_id TEXT,
251 \\ concurrency_limit INTEGER,
252 \\ UNIQUE(flow_id, name)
253 \\)
254 , .{});
255
256 // deployment_schedule table
257 try backend.db.exec(
258 \\CREATE TABLE IF NOT EXISTS deployment_schedule (
259 \\ id TEXT PRIMARY KEY,
260 \\ created TEXT NOT NULL,
261 \\ updated TEXT NOT NULL,
262 \\ deployment_id TEXT NOT NULL REFERENCES deployment(id) ON DELETE CASCADE,
263 \\ schedule TEXT NOT NULL,
264 \\ active INTEGER DEFAULT 1,
265 \\ max_scheduled_runs INTEGER,
266 \\ parameters TEXT DEFAULT '{}',
267 \\ slug TEXT,
268 \\ UNIQUE(deployment_id, slug)
269 \\)
270 , .{});
271
272 // concurrency_limit table (v2 only - no v1 support)
273 try backend.db.exec(
274 \\CREATE TABLE IF NOT EXISTS concurrency_limit (
275 \\ id TEXT PRIMARY KEY,
276 \\ created TEXT DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')),
277 \\ updated TEXT DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')),
278 \\ name TEXT NOT NULL UNIQUE,
279 \\ "limit" INTEGER NOT NULL,
280 \\ active INTEGER DEFAULT 1,
281 \\ active_slots INTEGER DEFAULT 0,
282 \\ denied_slots INTEGER DEFAULT 0,
283 \\ slot_decay_per_second REAL DEFAULT 0.0,
284 \\ avg_slot_occupancy_seconds REAL DEFAULT 2.0
285 \\)
286 , .{});
287
288 // indexes
289 try backend.db.exec("CREATE INDEX IF NOT EXISTS ix_flow_run__state_type ON flow_run(state_type)", .{});
290 try backend.db.exec("CREATE INDEX IF NOT EXISTS ix_flow_run__flow_id ON flow_run(flow_id)", .{});
291 try backend.db.exec("CREATE INDEX IF NOT EXISTS ix_flow_run__next_scheduled_start_time ON flow_run(next_scheduled_start_time)", .{});
292 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", .{});
293 try backend.db.exec("CREATE INDEX IF NOT EXISTS ix_flow_run_state__flow_run_id ON flow_run_state(flow_run_id)", .{});
294 try backend.db.exec("CREATE INDEX IF NOT EXISTS ix_task_run_state__task_run_id ON task_run_state(task_run_id)", .{});
295 try backend.db.exec("CREATE INDEX IF NOT EXISTS ix_task_run__flow_run_id ON task_run(flow_run_id)", .{});
296 try backend.db.exec("CREATE INDEX IF NOT EXISTS ix_task_run__task_key_dynamic_key ON task_run(task_key, dynamic_key)", .{});
297 try backend.db.exec("CREATE INDEX IF NOT EXISTS ix_events__occurred ON events(occurred)", .{});
298 try backend.db.exec("CREATE INDEX IF NOT EXISTS ix_events__event__id ON events(event, id)", .{});
299 try backend.db.exec("CREATE INDEX IF NOT EXISTS ix_events__event_resource_id_occurred ON events(event, resource_id, occurred)", .{});
300 try backend.db.exec("CREATE INDEX IF NOT EXISTS ix_events__occurred_id ON events(occurred, id)", .{});
301 try backend.db.exec("CREATE INDEX IF NOT EXISTS ix_block_schema__block_type_id ON block_schema(block_type_id)", .{});
302 try backend.db.exec("CREATE INDEX IF NOT EXISTS ix_block_schema__checksum ON block_schema(checksum)", .{});
303 try backend.db.exec("CREATE INDEX IF NOT EXISTS ix_block_document__block_type_id ON block_document(block_type_id)", .{});
304 try backend.db.exec("CREATE INDEX IF NOT EXISTS ix_block_document__block_schema_id ON block_document(block_schema_id)", .{});
305 try backend.db.exec("CREATE INDEX IF NOT EXISTS ix_block_document__name ON block_document(name)", .{});
306 try backend.db.exec("CREATE INDEX IF NOT EXISTS ix_variable__name ON variable(name)", .{});
307 try backend.db.exec("CREATE INDEX IF NOT EXISTS ix_work_pool__name ON work_pool(name)", .{});
308 try backend.db.exec("CREATE INDEX IF NOT EXISTS ix_work_pool__type ON work_pool(type)", .{});
309 try backend.db.exec("CREATE INDEX IF NOT EXISTS ix_work_queue__work_pool_id ON work_queue(work_pool_id)", .{});
310 try backend.db.exec("CREATE INDEX IF NOT EXISTS ix_work_queue__priority ON work_queue(priority)", .{});
311 try backend.db.exec("CREATE INDEX IF NOT EXISTS ix_worker__work_pool_id ON worker(work_pool_id)", .{});
312 try backend.db.exec("CREATE INDEX IF NOT EXISTS ix_flow_run__deployment_id ON flow_run(deployment_id)", .{});
313 try backend.db.exec("CREATE INDEX IF NOT EXISTS ix_deployment__flow_id ON deployment(flow_id)", .{});
314 try backend.db.exec("CREATE INDEX IF NOT EXISTS ix_deployment__work_queue_id ON deployment(work_queue_id)", .{});
315 try backend.db.exec("CREATE INDEX IF NOT EXISTS ix_deployment__work_pool_name ON deployment(work_pool_name)", .{});
316 try backend.db.exec("CREATE INDEX IF NOT EXISTS ix_deployment__created ON deployment(created)", .{});
317 try backend.db.exec("CREATE INDEX IF NOT EXISTS ix_deployment__updated ON deployment(updated)", .{});
318 try backend.db.exec("CREATE INDEX IF NOT EXISTS ix_deployment_schedule__deployment_id ON deployment_schedule(deployment_id)", .{});
319 try backend.db.exec("CREATE INDEX IF NOT EXISTS ix_deployment_schedule__active ON deployment_schedule(active)", .{});
320 try backend.db.exec("CREATE INDEX IF NOT EXISTS ix_concurrency_limit__name ON concurrency_limit(name)", .{});
321
322 log.info("database", "sqlite schema initialized", .{});
323}