Skip to content
My Docs

Database Overview

The SIM Database is the single consolidated PostgreSQL store for GLOBAL SIM GROUP. It backs every business line — Résidence, Market, Pressing, Restaurant, Salle de fête, Facturation, Finances and RH — through a single logical database split into PostgreSQL schemas.

What this documentation covers

Section titled “What this documentation covers”
  • The PostgreSQL architecture and schema map
  • The operational tooling (Docker, Flyway, pgTAP, Make, GitHub Actions)
  • Security, roles, permissions and audit
  • The data model per business domain
  • The pgTAP test suite and the business rules it enforces

Quick start (local)

Section titled “Quick start (local)”
Terminal window
cd sim-database
make setup      # create .env from .env.example
make up         # start PostgreSQL
make migrate    # apply Flyway migrations
make seed       # load reference + dev seeds
make test       # run pgTAP tests
make db         # open psql

Repository layout

Section titled “Repository layout”
.
├── Makefile                    # cross-platform command wrapper
├── docker-compose.yml          # local PostgreSQL + pgTAP
├── docker-compose.prod.yml     # dedicated production PostgreSQL
├── config/flyway.conf          # Flyway configuration
├── migrations/                 # schema evolution, one folder per PostgreSQL schema
├── seeds/                      # reference, dev and generated seed data
├── tests/                      # pgTAP SQL regression suite
├── scripts/                    # backup/restore, bootstrap, password hashing
└── .github/workflows/          # CI/CD pipelines

Core principles

Section titled “Core principles”
Principle How it is implemented
Schema per module Each SIM module owns a PostgreSQL schema (e.g. residence, market, finances).
Migrations are immutable Flyway validateOnMigrate=true; never edit an applied migration. Add a new one.
Single client repository client.client is referenced by every module (R1).
Every operation is audited Generic DML triggers write one row per operation to audit.journal_audit.
No dev data in production prod-seed loads only seeds/reference/ plus a real admin account.

Business modules and schemas

Section titled “Business modules and schemas”
Module PostgreSQL schema Purpose
Core core Transverse parameters
Client client Single client repository, identity documents, emergency contacts
Résidence residence Buildings, rooms, contracts, rent, deposits, short stays
Market market Products, categories, suppliers, stock movements, sales
Pressing pressing Garment deposit → collection lifecycle
Restaurant restaurant Dishes, categories, orders
Salle de fête salle_fete Event-room reservations
Facturation facturation Services, invoices, lines
Finances finances Payments, expenses, cash drawers, consolidation
RH rh Employees, attendance, payroll
Administration admin Users, roles, permissions, tokens, backups
Audit audit Append-only journal
Abonnement abonnement Subscription categories

Next steps

Section titled “Next steps”