clavitor/clavitor.ai/schema.sql

-- Clavitor Corporate Service Database Schema
-- Uses 8-byte int64 IDs (positive only, via lib.NewID())
-- Separate from vault databases - this is the central metadata/operational service

-- Tenants: Multi-tenant hierarchy (MSP → Customer → Dept)
CREATE TABLE tenants (
    id              INTEGER PRIMARY KEY,  -- 8-byte int64, use lib.NewID()
    parent_id       INTEGER REFERENCES tenants(id),  -- NULL for root tenants
    level           INTEGER NOT NULL,  -- Hierarchy depth: 1=MSP, 2=Customer, 3=Dept
    name            TEXT NOT NULL,
    status          TEXT NOT NULL DEFAULT 'active',  -- active, suspended, archived
    billing_email   TEXT,  -- Where invoices go
    created_at      INTEGER NOT NULL,
    updated_at      INTEGER
);

CREATE INDEX idx_tenants_parent ON tenants(parent_id);
CREATE INDEX idx_tenants_status ON tenants(status);

-- Humans: People who can access the system
CREATE TABLE humans (
    id              INTEGER PRIMARY KEY,  -- 8-byte int64
    tenant_id       INTEGER NOT NULL REFERENCES tenants(id),
    email           TEXT NOT NULL UNIQUE,
    name            TEXT NOT NULL,
    status          TEXT NOT NULL DEFAULT 'active',  -- active, inactive, suspended
    created_at      INTEGER NOT NULL,
    updated_at      INTEGER
);

CREATE INDEX idx_humans_tenant ON humans(tenant_id);
CREATE INDEX idx_humans_email ON humans(email);

-- Roles: Global role definitions (we define these, customers assign them)
CREATE TABLE roles (
    id              INTEGER PRIMARY KEY,  -- 8-byte int64
    name            TEXT NOT NULL UNIQUE,  -- SuperAdmin, MSPAdmin, TenantAdmin, VaultUser, BillingAdmin, ReadOnly
    permissions     TEXT NOT NULL,  -- JSON: {"vault_create": true, "agent_create": true, ...}
    description     TEXT,
    created_at      INTEGER NOT NULL
);

-- Pre-populate standard roles
INSERT INTO roles (id, name, permissions, description, created_at) VALUES
    (lib.NewID(), 'SuperAdmin', '{"all": true}', 'Full access to all tenants and systems', strftime('%s', 'now')),
    (lib.NewID(), 'MSPAdmin', '{"tenant_admin": true, "billing_view": true, "vault_read": true}', 'Admin for MSP and all child tenants', strftime('%s', 'now')),
    (lib.NewID(), 'TenantAdmin', '{"tenant_admin": true, "vault_create": true, "agent_create": true, "device_enroll": true}', 'Admin for specific tenant', strftime('%s', 'now')),
    (lib.NewID(), 'VaultUser', '{"vault_read": true, "vault_write": true}', 'Can use vaults, cannot admin', strftime('%s', 'now')),
    (lib.NewID(), 'BillingAdmin', '{"billing_view": true, "invoices": true}', 'Can view and pay invoices', strftime('%s', 'now')),
    (lib.NewID(), 'ReadOnly', '{"vault_read": true}', 'View only, no changes', strftime('%s', 'now'));

-- RoleAssignments: Humans can have different roles in different tenants
CREATE TABLE role_assignments (
    id              INTEGER PRIMARY KEY,  -- 8-byte int64
    human_id        INTEGER NOT NULL REFERENCES humans(id),
    tenant_id       INTEGER NOT NULL REFERENCES tenants(id),
    role_id         INTEGER NOT NULL REFERENCES roles(id),
    granted_by      INTEGER REFERENCES humans(id),  -- Who gave this permission
    created_at      INTEGER NOT NULL,
    UNIQUE(human_id, tenant_id, role_id)  -- One role per human per tenant
);

CREATE INDEX idx_role_assignments_human ON role_assignments(human_id);
CREATE INDEX idx_role_assignments_tenant ON role_assignments(tenant_id);

-- Subscriptions: Flat-rate license plans (not metered usage)
CREATE TABLE subscriptions (
    id                  INTEGER PRIMARY KEY,  -- 8-byte int64
    tenant_id           INTEGER NOT NULL REFERENCES tenants(id),
    plan                TEXT NOT NULL,  -- Personal, Family, Pro, Team10, Team25, Team100, Team250, Team500, MME, Enterprise
    status              TEXT NOT NULL DEFAULT 'active',  -- active, past_due, cancelled, suspended, archived
    started_at          INTEGER NOT NULL,
    ends_at             INTEGER,  -- NULL = perpetual
    billing_cycle       TEXT,  -- monthly, annual
    next_invoice_at     INTEGER,
    max_vaults          INTEGER NOT NULL,
    max_devices         INTEGER NOT NULL,    -- Per vault
    max_tokens          INTEGER NOT NULL,    -- Per vault
    price_per_year_cents INTEGER NOT NULL,    -- For reference/display
    paddle_subscription_id TEXT,  -- External billing reference (optional for direct invoice)
    invoice_terms       TEXT,  -- net_60, immediate - NULL defaults by tier
    created_at          INTEGER NOT NULL,
    updated_at          INTEGER
);

CREATE INDEX idx_subscriptions_tenant ON subscriptions(tenant_id);
CREATE INDEX idx_subscriptions_status ON subscriptions(status);
CREATE INDEX idx_subscriptions_ends ON subscriptions(ends_at);

-- Vaults: Registry linking L0 vaults to tenants and humans
CREATE TABLE vaults (
    l0                  TEXT PRIMARY KEY,  -- Vault identifier (first 4 bytes of L3), hex encoded
    tenant_id           INTEGER NOT NULL REFERENCES tenants(id),
    owner_human_id      INTEGER REFERENCES humans(id),  -- Who created/owns this vault
    subscription_id     INTEGER REFERENCES subscriptions(id),
    created_at          INTEGER NOT NULL,
    last_seen_at        INTEGER,  -- For activity tracking
    status              TEXT NOT NULL DEFAULT 'active',  -- active, suspended, archived
    plan_override       TEXT  -- NULL = use subscription plan, or override for this specific vault
);

CREATE INDEX idx_vaults_tenant ON vaults(tenant_id);
CREATE INDEX idx_vaults_owner ON vaults(owner_human_id);
CREATE INDEX idx_vaults_status ON vaults(status);

-- Credentials (WL3 Registry): Which humans have which wrapped L3s
-- This is the "journal" for POPs to pull from - no vault data, just metadata
CREATE TABLE credentials (
    id              INTEGER PRIMARY KEY,  -- 8-byte int64
    human_id        INTEGER NOT NULL REFERENCES humans(id),
    p0              TEXT NOT NULL,  -- First 4 bytes of PRF, hex encoded (lookup key)
    l0              TEXT NOT NULL REFERENCES vaults(l0),  -- Which vault this opens
    credential_id   BLOB NOT NULL,  -- WebAuthn credential ID (for display/admin)
    wl3_path        TEXT NOT NULL,  -- Path in storage: a1/a1b2c3d4/20260405_abc.cla
    enrolled_by     INTEGER REFERENCES humans(id),  -- Device that authorized this enrollment
    created_at      INTEGER NOT NULL,
    last_used_at    INTEGER  -- For "active device" tracking
);

CREATE INDEX idx_credentials_human ON credentials(human_id);
CREATE INDEX idx_credentials_l0 ON credentials(l0);
CREATE INDEX idx_credentials_p0 ON credentials(p0);
CREATE INDEX idx_credentials_created ON credentials(created_at);  -- For incremental sync to POPs

-- BillingEvents: Minimal audit trail
CREATE TABLE billing_events (
    id              INTEGER PRIMARY KEY,  -- 8-byte int64
    occurred_at     INTEGER NOT NULL,
    tenant_id       INTEGER NOT NULL REFERENCES tenants(id),
    subscription_id INTEGER REFERENCES subscriptions(id),
    human_id        INTEGER REFERENCES humans(id),
    vault_l0        TEXT REFERENCES vaults(l0),
    event_type      TEXT NOT NULL,  -- subscription_created, upgraded, payment_received, vault_created, device_enrolled, etc.
    amount_cents    INTEGER,  -- For payment events
    details         TEXT  -- JSON blob for extensibility
);

CREATE INDEX idx_billing_events_tenant ON billing_events(tenant_id, occurred_at);
CREATE INDEX idx_billing_events_time ON billing_events(occurred_at);

-- SyncState: Track which POPs have which credentials (for incremental pull API)
CREATE TABLE pop_sync_state (
    pop_id          TEXT PRIMARY KEY,  -- e.g., "pop-zurich-01", "pop-singapore-03"
    last_sync_at    INTEGER NOT NULL,  -- Timestamp of last successful sync
    last_sync_cursor INTEGER,  -- Optional: last credential.id synced
    status          TEXT NOT NULL DEFAULT 'active',  -- active, paused, error
    updated_at      INTEGER NOT NULL
);

-- AgentTokens: For CLI/extension authentication (separate from WebAuthn)
CREATE TABLE agent_tokens (
    id              INTEGER PRIMARY KEY,  -- 8-byte int64
    vault_l0        TEXT NOT NULL REFERENCES vaults(l0),
    name            TEXT NOT NULL,  -- e.g., "Claude Code", "Deploy CI"
    token_hash      TEXT NOT NULL UNIQUE,  -- SHA256 of the actual token
    scopes          TEXT NOT NULL DEFAULT '',  -- Comma-separated scope IDs: "0001,0002"
    all_access      INTEGER NOT NULL DEFAULT 0,  -- 1 = reads everything
    created_by      INTEGER REFERENCES humans(id),
    created_at      INTEGER NOT NULL,
    expires_at      INTEGER,  -- NULL = no expiry
    last_used_at    INTEGER,
    status          TEXT NOT NULL DEFAULT 'active'  -- active, revoked, expired
);

CREATE INDEX idx_agent_tokens_vault ON agent_tokens(vault_l0);
CREATE INDEX idx_agent_tokens_hash ON agent_tokens(token_hash);