Rewrite, don't refactor: a year of debt, and why AWS

A flight school in Romania ran for a year on a Next.js app deployed to Vercel with a Supabase Postgres behind it. The app worked - pilots logged flights, invoices went out - but a year of fast iteration left structural debt everywhere: TypeScript and ESLint errors suppressed at build time, ~30 debug/test routes live in production, two overlapping invoice implementations plus an invoice microservice holding state in memory, no migrations framework, cosmetic RLS, and committed database credentials. Instead of refactoring in place, the whole thing was rebuilt greenfield on AWS in 11 days (194 commits, 2026-07-25 → 2026-08-04): the old app demoted to a requirements document, its database to an ETL source.

The rebuild didn't just reach parity - it shipped things the old app never had: a WhatsApp assistant members use to check hours and buy flight time (with the LLM deliberately locked out of every transaction), automated weekly scheduling over WhatsApp with a Romanian free-text parser, dual-entity invoicing across two countries and two providers behind one seam, and an AI document pipeline that reads pilot licenses better than the humans who originally typed them in.

This is Part 1: the autopsy, the decision, the planning method, the schema redesign, the ETL, the auth migration, and the cutover.

---

Series overview

By the numbers

---

1. The old stack, and what a year did to it

The v1 (cruiserapp, v0.4.3) ran on Vercel (iad1) + Supabase Postgres. The autopsy that preceded the rebuild (three parallel code explorations, condensed in docs/PLAN.md) found:

• TS/ESLint errors suppressed at build (ignoreBuildErrors) - the type system had been opted out of.
• ~30 debug/test routes deployed in production.
• Two overlapping invoice implementations, plus a separate invoice microservice that held state in memory.
• No migrations framework - schema changes were applied by hand; 78 tables of which 14 were *_backup snapshots from a UUID-migration era.
• Cosmetic RLS - row-level security that looked like a boundary but wasn't one.
• Committed credentials: a real database password in env.production.example, hardcoded in source, with the app running on those credentials in production.
• Dead WebSocket/Cloudflare configs, a hand-rolled JWT auth system (HS256 shared secret, custom refresh-token tables, tokens in localStorage), quoted camelCase columns with no FK discipline, ~16 Postgres RPCs.

Crucially, the data was fine; the structure wasn't (docs/data-audit.md):

"Referential integrity is clean. Every orphan check came back zero […] The ETL risk is much lower than the code quality suggested. The cleanup problem is structural bloat, not broken data."

And the bloat was quantifiable: users had 82 columns, ~50 of them veriff*, with Veriff data present for exactly 4 users. invoices.user_id was NULL on all 469 rows (the real link lived in a side table). The orders table for the microservice flow had 0 rows - the flow was never used in production.

---

2. Rewrite vs refactor - and why AWS

The call: 100% greenfield rebuild. The old repo becomes reference documentation only; the old DB becomes the ETL source; the old app stays frozen on Vercel until cutover (instant rollback = point DNS back).

Why not refactor in place: every load-bearing piece needed replacement - auth (hand-rolled JWT → real IdP), schema (no migrations, camelCase, no FKs), billing (two implementations + a stateful microservice), build config (errors suppressed). When the foundation and the walls are the problem, incremental replacement costs more than a rebuild with a frozen reference implementation next to it.

Why AWS specifically, vs staying on Vercel/Supabase with better discipline:

Trade-offs accepted:

• Operational surface: 9 CDK stacks × 2 accounts vs git push on Vercel. The mitigation was CDK + GitHub OIDC so deploys stay one command / one merge.
• Cold-start ripple: staging's 0-ACU autopause means every ETL script and the WhatsApp Lambda carries a DatabaseResumingException retry loop, and the Lambda timeout is 120 s mostly for Aurora wake-up.
• No more platform magic: image optimization, preview deploys, edge caching are now your problem (CloudFront config, a blur-up LQIP pipeline written by hand).
• Slower first deploy (days of SES sandbox-exit, ACM, OIDC setup) in exchange for faster everything-after.

---

3. Method: plan first, decide in writing

The first commit is not code - it's the plan (fe5da85 docs: plan, module specs, decisions, and production data audit). The method has five components:

1. A phased plan with exit criteria (docs/PLAN.md): Phase 0 old-app hygiene (day 1: rotate the committed credentials - before any build work), Phase 1 AWS foundation, Phase 2 schema+auth, Phase 3 domains, Phase 4 validation+cutover, Phase 5 decommission. Each phase has explicit exit gates: "Hard gates: SES sandbox exit before Phase 4; reconciliation clean before DNS flip."

2. A KEEP/DROP triage table as the scope contract - e.g. DROP: Veriff (~42 files/13 routes), community board, admin impersonation, weather/METAR, puppeteer heatmaps, OG image generation, the capability-matrix editor. The risk register names scope creep as risk #1 and the triage table as the antidote.

3. One functional spec per module (17 files in docs/specs/), written from the old code before building the new one.

4. A decisions log (docs/specs/DECISIONS.md) that records product rulings with rationale and keeps getting amended as reality intrudes. Examples: "PROMO flights are free" (fixing a code-vs-docs contradiction in the old app), "SmartBill has no test mode - a staging invoice is a real fiscal document", "Invites are links, not passwords."

5. A production data audit before schema design (docs/data-audit.md): row counts, orphan checks, dead-table census. This is what de-risked the ETL and produced findings like "invoices.user_id is NULL on ALL 469 rows."

Code cites the docs back: nearly every non-trivial function carries a docs/specs/<module>.md §N or DECISIONS.md #N reference, so the "why" lives at the decision site.

---

4. Schema redesign: 78 → 31

docs/schema-design.md collapses 78 tables to 26 application + 5 re-seeded reference tables (38 today after post-launch modules), with conventions stated once and enforced everywhere: snake_case, UUID PKs, FKs always declared with explicit on-delete choices, no RLS (authz is app-layer role checks), files stored as S3 keys never URLs, money as numeric(12,2) + char(3) currency, computation recompute-on-read instead of triggers or generated columns.

Notable design choices:

• ETL'd tables keep their old UUIDs - which later makes incremental delta imports trivial (id-membership is the dedup key).
• bookings gets a real exclusion constraint (EXCLUDE USING gist, btree_gist) so double-booking an aircraft is a database error, not a code path.
• The reference data (~190k rows of public airport/runway/frequency data) is re-seeded from OurAirports CSVs rather than ETL'd - cleaner and fresher than copying stale rows.
• invoices.provider is text, not an enum: "future providers need no migration" (app/src/db/schema/billing.ts).

---

5. ETL as a product: idempotent, dry-run-first, reconciliation-gated

~40 Node scripts (etl/), all sharing one shape: source = old Supabase over the session pooler, target = Aurora over the RDS Data API (no bastion, no tunnel), dry-run by default, --apply to write, rerunnable at every rehearsal and at cutover.

The gnarly transforms are documented at the top of each script:

// flights.js — 13 rows carry the §8 "ISO import" drift (local midnight stored
// as a UTC instant, e.g. 22:00 previous day) — naive ::date would shift those
// flights a day early.

// billing-data.js — the old data has 9 duplicate (series, number) groups
// (28 rows: split imports "CA0579-1/-2", junk CASH/STRIPE/TRANSFER numbering)
// and the new schema enforces uniqueness. Number is re-derived from the
// smartbill_id… the provider id is the real fiscal identity.

The headline ETL find (etl/rebuild-hour-purchases.js): the old app's importer had populated its bought-hours ledger incompletely - ~65 users were under-counted by ~1,199 hours, making them look falsely overdrawn. The rebuild re-sourced the ledger from what the old app actually billed (paid/imported invoice hour lines), not from the broken intermediate table.

The reconciliation gate is the cutover's proof of correctness (etl/reconcile-hours.js). Two properties, checked per user, exit non-zero on any diff:

(A) new bought == old bought                                  (exact)
(B) new remaining − old remaining == the user's PROMO hours
    that were chargeable under the old rules                  (0 for most)

The trick that makes this trustworthy: the reconcile script imports the production FIFO engine directly -

const { computeUsage, classifyFlight, LEGACY_EXEMPT_FLIGHT_TYPES } =
  require('../app/src/app/(app)/usage/fifo.ts');   // Node type-stripping

• so the check can't drift from the app. The engine even exports two rule sets (EXEMPT_FLIGHT_TYPES vs LEGACY_EXEMPT_FLIGHT_TYPES) so the intentional behavior change (PROMO flights become free) is reconciled explicitly instead of explained away.

---

6. Auth migration with zero password resets

Old app: bcrypt hashes in a users table under hand-rolled JWT. New app: Cognito. The bridge is Cognito's user-migration Lambda (infra/lambda/user-migration.ts): on a legacy user's first login, Cognito hands the Lambda the password, the Lambda bcrypt-verifies it against the ETL'd hash (read from Aurora over the Data API), and the user is silently minted as a CONFIRMED Cognito user. Both trigger sources are handled - normal login and forgot-password for invited-but-never-logged-in users.

The second Lambda (infra/lambda/pre-token.ts) draws the authn/authz line:

// Injects the user's roles (comma-separated list — multi-role users exist)
// into the ID token as custom:roles, and links users.cognito_sub on first
// token issuance. Authorization stays in Postgres; Cognito is authn only.

Session model in the app: Cognito-issued RS256 JWTs in httpOnly cookies, verified against Cognito's JWKS with jose - the app contains no token-issuing code and no signing secrets, a pointed contrast with the old HS256-shared-secret system.

Migration-era concessions are written down with their sunset dates (infra/lib/auth-stack.ts):

// USER_PASSWORD_AUTH is required for the migration trigger to receive
// the password; disable it (SRP only) once the import window closes.
authFlows: { userPassword: true, userSrp: true },

Only 1 of 377 users lacked a bcrypt hash (they get a reset email). Invites were later redesigned as links, not passwords: the Cognito temporary password became an invisible single-use token inside a branded /welcome link, 7-day validity.

---

7. Cutover

• Prod schema built by replaying the Drizzle journal over the Data API (etl/apply-schema.js); data cloned staging→prod via a local dump using row_to_json() / json_populate_recordset() so Postgres does all serialization, inserted in FK-topological order (etl/clone-data.js).
• The old app kept running during the build; flights logged there after the last sync were caught up with etl/import-delta-flights.js - "NEVER deletes: it inserts only old flights whose id is not already in the target" (old UUIDs preserved = free dedup key).
• DNS: app.cruiseraviation.com + ACM cert attached to prod CloudFront; rollback plan = point DNS back at Vercel, old app untouched.
• A cutover scar that became a safety rail: an early clone silently overwrote prod's real package prices with staging's. Fix: clone-data.js now skips the operator-owned pricing catalog unless --include-catalog, and the catalog price became a server-computed formula (hours × rate) with the custom-total override removed.

---

Next: Part 2 - The platform: stack choices, benefits, and trades