119 lines
3.7 KiB
SQL
119 lines
3.7 KiB
SQL
-- Domain boundary for the first PostgreSQL adapter. Secrets and user files
|
|
-- are referenced by encrypted ids; they are never stored in static assets.
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id UUID PRIMARY KEY,
|
|
status TEXT NOT NULL,
|
|
display_name TEXT NOT NULL DEFAULT '',
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
CREATE TABLE IF NOT EXISTS user_identities (
|
|
id UUID PRIMARY KEY,
|
|
user_id UUID NOT NULL REFERENCES users(id),
|
|
kind TEXT NOT NULL,
|
|
normalized_value TEXT NOT NULL,
|
|
verified_at TIMESTAMPTZ,
|
|
UNIQUE(kind, normalized_value)
|
|
);
|
|
CREATE TABLE IF NOT EXISTS password_credentials (
|
|
user_id UUID PRIMARY KEY REFERENCES users(id),
|
|
password_hash TEXT NOT NULL,
|
|
algorithm_version TEXT NOT NULL,
|
|
changed_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
CREATE TABLE IF NOT EXISTS verification_codes (
|
|
id UUID PRIMARY KEY,
|
|
target_hash TEXT NOT NULL,
|
|
purpose TEXT NOT NULL,
|
|
code_hash TEXT NOT NULL,
|
|
expires_at TIMESTAMPTZ NOT NULL,
|
|
attempts INTEGER NOT NULL DEFAULT 0,
|
|
consumed_at TIMESTAMPTZ,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
CREATE TABLE IF NOT EXISTS sessions (
|
|
id UUID PRIMARY KEY,
|
|
subject_id UUID NOT NULL,
|
|
scope TEXT NOT NULL,
|
|
refresh_hash TEXT NOT NULL UNIQUE,
|
|
rotation_id UUID NOT NULL,
|
|
expires_at TIMESTAMPTZ NOT NULL,
|
|
revoked_at TIMESTAMPTZ,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
CREATE TABLE IF NOT EXISTS balance_accounts (
|
|
user_id UUID PRIMARY KEY REFERENCES users(id),
|
|
available NUMERIC(24, 6) NOT NULL DEFAULT 0,
|
|
reserved NUMERIC(24, 6) NOT NULL DEFAULT 0,
|
|
version BIGINT NOT NULL DEFAULT 0,
|
|
CHECK (available >= 0 AND reserved >= 0)
|
|
);
|
|
CREATE TABLE IF NOT EXISTS balance_ledger (
|
|
id UUID PRIMARY KEY,
|
|
user_id UUID NOT NULL REFERENCES users(id),
|
|
type TEXT NOT NULL,
|
|
amount NUMERIC(24, 6) NOT NULL,
|
|
unit_version INTEGER NOT NULL,
|
|
idempotency_key TEXT NOT NULL UNIQUE,
|
|
reference_id TEXT,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
CREATE TABLE IF NOT EXISTS model_products (
|
|
id TEXT PRIMARY KEY,
|
|
public_model_id TEXT NOT NULL UNIQUE,
|
|
tier TEXT NOT NULL,
|
|
enabled BOOLEAN NOT NULL DEFAULT false,
|
|
version INTEGER NOT NULL DEFAULT 1,
|
|
snapshot JSONB NOT NULL
|
|
);
|
|
CREATE TABLE IF NOT EXISTS provider_channels (
|
|
id TEXT PRIMARY KEY,
|
|
status TEXT NOT NULL,
|
|
encrypted_secret_ref TEXT NOT NULL,
|
|
base_url TEXT NOT NULL,
|
|
health JSONB NOT NULL DEFAULT '{}'::jsonb,
|
|
version INTEGER NOT NULL DEFAULT 1
|
|
);
|
|
CREATE TABLE IF NOT EXISTS channel_groups (
|
|
id TEXT PRIMARY KEY,
|
|
version INTEGER NOT NULL,
|
|
enabled BOOLEAN NOT NULL DEFAULT false,
|
|
channel_order JSONB NOT NULL
|
|
);
|
|
CREATE TABLE IF NOT EXISTS generation_tasks (
|
|
id UUID PRIMARY KEY,
|
|
user_id UUID NOT NULL REFERENCES users(id),
|
|
task_type TEXT NOT NULL,
|
|
status TEXT NOT NULL,
|
|
idempotency_key TEXT NOT NULL,
|
|
model_snapshot JSONB NOT NULL,
|
|
route_snapshot JSONB NOT NULL,
|
|
pricing_snapshot JSONB NOT NULL,
|
|
reserved_amount NUMERIC(24, 6) NOT NULL DEFAULT 0,
|
|
state_version BIGINT NOT NULL DEFAULT 0,
|
|
lease_expires_at TIMESTAMPTZ,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
UNIQUE(user_id, idempotency_key)
|
|
);
|
|
CREATE TABLE IF NOT EXISTS generation_attempts (
|
|
id UUID PRIMARY KEY,
|
|
task_id UUID NOT NULL REFERENCES generation_tasks(id),
|
|
channel_id TEXT NOT NULL,
|
|
sequence INTEGER NOT NULL,
|
|
status TEXT NOT NULL,
|
|
provider_request_id TEXT,
|
|
error_code TEXT,
|
|
started_at TIMESTAMPTZ NOT NULL,
|
|
finished_at TIMESTAMPTZ
|
|
);
|
|
CREATE TABLE IF NOT EXISTS audit_logs (
|
|
id UUID PRIMARY KEY,
|
|
actor_id UUID,
|
|
action TEXT NOT NULL,
|
|
object_type TEXT NOT NULL,
|
|
object_id TEXT,
|
|
request_id TEXT NOT NULL,
|
|
payload_digest TEXT NOT NULL,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|