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.

---

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 has created_at NOT NULL; mutable config tables also have updated_at.
• 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 (user, session, account, verification) are generated by Better Auth into the same cluster and not specified here. We add one field to user: staff_role text NULL CHECK (staff_role IN ('admin','support')), the staff flag that is separate from org roles. Every user_id below references user.id.

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

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

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

erDiagram
    ORGANIZATIONS ||--o{ ORG_MEMBERS : has
    ORGANIZATIONS ||--o{ INVITATIONS : issues
    ORGANIZATIONS ||--o{ WORKSPACES : owns
    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--o{ GATEWAY_DISCREPANCIES : "may have"

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

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

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

Notation: PK, FK, UQ unique. Indexes are listed under each table; anything not listed is not needed yet.

4.1 core — tenancy

organizations

Column	Type	Notes
id	uuid	PK
name	text NOT NULL
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
created_at, updated_at	timestamptz
deleted_at	timestamptz NULL

org_members — PK (org_id, user_id)

Column	Type	Notes
org_id	uuid FK
user_id	uuid FK
role	text NOT NULL	CHECK owner, admin, member
created_at	timestamptz

Indexes: (user_id); partial UQ (org_id) WHERE role = 'owner' (exactly one owner).

invitations

Column	Type	Notes
id	uuid	PK
org_id	uuid FK
email	citext NOT NULL
role	text NOT NULL	CHECK admin, member
workspace_ids	uuid[] NOT NULL DEFAULT ‘{}’	Workspaces a member invite grants; ignored for admins
token_hash	bytea NOT NULL	UQ. Raw token only in the email link
status	text NOT NULL	CHECK pending, accepted, revoked, expired
expires_at	timestamptz NOT NULL
invited_by	uuid FK
created_at, accepted_at	timestamptz

Indexes: partial UQ (org_id, 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
rate_rounding_step	numeric(20,12) NOT NULL	The “round up to a clean denomination” step for published rates in this currency

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
content_logging_enabled	boolean NOT NULL DEFAULT true	Opt-out flag, 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). Only member-role users need rows; Owners and Admins see every workspace. Index (user_id).

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, e.g. first 8 chars
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
bifrost_vk_id	text NULL	UQ. Set at step 2 of key create
sync_state	text NOT NULL DEFAULT ‘pending_push’	CHECK in_sync, pending_push (create or update not yet confirmed on Bifrost), pending_delete (revoked, Bifrost delete retrying), failed
sync_error	text NULL	Last error from Bifrost
synced_at	timestamptz NULL
admin_spend_cap_usd	numeric(20,6) NULL	Pushed to Bifrost as the virtual key budget. Owned by Bifrost
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

Indexes: UQ (key_hash); UQ (bifrost_vk_id); (workspace_id, status); partial (sync_state) WHERE sync_state <> 'in_sync' for the reconciler.

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_bifrost, missing_in_postgres, revoked_still_live, config_drift
key_id	uuid FK NULL	NULL for Bifrost keys with no Postgres row
bifrost_vk_id	text NULL
detail	jsonb NOT NULL	For drift: field, Postgres value, Bifrost 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, COALESCE(key_id::text, bifrost_vk_id)) WHERE resolved_at IS NULL (one open row per mismatch); partial (detected_at) WHERE resolved_at IS NULL.

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. Bifrost’s model id (confirm the exact format against Bifrost’s model listing)
display_name	text NOT NULL
provider	text NULL
in_catalog	boolean NOT NULL DEFAULT true	False when a sync no longer sees it in Bifrost
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	Bifrost cost input, highest across providers
fx_rate	numeric(20,10) NOT NULL	USD to currency, as used
markup_rule_id	uuid FK NULL	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, transfer. Specificity for resolution: global < currency < model. transfer applies only to cross-currency transfers
scope_currency	char(3) FK NULL	Set iff scope is currency
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
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	Units of currency per 1 USD. Non-USD to non-USD is chained through USD at query time
rate	numeric(20,10) NOT NULL
rate_date	date NOT NULL
source	text NOT NULL	CHECK frankfurter, manual
created_by	uuid FK NULL	Set for manual overrides
created_at	timestamptz

Index: (currency, rate_date DESC, created_at DESC). Resolution: latest rate_date; a manual row beats a frankfurter row for the same date.

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

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
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 Bifrost
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, Bifrost 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.

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_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 Bifrost log id
kind	text NOT NULL	CHECK not_charged, token_mismatch
key_id	uuid FK NULL	Resolved from the log’s virtual key via api_keys.bifrost_vk_id
workspace_id	uuid FK NULL	From the key
model_id	text NULL
bifrost_input_tokens, bifrost_output_tokens	bigint NOT NULL	As Bifrost logged them
bifrost_cost_usd	numeric(20,12) NULL
bifrost_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'. 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
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 + exact body), or of the client’s Idempotency-Key
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

Index: (expires_at) for cleanup.

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 by the status-check job
check_count	integer NOT NULL DEFAULT 0
created_by	uuid FK
created_at, completed_at	timestamptz

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), fx_rate_used NULL, markup_rule_id NULL, created_by, created_at. Written with its two ledger_entries in one transaction. Index (org_id, created_at DESC).

4.5 logs — request log, audit, rollups

Every table here: no foreign keys, created_at NOT NULL and in the primary key, 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)
bifrost_cost_usd	numeric(20,12) NULL	COGS. Logged as-is from day one, per the pricing model
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
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, rate_limit.changed, ledger.adjusted, discrepancy.resolved, job.updated, job.run_manually, 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 (hour_start, workspace_id, key_id, model_id). Columns: org_id, currency, requests, error_requests, input_tokens, output_tokens, charged, bifrost_cost_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 (day, workspace_id); 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

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

1. One read, before any lock: key by key_hash joined to 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 short transaction (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).
   • 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 transaction: 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, idempotency row marked completed.

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 in both hold and settle: idempotency row, then key_spend_windows, then workspace_balances. Same order everywhere, so no deadlocks.
• 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).
• 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.

---

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

Better Auth’s own tables (generated); 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

8.1 Decided in this doc

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

8.2 Noted, to confirm before the build

1. 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.
2. Flat top-up fees are per vendor and currency (payment_vendor_fees).
3. Minimum hold and rate rounding step are per currency (currencies columns).

8.3 Accepted, built into the design

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. Expired idempotency_keys rows are deleted by a single statement in the daily housekeeping job.
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) 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.
7. Usage reconciliation with Bifrost. An hourly job pages Bifrost’s 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. An admin charges a not_charged row (the normal settle transaction with source = 'recon', at the hold’s locked rates and discount, or the published rate at the Bifrost log’s time when there was no hold) or releases it without charge. Nothing is charged automatically. 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).