Recettes

Cette page est un guide pratique. Elle montre quoi faire, quoi ne pas faire et comment les choses fonctionnent à travers du SQL réel tiré des fichiers de migration, de seed et de test.

1. Migrations : bonnes pratiques et pièges

À faire

• Ajouter chaque changement de schéma dans un nouveau fichier avec un numéro globalement unique, complété de zéros.
• Garder les migrations de tables et de routines dans des fichiers séparés ; les routines vont dans migrations/<module>/procedures/.
• Rendre les migrations idempotentes quand c’est possible (DROP TRIGGER IF EXISTS, IF NOT EXISTS, ON CONFLICT DO NOTHING).
• Utiliser ON DELETE RESTRICT sur les clés étrangères métier sauf bonne raison de cascader.
• Ajouter des COMMENT ON pour les tables et colonnes importantes.

À ne pas faire

• Ne jamais modifier une migration déjà appliquée. Flyway validateOnMigrate=true la rejettera.
• Ne pas mélanger DDL et logique de routines dans le même fichier.
• Ne pas utiliser les types ENUM natifs de PostgreSQL ; utiliser des contraintes CHECK sur VARCHAR pour que les valeurs soient extensibles par une nouvelle migration.
• Ne pas placer de tables métier dans le schéma public.

Exemple : un fichier de migration correct

-- 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. Comment ajouter une nouvelle table

Disons qu’il faut suivre les demandes de maintenance d’un logement.

Étape par étape

1. Choisir le bon schéma — residence est le bon module.
2. Prendre le prochain numéro globalement unique — si le dernier est 073, utiliser 074.
3. Nommer le fichier migrations/residence/074_residence_demande_maintenance.sql.
4. Écrire le CREATE TABLE avec clé primaire, clés étrangères, contraintes, index et commentaires.

-- 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.';

À ne pas faire

-- MAUVAIS : pas de schéma, pas de checks, pas de commentaires, numéro d'ordre réutilisé
CREATE TABLE demande_maintenance (
    id int,
    id_logement int
);

3. Comment ajouter une fonction ou un trigger

Créer la fonction et le trigger dans le dossier procedures/ du module pour ne jamais les mélanger avec le DDL de tables.

Exemple : un trigger qui maintient un compteur

-- 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();

À ne pas faire

• Ne pas créer la fonction dans le même fichier que le DDL de la table.
• Ne pas utiliser SELECT * dans un trigger ; nommer les colonnes explicitement.
• Ne pas oublier RETURN NEW (ou RETURN OLD pour les triggers DELETE).

4. Comment écrire un test pgTAP

Les tests sont des fichiers SQL autonomes. Chacun ouvre une transaction, active pgtap, définit plan(n) assertions et fait un rollback.

Exemple minimal

-- 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;

À ne pas faire

• Ne pas laisser de COMMIT dans un fichier de test ; les tests doivent finir par ROLLBACK.
• Ne pas dépendre des données de seeds/dev ; créer ses propres fixtures dans la transaction.
• Ne pas oublier SELECT * FROM finish(true).

5. Comment fonctionnent les seeds

Les seeds de référence sont idempotents pour pouvoir être rejoués sans risque.

Bien : 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;

Bien : WHERE NOT EXISTS pour les valeurs sans clé unique

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

À ne pas faire

-- MAUVAIS : pas idempotent, échouera au second passage
INSERT INTO finances.moyen_paiement (libelle, actif) VALUES ('Espèces', TRUE);

6. Recettes de sécurité et d’audit

Définir l’utilisateur d’audit dans une session

Le trigger d’audit lit sim.id_utilisateur. Votre application doit le définir avant chaque transaction.

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

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

-- audit.journal_audit contient maintenant une trace avec id_utilisateur = 42

Vérifier si un utilisateur a une 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';

À ne pas faire

• Ne pas stocker les jetons de rafraîchissement ou de réinitialisation en clair ; le projet ne stocke que des hachages SHA-256.
• Ne pas définir sim.id_utilisateur dans un fichier de seed sauf si vous voulez explicitement simuler un utilisateur.

7. Recettes de requêtes courantes

Trouver un client et ses contrats actifs

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';

Suivi des loyers d’un contrat

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;

Loyers en retard avec bâtiment et jours de retard

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;

Historique de stock d’un produit

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;

Ou utiliser la vue intégrée :

SELECT * FROM market.stock_historique WHERE id_produit = 1;

Statut de paiement des factures

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;

Appliquer un paiement programmatiquement :

-- 1. Créer le paiement
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. L'appliquer à la facture
INSERT INTO finances.application_paiement (id_paiement, id_facture, montant_applique)
VALUES (1, 42, 60000);

-- Le trigger trg_application_paiement_facture met à jour facture.montant_paye, reste et statut

8. Comment fonctionne le trigger d’audit

Le trigger d’audit générique est installé sur chaque table métier. Il lit sim.id_utilisateur depuis la session, puis écrit une ligne audit.journal_audit par INSERT, UPDATE ou DELETE.

-- Le trigger appelle cette fonction avec le nom de la colonne PK en 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');

Lors d’un UPDATE, le trigger stocke les anciennes (avant) et nouvelles (apres) valeurs en texte, et extrait automatiquement un montant monétaire des colonnes courantes (montant, total, prix, etc.). Les mots de passe sont rédigés par audit.masquer_donnees.

9. Comment fonctionnent la génération et la révision des loyers

Générer les échéances mensuelles d’un contrat

SELECT residence.generer_echeances(1);

Cela crée une ligne residence.echeance_loyer pour chaque mois de date_debut à la fin du contrat ou au mois courant + 1. Les mois passés sont marqués PAYE (hypothèse héritée pour les données seedées) ; les mois courants et futurs sont A_VENIR.

Réviser un loyer

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

Cela :

1. Insère une ligne dans residence.revision_loyer.
2. Met à jour contrat_location.montant_loyer.
3. Re-tarifie toutes les échéances A_VENIR à partir de date_effet.

À ne pas faire

• Ne pas mettre à jour contrat_location.montant_loyer directement en production ; utiliser residence.reviser_loyer pour que la trace soit enregistrée.
• Ne pas essayer d’insérer des lignes echeance_loyer manuellement ; laisser le trigger ou generer_echeances s’en charger.

10. Erreurs courantes et comment les éviter

Erreur	Pourquoi ça échoue	Quoi faire à la place
Modifier une migration appliquée	Flyway validateOnMigrate=true lève une erreur	Ajouter une nouvelle migration
Utiliser le schéma public pour une nouvelle table	Casse l’isolation des modules	Utiliser le bon schéma de module
Créer un type ENUM	Non extensible sans ALTER TYPE	Utiliser CHECK sur VARCHAR
ON DELETE CASCADE sur les FK métier	Risque de perte de données accidentelle	Utiliser ON DELETE RESTRICT
Stocker des images en BYTEA	Gonfle la base et pénalise les sauvegardes	Stocker les clés de stockage objet en TEXT ou VARCHAR(500)
Écrire SELECT * dans une routine	Fragile face aux changements de schéma	Lister les colonnes explicitement
Laisser un COMMIT dans un test	Laisse des données après les tests	Finir par ROLLBACK
Lancer make seed-dev en production	Charge des comptes et mots de passe de démo	Utiliser make prod-seed uniquement
Oublier RETURN NEW dans un trigger	Fait disparaître la ligne	Toujours retourner la bonne ligne
Ne pas définir sim.id_utilisateur	Les traces d’audit sont attribuées à system	Définir la variable de session dans l’application

11. Recette de reconstruction locale complète

Quand le schéma a dérivé ou que vous voulez un état propre :

cd sim-database
make clean        # arrête et supprime le volume (DESTRUCTIF)
make up
make migrate
make seed
make test

Pour ne relancer que les migrations sans perdre de données :

make migrate

Si une migration échoue, vérifiez la sortie de Flyway, corrigez le nouveau fichier de migration fautif et relancez make migrate. Ne modifiez jamais un fichier déjà appliqué.