Router.Africa — Database Schema
Backlog item #6. Column-level spec for the ledger cluster, an ER overview, and how the schema supports the hold/settle hot path. It implements decisions already in decisions-log.md and backend-scope.md; where they disagree with this file, they win. It is a spec, not DDL: real migrations are written in packages/db during the apps/api build.
Terms follow backend-scope.md: an org owns members and roles; a workspace has its own currency, balance, holds, settlements and keys. “Account” in the decisions log means the workspace wherever balance, hold or spend is involved.
Section 8 lists the small choices this doc makes and the follow-ups that came out of writing it. Everything else restates decisions.
Gateway-neutral (revised 2026-10-25): the gateway is swappable (decisions log, Gateway abstraction). At launch there is exactly one gateway; the tables below make a second one an addition, not a migration. Tables and columns use gateway names; where the text says Bifrost, read “the gateway, currently Bifrost”.
1. Conventions
Section titled “1. Conventions”- IDs:
uuid, generated by the app as v7 so they sort by time. The request id is the same uuid everywhere (hold, settlement, log row, R2 key). - Time:
timestamptz, UTC. Every table that records an entity or an event hascreated_at NOT NULL, and mutable config tables also haveupdated_at. Pure join, counter and state tables (api_key_models,key_spend_windows,workspace_balances,current_rates,scheduled_jobs) carry only the columns listed for them. - Money and rates:
numeric, never float. Creditsnumeric(24,8); per-token rates and USD costsnumeric(20,12), since a per-token rate is a tiny fraction of a credit. - Enums:
textwith aCHECK, not Postgres enum types, so adding a value is a one-line migration. - Deletes: money and log rows are never deleted. Config rows are disabled or soft-deleted (
status/deleted_at). - Better Auth tables are specified in 4.0. Every
user_idin this doc referencesusers.id.
2. Where things live (decided)
Section titled “2. Where things live (decided)”Recorded in the decisions log (Ledger database, Analytics/dashboard read store); restated so the table lists below have a home.
| Schema | In it | Written by |
|---|---|---|
core |
Better Auth tables (users, sessions, accounts, verifications, two_factors, passkeys, rate_limits), tenancy, keys, models, pricing config, balances, holds, settlements, ledger entries, idempotency, top-ups, transfers, gateway discrepancies |
apps/api (hold/settle path), apps/dashboard-api (everything else, and the only writer of the Better Auth tables; apps/api never reads the login tables, and reads organizations only for hold_enabled, status and max_in_flight) |
logs |
Request log, audit events, rollups | Queue consumer in apps/dashboard-api, through its store interface, on its own Hyperdrive config. Privileged audit events are written directly (see 8.3) |
| R2 | Prompt and response content | apps/api, after the response; pointer on the request-log row |
Database roles (decided 2026-10-26): each writer connects with its own role and Hyperdrive config: api (the hot-path tables only, no Better Auth tables), dashboard (core), consumer (logs), and migrator (schema changes, CI only). Grants are written as migrations.
The seven rules for logs (time-bounded queries, created_at in every key, no foreign keys to core, append-only, rollups for aggregates, store interface, separate connection budget) are applied in section 4 and checked in section 6.
3. ER overview
Section titled “3. ER overview”Relationships only; columns are in section 4. Split into four diagrams so each stays readable; the last one shows the logs schema, where dotted lines are plain id columns with no foreign key. Rendered PNGs are in diagrams/ for viewers that don’t render Mermaid (VS Code needs the Markdown Preview Mermaid Support extension; GitHub renders it natively).
3.1 Tenancy and keys
Section titled “3.1 Tenancy and keys”erDiagram
ORGANIZATIONS ||--o{ ORG_MEMBERS : has
ORGANIZATIONS ||--o{ INVITATIONS : issues
ORGANIZATIONS ||--o{ WORKSPACES : owns
USERS ||--o{ SESSIONS : "signed in as"
USERS ||--o{ ACCOUNTS : "logs in with"
USERS ||--o| TWO_FACTORS : "secures with"
USERS ||--o{ PASSKEYS : "registers"
USERS ||--o{ ORG_MEMBERS : "joins via"
USERS ||--o{ WORKSPACE_MEMBERS : "added to"
WORKSPACES ||--o{ WORKSPACE_MEMBERS : "limits members to"
CURRENCIES ||--o{ WORKSPACES : "priced in"
WORKSPACES ||--o{ API_KEYS : has
USERS |o--o{ API_KEYS : owns
API_KEYS ||--o{ API_KEY_MODELS : allows
MODELS ||--o{ API_KEY_MODELS : "allowed on"
API_KEYS ||--o{ KEY_SPEND_WINDOWS : counts
API_KEYS ||--o{ API_KEY_GATEWAY_CREDENTIALS : "authenticates via"
GATEWAYS ||--o{ API_KEY_GATEWAY_CREDENTIALS : issues
API_KEYS |o--o{ GATEWAY_DISCREPANCIES : "may have"
GATEWAYS ||--o{ GATEWAY_DISCREPANCIES : "found on"
3.2 Pricing
Section titled “3.2 Pricing”erDiagram
MODELS ||--o{ PUBLISHED_RATES : "priced by"
MODELS ||--o{ CURRENT_RATES : "current price"
CURRENCIES ||--o{ CURRENT_RATES : "priced in"
MARKUP_RULES |o--o{ PUBLISHED_RATES : "applied in"
WORKSPACES ||--o{ WORKSPACE_DISCOUNTS : gets
MODELS |o--o{ WORKSPACE_DISCOUNTS : "narrowed to"
PAYMENT_VENDORS ||--o{ PAYMENT_VENDOR_FEES : charges
CURRENCIES ||--o{ PAYMENT_VENDOR_FEES : "in"
3.3 Money
Section titled “3.3 Money”erDiagram
WORKSPACES ||--|| WORKSPACE_BALANCES : "has one"
WORKSPACES ||--o{ HOLDS : reserves
HOLDS |o--o| SETTLEMENTS : "closed by"
WORKSPACES ||--o{ SETTLEMENTS : charged
WORKSPACES ||--o{ LEDGER_ENTRIES : "balance changes"
WORKSPACES ||--o{ TOPUPS : funds
PAYMENT_VENDORS ||--o{ TOPUPS : collects
ORGANIZATIONS ||--o{ TRANSFERS : records
WORKSPACES ||--o{ TRANSFERS : "moves credits between"
WORKSPACES ||--o{ IDEMPOTENCY_KEYS : claims
API_KEYS |o--o{ USAGE_DISCREPANCIES : "may have"
HOLDS |o--o| USAGE_DISCREPANCIES : "open hold"
3.4 Logs schema
Section titled “3.4 Logs schema”erDiagram
REQUEST_LOGS }o..|| WORKSPACES : "workspace_id"
REQUEST_LOGS }o..o| API_KEYS : "key_id"
REQUEST_LOGS }o..o| MODELS : "model_id"
AUDIT_EVENTS }o..o| ORGANIZATIONS : "org_id"
AUDIT_EVENTS }o..o| REQUEST_LOGS : "content reads"
REQUEST_LOGS ||..o{ USAGE_HOURLY : "rolled up into"
REQUEST_LOGS ||..o{ CONTENT_STORAGE_DAILY : "bytes counted in"
4. Tables
Section titled “4. Tables”Notation: PK, FK, UQ unique. Indexes are listed under each table; anything not listed is not needed yet.
4.0 core — Better Auth (dashboard login)
Section titled “4.0 core — Better Auth (dashboard login)”apps/dashboard-api runs Better Auth with the plugins listed below; the main ones for the schema are admin (staff roles, ban, revoke sessions, impersonation), organization (orgs, members, invitations, teams off), two-factor and passkey. These tables are theirs; we own the names, types and migrations.
How they’re set up
- Migrations are ours. The Drizzle schema in
packages/dbis hand-written and its migrations are generated withdrizzle-kit. Don’t run Better Auth’s runtimemigrate. That is how the ids, names and constraints below stay under our control. Verified 2026-10-26 (P1.01):@better-auth/cli generateisn’t published for 1.7.x (the CLI stops at 1.5 betas), so a test inapps/dashboard-api(better-auth-schema.test.ts) asks the configured Better Auth instance for its expected tables (getAuthTablesfrombetter-auth/db) and fails on any missing table, missing field, or required field that is nullable. It runs on every PR, so enabling a plugin or upgrading Better Auth can’t silently leave a column out. - Naming: plural, snake_case, in the
coreschema (modelNameandfieldsmappings in the Better Auth config). - Ids:
uuidv7, from a customadvanced.database.generateIdcallback, to match every other table (Better Auth defaults to random strings, which would make each foreign key below atext). - Login methods: email and password, plus Google, GitHub and GitLab social login. Account linking is on: signing in with a second provider that has the same verified email joins the existing user instead of creating a duplicate. Only Google is a
trustedProvider; for GitHub and GitLab the provider must report the email as verified, otherwise someone could take over an account by registering the victim’s address, unverified, at a provider. - Plugins enabled: admin (including impersonation), organization, one-time-token (hands an impersonation session across origins, see below), two-factor (authenticator app and backup codes), passkey, haveIBeenPwned (rejects known-breached passwords at signup and reset), captcha (Cloudflare Turnstile on signup, login and password reset), lastLoginMethod (remembers the last method used, no schema). Two-factor is optional for customers and required for staff: the admin middleware refuses
supportandadminusers untiltwo_factor_enabledis true. - Not enabled, on purpose: bearer, JWT and API-key plugins (each adds a second credential path; the session cookie is the only valid dashboard credential, and API keys are our own
api_keys), teams (workspaces are ours), SSO/SAML and the OIDC provider (deferred in the build inventory), anonymous, phone and magic-link or email-OTP login (no need with the methods above). - Hardening rules for the cookie and session (names, attributes, lifetimes, step-up, tests) are in
auth-security.md. - Column lists below are what the generator emits at the time of writing; diff them against the generated SQL for the pinned version when writing the migration.
users
| Column | Type | Notes |
|---|---|---|
| id | uuid | PK |
| name | text NOT NULL | |
| text NOT NULL | UQ. Better Auth stores it lowercased | |
| email_verified | boolean NOT NULL DEFAULT false | |
| image | text NULL | |
| two_factor_enabled | boolean NOT NULL DEFAULT false | Two-factor plugin. Must be true for staff |
| role | text NOT NULL DEFAULT ‘user’ | Admin plugin. CHECK user, support, admin. user is every customer. support and admin are Router.Africa staff, the staff flag, separate from org roles. Admin routes check this, plus the session audience, ban status and two-factor (auth-security.md 3.2) |
| banned | boolean NOT NULL DEFAULT false | Admin plugin |
| ban_reason | text NULL | |
| ban_expires | timestamptz NULL | |
| created_at, updated_at | timestamptz |
Indexes: UQ (email); partial (role) WHERE role <> 'user' (the Staff screen).
sessions
| Column | Type | Notes |
|---|---|---|
| id | uuid | PK |
| user_id | uuid FK NOT NULL | ON DELETE CASCADE |
| token | text NOT NULL | UQ. The session credential. The only valid auth path in the dashboard (decisions log, Dashboard auth model) |
| expires_at | timestamptz NOT NULL | |
| ip_address, user_agent | text NULL | |
| audience | text NOT NULL DEFAULT ‘app’ | Our addition (session additionalFields, set from the login origin). CHECK app, admin. Verified on every request, so a session minted for one origin is refused on the other even if the cookie value is copied across (auth-security.md 3.2). Impersonation sessions are always app |
| active_organization_id | uuid NULL | Organization plugin: the org currently selected |
| impersonated_by | uuid NULL | Admin plugin. The staff admin viewing as this user; NULL for every normal session (see Impersonation below) |
| created_at, updated_at | timestamptz |
Indexes: UQ (token); (user_id) (support’s “force logout” revokes every session for a user); (expires_at) for cleanup.
accounts — one row per way a user can log in.
| Column | Type | Notes |
|---|---|---|
| id | uuid | PK |
| user_id | uuid FK NOT NULL | ON DELETE CASCADE |
| provider_id | text NOT NULL | credential for email and password, otherwise google, github or gitlab |
| account_id | text NOT NULL | The provider’s id for the user; for credential, the user’s own id |
| password | text NULL | Password hash, only for credential |
| access_token, refresh_token, id_token | text NULL | Sensitive. We don’t call provider APIs after login, so request only profile and email scopes; if the pinned version supports encrypting stored OAuth tokens, turn it on |
| access_token_expires_at, refresh_token_expires_at | timestamptz NULL | |
| scope | text NULL | |
| created_at, updated_at | timestamptz |
Indexes: UQ (provider_id, account_id); (user_id).
verifications — email verification and password-reset tokens. id uuid PK, identifier text NOT NULL, value text NOT NULL, expires_at timestamptz NOT NULL, created_at, updated_at. Indexes: (identifier); (expires_at) for cleanup.
two_factors — one row per user who has set up an authenticator app.
| Column | Type | Notes |
|---|---|---|
| id | uuid | PK |
| user_id | uuid FK NOT NULL | UQ. ON DELETE CASCADE |
| secret | text NOT NULL | The TOTP secret, stored encrypted by the plugin. Sensitive |
| backup_codes | text NOT NULL | Encrypted JSON list. Sensitive |
| verified | boolean NOT NULL DEFAULT true | Present in 1.7.7: false until the first code is confirmed |
| failed_verification_count | integer NOT NULL DEFAULT 0 | Present in 1.7.7 (found 2026-10-26): consecutive wrong codes, for lockout |
| locked_until | timestamptz NULL | Present in 1.7.7: two-factor locked after too many wrong codes |
passkeys — WebAuthn credentials; a user can register several.
| Column | Type | Notes |
|---|---|---|
| id | uuid | PK |
| user_id | uuid FK NOT NULL | ON DELETE CASCADE |
| name | text NULL | The label the user gave it |
| credential_id | text NOT NULL | UQ |
| public_key | text NOT NULL | |
| counter | integer NOT NULL | Replay protection |
| device_type | text NOT NULL | singleDevice or multiDevice |
| backed_up | boolean NOT NULL | |
| transports | text NULL | |
| aaguid | text NULL | |
| created_at | timestamptz |
Indexes: (user_id). Both tables’ exact columns follow the pinned plugin version, as with the core four; the drift test enforces it. Passkey packaging (1.7.7): the passkey plugin is a separate package, @better-auth/passkey. Its loose peer range pulls a newer @better-auth/core, so pin @better-auth/core to the Better Auth version with an npm overrides entry when adding it (P5.02). The plugin indexes credential_id but doesn’t make it unique; keep our UQ.
user_consents — added with the first auth PR (2026-10-26). Append-only record of a user accepting the terms of service and privacy policy; the sign-up checkbox covers both, so one row each. A user with no row for the current terms version is sent to an accept-terms screen (covers Google sign-ups and terms changes). Separate from the per-workspace content-logging consent (4.1).
| Column | Type | Notes |
|---|---|---|
| id | uuid | PK |
| user_id | uuid FK NOT NULL | |
| kind | text NOT NULL | CHECK terms, privacy |
| version | text NOT NULL | Terms version accepted (TERMS_VERSION in apps/dashboard-api) |
| accepted_at | timestamptz NOT NULL DEFAULT now() | |
| ip_address, user_agent | text NULL | From CF-Connecting-IP and the request |
Indexes: (user_id, kind, accepted_at DESC).
rate_limits — Better Auth’s own limiter for login and signup endpoints, kept in the database because Worker isolates don’t share memory (rateLimit.storage: 'database'). id uuid PK, key text NOT NULL (UQ), count integer NOT NULL, last_request bigint NOT NULL (ms). This limits login attempts only; API rate limits are Bifrost’s, per the decisions log.
Impersonation (admin “view as customer”)
A staff admin can open the customer dashboard as a customer to see what they see. The plugin creates a normal sessions row for the customer with impersonated_by set to the staff user. Guardrails (8.2 item 1):
- Who: only
admin(notsupport), only on customers (role = 'user'), never on other staff. The staff user’s two-factor requirement still applies. - Reason required, recorded in the audit event. Sessions last 30 minutes (
impersonationSessionDuration); staff can end one earlier. - Read-only. While a session has
impersonated_byset,apps/dashboard-apirejects every non-GET route and every stored-content read. Staff use the admin screens for anything that changes data. - Always visible: the dashboard shows a “Viewing as
” banner with an exit button. - Audited:
impersonation.startedandimpersonation.endedevents, and every audit event written during the session carriesimpersonated_by, so an action is never attributed to the customer alone. - Cross-origin hand-off (settle at build): the admin app (
admin.router.africa) and the dashboard (app.router.africa) have separate session cookies by design, and the plugin sets its cookie on the origin that calls it. So the admin backend mints a single-use token (one-time-token plugin, stored inverifications) and the browser exchanges it for the impersonation session onapp.router.africa. Shared cookies across.router.africaare not the answer; they would undo the origin separation.
Expired sessions and verifications are deleted by the daily ledger invariant check job, which also does this housekeeping (8.3).
4.1 core — tenancy
Section titled “4.1 core — tenancy”organizations — the organization plugin’s table, extended with our fields through its additionalFields.
| Column | Type | Notes |
|---|---|---|
| id | uuid | PK |
| name | text NOT NULL | |
| slug | text NOT NULL | UQ. Required by the plugin; generated from the name |
| logo | text NULL | Plugin field, unused for now |
| metadata | jsonb NULL | Plugin field, unused for now |
| hold_enabled | boolean NOT NULL DEFAULT true | Per-org hold toggle. Read on the request path through the workspace’s org |
| rate_limit_requests | integer NULL | Per key, copied onto each virtual key. NULL = none |
| rate_limit_tokens | bigint NULL | |
| rate_limit_window_seconds | integer NULL | CHECK not null when either limit is set. Exact shape follows Bifrost’s governance API; confirm when building the sync |
| max_in_flight | integer NOT NULL DEFAULT 20 | CHECK > 0. Most requests the org may have running at once, across all its workspaces and keys, holds on or off (0016, code #107). Over it: 429 concurrency_limited. Staff change it per org (1–10,000), audit event in_flight_cap.changed |
| status | text NOT NULL DEFAULT ‘active’ | CHECK active, suspended. Read on the request path with hold_enabled; a suspended org’s keys fail auth (decisions log, Org suspension) |
| suspended_at, suspended_by | timestamptz, uuid NULL | Set by staff; the reason is on the audit event |
| created_at, updated_at | timestamptz | |
| deleted_at | timestamptz NULL |
org_members — the plugin’s member table.
| Column | Type | Notes |
|---|---|---|
| id | uuid | PK |
| org_id | uuid FK NOT NULL | |
| user_id | uuid FK NOT NULL | |
| role | text NOT NULL | CHECK owner, admin, member. One role only; the plugin allows comma-separated roles and we don’t use that |
| created_at | timestamptz |
Indexes: UQ (org_id, user_id); (user_id); partial UQ (org_id) WHERE role = 'owner' (exactly one owner).
invitations — the plugin’s invitation table plus our workspace list.
| Column | Type | Notes |
|---|---|---|
| id | uuid | PK. Also the token in the invite link; the plugin only lets a signed-in user whose email matches email accept it |
| org_id | uuid FK NOT NULL | |
| text NOT NULL | ||
| role | text NOT NULL | CHECK admin, member |
| status | text NOT NULL | The plugin’s values: pending, accepted, rejected, canceled (canceled = revoked; “expired” is expires_at in the past) |
| workspace_ids | uuid[] NOT NULL DEFAULT ‘{}’ | Our addition. Workspaces a member invite grants; ignored for admins. Applied to workspace_members on accept |
| inviter_id | uuid FK NOT NULL | |
| expires_at | timestamptz NOT NULL | |
| created_at | timestamptz |
Indexes: partial UQ (org_id, lower(email)) WHERE status = 'pending'.
currencies
| Column | Type | Notes |
|---|---|---|
| code | char(3) | PK |
| name | text NOT NULL | |
| enabled | boolean NOT NULL DEFAULT false | Admin enables supported currencies |
| min_hold | numeric(24,8) NOT NULL | Minimum hold threshold in credits. Per currency because 1 credit is 1 unit of the currency |
| fx_rate_basis | text NOT NULL DEFAULT ‘official’ | CHECK official, parallel_usd_cost. Which fx_rates value prices this currency (decisions log, FX providers and rate basis, 2026-11-02) |
| fx_providers | text[] NOT NULL DEFAULT ‘{mansa}’ | Provider order for the FX job, first valid wins. Values mansa, frankfurter; empty = manual entry only (SLE). Manual rows aren’t listed: they always win |
| rate_sig_figs | smallint NOT NULL DEFAULT 3 | CHECK 1–6. Published rates round up the price per 1M tokens to this many significant figures (pricing-model.md, Rounding; migration 0006, 2026-11-01) |
| rate_rounding_step | numeric(20,12) NOT NULL | Deprecated (2026-11-01): the old fixed rounding step, replaced by rate_sig_figs. Kept because test fixtures on open branches still write it; a later migration drops it |
workspaces
| Column | Type | Notes |
|---|---|---|
| id | uuid | PK |
| org_id | uuid FK NOT NULL | |
| name | text NOT NULL | |
| currency | char(3) FK NOT NULL | |
| currency_locked_at | timestamptz NULL | Set on the first successful top-up; the currency can’t change once set |
| status | text NOT NULL DEFAULT ‘active’ | CHECK active, frozen, deleted. Frozen and deleted workspaces fail auth. Deleting requires a zero balance (moved out first) and revokes the workspace’s keys through the normal sync path; the last workspace in an org can’t be deleted |
| content_logging_enabled | boolean NOT NULL DEFAULT false | Opt-in flag (content is not saved by default, 2026-11-07), read on the request path with no org lookup |
| content_logging_updated_at | timestamptz NOT NULL | When the flag was last set (creation counts) |
| content_logging_terms_version | text NOT NULL | Terms version in force when it was last set. This plus the timestamp is the consent record; who changed it is in the audit event |
| low_balance_threshold | numeric(24,8) NULL | Credits |
| low_balance_email | text NULL | |
| low_balance_alerted_at | timestamptz NULL | Cleared when the balance rises back above the threshold, so an alert fires once per dip |
| created_at | timestamptz |
Indexes: (org_id); partial UQ (org_id, name) WHERE status <> 'deleted'.
workspace_members — PK (workspace_id, user_id), plus created_at. Only member-role users need rows; Owners and Admins see every workspace. Index (user_id).
4.2 core — keys and gateway sync
Section titled “4.2 core — keys and gateway sync”api_keys
| Column | Type | Notes |
|---|---|---|
| id | uuid | PK |
| workspace_id | uuid FK NOT NULL | |
| name | text NOT NULL | |
| owner_user_id | uuid FK NULL | NULL = workspace-owned |
| key_prefix | text NOT NULL | Display only: the first 12 characters, sk-ra- plus 6 (key format in auth-security.md 2.2) |
| key_hash | bytea NOT NULL | UQ. SHA-256 of the raw key. Keys are long random values, so a fast unsalted hash is fine and allows lookup by hash. Raw key is never stored |
| status | text NOT NULL | CHECK pending, active, revoked. Only active authenticates |
| admin_spend_cap_usd | numeric(20,6) NULL | Pushed to each gateway credential as its budget. Owned by the gateway. With more than one gateway it applies per gateway |
| user_spend_cap | numeric(24,8) NULL | Credits. Owned and enforced by Postgres |
| user_spend_cap_period | text NOT NULL DEFAULT ‘none’ | CHECK none, daily, weekly, monthly. none = one lifetime window |
| expires_at | timestamptz NULL | Enforced at auth |
| last_used_at | timestamptz NULL | Written by the queue consumer, never the hot path |
| created_by | uuid FK | |
| created_at | timestamptz | |
| revoked_at, revoked_by | timestamptz, uuid |
Rotate (the key detail drawer) replaces key_hash and key_prefix on the same row, through the same ordered sync as create (new key shown once, old key dead immediately, no overlap window; see 8.2). The gateway credentials and every setting stay as they are.
Indexes: UQ (key_hash); (workspace_id, status).
gateways — one row per gateway instance (one at launch, e.g. bifrost-1). Instance and adapter type are separate so two instances of the same software can coexist later.
| Column | Type | Notes |
|---|---|---|
| id | text | PK. Instance name, e.g. bifrost-1 |
| adapter | text NOT NULL | Which code adapter talks to it, e.g. bifrost |
| binding | text NOT NULL | Name of the Workers VPC binding both Workers use to reach it |
| enabled | boolean NOT NULL DEFAULT false | |
| created_at, updated_at | timestamptz |
api_key_gateway_credentials — the key’s credential on the gateway (for Bifrost, the virtual key). PK (key_id, gateway_id). At launch every key has exactly one row, on the single gateway; the composite key leaves room for a second instance (failover or a dedicated instance, deferred) without a migration. A key authenticates when its own status is active and its credential exists on the gateway with a stored secret and ref. sync_state doesn’t have to be in_sync (revised 2026-11-01, review of P1.10): editing a key moves it to pending_push (or failed) while the gateway still holds the previous, working credential, so requiring in_sync turned every key edit into an outage. A new key is pending and a revoked key revoked, so the key’s status already blocks both.
| Column | Type | Notes |
|---|---|---|
| key_id | uuid FK | |
| gateway_id | text FK | |
| gateway_key_ref | text NULL | The gateway’s id for the credential (Bifrost’s virtual key id). Set at step 2 of key create |
| secret_enc | bytea NULL | The credential’s value: nonce + AES-256-GCM ciphertext, with key id and gateway id as associated data. Never logged or returned (decisions log, Bifrost virtual key secret storage) |
| enc_version | smallint NULL | Which encryption key encrypted it; allows rotation |
| sync_state | text NOT NULL DEFAULT ‘pending_push’ | CHECK in_sync, pending_push (create or update not yet confirmed on the gateway), pending_delete (revoked, gateway delete retrying), failed |
| sync_error | text NULL | Last error from the gateway |
| synced_at | timestamptz NULL | |
| created_at | timestamptz |
Indexes: UQ (gateway_id, gateway_key_ref); partial (sync_state) WHERE sync_state <> 'in_sync' for the reconciler. The request path reads the key’s row by primary key.
api_key_models — the key’s allowed_models. PK (key_id, model_id); index (model_id). A join table rather than an array so model_id is a real foreign key to models. The request path checks it with a primary-key lookup.
key_spend_windows — the user-set spend cap counters. PK (key_id, window_start), fillfactor = 70.
| Column | Type | Notes |
|---|---|---|
| key_id | uuid FK | |
| window_start | timestamptz | UTC start of the day, ISO week or month for the key’s period; a fixed epoch constant when the period is none |
| used | numeric(24,8) NOT NULL DEFAULT 0 | CHECK >= 0. Held plus settled credits: raised at hold, adjusted by (charged − held) at settle |
No reset job: a new period is just a new window_start row, created by upsert on first use. Old rows are history and can be pruned later.
gateway_discrepancies — backs the admin discrepancy view; written by the key reconciliation job.
| Column | Type | Notes |
|---|---|---|
| id | uuid | PK |
| kind | text NOT NULL | CHECK stuck_pending, missing_in_gateway, missing_in_postgres, revoked_still_live, config_drift |
| gateway_id | text FK NOT NULL | |
| key_id | uuid FK NULL | NULL for gateway credentials with no Postgres row |
| gateway_key_ref | text NULL | |
| detail | jsonb NOT NULL | For drift: field, Postgres value, gateway value |
| detected_at, last_seen_at | timestamptz | Each reconciliation run touches last_seen_at |
| resolved_at | timestamptz NULL | |
| resolved_by | uuid FK NULL | NULL when the job clears it because the mismatch is gone |
| resolution | text NULL | CHECK resynced, cleaned_up, dismissed, auto_cleared |
Indexes: partial UQ (kind, gateway_id, COALESCE(key_id::text, gateway_key_ref)) WHERE resolved_at IS NULL (one open row per mismatch); partial (detected_at) WHERE resolved_at IS NULL.
4.3 core — models, rates, markup
Section titled “4.3 core — models, rates, markup”models — the admin catalog. A model is on for a workspace when enabled and a current rate exists for the workspace’s currency; the join in the request path is the gate.
| Column | Type | Notes |
|---|---|---|
| id | text | PK. Our public model id, the one customers send. Independent of any gateway’s naming |
| gateway_model_id | text NOT NULL | The gateway’s own id for the model (confirm Bifrost’s format against its model listing). Usually equal to id; kept separate so a gateway change never renames a customer-facing model. Moves to a per-instance table if instances ever serve different model lists (deferred) |
| display_name | text NOT NULL | |
| provider | text NULL | |
| in_catalog | boolean NOT NULL DEFAULT true | False when a sync no longer sees it on its gateway |
| deprecated_at | timestamptz NULL | Staff “mark deprecated” (admin Model catalog). Display only: it flags the model to customers; it stays usable until staff turn it off |
| enabled | boolean NOT NULL DEFAULT false | Staff switch. New models from the sync start false |
| enabled_at, enabled_by | timestamptz, uuid | |
| default_for_new_keys | boolean NOT NULL DEFAULT false | Pre-checked in onboarding and available to all users |
| max_tokens_ceiling | integer NULL | Per-model cap on the client’s max_tokens |
| default_max_tokens | integer NULL | Used when the client omits it |
| input_allowance_tokens | integer NULL | Added to the hold as tokens × input rate (see 8.2) |
| catalog_synced_at | timestamptz | |
| created_at, updated_at | timestamptz |
published_rates — append-only history of every computed rate.
| Column | Type | Notes |
|---|---|---|
| id | uuid | PK |
| model_id | text FK NOT NULL | |
| currency | char(3) FK NOT NULL | |
| token_type | text NOT NULL | CHECK input, output |
| rate | numeric(20,12) NOT NULL | Final published credits per token, after markup and round-up |
| base_cost_usd | numeric(20,12) NOT NULL | Gateway-reported cost input, highest across providers |
| fx_rate | numeric(20,10) NOT NULL | USD to currency, as used: the day’s basis rate after the FX markup, rounded up to 10 places |
| fx_rate_basis | text NULL | The basis actually used (official, or parallel_usd_cost; official also when a parallel currency fell back). NULL for USD. Added with the FX providers change (2026-11-02) |
| fx_provider | text NULL | Provider of the rate used (mansa, frankfurter, manual). NULL for USD |
| fx_markup_rule_id | uuid FK NULL | The FX markup rule applied (scope = fx); NULL for USD. Added in 0007 (2026-11-01) |
| markup_rule_id | uuid FK NULL | Token markup rule that won resolution |
| pre_round_rate | numeric(20,12) NOT NULL | Kept so rounding is explainable |
| batch_id | uuid NOT NULL | One daily run |
| computed_at, effective_from | timestamptz |
Indexes: (model_id, currency, token_type, effective_from DESC); (batch_id).
current_rates — one row per (model, currency): what the request path reads. Updated by the daily job in one transaction with the published_rates inserts.
| Column | Type | Notes |
|---|---|---|
| id | uuid | PK |
| model_id | text FK | |
| currency | char(3) FK | |
| input_rate, output_rate | numeric(20,12) NOT NULL | |
| input_rate_id, output_rate_id | uuid FK to published_rates |
|
| effective_from, updated_at | timestamptz |
Index: UQ (model_id, currency). The request path reads it by that key.
markup_rules
| Column | Type | Notes |
|---|---|---|
| id | uuid | PK |
| name | text NOT NULL | |
| scope | text NOT NULL | CHECK global, currency, model, fx. Token prices resolve global < currency < model. fx is the FX markup (percentage only), global when scope_currency is NULL or per currency, applied wherever FX is used (revised 2026-10-26; replaces transfer) |
| scope_currency | char(3) FK NULL | Required for currency; optional for fx (NULL = all currencies) |
| scope_model_id | text FK NULL | Set iff model |
| kind | text NOT NULL | CHECK flat, percentage |
| flat_amount | numeric(20,12) NULL | Kind flat |
| flat_per_tokens | integer NULL | e.g. 1000 = per 1K tokens |
| flat_currency | text NULL | USD or a currency code |
| percent | numeric(9,4) NULL | Kind percentage |
| enabled | boolean NOT NULL DEFAULT true |
Checks added in 0007 (2026-11-01): percent and flat_amount can’t be negative, so no rule can price below cost; an fx rule must be a percentage. The migration stops if any old transfer-scope rule still exists.
| created_by, created_at, updated_at | | |
CHECKs: exactly the scope column matching scope is set; value columns match kind. Indexes: partial UQ (scope, scope_currency, scope_model_id) NULLS NOT DISTINCT WHERE enabled (one live rule per scope). Launch values are entered later in admin; the table ships empty.
workspace_discounts — a percentage off the published rate for one workspace. Rates, FX and markup are never per workspace; a negotiated price is a discount applied at charge time and locked with the rate at hold.
| Column | Type | Notes |
|---|---|---|
| id | uuid | PK |
| workspace_id | uuid FK NOT NULL | |
| model_id | text FK NULL | NULL = all models. A model-specific discount wins over a workspace-wide one; discounts never stack |
| percent | numeric(7,4) NOT NULL | CHECK > 0 AND <= 100 |
| enabled | boolean NOT NULL DEFAULT true | End a discount by disabling it |
| reason | text NOT NULL | Why it was granted |
| created_by | uuid FK | |
| created_at, updated_at | timestamptz |
Indexes: partial UQ (workspace_id, model_id) NULLS NOT DISTINCT WHERE enabled (one live discount per scope). Lookup: WHERE workspace_id = $1 AND enabled AND (model_id = $2 OR model_id IS NULL) ORDER BY model_id NULLS LAST LIMIT 1. Changes are audit-logged (discount.changed).
fx_rates
| Column | Type | Notes |
|---|---|---|
| id | uuid | PK |
| currency | char(3) FK | All values are units of currency per 1 USD. Non-USD to non-USD is chained through USD at query time |
| official | numeric(20,10) NULL | The official rate. Replaces rate |
| parallel_usd_cost | numeric(20,10) NULL | Parallel (P2P) market: what we pay to get 1 USD. Named from our side; each provider adapter maps its own label onto it (for Mansa, its “Sell”, to be confirmed on a real response) |
| parallel_usd_proceeds | numeric(20,10) NULL | Parallel market: what we’d get for selling 1 USD. Recorded for reference, not priced on |
| rate_date | date NOT NULL | UTC date the sample belongs to |
| provider | text NOT NULL | CHECK mansa, frankfurter, manual. Replaces source |
| provider_fetched_at | timestamptz NULL | When the provider quoted it (the provider’s own timestamp if it gives one, else fetch time). NULL for manual rows |
| created_by | uuid FK NULL | Set for manual overrides |
| created_at | timestamptz |
CHECKs: every value > 0; official or parallel_usd_cost is set; the two parallel values are both set or both NULL, and parallel_usd_cost >= parallel_usd_proceeds (an inverted quote is dropped before insert and alerted, keeping its official rate).
One row per fetch: Mansa is sampled hourly, so a day has up to 24 rows per currency. Index: (currency, rate_date DESC, created_at DESC). Resolution for a date, using the currency’s fx_rate_basis: a manual row with that value set wins; otherwise the first provider in currencies.fx_providers with rows for the date, taking the median of its values for the basis. A parallel-basis currency with no parallel value falls back to official and is flagged. The stale-FX rules (pricing-model.md) apply to the resolved value.
Migration (with the P2.01 update): rename rate → official and source → provider (existing rows keep frankfurter/manual), add the new columns and CHECKs, add the two currencies columns.
payment_vendors — id, name, enabled, updated_at.
payment_vendor_fees — PK (vendor_id, currency); fee_type (flat, percentage), fee_value numeric(20,8), updated_by, updated_at. Keyed by currency because a flat fee only means something in one currency.
4.4 core — money
Section titled “4.4 core — money”workspace_balances — one row per workspace, kept narrow and separate from workspaces because it is the hottest row in the system. PK workspace_id, fillfactor = 70.
| Column | Type | Notes |
|---|---|---|
| workspace_id | uuid | PK, FK |
| balance | numeric(24,8) NOT NULL DEFAULT 0 | CHECK >= 0 |
| held | numeric(24,8) NOT NULL DEFAULT 0 | CHECK >= 0 |
| updated_at | timestamptz |
There is deliberately no held <= balance check: settle floors the balance at zero and absorbs shortfall, so with other holds outstanding held can briefly exceed balance. The hold condition (balance - held >= min_hold) still blocks the next request.
holds
| Column | Type | Notes |
|---|---|---|
| id | uuid | PK |
| workspace_id | uuid FK | |
| key_id | uuid FK | |
| request_id | uuid NOT NULL | UQ |
| model_id | text FK | |
| amount | numeric(24,8) NOT NULL | What was actually reserved: LEAST(available, computed) |
| input_rate, output_rate | numeric(20,12) NOT NULL | The locked published rates settle will use |
| discount_percent | numeric(7,4) NOT NULL DEFAULT 0 | Locked with the rates |
| status | text NOT NULL | CHECK open, settled, released |
| window_start | timestamptz NULL | The key’s spend window the hold reserved in (since 0013, 2026-11-04). NULL when the key has no cap, and on holds opened before 0013 |
| window_reserved | numeric(24,8) NOT NULL DEFAULT 0 | What the hold added to that window’s used, so whoever closes the hold (settle, release, unbilled charging, a staff release) gives back exactly that |
| created_at | timestamptz | |
| closed_at | timestamptz NULL |
Indexes: UQ (request_id); partial (workspace_id) WHERE status = 'open' (backs the held-equals-open-holds check). Settle and release each close the hold in the same transaction as the balance change, so a finished request never leaves an open hold. Orgs with hold off write no row: nothing is reserved, and the rate locked at request start lives in the Worker until settle, which records it on the settlement.
settlements — one per charged request.
| Column | Type | Notes |
|---|---|---|
| id | uuid | PK |
| workspace_id, key_id | uuid FK | |
| request_id | uuid NOT NULL | UQ |
| hold_id | uuid FK NULL | NULL when hold is off |
| model_id | text FK | |
| input_tokens, output_tokens | bigint NOT NULL | As reported by the gateway |
| input_rate, output_rate | numeric(20,12) NOT NULL | Locked published rates |
| discount_percent | numeric(7,4) NOT NULL DEFAULT 0 | Locked discount |
| charge | numeric(24,8) NOT NULL | Tokens × rates × (1 − discount) |
| charged | numeric(24,8) NOT NULL | What was actually debited after the zero floor |
| absorbed_shortfall | numeric(24,8) NOT NULL | charge − charged |
| source | text NOT NULL DEFAULT ‘request’ | CHECK request, recon. recon = charged by an admin from the usage reconciliation |
| created_at | timestamptz |
Indexes: UQ (request_id); (workspace_id, created_at DESC). Released holds (failures, gateway rejections) write no settlement.
ledger_entries — append-only record of every balance change; the Transactions screen reads it. Invariant: workspace_balances.balance = SUM(amount) per workspace. BRIN index on created_at (0018), for the incremental ledger check; building it blocks writes to the table for one pass.
ledger_checkpoints and ledger_check_state (0018, code #115) — let the invariant check read only new entries. ledger_checkpoints, one row per workspace: the ledger total plus the settlement entries’ sum and count, for entries created before the cutoff. ledger_check_state, one row: the cutoff (through) and the last full recount (recounted_at). See 8.3, item 6.
| Column | Type | Notes |
|---|---|---|
| id | uuid | PK |
| workspace_id | uuid FK | |
| entry_type | text NOT NULL | CHECK topup, transfer_out, transfer_in, settlement, adjustment |
| amount | numeric(24,8) NOT NULL | Signed |
| balance_after | numeric(24,8) NOT NULL | |
| ref_type, ref_id | text, uuid | The top-up, transfer, settlement or adjustment behind it |
| note | text NULL | Required for adjustment (CHECK) |
| created_by | uuid FK NULL | Staff user for adjustments |
| created_at | timestamptz |
Indexes: (workspace_id, created_at DESC, id); (ref_type, ref_id).
usage_charges_daily — rollup of usage charges, one row per workspace per UTC day (since 0012, 2026-11-04). PK (workspace_id, day); amount numeric(24,8) (the signed sum of that day’s settlement ledger entries) and entries integer (their count). Kept by an AFTER INSERT trigger on ledger_entries (core.roll_up_usage_charge()) in the inserting transaction, so every writer (settle, unbilled charging) keeps it exact without code of its own; the ledger is append-only, so inserts are enough. Backfilled from the ledger by the same migration. The billing history (Transactions, screen 19) reads it instead of summing every settlement since the account began: one index range scan, the same cost however many requests. api has SELECT, INSERT, UPDATE on it, since the trigger runs as the role that inserted the ledger entry. Cost on the request path: one upsert in the settle transaction, on a row only that workspace’s settles touch, and they already queue on its balance row. The ledger invariant check proves it matches the ledger.
usage_discrepancies — backs the admin Unbilled usage screen; written by the usage reconciliation job.
| Column | Type | Notes |
|---|---|---|
| id | uuid | PK |
| request_id | uuid NOT NULL | UQ. One row per request; the gateway’s log id |
| kind | text NOT NULL | CHECK not_charged, token_mismatch |
| gateway_id | text FK NOT NULL | Which gateway logged the call |
| key_id | uuid FK NULL | Resolved from the log’s credential via api_key_gateway_credentials (gateway_id, gateway_key_ref) |
| workspace_id | uuid FK NULL | From the key |
| model_id | text NULL | |
| gateway_input_tokens, gateway_output_tokens | bigint NOT NULL | As the gateway logged them |
| gateway_cost_usd | numeric(20,12) NULL | |
| gateway_logged_at | timestamptz NOT NULL | Also picks the published rate when charging a call that had no hold |
| ledger_input_tokens, ledger_output_tokens | bigint NULL | Set for token_mismatch |
| hold_id | uuid FK NULL | The still-open hold for this request, if there is one |
| status | text NOT NULL DEFAULT ‘open’ | CHECK open, charged, released, dismissed |
| detected_at | timestamptz | |
| resolved_at | timestamptz NULL | |
| resolved_by | uuid FK NULL | |
| resolution_note | text NULL | Required when resolved (CHECK) |
Indexes: UQ (request_id); partial (detected_at) WHERE status = 'open'; partial (workspace_id) WHERE status = 'open' (since 0014: settle asks whether the workspace has open unbilled usage in the same statement as its balance update, one index probe). Charging a row runs the normal settle transaction with source = 'recon', closes the hold, and sets status = 'charged' in the same transaction.
scheduled_jobs — one row per scheduled job; the admin Scheduled jobs screen reads and edits it, and the dispatcher (section 8.3, item 8) runs from it.
| Column | Type | Notes |
|---|---|---|
| job | text | PK. Fixed set, seeded by migration: daily_rates, model_catalog_sync, key_reconciliation, low_balance_alerts, topup_status_check, usage_reconciliation, ledger_invariant_check, expiry_purge (0019, every */10; seeded enabled exactly when ledger_invariant_check is) |
| description | text NOT NULL | Shown in the admin screen |
| enabled | boolean NOT NULL DEFAULT true | |
| schedule | text NOT NULL | Standard 5-field cron expression, UTC. Validated on save, with a minimum interval of one minute |
| config | jsonb NOT NULL DEFAULT ‘{}’ | Job parameters, e.g. grace_minutes for the usage reconciliation, min_age_minutes for the top-up status check. Validated per job on save |
| next_run_at | timestamptz NULL | Computed from schedule after each run and on every edit. NULL when disabled |
| running_since | timestamptz NULL | Lease held while a run is in progress |
| max_runtime_seconds | integer NOT NULL DEFAULT 900 | A lease older than this is treated as a dead run and reclaimed |
| last_run_at | timestamptz NULL | |
| last_status | text NULL | CHECK success, failed, partial |
| state | jsonb NOT NULL DEFAULT ‘{}’ | Job-owned cursor, e.g. the usage reconciliation’s watermark, advanced only after a window is read completely |
| updated_by | uuid FK NULL | |
| updated_at | timestamptz |
Index: partial (next_run_at) WHERE enabled. Jobs are never added or deleted from the admin screen; only their enabled flag, schedule and config change.
idempotency_keys — PK (workspace_id, hash).
| Column | Type | Notes |
|---|---|---|
| workspace_id | uuid | |
| hash | bytea | hash(workspace + key + exact body), or of workspace, key and the client’s Idempotency-Key. The API key is part of the hash since code #117 (2026-11-05): before, a second key in the workspace sending the same body within 30 s got the first key’s stored response, uncounted against its own cap and logs |
| kind | text NOT NULL | CHECK auto, client |
| status | text NOT NULL | CHECK in_progress, completed |
| request_id | uuid NOT NULL | |
| response_status | smallint NULL | |
| response_body | bytea NULL | Compressed |
| created_at, expires_at | timestamptz | Seconds for auto; longer for client |
Indexes: (expires_at) for cleanup, which the expiry_purge job does every 10 minutes (code #117); partial idempotency_keys_in_progress_idx (workspace_id, created_at) WHERE status = 'in_progress' for the in-flight count (0016).
topups
| Column | Type | Notes |
|---|---|---|
| id | uuid | PK |
| workspace_id | uuid FK | |
| vendor_id | uuid FK | |
| currency | char(3) FK | |
| credits | numeric(24,8) NOT NULL | Credited to the balance |
| fee | numeric(24,8) NOT NULL | Recorded as revenue, never credited |
| total_collected | numeric(24,8) NOT NULL | CHECK = credits + fee |
| status | text NOT NULL | CHECK pending, succeeded, failed |
| niobi_reference | text NULL | |
| failure_reason | text NULL | |
| last_checked_at | timestamptz NULL | Last Niobi status query. Since code #114 (2026-11-05) it is also the throttle: a dashboard poll claims the check with UPDATE … WHERE status = 'pending' AND (last_checked_at IS NULL OR last_checked_at < now() - 10 s), and only the poll that wins asks the provider; the rest return the row as it is. Staff “check now” passes force and always asks |
| check_count | integer NOT NULL DEFAULT 0 | Real provider calls, not polls (code #114) |
| created_by | uuid FK | |
| created_at, completed_at | timestamptz |
Crediting (code #95, 2026-11-04): a succeeded result must carry the amount and currency Niobi collected, and the top-up is credited only when they equal total_collected and currency; a mismatch stays pending (the admin overview flags overdue top-ups) and logs topup.amount_mismatch. If creating the collection fails or times out, the top-up stays pending, not failed: Niobi may have created it, and knows it by our top-up id, so the callback or status check can still match it. The fee is computed in exact decimals and rounded up to the cent.
Indexes: partial UQ (vendor_id, niobi_reference) WHERE niobi_reference IS NOT NULL (makes a repeated Niobi callback harmless); (workspace_id, created_at DESC); partial (last_checked_at) WHERE status = 'pending' (the status-check job). Fee revenue is SUM(fee) WHERE status = 'succeeded'; no separate table.
transfers — id, org_id, from_workspace_id, to_workspace_id (CHECK different), from_credits, to_credits, final_rate (the one rate shown to the customer), mid_rate NULL, from_fx_markup_rule_id NULL, to_fx_markup_rule_id NULL, created_by, created_at. Written with its two ledger_entries in one transaction. Index (org_id, created_at DESC). Revised in 0007 (2026-11-01): mid_rate plus an FX markup rule per leg replace fx_rate_used and the single markup_rule_id, because a move between two non-USD currencies applies two markups (decisions log, FX markup). Within one currency there is no rate and no rule: CHECK mid_rate IS NOT NULL OR (final_rate = 1 AND both rule ids NULL). Both workspaces must belong to the transfer’s org (composite FKs (workspace_id, org_id)). As built (code #74, 2026-11-02): final_rate is rounded down to its 10 places and the credited amount rounded down, so a move never creates credits; both balance rows are locked in id order, so opposite moves at the same time queue instead of deadlocking; a move with no FX rule for a non-USD leg is refused, not treated as 0%.
4.5 logs — request log, audit, rollups
Section titled “4.5 logs — request log, audit, rollups”Every table here: no foreign keys, a non-null time column in the primary key (created_at, started_at for job runs, or the rollup’s time bucket), and append-only unless marked as a rollup.
request_logs — PK (created_at, id).
| Column | Type | Notes |
|---|---|---|
| created_at | timestamptz | From the event, never now() (see 8.3) |
| id | uuid | The request id |
| org_id, workspace_id, key_id | uuid | Plain columns |
| key_owner_user_id | uuid NULL | Spend attribution |
| endpoint | text | |
| model_id, provider | text | |
| status_code | smallint | |
| error_code | text NULL | |
| latency_ms | integer | |
| stream | boolean | |
| idempotent_replay | boolean | |
| hold_enabled | boolean | As applied to this request |
| input_tokens, output_tokens | bigint NULL | |
| input_rate, output_rate | numeric(20,12) NULL | Locked published rates |
| discount_percent | numeric(7,4) NULL | |
| charge, charged | numeric(24,8) NULL | Copies from the settlement; the ledger stays the source of truth |
| absorbed_shortfall | numeric(24,8) NULL | |
| currency | char(3) | |
| gateway_id | text | Which gateway served the request |
| client_country | char(2) NULL | From CF-IPCountry |
| client_asn | integer NULL | From cf.asn |
| client_ip | inet NULL | Never written until IP logging is decided (decisions log, API error contract and response extras); exists so enabling it needs no migration |
| gateway_cost_usd | numeric(20,12) NULL | COGS, as the gateway reported it. Logged as-is from day one, per the pricing model |
| fx_rate | numeric(20,10) NULL | USD to the workspace’s currency, FX markup included, as built into the request’s locked price (published_rates.fx_rate). Margin converts cost with it, never a later rate (operator answer 9) |
| content_status | text NOT NULL | CHECK stored, partial, truncated, none, opted_out, failed. Status as written; later deletion is an audit event (see 8.3) |
| content_object_key | text NULL | R2 object key |
| content_offset, content_length | bigint NULL | Empty at launch; lets many requests share one object later |
| content_bytes | integer NULL | Compressed size |
Indexes: (workspace_id, created_at DESC); (key_id, created_at DESC); (id) (non-unique, for lookup by request id).
audit_events — PK (created_at, id). Same store as the request log, per the decision.
| Column | Type | Notes |
|---|---|---|
| created_at, id | ||
| actor_type | text | CHECK user, staff, system |
| actor_user_id | uuid NULL | |
| impersonated_by | uuid NULL | The staff admin, when the action happened in an impersonation session (actor_user_id is then the customer) |
| org_id, workspace_id | uuid NULL | |
| action | text NOT NULL | Dotted name, e.g. key.created, key.revoked, topup.succeeded, transfer.created, member.role_changed, workspace.updated, content_logging.changed, hold_toggle.changed, in_flight_cap.changed, rate_limit.changed, ledger.adjusted, discrepancy.resolved, usage_discrepancy.resolved, hold.released, discount.changed, user.banned, session.revoked, auth.credentials_changed (password, email or two-factor), job.updated, job.run_manually, impersonation.started, impersonation.ended, content.read |
| target_type, target_id | text, uuid | |
| metadata | jsonb NOT NULL DEFAULT ‘{}’ | Before/after values and reason. Never secrets or content |
| ip | inet NULL |
Indexes: (org_id, created_at DESC); (target_type, target_id, created_at DESC); (action, created_at DESC).
usage_hourly — rollup, updatable. PK (workspace_id, hour_start, key_id, model_id), plus an index on (hour_start) for the admin margin report, which reads all workspaces by time. (Workspace first since 0005, 2026-11-01: every dashboard read is one workspace over a time range, and a time-first key scanned every tenant’s rows for that range.) Columns: org_id, currency, requests, error_requests, input_tokens, output_tokens, charged, gateway_cost_usd, gateway_cost_local (each request’s cost × its own fx_rate; × 1 for USD), absorbed_shortfall, latency_ms_sum. Feeds the overview, usage-by-model/key screens and the margin report; daily and weekly views sum hourly rows.
content_storage_daily — rollup, updatable. PK (workspace_id, day) (workspace first since 0005); objects_written, bytes_written. Per-workspace storage is the running sum. Workspace id is what the decisions log calls account_id in the R2 key layout.
job_runs — append-only history of scheduled job runs, PK (started_at, id). Inserted when a run finishes, so it is never updated; a run that died leaves no row until the dispatcher reclaims its expired lease and records it as failed.
| Column | Type | Notes |
|---|---|---|
| started_at, id | ||
| job | text NOT NULL | Plain column, no foreign key |
| trigger | text NOT NULL | CHECK schedule, manual |
| triggered_by | uuid NULL | Staff user for manual |
| finished_at | timestamptz NOT NULL | |
| status | text NOT NULL | CHECK success, failed, partial |
| summary | jsonb NOT NULL DEFAULT ‘{}’ | Counts and outcome, e.g. rows checked, discrepancies found, models priced |
| error | text NULL | Error message when failed, or lease expired for a reclaimed run |
Index: (job, started_at DESC).
5. Hold/settle hot path
Section titled “5. Hold/settle hot path”The mechanism is decided (two-phase, atomic conditional UPDATE, no Durable Object). This section is what the schema does to make it cheap and safe.
Request path, in database terms
- One read, before any lock, after the local key-format and checksum check (
auth-security.md2.2, so malformed keys never reach the database): key bykey_hashjoined to workspace and org (key, workspace and org status, currency,hold_enabled,content_logging_enabled, caps, expiry), thenapi_key_models→models→current_ratesfor the gate and the rates, plus the workspace’s discount fromworkspace_discounts. Nothing is locked, so a rejected request costs no write. - One statement:
core.claim_and_hold(...)(since0015, 2026-11-05), a plpgsql function that does the steps below in this order, so the hold is one round trip to Postgres instead of a client-side transaction of about six (the Worker runs near the customer, far from the database). A refusal (over cap, below the minimum hold) raises inside the function, which rolls back everything it wrote, the idempotency claim included, and returns the reason; a replay or an in-progress conflict writes nothing.SECURITY INVOKER,EXECUTErevoked fromPUBLICand granted toapionly, so theapirole’s table grants still apply. The steps (all incore):INSERT INTO idempotency_keys ... ON CONFLICT DO NOTHING. Zero rows inserted: roll back and take the conflict path (409, or return the stored response). Since0017(code #113) anin_progressclaim older than 15 minutes is taken over instead (its Worker died), so a client retrying with the sameIdempotency-Keyisn’t refused for the claim’s whole 24 h. A completed claim is still replayed until it expires; the original Worker finishing late can’t overwrite the new claim, because completing and dropping a claim match onrequest_id.- In-flight cap (since
0016, code #107): take a per-org advisory lock (pg_advisory_xact_lock, held to the end of the statement, so simultaneous requests can’t all see room), then count the org’s otherin_progressclaims created in the last 15 minutes. At or overmax_in_flight: raise, roll back,429 concurrency_limited. The 15-minute window means a claim a dead Worker leftin_progressstops counting by itself. Lock order is idempotency row → org lock → spend window → balance; settle never takes the org lock, so the two can’t deadlock. - If the key has a user cap: upsert the current
key_spend_windowsrow,used = used + hold, onlyWHERE used + hold <= cap. Zero rows: over cap, roll back. - Hold (hold on;
$computed_holdis priced at the locked, discounted rates):Zero rows means below the minimum hold: roll back and reject. Then insert theUPDATE core.workspace_balances bSET held = b.held + LEAST(o.balance - o.held, $computed_hold)FROM (SELECT workspace_id, balance, held FROM core.workspace_balancesWHERE workspace_id = $1 FOR UPDATE) oWHERE b.workspace_id = o.workspace_id AND o.balance - o.held >= $min_holdRETURNING LEAST(o.balance - o.held, $computed_hold) AS hold_amount;holdsrow with the locked rates. - Hold off: a plain read of
balance - held >= min_hold, no lock and noholdsrow. The idempotency insert still runs.
- Settle, one statement:
core.settle(...)(since0017, code #113; release iscore.release_request(...)). Both are plpgsql,EXECUTEgranted toapionly, and hold their locks only while they run, where the earlier Drizzle transactions of 5–8 round trips kept the balance row locked for the last 3. The hold amount and window reservation are read from the hold row, not passed by the Worker. Since0019(code #117) settle first marks the request’s idempotency rowcompleted(matchingrequest_id), so a request makes 3 database calls, not 4, and frees its in-flight slot as soon as settle commits. What they do: charge is tokens × locked rates × (1 − locked discount); debitLEAST(charge, balance)and reduceheldby the hold amount onworkspace_balances; insert thesettlementsrow (withabsorbed_shortfall) and theledger_entriesrow; close the hold assettled, so no hold outlives its charge; adjust the spend window bycharged − hold_amount. Release (any failure or Bifrost rejection): reduceheld, mark the holdreleased, and give the spend window its reservation back. - Off the hot path: usage event to the queue, content to R2. The idempotency row is marked
completedby settle; a release, or a settle that throws, still completes it with a separate statement.
Why it stays fast
- One hot row per workspace.
workspace_balancesholds onlybalance,heldandupdated_at, no other reason to update it and no index on the columns that change. Updates are heap-only (HOT), andfillfactor = 70leaves room for that. Concurrent requests for one workspace serialize on this row for a few milliseconds each, which is the intended correctness mechanism; requests for different workspaces never contend. - Fixed lock order everywhere: idempotency row (hold, and settle since
0019), then the org’s in-flight lock (hold only), thenkey_spend_windows, then theholdsrow, thenworkspace_balances. Hold, settle, release, unbilled charging and a staff release all follow it, so none can deadlock with another (code #96, #99, #102). Settle and release close the hold first (UPDATE holds … WHERE status = 'open' RETURNING) and touch the window and balance only if they closed it, so a hold closed elsewhere (by unbilled charging, or a second release) is never given back twice. Settle then returns nothing, and the request logssettle_skipped_hold_closed. - Reads are primary-key or unique lookups:
key_hash,(key_id, model_id),(model_id, currency),(workspace_id, model_id)for the discount,(workspace_id),(workspace_id, hash). No scans on the request path. - The log schema is never touched in the transaction. Queue publish failing cannot affect money.
Invariants the schema enforces or lets us check
balance >= 0andheld >= 0(CHECKs).- At most one hold, one settlement and one idempotency claim per request (unique
request_id, PK on idempotency). balance = SUM(ledger_entries.amount)andheld = SUM(amount)of open holds, per workspace, checkable by query (the invariant check job, 8.3). The check also provesusage_charges_dailyequals the settlement entries it rolls up, per workspace (rollupMismatched).total_collected = credits + feeon every top-up (CHECK).- A key that is not
activenever authenticates, so apendinghalf-created key cannot be used.
6. Hooks checklist (future-optimizations.md)
Section titled “6. Hooks checklist (future-optimizations.md)”| Item | Where it’s satisfied |
|---|---|
created_at non-null on every log table and in every primary or unique key |
request_logs, audit_events PK (created_at, id); rollups are keyed by their time bucket (hour_start, day) |
| No foreign keys from log tables to ledger tables | 4.5: all ids are plain columns; second ER diagram |
| Log tables append-only | request_logs and audit_events have no update path; later content deletion is an audit event, not an update to the row |
| Rollup tables for the dashboard’s aggregates | usage_hourly, content_storage_daily |
| Object key plus optional offset and length, and a content status | request_logs.content_object_key, content_offset, content_length, content_status |
| Per-account storage bytes and object counts | content_storage_daily |
| Separate Hyperdrive config or connection limit for analytics writes | Config, not schema: the queue consumer’s store interface uses its own binding. Called out in 4.5 and 2 |
| Queue consumer through a store interface; content through a write interface | Config and code structure, not schema; the consumer writes only to logs |
7. Not in this doc
Section titled “7. Not in this doc”Bifrost’s database (Bifrost’s own); the R2 object format (decisions-log.md, Audit trail); DDL and migrations (written during the build); the request-log retention and partitioning steps (deferred, hooks above).
8. Choices made here, and follow-ups
Section titled “8. Choices made here, and follow-ups”8.1 Decided in this doc
Section titled “8.1 Decided in this doc”- Better Auth’s organization and admin plugins own the org tables and the staff role (confirmed 2026-09-21), instead of hand-rolled ones (4.0, 4.1). The plugin’s shape wins where they differ: an invite’s id is its link token, so there is no
token_hash, and its statuses arepending,accepted,rejected,canceled. - Login methods and plugins as listed in 4.0: Google, GitHub, GitLab and email and password, with account linking, two-factor (required for staff), passkeys, breach-checked passwords and a Turnstile captcha.
- Workspace-specific pricing is a discount on usage (
workspace_discounts), applied at charge time and locked with the rate at hold. Rates, FX and markup are never per workspace, sopublished_ratesandcurrent_rateshave no workspace column. Tiered markup is dropped from the schema, since its only basis was per-workspace usage; a volume tier can come back later as a tiered discount. - User spend cap counters live in a separate
key_spend_windowstable, not on the key row. Reset is by new window row, with no reset job. - Balances split from workspaces into
workspace_balances, for the hot-row reasons in section 5. - Money is
numeric, credits at 8 decimal places; enums aretextwithCHECK; ids are uuid v7.
8.2 Noted, to confirm before the build
Section titled “8.2 Noted, to confirm before the build”- Impersonation guardrails are proposed, on top of the decision to have impersonation:
adminonly (Support can’t), customers only, read-only, 30 minutes, reason required, always bannered and audited. Support is who usually helps customers, so the alternative is letting Support impersonate too; the read-only rule is what makes that safe, and it has to be enforced in one place with a test. input_allowanceis stored as tokens (input_allowance_tokens) priced at the input rate, so it means the same thing in every currency. The decisions log doesn’t give a unit.- Flat top-up fees are per vendor and currency (
payment_vendor_fees). - Minimum hold and rate significant figures are per currency (
currenciescolumns;rate_sig_figsreplaced the rounding step on 2026-11-01). - Key rotation replaces the hash on the same key row with no overlap window, so the old key stops working the moment the new one is issued. A grace period where both work would need a second hash column or a new row; add it only if customers ask.
8.3 Accepted, built into the design
Section titled “8.3 Accepted, built into the design”- Hold close-out, no sweeper. Settle and release each close their hold in the same transaction as the balance change, so a completed request never leaves a hold behind. There is no hold expiry and no sweeper job. If a Worker dies between hold and settle, the hold stays
openandheldstays raised; the invariant check (item 6) flags the stuck hold, and the usage reconciliation (item 7) finds the uncharged call, which an admin charges or releases. Release is built (code #102, 2026-11-04): releasing an unbilled row also releases its still-open hold, and a hold with no unbilled row (a call the gateway never logged) is released from the admin Stuck holds list. Either way the held credits and the spend-window reservation come back, and nothing is charged or written to the ledger. Expiredidempotency_keys,sessionsandverificationsrows are deleted in batches by theexpiry_purgejob every 10 minutes (code #117, 2026-11-05; before, the daily ledger check did it, so the table held up to a day of every stored response). - Pending top-ups: status-check workflow. A scheduled job takes
pendingtop-ups that have gone a couple of minutes without a callback, asksniobi-proxyfor each one’s transaction status byniobi_reference, and on a terminal result runs the same code path as the callback: credit the balance, write the ledger entry, setcurrency_locked_at, all guarded byUPDATE ... WHERE status = 'pending'so a callback and a check can’t both apply. A non-terminal answer only updateslast_checked_atandcheck_count. A top-up that stays pending past a threshold shows as such in the admin Transactions view; nothing auto-fails. - At-least-once queue delivery. The consumer inserts into
request_logswithON CONFLICT (created_at, id) DO NOTHINGand applies rollup increments only when the insert happened, in one transaction. Socreated_atcomes from the event, nevernow(). - Audit writes. Privileged actions (ledger adjustments, key changes, hold toggle, content-logging change, discount changes, releasing a stuck hold, starting an impersonation) are written directly to
logs.audit_eventsbyapps/dashboard-apiin the same request, not through the queue. A stored-content read writes itscontent.readevent before returning the content: no audit event, no content. - Content deletion vs append-only. When deleting or purging stored content ships, it is recorded as a
content.deletedaudit event; effective content status is the row’s status overridden by that event. The request-log row itself is never updated. - Invariant check. A scheduled query per workspace comparing
balancewithSUM(ledger_entries.amount)andheldwith the sum of open holds, alerting on any difference. It is what catches a stuck hold, and a bug in the hot path before a customer does. Incremental since0018(code #115, 2026-11-05): each run locksledger_check_state, then in one statement reads only the entries sincethrough, checks balances and the rollup against the checkpoint plus those entries, folds entries older than the new cutoff intoledger_checkpoints, and movesthroughforward. The cutoff trails the run bycheckpoint_lag_minutes(60), becausecreated_atis when the inserting transaction began and a newer entry may not have committed; newer entries are still checked, just checkpointed next run, and the cutoff never moves back. Everyrecount_days(7), and on the first run, it recounts the whole ledger and rebuilds the checkpoints, so a wrong checkpoint can hide a mismatch for a week at most. The run summary’sledgerReadsays which kind of run it was. - Usage reconciliation with the gateway. An hourly job, per enabled gateway (one at launch), pages its usage logs through the adapter (for Bifrost,
GET /api/logs) from its watermark to now minus a 15-minute grace and checks each log id (which is our request id, sent asx-request-id) againstsettlements.request_id. A missing settlement becomes anot_chargedrow inusage_discrepancies; a settlement with different token counts becomestoken_mismatch. Each row is charged automatically (operator answer 8, decisions log), in full, at the rate in force when the request ran: atoken_mismatchat its settlement’s locked rates and discount for the extra tokens; anot_chargedrow at the hold’s locked rates and discount, or the published rate at the Bifrost log’s time when there was no hold. The charge closes a still-open hold in the same transaction. If the workspace’s available credits don’t cover it, the row staysopen(pending, on the admin Unbilled usage screen) and is charged on the workspace’s next request once they do. An admin can also charge a row, or release it without charge. Verify in staging thatx-request-idbecomes the log id and that log rows carry the virtual key id. - Scheduled jobs: one dispatcher, schedules in the database. A Cron Trigger’s schedule lives in the Wrangler config and can’t be edited at runtime, so
apps/dashboard-apihas a single Cron Trigger that fires every minute. Itsscheduled()handler selects enabled jobs withnext_run_at <= now()and no live lease, claims each withUPDATE scheduled_jobs SET running_since = now() WHERE job = $1 AND (running_since IS NULL OR running_since < now() - max_runtime) RETURNING ..., runs it, then in one statement setslast_run_at,last_status,state, the nextnext_run_atand clears the lease, and inserts ajob_runsrow. A lease that expired is recorded as afailedrun (lease expired) and the job runs again. “Run now” from the admin screen uses the same claim and runner withtrigger = 'manual', so it can’t overlap a scheduled run. All edits and manual runs are audit-logged (job.updated,job.run_manually, reason required when disabling).