[CRITICAL] Migrate database from SQLite to PostgreSQL with connection pooling

[kilodesodiq-arch]

Engineering Gap

Prisma schema at app/backend/prisma/schema.prisma line 2 uses provider = "sqlite". SQLite is single-file, single-writer — unsuitable for concurrent production workloads. 37 models would experience SQLITE_BUSY errors. PrismaService has simple connect/disconnect with no pooling. Meanwhile, app/backend/src/idempotency/store.ts already imports pg.Pool — PostgreSQL assumed for some features.

---

Codebase Evidence

• app/backend/prisma/schema.prisma line 2: provider = "sqlite"
• app/backend/prisma.config.ts line 9: falls back to file:./dev.db
• app/backend/src/prisma/prisma.service.ts: No pooling, retry, or transaction timeout
• app/backend/src/idempotency/store.ts: Already uses pg.Pool
• .github/workflows/backend-ci.yml: No PostgreSQL service container

---

Risk Profile

Current Risk

Multi-instance deployment causes SQLITE_BUSY and data corruption. No horizontal scaling.

Failure Scenario

2+ backend instances behind load balancer. Concurrent writes cause database locks. API returns 500 errors. Database file corrupted — all data lost.

Business Impact

Complete operational failure under load. Aid distribution halted. Humanitarian operations compromised.

---

Remediation Strategy

1. Change provider to postgresql 2. Create migration 3. Configure connection pooling 4. Update CI with PostgreSQL container 5. Document backup strategy (pg_dump, WAL).

---

Success Conditions

• Provider changed to postgresql
• Migration created and verified
• All e2e tests pass against PostgreSQL
• CI includes PostgreSQL container
• Backup strategy documented

---

Change Surface

Files: prisma/schema.prisma, prisma.config.ts, prisma.service.ts, .env.example, backend-ci.yml

---

Security Review

PostgreSQL enables proper auth, TLS, access control — security improvement over SQLite.

---

Completion Checklist

• Implementation completed
• Peer reviewed
• Tests passing
• Security review completed
• Ready for merge