orchard

 1-- The schema this app inherited: the two original migrations concatenated
 2-- verbatim. openDB has to accept a database of this shape without a migration;
 3-- see TestSchemaAcceptsLegacyDatabase.
 4
 5-- A "property" is a tracked URL: the unit of monitoring. The original
 6-- schema had per-user properties; the rust port collapses to single-operator
 7-- so user_id is gone, but the rest of the columns line up 1:1 (preserving
 8-- UUIDs lets prior public status URLs keep working after migration).
 9CREATE TABLE properties (
10    id                          BLOB PRIMARY KEY,
11    url                         TEXT NOT NULL,
12    is_public                   INTEGER NOT NULL DEFAULT 0,
13    is_protected                INTEGER NOT NULL DEFAULT 0,
14
15    last_run_at                 INTEGER,
16    next_run_at                 INTEGER,
17
18    last_run_at_crawler         INTEGER,
19    next_run_at_crawler         INTEGER,
20    crawler_insights            TEXT,
21    crawl_state                 TEXT NOT NULL DEFAULT 'idle',
22    crawl_started_at            INTEGER,
23    last_crawl_success_at       INTEGER,
24    last_crawl_error            TEXT,
25    last_crawl_duration_ms      INTEGER,
26    last_crawl_pages_count      INTEGER,
27
28    lighthouse_scores           TEXT,
29    lighthouse_details          TEXT,
30    last_lighthouse_run_at      INTEGER,
31    last_lighthouse_success_at  INTEGER,
32    last_lighthouse_error       TEXT,
33    last_lighthouse_duration_ms INTEGER,
34    next_lighthouse_run_at      INTEGER,
35    lighthouse_state            TEXT NOT NULL DEFAULT 'idle',
36    lighthouse_started_at       INTEGER,
37
38    -- alert state machine: "up" or "down". Transitions only fire alerts.
39    alert_state                 TEXT NOT NULL DEFAULT 'up',
40    last_alert_sent             INTEGER,
41
42    created_at                  INTEGER NOT NULL,
43    updated_at                  INTEGER NOT NULL
44);
45CREATE INDEX properties_url ON properties(url);
46
47-- Result of a single HTTP check. Cleaned up after 3 days by the scheduler.
48CREATE TABLE checks (
49    id           INTEGER PRIMARY KEY AUTOINCREMENT,
50    property_id  BLOB NOT NULL REFERENCES properties(id) ON DELETE CASCADE,
51    status_code  INTEGER NOT NULL,
52    response_ms  INTEGER NOT NULL DEFAULT 0,
53    headers      TEXT NOT NULL DEFAULT '{}',
54    created_at   INTEGER NOT NULL
55);
56CREATE INDEX checks_created_at         ON checks(created_at);
57CREATE INDEX checks_property_created   ON checks(property_id, created_at DESC);
58
59-- Key-value table for one-off settings (schema version, etc.).
60CREATE TABLE meta (
61    key   TEXT PRIMARY KEY,
62    value TEXT NOT NULL
63);
64-- Per-phase timing breakdown for HTTP checks. Pre-existing rows leave
65-- these NULL; the dashboard chart skips nulls. response_ms remains the
66-- canonical total (alert email avg still reads it).
67ALTER TABLE checks ADD COLUMN dns_ms  INTEGER;
68ALTER TABLE checks ADD COLUMN tcp_ms  INTEGER;
69ALTER TABLE checks ADD COLUMN tls_ms  INTEGER;
70ALTER TABLE checks ADD COLUMN ttfb_ms INTEGER;