From 1a3d7f9a8a181df48f0e911cbe17a3fad3ab9ac9 Mon Sep 17 00:00:00 2001 From: Chia Date: Wed, 5 Aug 2026 00:26:25 +1200 Subject: add some scripts --- internal/controlplane/schema.sql | 179 +++++++++++++++++++++++++++++++++++++++ 1 file changed, 179 insertions(+) (limited to 'internal/controlplane/schema.sql') 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); -- cgit v1.2.3