AkurAI-DNS / src/schema.rs

Latest change 1f985ee57c4d7762e35f4e0bd2fbbfe716bf42ee - feat(domains): audited domain lifecycle - certificate-ready state, transitions, events, customer-safe status (AKURAI-DNS-4) by agent

use rusqlite::Connection;
use rusqlite::Result;

/// Run all schema migrations idempotently.
pub fn migrate(conn: &Connection) -> Result<()> {
    // Base tables for a fresh install. State is validated in Rust
    // (DomainState::from_str) rather than a SQL CHECK constraint, so new
    // lifecycle states never require a destructive schema change.
    conn.execute_batch(
        "CREATE TABLE IF NOT EXISTS domain_ownerships (
            id              INTEGER PRIMARY KEY AUTOINCREMENT,
            org_id          TEXT NOT NULL,
            workspace_id    TEXT NOT NULL,
            domain          TEXT NOT NULL UNIQUE,
            state           TEXT NOT NULL DEFAULT 'requested',
            verification_token TEXT,
            verified_at     TEXT,
            provisioned_at  TEXT,
            cert_ready_at   TEXT,
            expires_at      TEXT,
            last_error      TEXT,
            created_at      TEXT NOT NULL DEFAULT (datetime('now')),
            updated_at      TEXT NOT NULL DEFAULT (datetime('now'))
        );

        CREATE INDEX IF NOT EXISTS idx_domain_ownerships_workspace
            ON domain_ownerships(org_id, workspace_id);

        CREATE TABLE IF NOT EXISTS domain_lifecycle_events (
            id           INTEGER PRIMARY KEY AUTOINCREMENT,
            domain_id    INTEGER NOT NULL,
            org_id       TEXT NOT NULL,
            workspace_id TEXT NOT NULL,
            from_state   TEXT,
            to_state     TEXT NOT NULL,
            detail       TEXT,
            occurred_at  TEXT NOT NULL DEFAULT (datetime('now'))
        );

        CREATE INDEX IF NOT EXISTS idx_domain_lifecycle_events_domain
            ON domain_lifecycle_events(domain_id);

        CREATE TABLE IF NOT EXISTS domain_retry_idempotency (
            domain_id       INTEGER NOT NULL,
            idempotency_key TEXT NOT NULL,
            created_at      TEXT NOT NULL DEFAULT (datetime('now')),
            PRIMARY KEY (domain_id, idempotency_key)
        );",
    )?;

    // Additive migration for installs created before this column set existed
    // (AKURAI-DNS-5 shipped a stricter CHECK(state IN (...)) constraint that
    // rejects any state this task adds, e.g. 'certificate_ready'). Detect via
    // cert_ready_at column presence and rebuild domain_ownerships without the
    // CHECK constraint while preserving every existing row; SQLite cannot
    // ALTER a CHECK constraint in place.
    let has_cert_ready_at: bool = conn
        .prepare(
            "SELECT 1 FROM pragma_table_info('domain_ownerships') WHERE name = 'cert_ready_at'",
        )?
        .exists([])?;
    if !has_cert_ready_at {
        conn.execute_batch(
            "ALTER TABLE domain_ownerships RENAME TO domain_ownerships_old;
             CREATE TABLE domain_ownerships (
                id              INTEGER PRIMARY KEY AUTOINCREMENT,
                org_id          TEXT NOT NULL,
                workspace_id    TEXT NOT NULL,
                domain          TEXT NOT NULL UNIQUE,
                state           TEXT NOT NULL DEFAULT 'requested',
                verification_token TEXT,
                verified_at     TEXT,
                provisioned_at  TEXT,
                cert_ready_at   TEXT,
                expires_at      TEXT,
                last_error      TEXT,
                created_at      TEXT NOT NULL DEFAULT (datetime('now')),
                updated_at      TEXT NOT NULL DEFAULT (datetime('now'))
             );
             INSERT INTO domain_ownerships
                (id, org_id, workspace_id, domain, state, verification_token,
                 verified_at, provisioned_at, expires_at, created_at, updated_at)
             SELECT id, org_id, workspace_id, domain, state, verification_token,
                 verified_at, provisioned_at, expires_at, created_at, updated_at
             FROM domain_ownerships_old;
             DROP TABLE domain_ownerships_old;
             CREATE INDEX IF NOT EXISTS idx_domain_ownerships_workspace
                ON domain_ownerships(org_id, workspace_id);",
        )?;
    }

    Ok(())
}

#[cfg(test)]
mod tests {
    use super::*;

    #[test]
    fn migration_is_idempotent() {
        let conn = Connection::open_in_memory().unwrap();
        migrate(&conn).unwrap();
        migrate(&conn).unwrap(); // second call must not fail
    }

    #[test]
    fn schema_has_expected_columns() {
        let conn = Connection::open_in_memory().unwrap();
        migrate(&conn).unwrap();
        let stmt = conn
            .prepare("SELECT id, org_id, workspace_id, domain, state, verification_token, verified_at, provisioned_at, cert_ready_at, expires_at, last_error, created_at, updated_at FROM domain_ownerships")
            .unwrap();
        let cols: Vec<String> = stmt.column_names().iter().map(|c| c.to_string()).collect();
        assert!(cols.contains(&"org_id".into()));
        assert!(cols.contains(&"workspace_id".into()));
        assert!(cols.contains(&"domain".into()));
        assert!(cols.contains(&"state".into()));
        assert!(cols.contains(&"cert_ready_at".into()));
        assert!(cols.contains(&"last_error".into()));
    }

    /// A pre-existing DNS-5 install (CHECK(state IN (...)) constraint, no
    /// cert_ready_at column) must migrate cleanly to the new schema and
    /// preserve every existing row, then accept a state the old CHECK would
    /// have rejected.
    #[test]
    fn migrates_a_pre_existing_dns5_install_preserving_rows() {
        let conn = Connection::open_in_memory().unwrap();
        conn.execute_batch(
            "CREATE TABLE domain_ownerships (
                id              INTEGER PRIMARY KEY AUTOINCREMENT,
                org_id          TEXT NOT NULL,
                workspace_id    TEXT NOT NULL,
                domain          TEXT NOT NULL UNIQUE,
                state           TEXT NOT NULL DEFAULT 'requested'
                                CHECK(state IN (
                                    'requested','verifying','provisioned',
                                    'renewing','failed','retired'
                                )),
                verification_token TEXT,
                verified_at     TEXT,
                provisioned_at  TEXT,
                expires_at      TEXT,
                created_at      TEXT NOT NULL DEFAULT (datetime('now')),
                updated_at      TEXT NOT NULL DEFAULT (datetime('now'))
            );
            INSERT INTO domain_ownerships (org_id, workspace_id, domain, state)
            VALUES ('org-1', 'ws-1', 'example.com', 'provisioned');",
        )
        .unwrap();

        migrate(&conn).unwrap();

        let (domain, state): (String, String) = conn
            .query_row(
                "SELECT domain, state FROM domain_ownerships WHERE domain = 'example.com'",
                [],
                |row| Ok((row.get(0)?, row.get(1)?)),
            )
            .unwrap();
        assert_eq!(domain, "example.com");
        assert_eq!(state, "provisioned");

        // The old CHECK constraint is gone — a state it never allowed now inserts cleanly.
        conn.execute(
            "UPDATE domain_ownerships SET state = 'certificate_ready' WHERE domain = 'example.com'",
            [],
        )
        .unwrap();
    }
}