Skip to content

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”.


  • 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 has created_at NOT NULL, and mutable config tables also have updated_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. Credits numeric(24,8); per-token rates and USD costs numeric(20,12), since a per-token rate is a tiny fraction of a credit.
  • Enums: text with a CHECK, 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_id in this doc references users.id.

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.

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).

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"
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"
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"
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"

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/db is hand-written and its migrations are generated with drizzle-kit. Don’t run Better Auth’s runtime migrate. That is how the ids, names and constraints below stay under our control. Verified 2026-10-26 (P1.01): @better-auth/cli generate isn’t published for 1.7.x (the CLI stops at 1.5 betas), so a test in apps/dashboard-api (better-auth-schema.test.ts) asks the configured Better Auth instance for its expected tables (getAuthTables from better-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 core schema (modelName and fields mappings in the Better Auth config).
  • Ids: uuid v7, from a custom advanced.database.generateId callback, to match every other table (Better Auth defaults to random strings, which would make each foreign key below a text).
  • 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 support and admin users until two_factor_enabled is 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
email 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 (not support), 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_by set, apps/dashboard-api rejects 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.started and impersonation.ended events, and every audit event written during the session carries impersonated_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 in verifications) and the browser exchanges it for the impersonation session on app.router.africa. Shared cookies across .router.africa are 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).

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
email 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).

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.

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.

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%.

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).


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.

Hold and idempotency states

Request path, in database terms

  1. One read, before any lock, after the local key-format and checksum check (auth-security.md 2.2, so malformed keys never reach the database): key by key_hash joined to workspace and org (key, workspace and org status, currency, hold_enabled, content_logging_enabled, caps, expiry), then api_key_models → models → current_rates for the gate and the rates, plus the workspace’s discount from workspace_discounts. Nothing is locked, so a rejected request costs no write.
  2. One statement: core.claim_and_hold(...) (since 0015, 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, EXECUTE revoked from PUBLIC and granted to api only, so the api role’s table grants still apply. The steps (all in core):
    • INSERT INTO idempotency_keys ... ON CONFLICT DO NOTHING. Zero rows inserted: roll back and take the conflict path (409, or return the stored response). Since 0017 (code #113) an in_progress claim older than 15 minutes is taken over instead (its Worker died), so a client retrying with the same Idempotency-Key isn’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 on request_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 other in_progress claims created in the last 15 minutes. At or over max_in_flight: raise, roll back, 429 concurrency_limited. The 15-minute window means a claim a dead Worker left in_progress stops 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_windows row, used = used + hold, only WHERE used + hold <= cap. Zero rows: over cap, roll back.
    • Hold (hold on; $computed_hold is priced at the locked, discounted rates):
      UPDATE core.workspace_balances b
      SET held = b.held + LEAST(o.balance - o.held, $computed_hold)
      FROM (SELECT workspace_id, balance, held FROM core.workspace_balances
      WHERE workspace_id = $1 FOR UPDATE) o
      WHERE b.workspace_id = o.workspace_id AND o.balance - o.held >= $min_hold
      RETURNING LEAST(o.balance - o.held, $computed_hold) AS hold_amount;
      Zero rows means below the minimum hold: roll back and reject. Then insert the holds row with the locked rates.
    • Hold off: a plain read of balance - held >= min_hold, no lock and no holds row. The idempotency insert still runs.
  3. Settle, one statement: core.settle(...) (since 0017, code #113; release is core.release_request(...)). Both are plpgsql, EXECUTE granted to api only, 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. Since 0019 (code #117) settle first marks the request’s idempotency row completed (matching request_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); debit LEAST(charge, balance) and reduce held by the hold amount on workspace_balances; insert the settlements row (with absorbed_shortfall) and the ledger_entries row; close the hold as settled, so no hold outlives its charge; adjust the spend window by charged − hold_amount. Release (any failure or Bifrost rejection): reduce held, mark the hold released, and give the spend window its reservation back.
  4. Off the hot path: usage event to the queue, content to R2. The idempotency row is marked completed by 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_balances holds only balance, held and updated_at, no other reason to update it and no index on the columns that change. Updates are heap-only (HOT), and fillfactor = 70 leaves 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), then key_spend_windows, then the holds row, then workspace_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 logs settle_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 >= 0 and held >= 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) and held = SUM(amount) of open holds, per workspace, checkable by query (the invariant check job, 8.3). The check also proves usage_charges_daily equals the settlement entries it rolls up, per workspace (rollupMismatched).
  • total_collected = credits + fee on every top-up (CHECK).
  • A key that is not active never authenticates, so a pending half-created key cannot be used.

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

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).


  • 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 are pending, 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, so published_rates and current_rates have 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_windows table, 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 are text with CHECK; ids are uuid v7.
  1. Impersonation guardrails are proposed, on top of the decision to have impersonation: admin only (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.
  2. input_allowance is 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.
  3. Flat top-up fees are per vendor and currency (payment_vendor_fees).
  4. Minimum hold and rate significant figures are per currency (currencies columns; rate_sig_figs replaced the rounding step on 2026-11-01).
  5. 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.
  1. 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 open and held stays 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. Expired idempotency_keys, sessions and verifications rows are deleted in batches by the expiry_purge job 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).
  2. Pending top-ups: status-check workflow. A scheduled job takes pending top-ups that have gone a couple of minutes without a callback, asks niobi-proxy for each one’s transaction status by niobi_reference, and on a terminal result runs the same code path as the callback: credit the balance, write the ledger entry, set currency_locked_at, all guarded by UPDATE ... WHERE status = 'pending' so a callback and a check can’t both apply. A non-terminal answer only updates last_checked_at and check_count. A top-up that stays pending past a threshold shows as such in the admin Transactions view; nothing auto-fails.
  3. At-least-once queue delivery. The consumer inserts into request_logs with ON CONFLICT (created_at, id) DO NOTHING and applies rollup increments only when the insert happened, in one transaction. So created_at comes from the event, never now().
  4. 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_events by apps/dashboard-api in the same request, not through the queue. A stored-content read writes its content.read event before returning the content: no audit event, no content.
  5. Content deletion vs append-only. When deleting or purging stored content ships, it is recorded as a content.deleted audit event; effective content status is the row’s status overridden by that event. The request-log row itself is never updated.
  6. Invariant check. A scheduled query per workspace comparing balance with SUM(ledger_entries.amount) and held with 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 since 0018 (code #115, 2026-11-05): each run locks ledger_check_state, then in one statement reads only the entries since through, checks balances and the rollup against the checkpoint plus those entries, folds entries older than the new cutoff into ledger_checkpoints, and moves through forward. The cutoff trails the run by checkpoint_lag_minutes (60), because created_at is 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. Every recount_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’s ledgerRead says which kind of run it was.
  7. 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 as x-request-id) against settlements.request_id. A missing settlement becomes a not_charged row in usage_discrepancies; a settlement with different token counts becomes token_mismatch. Each row is charged automatically (operator answer 8, decisions log), in full, at the rate in force when the request ran: a token_mismatch at its settlement’s locked rates and discount for the extra tokens; a not_charged row 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 stays open (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 that x-request-id becomes the log id and that log rows carry the virtual key id.
  8. 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-api has a single Cron Trigger that fires every minute. Its scheduled() handler selects enabled jobs with next_run_at <= now() and no live lease, claims each with UPDATE 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 sets last_run_at, last_status, state, the next next_run_at and clears the lease, and inserts a job_runs row. A lease that expired is recorded as a failed run (lease expired) and the job runs again. “Run now” from the admin screen uses the same claim and runner with trigger = '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).