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 on Fly.io (Frankfurt region): development, staging, production.

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 (7,634 lines)
• Tables: 140 (via pgTable())
• Enums: 85 (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	leads, contacts, companies	Leads, Contacts, Companies
Projects	projects, buildings, scenarios	Projects, Buildings, Scenarios
Components	building_components, measures	Building Components, Measures
Finance	quotes, invoices, products	Quotes, Invoices, Products
Funding	funding_programs, funding_applications	Funding Programs, Funding Applications
Documents	documents, document_requests, files	Documents, Document Obtaining, Files
Auth	users, departments, user_roles	Users, Departments, RBAC Authorization
Automation	workflows, forms, form_submissions	Workflows, Forms
Scheduling	appointments	Appointments
Audit	audit_logs	Audit Logs
Templates	pdf_templates	PDF Templates

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.

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

Seeding

Deterministic seeds using UUID v5 namespaces. See Database Seeding.

import { seedUuid, SEED_NAMESPACES } from './seed-utils';
seedUuid(SEED_NAMESPACES.USER, 'admin@renewa.de');

Pattern: check marker → skip if exists → insert with seedUuid(). Idempotent, incremental. Auto-runs when AUTO_SEED=true.

Seed files: backend/src/db/seeds/, imported via seeds/index.ts.

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).

Related Pages

• Architecture Overview — System-level architecture
• Database Migrations — Atlas workflow and commands
• Database Seeding — Seed data management
• 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