Skip to main content

ADR-019: Database-per-service — per-context schemas at MVP, separate databases on extraction

Context​

The NestJS monolith's domains today share one Prisma schema (apps/api/prisma/schema.prisma) with cross-domain foreign keys (payroll → employee → org). The Go ledger and integration-gateway already own separate Postgres databases (LEDGER_DATABASE_URL, GATEWAY_DATABASE_URL). For services to be independently deployable and replaceable (ADR-018), no service may read another's tables — but one shared schema makes that impossible to enforce and turns "extract a service" into a data-untangling project.

Decision​

Each bounded context owns its data. No cross-context foreign keys, no cross-context joins, no service querying another service's tables. Cross-context references are by ID, resolved via the owning context's gRPC port or via event-fed read-model projections — never a SQL join.

Adoption is progressive, matching ADR-018:

  • MVP: carve the shared schema into per-context Postgres schemas in one instance (Identity, Tenancy, Workforce, Payroll, KYC). Cheap to do pre-launch; drops the cross-context FKs.
  • On extraction: promote a context's schema to its own database (already true for ledger + gateway).
  • At scale: partition/shard the hot stores by tenant (ledger first).

Alternatives considered​

  • Keep one shared schema — rejected: forfeits independent deployment, lets cross-domain joins re-grow, makes extraction a rewrite.
  • Separate databases immediately for every domain — rejected for MVP: operational overhead before launch; per-context schemas in one instance get the boundary discipline at a fraction of the cost.

Consequences​

  • Positive: services become independently deployable/scalable/replaceable; the boundary is enforceable (a lint/schema check can forbid cross-context FKs); extraction is a lift, not an untangle.
  • Negative / accepted: cross-context reads cost a port call or a projection instead of a join — more code, and eventual consistency for projections; referential integrity across contexts is enforced in application code, not the DB.
  • Follow-ups: the schema carve-out is the spine of the alignment plan (do it pre-launch, expand-contract, one context at a time); RLS stays FORCE per context (ADR-013) and must be proven under a non-superuser role.

Revisit when​

  • A context is extracted → its schema becomes its own database.
  • A store's write volume needs partitioning/sharding → tenant is the shard key.