1 files changed, 179 insertions, 0 deletions
diff --git a/internal/controlplane/schema.sql b/internal/controlplane/schema.sql
index 25af7c9..a518b1d 100644
--- a/internal/controlplane/schema.sql
+++ b/internal/controlplane/schema.sql
@@ -58,11 +58,20 @@ CREATE TABLE IF NOT EXISTS models (
     id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
     public_id TEXT NOT NULL UNIQUE,
     owned_by TEXT NOT NULL DEFAULT '',
+    input_price_micros_per_million BIGINT NOT NULL DEFAULT 0 CHECK (input_price_micros_per_million >= 0),
+    output_price_micros_per_million BIGINT NOT NULL DEFAULT 0 CHECK (output_price_micros_per_million >= 0),
+    cache_read_price_micros_per_million BIGINT NOT NULL DEFAULT 0 CHECK (cache_read_price_micros_per_million >= 0),
+    cache_write_price_micros_per_million BIGINT NOT NULL DEFAULT 0 CHECK (cache_write_price_micros_per_million >= 0),
     enabled BOOLEAN NOT NULL DEFAULT TRUE,
     created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
     updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
 );
 
+ALTER TABLE models ADD COLUMN IF NOT EXISTS input_price_micros_per_million BIGINT NOT NULL DEFAULT 0 CHECK (input_price_micros_per_million >= 0);
+ALTER TABLE models ADD COLUMN IF NOT EXISTS output_price_micros_per_million BIGINT NOT NULL DEFAULT 0 CHECK (output_price_micros_per_million >= 0);
+ALTER TABLE models ADD COLUMN IF NOT EXISTS cache_read_price_micros_per_million BIGINT NOT NULL DEFAULT 0 CHECK (cache_read_price_micros_per_million >= 0);
+ALTER TABLE models ADD COLUMN IF NOT EXISTS cache_write_price_micros_per_million BIGINT NOT NULL DEFAULT 0 CHECK (cache_write_price_micros_per_million >= 0);
+
 CREATE TABLE IF NOT EXISTS model_routes (
     id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
     model_id UUID NOT NULL REFERENCES models(id) ON DELETE CASCADE,
@@ -80,3 +89,173 @@ CREATE INDEX IF NOT EXISTS api_keys_active_hash_idx ON api_keys (key_hash) WHERE
 CREATE INDEX IF NOT EXISTS projects_tenant_idx ON projects (tenant_id);
 CREATE INDEX IF NOT EXISTS model_routes_model_idx ON model_routes (model_id) WHERE enabled;
 CREATE INDEX IF NOT EXISTS model_routes_provider_idx ON model_routes (provider_id) WHERE enabled;
+
+CREATE TABLE IF NOT EXISTS tenant_wallets (
+    tenant_id UUID PRIMARY KEY REFERENCES tenants(id) ON DELETE CASCADE,
+    currency TEXT NOT NULL CHECK (currency = lower(currency) AND length(currency) = 3),
+    balance_micros BIGINT NOT NULL DEFAULT 0 CHECK (balance_micros >= 0),
+    reserved_micros BIGINT NOT NULL DEFAULT 0 CHECK (reserved_micros >= 0 AND reserved_micros <= balance_micros),
+    updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
+);
+
+CREATE TABLE IF NOT EXISTS billing_reservations (
+    request_id TEXT PRIMARY KEY,
+    tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
+    project_id UUID NOT NULL,
+    key_id UUID NOT NULL,
+    public_model TEXT NOT NULL,
+    currency TEXT NOT NULL,
+    reserved_micros BIGINT NOT NULL CHECK (reserved_micros >= 0),
+    status TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'settled', 'released')),
+    input_price_micros_per_million BIGINT NOT NULL,
+    output_price_micros_per_million BIGINT NOT NULL,
+    cache_read_price_micros_per_million BIGINT NOT NULL,
+    cache_write_price_micros_per_million BIGINT NOT NULL,
+    actual_cost_micros BIGINT NOT NULL DEFAULT 0 CHECK (actual_cost_micros >= 0),
+    charged_micros BIGINT NOT NULL DEFAULT 0 CHECK (charged_micros >= 0),
+    uncollected_micros BIGINT NOT NULL DEFAULT 0 CHECK (uncollected_micros >= 0),
+    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
+    settled_at TIMESTAMPTZ,
+    FOREIGN KEY (project_id, tenant_id) REFERENCES projects(id, tenant_id) ON DELETE CASCADE
+);
+
+CREATE TABLE IF NOT EXISTS usage_events (
+    request_id TEXT PRIMARY KEY,
+    tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE RESTRICT,
+    project_id UUID NOT NULL,
+    key_id UUID NOT NULL,
+    public_model TEXT NOT NULL,
+    provider_id TEXT,
+    upstream_model TEXT,
+    protocol TEXT NOT NULL,
+    stream BOOLEAN NOT NULL DEFAULT FALSE,
+    status_code INTEGER NOT NULL,
+    success BOOLEAN NOT NULL,
+    error_type TEXT NOT NULL DEFAULT '',
+    attempts INTEGER NOT NULL DEFAULT 0,
+    started_at TIMESTAMPTZ NOT NULL,
+    duration_ms BIGINT NOT NULL DEFAULT 0,
+    input_tokens BIGINT NOT NULL DEFAULT 0,
+    output_tokens BIGINT NOT NULL DEFAULT 0,
+    total_tokens BIGINT NOT NULL DEFAULT 0,
+    cache_creation_input_tokens BIGINT NOT NULL DEFAULT 0,
+    cache_read_input_tokens BIGINT NOT NULL DEFAULT 0,
+    cost_micros BIGINT NOT NULL DEFAULT 0,
+    charged_micros BIGINT NOT NULL DEFAULT 0,
+    uncollected_micros BIGINT NOT NULL DEFAULT 0,
+    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
+    FOREIGN KEY (project_id, tenant_id) REFERENCES projects(id, tenant_id) ON DELETE RESTRICT
+);
+
+-- Usage persistence is independent from billing. Older installations created this
+-- foreign key, which prevented recording requests when prepaid billing was disabled.
+ALTER TABLE usage_events DROP CONSTRAINT IF EXISTS usage_events_request_id_fkey;
+
+CREATE TABLE IF NOT EXISTS billing_ledger (
+    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
+    tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE RESTRICT,
+    project_id UUID,
+    currency TEXT NOT NULL,
+    amount_micros BIGINT NOT NULL,
+    balance_after_micros BIGINT NOT NULL CHECK (balance_after_micros >= 0),
+    kind TEXT NOT NULL CHECK (kind IN ('topup', 'usage', 'adjustment', 'refund', 'release')),
+    source_type TEXT NOT NULL,
+    source_id TEXT NOT NULL,
+    description TEXT NOT NULL DEFAULT '',
+    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
+    UNIQUE (source_type, source_id)
+);
+
+CREATE TABLE IF NOT EXISTS topup_orders (
+    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
+    tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE RESTRICT,
+    amount_minor BIGINT NOT NULL CHECK (amount_minor > 0),
+    amount_micros BIGINT NOT NULL CHECK (amount_micros > 0),
+    currency TEXT NOT NULL,
+    status TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'paid', 'failed', 'expired')),
+    stripe_session_id TEXT UNIQUE,
+    checkout_url TEXT,
+    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
+    paid_at TIMESTAMPTZ
+);
+
+CREATE TABLE IF NOT EXISTS stripe_webhook_events (
+    event_id TEXT PRIMARY KEY,
+    event_type TEXT NOT NULL,
+    processed_at TIMESTAMPTZ,
+    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
+);
+
+CREATE TABLE IF NOT EXISTS console_users (
+    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
+    tenant_id UUID REFERENCES tenants(id) ON DELETE CASCADE,
+    email TEXT NOT NULL,
+    display_name TEXT NOT NULL,
+    role TEXT NOT NULL CHECK (role IN (
+        'platform_admin', 'platform_viewer', 'tenant_admin',
+        'tenant_billing', 'tenant_developer', 'tenant_viewer'
+    )),
+    token_prefix TEXT NOT NULL,
+    token_hash BYTEA NOT NULL UNIQUE CHECK (octet_length(token_hash) = 32),
+    status TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'revoked')),
+    last_used_at TIMESTAMPTZ,
+    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
+    revoked_at TIMESTAMPTZ,
+    CHECK ((role LIKE 'platform_%' AND tenant_id IS NULL) OR (role LIKE 'tenant_%' AND tenant_id IS NOT NULL))
+);
+CREATE UNIQUE INDEX IF NOT EXISTS console_users_email_tenant_idx
+    ON console_users (lower(email), COALESCE(tenant_id, '00000000-0000-0000-0000-000000000000'::uuid));
+
+CREATE TABLE IF NOT EXISTS project_limits (
+    project_id UUID PRIMARY KEY,
+    tenant_id UUID NOT NULL,
+    requests_per_minute BIGINT NOT NULL DEFAULT 0 CHECK (requests_per_minute >= 0),
+    tokens_per_minute BIGINT NOT NULL DEFAULT 0 CHECK (tokens_per_minute >= 0),
+    concurrent_requests BIGINT NOT NULL DEFAULT 0 CHECK (concurrent_requests >= 0),
+    monthly_spend_micros BIGINT NOT NULL DEFAULT 0 CHECK (monthly_spend_micros >= 0),
+    updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
+    FOREIGN KEY (project_id, tenant_id) REFERENCES projects(id, tenant_id) ON DELETE CASCADE
+);
+
+CREATE TABLE IF NOT EXISTS usage_monthly_rollups (
+    period_start DATE NOT NULL,
+    tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE RESTRICT,
+    project_id UUID NOT NULL,
+    request_count BIGINT NOT NULL DEFAULT 0,
+    successful_requests BIGINT NOT NULL DEFAULT 0,
+    input_tokens BIGINT NOT NULL DEFAULT 0,
+    output_tokens BIGINT NOT NULL DEFAULT 0,
+    total_tokens BIGINT NOT NULL DEFAULT 0,
+    cost_micros BIGINT NOT NULL DEFAULT 0,
+    charged_micros BIGINT NOT NULL DEFAULT 0,
+    uncollected_micros BIGINT NOT NULL DEFAULT 0,
+    updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
+    PRIMARY KEY (project_id, period_start),
+    FOREIGN KEY (project_id, tenant_id) REFERENCES projects(id, tenant_id) ON DELETE RESTRICT
+);
+
+CREATE TABLE IF NOT EXISTS audit_logs (
+    id BIGSERIAL PRIMARY KEY,
+    actor_id UUID REFERENCES console_users(id) ON DELETE SET NULL,
+    actor_type TEXT NOT NULL CHECK (actor_type IN ('bootstrap', 'console_user')),
+    actor_role TEXT NOT NULL,
+    tenant_id UUID REFERENCES tenants(id) ON DELETE SET NULL,
+    request_id TEXT NOT NULL,
+    method TEXT NOT NULL,
+    path TEXT NOT NULL,
+    action TEXT NOT NULL,
+    status_code INTEGER NOT NULL,
+    remote_ip INET,
+    user_agent TEXT NOT NULL DEFAULT '',
+    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
+);
+
+CREATE INDEX IF NOT EXISTS billing_ledger_tenant_idx ON billing_ledger (tenant_id, created_at DESC);
+CREATE INDEX IF NOT EXISTS usage_events_tenant_idx ON usage_events (tenant_id, created_at DESC);
+CREATE INDEX IF NOT EXISTS usage_events_project_idx ON usage_events (project_id, created_at DESC);
+CREATE INDEX IF NOT EXISTS usage_events_model_idx ON usage_events (public_model, created_at DESC);
+CREATE INDEX IF NOT EXISTS billing_reservations_pending_idx ON billing_reservations (status, created_at) WHERE status = 'pending';
+CREATE INDEX IF NOT EXISTS billing_reservations_project_pending_idx ON billing_reservations (project_id, created_at) WHERE status = 'pending';
+CREATE INDEX IF NOT EXISTS console_users_tenant_idx ON console_users (tenant_id, created_at DESC);
+CREATE INDEX IF NOT EXISTS audit_logs_created_idx ON audit_logs (created_at DESC);
+CREATE INDEX IF NOT EXISTS audit_logs_tenant_idx ON audit_logs (tenant_id, created_at DESC);