deploy/sql/001_usage_budget.sql

-- Explicit migration for the server-only operational ledger. No prompts/answers.
BEGIN;
SELECT pg_advisory_xact_lock(5787785515774603124);
CREATE SCHEMA IF NOT EXISTS precedent_private;
REVOKE ALL ON SCHEMA precedent_private FROM PUBLIC;
CREATE TABLE IF NOT EXISTS precedent_private.quota_schema (
    version integer PRIMARY KEY CHECK (version = 1)
);
INSERT INTO precedent_private.quota_schema VALUES (1) ON CONFLICT DO NOTHING;
CREATE TABLE IF NOT EXISTS precedent_private.requests (
    owner_key varchar(32) NOT NULL CHECK (owner_key ~ '^[a-f0-9]{32}$'),
    request_id uuid NOT NULL,
    fingerprint varchar(64) NOT NULL CHECK (fingerprint ~ '^[a-f0-9]{64}$'),
    created_at timestamptz NOT NULL DEFAULT clock_timestamp(),
    finished_at timestamptz,
    status varchar(16) NOT NULL DEFAULT 'running'
        CHECK (status IN ('running', 'completed', 'failed')),
    response_status integer CHECK (response_status BETWEEN 100 AND 599),
    PRIMARY KEY (owner_key, request_id)
);
CREATE INDEX IF NOT EXISTS requests_created ON precedent_private.requests(created_at);
CREATE INDEX IF NOT EXISTS requests_owner_created ON precedent_private.requests(owner_key, created_at);
CREATE TABLE IF NOT EXISTS precedent_private.attempts (
    attempt_id uuid PRIMARY KEY,
    owner_key varchar(32) NOT NULL,
    request_id uuid NOT NULL,
    kind varchar(16) NOT NULL CHECK (kind IN ('provider', 'external')),
    provider varchar(64) NOT NULL,
    model varchar(128) NOT NULL,
    created_at timestamptz NOT NULL DEFAULT clock_timestamp(),
    finished_at timestamptz,
    reserved_tokens bigint NOT NULL CHECK (reserved_tokens >= 0),
    used_tokens bigint CHECK (used_tokens >= 0),
    prompt_tokens bigint CHECK (prompt_tokens >= 0),
    completion_tokens bigint CHECK (completion_tokens >= 0),
    status varchar(16) NOT NULL DEFAULT 'reserved'
        CHECK (status IN ('reserved', 'completed', 'unknown')),
    FOREIGN KEY (owner_key, request_id) REFERENCES precedent_private.requests
        ON DELETE CASCADE
);
CREATE INDEX IF NOT EXISTS attempts_kind_created ON precedent_private.attempts(kind, created_at);
CREATE INDEX IF NOT EXISTS attempts_owner_created ON precedent_private.attempts(owner_key, created_at);
REVOKE ALL ON ALL TABLES IN SCHEMA precedent_private FROM PUBLIC;
-- Supabase's API roles must not inherit table/default grants to this ledger.
-- Backend access uses the SQL connection role, never a browser API credential.
DO $$
DECLARE api_role text;
BEGIN
    FOREACH api_role IN ARRAY ARRAY['anon', 'authenticated', 'service_role'] LOOP
        IF EXISTS (SELECT 1 FROM pg_roles WHERE rolname = api_role) THEN
            EXECUTE format('REVOKE ALL ON SCHEMA precedent_private FROM %I', api_role);
            EXECUTE format('REVOKE ALL ON ALL TABLES IN SCHEMA precedent_private FROM %I', api_role);
        END IF;
    END LOOP;
END;
$$;
COMMIT;