diff options
Diffstat (limited to 'internal/controlplane/schema.sql')
| -rw-r--r-- | internal/controlplane/schema.sql | 40 |
1 files changed, 34 insertions, 6 deletions
diff --git a/internal/controlplane/schema.sql b/internal/controlplane/schema.sql index ef1ccdd..d71f37f 100644 --- a/internal/controlplane/schema.sql +++ b/internal/controlplane/schema.sql @@ -41,24 +41,35 @@ CREATE TABLE IF NOT EXISTS api_keys ( project_id UUID NOT NULL, name TEXT NOT NULL, key_prefix TEXT NOT NULL, + key_suffix TEXT NOT NULL DEFAULT '', key_hash BYTEA NOT NULL UNIQUE CHECK (octet_length(key_hash) = 32), scopes JSONB NOT NULL DEFAULT '["inference"]'::jsonb, - status TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'revoked')), + status TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'disabled', 'revoked')), last_used_at TIMESTAMPTZ, created_at TIMESTAMPTZ NOT NULL DEFAULT now(), 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 daily_spend_micros BIGINT NOT NULL DEFAULT 0 CHECK (daily_spend_micros >= 0); +ALTER TABLE api_keys ADD COLUMN IF NOT EXISTS requests_per_minute BIGINT NOT NULL DEFAULT 0 CHECK (requests_per_minute >= 0); +ALTER TABLE api_keys ADD COLUMN IF NOT EXISTS tokens_per_minute BIGINT NOT NULL DEFAULT 0 CHECK (tokens_per_minute >= 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; +ALTER TABLE api_keys ADD COLUMN IF NOT EXISTS disabled_at TIMESTAMPTZ; +ALTER TABLE api_keys ADD COLUMN IF NOT EXISTS rotated_from_id UUID REFERENCES api_keys(id) ON DELETE SET NULL; +ALTER TABLE api_keys ADD COLUMN IF NOT EXISTS key_suffix TEXT NOT NULL DEFAULT ''; +ALTER TABLE api_keys DROP CONSTRAINT IF EXISTS api_keys_key_suffix_check; +ALTER TABLE api_keys ADD CONSTRAINT api_keys_key_suffix_check CHECK (key_suffix = '' OR key_suffix ~ '^[A-Za-z0-9_-]{6}$'); +ALTER TABLE api_keys DROP CONSTRAINT IF EXISTS api_keys_status_check; +ALTER TABLE api_keys ADD CONSTRAINT api_keys_status_check CHECK (status IN ('active', 'disabled', 'revoked')); 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')), + wire_api TEXT NOT NULL DEFAULT 'chat_completions' CHECK (wire_api IN ('chat_completions', 'responses', 'embeddings', 'messages')), base_url TEXT NOT NULL, api_key_ciphertext BYTEA NOT NULL, enabled BOOLEAN NOT NULL DEFAULT TRUE, @@ -90,10 +101,10 @@ ALTER TABLE providers ADD CONSTRAINT providers_slug_check CHECK (slug ~ '^[a-z0- 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 ADD CONSTRAINT providers_wire_api_check CHECK (wire_api IN ('chat_completions', 'responses', 'embeddings', '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 = 'openai' AND wire_api IN ('chat_completions', 'responses', 'embeddings')) OR (protocol = 'anthropic' AND wire_api = 'messages') ); @@ -204,6 +215,7 @@ CREATE TABLE IF NOT EXISTS model_routes ( ); CREATE INDEX IF NOT EXISTS api_keys_active_hash_idx ON api_keys (key_hash) WHERE status = 'active'; +CREATE INDEX IF NOT EXISTS api_keys_rotated_from_idx ON api_keys (rotated_from_id) WHERE rotated_from_id IS NOT NULL; 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; @@ -224,9 +236,21 @@ CREATE TABLE IF NOT EXISTS tenant_preferences ( 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), + spend_anomaly_enabled BOOLEAN NOT NULL DEFAULT TRUE, + spend_anomaly_multiplier BIGINT, + spend_anomaly_min_micros BIGINT, updated_at TIMESTAMPTZ NOT NULL DEFAULT now(), CHECK (default_model IS NULL OR fallback_model IS NULL OR default_model <> fallback_model) ); +ALTER TABLE tenant_preferences ADD COLUMN IF NOT EXISTS spend_anomaly_enabled BOOLEAN NOT NULL DEFAULT TRUE; +ALTER TABLE tenant_preferences ADD COLUMN IF NOT EXISTS spend_anomaly_multiplier BIGINT; +ALTER TABLE tenant_preferences ADD COLUMN IF NOT EXISTS spend_anomaly_min_micros BIGINT; +ALTER TABLE tenant_preferences DROP CONSTRAINT IF EXISTS tenant_preferences_spend_anomaly_multiplier_check; +ALTER TABLE tenant_preferences ADD CONSTRAINT tenant_preferences_spend_anomaly_multiplier_check + CHECK (spend_anomaly_multiplier IS NULL OR spend_anomaly_multiplier BETWEEN 2 AND 1000); +ALTER TABLE tenant_preferences DROP CONSTRAINT IF EXISTS tenant_preferences_spend_anomaly_min_check; +ALTER TABLE tenant_preferences ADD CONSTRAINT tenant_preferences_spend_anomaly_min_check + CHECK (spend_anomaly_min_micros IS NULL OR spend_anomaly_min_micros BETWEEN 0 AND 1000000000000000); CREATE TABLE IF NOT EXISTS billing_reservations ( request_id TEXT PRIMARY KEY, @@ -289,6 +313,7 @@ CREATE TABLE IF NOT EXISTS usage_events ( attempts INTEGER NOT NULL DEFAULT 0, started_at TIMESTAMPTZ NOT NULL, duration_ms BIGINT NOT NULL DEFAULT 0, + ttft_ms BIGINT NOT NULL DEFAULT 0 CHECK (ttft_ms >= 0), input_tokens BIGINT NOT NULL DEFAULT 0, output_tokens BIGINT NOT NULL DEFAULT 0, total_tokens BIGINT NOT NULL DEFAULT 0, @@ -299,15 +324,18 @@ CREATE TABLE IF NOT EXISTS usage_events ( uncollected_micros BIGINT NOT NULL DEFAULT 0, usage_reported BOOLEAN NOT NULL DEFAULT FALSE, metering_status TEXT NOT NULL DEFAULT 'not_billable' - CHECK (metering_status IN ('not_billable','reported','missing','upstream_failed')), + CHECK (metering_status IN ('not_billable','reported','missing','released_unmetered','upstream_failed')), created_at TIMESTAMPTZ NOT NULL DEFAULT now(), FOREIGN KEY (project_id, tenant_id) REFERENCES projects(id, tenant_id) ON DELETE RESTRICT ); ALTER TABLE usage_events ADD COLUMN IF NOT EXISTS usage_reported BOOLEAN NOT NULL DEFAULT FALSE; ALTER TABLE usage_events ADD COLUMN IF NOT EXISTS metering_status TEXT NOT NULL DEFAULT 'not_billable'; +ALTER TABLE usage_events ADD COLUMN IF NOT EXISTS ttft_ms BIGINT NOT NULL DEFAULT 0; +ALTER TABLE usage_events DROP CONSTRAINT IF EXISTS usage_events_ttft_ms_check; +ALTER TABLE usage_events ADD CONSTRAINT usage_events_ttft_ms_check CHECK (ttft_ms >= 0); ALTER TABLE usage_events DROP CONSTRAINT IF EXISTS usage_events_metering_status_check; ALTER TABLE usage_events ADD CONSTRAINT usage_events_metering_status_check - CHECK (metering_status IN ('not_billable','reported','missing','upstream_failed')); + CHECK (metering_status IN ('not_billable','reported','missing','released_unmetered','upstream_failed')); -- Usage persistence is independent from billing. Older installations created this -- foreign key, which prevented recording requests when prepaid billing was disabled. |