Data Model

This page describes the data model by PostgreSQL schema. Columns are given in snake_case with type and constraint notes; PK = primary key, FK = foreign key, UQ = unique, chk = check constraint.

core

core.parametre

Column	Type	Notes
id_parametre	BIGINT identity	PK
cle	VARCHAR(50) NOT NULL	UQ uq_parametre_cle
valeur	VARCHAR(255) NOT NULL	Current value
description	TEXT	Optional

Examples: devise, taux_tva, seuil_stock_alerte.

client

Single customer repository. Every module references client.client.

client.client

Column	Type	Notes
id_client	BIGINT identity	PK
nom	VARCHAR(100) NOT NULL	Last name
prenoms	VARCHAR(150) NOT NULL	First names
date_naissance	DATE	Birth date
lieu_naissance	VARCHAR(100)	Birth place
sexe	VARCHAR(1)	Gender
nationalite	VARCHAR(100)	Nationality
profession	VARCHAR(150)	Occupation
photo	TEXT	Object-storage key
tel_principal	VARCHAR(30) NOT NULL	Main phone
tel_secondaire	VARCHAR(30)	Secondary phone
email	VARCHAR(150)	Email
adresse	VARCHAR(255)	Address
ville	VARCHAR(100)	City
pays	VARCHAR(100)	Country
date_enregistrement	TIMESTAMP NOT NULL	Registration date
type_client	VARCHAR(20) NOT NULL	chk LOCATAIRE / PASSAGE / AUTRE
code	VARCHAR(20) NOT NULL	UQ, auto-generated GSG-CL-NNN

client.piece_identite

Column	Type	Notes
id_piece	BIGINT identity	PK
id_client	BIGINT NOT NULL	FK → client.client
type_piece	VARCHAR(20) NOT NULL	chk CNI / PASSEPORT / CARTE_SEJOUR / AUTRE
numero	VARCHAR(50) NOT NULL	Document number
date_delivrance	DATE	Issue date
date_expiration	DATE	Expiry date
autorite_delivrance	VARCHAR(100)	Issuing authority
copie_num	TEXT	Front scan object key
copie_num_verso	TEXT	Back scan object key

UQ (type_piece, numero).

client.contact_urgence

Column	Type	Notes
id_contact	BIGINT identity	PK
id_client	BIGINT NOT NULL	FK → client.client
nom	VARCHAR(100) NOT NULL	Name
prenom	VARCHAR(100)	First name
lien	VARCHAR(100) NOT NULL	Relationship
tel_principal	VARCHAR(30) NOT NULL	Main phone
tel_secondaire	VARCHAR(30)
adresse	VARCHAR(255)
email	VARCHAR(150)

residence

Buildings, rooms, rental contracts, rent dues, deposits, short stays and subscriptions.

Reference and core rental objects

Table	Key elements
residence.categorie_charge	id_categorie_charge PK, libelle, actif
residence.batiment	id_batiment PK, code UQ, nom, adresse, actif
residence.logement	id_logement PK, id_batiment FK, numero, nom, type (CHAMBRE/STUDIO), tarif, equipements, statut (DISPONIBLE/RESERVE/OCCUPE/EN_NETTOYAGE/EN_MAINTENANCE/INDISPONIBLE), etat
residence.contrat_location	id_contrat PK, numero_contrat UQ, id_client FK, id_logement FK, date_debut, date_fin_prevue, duree_mois, type_location (MENSUEL/ANNUEL), montant_loyer, periodicite, statut (EN_ATTENTE/ACTIF/RESILIE/TERMINE), date_signature, date_resiliation, motif_resiliation

Rent, deposits and subscriptions

Table	Key elements
residence.echeance_loyer	id_echeance PK, id_contrat FK, mois (1-12), annee, montant, date_echeance, statut, id_paiement FK → finances.paiement; UQ (id_contrat, mois, annee)
residence.application_paiement_echeance	id_application PK, id_paiement FK → finances.paiement, id_echeance FK → residence.echeance_loyer, montant_applique > 0
residence.caution	id_caution PK, id_contrat FK UQ, montant, date_versement, payee, date_restitution, montant_restitue, retenue, motif_retenue, statut
residence.historique_caution	id_histo PK, id_caution FK, evenement, date, montant, motif, id_utilisateur FK, id_paiement FK
residence.abonnement	id_abonnement PK, id_client FK, id_logement FK, id_categorie_abonnement FK → abonnement.categorie_abonnement, service, type, montant, date_debut, date_fin, montant_paye, statut

Stays, reservations and charges

Table	Key elements
residence.sejour_court	id_sejour PK, type_prestation (NUITEE/SIESTE), id_client FK, id_logement FK, date_heure_arrivee, date_heure_depart_prevue, date_heure_depart_reelle, tarif, montant_total, montant_paye, reste_a_payer, id_moyen_paiement FK → finances.moyen_paiement, statut
residence.reservation_logement	id_reservation PK, id_client FK, id_logement FK, date_arrivee, date_depart, nombre_nuits, acompte, statut (EN_ATTENTE/CONFIRMEE/ANNULEE/REALISEE), notes
residence.charge_facturee	id_charge PK, id_logement FK, id_categorie_charge FK, periode, compteur_numero, lecture_debut, lecture_fin, consommation, montant, montant_paye, reste_a_payer, statut

Other objects

Table	Key elements
residence.revision_loyer	id_revision PK, id_contrat FK, ancien_montant, nouveau_montant > 0, date_effet, motif, id_utilisateur FK, date_revision
residence.etat_des_lieux_photo	ENTREE/SORTIE photos for a contract, object-storage key, id_utilisateur FK

Functions and views

Object	Purpose
residence.generer_echeances(p_id_contrat, p_jusqua)	Create monthly dues for a contract
residence.reviser_loyer(...)	Atomic rent revision with trace and future-due re-pricing
residence.encaisser_loyer(...)	Single-due payment creating a finances.paiement
residence.encaisser_loyer_lot(...)	Lot payment: deposit financing → arrears → advance
residence.statuer_echeance_paiement()	Trigger: recalculates due status from applications
residence.suivi_loyer	Per-due rent tracking with total paid, last payment and encashor
residence.releve_client	Client statement with LOYER / CHARGE / FACTURE rows
residence.impayes_loyers	Overdue/partial dues with days late
residence.occupation_batiment	Building occupancy % and rent CA

market

Product catalog, suppliers, stock movements and sales.

Tables

Table	Key elements
market.categorie_produit	id_categorie_produit PK, libelle, actif
market.fournisseur	id_fournisseur PK, nom, contact, telephone, email, adresse, actif
market.produit	id_produit PK, reference UQ, nom, id_categorie_produit FK, prix_achat, prix_vente, quantite_stock, seuil_alerte, id_fournisseur FK, date_entree, actif, image_url, code_barre (partial UQ)
market.mouvement_stock	id_mouvement PK, id_produit FK, type (ENTREE/SORTIE/AJUSTEMENT), quantite, date, motif, document_ref, id_utilisateur FK
market.vente	id_vente PK, id_client FK nullable, date, remise, total, statut (EN_COURS/PAYEE/ANNULEE), id_utilisateur FK
market.ligne_vente	id_ligne PK, id_vente FK, id_produit FK, quantite > 0, prix_unitaire, remise_ligne, total_ligne

Functions and views

Object	Purpose
market.solder_stock_mouvement()	Trigger: updates produit.quantite_stock after each movement, rejects negative stock
market.reesolde_stock()	Rebuilds all product stocks from movement history
market.stock_alerte	Products with stock ≤ alert threshold
market.stock_historique	Running balance per product

pressing

Laundry order lifecycle.

Table	Key elements
pressing.commande_pressing	id_commande PK, id_client FK, numero_commande UQ, date_depot, date_retrait_prevue, date_retrait_reelle, montant_total, acompte, reste_a_payer, statut (DEPOSE/EN_TRAITEMENT/PRET/RETIRE/ANNULEE)
pressing.ligne_commande_pressing	id_ligne PK, id_commande FK, type_vetement, quantite > 0, prestation, tarif, total

restaurant

Dishes and orders.

Table	Key elements
restaurant.categorie_plat	id_categorie_plat PK, libelle
restaurant.plat	id_plat PK, nom, id_categorie_plat FK, prix, disponible, description, image_url
restaurant.commande_restaurant	id_commande PK, id_client FK nullable, date, type (SUR_PLACE/A_EMPORTER/LIVRAISON), total, statut (EN_COURS/EN_PREPARATION/SERVIE/PAYEE/ANNULEE), id_utilisateur FK
restaurant.ligne_commande_restaurant	id_ligne PK, id_commande FK, id_plat FK, quantite > 0, prix_unitaire, total

salle_fete

Party-room reservations.

Table	Key elements
salle_fete.reservation_fete	id_reservation PK, id_client FK, date_evenement, heure_debut, duree > 0, type_manifestation, tarif, acompte, solde, statut (DISPONIBLE/RESERVEE/CONFIRMEE/REALISEE/ANNULEE), observations

facturation

Services and invoices.

Table	Key elements
facturation.prestation	id_prestation PK, libelle, categorie, prix, description, id_activite FK, actif
facturation.facture	id_facture PK, numero UQ, date, id_client FK, id_activite FK, remise, montant_total, montant_paye, reste, statut (PAYEE/PARTIELLE/IMPAYEE/ANNULEE), source_type (VENTE/COMMANDE_PRESSING/…), source_id, libelle
facturation.ligne_facture	id_ligne PK, id_facture FK, libelle, quantite > 0, prix_unitaire, total

facturation.facture.source_type is polymorphic; source_id is a generic BIGINT with no FK.

finances

Payments, expenses, cash drawers, payment applications and consolidation.

Reference tables

Table	Key elements
finances.activite	id_activite PK, code UQ, libelle, actif
finances.moyen_paiement	id_moyen PK, libelle, actif
finances.categorie_depense	id_categorie_depense PK, libelle

Movement tables

Table	Key elements
finances.paiement	id_paiement PK, date, montant, id_moyen FK, id_activite FK, type (ENCAISSEMENT/DECAISSEMENT), motif, id_utilisateur FK, reference, id_caisse FK, id_periode_caisse FK
finances.depense	id_depense PK, date, montant, id_categorie_depense FK, id_activite FK nullable, libelle, justificatif, id_utilisateur FK nullable, id_caisse FK, id_periode_caisse FK

Payment application and cash management

Table	Key elements
finances.application_paiement	id_application PK, id_paiement FK, id_facture FK, montant_applique > 0
finances.caisse	id_caisse PK, libelle, id_activite FK, actif
finances.tirage_caisse	id_tirage PK, id_caisse FK, date, montant_compte, montant_attendu, ecart, id_utilisateur FK, note, date_tirage; UQ (id_caisse, date)
finances.periode_caisse	id_periode PK, id_caisse FK, ouverture, fermeture, id_utilisateur_ouverture FK, id_utilisateur_fermeture FK; partial UQ (id_caisse) where fermeture IS NULL

Functions and views

Object	Purpose
finances.solder_facture_application()	Trigger: recomputes facture.montant_paye, reste, statut on payment application
finances.ca_par_activite	Per-activity encaissements, decaissements, depenses, net (fan-out fix)
finances.tableau_de_bord	Monthly financial and invoicing dashboard

rh

Employees, attendance and payroll.

Table	Key elements
rh.service_rh	id_service PK, libelle
rh.employe	id_employe PK, nom, prenom, telephone, fonction (chk restricted list), id_service FK, date_embauche, type_contrat, salaire_base, statut, autres_infos
rh.pointage	id_pointage PK, id_employe FK, date, heure_arrivee, heure_depart, duree_travaillee, statut, heures_sup, note; UQ (id_employe, date)
rh.paie	id_paie PK, id_employe FK, periode, salaire_base, total_elements, total_retenues, montant_a_payer, statut, id_paiement FK → finances.paiement UQ
rh.element_salaire	id_element PK, id_paie FK, type (PRIME/AVANCE/RETENUE/HEURE_SUP/AUTRE), libelle, montant signed

Functions

Object	Purpose
rh.pointer_heure_arrivee()	Trigger: fills heure_arrivee and computes duree_travaillee
rh.recalculer_paie(p_id_paie)	Recomputes total_elements, total_retenues, montant_a_payer; skips PAYEE/ANNULEE
rh.recalculer_paie_elements()	Trigger: recomputes payslip when an element changes

abonnement

Subscription category catalog.

Table	Key elements
abonnement.categorie_abonnement	id_categorie_abonnement PK, code UQ, libelle, description, actif, date_creation, date_modification, prix_defaut

Seeded categories: RESTAURANT, PRESSING, SALLE_FETE, INTERNET, TV.

admin

Users, roles, permissions, tokens and backup metadata. See the security page for details.

Table	Purpose
admin.role	Role catalogue
admin.permission	Atomic permission catalogue
admin.role_permission	N:N role/permission links
admin.utilisateur	User accounts
admin.refresh_token	Refresh-token hashes
admin.password_reset_token	Password-reset-token hashes
admin.sauvegarde	Backup log
admin.sauvegarde_planification	Singleton backup schedule

audit

See the security page for details.

Table	Purpose
audit.journal_audit	Append-only operation log

Key cross-module relationships

client.client
  ├── residence.contrat_location.id_client
  ├── residence.abonnement.id_client
  ├── residence.sejour_court.id_client
  ├── residence.reservation_logement.id_client
  ├── pressing.commande_pressing.id_client
  ├── salle_fete.reservation_fete.id_client
  ├── market.vente.id_client
  ├── restaurant.commande_restaurant.id_client
  └── facturation.facture.id_client

finances.paiement
  ├── residence.echeance_loyer.id_paiement (legacy)
  ├── residence.application_paiement_echeance.id_paiement
  ├── residence.historique_caution.id_paiement
  └── rh.paie.id_paiement

facturation.facture
  ├── finances.application_paiement.id_facture
  └── finances.tableau_de_bord (dashboard)

admin.utilisateur
  ├── admin.role.id_role
  ├── finances.activite.id_activite_scope
  ├── finances.caisse.id_caisse
  ├── rh.employe.id_employe
  └── client.client.id_client

Working with the data model

Inserting a client, contract and rent due

-- 1. Create the client
INSERT INTO client.client (nom, prenoms, tel_principal, type_client)
VALUES ('Kouassi', 'Yao Blé Jean', '0123456789', 'LOCATAIRE')
RETURNING id_client;

-- 2. Create a rental contract (assume room 1 exists)
INSERT INTO residence.contrat_location (
    numero_contrat, id_client, id_logement, date_debut, date_fin_prevue,
    duree_mois, type_location, montant_loyer, periodicite, statut, date_signature
)
VALUES ('CTR-2026-001', 1, 1, '2026-09-01', '2027-08-31',
        12, 'MENSUEL', 65000, 1, 'ACTIF', '2026-09-01')
RETURNING id_contrat;

-- 3. Generate monthly dues for the contract
SELECT residence.generer_echeances(1);

Recording a rent payment

-- 1. Create the payment in finances
INSERT INTO finances.paiement (
    date, montant, id_moyen, id_activite, type, motif, id_utilisateur, reference
)
VALUES (
    now(), 65000, 1,
    (SELECT id_activite FROM finances.activite WHERE code = 'LOCATION_RESIDENTIEL'),
    'ENCAISSEMENT', 'Loyer septembre 2026 CTR-2026-001', 1, 'LOYER-CTR-2026-001-9-2026'
)
RETURNING id_paiement;

-- 2. Link the payment to the rent due
UPDATE residence.echeance_loyer
SET id_paiement = 1
WHERE id_echeance = 1;

-- The trigger statuer_echeance_paiement updates statut to PAYE or PARTIEL.

Querying the rent dashboard

-- Total rent collected vs expected for each building this month
SELECT
    b.code,
    b.nom,
    count(l.id_logement) AS logements,
    count(*) FILTER (WHERE l.statut = 'OCCUPE') AS occupes,
    sum(e.montant) FILTER (WHERE e.statut = 'PAYE') AS encaisse,
    sum(e.montant) FILTER (WHERE e.statut IN ('A_VENIR', 'IMPAYE', 'PARTIEL')) AS attendu
FROM residence.batiment b
LEFT JOIN residence.logement l ON l.id_batiment = b.id_batiment
LEFT JOIN residence.contrat_location c
       ON c.id_logement = l.id_logement AND c.statut = 'ACTIF'
LEFT JOIN residence.echeance_loyer e
       ON e.id_contrat = c.id_contrat
      AND e.mois = 9 AND e.annee = 2026
GROUP BY b.id_batiment, b.code, b.nom;

Applying a payment to an invoice

-- After creating a payment, insert one application row per invoice.
INSERT INTO finances.application_paiement (id_paiement, id_facture, montant_applique)
VALUES (1, 42, 60000);

-- Trigger finances.solder_facture_application updates:
-- facturation.facture.montant_paye, reste, statut

Stock movement example

-- Record an incoming delivery of 50 units of product 1
INSERT INTO market.mouvement_stock (
    id_produit, type, quantite, date, motif, document_ref, id_utilisateur
)
VALUES (1, 'ENTREE', 50, now(), 'Réception fournisseur', 'BL-2026-001', 1);

-- Record a sale that decrements stock
INSERT INTO market.mouvement_stock (
    id_produit, type, quantite, date, motif, document_ref, id_utilisateur
)
VALUES (1, 'SORTIE', 5, now(), 'Vente client', 'VTE-2026-001', 1);

Payroll recalculation

-- Add a bonus to a payslip
INSERT INTO rh.element_salaire (id_paie, type, libelle, montant)
VALUES (10, 'PRIME', 'Prime de performance', 15000);

-- The trigger recalculates rh.paie.total_elements and montant_a_payer
-- automatically.

What to avoid in data manipulation

Anti-pattern	Correct approach
UPDATE residence.contrat_location SET montant_loyer = 70000	Use residence.reviser_loyer(...) so the revision is traced
INSERT INTO residence.echeance_loyer manually for each month	Use residence.generer_echeances(id_contrat)
UPDATE facturation.facture SET statut = 'PAYEE' directly	Insert a finances.paiement and an application_paiement; let the trigger update the status
UPDATE rh.paie SET montant_a_payer = ... by hand	Use rh.element_salaire and let the trigger recompute
DELETE FROM audit.journal_audit	The audit table is append-only; deletion defeats R15