Plan: Database design

Status: proposed · 2026-08-30 · companion to card-data-ingestion.md (where the data comes from) and ../reference/system-architecture.md (how everything fits). Scaffolded migrations live in supabase/migrations/.

Decision summary

Postgres on Supabase, three layers:

Evidence (append-only) — every fetched document and every LLM extraction, stored verbatim. jsonb lives here.
Structured truth (normalized, versioned rows) — the queryable state of the market. Anything that changes competitively carries effective_from / effective_to; the current row has effective_to IS NULL.
Derived events — card_events written only by the publish step, never by hand. The changes feed and history timelines render from these.

Naming: issuer (not "provider") — the bank extending credit. Network (Visa/Mastercard/Amex/Discover) and cobrand partner are separate fields on the card; conflating the three is the classic modeling mistake in this domain.

Benefits: table, not jsonb

The tempting shortcut is cards.benefits jsonb. Rejected because benefits are where Frankie's intelligence value concentrates:

History: "airline credit $200 → $250" needs a closed row with effective_to and an event with old/new — jsonb diffs are mush.
Cross-card queries: "every card with lounge access", "cards that added credits this year" want an indexed category column, not jsonb @> gymnastics.
Benchmarking: average credits value by tier needs value_usd as a real numeric column.
Provenance: each benefit gets its own data_evidence rows.

jsonb is used exactly twice: extractions.extracted (the raw LLM output, layer 1) and card_benefits.details (irregular restrictions that don't deserve columns yet — display-only, never filtered on).

Same logic applies to rewards: card_rewards rows per category, not a map on the card.

Schema (column level)

Postgres enums mirror @frankie/shared-types taxonomies 1:1 (network, card_category, card_tier, card_status, personal_or_business, rewards_currency, reward_category, benefit_category, benefit_frequency, transfer_partner_type, source_type, confidence, card_event_type, competitive_direction, extraction_status). They're code-fixed; changing one is a migration, which is the right friction.

Keys: text slugs for identity tables (cards.id = 'chase-sapphire-reserve') — stable, readable, and what the domain layer already speaks. UUIDs for high-volume rows (versioned rows, events, evidence).

Identity

table	columns
`issuers`	`id` text PK, `slug` unique, `name`, `parent_company`, `website`
`cards`	`id` text PK, `slug` unique, `name`, `issuer_id` FK, `network`, `category`, `tier`, `personal_or_business`, `cobrand_partner`, `launch_year`, `status`, `rewards_currency`, `point_value_cents` numeric, `created_at`, `updated_at`
`transfer_partners`	`id` text PK, `name`, `type`, `ratio`

Versioned state (all carry `effective_from date`, `effective_to date NULL`)

table	columns beyond card_id + effective range
`card_terms`	`annual_fee`, `foreign_transaction_pct`, `authorized_user_fee`, `late_fee`, `purchase_apr_min/max`, `intro_purchase_months`, `balance_transfer_apr`, `cash_advance_apr`, `penalty_apr`
`signup_offers`	`bonus_amount`, `currency`, `label`, `spend_requirement`, `window_days`, `estimated_value_usd`, `is_public`, `end_date`
`card_rewards`	`category`, `multiplier` numeric, `cap_usd`, `portal_only`, `notes`
`card_benefits`	`benefit_slug`, `name`, `category`, `value_usd`, `frequency`, `notes`, `details` jsonb
`card_transfer_partners`	`partner_id` FK

Integrity: partial unique index ON (card_id) WHERE effective_to IS NULL for card_terms and signup_offers (exactly one current row); plain partial indexes for the multi-row tables; CHECK (effective_to IS NULL OR effective_to > effective_from) everywhere.

Evidence & events

table	columns
`sources`	`id` uuid, `url`, `source_type`, `publisher`, `retrieved_at`, `content_hash`, `storage_path` (raw HTML/PDF in Supabase Storage, hash-addressed)
`extractions`	`id` uuid, `card_id`, `source_id`, `extracted` jsonb, `model`, `status` (pending → validated/rejected → published), `review_note`, `created_at`
`data_evidence`	`id` uuid, `entity_type`, `entity_id`, `field_name`, `source_id`, `raw_value`, `parsed_value`, `confidence`, `verified_at`
`card_events`	`id` uuid, `card_id`, `type`, `date`, `field`, `old_value`, `new_value`, `summary`, `direction`, `source_id`, `created_at`; indexes `(date DESC)` and `(card_id, date DESC)`

Read model

cards_current view: one flat row per card joining the current card_terms and signup_offers. The SupabaseDataSource maps a card with one nested supabase-js select — flat columns from the view, plus card_rewards / card_benefits / card_transfer_partners as nested relations filtered effective_to=is.null. Dashboard aggregates stay in lib/domain (pure functions over a few hundred rows); no materialized views until the data outgrows that, which it won't for a long time.

The publish function (the invariant enforcer)

All writes to layer 2/3 go through one Postgres function:

publish_extraction(p_extraction_id uuid) →
  1. load extraction + validate status = 'validated'
  2. diff extracted JSON against current rows
  3. for each changed field/row:
       UPDATE old row SET effective_to = capture_date
       INSERT new row (effective_from = capture_date)
       INSERT card_events (field, old, new, direction, source_id)
       INSERT data_evidence rows
  4. mark extraction 'published'
  — one transaction; partial failure = no change at all

Deliberately not in the scaffolded migrations: its diff logic depends on the extraction JSON contract, which Phase 0 of the ingestion plan defines. Shipping a half-working SQL function would be worse than shipping the contract. The schema it writes into is fully scaffolded; the function lands with the pipeline. Nothing else gets INSERT/UPDATE grants on layer 2/3 — "history is sacred" enforced by the database, not by convention.

Security (RLS)

Read-only SELECT for anon/authenticated on all layer-2/3 tables plus sources and data_evidence (they power the provenance UI).
extractions (and later any pipeline staging): service role only.
No client-side writes anywhere; the pipeline uses the service key, humans use the publish function.
Auth (Supabase Auth) arrives in PRD Phase 2; RLS is already shaped for it.

Seeding & rollout

supabase/seed.sql is generated from @frankie/mocks by supabase/generate-seed.ts — the mock dataset and the database start life as the same data, which is also the equivalence test for SupabaseDataSource vs MockDataSource behind getDataSource().
Local dev: supabase start + supabase db reset (applies migrations + seed). Cloud: supabase link + supabase db push.
Cutover per card-data-ingestion.md Phase 0: real data replaces seed rows through the publish path, mock stays the dev/test fixture.

Open questions

signup_offers end-dating: model expired public offers as closed rows (proposed) or keep an is_active flag too? Proposed: effective range only; "no current offer" = no open row.
Do we want networks as a table (PRD sketch) instead of an enum? Enum until a network carries metadata; revisit if we track network-level benefits (e.g. Visa Infinite perks).
Benefit catalog dedup (benefits table with card_benefits join, per original PRD) — deferred until the same benefit meaningfully repeats across many cards and we want cross-card benefit analytics keyed on a canonical id. benefit_slug keeps the door open.