Cookbook

This page is a hands-on guide. It shows what to do, what not to do, and how things work through real SQL taken from the migration, seed and test files.

1. Migrations: do’s and don’ts

What to do

• Add every schema change as a new file with a globally unique, zero-padded number.
• Keep table and routine migrations in separate files; routines go in migrations/<module>/procedures/.
• Make migrations idempotent where possible (DROP TRIGGER IF EXISTS, IF NOT EXISTS, ON CONFLICT DO NOTHING).
• Use ON DELETE RESTRICT on business foreign keys unless you have a good reason to cascade.
• Add COMMENT ON for tables and important columns.

What not to do

• Never edit a migration that has already been applied. Flyway validateOnMigrate=true will reject it.
• Do not mix DDL and routine logic in the same file.
• Do not use native PostgreSQL ENUM types; use CHECK constraints on VARCHAR so values can be extended by a new migration.
• Do not place domain tables in the public schema.

Example: a correct migration file

-- 002_core_parametre.sql
-- M0 — Fondation transverse : paramétrage global.

CREATE TABLE core.parametre (
    id_parametre BIGINT GENERATED ALWAYS AS IDENTITY,
    cle          VARCHAR(50)  NOT NULL,
    valeur       VARCHAR(255) NOT NULL,
    description  TEXT,
    CONSTRAINT pk_parametre PRIMARY KEY (id_parametre),
    CONSTRAINT uq_parametre_cle UNIQUE (cle)
);

COMMENT ON TABLE  core.parametre IS 'Paramètre de configuration global.';
COMMENT ON COLUMN core.parametre.cle    IS 'Clé unique (ex : devise, taux_tva).';
COMMENT ON COLUMN core.parametre.valeur IS 'Valeur courante.';

2. How to add a new table

Let’s say we need to track maintenance requests for a room.

Step-by-step

1. Choose the correct schema — residence is the right module.
2. Pick the next globally unique number — if the latest is 073, use 074.
3. Name the file migrations/residence/074_residence_demande_maintenance.sql.
4. Write the CREATE TABLE with a primary key, foreign keys, constraints, indexes and comments.

-- 074_residence_demande_maintenance.sql

CREATE TABLE residence.demande_maintenance (
    id_demande     BIGINT GENERATED ALWAYS AS IDENTITY,
    id_logement    BIGINT       NOT NULL,
    description    TEXT         NOT NULL,
    statut         VARCHAR(20)  NOT NULL DEFAULT 'OUVERT',
    date_creation  TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    id_utilisateur BIGINT,
    CONSTRAINT pk_demande_maintenance PRIMARY KEY (id_demande),
    CONSTRAINT fk_demande_maintenance_logement
        FOREIGN KEY (id_logement) REFERENCES residence.logement (id_logement)
        ON DELETE RESTRICT,
    CONSTRAINT fk_demande_maintenance_utilisateur
        FOREIGN KEY (id_utilisateur) REFERENCES admin.utilisateur (id_utilisateur)
        ON DELETE RESTRICT,
    CONSTRAINT chk_demande_maintenance_statut
        CHECK (statut IN ('OUVERT', 'EN_COURS', 'RESOLU', 'ANNULE'))
);

CREATE INDEX idx_demande_maintenance_logement ON residence.demande_maintenance (id_logement);
CREATE INDEX idx_demande_maintenance_statut   ON residence.demande_maintenance (statut);

COMMENT ON TABLE residence.demande_maintenance IS 'Demandes de maintenance par logement.';

What not to do

-- BAD: no schema, no checks, no comments, wrong order number reused
CREATE TABLE demande_maintenance (
    id int,
    id_logement int
);

3. How to add a function or trigger

Create the function and the trigger in the module’s procedures/ folder so it is never mixed with table DDL.

Example: a trigger that maintains a counter

-- migrations/residence/procedures/075_residence_maintenance_stats.sql

CREATE FUNCTION residence.compter_demandes_ouvertes()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE residence.logement
    SET equipements = jsonb_set(
        COALESCE(equipements, '{}')::jsonb,
        '{demandes_ouvertes}',
        to_jsonb((SELECT count(*) FROM residence.demande_maintenance
                  WHERE id_logement = NEW.id_logement AND statut = 'OUVERT'))
    )
    WHERE id_logement = NEW.id_logement;

    RETURN NEW;
END;
$$;

CREATE TRIGGER trg_demande_maintenance_compter
AFTER INSERT OR UPDATE OF statut ON residence.demande_maintenance
FOR EACH ROW EXECUTE FUNCTION residence.compter_demandes_ouvertes();

What not to do

• Do not create the function in the same file as the table DDL.
• Do not use SELECT * inside a trigger; name the columns explicitly.
• Do not forget RETURN NEW (or RETURN OLD for DELETE triggers).

4. How to write a pgTAP test

Tests are self-contained SQL files. Each opens a transaction, enables pgtap, defines plan(n) assertions, and rolls back.

Minimal example

-- tests/999_demande_maintenance.sql

BEGIN;
CREATE EXTENSION pgtap;

SELECT plan(3);

SELECT has_table('residence', 'demande_maintenance',
                 'table demande_maintenance exists');

SELECT is_empty($$
    SELECT * FROM residence.demande_maintenance
    WHERE statut NOT IN ('OUVERT', 'EN_COURS', 'RESOLU', 'ANNULE')
$$, 'all statuses are allowed values');

SELECT lives_ok($$
    INSERT INTO residence.demande_maintenance (id_logement, description)
    VALUES (1, 'Fuite sous l''évier')
$$, 'inserting a maintenance request works');

SELECT * FROM finish(true);
ROLLBACK;

What not to do

• Do not leave COMMIT in a test file; tests must end with ROLLBACK.
• Do not rely on seeds/dev data; create your own fixtures inside the transaction.
• Do not forget SELECT * FROM finish(true).

5. How seeds work

Reference seeds are idempotent so they can be replayed safely.

Good: ON CONFLICT DO NOTHING

-- seeds/reference/10_core_finances.sql

INSERT INTO core.parametre (cle, valeur, description) VALUES
    ('devise',              'FCFA (XOF)', 'Devise d''affichage et de facturation.'),
    ('taux_tva',            '18',         'Taux de TVA par défaut.'),
    ('seuil_stock_alerte',  '10',         'Seuil de stock déclenchant une alerte.')
ON CONFLICT (cle) DO NOTHING;

Good: WHERE NOT EXISTS for values that have no unique key yet

INSERT INTO finances.moyen_paiement (libelle, actif)
SELECT m.libelle, m.actif
FROM (VALUES
    ('Espèces',           TRUE),
    ('Chèque',            TRUE),
    ('Virement bancaire', TRUE)
) AS m(libelle, actif)
WHERE NOT EXISTS (
    SELECT 1 FROM finances.moyen_paiement p WHERE p.libelle = m.libelle
);

What not to do

-- BAD: not idempotent, will fail on second run
INSERT INTO finances.moyen_paiement (libelle, actif) VALUES ('Espèces', TRUE);

6. Security and audit recipes

Set the audit user in a session

The audit trigger reads sim.id_utilisateur. Your application must set it before each transaction.

SELECT set_config('sim.id_utilisateur', '42', true);

UPDATE admin.utilisateur
SET dernier_connexion = now()
WHERE id_utilisateur = 42;

-- audit.journal_audit now contains a trace with id_utilisateur = 42

Check whether a user has a permission

SELECT count(*) > 0 AS has_permission
FROM admin.utilisateur u
JOIN admin.role r ON r.id_role = u.id_role
JOIN admin.role_permission rp ON rp.id_role = r.id_role
JOIN admin.permission p ON p.id_permission = rp.id_permission
WHERE u.login = 'toto'
  AND p.code = 'RESIDENCE.MODIFIER';

What not to do

• Do not store refresh or password-reset tokens in plain text; the project stores only SHA-256 hashes.
• Do not set sim.id_utilisateur in a seed file unless you explicitly want to simulate a user.

7. Common query recipes

Find a client and active contracts

SELECT
    cl.code,
    cl.nom || ' ' || cl.prenoms AS client,
    c.numero_contrat,
    l.numero                    AS logement,
    c.montant_loyer,
    c.statut
FROM client.client cl
LEFT JOIN residence.contrat_location c
       ON c.id_client = cl.id_client AND c.statut = 'ACTIF'
LEFT JOIN residence.logement l
       ON l.id_logement = c.id_logement
WHERE cl.tel_principal = '0123456789'
   OR cl.code = 'GSG-CL-001';

Rent tracking for a contract

SELECT
    e.annee,
    e.mois,
    e.montant,
    e.statut,
    p.montant AS montant_paye,
    p.date    AS date_paiement
FROM residence.echeance_loyer e
LEFT JOIN finances.paiement p ON p.id_paiement = e.id_paiement
WHERE e.id_contrat = 1
ORDER BY e.annee, e.mois;

Overdue rents with building and days late

SELECT
    cl.nom || ' ' || cl.prenoms AS client,
    c.numero_contrat,
    b.code                      AS batiment,
    e.montant,
    CURRENT_DATE - e.date_echeance AS jours_retard
FROM residence.echeance_loyer e
JOIN residence.contrat_location c ON c.id_contrat = e.id_contrat
JOIN client.client cl             ON cl.id_client = c.id_client
JOIN residence.logement l         ON l.id_logement = c.id_logement
LEFT JOIN residence.batiment b    ON b.id_batiment = l.id_batiment
WHERE e.statut IN ('IMPAYE', 'PARTIEL')
ORDER BY e.date_echeance;

Stock history for a product

SELECT
    m.date,
    m.type,
    m.quantite,
    SUM(m.quantite * CASE m.type
        WHEN 'ENTREE' THEN 1
        WHEN 'SORTIE' THEN -1
        WHEN 'AJUSTEMENT' THEN 1
        ELSE 0
    END) OVER (ORDER BY m.date, m.id_mouvement) AS solde
FROM market.mouvement_stock m
WHERE m.id_produit = 1
ORDER BY m.date, m.id_mouvement;

Or use the built-in view:

SELECT * FROM market.stock_historique WHERE id_produit = 1;

Invoice payment status

SELECT
    f.numero,
    f.montant_total,
    f.montant_paye,
    f.reste,
    f.statut
FROM facturation.facture f
WHERE f.id_client = 1
ORDER BY f.date DESC;

Applying a payment programmatically:

-- 1. Create the payment
INSERT INTO finances.paiement (
    date, montant, id_moyen, id_activite, type, motif, id_utilisateur, reference
)
VALUES (
    now(), 60000, 1, 1, 'ENCAISSEMENT', 'Paiement facture', 1, 'PAY-123'
)
RETURNING id_paiement;

-- 2. Apply it to the invoice
INSERT INTO finances.application_paiement (id_paiement, id_facture, montant_applique)
VALUES (1, 42, 60000);

-- The trigger trg_application_paiement_facture updates facture.montant_paye, reste and statut

8. How the audit trigger works

The generic audit trigger is installed on every business table. It reads sim.id_utilisateur from the session, then writes one audit.journal_audit row per INSERT, UPDATE or DELETE.

-- The trigger calls this function with the PK column name as argument
CREATE TRIGGER trg_audit_parametre
AFTER INSERT OR UPDATE OR DELETE ON core.parametre
FOR EACH ROW EXECUTE FUNCTION audit.enregistrer_trace('id_parametre');

When an UPDATE happens, the trigger stores both the old (avant) and new (apres) values as text, and it automatically extracts a monetary amount from common columns (montant, total, prix, etc.). Passwords are redacted by audit.masquer_donnees.

9. How rent generation and revision work

Generate monthly dues for a contract

SELECT residence.generer_echeances(1);

This creates one residence.echeance_loyer row for each month from date_debut to the end of the contract or current month + 1. Past months are marked PAYE (legacy assumption for seeded data); current and future months are A_VENIR.

Revise rent

SELECT residence.reviser_loyer(
    p_id_contrat      := 1,
    p_nouveau_montant := 75000,
    p_date_effet      := '2026-10-01',
    p_motif           := 'Révision annuelle',
    p_id_utilisateur  := 2
);

This:

1. Inserts a row in residence.revision_loyer.
2. Updates contrat_location.montant_loyer.
3. Re-prices all A_VENIR dues from date_effet onward.

What not to do

• Do not update contrat_location.montant_loyer directly in production; use residence.reviser_loyer so the trace is recorded.
• Do not try to insert echeance_loyer rows manually; let the trigger or generer_echeances handle it.

10. Common mistakes and how to avoid them

Mistake	Why it fails	What to do instead
Editing an applied migration	Flyway validateOnMigrate=true throws an error	Add a new migration
Using public schema for a new table	Breaks module isolation	Use the correct module schema
Creating an ENUM type	Cannot be extended without ALTER TYPE	Use CHECK on VARCHAR
ON DELETE CASCADE on business FKs	Risks accidental data loss	Use ON DELETE RESTRICT
Storing images in BYTEA	Bloats the database and hurts backup times	Store object-storage keys as TEXT or VARCHAR(500)
Writing SELECT * in a routine	Brittle against schema changes	List columns explicitly
Leaving COMMIT in a test file	Leaves data behind after tests	End with ROLLBACK
Running make seed-dev in production	Loads demo accounts and demo password	Use make prod-seed only
Forgetting RETURN NEW in a trigger	Causes the row to disappear	Always return the correct row
Not setting sim.id_utilisateur	Audit traces are attributed to system	Set the session variable in the application

11. Full local rebuild recipe

When the schema has drifted or you want a clean state:

cd sim-database
make clean        # stop and remove the volume (DESTRUCTIVE)
make up
make migrate
make seed
make test

If you only want to re-run migrations without losing data:

make migrate

If a migration fails, check Flyway’s output, fix the offending new migration file, and run make migrate again. Never change an already-applied file.