PostgreSQL — Multi-Schema Database Infrastructure

PostgreSQL — Multi-Schema Database Infrastructure

A single consolidated PostgreSQL 16 container (ddx-001-postgres-1, host :6432) hosts every application schema Prisma manages plus the Hibernate-managed ddx_fhir_core FHIR clinical store. Domain separation is enforced at the database level (10 logically-isolated databases, one ORM per database) and at the connection-string level (Prisma never carries a URL to ddx_fhir_core), not by separate containers.

DRIFT NOTE (corrected 2026-07-02): earlier revisions of this page described two containers (ddx-hms-postgres-1 app + ddx-hms-postgres-fhir-1 FHIR on :15433). The dev stack was consolidated to a single ddx-001-postgres-1 on :6432 (memory [[project-hms-port-discipline]] + context/ENVIRONMENT.md); ddx_fhir_core now lives in the same instance, created by init-fhir-database.sh. There is no :15433 port and no separate FHIR container.

Business Purpose

Every clinic running on Dudoxx HMS depends on PostgreSQL for the data that is regulated, billed, audited, and clinically load-bearing: patient demographics, appointments, prescriptions, invoices, audit trails, and the AI/voice-session metadata that powers the platform's automation. The infrastructure splits this into the Prisma application schemas plus the FHIR clinical store as separate databases in one instance, so a healthcare auditor can reason about each compliance surface independently and so the platform can offer per-domain backup/restore and data-residency guarantees without coupling operational releases to FHIR schema migrations. The single-container design still prevents the most common cross-cutting incident in healthcare platforms — an ORM migration accidentally rewriting clinical tables — because ddx-api has no Prisma schema or connection string pointing at ddx_fhir_core: the isolation is at the ORM/connection layer, not the container layer.

Audiences

• Investor: Logically-separated application databases + an isolated FHIR clinical database (one ORM each) is the data-isolation story we sell to enterprise clinics, GDPR auditors, and partners — it converts the abstract "compliance" promise into a concrete, demonstrable boundary (10 databases, two ORMs, per-database backup paths). In a hardened/HA deployment the FHIR store can be split back onto its own instance without touching application code (the connection strings already point at a distinct DB).
• Clinical buyer (doctor/nurse/receptionist): Invisible to end users, but the reason the system can keep audit logs, AI session traces, and patient records strictly partitioned is this multi-database layout — your clinical data never sits in the same table as a chatbot transcript.
• Developer/partner: Five Prisma clients (PrismaService, PrismaAiService, PrismaLoggingService, PrismaComService, PrismaAccountingService) map 1:1 onto their application databases on localhost:6432 (POSTGRES_PORT). The FHIR database ddx_fhir_core lives in the same instance but is only accessible through HAPI FHIR REST — never Prisma, never raw SQL. Each schema lives under its own ddx-api/prisma-*/ folder; never mix clients between schemas.
• Internal (ops/support): One container, one volume, one healthcheck. pg_isready runs every 10s; restart policy unless-stopped. Backup scripts operate per-database and are wired into pnpm run prisma:backup; ddx-api/scripts/safe-reset.sh snapshots ALL databases (including ddx_fhir_core) before any reset.

Architecture

One container runs on the Docker network (${DDX_NETWORK_NAME} = ddx-001-network), exposes host port :6432, and mounts one named volume. It hosts the five Prisma-owned application databases, four companion databases (ddx_doxing, ddx_ocr, ddx_voice, ddx_api_saas) created by init-databases.sh, and the ddx_fhir_core database created by init-fhir-database.sh whose schema is managed by HAPI FHIR's Hibernate JPA layer — 10 databases total.

On the ddx-001-network, container ddx-001-postgres-1 (postgres:16-alpine, host :6432 → container :5432) hosts 10 databases:

The container name / port / volume / network all derive from DDX_DEPLOYMENT_ID (ddx-001) in .env.docker — a single knob renames the whole stack (see [[project-deployment-id-container-naming]]). The compose defaults (ddx-hms-postgres-1, :15432, :15433) are fallbacks the live env overrides.

Architecture anchor: see coding_context/ddx-hms-context.md for the full HMS service topology and the relationship between the Prisma databases and the NestJS module groups that consume them. Per-schema dimensions (model count, enum count, env vars) are kept canonical in docs/claude-context/CLAUDE_DATABASE.md; this page describes the infrastructure layer, not the data model.

Tech Stack & Choices

• Engine: PostgreSQL 16 on the postgres:16-alpine image, pinned via POSTGRES_IMAGE in ddx-docker-postgres/docker-compose.yml:30 and :87. Alpine keeps the image small (~80 MB) and the production-tuned config file is mounted read-only.
• Configuration: postgresql.conf + pg_hba.conf mounted from ddx-docker-postgres/config/ (read-only volume mounts at docker-compose.yml:47-48 and :104-105). Memory tuning: shared_buffers=2GB, effective_cache_size=6GB, work_mem=16MB, max_connections=200 — production defaults tuned for the HMS workload (mix of OLTP appointment writes, audit-log inserts, and AI-session reads).
• Initialization: First-boot scripts run from /docker-entrypoint-initdb.d/. init-databases.sh:31 declares the full DATABASES list (all 10, including ddx_fhir_core) and creates each in a loop (init-databases.sh:35), then enables extensions (:53); init-fhir-database.sh layers FHIR-specific setup on ddx_fhir_core. Both are idempotent (SELECT 'CREATE DATABASE …' WHERE NOT EXISTS).
• Extensions: uuid-ossp and pgcrypto are enabled in every application database — see the CREATE EXTENSION block at init-databases.sh:50-53. UUIDs are the canonical primary-key type across all five Prisma schemas; pgcrypto backs hash-based deduplication in the audit and AI tables.
• ORM split: Prisma 6 (multi-client) on the application side; Hibernate JPA on the FHIR side. The split is deliberate — running two ORMs on one database invites schema drift and migration conflicts. The split is enforced architecturally: ddx-api has no JDBC driver, and HAPI FHIR has no Prisma. The forbidden-pattern table in ddx-api/CLAUDE.md makes this explicit ("prisma:seed in ddx-api → use ddx-seeder-ts/").
• Alternatives rejected: A single database with separate Postgres schemas was rejected because it doesn't give us the backup/restore granularity or the data-isolation story we need for compliance audits. A managed cloud DB was rejected for the on-prem clinic deployment target — many German clinics require data residency on the local LAN.

Data Flow

Application reads/writes (host port 6432):

1. NestJS bootstraps in ddx-api/src/main.ts; the root module app.module.ts imports PrismaModule from ddx-api/src/platform/prisma/prisma.module.ts:1.
2. PrismaService extends PrismaClient and opens its connection in onModuleInit — see ddx-api/src/platform/prisma/prisma.service.ts:37-55. Connection is logged exactly once via the static connectedLogged flag (prisma.service.ts:23) to avoid duplicate "Database connected" lines when the service is re-provided in multiple module trees.
3. Each business service injects the right client by DI token: PrismaService (main), PrismaAiService, PrismaLoggingService, PrismaComService, PrismaAccountingService. Cross-schema joins are not allowed at the DB level — they happen in service code.
4. Disconnect is handled in onModuleDestroy at prisma.service.ts:58-61; ping() at :66-75 is what /health uses to surface liveness to ops.

FHIR clinical data (ddx_fhir_core in the same instance, :6432):

1. Browser → Next.js → NestJS (Trusted Gateway) → HAPI FHIR REST at http://localhost:6080/fhir (HAPI is a native JVM / ROOT.war, NOT a docker container — see context/ENVIRONMENT.md).
2. HAPI's Hibernate JPA writes/reads hfj_* tables in ddx_fhir_core (the FHIR database inside ddx-001-postgres-1, port 6432).
3. NestJS calls FHIR via the canonical FhirClientService injected through the FHIR_CLIENT DI token (see ddx-api/CLAUDE.md §FHIR Integration). No part of ddx-api ever opens a JDBC/Prisma connection to ddx_fhir_core. This is enforced by the lack of any Prisma schema pointing at it — the isolation is by connection string, not by container.

No SSE on this layer: PostgreSQL writes do not directly publish SSE events. Business services that publish SSE do so after the Prisma write succeeds, via RedisSsePublisher (see Redis topic). The DB transaction commits first; the event emits second.

Implicated Code

≥3 file:line citations required. Counts: 11 below.

• ddx-docker-postgres/docker-compose.yml:25 — single postgres service (container_name ${POSTGRES_CONTAINER:-ddx-hms-postgres-1}, live ddx-001-postgres-1).
• ddx-docker-postgres/docker-compose.yml:37 — host port mapping ${POSTGRES_PORT:-6432}:5432 (live 6432). There is NO second postgres-fhir service in the current compose — ddx_fhir_core is a database in this same instance.
• ddx-docker-postgres/docker-compose.yml:59 — pg_isready healthcheck (10s interval).
• ddx-docker-postgres/scripts/init-databases.sh:31 — DATABASES list: all 10 databases (ddx_api_main, ddx_api_log, ddx_api_ai, ddx_api_com, ddx_api_acc, ddx_api_saas, ddx_doxing, ddx_ocr, ddx_voice, ddx_fhir_core).
• ddx-docker-postgres/scripts/init-databases.sh:35 — create-each-database loop (idempotent).
• ddx-docker-postgres/scripts/init-databases.sh:53 — enables uuid-ossp and pgcrypto in every database.
• ddx-docker-postgres/scripts/init-fhir-database.sh — FHIR-specific setup on ddx_fhir_core (Hibernate JPA, never Prisma).
• ddx-api/src/platform/prisma/prisma.service.ts:16 — PrismaService extends PrismaClient from @ddx/prisma-main and implements OnModuleInit/OnModuleDestroy.
• ddx-api/src/platform/prisma/prisma.service.ts:37 — onModuleInit opens the connection and logs success exactly once (static connectedLogged flag at line 23).
• ddx-api/src/platform/prisma/prisma.service.ts:66 — ping() exposed as a liveness probe (used by /health).
• ddx-api/src/platform/prisma/prisma.module.ts:1 — PrismaModule declares the DI provider that the rest of the application consumes (4 other Prisma services follow the same pattern in their own platform/prisma-* modules).
• ddx-api/prisma-main/schema.prisma — main schema entry (135 models per ddx-api/CLAUDE.md); migrations are deployed via pnpm run prisma:migrate:deploy (NEVER raw npx prisma migrate).
• ddx-api/prisma-ai/schema.prisma, prisma-logging/schema.prisma, prisma-com/schema.prisma, prisma-accounting/schema.prisma — the four sibling Prisma schemas, each generating its own client.

Business → Technical match. Business outcome: a doctor saves an encounter note and the system records both the clinical FHIR resource and an immutable audit row that survives independent of any application-side incident. Technical mechanism: the encounter is written through FhirClientService (FHIR_CLIENT DI token) into ddx_fhir_core (Hibernate), and the audit row is written via PrismaLoggingService into ddx_api_log — two separate databases in the same :6432 instance, two separate ORMs, two per-database backup chains. safe-reset.sh snapshots and restores each database independently, so restoring the audit DB does not disturb the clinical record, and vice-versa.

Operational Notes

• Port collision: the container maps to non-standard host port 6432 (POSTGRES_PORT, 6xxx dev band) precisely so it doesn't collide with a developer's locally-installed Postgres. If a developer sees "port already allocated" on startup, the offender is almost always a host-side postgres.app instance — lsof -i :6432 will reveal it.
• NEVER run Prisma migrations against ddx_fhir_core. HAPI manages this schema with Hibernate; a Prisma migration will rewrite or drop HAPI tables. The pattern is called out in ddx-docker-postgres/CLAUDE.md, in ddx-api/CLAUDE.md (Forbidden Patterns table), and as memory pitfall [[project-fhir-prisma-dual-store-drift]] — resetting FHIR independently of Prisma leaves dangling fhir*Id pointers (382 dangling patients on 2026-06-18). Always co-reset via safe-reset.sh.
• Seeding lives in ddx-seeder-ts/, not in ddx-api/. The ddx-api/CLAUDE.md forbidden-patterns table makes this explicit; pnpm run prisma:seed inside ddx-api/ is the wrong path.
• Connection strings are managed via env vars (DATABASE_URL, AI_DATABASE_URL, LOGGING_DATABASE_URL, COM_DATABASE_URL, ACCOUNTING_DATABASE_URL) — five separate variables, one per Prisma client. Documented at init-databases.sh:67-77 and in ddx-api/CLAUDE.md §6 Databases.
• Validation commands:
  • docker compose --env-file ../.env.docker up -d (both containers)
  • docker exec ddx-001-postgres-1 pg_isready -U dudoxx_user
  • docker exec ddx-001-postgres-1 psql -U dudoxx_user -d ddx_fhir_core -c '\dt hfj_*' | head (confirm FHIR tables present)
  • pnpm run prisma:migrate:status:all from inside ddx-api/
• Backup / restore granularity: backups are taken per-database (per pnpm run prisma:backup), not as a single cluster dump. This keeps the audit DB restorable without touching the AI session DB or the accounting DB.
• Logging discipline: PrismaService constructor configures error and warn to stdout but suppresses query logging — see prisma.service.ts:27-32. Query logging at HMS volume floods logs; enable PRISMA_DEBUG=true locally if needed (block currently commented out at :46-51).

Related Topics

• MinIO — Object Storage Infrastructure — paired with PostgreSQL when an entity has both relational metadata (in ddx_api_main) and a binary payload (in the clinic-scoped MinIO bucket).
• Redis — Cache and SSE Transport — every business-state mutation that fans out to live UIs goes Prisma-write → Redis Streams → ddx-web SSE consumer.
• Qdrant — Vector Search Infrastructure — Qdrant supplements PostgreSQL with vector similarity search; embeddings reference Prisma row IDs.
• Canonical DB reference (not duplicated here): docs/claude-context/CLAUDE_DATABASE.md for the full per-schema model/enum tables.