Postgres migration — Phase 0 decision memo

Status: complete (2026-05-14). Sheets decommissioned in #640; the codebase is now Postgres-only and the dispatcher/dual-write machinery described below has been removed. The historical record stays in place because it shaped every design choice that survives.

Issue: #438 · Branch: merged to main · Date: 2026-04-21 · Phase: 0 (scoping)

This memo records the three Phase 0 decisions called for in #438 (host, migration tool, confidence window) and summarises the contract that Phase 1 must preserve when it rewrites the DAL. It supersedes the pre-trigger plan at migration-plan.md (2026-03-23) — that file remains for historical reference but the dispatcher + dual-write architecture in #438 is now canonical.

---

Status as of 2026-04-24 — Phase 2.5 complete, Phase 3 ready

Phases 0 through 2.5 have landed on main. The data access layer is now a dispatcher (lib/dal.js) with two backends (lib/dal.sheets.js, lib/dal.postgres.js) and a per-table BACKEND map routing to sheets / postgres / both.

What's live:

• All 14 tables are on 'both' mode. Sheets is the primary write path; Postgres receives a best-effort shadow-write on the same transaction, gated by the PG_SHADOW_WRITE_TENANTS env allowlist (fail-closed: unset = no pg writes anywhere). Reads still come from Sheets exclusively.
• Legacy value coercion on the pg path. toEmploymentType (#478) and toProjectType (#480) normalise casing so old rows like "FTE" or "RETAINER" satisfy the Postgres enum constraints on the shadow-write path without touching the canonical Sheets value.
• No raw Sheets writers remain in callers. Issue #482 (Clusters A–E, PRs #483–#487, merged 2026-04-23/24) closed 12 bypass sites across mcp-server.js where spreadsheets.values.append/update or appendRowsToSheet were called directly. Every write now dispatches through lib/dal.js so pg shadow-writes fire on every code path. This is an invariant going forward; CLAUDE.md's DAL section documents the enforcement rule.
• New typed updaters on both backends (Clusters A–E): updateClientName, updateProjectStatus, updateProjectType, updateProjectIsBillable, updateProjectContractValue, updateProjectBillingRate, updateProjectAlertThreshold, updateProjectNotes, updateUserSlackId, updateUserEmploymentType, updateProjectPeriodStatus, updateUnresolvedItemStatus. update_project was refactored from a full-row rewrite into per-field DAL calls; updateProjectBudget/updateProjectDeadline were hardened to accept ''/null as the clear signal.
• rename_client tool shipped (#481, PR #483) — owner-only, Internal-protected, collision-checked.
• Test suite: 455/455 on main.

What's next — Phase 3 (read migration):

Flipping one table at a time by changing BACKEND[table] from 'both' to 'postgres' switches reads (and writes) for that table to pg. Pre-flip checklist per table:

1. Run the diff-store reconciliation job on T0ADQ6UAVKR across a representative time window; confirm zero mismatches.
2. Confirm every FK parent is already on 'postgres' (or still on 'both' with reconciled rows — a 'postgres' child cannot FK to a Sheets-only parent).
3. Flip, deploy, watch for 24–48h, then either promote to the next table or roll back by flipping back to 'both'.

Tables should flip in dependency order: tenants (not in the dispatcher; already the fixed reference) → clients, users, disciplines, tasks, company_calendar (no FK parents) → projects (FK → clients, users) → everything else (FK → projects + users).

The 8-week confidence window (§0.4 below) starts when the first table flips to 'postgres'. Phase 4 deletes the Sheets path only after zero mismatches and zero flag flips across the window.

0.1 — The contract: lib/dal.js

lib/dal.js (757 lines) is the only module that touches Google Sheets by column index. Phase 1's dal.postgres.js must expose the same public API so callers do not change. Consumers are eight files; the DAL is imported 392 times across the repo.

File	Import count
mcp-server.js	207
envelopes.js	51
budgets.js	37
tests/mcp-routes.test.js	17
rates.js	10
liabilities.js	9
lib/availability.js	3
lib/unresolved.js	3
demo-seed.js	2

Exported surface (56 symbols):

• Cache — cachedSheetGet, setCacheEntry, bustCache
• WAL — addTimeEntryWALEntry, getTimeEntryWALEntries, addBudgetWALEntry, addConversationWALEntry, getConversationWALEntries
• Helpers — sanitizeSlackId, findUserByName, findProjectByName, buildIndex
• Readers (12) — readUsers, readProjects, readTimeEntries, readTasks, readProjectTasks, readClients, readBudgets, readDisciplines, readCompanyCalendar, readPendingPTO, readRateHistory, readLiabilities
• Generic writers — appendToSheet, appendRowsToSheet, updateCell, updateRange
• Typed appends (14) — appendTimeEntry, appendProject, appendUser, appendClient, appendTask, appendProjectTask, appendBudget, appendDiscipline, appendConversation, appendPendingPTO, appendCalendarEvent, appendRateHistory, appendLiability, appendUnresolvedItem
• Typed updates (~20) — users (role/name/status), projects (PM/budget/deadline/name/period_status/period_start/period_end), time entries (row/user/project/task), budgets (hours/status/task), tasks (name), PTO (approval/rejection), unresolved items (status)

Contract-preserving notes for Phase 1:

• cachedSheetGet(range) and setCacheEntry survive the cutover window — they stay in the dispatcher layer so the Sheets path continues to work during dual-write. Deleted in Phase 4.
• WAL exports (add*WALEntry, get*WALEntries) stay in the dispatcher and continue to back the Sheets path during dual-write. Once Postgres is the source of truth the WAL is obsolete and removed in Phase 4; Postgres itself has read-after-write consistency so no equivalent is needed.
• bustCache(tabName) becomes a no-op on the Postgres path; the Sheets path still calls it, so the dispatcher keeps the export.
• sanitizeSlackId, findUserByName, findProjectByName, buildIndex are pure helpers — no backend involvement. They live in a shared lib/dal-helpers.js and are re-exported by both backends.
• mcp-server.js has two require('./lib/dal') blocks (line 12 and line 1409). Consolidate into one in Phase 1.5 when the dispatcher lands.

0.2 — Host: Supabase

Decision: Supabase (two projects: vera-staging, vera-prod). Connect via raw pg driver. Do not adopt supabase-js.

Criterion	Railway PG	Neon	Supabase
Co-located with app (Railway)	✅	➖ network hop	➖ network hop
Staging/prod split on free tier	➖ paid	✅ branches	✅ two projects
Row-level security first-class	➖ plain PG	➖ plain PG	✅
Data-inspection UI (replaces Sheets' visual feel for the owner)	❌	➖ limited	✅ Table editor
Point-in-time recovery on free/low tier	❌	✅	✅
Works with raw pg driver (no SDK lock-in)	✅	✅	✅

Why Supabase wins:

• The Sheets-to-Postgres migration threatens one genuine Sheets benefit: Tom can see the data without writing SQL. Supabase's Table editor preserves that affordance at zero extra cost.
• #438 mandates RLS for tenant isolation. Supabase makes RLS the default path; plain Postgres hosts treat it as an expert feature.
• Two free-tier projects cover staging and production with no billing change. A paid plan ($25/mo Pro) is justified at cutover for daily backups + PITR. Still cheaper than the $5–6/mo Railway footprint plus the time cost of running our own backup cron.

Connect via raw pg only. supabase-js adds an HTTP proxy layer (PostgREST) that this codebase has no use for — our reads are all parameterised SQL and benefit from direct TCP. Using pg also means Supabase is a host, not a framework: if we ever outgrow it, the swap is a connection-string change.

Region (resolved in Phase 1.1, 2026-04-21): Both Railway app and Supabase prod pinned to us-east-1. Project ref gdqmtevlnumwixevcmgg.

0.3 — Migration tooling: node-pg-migrate

Decision: node-pg-migrate for schema migrations. Raw parameterised SQL for queries (no ORM, no query builder).

Criterion	node-pg-migrate	drizzle-kit	prisma
Migration format	Plain .sql / .js up/down	TS schema → generated SQL	Schema DSL → generated SQL
Query layer	Caller's choice (pg)	Drizzle query builder	Prisma client
TypeScript required for value	No	Yes (VERA is plain JS)	No but heavy
Owner can read migrations without learning a DSL	✅	➖	❌
Depth of buy-in	Shallow	Medium	Deep

Why node-pg-migrate wins:

• VERA is plain JavaScript. Drizzle's value is its TypeScript schema + type-safe queries. Without TS that value evaporates and we'd be adopting a framework for its migration runner alone — that's what node-pg-migrate already does, smaller.
• The existing DAL's "typed update helpers" (updateProjectPM, updateBudgetHours, etc.) already give us a small, named, typed-in-practice surface. A query builder is solving a problem we don't have.
• Migrations as readable SQL files match how Tom inspects Sheets today — the primary artifact stays human-readable.
• Escape hatch: if the DAL grows past ~50 typed query sites and type safety starts to pay off, swapping node-pg-migrate + pg for Drizzle later is ~1 week of work and doesn't touch the migration history.

0.4 — Confidence window: 8 weeks (accept default)

Decision: 8-week confidence window before Phase 4 deletes the Sheets path. No shortening.

Rationale:

• Two full month-end cycles pass in 8 weeks. Every retainer is exercised at least twice under the new backend.
• Snapshot-dependent questions ("show me project spend on Nov 13") test the daily-snapshot cron — those surface monthly, not weekly.
• Phase 4's "delete the Sheets path" commit is gated on zero diff-store mismatches and zero flag flips over the window. Any incident resets the clock by the same amount, per #438.
• Earliest realistic Phase 4 date, assuming Phase 1 starts 2026-04-22 and each phase lands on its estimated duration: Phase 1 ends ~2026-05-06, Phase 2 ends ~2026-05-14, Phase 3 ends ~2026-05-21, confidence window closes ~2026-07-16, Phase 4 ends ~2026-07-22. Add 25% buffer → ~2026-08-05.

Go/no-go signal for Phase 1

All three decisions are recorded. The contract in §0.1 is the surface Phase 1 must preserve. Phase 1 opens with:

1. Provision Supabase vera-staging + vera-prod; record connection strings in Railway env vars (step 1.1).
2. Add pg, pg-pool, node-pg-migrate to package.json (step 1.2).
3. Write migrations for all 14 tables using CLAUDE.md's column-index tables as the authoritative source (step 1.3).

No code in this branch yet. This memo is the only artifact of Phase 0.