Skip to content

armgit / ARES

Public Imported from GitHub
0

ARES - authorized red-team engagement automation with dashboard, campaign scope, module orchestration, OPSEC controls, encrypted vault, and reporting.

24.9 KB
Raw Blame
"""
ARES Database Schema - v5
SQLite WAL mode via aiosqlite. All credential/token content encrypted at rest.

Tables (v5 additions marked ★):
  schema_version  - migration tracking
  campaigns       - engagement metadata
  findings        - vulnerability findings with MITRE mapping
  hosts           - discovered hosts and services
  credentials     - captured attack credentials (encrypted)
  loot            - captured artifacts (hashes, tokens, files)
  audit_log       - append-only action log
  ★ users         - operator accounts (replaces in-memory dict)
  ★ api_keys      - long-lived API keys for CI/CD automation
  ★ refresh_tokens - JWT refresh tokens with expiry tracking
"""
from __future__ import annotations

from ares.db.execution_lifecycle import (
    sqlite_admission_authority_runtime_script,
    sqlite_lifecycle_runtime_script,
)

SCHEMA_VERSION = 9

CREATE_TABLES = """
PRAGMA journal_mode = WAL;
PRAGMA foreign_keys = ON;

CREATE TABLE IF NOT EXISTS schema_version (
    version     INTEGER PRIMARY KEY,
    applied_at  TEXT NOT NULL DEFAULT (datetime('now'))
);

-- ── Campaigns ────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS campaigns (
    id              TEXT PRIMARY KEY,
    name            TEXT NOT NULL,
    client          TEXT NOT NULL DEFAULT 'Internal',
    operator        TEXT NOT NULL DEFAULT 'unknown',
    noise_profile   TEXT NOT NULL DEFAULT 'stealth',
    status          TEXT NOT NULL DEFAULT 'created',
    scope_json      TEXT NOT NULL DEFAULT '[]',
    targets_json    TEXT NOT NULL DEFAULT '[]',
    notes           TEXT DEFAULT '',
    created_at      TEXT NOT NULL DEFAULT (datetime('now')),
    updated_at      TEXT NOT NULL DEFAULT (datetime('now'))
);

-- ── Findings ─────────────────────────────────────────────────────────────────
-- Module execution history (non-sensitive telemetry metadata)
CREATE TABLE IF NOT EXISTS module_runs (
    id              TEXT PRIMARY KEY,
    campaign_id     TEXT NOT NULL REFERENCES campaigns(id) ON DELETE CASCADE,
    module_id       TEXT NOT NULL,
    outcome         TEXT NOT NULL,
    success         INTEGER NOT NULL DEFAULT 0,
    duration_ms     REAL NOT NULL DEFAULT 0.0,
    completed_at    TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE INDEX IF NOT EXISTS idx_module_runs_campaign ON module_runs(campaign_id);
CREATE INDEX IF NOT EXISTS idx_module_runs_completed ON module_runs(completed_at);

CREATE TABLE IF NOT EXISTS findings (
    id              TEXT PRIMARY KEY,
    campaign_id     TEXT NOT NULL REFERENCES campaigns(id) ON DELETE CASCADE,
    module_id       TEXT NOT NULL,
    title           TEXT NOT NULL,
    description     TEXT NOT NULL,
    severity        TEXT NOT NULL,
    confidence      REAL NOT NULL DEFAULT 1.0,
    mitre_technique TEXT,
    mitre_tactic    TEXT,
    cvss_score      REAL NOT NULL DEFAULT 0.0,
    cvss_vector     TEXT NOT NULL DEFAULT '',
    trace_id        TEXT NOT NULL DEFAULT '',
    evidence_json   TEXT NOT NULL DEFAULT '{}',
    remediation     TEXT DEFAULT '',
    host            TEXT,
    validated       INTEGER NOT NULL DEFAULT 0,
    false_positive  INTEGER NOT NULL DEFAULT 0,
    discovered_at   TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE INDEX IF NOT EXISTS idx_findings_campaign   ON findings(campaign_id);
CREATE INDEX IF NOT EXISTS idx_findings_severity   ON findings(severity);
CREATE INDEX IF NOT EXISTS idx_findings_fp         ON findings(false_positive);
CREATE INDEX IF NOT EXISTS idx_findings_mitre      ON findings(mitre_technique);

-- ── Hosts ─────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS hosts (
    id              TEXT PRIMARY KEY,
    campaign_id     TEXT NOT NULL REFERENCES campaigns(id) ON DELETE CASCADE,
    ip_address      TEXT NOT NULL,
    hostname        TEXT,
    fqdn            TEXT,
    os              TEXT,
    os_version      TEXT,
    domain          TEXT,
    is_dc           INTEGER NOT NULL DEFAULT 0,
    open_ports_json TEXT NOT NULL DEFAULT '[]',
    tags_json       TEXT NOT NULL DEFAULT '[]',
    first_seen      TEXT NOT NULL DEFAULT (datetime('now')),
    last_seen       TEXT NOT NULL DEFAULT (datetime('now')),
    UNIQUE(campaign_id, ip_address)
);

CREATE INDEX IF NOT EXISTS idx_hosts_campaign  ON hosts(campaign_id);
CREATE INDEX IF NOT EXISTS idx_hosts_ip        ON hosts(ip_address);
CREATE INDEX IF NOT EXISTS idx_hosts_domain    ON hosts(domain);

-- ── Credentials ──────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS credentials (
    id              TEXT PRIMARY KEY,
    campaign_id     TEXT NOT NULL REFERENCES campaigns(id) ON DELETE CASCADE,
    host_id         TEXT REFERENCES hosts(id) ON DELETE SET NULL,
    username        TEXT NOT NULL,
    secret_enc      TEXT,
    cred_type       TEXT NOT NULL,
    domain          TEXT,
    source_module   TEXT,
    notes           TEXT DEFAULT '',
    cracked         INTEGER NOT NULL DEFAULT 0,
    cracked_value_enc TEXT,              -- Vault-encrypted cracked plaintext (AES-256-GCM v2). MUST be encrypted before writing; never store plaintext here.
    captured_at     TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE INDEX IF NOT EXISTS idx_creds_campaign  ON credentials(campaign_id);
CREATE INDEX IF NOT EXISTS idx_creds_username  ON credentials(username);
CREATE INDEX IF NOT EXISTS idx_creds_type      ON credentials(cred_type);

-- ── Loot ──────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS loot (
    id              TEXT PRIMARY KEY,
    campaign_id     TEXT NOT NULL REFERENCES campaigns(id) ON DELETE CASCADE,
    host_id         TEXT REFERENCES hosts(id) ON DELETE SET NULL,
    loot_type       TEXT NOT NULL,
    name            TEXT NOT NULL,
    description     TEXT DEFAULT '',
    content_enc     TEXT,
    size_bytes      INTEGER DEFAULT 0,
    path_on_target  TEXT,
    source_module   TEXT,
    tags_json       TEXT NOT NULL DEFAULT '[]',
    captured_at     TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE INDEX IF NOT EXISTS idx_loot_campaign   ON loot(campaign_id);
CREATE INDEX IF NOT EXISTS idx_loot_type       ON loot(loot_type);

-- ── Audit log ─────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS audit_log (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    campaign_id     TEXT REFERENCES campaigns(id) ON DELETE SET NULL,
    actor           TEXT NOT NULL DEFAULT 'system',
    action          TEXT NOT NULL,
    detail          TEXT DEFAULT '',
    module_id       TEXT,
    timestamp       TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE INDEX IF NOT EXISTS idx_audit_campaign  ON audit_log(campaign_id);
CREATE INDEX IF NOT EXISTS idx_audit_actor     ON audit_log(actor);
CREATE INDEX IF NOT EXISTS idx_audit_action    ON audit_log(action);

-- ── Users (★ v5) ─────────────────────────────────────────────────────────────
-- Replaces in-memory _users dict in server.py.
CREATE TABLE IF NOT EXISTS users (
    id              TEXT PRIMARY KEY,
    username        TEXT NOT NULL UNIQUE,
    hashed_password TEXT NOT NULL,
    role            TEXT NOT NULL DEFAULT 'reporter',
    is_active       INTEGER NOT NULL DEFAULT 1,
    created_by      TEXT NOT NULL DEFAULT 'system',
    created_at      TEXT NOT NULL DEFAULT (datetime('now')),
    last_login      TEXT,
    auth_epoch      INTEGER NOT NULL DEFAULT 1
                    CONSTRAINT ck_users_auth_epoch CHECK (auth_epoch >= 1)
);

CREATE INDEX IF NOT EXISTS idx_users_username  ON users(username);
CREATE INDEX IF NOT EXISTS idx_users_role      ON users(role);

-- ── API Keys (★ v5) ──────────────────────────────────────────────────────────
-- Long-lived keys for CI/CD automation (X-API-Key header).
-- key_hash is bcrypt hash of the actual key (only shown once on creation).
CREATE TABLE IF NOT EXISTS api_keys (
    id              TEXT PRIMARY KEY,
    user_id         TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    name            TEXT NOT NULL,
    key_hash        TEXT NOT NULL,
    key_prefix      TEXT NOT NULL,           -- first 8 chars shown for identification
    scopes          TEXT NOT NULL DEFAULT 'read',  -- read | write | admin
    is_active       INTEGER NOT NULL DEFAULT 1,
    last_used       TEXT,
    expires_at      TEXT,                    -- NULL = never expires
    created_at      TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE INDEX IF NOT EXISTS idx_apikeys_user    ON api_keys(user_id);
CREATE INDEX IF NOT EXISTS idx_apikeys_prefix  ON api_keys(key_prefix);

-- ── Refresh Tokens (★ v5) ────────────────────────────────────────────────────
-- Opaque tokens stored in DB; rotated on each use (rotation security).
CREATE TABLE IF NOT EXISTS refresh_token_families (
    id                  TEXT NOT NULL PRIMARY KEY
                        CONSTRAINT ck_refresh_family_id CHECK (
                            length(id)=43
                            AND id NOT GLOB '*[^A-Za-z0-9_-]*'
                        ),
    user_id             TEXT NOT NULL
                        CONSTRAINT fk_refresh_family_user
                        REFERENCES users(id) ON DELETE CASCADE,
    auth_epoch          INTEGER NOT NULL
                        CONSTRAINT ck_refresh_family_epoch CHECK (auth_epoch >= 1),
    state               TEXT NOT NULL
                        CONSTRAINT ck_refresh_family_state CHECK (
                            state IN ('active','revoked')
                        ),
    created_at          TEXT NOT NULL,
    absolute_expires_at TEXT NOT NULL,
    revoked_at          TEXT,
    revoke_reason       TEXT,
    retain_until        TEXT NOT NULL,
    CONSTRAINT uq_refresh_family_owner UNIQUE (id,user_id),
    CONSTRAINT ck_refresh_family_revocation_shape CHECK (
        (state='active' AND revoked_at IS NULL AND revoke_reason IS NULL)
        OR
        (state='revoked' AND revoked_at IS NOT NULL AND revoke_reason IS NOT NULL)
    ),
    CONSTRAINT ck_refresh_family_reason CHECK (
        revoke_reason IS NULL OR revoke_reason IN (
            'replay','logout_current','logout_all','password_change',
            'password_reset','role_change','user_status_change','rollout_reset',
            'expired','operator_revoke'
        )
    ),
    CONSTRAINT ck_refresh_family_created_at_utc CHECK (
        strftime('%Y-%m-%dT%H:%M:%fZ',created_at) IS NOT NULL
        AND strftime('%Y-%m-%dT%H:%M:%fZ',created_at)=created_at
    ),
    CONSTRAINT ck_refresh_family_absolute_expires_at_utc CHECK (
        strftime('%Y-%m-%dT%H:%M:%fZ',absolute_expires_at) IS NOT NULL
        AND strftime('%Y-%m-%dT%H:%M:%fZ',absolute_expires_at)=absolute_expires_at
    ),
    CONSTRAINT ck_refresh_family_revoked_at_utc CHECK (
        revoked_at IS NULL OR (
            strftime('%Y-%m-%dT%H:%M:%fZ',revoked_at) IS NOT NULL
            AND strftime('%Y-%m-%dT%H:%M:%fZ',revoked_at)=revoked_at
        )
    ),
    CONSTRAINT ck_refresh_family_retain_until_utc CHECK (
        strftime('%Y-%m-%dT%H:%M:%fZ',retain_until) IS NOT NULL
        AND strftime('%Y-%m-%dT%H:%M:%fZ',retain_until)=retain_until
    ),
    CONSTRAINT ck_refresh_family_expiry_order CHECK (
        absolute_expires_at > created_at
    ),
    CONSTRAINT ck_refresh_family_retention_order CHECK (
        retain_until > absolute_expires_at
        AND (revoked_at IS NULL OR retain_until > revoked_at)
    )
);

CREATE INDEX IF NOT EXISTS idx_refresh_family_user_state_exp
    ON refresh_token_families(user_id,state,absolute_expires_at);
CREATE INDEX IF NOT EXISTS idx_refresh_family_retain
    ON refresh_token_families(retain_until);

CREATE TABLE IF NOT EXISTS refresh_tokens (
    id              TEXT NOT NULL PRIMARY KEY
                    CONSTRAINT ck_refresh_token_hash CHECK (
                        length(id)=64 AND id NOT GLOB '*[^0-9a-f]*'
                    ),
    user_id         TEXT NOT NULL
                    CONSTRAINT fk_refresh_tokens_user
                    REFERENCES users(id) ON DELETE CASCADE,
    is_revoked      INTEGER NOT NULL DEFAULT 0,
    expires_at      TEXT NOT NULL,
    created_at      TEXT NOT NULL,
    used_at         TEXT,
    family_id       TEXT NOT NULL,
    parent_id       TEXT,
    generation      INTEGER NOT NULL,
    state           TEXT NOT NULL,
    revoked_at      TEXT,
    CONSTRAINT uq_refresh_token_family_hash UNIQUE (family_id,id),
    CONSTRAINT uq_refresh_token_family_generation UNIQUE (family_id,generation),
    CONSTRAINT uq_refresh_token_parent UNIQUE (parent_id),
    CONSTRAINT fk_refresh_token_family_owner
        FOREIGN KEY (family_id,user_id)
        REFERENCES refresh_token_families(id,user_id) ON DELETE CASCADE,
    CONSTRAINT fk_refresh_token_parent
        FOREIGN KEY (family_id,parent_id)
        REFERENCES refresh_tokens(family_id,id)
        ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
    CONSTRAINT ck_refresh_token_generation CHECK (generation >= 0),
    CONSTRAINT ck_refresh_token_parent_shape CHECK (
        (generation=0 AND parent_id IS NULL)
        OR (generation>0 AND parent_id IS NOT NULL)
    ),
    CONSTRAINT ck_refresh_token_state CHECK (
        state IN ('active','consumed','retired')
    ),
    CONSTRAINT ck_refresh_token_state_shape CHECK (
        (state='active' AND is_revoked=0 AND used_at IS NULL AND revoked_at IS NULL)
        OR
        (state='consumed' AND is_revoked=1 AND used_at IS NOT NULL AND revoked_at IS NULL)
        OR
        (state='retired' AND is_revoked=1 AND revoked_at IS NOT NULL)
    ),
    CONSTRAINT ck_refresh_token_created_utc CHECK (
        strftime('%Y-%m-%dT%H:%M:%fZ',created_at) IS NOT NULL
        AND strftime('%Y-%m-%dT%H:%M:%fZ',created_at)=created_at
    ),
    CONSTRAINT ck_refresh_token_expires_utc CHECK (
        strftime('%Y-%m-%dT%H:%M:%fZ',expires_at) IS NOT NULL
        AND strftime('%Y-%m-%dT%H:%M:%fZ',expires_at)=expires_at
    ),
    CONSTRAINT ck_refresh_token_used_utc CHECK (
        used_at IS NULL OR (
            strftime('%Y-%m-%dT%H:%M:%fZ',used_at) IS NOT NULL
            AND strftime('%Y-%m-%dT%H:%M:%fZ',used_at)=used_at
        )
    ),
    CONSTRAINT ck_refresh_token_revoked_utc CHECK (
        revoked_at IS NULL OR (
            strftime('%Y-%m-%dT%H:%M:%fZ',revoked_at) IS NOT NULL
            AND strftime('%Y-%m-%dT%H:%M:%fZ',revoked_at)=revoked_at
        )
    ),
    CONSTRAINT ck_refresh_token_expiry_order CHECK (expires_at > created_at)
);

CREATE INDEX IF NOT EXISTS idx_refresh_user    ON refresh_tokens(user_id);
CREATE INDEX IF NOT EXISTS idx_refresh_exp     ON refresh_tokens(expires_at);
CREATE UNIQUE INDEX IF NOT EXISTS uq_refresh_token_one_active
    ON refresh_tokens(family_id) WHERE state='active';
CREATE INDEX IF NOT EXISTS idx_refresh_family_generation
    ON refresh_tokens(family_id,generation);

-- Revoked access token JTIs - allows early revocation before natural expiry (60 min TTL)
CREATE TABLE IF NOT EXISTS revoked_access_tokens (
    jti         TEXT PRIMARY KEY,            -- JWT jti claim
    user_id     TEXT NOT NULL,
    revoked_at  TEXT NOT NULL DEFAULT (datetime('now')),
    expires_at  TEXT NOT NULL                -- auto-cleaned when past expiry
);
CREATE INDEX IF NOT EXISTS idx_rat_expires ON revoked_access_tokens(expires_at);

-- One-time, campaign-bound WebSocket handshake tickets.
CREATE TABLE IF NOT EXISTS websocket_tickets (
    ticket_hash       TEXT NOT NULL PRIMARY KEY
                      CONSTRAINT ck_ws_ticket_hash CHECK (
                          length(ticket_hash)=64
                          AND ticket_hash NOT GLOB '*[^0-9a-f]*'
                      ),
    campaign_id       TEXT NOT NULL
                      CONSTRAINT fk_ws_ticket_campaign
                      REFERENCES campaigns(id) ON DELETE CASCADE,
    user_id           TEXT NOT NULL
                      CONSTRAINT fk_ws_ticket_user
                      REFERENCES users(id) ON DELETE CASCADE,
    credential_kind   TEXT NOT NULL
                      CONSTRAINT ck_ws_ticket_kind CHECK (
                          credential_kind IN ('bearer', 'api_key')
                      ),
    bearer_subject    TEXT,
    bearer_jti        TEXT,
    bearer_expires_at TEXT,
    api_key_id        TEXT
                      CONSTRAINT fk_ws_ticket_api_key
                      REFERENCES api_keys(id) ON DELETE CASCADE,
    required_scope    TEXT,
    created_at        TEXT NOT NULL,
    expires_at        TEXT NOT NULL,
    consumed_at       TEXT,
    bearer_family_id  TEXT,
    bearer_auth_epoch INTEGER,
    CONSTRAINT ck_ws_ticket_created_at CHECK (
        strftime('%Y-%m-%dT%H:%M:%fZ', created_at) IS NOT NULL
        AND strftime('%Y-%m-%dT%H:%M:%fZ', created_at)=created_at
    ),
    CONSTRAINT ck_ws_ticket_expires_at CHECK (
        strftime('%Y-%m-%dT%H:%M:%fZ', expires_at) IS NOT NULL
        AND strftime('%Y-%m-%dT%H:%M:%fZ', expires_at)=expires_at
    ),
    CONSTRAINT ck_ws_ticket_consumed_at CHECK (
        consumed_at IS NULL OR (
            strftime('%Y-%m-%dT%H:%M:%fZ', consumed_at) IS NOT NULL
            AND strftime('%Y-%m-%dT%H:%M:%fZ', consumed_at)=consumed_at
        )
    ),
    CONSTRAINT ck_ws_ticket_time_order CHECK (
        julianday(expires_at) > julianday(created_at)
        AND (
            consumed_at IS NULL
            OR julianday(consumed_at) < julianday(expires_at)
        )
    ),
    CONSTRAINT ck_ws_ticket_source_shape CHECK (
        (
            credential_kind='bearer'
            AND bearer_subject IS NOT NULL
            AND length(trim(bearer_subject)) > 0
            AND bearer_subject=trim(bearer_subject)
            AND bearer_jti IS NOT NULL
            AND length(trim(bearer_jti)) > 0
            AND bearer_jti=trim(bearer_jti)
            AND bearer_expires_at IS NOT NULL
            AND strftime(
                '%Y-%m-%dT%H:%M:%fZ', bearer_expires_at
            ) IS NOT NULL
            AND strftime(
                '%Y-%m-%dT%H:%M:%fZ', bearer_expires_at
            )=bearer_expires_at
            AND bearer_family_id IS NOT NULL
            AND length(bearer_family_id)=43
            AND bearer_family_id NOT GLOB '*[^A-Za-z0-9_-]*'
            AND bearer_auth_epoch IS NOT NULL
            AND typeof(bearer_auth_epoch)='integer'
            AND bearer_auth_epoch >= 1
            AND api_key_id IS NULL
            AND required_scope IS NULL
        )
        OR
        (
            credential_kind='api_key'
            AND bearer_subject IS NULL
            AND bearer_jti IS NULL
            AND bearer_expires_at IS NULL
            AND bearer_family_id IS NULL
            AND bearer_auth_epoch IS NULL
            AND api_key_id IS NOT NULL
            AND length(trim(api_key_id)) > 0
            AND api_key_id=trim(api_key_id)
            AND required_scope='read'
        )
    ),
    CONSTRAINT fk_ws_ticket_bearer_family
        FOREIGN KEY (bearer_family_id,user_id)
        REFERENCES refresh_token_families(id,user_id) ON DELETE CASCADE
);
CREATE INDEX IF NOT EXISTS idx_ws_tickets_expires
    ON websocket_tickets(expires_at);
CREATE INDEX IF NOT EXISTS idx_ws_tickets_user
    ON websocket_tickets(user_id);
CREATE INDEX IF NOT EXISTS idx_ws_tickets_campaign
    ON websocket_tickets(campaign_id);
CREATE INDEX IF NOT EXISTS idx_ws_tickets_api_key
    ON websocket_tickets(api_key_id);
CREATE INDEX IF NOT EXISTS idx_ws_tickets_bearer_family
    ON websocket_tickets(bearer_family_id);

-- ── Organizations (★ Multi-tenant SSO) ───────────────────────────────────────
CREATE TABLE IF NOT EXISTS organizations (
    id          TEXT PRIMARY KEY,
    slug        TEXT NOT NULL UNIQUE,
    name        TEXT NOT NULL,
    is_active   INTEGER NOT NULL DEFAULT 1,
    created_at  TEXT NOT NULL DEFAULT (datetime('now'))
);
CREATE INDEX IF NOT EXISTS idx_organizations_slug ON organizations(slug);

-- ── SSO Configurations ────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS sso_configurations (
    id                  TEXT PRIMARY KEY,
    org_id              TEXT NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
    protocol            TEXT NOT NULL CONSTRAINT ck_sso_protocol CHECK (protocol IN ('saml', 'oidc')),
    is_enabled          INTEGER NOT NULL DEFAULT 1,
    issuer_or_entity_id TEXT NOT NULL DEFAULT '',
    sso_url             TEXT NOT NULL DEFAULT '',
    idp_certificate_enc TEXT DEFAULT '',
    sp_entity_id        TEXT DEFAULT '',
    acs_url             TEXT DEFAULT '',
    client_id           TEXT DEFAULT '',
    client_secret_enc   TEXT DEFAULT '',
    jwks_uri            TEXT DEFAULT '',
    default_role        TEXT NOT NULL DEFAULT 'reporter',
    role_mapping_json   TEXT NOT NULL DEFAULT '{}',
    created_at          TEXT NOT NULL DEFAULT (datetime('now')),
    updated_at          TEXT NOT NULL DEFAULT (datetime('now')),
    CONSTRAINT uq_sso_org_protocol UNIQUE (org_id, protocol)
);
CREATE INDEX IF NOT EXISTS idx_sso_org ON sso_configurations(org_id);

-- ── Ephemeral One-Time SSO Flow States (Anti-Replay / CSRF) ─────────────────
CREATE TABLE IF NOT EXISTS sso_flow_states (
    id          TEXT PRIMARY KEY,
    org_id      TEXT NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
    flow_type   TEXT NOT NULL CONSTRAINT ck_flow_type CHECK (flow_type IN ('saml', 'oidc')),
    flow_id     TEXT NOT NULL UNIQUE,
    nonce       TEXT,
    created_at  TEXT NOT NULL DEFAULT (datetime('now')),
    expires_at  TEXT NOT NULL,
    is_consumed INTEGER NOT NULL DEFAULT 0,
    consumed_at TEXT
);
CREATE INDEX IF NOT EXISTS idx_sso_flow_lookup ON sso_flow_states(flow_id, is_consumed, expires_at);
""" + sqlite_lifecycle_runtime_script() + sqlite_admission_authority_runtime_script()

# ── Migration: v4 → v5 ────────────────────────────────────────────────────────
# Applied automatically by AresDatabase.connect() when SCHEMA_VERSION mismatch.

# V5 → V6: adds revoked_access_tokens table for JWT JTI blacklist (logout revocation).
# Databases upgraded from v5 will not have this table - this migration adds it safely
# via CREATE TABLE IF NOT EXISTS so running it on a fresh v6 DB is also a no-op.
MIGRATION_V5_TO_V6 = """
CREATE TABLE IF NOT EXISTS revoked_access_tokens (
    jti         TEXT PRIMARY KEY,
    user_id     TEXT NOT NULL,
    expires_at  TEXT NOT NULL,
    revoked_at  TEXT NOT NULL DEFAULT (datetime('now'))
);
CREATE INDEX IF NOT EXISTS idx_rat_expires ON revoked_access_tokens(expires_at);
CREATE INDEX IF NOT EXISTS idx_rat_user    ON revoked_access_tokens(user_id);
"""

MIGRATION_V4_TO_V5 = """
CREATE TABLE IF NOT EXISTS users (
    id TEXT PRIMARY KEY, username TEXT NOT NULL UNIQUE,
    hashed_password TEXT NOT NULL, role TEXT NOT NULL DEFAULT 'reporter',
    is_active INTEGER NOT NULL DEFAULT 1, created_by TEXT NOT NULL DEFAULT 'system',
    created_at TEXT NOT NULL DEFAULT (datetime('now')), last_login TEXT
);
CREATE INDEX IF NOT EXISTS idx_users_username ON users(username);
CREATE INDEX IF NOT EXISTS idx_users_role     ON users(role);

CREATE TABLE IF NOT EXISTS api_keys (
    id TEXT PRIMARY KEY, user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    name TEXT NOT NULL, key_hash TEXT NOT NULL, key_prefix TEXT NOT NULL,
    scopes TEXT NOT NULL DEFAULT 'read', is_active INTEGER NOT NULL DEFAULT 1,
    last_used TEXT, expires_at TEXT, created_at TEXT NOT NULL DEFAULT (datetime('now'))
);
CREATE INDEX IF NOT EXISTS idx_apikeys_user   ON api_keys(user_id);
CREATE INDEX IF NOT EXISTS idx_apikeys_prefix ON api_keys(key_prefix);

CREATE TABLE IF NOT EXISTS refresh_tokens (
    id TEXT PRIMARY KEY, user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    is_revoked INTEGER NOT NULL DEFAULT 0, expires_at TEXT NOT NULL,
    created_at TEXT NOT NULL DEFAULT (datetime('now')), used_at TEXT
);
CREATE INDEX IF NOT EXISTS idx_refresh_user ON refresh_tokens(user_id);
CREATE INDEX IF NOT EXISTS idx_refresh_exp  ON refresh_tokens(expires_at);
"""