dd0c/products/01-llm-cost-router/migrations/001_init.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)
);