ADR 0004 — Database split: one Postgres 18 instance, six databases

Status

Accepted 2026-10-23 (owner Q5, confirmed by Q13).

Context

ddx-api needs six Prisma clients: auth, main, audit, logs, ai, communication. Each should migrate and back up on its own. Evidence: ../research/rq4-db-split.md. Version: PostgreSQL 18 (../versions.md).

Decision

One PostgreSQL 18 container (ddx-docker-postgres/, host port 16330, mapping in ../ports.md) with one separate database per Prisma client: ddx_auth, ddx_main, ddx_audit, ddx_logs, ddx_ai, ddx_communication. An init script creates the six databases. Each client has its own schema.prisma, migration history and DATABASE_URL_*.

Rejected alternative

One database, one schema per client. Rejected: partial restore needs schema filters, cross-schema joins tempt code past the service layer, and each client still needs its own migration history anyway.

Consequences

• No SQL join across domains; services join in code.
• pg_dump -d <db> backs up one domain alone.
• prisma-main, prisma-ai and prisma-communication go only through TenantPrismaService with the org filter; no $queryRaw (invariant 2).
• Six connection pools on one instance; pool sizes stay small in dev.