diff options
Diffstat (limited to '')
| -rw-r--r-- | internal/controlplane/schema.sql | 110 |
1 files changed, 110 insertions, 0 deletions
diff --git a/internal/controlplane/schema.sql b/internal/controlplane/schema.sql index 88db49b..ef1ccdd 100644 --- a/internal/controlplane/schema.sql +++ b/internal/controlplane/schema.sql @@ -49,17 +49,53 @@ CREATE TABLE IF NOT EXISTS api_keys ( revoked_at TIMESTAMPTZ, FOREIGN KEY (project_id, tenant_id) REFERENCES projects(id, tenant_id) ON DELETE CASCADE ); +ALTER TABLE api_keys ADD COLUMN IF NOT EXISTS monthly_spend_micros BIGINT NOT NULL DEFAULT 0 CHECK (monthly_spend_micros >= 0); +ALTER TABLE api_keys ADD COLUMN IF NOT EXISTS expires_at TIMESTAMPTZ; +ALTER TABLE api_keys ADD COLUMN IF NOT EXISTS tags JSONB NOT NULL DEFAULT '[]'::jsonb; CREATE TABLE IF NOT EXISTS providers ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), + slug TEXT NOT NULL UNIQUE CHECK (slug ~ '^[a-z0-9][a-z0-9-]{1,62}[a-z0-9]$'), name TEXT NOT NULL UNIQUE, protocol TEXT NOT NULL CHECK (protocol IN ('openai', 'anthropic')), + wire_api TEXT NOT NULL DEFAULT 'chat_completions' CHECK (wire_api IN ('chat_completions', 'responses', 'messages')), base_url TEXT NOT NULL, api_key_ciphertext BYTEA NOT NULL, enabled BOOLEAN NOT NULL DEFAULT TRUE, created_at TIMESTAMPTZ NOT NULL DEFAULT now(), updated_at TIMESTAMPTZ NOT NULL DEFAULT now() ); +ALTER TABLE providers ADD COLUMN IF NOT EXISTS slug TEXT; +WITH normalized AS ( + SELECT id, + left(trim(both '-' from regexp_replace(lower(name), '[^a-z0-9]+', '-', 'g')), 64) AS base + FROM providers +), ranked AS ( + SELECT id, base, count(*) OVER (PARTITION BY base) AS base_count + FROM normalized +) +UPDATE providers p +SET slug = CASE + WHEN length(r.base) BETWEEN 3 AND 64 + AND r.base ~ '^[a-z0-9][a-z0-9-]{1,62}[a-z0-9]$' + AND r.base_count = 1 THEN r.base + ELSE 'provider-' || left(replace(p.id::text, '-', ''), 12) +END +FROM ranked r +WHERE p.id = r.id AND (p.slug IS NULL OR p.slug = ''); +ALTER TABLE providers ALTER COLUMN slug SET NOT NULL; +CREATE UNIQUE INDEX IF NOT EXISTS providers_slug_unique_idx ON providers (slug); +ALTER TABLE providers DROP CONSTRAINT IF EXISTS providers_slug_check; +ALTER TABLE providers ADD CONSTRAINT providers_slug_check CHECK (slug ~ '^[a-z0-9][a-z0-9-]{1,62}[a-z0-9]$'); +ALTER TABLE providers ADD COLUMN IF NOT EXISTS wire_api TEXT NOT NULL DEFAULT 'chat_completions'; +UPDATE providers SET wire_api = 'messages' WHERE protocol = 'anthropic' AND wire_api = 'chat_completions'; +ALTER TABLE providers DROP CONSTRAINT IF EXISTS providers_wire_api_check; +ALTER TABLE providers ADD CONSTRAINT providers_wire_api_check CHECK (wire_api IN ('chat_completions', 'responses', 'messages')); +ALTER TABLE providers DROP CONSTRAINT IF EXISTS providers_protocol_wire_api_check; +ALTER TABLE providers ADD CONSTRAINT providers_protocol_wire_api_check CHECK ( + (protocol = 'openai' AND wire_api IN ('chat_completions', 'responses')) OR + (protocol = 'anthropic' AND wire_api = 'messages') +); CREATE TABLE IF NOT EXISTS models ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), @@ -145,6 +181,15 @@ CREATE TABLE IF NOT EXISTS api_key_model_allowlist ( PRIMARY KEY (api_key_id, model_id) ); +-- Per-key restrictions are separate from the platform model allowlist above: +-- an empty set means that the key may use every model visible to its tenant. +CREATE TABLE IF NOT EXISTS api_key_model_restrictions ( + api_key_id UUID NOT NULL REFERENCES api_keys(id) ON DELETE CASCADE, + model_id UUID NOT NULL REFERENCES models(id) ON DELETE CASCADE, + created_at TIMESTAMPTZ NOT NULL DEFAULT now(), + PRIMARY KEY (api_key_id, model_id) +); + 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, @@ -171,6 +216,18 @@ CREATE TABLE IF NOT EXISTS tenant_wallets ( updated_at TIMESTAMPTZ NOT NULL DEFAULT now() ); +-- Tenant-scoped developer and billing preferences. These values are control +-- plane data, but are intentionally not loaded into the inference snapshot. +CREATE TABLE IF NOT EXISTS tenant_preferences ( + tenant_id UUID PRIMARY KEY REFERENCES tenants(id) ON DELETE CASCADE, + default_model TEXT REFERENCES models(public_id) ON DELETE SET NULL, + fallback_model TEXT REFERENCES models(public_id) ON DELETE SET NULL, + low_balance_enabled BOOLEAN NOT NULL DEFAULT TRUE, + low_balance_threshold_micros BIGINT NOT NULL DEFAULT 5000000 CHECK (low_balance_threshold_micros >= 0), + updated_at TIMESTAMPTZ NOT NULL DEFAULT now(), + CHECK (default_model IS NULL OR fallback_model IS NULL OR default_model <> fallback_model) +); + CREATE TABLE IF NOT EXISTS billing_reservations ( request_id TEXT PRIMARY KEY, tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE, @@ -286,6 +343,10 @@ CREATE TABLE IF NOT EXISTS topup_orders ( created_at TIMESTAMPTZ NOT NULL DEFAULT now(), paid_at TIMESTAMPTZ ); +ALTER TABLE topup_orders ADD COLUMN IF NOT EXISTS trigger_type TEXT NOT NULL DEFAULT 'manual'; +ALTER TABLE topup_orders DROP CONSTRAINT IF EXISTS topup_orders_trigger_type_check; +ALTER TABLE topup_orders ADD CONSTRAINT topup_orders_trigger_type_check + CHECK (trigger_type IN ('manual','auto')); ALTER TABLE topup_orders DROP CONSTRAINT IF EXISTS topup_orders_status_check; ALTER TABLE topup_orders ADD CONSTRAINT topup_orders_status_check CHECK (status IN ('pending','paid','failed','expired','partially_refunded','refunded','disputed','reversed')); @@ -306,6 +367,8 @@ ALTER TABLE topup_orders ADD CONSTRAINT topup_orders_reconciliation_status_check CHECK (reconciliation_status IN ('unknown','ok','repaired','missing','mismatch','resolved')); CREATE INDEX IF NOT EXISTS topup_orders_payment_intent_idx ON topup_orders (stripe_payment_intent_id) WHERE stripe_payment_intent_id IS NOT NULL; CREATE INDEX IF NOT EXISTS topup_orders_customer_idx ON topup_orders (stripe_customer_id) WHERE stripe_customer_id IS NOT NULL; +CREATE UNIQUE INDEX IF NOT EXISTS topup_orders_auto_pending_idx ON topup_orders (tenant_id) + WHERE trigger_type='auto' AND status='pending'; CREATE TABLE IF NOT EXISTS billing_reconciliation_resolutions ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), @@ -325,6 +388,51 @@ CREATE TABLE IF NOT EXISTS stripe_customers ( updated_at TIMESTAMPTZ NOT NULL DEFAULT now() ); +CREATE TABLE IF NOT EXISTS tenant_billing_profiles ( + tenant_id UUID PRIMARY KEY REFERENCES tenants(id) ON DELETE CASCADE, + legal_name TEXT NOT NULL, + billing_email TEXT NOT NULL, + address_line1 TEXT NOT NULL, + address_line2 TEXT NOT NULL DEFAULT '', + city TEXT NOT NULL, + region TEXT NOT NULL DEFAULT '', + postal_code TEXT NOT NULL, + country TEXT NOT NULL CHECK (country ~ '^[A-Z]{2}$'), + stripe_sync_status TEXT NOT NULL DEFAULT 'pending' + CHECK (stripe_sync_status IN ('pending','synced','failed','disabled')), + stripe_synced_at TIMESTAMPTZ, + stripe_sync_error TEXT NOT NULL DEFAULT '', + created_at TIMESTAMPTZ NOT NULL DEFAULT now(), + updated_at TIMESTAMPTZ NOT NULL DEFAULT now() +); + +CREATE TABLE IF NOT EXISTS tenant_auto_topup_settings ( + tenant_id UUID PRIMARY KEY REFERENCES tenants(id) ON DELETE CASCADE, + enabled BOOLEAN NOT NULL DEFAULT FALSE, + threshold_micros BIGINT NOT NULL CHECK (threshold_micros >= 0), + topup_amount_minor BIGINT NOT NULL CHECK (topup_amount_minor > 0), + stripe_payment_method_id TEXT, + payment_method_type TEXT NOT NULL DEFAULT '', + payment_method_brand TEXT NOT NULL DEFAULT '', + payment_method_last4 TEXT NOT NULL DEFAULT '', + payment_method_exp_month INTEGER NOT NULL DEFAULT 0 CHECK (payment_method_exp_month BETWEEN 0 AND 12), + payment_method_exp_year INTEGER NOT NULL DEFAULT 0 CHECK (payment_method_exp_year >= 0), + stripe_setup_session_id TEXT UNIQUE, + status TEXT NOT NULL DEFAULT 'not_configured' + CHECK (status IN ('not_configured','ready','charging','action_required','failed')), + last_error TEXT NOT NULL DEFAULT '', + failure_count INTEGER NOT NULL DEFAULT 0 CHECK (failure_count >= 0), + last_attempt_at TIMESTAMPTZ, + last_succeeded_at TIMESTAMPTZ, + next_attempt_at TIMESTAMPTZ, + created_at TIMESTAMPTZ NOT NULL DEFAULT now(), + updated_at TIMESTAMPTZ NOT NULL DEFAULT now(), + CHECK (stripe_payment_method_id IS NOT NULL OR enabled = FALSE) +); +CREATE INDEX IF NOT EXISTS tenant_auto_topup_ready_idx + ON tenant_auto_topup_settings (next_attempt_at, tenant_id) + WHERE enabled AND stripe_payment_method_id IS NOT NULL; + CREATE TABLE IF NOT EXISTS stripe_refunds ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE RESTRICT, @@ -658,8 +766,10 @@ CREATE INDEX IF NOT EXISTS billing_ledger_tenant_idx ON billing_ledger (tenant_i 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 usage_events_key_idx ON usage_events (key_id, started_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 billing_reservations_key_period_idx ON billing_reservations (key_id, created_at DESC) WHERE status IN ('pending', 'metering_failed'); CREATE INDEX IF NOT EXISTS console_users_tenant_idx ON console_users (tenant_id, created_at DESC); CREATE INDEX IF NOT EXISTS console_sessions_user_idx ON console_sessions (user_id, created_at DESC); CREATE INDEX IF NOT EXISTS console_sessions_expiry_idx ON console_sessions (expires_at) WHERE revoked_at IS NULL; |