checkbot/app/migrations.py

MIGRATIONS = [
    """
    CREATE TABLE settings (
        key TEXT PRIMARY KEY,
        value TEXT NOT NULL,
        is_secret INTEGER NOT NULL DEFAULT 0,
        updated_at INTEGER NOT NULL
    );
    CREATE TABLE audit (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        ts INTEGER NOT NULL,
        user TEXT NOT NULL,
        action TEXT NOT NULL,
        target TEXT NOT NULL,
        old_value TEXT,
        new_value TEXT
    );
    CREATE INDEX audit_ts ON audit(ts);
    """,
    """
    CREATE TABLE users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        username TEXT NOT NULL UNIQUE COLLATE NOCASE,
        password_hash TEXT NOT NULL,
        role TEXT NOT NULL CHECK (role IN ('admin', 'viewer')),
        totp_secret TEXT,
        session_version INTEGER NOT NULL DEFAULT 0,
        disabled INTEGER NOT NULL DEFAULT 0,
        created_at INTEGER NOT NULL
    );
    """,
    """
    CREATE TABLE service_states (
        host TEXT NOT NULL,
        service TEXT NOT NULL,
        state INTEGER NOT NULL,
        output TEXT NOT NULL DEFAULT '',
        last_state_change INTEGER NOT NULL,
        in_downtime INTEGER NOT NULL DEFAULT 0,
        flapping INTEGER NOT NULL DEFAULT 0,
        reported_state INTEGER NOT NULL DEFAULT 0,
        flap_reported INTEGER NOT NULL DEFAULT 0,
        PRIMARY KEY (host, service)
    );
    CREATE TABLE state_log (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        host TEXT NOT NULL,
        service TEXT NOT NULL,
        state INTEGER NOT NULL,
        ts INTEGER NOT NULL
    );
    CREATE INDEX state_log_lookup ON state_log(host, service, ts);
    CREATE TABLE alerts (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        ts INTEGER NOT NULL,
        host TEXT NOT NULL,
        service TEXT NOT NULL,
        kind TEXT NOT NULL,
        state INTEGER NOT NULL,
        output TEXT NOT NULL DEFAULT '',
        suppressed INTEGER NOT NULL DEFAULT 0,
        suppress_reason TEXT,
        ai_status TEXT NOT NULL DEFAULT 'skipped'
            CHECK (ai_status IN ('pending', 'done', 'failed', 'skipped')),
        ai_text TEXT,
        message_group TEXT NOT NULL
    );
    CREATE INDEX alerts_ts ON alerts(ts);
    CREATE INDEX alerts_group ON alerts(message_group);
    CREATE TABLE deliveries (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        alert_id INTEGER NOT NULL REFERENCES alerts(id) ON DELETE CASCADE,
        backend TEXT NOT NULL,
        target TEXT NOT NULL,
        message_id TEXT NOT NULL
    );
    CREATE INDEX deliveries_alert ON deliveries(alert_id);
    """,
    """
    CREATE TABLE routes (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        position INTEGER NOT NULL,
        enabled INTEGER NOT NULL DEFAULT 1,
        match_host TEXT NOT NULL DEFAULT '',
        match_service TEXT NOT NULL DEFAULT '',
        min_state INTEGER NOT NULL DEFAULT 1 CHECK (min_state BETWEEN 0 AND 3),
        kinds TEXT NOT NULL DEFAULT 'problem,recovery,flapping',
        stop_on_match INTEGER NOT NULL DEFAULT 0,
        targets TEXT NOT NULL DEFAULT '[]'
    );
    CREATE INDEX routes_position ON routes(position);
    CREATE TABLE mutes (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        host_glob TEXT NOT NULL,
        service_glob TEXT NOT NULL,
        expires_at INTEGER NOT NULL,
        reason TEXT NOT NULL DEFAULT '',
        created_by TEXT NOT NULL,
        created_at INTEGER NOT NULL,
        source TEXT NOT NULL CHECK (source IN ('ui', 'chat'))
    );
    CREATE INDEX mutes_expires ON mutes(expires_at);
    """,
    """
    CREATE TABLE agent_runs (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        ts INTEGER NOT NULL,
        backend TEXT NOT NULL,
        sender TEXT NOT NULL,
        command TEXT NOT NULL,
        question TEXT NOT NULL,
        answer TEXT NOT NULL,
        abort_reason TEXT,
        steps TEXT NOT NULL DEFAULT '[]'
    );
    CREATE INDEX agent_runs_ts ON agent_runs(ts);
    CREATE TABLE listener_state (
        key TEXT PRIMARY KEY,
        value TEXT NOT NULL
    );
    """,
    """
    CREATE TABLE prompt_versions (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        version INTEGER NOT NULL,
        text TEXT NOT NULL,
        note TEXT NOT NULL DEFAULT '',
        created_by TEXT NOT NULL,
        created_at INTEGER NOT NULL,
        UNIQUE (name, version)
    );
    CREATE TABLE prompt_active (
        name TEXT PRIMARY KEY,
        version INTEGER NOT NULL
    );
    CREATE TABLE retention_state (
        id INTEGER PRIMARY KEY CHECK (id = 1),
        ts INTEGER NOT NULL,
        origin TEXT NOT NULL,
        counts TEXT NOT NULL
    );
    CREATE INDEX state_log_ts ON state_log(ts);
    """,
    """
    ALTER TABLE service_states ADD COLUMN problem_since INTEGER;
    ALTER TABLE service_states ADD COLUMN reported_at INTEGER NOT NULL DEFAULT 0;
    ALTER TABLE service_states ADD COLUMN reported_since INTEGER NOT NULL DEFAULT 0;
    ALTER TABLE service_states ADD COLUMN last_reminder_ts INTEGER NOT NULL DEFAULT 0;
    UPDATE service_states SET reported_at = CAST(strftime('%s', 'now') AS INTEGER),
        reported_since = last_state_change WHERE reported_state != 0;
    ALTER TABLE routes ADD COLUMN delay_minutes INTEGER;
    ALTER TABLE routes ADD COLUMN output_contains TEXT NOT NULL DEFAULT '';
    ALTER TABLE routes ADD COLUMN ai_enabled INTEGER NOT NULL DEFAULT 1;
    ALTER TABLE routes ADD COLUMN ai_hint TEXT NOT NULL DEFAULT '';
    ALTER TABLE routes ADD COLUMN reminder_hours INTEGER;
    ALTER TABLE routes ADD COLUMN loud INTEGER NOT NULL DEFAULT 1;
    ALTER TABLE routes ADD COLUMN use_default_targets INTEGER NOT NULL DEFAULT 0;
    CREATE TABLE app_state (
        key TEXT PRIMARY KEY,
        value TEXT NOT NULL
    );
    """,
    """
    ALTER TABLE service_states ADD COLUMN ack_state INTEGER;
    ALTER TABLE service_states ADD COLUMN host_held INTEGER NOT NULL DEFAULT 0;
    INSERT OR IGNORE INTO app_state(key, value)
        SELECT 'baseline.done', '1' WHERE EXISTS (SELECT 1 FROM service_states);
    """,
    """
    UPDATE routes SET reminder_hours = NULL WHERE reminder_hours = 0;
    """,
    """
    CREATE TABLE selftest_state (
        id INTEGER PRIMARY KEY CHECK (id = 1),
        ts INTEGER NOT NULL,
        version TEXT,
        checks TEXT NOT NULL
    );
    """,
]