254 lines
12 KiB
PL/PgSQL
254 lines
12 KiB
PL/PgSQL
-- Core gateway schema: identities, providers, the discovered model catalog,
|
|
-- curated aliases, virtual keys + allowlists, and the request log.
|
|
|
|
-- Shared trigger to keep updated_at columns current on UPDATE.
|
|
CREATE OR REPLACE FUNCTION set_updated_at()
|
|
RETURNS trigger AS $$
|
|
BEGIN
|
|
NEW.updated_at = now();
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
-- ===========================================================================
|
|
-- Identity
|
|
-- ===========================================================================
|
|
|
|
-- Provisioned lazily from OIDC (Authentik) on first login/exchange. Admin
|
|
-- status is NOT stored here: it is derived live from the JWT groups claim
|
|
-- against GATEWAY_OIDC_ADMIN_GROUP.
|
|
CREATE TABLE users (
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
subject text NOT NULL UNIQUE, -- OIDC `sub`
|
|
email text,
|
|
name text,
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
|
last_login_at timestamptz
|
|
);
|
|
CREATE INDEX idx_users_email ON users (email);
|
|
|
|
-- OIDC groups the gateway knows about. Membership is dynamic (read from the
|
|
-- JWT); this table attaches allowlists/budgets to a group so session keys
|
|
-- minted via /auth/exchange can be scoped.
|
|
CREATE TABLE groups (
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
name text NOT NULL UNIQUE, -- matches the OIDC group name
|
|
description text,
|
|
allow_all_models boolean NOT NULL DEFAULT false,
|
|
monthly_budget numeric(14,6), -- NULL = unlimited
|
|
rpm_limit integer,
|
|
tpm_limit integer,
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
|
updated_at timestamptz NOT NULL DEFAULT now()
|
|
);
|
|
CREATE TRIGGER trg_groups_updated_at BEFORE UPDATE ON groups
|
|
FOR EACH ROW EXECUTE FUNCTION set_updated_at();
|
|
|
|
-- ===========================================================================
|
|
-- Providers and the discovered model catalog
|
|
-- ===========================================================================
|
|
|
|
-- A configured upstream backend instance. Its `name` is the addressing prefix:
|
|
-- clients reference a model as "<provider name>/<slug>". Credentials are
|
|
-- AES-256-GCM encrypted by the app (nonce || ciphertext) using
|
|
-- GATEWAY_SECURITY_ENCRYPTION_KEY; the database never sees plaintext.
|
|
CREATE TABLE providers (
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
-- "any" is reserved as the cross-provider routing prefix (any/<slug>).
|
|
name text NOT NULL UNIQUE CHECK (lower(name) <> 'any'),
|
|
type text NOT NULL CHECK (type IN ('llamacpp','ollama','openai','openrouter','anthropic','github_copilot')),
|
|
base_url text,
|
|
credentials bytea,
|
|
enabled boolean NOT NULL DEFAULT true,
|
|
config jsonb NOT NULL DEFAULT '{}'::jsonb,
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
|
updated_at timestamptz NOT NULL DEFAULT now()
|
|
);
|
|
CREATE TRIGGER trg_providers_updated_at BEFORE UPDATE ON providers
|
|
FOR EACH ROW EXECUTE FUNCTION set_updated_at();
|
|
|
|
-- The model library: one row per (provider, upstream model slug), built at
|
|
-- runtime by syncing each provider's model listing and refreshed on a timer.
|
|
-- `disc_*` columns are owned by the syncer; the admin override columns
|
|
-- (enabled/blocked/param_overrides/price_*/limits) are owned by the dashboard
|
|
-- and are never clobbered by a sync. Effective price = COALESCE(override,
|
|
-- discovered, 0). `source = 'manual'` rows are admin-created (providers that
|
|
-- don't list, or custom entries) and are not touched by discovery.
|
|
CREATE TABLE provider_models (
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
provider_id uuid NOT NULL REFERENCES providers (id) ON DELETE CASCADE,
|
|
slug text NOT NULL, -- upstream model id
|
|
display_name text,
|
|
capabilities text[] NOT NULL DEFAULT ARRAY['chat'],
|
|
context_length integer,
|
|
source text NOT NULL DEFAULT 'discovered' CHECK (source IN ('discovered','manual')),
|
|
available boolean NOT NULL DEFAULT true, -- seen in the most recent sync
|
|
last_seen_at timestamptz,
|
|
raw jsonb NOT NULL DEFAULT '{}'::jsonb, -- raw provider listing entry
|
|
|
|
-- Discovered pricing (per 1M tokens); NULL when the provider does not report it.
|
|
disc_input_price_per_mtok numeric(12,6),
|
|
disc_output_price_per_mtok numeric(12,6),
|
|
|
|
-- Admin overrides.
|
|
enabled boolean NOT NULL DEFAULT true,
|
|
blocked boolean NOT NULL DEFAULT false,
|
|
param_overrides jsonb NOT NULL DEFAULT '{}'::jsonb,
|
|
price_input_per_mtok numeric(12,6),
|
|
price_output_per_mtok numeric(12,6),
|
|
price_cached_per_mtok numeric(12,6),
|
|
rpm_limit integer,
|
|
tpm_limit integer,
|
|
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
|
updated_at timestamptz NOT NULL DEFAULT now(),
|
|
UNIQUE (provider_id, slug)
|
|
);
|
|
CREATE INDEX idx_provider_models_provider ON provider_models (provider_id);
|
|
CREATE INDEX idx_provider_models_servable ON provider_models (enabled, blocked, available);
|
|
CREATE TRIGGER trg_provider_models_updated_at BEFORE UPDATE ON provider_models
|
|
FOR EACH ROW EXECUTE FUNCTION set_updated_at();
|
|
|
|
-- ===========================================================================
|
|
-- Curated aliases (optional, admin-defined)
|
|
-- ===========================================================================
|
|
|
|
-- A friendly client-facing alias (e.g. "gpt-fast") that the gateway exposes
|
|
-- alongside direct "<provider>/<slug>" addressing.
|
|
CREATE TABLE models (
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
name text NOT NULL UNIQUE,
|
|
display_name text,
|
|
type text NOT NULL DEFAULT 'chat' CHECK (type IN ('chat','completion','embedding')),
|
|
enabled boolean NOT NULL DEFAULT true,
|
|
params jsonb NOT NULL DEFAULT '{}'::jsonb, -- default/override sampling params
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
|
updated_at timestamptz NOT NULL DEFAULT now()
|
|
);
|
|
CREATE TRIGGER trg_models_updated_at BEFORE UPDATE ON models
|
|
FOR EACH ROW EXECUTE FUNCTION set_updated_at();
|
|
|
|
-- The catalog entries an alias routes to. Multiple targets enable failover
|
|
-- ("local first, cloud backup"): lower priority wins, weight balances ties.
|
|
CREATE TABLE model_targets (
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
model_id uuid NOT NULL REFERENCES models (id) ON DELETE CASCADE,
|
|
provider_model_id uuid NOT NULL REFERENCES provider_models (id) ON DELETE CASCADE,
|
|
priority integer NOT NULL DEFAULT 100, -- lower = preferred
|
|
weight integer NOT NULL DEFAULT 1,
|
|
enabled boolean NOT NULL DEFAULT true,
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
|
updated_at timestamptz NOT NULL DEFAULT now(),
|
|
UNIQUE (model_id, provider_model_id)
|
|
);
|
|
CREATE INDEX idx_model_targets_model ON model_targets (model_id, priority);
|
|
CREATE INDEX idx_model_targets_provider_model ON model_targets (provider_model_id);
|
|
CREATE TRIGGER trg_model_targets_updated_at BEFORE UPDATE ON model_targets
|
|
FOR EACH ROW EXECUTE FUNCTION set_updated_at();
|
|
|
|
-- ===========================================================================
|
|
-- Virtual keys and allowlists
|
|
-- ===========================================================================
|
|
|
|
CREATE TABLE virtual_keys (
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
name text,
|
|
key_hash bytea NOT NULL UNIQUE, -- sha256(raw key)
|
|
key_prefix text NOT NULL, -- e.g. "sk-a1b2c3" for display
|
|
type text NOT NULL DEFAULT 'standard' CHECK (type IN ('standard','session')),
|
|
user_id uuid REFERENCES users (id) ON DELETE CASCADE,
|
|
group_id uuid REFERENCES groups (id) ON DELETE SET NULL,
|
|
allow_all_models boolean NOT NULL DEFAULT false,
|
|
monthly_budget numeric(14,6),
|
|
rpm_limit integer,
|
|
tpm_limit integer,
|
|
created_by uuid REFERENCES users (id) ON DELETE SET NULL,
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
|
last_used_at timestamptz,
|
|
expires_at timestamptz,
|
|
revoked_at timestamptz
|
|
);
|
|
CREATE INDEX idx_virtual_keys_user ON virtual_keys (user_id);
|
|
CREATE INDEX idx_virtual_keys_group ON virtual_keys (group_id);
|
|
CREATE INDEX idx_virtual_keys_active ON virtual_keys (revoked_at, expires_at);
|
|
|
|
-- Per-key allowlist rules (ignored when allow_all_models = true). A pattern is
|
|
-- an exact address ("openrouter/openai/gpt-4o") or a provider wildcard
|
|
-- ("openrouter/*").
|
|
CREATE TABLE virtual_key_model_rules (
|
|
virtual_key_id uuid NOT NULL REFERENCES virtual_keys (id) ON DELETE CASCADE,
|
|
pattern text NOT NULL,
|
|
PRIMARY KEY (virtual_key_id, pattern)
|
|
);
|
|
|
|
-- Per-group allowlist rules; the scope source for session keys.
|
|
CREATE TABLE group_model_rules (
|
|
group_id uuid NOT NULL REFERENCES groups (id) ON DELETE CASCADE,
|
|
pattern text NOT NULL,
|
|
PRIMARY KEY (group_id, pattern)
|
|
);
|
|
|
|
-- ===========================================================================
|
|
-- Request log (billing source of truth; written async after the response)
|
|
-- ===========================================================================
|
|
|
|
CREATE TABLE request_logs (
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
|
|
|
-- Soft references: keep logs even after the referent is deleted.
|
|
virtual_key_id uuid REFERENCES virtual_keys (id) ON DELETE SET NULL,
|
|
user_id uuid REFERENCES users (id) ON DELETE SET NULL,
|
|
model_id uuid REFERENCES models (id) ON DELETE SET NULL, -- alias, if one was used
|
|
provider_model_id uuid REFERENCES provider_models (id) ON DELETE SET NULL, -- catalog entry served
|
|
provider_id uuid REFERENCES providers (id) ON DELETE SET NULL,
|
|
|
|
-- Snapshots so a row stays meaningful after deletes.
|
|
key_prefix text,
|
|
model_name text, -- the address the client requested
|
|
provider_name text,
|
|
|
|
-- Outcome.
|
|
status_code integer,
|
|
streamed boolean NOT NULL DEFAULT false,
|
|
finish_reason text,
|
|
error text,
|
|
|
|
-- Usage and cost (snapshotted at request time).
|
|
prompt_tokens integer NOT NULL DEFAULT 0,
|
|
completion_tokens integer NOT NULL DEFAULT 0,
|
|
cached_tokens integer NOT NULL DEFAULT 0,
|
|
total_tokens integer NOT NULL DEFAULT 0,
|
|
cost numeric(14,6) NOT NULL DEFAULT 0,
|
|
currency char(3) NOT NULL DEFAULT 'USD',
|
|
|
|
-- Timing.
|
|
latency_ms integer,
|
|
ttft_ms integer, -- time to first token (streaming)
|
|
|
|
-- Request metadata.
|
|
request_id text,
|
|
client_ip inet
|
|
);
|
|
CREATE INDEX idx_request_logs_created ON request_logs (created_at DESC);
|
|
CREATE INDEX idx_request_logs_key ON request_logs (virtual_key_id, created_at DESC);
|
|
CREATE INDEX idx_request_logs_user ON request_logs (user_id, created_at DESC);
|
|
CREATE INDEX idx_request_logs_provider_model ON request_logs (provider_model_id, created_at DESC);
|
|
|
|
-- ===========================================================================
|
|
-- Global settings
|
|
-- ===========================================================================
|
|
|
|
-- Dashboard-editable key/value settings. Notably the routing strategy used to
|
|
-- pick among candidates for an "any/<slug>" address (or a multi-target alias):
|
|
-- round_robin | lowest_cost | priority.
|
|
CREATE TABLE settings (
|
|
key text PRIMARY KEY,
|
|
value jsonb NOT NULL,
|
|
updated_at timestamptz NOT NULL DEFAULT now()
|
|
);
|
|
CREATE TRIGGER trg_settings_updated_at BEFORE UPDATE ON settings
|
|
FOR EACH ROW EXECUTE FUNCTION set_updated_at();
|
|
|
|
INSERT INTO settings (key, value) VALUES ('routing.strategy', '"round_robin"');
|