Max Mayfield
311a834228
- PostgreSQL schema: orgs, users, api_keys, provider_configs, routing_rules, cost_tables, feature_flags - TimescaleDB schema: request_events hypertable, hourly/daily continuous aggregates, compression, retention - docker-compose.yml: postgres, timescaledb, redis for local dev - README with quick start, architecture overview, pricing tiers - .env.example, .gitignore
109 lines
4.4 KiB
SQL
109 lines
4.4 KiB
SQL
-- dd0c/route V1 schema — PostgreSQL (config + auth)
|
|
|
|
-- Organizations
|
|
CREATE TABLE organizations (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
name TEXT NOT NULL,
|
|
slug TEXT NOT NULL UNIQUE,
|
|
tier TEXT NOT NULL DEFAULT 'free' CHECK (tier IN ('free', 'pro', 'enterprise')),
|
|
stripe_customer_id TEXT,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
-- Users (GitHub OAuth)
|
|
CREATE TABLE users (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
org_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
|
|
github_id BIGINT UNIQUE,
|
|
email TEXT NOT NULL,
|
|
name TEXT,
|
|
role TEXT NOT NULL DEFAULT 'member' CHECK (role IN ('owner', 'member', 'viewer')),
|
|
avatar_url TEXT,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
CREATE INDEX idx_users_org ON users(org_id);
|
|
CREATE INDEX idx_users_github ON users(github_id);
|
|
|
|
-- API Keys (proxy auth)
|
|
CREATE TABLE api_keys (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
org_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
|
|
key_prefix CHAR(8) NOT NULL, -- first 8 chars for fast lookup
|
|
key_hash TEXT NOT NULL, -- bcrypt hash of full key
|
|
name TEXT NOT NULL DEFAULT 'Default',
|
|
scopes TEXT[] NOT NULL DEFAULT '{"proxy"}',
|
|
last_used_at TIMESTAMPTZ,
|
|
revoked_at TIMESTAMPTZ,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
CREATE UNIQUE INDEX idx_api_keys_prefix ON api_keys(key_prefix) WHERE revoked_at IS NULL;
|
|
CREATE INDEX idx_api_keys_org ON api_keys(org_id);
|
|
|
|
-- Provider Configs (encrypted API keys for upstream providers)
|
|
CREATE TABLE provider_configs (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
org_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
|
|
provider TEXT NOT NULL, -- 'openai', 'anthropic', etc.
|
|
encrypted_api_key BYTEA NOT NULL, -- AES-256-GCM encrypted
|
|
base_url TEXT, -- custom endpoint override
|
|
is_default BOOLEAN NOT NULL DEFAULT false,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
UNIQUE(org_id, provider)
|
|
);
|
|
|
|
-- Routing Rules
|
|
CREATE TABLE routing_rules (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
org_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
|
|
priority INT NOT NULL DEFAULT 0,
|
|
name TEXT NOT NULL,
|
|
-- Match conditions
|
|
match_model TEXT, -- e.g. 'gpt-4o'
|
|
match_feature TEXT, -- X-DD0C-Feature header
|
|
match_team TEXT, -- X-DD0C-Team header
|
|
match_complexity TEXT CHECK (match_complexity IN ('low', 'medium', 'high')),
|
|
-- Routing action
|
|
strategy TEXT NOT NULL DEFAULT 'passthrough' CHECK (strategy IN ('passthrough', 'cheapest', 'quality-first', 'cascading')),
|
|
target_model TEXT, -- override model
|
|
target_provider TEXT, -- override provider
|
|
fallback_models TEXT[], -- for cascading strategy
|
|
enabled BOOLEAN NOT NULL DEFAULT true,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
CREATE INDEX idx_routing_rules_org ON routing_rules(org_id, priority);
|
|
|
|
-- Cost Tables (provider pricing, refreshed daily)
|
|
CREATE TABLE cost_tables (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
provider TEXT NOT NULL,
|
|
model TEXT NOT NULL,
|
|
input_cost_per_1k NUMERIC(10, 6) NOT NULL,
|
|
output_cost_per_1k NUMERIC(10, 6) NOT NULL,
|
|
effective_date DATE NOT NULL DEFAULT CURRENT_DATE,
|
|
UNIQUE(provider, model, effective_date)
|
|
);
|
|
|
|
-- Seed current pricing
|
|
INSERT INTO cost_tables (provider, model, input_cost_per_1k, output_cost_per_1k) VALUES
|
|
('openai', 'gpt-4o', 0.005000, 0.015000),
|
|
('openai', 'gpt-4o-mini', 0.000150, 0.000600),
|
|
('openai', 'gpt-4-turbo', 0.010000, 0.030000),
|
|
('openai', 'gpt-3.5-turbo', 0.000500, 0.001500),
|
|
('anthropic', 'claude-3-opus', 0.015000, 0.075000),
|
|
('anthropic', 'claude-3-sonnet', 0.003000, 0.015000),
|
|
('anthropic', 'claude-3-haiku', 0.000250, 0.001250),
|
|
('anthropic', 'claude-3.5-sonnet', 0.003000, 0.015000);
|
|
|
|
-- Feature flags (Transparent Factory: Atomic Flagging)
|
|
CREATE TABLE feature_flags (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
org_id UUID REFERENCES organizations(id) ON DELETE CASCADE, -- NULL = global
|
|
flag_key TEXT NOT NULL,
|
|
enabled BOOLEAN NOT NULL DEFAULT false,
|
|
rollout_pct INT NOT NULL DEFAULT 0 CHECK (rollout_pct BETWEEN 0 AND 100),
|
|
metadata JSONB NOT NULL DEFAULT '{}',
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
UNIQUE(org_id, flag_key)
|
|
);
|