Implement the Schema, Constraints, Indexes, Views, and Migrations

-- migrations/001_initial.sql-- Run only after connection initialization has enabled foreign_keys.BEGIN IMMEDIATE;CREATE TABLE site (    site_id     INTEGER PRIMARY KEY,    site_code   TEXT NOT NULL UNIQUE,    site_name   TEXT NOT NULL,    active      INTEGER NOT NULL DEFAULT 1 CHECK (active IN (0,1)),    created_at  TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP) STRICT;CREATE TABLE device (    device_id       INTEGER PRIMARY KEY,    site_id         INTEGER NOT NULL REFERENCES site(site_id),    device_code     TEXT NOT NULL UNIQUE,    device_name     TEXT NOT NULL,    status          TEXT NOT NULL DEFAULT 'active'                    CHECK (status IN ('active','inspection_due','retired')),    metadata_json   TEXT NOT NULL DEFAULT '{}'                    CHECK (json_valid(metadata_json)),    protocol        TEXT GENERATED ALWAYS AS                    (CASE WHEN json_valid(metadata_json)                          THEN json_extract(metadata_json,'$.protocol') END) STORED,    created_at      TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP) STRICT;CREATE TABLE inspection (    inspection_id      INTEGER PRIMARY KEY,    device_id          INTEGER NOT NULL REFERENCES device(device_id),    request_id         TEXT NOT NULL UNIQUE,    technician_name    TEXT NOT NULL,    started_at         TEXT NOT NULL,    finished_at        TEXT,    outcome            TEXT NOT NULL                       CHECK (outcome IN ('pass','follow_up','failed')),    summary            TEXT NOT NULL,    measurements_json  TEXT NOT NULL DEFAULT '{}'                       CHECK (json_valid(measurements_json)),    created_at         TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,    CHECK (finished_at IS NULL OR finished_at >= started_at)) STRICT;CREATE TABLE maintenance_note (    note_id        INTEGER PRIMARY KEY,    device_id      INTEGER NOT NULL REFERENCES device(device_id),    inspection_id  INTEGER REFERENCES inspection(inspection_id),    occurred_at    TEXT NOT NULL,    severity       TEXT NOT NULL DEFAULT 'info'                   CHECK (severity IN ('info','warning','critical')),    note_text      TEXT NOT NULL) STRICT;CREATE TABLE device_tag (    device_id  INTEGER NOT NULL REFERENCES device(device_id),    tag        TEXT NOT NULL,    PRIMARY KEY (device_id, tag)) STRICT, WITHOUT ROWID;CREATE TABLE sync_outbox (    event_id        TEXT PRIMARY KEY,    aggregate_type  TEXT NOT NULL CHECK (aggregate_type IN ('inspection','device')),    aggregate_id    INTEGER NOT NULL,    payload_json    TEXT NOT NULL CHECK (json_valid(payload_json)),    created_at      TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,    delivered_at    TEXT) STRICT, WITHOUT ROWID;CREATE INDEX idx_device_site_status    ON device(site_id, status);CREATE INDEX idx_device_protocol    ON device(protocol) WHERE protocol IS NOT NULL;CREATE INDEX idx_inspection_device_started    ON inspection(device_id, started_at DESC);CREATE INDEX idx_note_device_occurred    ON maintenance_note(device_id, occurred_at DESC);CREATE INDEX idx_outbox_pending    ON sync_outbox(created_at) WHERE delivered_at IS NULL;CREATE VIEW v_active_device ASSELECT s.site_code,       d.device_id,       d.device_code,       d.device_name,       d.status,       d.protocolFROM site AS sJOIN device AS d ON d.site_id = s.site_idWHERE s.active = 1  AND d.status <> 'retired';CREATE TRIGGER trg_critical_note_marks_dueAFTER INSERT ON maintenance_noteWHEN NEW.severity = 'critical'BEGIN    UPDATE device       SET status = 'inspection_due'     WHERE device_id = NEW.device_id       AND status = 'active';END;PRAGMA user_version = 1;COMMIT;

EXPLAIN QUERY PLANSELECT device_id, device_code, device_name, protocolFROM deviceWHERE site_id = 1  AND status = 'inspection_due'ORDER BY device_code;

EXPLAIN QUERY PLANSELECT inspection_id, started_at, outcome, summaryFROM inspectionWHERE device_id = ?ORDER BY started_at DESCLIMIT 20;

SELECT type, name, tbl_name, sqlFROM sqlite_schemaWHERE type IN ('view','trigger','index')ORDER BY type, name;

-- migrations/002_inspection_priority.sql-- Policy: migration 002 is applied once when user_version=1.BEGIN IMMEDIATE;ALTER TABLE inspectionADD COLUMN priority TEXT NOT NULL DEFAULT 'normal'CHECK (priority IN ('low','normal','high'));CREATE INDEX idx_inspection_open_priority    ON inspection(priority, started_at)    WHERE finished_at IS NULL;PRAGMA user_version = 2;COMMIT;

from pathlib import PathMIGRATIONS = {    1: Path("migrations/001_initial.sql"),    2: Path("migrations/002_inspection_priority.sql"),}TARGET_VERSION = max(MIGRATIONS)def migrate(con):    current = con.execute("PRAGMA user_version").fetchone()[0]    if current > TARGET_VERSION:        raise RuntimeError(f"database schema {current} is newer than app {TARGET_VERSION}")    for version in range(current + 1, TARGET_VERSION + 1):        sql = MIGRATIONS[version].read_text(encoding="utf-8")        # Each migration file owns its explicit BEGIN IMMEDIATE / COMMIT boundary.        try:            con.executescript(sql)        except sqlite3.Error:            if con.in_transaction:                con.execute("ROLLBACK")            raise        actual = con.execute("PRAGMA user_version").fetchone()[0]        if actual != version:            raise RuntimeError(f"migration {version} did not set user_version")

SELECT sqlite_version();PRAGMA user_version;PRAGMA foreign_keys;PRAGMA quick_check;PRAGMA foreign_key_check;SELECT type, name, tbl_nameFROM sqlite_schemaWHERE name NOT LIKE 'sqlite_%'ORDER BY type, name;SELECT name, "unique", origin, partialFROM pragma_index_list('inspection');-- Validate the generated JSON projection.SELECT device_code, protocol, json_valid(metadata_json)FROM deviceORDER BY device_id;

SELECT type, name, tbl_name, sqlFROM sqlite_schemaWHERE sql IS NOT NULL  AND name NOT LIKE 'sqlite_%'ORDER BY CASE type           WHEN 'table' THEN 1           WHEN 'index' THEN 2           WHEN 'view' THEN 3           WHEN 'trigger' THEN 4         END,         name;