Data Model

Data Model

_Last updated: 2026-08-23 — ADR-16: finstack no longer uses strictly one D1 database per Worker. 15 non-critical Workers (ai, analytics, billing, composer, customer, database, fraud, inventory, loyalty, payfac, receipt, split, tax, tip, webhooks) had their schemas folded into an existing critical Worker’s database by theme, to fit Cloudflare Free plan’s 10-database-per-account cap — e.g. fraud’s fraud_rules/fraud_scores tables live inside compliance-db, not a standalone fraud-db. Each Worker still owns its own tables exclusively (verified collision-free at the table-name level before merging) and its own migrations/ directory + a per-Worker migrations_table for independent migration bookkeeping inside the shared database — only the physical database file changed, not the schema ownership model. See DECISIONS.md’s ADR-16 for the full database-to-Worker mapping; each Worker’s own migrations/ directory (finstack/workers/<name>/migrations/) remains the current, authoritative schema for that Worker’s tables. Prior: 2026-08-08 — order-db gained account_id (migration 0002, PR #540). Prior: 2026-08-07 — new order-db (order/invoice primitive), deployed same day — see below. Prior: 2026-07-25 — ADR-9: finstack-rs (and finstack-rs/migrations/) fully deleted. Everything below describes that deleted Postgres schema — kept as historical/porting reference only.

Historical: full schema lived in finstack-rs/migrations/ (now deleted; preserved in git history). Below are tables added or modified during active development pre-removal; historical tables were in earlier migrations (0001–0027).

Current (finstack) — Order/Invoice Primitive (2026-08-07, deployed same day)

order-db (migrations 0001_create_orders.sql + 0002_add_account_id.sql + 0003_add_payment_confirmed_at.sql): orders (id, tenant_id, customer_id, account_id, status, currency, total_cents, payment_id, payment_confirmed_at, receipt_id, inventory_synced_at, receipt_issued_at, completing_since, idempotency_key, metadata, created_at, updated_at). payment_confirmed_at (nullable, added 2026-08-09) tracks whether payment’s /confirm route has been successfully accepted for payment_id’s intent, separately from payment_id itself — payment’s POST /payments/:id/confirm doesn’t update payments.status until the async, webhook-driven capture completes, so this is the only way /complete can tell “confirm was already attempted” from “never attempted” across a retry (a card decline or a missing-paymentMethodId 400 leaves payment_id set but payment_confirmed_at null; a retry checks it before deciding whether to re-attempt confirm or just poll for capture). See composition.ts’s completeOrder() and INTERFACES.md’s /complete entry. + order_line_items (id, order_id, inventory_item_id, name, description, unit_price_cents, quantity, fulfilled_quantity, line_total_cents). account_id (nullable, added 2026-08-08) is the order’s ledger-account funding source for /complete’s charge to payment (which requires exactly one of accountId/consumerUserId — orders always fund via the former, no wallet-topup path); charset-validated to [A-Za-z0-9_-]+, same guard as payment’s own. Settable via POST /orders or PATCH /orders/:id — PATCH’s accountId field has a wider mutability window than lineItems (any non-terminal status, not just pending), so an order that reached fulfilling without one isn’t permanently stuck at /complete’s no-accountId 409 guard. status is CHECK-constrained to the 9-state lifecycle (pending/confirmed/fulfilling/completing/payment_ambiguous/completed/cancelled/refunded/disputed); total_cents/line_total_cents are server-computed, never caller-supplied (an app-level invariant — D1 CHECK constraints can’t sum sibling rows). order_line_items deliberately has no tenant_id column — tenant scoping goes through a join to orders — which means TenantScopedDb.insert() (always prepends tenant_id unconditionally) is incompatible with this table; every write uses db.raw() instead, a real bug found and fixed during implementation, not a design choice made up front. completing_since is a lease timestamp for the /complete composition’s atomic claim, added for the same reason: implementation testing proved a plain “resume if completing” rule let a genuinely concurrent second /complete call defeat its own double-charge guard (Promise.all of two concurrent calls returned [200, 200], not [200, 409], before this fix) — only a stale (>= 30s) completing is reclaimable. UNIQUE (tenant_id, idempotency_key) and UNIQUE (payment_id) (nullable-safe — SQLite UNIQUE allows multiple NULLs). See docs/superpowers/specs/2026-08-07-order-invoice-primitive-design.md and ARCHITECTURE.md’s order primitive entry for full design rationale.

Current (finstack) — Payment-Event Publish Failures (2026-08-03/04)

payment-db (migration 0005_payment_event_publish_failures.sql): new payment_event_publish_failures (id, tenant_id, event_type, source_event_id, payload, occurred_at, error, replayed_at, created_at). Written by publishPaymentEvent (finstack/workers/payment/src/events.ts) when a payment-events queue .send() throws — durably records the lost event instead of only console.error-logging it, now that webhooks (PR #526) has real outbound delivery for payment.created rather than a log-and-ack stub. payload/occurred_at/error reconstruct a full PaymentEventEnvelope, so the row is replay-complete; replayed_at TEXT (nullable) exists for a future replay tool to mark a row processed. 2026-08-04: readable via finstack-ops-mcp’s ops_list_payment_event_publish_failures (unreplayed rows oldest-first by default) — read/triage only, no replay job yet (see TODO.md). A second-order failure (the D1 insert itself failing) is logged and swallowed with no further fallback — publishPaymentEvent must never reject, since the payment it describes has already succeeded by the time it runs. See PR #529, CHANGELOG.md.

Current (finstack) — Webhook Endpoints (2026-08-02)

webhooks-db (new database, migration 0001_webhook_endpoints.sql, US-002-012 Part B): webhook_endpoints (id, tenant_id, url, event_types, description, secret_enc, enabled, created_at). event_types is a JSON array (D1 has no array type). secret_enc is the tenant’s webhook-signing secret, AES-256-GCM encrypted (v1: + base64(nonce[12] || ciphertext), tenant-bound via GCM additionalData — mirrors routing/src/crypto.ts’s processor_configs.credentials scheme exactly, adapted for a raw string instead of JSON, see webhooks/src/crypto.ts) under a new WEBHOOK_ENDPOINT_MASTER_KEY Worker secret (vaulted at cloudflare/FINSTACK_CF_WEBHOOK_ENDPOINT_MASTER_KEY). enabled is INTEGER 0/1 per this repo’s D1 convention. secret_enc is never returned or decrypted by any CRUD route — the plaintext secret (whsec_ + 64 hex chars, generated server-side) is returned exactly once, in the POST /webhooks/endpoints response, and never recoverable after that (mirrors svc-auth’s sk_* API key minting). Decryption is deferred to Part C (outbound delivery, not yet built), the only Worker that will ever need the secret back. Index: (tenant_id, created_at DESC, id DESC) for keyset pagination, matching payment/merchant/payfac/platform’s own list routes.

webhooks-db (migration 0002_webhook_delivery_dead_letters.sql, 2026-08-04): new webhook_delivery_dead_letters (id, tenant_id, event_type, source_event_id, payload, occurred_at, dead_lettered_at). Written by webhooks’s queue() when a message from webhook-delivery-queue-dlq arrives (i.e. a delivery that exhausted webhook-delivery-queue’s 6 retries) — durably records the permanently-undelivered event instead of letting it vanish once Cloudflare’s own retention window passes. payload is the JSON-stringified original envelope payload. No error/attempt context is stored — Cloudflare doesn’t forward the original failure reason into the DLQ message body, so this table records that delivery failed permanently, not why (see TODO.md’s 2026-08-04 entry). Readable via finstack-ops-mcp’s ops_list_webhook_dead_letters (newest-first).

Current (finstack) — Consumer OTP auth (2026-07-28)

svc-auth-db (migration 0006_consumer_auth.sql): three changes closing the “Blocker Zero” consumer-auth gap (see INTERFACES.md’s Consumer OTP auth entry).

• session_tokens gains a nullable consumer_id TEXT column — NULL for fs_sess_* dashboard sessions, populated for cs_sess_* consumer sessions. Reuses the existing session table rather than a second one, since validateBearerToken already treats both prefixes identically (one shared hash-lookup query).
• New consumer_users (id, tenant_id, phone, created_at), unique index on (tenant_id, phone).
• New consumer_otp_challenges (id, tenant_id, phone, code_hash, attempts, consumed_at, expires_at, created_at). code_hash is HMAC-SHA256 (keyed on INTERNAL_ASSERTION_SECRET, domain-separated with an otp: prefix), not a bare hash — a 6-digit code is a 10^6 keyspace, fully precomputable against an unkeyed hash (caught in PR #511 review).

consumer_users(tenant_id, phone)’s unique index is enforced at the DB level and raced against deliberately in code: a concurrent double-verify for the same phone can have both requests pass a SELECT before either INSERTs, and the losing INSERT’s UNIQUE-constraint error is caught and treated as “someone else already created this row” (re-SELECT for the winner’s id) rather than a generic 500 — this repo has hit the same SELECT-then-INSERT race class before (see the CF-rewrite TICKET-27.5 memory note on KycGatedPayoutWorkflow).

Current (finstack) — Tenant Root Merchant Account (2026-07-27)

merchant-db (migration 0003_tenant_root.sql): merchant_accounts gains is_tenant_root INTEGER NOT NULL DEFAULT 0 plus a partial unique index (idx_merchant_one_root_per_tenant on (tenant_id) WHERE is_tenant_root = 1) enforcing at most one root-flagged merchant per tenant. Lets an enterprise tenant designate one of its own merchant_accounts rows as its own top-level identity, distinct from the other legal entities it owns (business line/risk/jurisdiction sub-entities, which are just ordinary merchant_accounts rows — nothing new there). Purely additive/informational: no other Worker (payment, compliance, routing, payout, platform) reads or gates on this column. Existing rows default to 0 — no backfill, no tenant ends up with an auto-designated root. POST/PATCH /merchants/:id both accept is_tenant_root, 409 on conflict. See docs/superpowers/specs/2026-07-27-tenant-root-merchant-design.md.

Current (finstack) — Wallet-topup credit path (2026-07-26)

payment-db (migration 0004_wallet_topup_consumer_user_id.sql): payments gains a nullable consumer_user_id TEXT column. Presence of consumer_user_id (not a separate boolean flag) is what marks a payment as a wallet topup — mutually exclusive with account_id at the application layer (POST /payments requires exactly one), not enforced by a DB constraint (D1 has no CHECK-across-columns in ALTER TABLE). PaymentWorkflow branches on it: an account_id payment posts a ledger entry as before; a consumer_user_id payment instead credits the wallet Worker’s WalletDO via a new WALLET service binding, once Stripe confirms the charge captured. See ARCHITECTURE.md’s “Wallet-topup credit path” entry and PR #499.

Current (finstack) — Stripe Identity KYC/KYB (2026-07-26)

merchant-db (migration 0002_pending_verification.sql): merchant_accounts.status gains 'pending_verification' as a 4th CHECK value ('pending_verification','active','suspended','closed') and becomes the column’s DEFAULT. Existing rows were copied through unchanged (grandfathered 'active') — only new inserts get the new default. Confirmed via source grep that nothing downstream (payment/split/customer) reads or gates on this column, so the new default has zero blast radius on existing money-movement paths.

compliance-db (migration 0003_stripe_identity.sql): new stripe_identity_webhook_events table (id, processor_event_id UNIQUE, event_type, payload, processed_at) — the two-phase idempotency claim table for POST /webhooks/stripe-identity, mirroring payment’s own webhook-idempotency pattern but scoped to this Worker’s own D1 (each CF Worker owns a separate database, no shared processor_webhook_events table across Workers). compliance_kyc_checks/compliance_kyb_checks schemas are unchanged — Stripe Identity reuses the existing provider/provider_ref columns (provider = 'stripe_identity'). compliance_kyc_checks.evidence gains a verifiedOutputs key (ADR-12 Task 6, 2026-07-28) — no migration, evidence is an untyped TEXT NOT NULL DEFAULT '{}' JSON column. Populated only on the identity.verification_session.verified webhook event for a provider = 'stripe_identity' KYC row (not KYB — compliance_kyb_checks has no evidence column at all): { ...existingEvidence, verifiedOutputs: { name?: string, dob?: string /* ISO date */, address?: { line1?, line2?, city?, state?, postalCode?, country? } } }, fetched via GET /v1/identity/verification_sessions/{id}?expand[]=verified_outputs and merged into whatever evidence already held if and only if the existing value parses to a plain object — a non-object stored value (e.g. an array, possible since evidence has no runtime shape validation at write time) is dropped rather than corrupted by the spread, logged loudly when this happens. Written in the same single transitionKyc UPDATE as the status: 'approved' transition — if the Stripe fetch fails, the whole transition (including updateSubjectStatus) is skipped and the webhook event is left unprocessed for retry, same write-ordering invariant as the rest of this handler. verifiedOutputs is redacted from the two list-shaped reads (GET /compliance/kyc/history, GET /compliance/kyc/stale) — both return every matching row for the tenant in one call gated only on compliance:read, so returning real verified PII (name/DOB/address) in bulk there would be a materially different exposure than the single-record GET /compliance/kyc/:id, which does still include it.

Current (finstack) — svc-auth Passkeys (migration 0005)

svc-auth-db gains two tables, distinct from (and not to be confused with) the historical Postgres users/passkey_credentials in the section immediately below this one — that section describes the deleted Rust schema.

Table	Purpose
users	id TEXT PRIMARY KEY — UUID, raw 16 bytes doubles as the WebAuthn userID. tenant_id TEXT NOT NULL — self-service tenant minted fresh at registration, no separate tenants table (finstack has none).
passkey_credentials	id, user_id (FK), credential_id TEXT UNIQUE, public_key TEXT (base64url COSE key), sign_count INTEGER DEFAULT 0. One credential per user (minimal scope — no multi-credential support).

svc-auth-db also already has passkey_ceremonies (id, state, expires_at — migration 0001), unrelated to this addition.

Passkeys / WebAuthn (migrations 0031, 0032)

users — identity anchor. id UUID doubles as the WebAuthn user_handle. Pre-tenant; no RLS (accessed via BYPASSRLS worker pool). The tenant link is the existing tenants.external_user_id = users.id::text.

passkey_credentials — one row per registered authenticator.

Column	Type	Notes
id	UUID PK	gen_random_uuid()
user_id	UUID NOT NULL	FK → users(id) ON DELETE CASCADE
credential_id	BYTEA NOT NULL UNIQUE	raw authenticator id; UNIQUE → re-register = 409
credential	JSONB NOT NULL	serialized webauthn-rs Passkey (counter lives inside)
created_at / last_used_at	TIMESTAMPTZ	last_used_at stamped on each auth

0032 dropped 0031’s public_key_cose/sign_count/aaguid — webauthn-rs persists the whole Passkey, not loose columns. No RLS.

passkey_ceremonies — short-lived WebAuthn challenge state.

Column	Type	Notes
id	UUID PK	ceremony id returned to the SPA
kind	TEXT NOT NULL	CHECK ('registration','authentication')
state	JSONB NOT NULL	serialized PasskeyRegistration / DiscoverableAuthentication
expires_at	TIMESTAMPTZ NOT NULL	60s TTL; single-use DELETE … RETURNING

Postgres (not Redis) — mirrors the OAuth code-store pattern. No RLS; index on expires_at.

database_provisions (migration 0028)

Tracks tenants that have provisioned a managed Postgres database via Post AI.

Column	Type	Notes
id	UUID PK	gen_random_uuid()
tenant_id	UUID NOT NULL	FK → tenants(id) ON DELETE CASCADE; UNIQUE
post_ai_tenant_id	TEXT NOT NULL	String representation of tenant_id UUID used as x-tenant-id with Post AI
provisioned_at	TIMESTAMPTZ NOT NULL	now() default
status	TEXT NOT NULL	'active' default

Constraints: UNIQUE (tenant_id) — ON CONFLICT DO NOTHING makes record_provision idempotent.

RLS: ENABLE ROW LEVEL SECURITY + FORCE ROW LEVEL SECURITY. Policy: tenant_id = current_setting('app.tenant_id', true)::uuid.

Index: database_provisions_tenant_id_idx on (tenant_id).

payouts (migration 0041)

Outbound payment disbursements (ACH, RTP, push-to-card). One row per initiated payout.

Column	Type	Notes
id	UUID PK	gen_random_uuid()
tenant_id	UUID NOT NULL	FK → tenants(id) ON DELETE CASCADE
recipient_id	UUID NOT NULL	Payee identity; FK to recipients (EPIC-014)
amount_minor	BIGINT NOT NULL	Amount in minor currency units (cents)
currency	TEXT NOT NULL	ISO-4217 lowercase (e.g. 'usd')
method	TEXT NOT NULL	CHECK ('ach','rtp','push_to_card')
status	TEXT NOT NULL	CHECK ('pending','processing','settled','failed','returned','cancelled')
processor_config_id	UUID	FK → processor_configs(id) — set at process time
processor_payout_id	TEXT	Processor-native disbursement ID (e.g. Moov transfer ID)
processor_status	TEXT	Processor-native status string
idempotency_key	TEXT NOT NULL	UNIQUE per tenant — ON CONFLICT DO NOTHING
failure_reason	TEXT	Set on failed transition
return_code	TEXT	ACH return code (e.g. R01) on returned transition
scheduled_at	TIMESTAMPTZ NOT NULL	When to initiate; now() default
processed_at	TIMESTAMPTZ	Set on pending→processing
settled_at	TIMESTAMPTZ	Set on processing→settled
failed_at	TIMESTAMPTZ	Set on terminal failure or return
created_at	TIMESTAMPTZ NOT NULL	now()
updated_at	TIMESTAMPTZ NOT NULL	Updated on every FSM transition

FSM: Pending → Processing → Settled | Failed | Returned | Cancelled (only Pending can be cancelled; Returned is ACH-specific terminal state).

RLS: ENABLE ROW LEVEL SECURITY + FORCE ROW LEVEL SECURITY. Policy: tenant_id = current_setting('app.tenant_id', true)::uuid.

Indexes: payouts_tenant_status_created on (tenant_id, status, created_at DESC), payouts_tenant_recipient_created on (tenant_id, recipient_id, created_at DESC), payouts_pending_scheduled on (scheduled_at) WHERE status = 'pending'.

processor_configs.processor_type (added migration 0041): TEXT NOT NULL DEFAULT 'payment' CHECK ('payment','payout','both'). Enables ProcessorRouter::route_payout() to filter out payment-only configs (Stripe card) for ACH/RTP disbursements. Default 'payment' leaves existing configs unaffected.

processor_configs.merchant_account_id (added migration 0075): UUID REFERENCES merchant_accounts(id), nullable — NULL means tenant-wide default (unchanged existing behavior). A non-NULL value scopes that config to one merchant’s card-present traffic so different merchants under the same tenant can be routed to different processors. ProcessorRouter::route_card_present() (new method) prefers a merchant match as its own precedence tier ahead of priority/created_at, falling back to the tenant-wide default. The existing route() method (used by every non-card-present payment) gained a mandatory AND merchant_account_id IS NULL guard so a merchant-scoped row can never leak into unrelated routing decisions. See docs/superpowers/specs/2026-07-16-unified-ttp-client-sdk-design.md.

recipients (migration 0042)

Stale — this section describes the pre-CF-rewrite Postgres schema (RLS, UUID PK, kyc_check_id FK — none of which exist in the current finstack D1 schema; see finstack/workers/recipient/migrations/). Not rewritten here (out of scope for ADR-12); noting one addition since it’s directly relevant: 2026-07-28, ADR-12 Task 4 — the current D1 recipients table gained payout_method TEXT CHECK (payout_method IS NULL OR payout_method IN ('stripe_connect','moov_bank_account')) and stripe_account_id TEXT (both nullable), migration 0002_stripe_connect_fields.sql. “Exactly one shape” (a stripe_connect recipient carries stripe_account_id; a moov_bank_account recipient carries a recipient_bank_accounts row instead) is enforced at the application layer, not a DB constraint — D1 has no cross-table CHECK.

Payee identity for disbursements. Separate from customers (payers) — different KYC requirements, different ledger entries, different API scopes.

Column	Type	Notes
id	UUID PK	gen_random_uuid()
tenant_id	UUID NOT NULL	FK → tenants(id) ON DELETE CASCADE
external_ref	TEXT NOT NULL	Platform’s own payee ID; UNIQUE per tenant
name	TEXT NOT NULL
email	TEXT
phone	TEXT
status	TEXT NOT NULL	'pending_kyc' default; CHECK ('pending_kyc','kyc_approved','kyc_rejected','suspended')
kyc_check_id	UUID	FK → compliance_kyb_checks (set on KYC initiation)
created_at	TIMESTAMPTZ NOT NULL	now()
updated_at	TIMESTAMPTZ NOT NULL	Updated on every FSM transition

FSM: PendingKyc → KycApproved | KycRejected; KycApproved → Suspended. Only KycApproved recipients may receive payouts (PayoutService::create() enforces this).

RLS: ENABLE + FORCE. Policy: tenant_id = current_setting('app.tenant_id')::uuid.

Indexes: recipients_tenant_ext_ref UNIQUE on (tenant_id, external_ref), recipients_tenant_status on (tenant_id, status, created_at DESC).

recipient_bank_accounts (migration 0042)

Bank accounts for payout disbursements. Account numbers encrypted at rest.

Column	Type	Notes
id	UUID PK
tenant_id	UUID NOT NULL	FK → tenants(id) ON DELETE CASCADE
recipient_id	UUID NOT NULL	FK → recipients(id) ON DELETE CASCADE
account_name	TEXT NOT NULL	Friendly label
account_number_enc	BYTEA NOT NULL	AES-256-GCM via RECIPIENT_BANK_MASTER_KEY; never returned from API
last4	TEXT NOT NULL	Last 4 digits; safe to store and return
routing_number	TEXT NOT NULL
account_type	TEXT NOT NULL	CHECK ('checking','savings')
verification_status	TEXT NOT NULL	'unverified' default; CHECK ('unverified','micro_deposit_sent','verified','failed')
micro_deposit_amount1/2	BIGINT	Stored for micro-deposit verification (cents); null until sent
is_default	BOOLEAN NOT NULL	false default; partial UNIQUE index WHERE is_default=true enforces one default per recipient
created_at	TIMESTAMPTZ NOT NULL
updated_at	TIMESTAMPTZ NOT NULL

RLS: ENABLE + FORCE. Policy: tenant_id = current_setting('app.tenant_id')::uuid.

custom_domains (migration 0051)

Tenant custom API domains — maps tenant slugs and arbitrary custom domains to CF routing config.

Column	Type	Notes
id	UUID PK	gen_random_uuid()
tenant_id	UUID NOT NULL	FK context (no FK constraint — pre-tenant identity pattern)
domain	TEXT NOT NULL	FQDN being registered (api.paystream.fi or paystream.api.finstack.sh)
slug	TEXT NOT NULL	FinStack slug: paystream → paystream.api.finstack.sh
cf_worker_domain_id	TEXT	CF Workers Custom Domain record ID; null if CF provisioning failed
cf_hostname_id	TEXT	CF for SaaS Custom Hostname ID; null until ssl_and_certificates:edit permission available
verification_token	TEXT NOT NULL	Pre-generated in Rust (Uuid::new_v4().simple()); returned to tenant for TXT record
dns_verified	BOOLEAN NOT NULL	false until POST /v1/custom-domains/{id}/verify confirms TXT or CNAME
created_at	TIMESTAMPTZ NOT NULL

Constraints: UNIQUE domain, UNIQUE slug.

RLS: ENABLE + FORCE. Policy: tenant_id::text = current_setting('app.tenant_id', true).
Reads use the app pool (RLS-enforced); writes use worker_pool (BYPASSRLS).

ai_tasks (migration 0053)

Tenant-defined configurable LLM task configs (prompt template + output JSON schema). Reserved slugs (e.g. categorize_transaction) are enforced in application code, not SQL — they never appear as a row here.

Column	Type	Notes
id	UUID PK	gen_random_uuid()
tenant_id	UUID NOT NULL	FK → tenants(id)
slug	TEXT NOT NULL	UNIQUE per (tenant_id, slug)
name	TEXT NOT NULL
system_prompt_template	TEXT NOT NULL	{{field_name}} placeholder syntax, ≤ 4,000 chars
output_schema	JSONB NOT NULL	Valid JSON Schema, ≤ 8KB serialized, max depth 5
model_id	TEXT	Optional Neureus catalog model override; validated active at write time
allowed_tools	JSONB NOT NULL DEFAULT '[]'	Stored but inert — no v1 code path reads or executes it; placeholder for a deferred tool-calling spec
created_at	TIMESTAMPTZ NOT NULL
updated_at	TIMESTAMPTZ NOT NULL

Constraints: UNIQUE (tenant_id, slug). Soft cap of 100 tasks per tenant, enforced in AiService::create_task.

RLS: ENABLE + FORCE. Policy: tenant_id = current_setting('app.tenant_id', true)::uuid.

ai_task_runs (migration 0053)

Audit + internal cost record for every primitive-ai run. task_id is nullable — null when the built-in categorize_transaction template was used unmodified (no ai_tasks row exists for reserved slugs).

Column	Type	Notes
id	UUID PK	gen_random_uuid()
tenant_id	UUID NOT NULL	FK → tenants(id)
task_id	UUID	FK → ai_tasks(id), nullable
task_slug	TEXT NOT NULL
input	JSONB NOT NULL
output	JSONB	NULL on a failed run
model_id	TEXT NOT NULL
input_tokens / output_tokens	INT NOT NULL DEFAULT 0	Cumulative across the initial attempt and any schema-validation repair retry
cost_usd	NUMERIC(18,8) NOT NULL DEFAULT 0	Money field — NUMERIC per repo Postgres rule, not FLOAT8; cumulative across retries
idempotency_key	TEXT	Optional, caller-supplied per-item key
created_at	TIMESTAMPTZ NOT NULL

Constraints: Partial UNIQUE index (tenant_id, idempotency_key) WHERE idempotency_key IS NOT NULL — dedupes a run within a tenant so partial-batch retries don’t re-run (or re-bill) items that already succeeded. record_run’s ON CONFLICT mirrors this predicate exactly.

RLS: ENABLE + FORCE. Policy: tenant_id = current_setting('app.tenant_id', true)::uuid. Index on (tenant_id, created_at DESC).

Not wired to primitive-billing: usage data here is captured but not fed into primitive-billing’s metering — that primitive meters FinStack’s tenants’ own end customers, not FinStack’s usage of its own primitives. See DATA_FLOWS.md §AI Task Run Flow.

moov_customer_accounts (migration 0070)

Maps a FinStack consumer to their Moov customer account (card-on-file host) for Moov CNP card acceptance (EPIC-026 follow-on, increment 1; spec docs/superpowers/specs/2026-07-09-moov-cnp-processor-design.md).

Column	Type	Notes
id	UUID PK	gen_random_uuid()
tenant_id	UUID NOT NULL	FK tenants(id)
consumer_id	UUID NULL	FK consumer_users(id); NULL = ephemeral guest
moov_account_id	TEXT NOT NULL	Moov account holding the tokenized card
created_at	TIMESTAMPTZ	NOW()

refunds.cumulative_before_minor (migration 0077)

Added column on the existing refunds table (base table predates this doc). BIGINT NULL — the sum of every OTHER refund on the same payment that had already succeeded when THIS refund’s Pending→Succeeded transition happened, captured once at that transition and never recomputed. Feeds post_refund_ledger()’s cumulative-diff (telescoping) fee-split rounding (#450) so a ledger-post retry re-reads this refund’s real historical position instead of live-re-summing (which would incorrectly include siblings that succeeded after it). Only merchant-scoped, fee-split-eligible refunds ever populate it; a flat or non-merchant refund never reads it back. See BUGS.md’s #450 entry and finstack-rs/crates/primitive-payment/src/refund.rs.

FORCE ROW LEVEL SECURITY (policy USING tenant_id = current_setting('app.tenant_id', true)::uuid). Partial UNIQUE (tenant_id, consumer_id) WHERE consumer_id IS NOT NULL — guests exempt (multiple guest rows allowed); the lazy-create ON CONFLICT ... WHERE consumer_id IS NOT NULL mirrors this predicate. Writes use the BYPASSRLS worker pool (tenant_id bound explicitly). Inert until increments 2–5 wire the charge.