Database Architecture

PostgreSQL on Fly.io with PgBouncer connection pooling. Drizzle ORM for type-safe queries. Atlas for declarative schema migrations.

Database Topology

  Backend App
      │
      ├── DATABASE_URL ────────────► PgBouncer ──► PostgreSQL (app-user, writer role)
      │   (runtime queries)
      │
      └── DATABASE_URL_MIGRATION ──► PostgreSQL directly (migration-user, schema_admin)
          (schema changes only)

Three managed clusters (Fly MPG) in the Frankfurt region: development, staging, production. The env → cluster-id map lives in renewa-one/scripts/lib/mpg-clusters.sh (single source of truth; cluster IDs change after a Path B restore — see Deployment Rollback).

Connection Users

User	Role	Connection	Used By
app-user	writer	PgBouncer (DATABASE_URL)	Runtime application queries
migration-user	schema_admin	Direct (DATABASE_URL_MIGRATION)	Atlas migrations via release_command

Schema

• Source of truth: backend/src/db/schema.ts (9,702 lines as of 2026-06)
• Tables: 187 (via pgTable())
• Enums: 102 (via pgEnum())
• ORM: Drizzle with .$type<LocalizedText>() for bilingual JSON fields

All entity types are exported from Shared Layer via shared/types.ts.

Key Entity Tables

Domain	Tables	See
CRM	contacts, companies, hubspot_leads	Leads, Contacts, Companies
Projects	projects, buildings, scenarios	Projects, Buildings, Scenarios
Components	building_components, renovation_measures, scenario_measures	Building Components, Measures
Finance	quotes, invoices, products, billing_*	Quotes, Invoices, Products
Funding	funding_programs, funding_applications	Funding Programs, Funding Applications
Documents	document_requests, files, file_collections	Documents, Document Obtaining, Files
Auth	users, departments, sessions	Users, Departments, RBAC Authorization
Automation	workflow_packages, workflow_templates, forms, form_submissions	Workflows, Forms
Scheduling	appointment_proposals	Appointments
Audit	audit_logs	Audit Logs
Templates	pdf_templates	PDF Templates
HubSpot sync	hubspot_associations, hubspot_config, sync mapping tables	HubSpot Integration

Several tables mirror HubSpot objects (Deal = R1 projects — there is no deals table). Mirror tables carry a hubspotId for round-tripping, but internal FKs always join on UUID PKs. HubSpot-tracked relationships (customer, contractor, owner, …) live in hubspot_associations, not FK columns (spec 2026-05-08). Every human FK points at contacts.id, never users.id (spec 2026-04-24).

Migration System

Atlas declarative migrations. See Database Migrations for workflow.

Artifact	Path	Editable
Schema definition	backend/src/db/schema.ts	Yes (source of truth)
Generated SQL	backend/atlas/migrations/*.sql	Never
Integrity checksum	backend/atlas/migrations/atlas.sum	Never

Workflow: Edit schema.ts → make db-generate NAME=description → verify SQL → commit migration + atlas.sum. 48 migration files as of 2026-06.

Atlas uses an ephemeral atlas_dev database on the Docker Compose PostgreSQL instance to compute schema diffs.

N-1 compatibility (expand → migrate → contract): every migration must keep the previous app image working while release_command runs. Renames, drops, SET NOT NULL, and type changes are gated in CI by scripts/check-migration-n1.sh (PR#1938). Table renames ship an auto-updatable compat view declared via -- renewa:n-1-shim: <old> drop-with #<issue>; the contract migration drops it with -- renewa:n-1-shim-drop. Environment gating of destructive data migrations uses the in-file -- renewa:atlas:skip-on staging,production directive (PR#1797).

Mock vs Config Data

“Seeding” is retired — data is split into two categories (decision rule: would the app fail in production without this row?):

Category	Mechanism	Environments
Config (app fails without it)	DB migration (make db-generate NAME=config_<thing>)	All, via release_command
Mock/demo	backend/src/db/mocks/ (deterministic UUIDs via deterministicUuid(UUID_NAMESPACES.X, key), marker check → skip → insert)	local/pr-preview/development only, loaded at startup when AUTO_MOCK=true

A third category — departments and internal employees — is Entra-owned: populated by the scheduled Entra sync job, never by mocks or migrations (spec 2026-06-09). Make targets: make db-mock / db-mock-large. See Database Seeding for the legacy page history.

Data Patterns

• LocalizedText: All user-facing text stored as jsonb with {de: string, en: string}. See Validation Pattern.
• Soft deletes: Many tables use deletedAt timestamp column.
• Audit trails: createdAt, updatedAt, createdBy, updatedBy on most tables. See Audit Logs.
• UUIDs: All primary keys are UUIDs (uuid type with gen_random_uuid() default).
• Money: decimal(p, s) columns leave Drizzle as strings; parse with fromString() from @shared/money (Big.js) and never coerce via Number(). See Shared Layer (spec 2026-04-24).
• Human FKs: attribution/assignment columns reference contacts.id, never users.id.

Related Pages

• Architecture Overview — System-level architecture
• Database Migrations — Atlas workflow and commands
• Mock and Config Data — Mock vs config split (Database Seeding is the archived predecessor)
• Persistence Topology — Which layers may import the DB
• Backend Architecture — Drizzle ORM usage in services
• Shared Layer — Entity types exported from shared/types.ts
• Validation Pattern — Zod schemas and LocalizedText
• Makefile Commands — make db-generate, make db-migrate, make db-studio