Data model and consistency

Relationships

account ──< identity, session, forwarding_token, user_card
user_card ──> card_product ──< rule_version
account ──< intake_receipt ──< capture_candidate
user_card ──< transaction_fact ──< correction_event, credit_link
account ──< calculation_revision ──< transaction_result, period_projection
account ──> published_revision
intake_receipt / correction_event ──< outbox_job

Records and invariants

Record	Key fields	Integrity rule
`account`	id, status, ledger_version, deleted_at	Active status required for every private operation
`identity`	issuer, subject, account_id	Unique issuer + subject; email is a profile field
`session`	token_hash, account_id, expires_at, auth_time	Store only hash; revocation checked on server
`forwarding_token`	digest, encrypted_display_value, account_id, version, revoked_at	128-bit random token; unique digest; no token in logs
`user_card`	account_id, id, product_id, last_four, statement_config_version	Composite tenant foreign keys prevent cross-account links
`card_settings_version`	card_id, effective dates, category selections, source	Statement-day and selection changes are dated
`intake_receipt`	account_id, id, raw_hash, raw_object_key, parser_version, state, expires_at	Unique account + raw hash; raw key is never a public URL
`capture_candidate`	intake_id, amount/date/card candidates, evidence, hold_reason	Held state is distinct from a posted purchase
`transaction_fact`	account_id, id, card_id, kind, original_currency, amount_minor, purchased_at, posted_at?, billed_sgd_minor?	Append preserved capture facts; credits have a separate kind
`correction_event`	account_id, transaction_id, version, patch, reason, actor, at	Monotonic version; no overwrite of source evidence
`credit_link`	credit_id, purchase_id?, allocated_minor, evidence	Link may be unknown; never double-subtract
`rule_version`	product_id, version, effective dates, source URLs + digest, schema_version, engine_support	Published versions immutable; no ambiguous overlapping standing versions
`merchant_alias`	normalized_key, readable_category, evidence, version	Public-business descriptors only; never authoritative bank MCC
`user_merchant_override`	account_id, key, category, provenance	Personal preference does not alter another account
`calculation_revision`	account_id, id, ledger_version, rule_ids, engine_version, settings_versions, reason, status	Results reproduce from explicit inputs
`transaction_result`	revision_id, transaction_id, portions, rewards, qualification, reasons	Covered + no-bonus + unclear portions reconcile to purchase amount
`period_projection`	revision_id, card/filter/period, purchases, credits, net, covered, unclear, rewards, completeness	Same revision for charts, lists and totals
`published_revision`	account_id, revision_id, ledger_version	Pointer changes only if inputs still match
`outbox_job`	id, account_id, kind, input_version, status, attempts, lease_expiry	Unique semantic job key; reclaim expired leases
`budget_counter`	scope, period, reserved, limit	Atomic reservation; fail closed when exhausted
`deletion_job`	opaque request_id, status, cursor, deadline	No raw finance data in queue or tombstone

Tenant indexes

Every private query begins with account_id. Index (account_id, card_id, purchased_at, id) for transaction pagination, (account_id, period_key, revision_id) for projections and (status, next_attempt_at, id) for bounded job dispatch. Query-plan checks must prove that filtered reads avoid global scans. Never enforce ownership only by an unguessable UUID.

Publish a consistent revision

Read inputs at ledger version L. Compute outside the write transaction. Stage results under revision R. A final D1 transactional batch publishes R only if account status is active and ledger version is still L. Use conditional writes and check affected-row counts; stale jobs discard staging and enqueue the newest version. No interactive multi-request SQL transaction is assumed.

A mutation does not promise a new numeric answer before R is complete. Pending markers and corrected facts commit together. All reads return ledger_version, calculation_revision and calculation_state. Personal read responses use Cache-Control: private, no-store.

Start without read replicas for financial reads. If replication is later enabled, use D1 Sessions with a primary first read or a returned bookmark to preserve read-your-writes. D1 Sessions.

Growth and migration

A proposed pilot guard pauses intake at 4 GB total D1 storage across testing and production. History already captured remains readable; no automatic history deletion is allowed to fit a budget. Before expanding, measure row/index bytes and revision growth. Retain explanation evidence for active-account history; compact reproducible intermediate results only under an approved retention policy.

A later Postgres move uses expand-and-contract: export tenant facts and versions, shadow-recompute, compare totals and revision digests, switch reads per account, keep rollback input copies for an approved window. Do not dual-write financial facts without an idempotent reconciliation plan.