ARES - authorized red-team engagement automation with dashboard, campaign scope, module orchestration, OPSEC controls, encrypted vault, and reporting.
Python active-directorycloud-securitydefensive-validationfastapigood-first-issuemitre-attackopsecreactred-teamreportingsecurity
"""
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);
"""