agent-governance/ledger/schema.sql

-- Agent Governance Ledger Schema
-- Version 2.0: Multi-Tenant Support (Phase 10)

-- ============================================================
-- MULTI-TENANT CORE TABLES
-- ============================================================

-- Tenants (organizations/customers)
CREATE TABLE IF NOT EXISTS tenants (
    tenant_id TEXT PRIMARY KEY,
    name TEXT NOT NULL,
    slug TEXT UNIQUE NOT NULL,
    owner_email TEXT,
    subscription_tier TEXT DEFAULT 'free',  -- free, pro, enterprise
    is_active INTEGER DEFAULT 1,
    settings TEXT,  -- JSON
    created_at TEXT DEFAULT CURRENT_TIMESTAMP,
    updated_at TEXT DEFAULT CURRENT_TIMESTAMP
);

-- Projects (workspaces within a tenant)
CREATE TABLE IF NOT EXISTS projects (
    project_id TEXT PRIMARY KEY,
    tenant_id TEXT NOT NULL,
    name TEXT NOT NULL,
    slug TEXT NOT NULL,
    description TEXT,
    is_active INTEGER DEFAULT 1,
    settings TEXT,  -- JSON
    created_at TEXT DEFAULT CURRENT_TIMESTAMP,
    updated_at TEXT DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (tenant_id) REFERENCES tenants(tenant_id) ON DELETE CASCADE,
    UNIQUE(tenant_id, slug)
);

-- Tenant quotas and limits
CREATE TABLE IF NOT EXISTS tenant_quotas (
    tenant_id TEXT PRIMARY KEY,
    max_projects INTEGER DEFAULT 3,
    max_agents_per_project INTEGER DEFAULT 5,
    max_concurrent_executions INTEGER DEFAULT 2,
    max_storage_bytes INTEGER DEFAULT 1073741824,  -- 1GB
    max_api_calls_per_day INTEGER DEFAULT 10000,
    max_tokens_per_day INTEGER DEFAULT 1000000,
    cost_budget_cents INTEGER DEFAULT 10000,  -- $100
    updated_at TEXT DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (tenant_id) REFERENCES tenants(tenant_id) ON DELETE CASCADE
);

-- Tenant usage tracking
CREATE TABLE IF NOT EXISTS tenant_usage (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    tenant_id TEXT NOT NULL,
    period_start TEXT NOT NULL,  -- YYYY-MM-DD
    api_calls INTEGER DEFAULT 0,
    tokens_used INTEGER DEFAULT 0,
    storage_bytes INTEGER DEFAULT 0,
    executions INTEGER DEFAULT 0,
    cost_cents INTEGER DEFAULT 0,
    updated_at TEXT DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (tenant_id) REFERENCES tenants(tenant_id) ON DELETE CASCADE,
    UNIQUE(tenant_id, period_start)
);

-- Project membership and roles
CREATE TABLE IF NOT EXISTS project_members (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    project_id TEXT NOT NULL,
    user_id TEXT NOT NULL,
    role TEXT NOT NULL DEFAULT 'viewer',  -- viewer, editor, admin, owner
    invited_by TEXT,
    created_at TEXT DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (project_id) REFERENCES projects(project_id) ON DELETE CASCADE,
    UNIQUE(project_id, user_id)
);

-- API keys for programmatic access
CREATE TABLE IF NOT EXISTS api_keys (
    key_id TEXT PRIMARY KEY,
    tenant_id TEXT NOT NULL,
    project_id TEXT,
    key_hash TEXT NOT NULL,
    name TEXT NOT NULL,
    permissions TEXT,  -- JSON
    rate_limit_per_minute INTEGER DEFAULT 60,
    expires_at TEXT,
    last_used_at TEXT,
    is_active INTEGER DEFAULT 1,
    created_at TEXT DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (tenant_id) REFERENCES tenants(tenant_id) ON DELETE CASCADE,
    FOREIGN KEY (project_id) REFERENCES projects(project_id) ON DELETE SET NULL
);

-- ============================================================
-- AGENT GOVERNANCE TABLES
-- ============================================================

CREATE TABLE IF NOT EXISTS agent_actions (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    tenant_id TEXT DEFAULT 'default',
    project_id TEXT DEFAULT 'default',
    timestamp TEXT NOT NULL,
    agent_id TEXT NOT NULL,
    agent_version TEXT NOT NULL,
    tier INTEGER NOT NULL,
    action TEXT NOT NULL,
    decision TEXT NOT NULL,
    confidence REAL NOT NULL,
    target TEXT,
    side_effects TEXT,  -- JSON array
    success INTEGER NOT NULL,  -- 0 or 1
    error_type TEXT,
    error_message TEXT,
    vault_token_accessor TEXT,
    session_id TEXT,
    created_at TEXT DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS agent_metrics (
    agent_id TEXT PRIMARY KEY,
    tenant_id TEXT DEFAULT 'default',
    project_id TEXT DEFAULT 'default',
    current_tier INTEGER DEFAULT 0,
    compliant_runs INTEGER DEFAULT 0,
    consecutive_compliant INTEGER DEFAULT 0,
    total_runs INTEGER DEFAULT 0,
    last_violation_at TEXT,
    last_active_at TEXT,
    promotion_eligible INTEGER DEFAULT 0,
    updated_at TEXT DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS violations (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    tenant_id TEXT DEFAULT 'default',
    project_id TEXT DEFAULT 'default',
    timestamp TEXT NOT NULL,
    agent_id TEXT NOT NULL,
    violation_type TEXT NOT NULL,
    severity TEXT NOT NULL,  -- low, medium, high, critical
    description TEXT NOT NULL,
    triggering_action TEXT,
    evidence TEXT,  -- JSON
    remediation TEXT,
    acknowledged INTEGER DEFAULT 0,
    acknowledged_by TEXT,
    created_at TEXT DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS promotions (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    tenant_id TEXT DEFAULT 'default',
    project_id TEXT DEFAULT 'default',
    timestamp TEXT NOT NULL,
    agent_id TEXT NOT NULL,
    from_tier INTEGER NOT NULL,
    to_tier INTEGER NOT NULL,
    approved_by TEXT NOT NULL,
    rationale TEXT,
    evidence TEXT,  -- JSON
    created_at TEXT DEFAULT CURRENT_TIMESTAMP
);

-- ============================================================
-- ORCHESTRATION TABLES
-- ============================================================

CREATE TABLE IF NOT EXISTS orchestration_log (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    tenant_id TEXT DEFAULT 'default',
    project_id TEXT DEFAULT 'default',
    timestamp TEXT NOT NULL,
    model TEXT NOT NULL,
    instruction TEXT NOT NULL,
    response TEXT,
    tokens_used INTEGER,
    success INTEGER NOT NULL,
    error_message TEXT,
    checkpoint_before TEXT,
    checkpoint_after TEXT,
    created_at TEXT DEFAULT CURRENT_TIMESTAMP
);

-- ============================================================
-- INDEXES
-- ============================================================

-- Multi-tenant indexes
CREATE INDEX IF NOT EXISTS idx_projects_tenant ON projects(tenant_id);
CREATE INDEX IF NOT EXISTS idx_tenant_usage_tenant ON tenant_usage(tenant_id);
CREATE INDEX IF NOT EXISTS idx_project_members_project ON project_members(project_id);
CREATE INDEX IF NOT EXISTS idx_project_members_user ON project_members(user_id);
CREATE INDEX IF NOT EXISTS idx_api_keys_tenant ON api_keys(tenant_id);

-- Agent table indexes
CREATE INDEX IF NOT EXISTS idx_actions_agent ON agent_actions(agent_id);
CREATE INDEX IF NOT EXISTS idx_actions_timestamp ON agent_actions(timestamp);
CREATE INDEX IF NOT EXISTS idx_actions_tenant ON agent_actions(tenant_id);
CREATE INDEX IF NOT EXISTS idx_actions_project ON agent_actions(project_id);
CREATE INDEX IF NOT EXISTS idx_metrics_tenant ON agent_metrics(tenant_id);
CREATE INDEX IF NOT EXISTS idx_metrics_project ON agent_metrics(project_id);
CREATE INDEX IF NOT EXISTS idx_violations_agent ON violations(agent_id);
CREATE INDEX IF NOT EXISTS idx_violations_severity ON violations(severity);
CREATE INDEX IF NOT EXISTS idx_violations_tenant ON violations(tenant_id);
CREATE INDEX IF NOT EXISTS idx_promotions_tenant ON promotions(tenant_id);

-- ============================================================
-- DEFAULT DATA
-- ============================================================

INSERT OR IGNORE INTO tenants (tenant_id, name, slug, subscription_tier)
VALUES ('default', 'Default Tenant', 'default', 'enterprise');

INSERT OR IGNORE INTO projects (project_id, tenant_id, name, slug)
VALUES ('default', 'default', 'Default Project', 'default');

INSERT OR IGNORE INTO tenant_quotas (
    tenant_id, max_projects, max_agents_per_project,
    max_concurrent_executions, max_storage_bytes,
    max_api_calls_per_day, max_tokens_per_day, cost_budget_cents
) VALUES (
    'default', 100, 100, 50, 107374182400, 1000000, 100000000, 1000000
);