Construire Un Système De Réservation Temporelle Avec PostgreSQL

Un système de réservation de créneaux doit répondre à une question centrale : deux utilisateurs peuvent-ils réserver la même ressource au même moment ? Une vérification approximative côté application ne suffit pas. Entre les requêtes concurrentes, les fuseaux horaires et les transactions interrompues, seule la base de données peut garantir durablement l’intégrité du planning.

PostgreSQL fournit plusieurs outils adaptés à ce problème : types temporels, intervalles, index GiST, transactions et contraintes d’exclusion. Cette combinaison permet de modéliser un rendez-vous, de détecter les chevauchements et de rejeter automatiquement une réservation conflictuelle.

L’exemple suivant s’appuie sur une API Node.js, mais les principes restent valables avec Python, PHP, Ruby ou un autre environnement serveur. L’objectif est de construire une fondation fiable, extensible vers la gestion des disponibilités, des annulations et des calendriers partagés.

Modéliser Les Créneaux Et Les Ressources

Une réservation associe généralement une ressource à une période. La ressource peut être une salle, un professionnel, une machine ou un compte utilisateur. Il est préférable de stocker les dates en timestamptz, afin que PostgreSQL conserve un instant réel tout en permettant à l’application d’afficher l’heure dans le fuseau approprié.

Pour représenter une période, PostgreSQL propose les types de plages, notamment tstzrange. Une réservation de 10 h à 11 h peut être enregistrée avec une borne incluse et une borne exclue : [10:00, 11:00). Ainsi, un créneau qui commence exactement à 11 h ne chevauche pas celui qui se termine à cette heure.

CREATE TABLE reservations (
  id              BIGSERIAL PRIMARY KEY,
  resource_id     BIGINT NOT NULL REFERENCES resources(id),
  customer_id     BIGINT NOT NULL REFERENCES customers(id),
  starts_at       TIMESTAMPTZ NOT NULL,
  ends_at         TIMESTAMPTZ NOT NULL,
  period          TSTZRANGE GENERATED ALWAYS AS
                  (tstzrange(starts_at, ends_at, '[)')) STORED,
  status          TEXT NOT NULL DEFAULT 'confirmed',
  created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  CHECK (ends_at > starts_at)
);

La contrainte ends_at > starts_at empêche les durées nulles ou négatives. Dans un modèle plus compact, seule la colonne period pourrait être conservée. Toutefois, garder starts_at et ends_at simplifie les réponses JSON, les tris et les filtres destinés aux utilisateurs.

Empêcher Les Chevauchements Au Niveau SQL

La règle métier essentielle peut être exprimée avec une contrainte d’exclusion. Elle indique que deux lignes ne doivent pas avoir simultanément la même ressource et des périodes qui se recouvrent. PostgreSQL vérifie cette règle durant l’insertion ou la modification, y compris lorsque plusieurs clients envoient leur demande presque au même instant.

CREATE EXTENSION IF NOT EXISTS btree_gist;

ALTER TABLE reservations
ADD CONSTRAINT reservations_no_overlap
EXCLUDE USING gist (
  resource_id WITH =,
  period WITH &&
)
WHERE (status IN ('pending', 'confirmed'));

L’extension btree_gist permet d’utiliser l’opérateur d’égalité sur un identifiant dans un index GiST. L’opérateur && détecte le chevauchement de plages. Une réservation annulée peut donc rester dans l’historique sans bloquer un nouveau rendez-vous.

Cette approche est plus robuste qu’une séquence composée d’un SELECT, puis d’un INSERT. Deux transactions peuvent exécuter le SELECT avant qu’aucune ne voie la ligne de l’autre. La contrainte PostgreSQL devient alors le dernier rempart contre la double réservation. L’API doit intercepter l’erreur correspondante et retourner un statut HTTP 409 Conflict.

Interroger Un Planning Temporel

Pour rechercher les réservations actives pendant une fenêtre donnée, il suffit d’utiliser l’opérateur de chevauchement. Une requête comme period && tstzrange($1, $2, '[)') retourne les rendez-vous qui intersectent l’intervalle demandé. Elle convient à l’affichage d’un agenda, à la validation d’un formulaire ou au calcul des disponibilités.

Les requêtes temporelles peuvent aussi identifier les créneaux libres. On commence par définir les heures d’ouverture, puis on retire les périodes déjà réservées. Pour des règles complexes, une génération de créneaux de quinze ou trente minutes avec generate_series facilite le calcul :

WITH slots AS (
  SELECT generate_series(
    $1::timestamptz,
    $2::timestamptz - interval '30 minutes',
    interval '30 minutes'
  ) AS slot_start
)
SELECT slot_start,
       slot_start + interval '30 minutes' AS slot_end
FROM slots s
WHERE NOT EXISTS (
  SELECT 1
  FROM reservations r
  WHERE r.resource_id = $3
    AND r.status IN ('pending', 'confirmed')
    AND r.period && tstzrange(
      s.slot_start,
      s.slot_start + interval '30 minutes',
      '[)'
    )
)
ORDER BY slot_start;
Approche Avantage principal Limite Usage recommandé
Vérification dans l’application Simple à écrire Vulnérable aux accès concurrents Prototype ou prévalidation
SELECT avec verrouillage Contrôle explicite de la transaction Plus difficile à maintenir Règles métier complexes
Contrainte d’exclusion Garantie centralisée et fiable Requiert GiST et une bonne gestion d’erreurs Réservation en production
Créneaux générés par SQL Calcul clair des disponibilités Peut coûter cher sur de longues périodes Agenda journalier ou hebdomadaire

Le choix entre créneaux fixes et durée libre dépend du métier. Un cabinet peut accepter des rendez-vous de durée variable, tandis qu’un centre de formation préfère des tranches prédéfinies. Dans les deux cas, l’intervalle PostgreSQL reste la représentation la plus expressive.

Sécuriser La Création Et La Modification

La création d’une réservation doit s’effectuer dans une transaction. L’API valide d’abord l’identité du client, la ressource, les limites de durée et les horaires autorisés. Elle tente ensuite l’insertion. Si la contrainte d’exclusion échoue, la transaction est annulée et le serveur renvoie une réponse compréhensible.

Les paramètres doivent toujours être transmis avec des requêtes préparées. Il faut aussi normaliser les dates reçues en ISO 8601 avec fuseau, par exemple 2026-06-14T09:00:00Z. Une date locale sans indication de zone peut produire des décalages lors du passage à l’heure d’été ou lorsque l’utilisateur voyage.

Pour publier l’API, un proxy inverse Nginx peut terminer TLS, limiter certaines requêtes et répartir la charge entre plusieurs instances Node.js. Cette couche ne remplace toutefois pas la contrainte de base : plusieurs instances doivent pouvoir écrire sans créer de conflit logique.

Organiser Les Règles Métier Et L’exploitation

Une réservation possède souvent plusieurs états : pending, confirmed, cancelled ou expired. La contrainte d’exclusion doit viser uniquement les états qui bloquent réellement la ressource. Une réservation en attente peut aussi expirer avec un traitement périodique, exécuté par un worker ou une tâche planifiée.

Les créneaux récurrents nécessitent une attention particulière. Il est souvent préférable de développer les occurrences dans une table distincte plutôt que d’enregistrer une règle abstraite impossible à contrôler finement. Chaque occurrence devient alors une période soumise aux mêmes règles de conflit.

Contrôles À Prévoir

Les performances dépendent du volume et de la durée des recherches. La contrainte crée son index GiST, mais des index complémentaires sur resource_id, status ou starts_at peuvent accélérer les écrans d’administration. Les tests doivent couvrir les requêtes simultanées, les bornes identiques et les changements d’heure.

Scénarios De Test Utiles

Pour les notifications, il est possible d’émettre un événement après validation de la transaction. Un mécanisme comme une file de messages évite de bloquer la réponse HTTP avec l’envoi d’un courriel. Les données complémentaires, telles que des pièces jointes ou des exports, peuvent être conservées dans un stockage objet ; une comparaison entre stockage MinIO et S3 aide à choisir une solution locale ou hébergée.

Relier PostgreSQL À Une API Node.js

Avec le paquet pg, le service Node.js peut utiliser un pool de connexions et une transaction explicite. Le serveur reçoit les paramètres, démarre BEGIN, tente l’insertion, puis exécute COMMIT. En cas d’erreur, ROLLBACK libère la connexion dans un état propre.

Une réponse réussie peut contenir l’identifiant, l’instant de début, l’instant de fin et le statut. Pour les conflits, le code PostgreSQL doit être traduit en message fonctionnel, sans exposer les détails internes du schéma. Les journaux doivent conserver l’identifiant de corrélation, la ressource et la durée demandée, mais éviter les informations personnelles inutiles.

Pour tester rapidement des scénarios depuis un terminal, une interface CLI interactive peut demander une ressource, une date et une durée avant d’appeler l’API. Cette méthode accélère les essais manuels et permet de reproduire un conflit sans dépendre immédiatement d’une interface web.

Un système fiable repose donc sur trois niveaux : validation applicative, transaction serveur et contrainte PostgreSQL. Commencez par créer le schéma avec tstzrange, ajoutez l’exclusion GiST, puis exposez une route de réservation qui traite explicitement les conflits. Vous pourrez ensuite enrichir l’agenda avec les disponibilités, les rappels et les règles récurrentes sans fragiliser le cœur temporel.