B-9: PostgreSQL migration with connection pooling and query optimization

[KuchiMercy]

Summary

Development uses SQLite, and the TypeORM config supports PostgreSQL, but the migration path has not been validated end-to-end. Moving to PostgreSQL is required for production — SQLite does not support concurrent writes, which will fail under real load.

Requirements

• Validate all 11+ TypeORM entities work correctly with PostgreSQL (fix any SQLite-specific column types, default values, or query syntax)
• Create a complete migration script from SQLite to PostgreSQL using TypeORM migrations
• Configure connection pooling via pg-pool with sensible defaults (min: 2, max: 10, idle timeout: 30s)
• Add query performance logging in development mode (slow query threshold: 500ms)
• Create database indexes on hot query paths:
  • escrow.status — filtered on every dashboard query
  • escrow.created_at — sorted frequently
  • escrow.expires_at — used by the expiration scheduler
  • party.wallet_address — user's escrow lookup
  • escrow_event.escrow_id + created_at — timeline queries
  • notification.user_id + read — unread count queries
  • user.wallet_address — auth lookups (unique index)
• Document the migration procedure for existing deployments
• Add a health check endpoint that verifies database connectivity

Acceptance Criteria

• All entities create correct PostgreSQL tables (verified via migration:run)
• All existing API endpoints work with PostgreSQL (run full test suite)
• Connection pooling is configured and logged on startup
• Slow queries (>500ms) are logged in development
• All listed indexes are created via migration
• Health check endpoint returns database connection status
• Migration from SQLite to PostgreSQL is documented step-by-step
• No SQLite-specific syntax remains in any query or entity

Context

• Data source config: apps/backend/src/data-source.ts
• Migrations directory: apps/backend/src/migrations/
• Environment config: apps/backend/.env.example (DATABASE_PATH for SQLite)
• TypeORM is configured in: apps/backend/src/app.module.ts

Rollout Phase

Phase 5 — Admin & Production Hardening

Points: 200 (Difficult)