-- 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);