Architecture and Conventions

The SIM Database is a single PostgreSQL 18.4 instance (Alpine) whose schema is versioned by Flyway and tested with pgTAP. This page describes the architecture, the schema layout and the conventions that keep the project maintainable.

High-level architecture

┌──────────────────────────────────────────────────────────────┐
│                         Host                                  │
│   Makefile  ──►  docker compose  ──►  sim-postgres:local      │
│                         │                                     │
│   Flyway one-shot       │                                     │
│   container ────────────┤                                     │
│                         ▼                                     │
│                PostgreSQL 18.4 + pgTAP                        │
│   migrations/  seeds/  tests/  scripts/  (mounted)            │
└──────────────────────────────────────────────────────────────┘

Main components

Component	Technology	Responsibility
Database engine	PostgreSQL 18.4 Alpine	Stores all data, executes pgTAP tests inside the container
Migrations	Flyway 13.2 (one-shot container)	Schema evolution and validation
Tests	pgTAP 1.3.4	SQL-level regression tests
Local orchestration	Docker Compose	sim-postgres:local dev container
Production orchestration	Docker Compose	sim-postgres:prod dedicated container + optional pgAdmin
Command wrapper	GNU Make	Cross-platform targets for Windows cmd/Git Bash/POSIX
Seed tooling	Node + bcryptjs	Generate/verify committed bcrypt password hashes

Schema map

The database is created in migrations/common/001_bootstrap_schemas.sql. public is intentionally reserved; every business table lives in its own module schema.

PostgreSQL schema	Module	Created by	Role
core	M0 — Transverse	001_bootstrap_schemas.sql	Global parameters
client	M0 — Client	001_bootstrap_schemas.sql	Single client repository
residence	M2 — Résidence	001_bootstrap_schemas.sql	Housing and rentals
market	M3 — Market	001_bootstrap_schemas.sql	Shop
pressing	M4 — Pressing	001_bootstrap_schemas.sql	Laundry
restaurant	M5 — Restaurant	001_bootstrap_schemas.sql	Food service
salle_fete	M6 — Salle de fête	001_bootstrap_schemas.sql	Party room
facturation	M7 — Facturation	001_bootstrap_schemas.sql	Invoicing
finances	M8 — Finances	001_bootstrap_schemas.sql	Payments and consolidation
rh	M9 — RH	001_bootstrap_schemas.sql	Human resources
admin	M11 — Admin	001_bootstrap_schemas.sql	Users, roles, permissions
audit	R15 — Audit	001_bootstrap_schemas.sql	Append-only journal
abonnement	Subscription catalog	046_common_abonnement_schema.sql	Subscription categories

Migration conventions

File naming

Every migration is a single file named with a globally unique, zero-padded number:

<number>_<module>_<description>.sql

Examples:

migrations/common/001_bootstrap_schemas.sql
migrations/residence/021_residence_batiments.sql
migrations/finances/procedures/025_finances_routines.sql

Rules:

• Numbers are global across the whole project, not per folder.
• Tables and routines/views are not mixed in the same file; routines go in a procedures/ subfolder.
• Applied migrations are immutable. Flyway validateOnMigrate=true rejects any change to an already-applied migration.
• New changes are added as new migration files (back-fills use FLYWAY_OUT_OF_ORDER=true).

Object naming

Object	Naming pattern	Example
Tables	lowercase_snake_case (French domain)	contrat_location, mouvement_stock
Columns	lowercase_snake_case	id_client, montant_loyer
Primary keys	pk_<table>	pk_facture
Foreign keys	fk_<table>_<column>	fk_facture_client
Unique constraints	uq_<table>_<column>	uq_role_code
Check constraints	chk_<table>_<domain>	chk_logement_type
Indexes	idx_<table>_<column>	idx_client_nom
Unique indexes	ux_<table>_<column>	ux_utilisateur_email
Functions	schema-qualified, verb-first	residence.generer_echeances
Triggers	trg_<table>_<event>	trg_mouvement_stock_solde

Data model rules

1. No public schema usage — public is kept empty for infrastructure compatibility.
2. Single client repository — client.client is the only client table; all modules reference it.
3. Prefer ON DELETE RESTRICT — cascading deletes are avoided on reference and business data.
4. Status and type enumerations — implemented as CHECK constraints on VARCHAR columns rather than native PostgreSQL enums, to allow easy extension via new migrations.
5. Audit by default — every table in the 11 business schemas gets an AFTER INSERT OR UPDATE OR DELETE trigger that logs to audit.journal_audit.

Migrations vs seeds

Area	Purpose	Content
migrations/	DDL and structural DML	Create/alter tables, functions, triggers, constraints
seeds/reference/	Required reference data	Activities, payment methods, roles, permissions, categories
seeds/dev/	Disposable development fixtures	Test clients, contracts, products, employees
seeds/generated/	Generated artifacts	Bcrypt password hashes (10_auth_passwords.sql)

Migration flow

Flyway applies migrations in numeric order. FLYWAY_OUT_OF_ORDER=true lets back-fill migrations (e.g. 032 inserted after 033 has already been deployed) run on older databases. Because migrations are purely additive, this is safe.

On a fresh database:

001 → 002 → 003 → ... → 073

On a database already at 073, a new 032_* back-fill is a no-op for objects that already exist and adds only the missing change.