Database
PostgreSQL stores versioned review bodies and a current projection. An append transaction keeps saved review data and its derived lists consistent.
Tables
| Table | Key and purpose |
|---|---|
| snap_review_snapshots | (review_key UUID, version); complete review JSON, actor, saved time, globally unique request_id. |
| review_index | review_key UUID; current list fields/version/report JSON. Unique (program, fiscal_year, review_number). |
| review_touches | (staff_id, review_key); last touch, read_version, personal flag. |
| inbox_events | Derived staff news; ID identifies request/recipient/kind. |
| inbox_visits | staff_id; last-seen watermark. |
| snap_policy_tables | Published amount-table JSON; unique ID and effective month. |
Read and append
reviewStore.load selects the highest snapshot version for a UUID.
findBusiness joins the index to its current snapshot for an import identity.
findRequest supports save retries. The protected store adapter binds legacy
rules on read and wraps storage faults as ReviewStoreUnavailableError; it
does not grant access.
append loads the previous review to derive news, computes report fields, then
batches snapshot insert, index upsert, optional event inserts, and saving staff’s touch.
The snapshot primary key makes a simultaneous same-version append fail. Persistence
resolves that failure as a repeated request or a stale version. Initial intake is version
0.
Read projections
reviewListColumns excludes report JSON. Inbox uses a narrower subset; catalog
uses its own list shape. Search can extract requested nested values from the current
snapshot. Reports read only the required report projection. Full snapshot history is not a
list API.
Driver and migrations
getDb selects node-postgres for localhost/127.0.0.1 and Neon HTTP otherwise.
The local batch wrapper executes statements in one transaction. Database
tooling selects development DATABASE_URL or production
PROD_DATABASE_URL. Migration tooling uses a direct pg connection, checks
history, and emits redacted diagnostics.
Apply migrations through 0008. 0006 introduced FFY-qualified text keys; 0007 replaced them with UUIDs across snapshots, index, events, and touches, rewrote JSON IDs, and added the business-identity constraint. 0008 makes the report projection required. Historical SQL records prior schema states; it is not the current URL contract.
Changing stored data
For SQL columns, change the Drizzle schema, generate and inspect the migration, and test
existing-row population. For JSON fields, update types, defaults/read compatibility,
command validation, and derived projections. Never assume a TypeScript change rewrites
stored snapshots. Report-rule changes need db:backfill after validation; it
rebuilds all three report fields for every review with a version guard.