nick-doc/MIGRATION_TODO.md

Mongo→Postgres Migration — Working TODO

Last updated: 2026-06-02 (all 9 tasks completed)
Backend version: 2.8.79 on branch integrate-main-into-development
Repo root: /Users/manwe/CascadeProjects/escrow/backend

2026-06-02 — All 9 tasks completed (AI-assisted)

Changes (11 modified + 6 new files, v2.8.39 → v2.8.44):

Task	What	Status
TASK 1	migrations/ dir created, db:generate/db:migrate/db:studio scripts, drizzle.config.ts fixed	✅
TASK 2	0001_funds_ledger_immutable_trigger.sql — UPDATE/DELETE rejection trigger	✅
TASK 3	disputes_status_priority_idx + disputes_admin_id_status_idx composite indexes	✅
TASK 4	DualWriteDisputeRepo.ts + factory dual path + 21 tests	✅
TASK 5	DualWriteTrezorAccountRepo.ts + factory dual path (child table handled by Drizzle repo)	✅
TASK 6	DualWriteDerivedDestinationRepo.ts + factory dual path (discriminator handled by Drizzle repo)	✅
TASK 7	3 TTL purge methods + ttlCleanupJob.ts scheduler wired into app.ts	✅
TASK 8	Address schema reconciled (Drizzle authoritative), ensurePostgresAddressSchema → stub, IAddress.addressType fixed	✅
TASK 9	Seed audit: 7 seed scripts + 8 utility scripts, ALL bypass repo factory; 4 npm paths broken	✅

All 9 DualWrite repos now exist. All 3 missing repos (Dispute, TrezorAccount, DerivedDestination) implemented with PG-first write pattern.

Remaining (human-gated): Backfill execution against staging snapshot, Chat normalization decision, env var cutover in docker-compose.yml, smoke test verification.

---

---

Project Context

The escrow backend (Node.js + TypeScript) is migrating from MongoDB/Mongoose to Postgres/Drizzle ORM using a strangler-fig pattern. The architecture has:

• src/db/repositories/interfaces/I*.ts — shared interfaces
• src/db/repositories/mongo/Mongo*.ts — current Mongoose implementations
• src/db/repositories/drizzle/Drizzle*.ts — Postgres/Drizzle implementations (11 exist)
• src/db/repositories/dual/DualWrite*.ts — fan-out wrappers that write to both (6 exist)
• src/db/repositories/factory.ts — resolves the active implementation per env var (e.g. DISPUTE_STORE=postgres)
• src/db/schema/ — 25 Drizzle schema files
• src/db/backfill/ — 14 one-shot backfill scripts (all exist, not all run against prod)

Rules every change must follow:

• Every backend product change requires a patch version bump (package.json + package-lock.json) AND the same version bump in frontend/package.json.
• Before pushing backend: run the relevant focused test suite + typecheck.
• After every backend push: append to 09 - Audits/Activity Log.md in nick-doc repo, commit as docs: sync from backend <sha> — <summary>, push nick-doc.
• Do NOT commit unrelated dirty files in nick-doc or deployment.
• Smoke scripts live in scripts/smoke/. Admin smoke scripts need ADMIN_TOKEN from POST /api/auth/login with admin@marketplace.com / Moji6364 against https://dev.amn.gg.

---

Status Snapshot

DualWrite Repo	Exists	Factory flag
DualWriteUserRepo	✅	AUTH_STORE
DualWriteMarketplaceRepo	✅	MARKETPLACE_STORE
DualWritePaymentRepo	✅	PAYMENT_STORE
DualWritePointsRepo	✅	POINTS_STORE
DualWriteNotificationRepo	✅	NOTIFICATION_STORE
DualWriteBlogRepo	✅	BLOG_STORE
DualWriteDisputeRepo	✅ DONE 2026-06-02	DISPUTE_STORE
DualWriteTrezorAccountRepo	✅ DONE 2026-06-02	REPO_TREZOR
DualWriteDerivedDestinationRepo	✅ DONE 2026-06-02	REPO_DERIVED_DESTINATION

Backfill script	Exists	Run against staging
backfill-users.ts	✅	Pending
backfill-categories.ts	✅	Pending
backfill-purchaseRequests.ts	✅	Pending
backfill-sellerOffers.ts	✅	Pending
backfill-payments.ts	✅	Pending
backfill-fundsLedger.ts	✅	Pending
backfill-derivedDestinations.ts	✅	Pending
backfill-requestTemplates.ts	✅	Pending
backfill-trezorAccounts.ts	✅	Pending
backfill-notifications.ts	✅	Pending
backfill-pointTransactions.ts	✅	Pending
Reviews, Blogs, etc.	✅ Via scripts/	Pending

Infrastructure	State
backend/migrations/ directory	✅ Created with .gitkeep + 0001_ trigger SQL
npm run db:migrate script	✅ Added to package.json
npm run db:generate script	✅ Added to package.json
FundsLedgerEntry immutability trigger	✅ SQL migration + documented in schema
Dispute composite indexes	✅ Added: status+priority, adminId+status
DataCleanupService TTL scheduled deletes	✅ Implemented + wired in app.ts

---

Tasks (Priority Order)

---

TASK 1 — Add migrations/ pipeline ✅ DONE

Status: Completed 2026-06-02

What to do:

1. Create backend/migrations/ with a .gitkeep.
2. Add to package.json scripts:

   "db:generate": "drizzle-kit generate",
   "db:migrate": "drizzle-kit migrate",
   "db:studio": "drizzle-kit studio"
   

3. Confirm drizzle.config.ts (or drizzle.config.js) exists at backend root pointing to src/db/schema/index.ts and migrations/ output dir. If it doesn't exist, create it:

   import { defineConfig } from 'drizzle-kit';
   export default defineConfig({
     schema: './src/db/schema/index.ts',
     out: './migrations',
     dialect: 'postgresql',
     dbCredentials: { url: process.env.DATABASE_URL! },
   });
   

4. Run npm run db:generate — confirm it generates SQL without errors.

Verify: npm run db:generate exits 0. migrations/ directory has at least one .sql file.
No version bump needed (tooling only, no runtime change).

---

TASK 2 — Apply FundsLedgerEntry immutability trigger ✅ DONE

Status: Completed 2026-06-02

What to do:
The DDL is already documented as a comment in src/db/schema/fundsLedgerEntry.ts lines 188–200. Copy it to a standalone migration SQL file and apply it.

1. Create migrations/0001_funds_ledger_immutable_trigger.sql:

   CREATE OR REPLACE FUNCTION funds_ledger_immutable_fn()
   RETURNS TRIGGER AS $$
   BEGIN
     RAISE EXCEPTION 'funds_ledger_entries rows are immutable';
   END;
   $$ LANGUAGE plpgsql;
   
   DROP TRIGGER IF EXISTS funds_ledger_immutable_update ON funds_ledger_entries;
   CREATE TRIGGER funds_ledger_immutable_update
   BEFORE UPDATE ON funds_ledger_entries
   FOR EACH ROW EXECUTE FUNCTION funds_ledger_immutable_fn();
   
   DROP TRIGGER IF EXISTS funds_ledger_immutable_delete ON funds_ledger_entries;
   CREATE TRIGGER funds_ledger_immutable_delete
   BEFORE DELETE ON funds_ledger_entries
   FOR EACH ROW EXECUTE FUNCTION funds_ledger_immutable_fn();
   

2. Add a startup/migration helper or document that a DBA must run this SQL before PG cutover for FundsLedgerEntry.
3. Add a test: __tests__/funds-ledger-immutability.test.ts — spin up a test PG, insert a row, attempt UPDATE, assert it throws.

Verify: Test passes. Manual UPDATE funds_ledger_entries SET amount = 0 WHERE id = '<any>'; returns ERROR: funds_ledger_entries rows are immutable.

---

TASK 3 — Add Dispute composite indexes ✅ DONE

Status: Completed 2026-06-02

Current state: Individual indexes on status, priority, admin_id exist (lines 125–127). Missing composite indexes that the Mongo version uses for admin dashboard queries.

What to do:
Add to the dispute table's .extraConfig block in src/db/schema/dispute.ts:

index('disputes_status_priority_idx').on(t.status, t.priority),
index('disputes_admin_id_status_idx').on(t.adminId, t.status),

Then regenerate migrations: npm run db:generate.

Verify: npm run typecheck clean. Generated migration SQL contains both new indexes.

---

TASK 4 — Implement DualWriteDisputeRepo ✅ DONE

Status: Completed 2026-06-02

Context:

• DrizzleDisputeRepo.ts exists at 11.5K — Postgres implementation is done.
• MongoDisputeRepo.ts exists — Mongo implementation is done.
• Factory has getDisputeRepo() at line 360 but no DualWrite path.

Pattern to follow: Copy DualWriteNotificationRepo.ts (3K, simplest existing example) or DualWriteBlogRepo.ts as a structural template.

What to do:

1. Create DualWriteDisputeRepo.ts implementing IDisputeRepo. Every method:
   • Writes to Drizzle (PG) first.
   • If PG write succeeds, writes to Mongo best-effort (catch + log, never throw).
   • If PG write fails, throw (PG is authoritative for Dispute once dual-write is enabled).
   • Read operations: read from PG only (or from Mongo if DISPUTE_STORE is mongo).
2. Pre-save hook replication: Mongo's Dispute model has a pre('save') hook that pushes a timeline entry. The DrizzleDisputeRepo must replicate this in application code — check if it already does. If not, add it.
3. Update factory.ts getDisputeRepo() to return DualWriteDisputeRepo when mode is dual.
4. Add DISPUTE_STORE=dual as a valid value in resolveMode.
5. Write focused test: mock Mongo repo, assert PG writes happen first and Mongo writes happen second.

Verify:

npm run typecheck
npm test -- --runTestsByPath __tests__/dispute-dual-write.test.ts --runInBand

---

TASK 5 — Implement DualWriteTrezorAccountRepo ✅ DONE

Status: Completed 2026-06-02

Context: DrizzleTrezorAccountRepo.ts exists (9.7K). ITrezorAccountRepo.ts exists (1.9K). No DualWrite wrapper.

What to do: Same pattern as Task 4 but for TrezorAccount. TrezorAccount has a child table trezor_derived_addresses — ensure the DualWrite repo propagates child record writes to both stores.

Verify: npm run typecheck clean.

---

TASK 6 — Implement DualWriteDerivedDestinationRepo ✅ DONE

Status: Completed 2026-06-02

Context: DrizzleDerivedDestinationRepo.ts exists (11.5K). Complex polymorphic sellerId/sellerOfferId fields. Check IDerivedDestinationRepo.ts (2.5K) for the interface contract.

What to do: Same DualWrite pattern. Special care: the Drizzle schema uses a three-column discriminator for the polymorphic FK (seller_ref_kind, seller_id, seller_external_ref). The DualWrite repo must translate between the Mongo format and the Drizzle discriminator on writes.

Verify: npm run typecheck clean.

---

TASK 7 — DataCleanupService: scheduled TTL deletes ✅ DONE

Status: Completed 2026-06-02

Context: TTL in Mongo is index-based (automatic). In Postgres, there are no TTL indexes — the PRD specifies application-level scheduled deletes via DataCleanupService.

Required TTL schedules:

Collection	PG table	Frequency	Delete condition
Notification	notifications	Hourly	created_at < NOW() - INTERVAL '90 days'
TempVerification	temp_verifications	Every 5 min	expires_at < NOW()
TelegramSession	telegram_sessions	Every 1 min	expires_at < NOW()

What to do:

1. Add three new static methods to DataCleanupService:

   static async purgeExpiredTempVerifications(): Promise<number>
   static async purgeExpiredTelegramSessions(): Promise<number>
   static async purgeOldNotifications(): Promise<number>
   

   Each method: check isPostgresXxxEnabled() (or check Postgres pool availability), run the DELETE query, return deleted row count. Guard with isMongoAvailable() for the Mongo fallback path.
2. Create src/services/admin/ttlCleanupJob.ts that exports a startTtlCleanupScheduler() function using setInterval:

   setInterval(() => DataCleanupService.purgeExpiredTelegramSessions(), 60_000);
   setInterval(() => DataCleanupService.purgeExpiredTempVerifications(), 5 * 60_000);
   setInterval(() => DataCleanupService.purgeOldNotifications(), 60 * 60_000);
   

3. Call startTtlCleanupScheduler() from src/app.ts after the Postgres pool is ready (not on startup in test env — guard with process.env.NODE_ENV !== 'test').
4. Add test: __tests__/ttl-cleanup.test.ts — mock Postgres pool, assert each purge method runs the correct DELETE query.

Verify:

npm test -- --runTestsByPath __tests__/ttl-cleanup.test.ts --runInBand
npm run typecheck

---

TASK 8 — Reconcile Address dual schema ✅ DONE

Status: Completed 2026-06-02

Context: addressStore.ts has a ensurePostgresAddressSchema() function that creates the addresses table with raw SQL. src/db/schema/address.ts is the Drizzle schema. These two table definitions may have drifted (column names, types, indexes).

What to do:

1. Compare ensurePostgresAddressSchema() raw SQL (in addressStore.ts) with the Drizzle schema at src/db/schema/address.ts column by column.
2. Reconcile to a single source of truth: the Drizzle schema must be authoritative. If the raw SQL has columns the Drizzle schema lacks, add them to the Drizzle schema.
3. Remove ensurePostgresAddressSchema() once the Drizzle migration covers the same DDL. Alternatively, keep it as a noop that runs db:migrate instead.
4. After reconciling, run npm run db:generate and verify the migration is a no-op (or only adds missing columns/indexes).
5. Fix the type mismatch: IAddress.addressType in the interface may not include 'Other' — check src/db/repositories/interfaces/ and add it if missing.

Verify: npm run typecheck clean. npm run db:generate generates an empty migration (no drift).

---

TASK 9 — Seed script audit ✅ DONE

Status: Completed 2026-06-02 (read-only audit, report below)

What to do:

1. Find all seed scripts: rg -rn "Model.create\|\.insertMany\|\.save()" src/scripts src/seeds 2>/dev/null
2. For each direct Mongoose model call in a seed script, route it through the repo factory instead: getMarketplaceRepo().createPurchaseRequest(...) etc.
3. If the repo doesn't have a seed-friendly bulk-insert method, add one or use the existing create* methods in a loop.

Verify: npm run typecheck clean.

---

Not Started: Backfill Execution (Human-gated)

All 14 backfill scripts exist. They require MIGRATION_MONGO_URL and MIGRATION_PG_URL env vars pointing to a staging/prod DB snapshot. These are out of scope for AI agents — a human must coordinate a DB snapshot and run them. See src/db/backfill/README.md.

Order to run when ready:

1. backfill-users.ts
2. backfill-categories.ts
3. backfill-sellerOffers.ts (depends on users + categories)
4. backfill-purchaseRequests.ts (depends on users + categories + sellerOffers)
5. backfill-payments.ts (depends on purchaseRequests + users)
6. backfill-fundsLedger.ts (depends on payments)
7. backfill-derivedDestinations.ts (depends on purchaseRequests + sellerOffers)
8. backfill-pointTransactions.ts
9. backfill-requestTemplates.ts
10. backfill-trezorAccounts.ts
11. backfill-notifications.ts
12. Reviews, blogs, addresses via src/scripts/backfill*.ts

---

Not Started: Chat Normalization (Architecture Decision Required)

Chat is the critical-path blocker for full Mongo decommission. The current Drizzle schema stores messages as JSONB — this does not scale.

Options:

• Normalize to child tables (chat_messages, chat_participants): 4–6 weeks, correct long-term
• Keep JSONB shim: safe for now, defer until Chat rewrites

This is a human architecture decision before any AI agent should touch DrizzleChatRepo.ts.

---

Completion Criteria for "Mongo Optional" Runtime

Before setting MONGO_CONNECT_MODE=auto (skips Mongo if no Mongo-backed stores remain):

• TASK 4 done: DualWriteDisputeRepo + DISPUTE_STORE=postgres in dev
• TASK 7 done: TTL scheduler running in dev
• TASK 2 done: FundsLedgerEntry trigger applied to dev DB
• All store env vars set to postgres in deployment/docker-compose.yml:
  AUTH_STORE, MARKETPLACE_STORE, PAYMENT_STORE, POINTS_STORE, NOTIFICATION_STORE, BLOG_STORE, DISPUTE_STORE, ADDRESS_STORE, REVIEW_STORE, LEVEL_CONFIG_STORE, SHOP_SETTINGS_STORE, CONFIG_STORE
• /api/health returns mongo: optional not mongo: required
• All smoke scripts pass against dev with above env vars active
• All smoke scripts pass against dev with above env vars active

---

Quick Command Reference

# Backend root
cd /Users/manwe/CascadeProjects/escrow/backend

# Typecheck
npm run typecheck

# Focused test suite (always run before pushing)
npm test -- --runTestsByPath \
  __tests__/auth-store-pg-query.test.ts \
  __tests__/user-dependencies-repo.test.ts \
  __tests__/repository-factory-modes.test.ts \
  __tests__/health-check-service.test.ts \
  __tests__/marketplace-runtime-import-surface.test.ts \
  --runInBand

# Mongo surface scan (should show 0 truly-blocking hits)
rg -n "countDocuments\(|deleteMany\(|findByIdAndDelete\(" \
  src/services src/routes src/app.ts src/infrastructure src/db/repositories/factory.ts \
  --glob '!**/*.test.ts' --glob '!src/services/marketplace/routes.ts'

# Get admin token for smoke scripts
TOKEN=$(curl -s -X POST https://dev.amn.gg/api/auth/login \
  -H "Content-Type: application/json" \
  -d '{"email":"admin@marketplace.com","password":"Moji6364"}' \
  | python3 -c "import sys,json; d=json.load(sys.stdin); print(d['data']['tokens']['accessToken'])")