Modèle de données

Cette fiche décrit le schéma de la base PostgreSQL de TimePick : les tables principales, leurs relations, et les conventions suivies par toutes les migrations. Elle s'adresse à qui doit interroger la base directement (requêtes de diagnostic, export, restauration partielle) — pas à qui écrit du code applicatif.

Instantané daté

Au 2026-07-13, le schéma compte 13 tables et 38 migrations appliquées (001 à 038). Ces chiffres évoluent à chaque nouvelle migration ; voir server/src/migrations/ pour le décompte à jour.

Vue d'ensemble des relations

erDiagram
    events ||--o{ slots : "1-N"
    slots ||--o{ bookings : "1-N"
    users ||--o{ bookings : "1-N"
    events }o--o{ users : "event_users (M:N)"
    events ||--o{ invitations : "1-N"
    users ||--o{ invitations : "1-N"

Un événement porte des créneaux ; chaque créneau porte des réservations ; chaque réservation lie un créneau à un utilisateur. L'accès d'un utilisateur à un événement (avant même toute réservation) passe par la table de jointure event_users, distincte du suivi d'invitation (invitations).

Tables principales

Table	Rôle
users	Comptes utilisateurs — identité (email, first_name, last_name, phone), rôle, profil membre (profession, informations), authentification magic link
events	Événements — nom, description, workflow brouillon/publié, date d'ouverture (opens_at), date de fin, gabarit d'invitation propre à l'événement (invitation_mjml, facultatif)
event_users	Table de jointure M:N entre events et users — détermine quels membres voient et réservent un événement donné
slots	Créneaux horaires d'un événement — début, fin, capacité ; soft-delete via cancelled_at (voir plus bas)
bookings	Réservations — lie un slot à un user, unicité par couple créneau/utilisateur
invitations	Suivi des invitations envoyées à un membre pour un événement — statut (sent/clicked/failed), compteur d'envois
app_config	Configuration applicative clé/valeur — paramètres SMTP, intervalle de polling, durées de session, métadonnées de l'organisation
email_templates	Modèles d'email éditables, un par template_key (9 clés au 2026-07-13)
shell_parts	Parties MJML de l'enveloppe email commune, résolues en cascade marque → modèle → événement
email_brand_settings	Identité visuelle des emails — ligne unique (logo, couleurs, police)
admin_recovery_codes	Codes de secours administrateur, hachés (bcrypt)
recovery_audit_log	Journal d'audit des utilisations de codes de secours
schema_migrations	Suivi des migrations SQL déjà appliquées par le runner

Événements, créneaux, réservations

Le cœur fonctionnel de TimePick repose sur trois tables enchaînées :

• events — un événement passe par un workflow brouillon → publié. Tant qu'il est en brouillon, il n'est visible que dans l'administration. La date d'ouverture (opens_at) détermine, une fois l'événement publié, à partir de quand les membres peuvent réserver ; avant cette date, l'événement est consultable mais pas réservable (état « consultatif », voir la fiche Vue publique d'un événement).
• slots — chaque créneau appartient à un seul événement (event_id, ON DELETE CASCADE), avec une plage horaire (start_time/end_time, contrainte end_time > start_time) et une capacité (capacity, entier strictement positif).
• bookings — chaque réservation lie un créneau à un utilisateur (slot_id, user_id), avec une contrainte d'unicité sur le couple : impossible de réserver deux fois le même créneau avec le même compte.

Membres, accès et invitations

• event_users — clé primaire composite (event_id, user_id) : c'est la table qui donne l'accès d'un membre à l'espace d'un événement (visibilité, réservation). Elle est peuplée à l'envoi d'une invitation ou à l'ajout manuel d'un membre par un administrateur.
• invitations — trace l'historique d'envoi des invitations (une ligne par couple event_id/user_id, contrainte d'unicité), avec un statut (sent, clicked, failed) et un compteur de renvois (send_count). Distincte d'event_users : une invitation peut échouer sans retirer l'accès déjà accordé, et l'accès peut être accordé sans passer par une invitation (ajout manuel).

Configuration et identité visuelle

• app_config — table clé/valeur (clé primaire = key, texte) qui porte tous les réglages modifiables depuis l'interface d'administration : provisionnement SMTP, intervalle de polling du tableau de bord, durées de session (TTL magic link admin/membre, durée de session), métadonnées de l'organisation. Voir la fiche Paramètres in-app.
• email_brand_settings — ligne unique (singleton) portant l'identité visuelle des emails : logo, couleurs, police, arrondi. Ce réglage est unique à l'instance ; il ne se décline pas par modèle ou par événement.
• email_templates — un modèle par template_key. Les 9 clés existantes couvrent l'invitation, la connexion par lien magique, la confirmation de réservation, la confirmation d'annulation, la confirmation de désinscription, la création de compte, la promotion et la rétrogradation de rôle, et la modification de créneau. Voir la fiche Éditeur d'emails.
• shell_parts — parties communes de l'enveloppe MJML (en-tête, corps, pied), avec une référence polymorphe (owner_kind + owner_id) vers soit la marque de l'instance, soit un modèle d'email, soit un événement. La résolution suit la cascade marque → modèle → événement : la valeur la plus spécifique disponible l'emporte.

Sécurité et authentification de secours

• admin_recovery_codes — codes de secours administrateur, générés par lot et stockés hachés (bcrypt), jamais en clair. Voir la fiche Connexion de secours.
• recovery_audit_log — journal d'audit de chaque tentative d'utilisation d'un code de secours (succès ou échec), pour la traçabilité.

Suivi des migrations

La table schema_migrations (clé primaire version, texte — le préfixe numérique du fichier, ex. 038) enregistre une ligne par migration appliquée, avec l'horodatage d'application (applied_at). Le runner (npm run migrate) s'appuie dessus pour ne rejouer que les migrations manquantes ; elle ne contient aucune donnée applicative.

Relations clés

• Événement → créneaux → réservations : une chaîne 1-N-N. Supprimer un événement supprime en cascade ses créneaux, qui suppriment en cascade leurs réservations (ON DELETE CASCADE sur toutes les clés étrangères du schéma).
• event_users (M:N) : un utilisateur peut être rattaché à plusieurs événements, un événement peut avoir plusieurs utilisateurs rattachés. Cette table conditionne l'accès à l'espace membre d'un événement, indépendamment des réservations effectives.
• invitations : une ligne par couple (event_id, user_id) (contrainte d'unicité) — trace l'historique d'envoi, distincte de event_users qui trace l'accès.
• bookings : une ligne par couple (slot_id, user_id) (contrainte d'unicité) — un utilisateur ne peut réserver qu'une fois le même créneau.

Conventions du schéma

• Nommage : tables et colonnes en snake_case (ex. first_name, opens_at, cancelled_at).
• Identifiants : toutes les clés primaires sont des UUID générés côté base (uuid_generate_v4()), à l'exception d'app_config dont la clé primaire est la clé de configuration elle-même (key, texte).
• Horodatages : created_at et, pour les tables qui en disposent, updated_at, en TIMESTAMP WITH TIME ZONE.
• Trigger updated_at : les tables events, slots et invitations portent un trigger automatique (update_events_updated_at, update_slots_updated_at, update_invitations_updated_at) qui met à jour updated_at avant chaque UPDATE, sans intervention applicative.
• Soft-delete des créneaux : un créneau annulé n'est jamais supprimé de la table slots. La colonne slots.cancelled_at (horodatage, NULL = créneau actif) marque l'annulation ; slots.cancellation_reason porte le motif optionnel saisi par l'administrateur. Les réservations existantes sont préservées telles quelles — c'est le canal qui permet de notifier les inscrits d'un créneau annulé.
• Suppressions en cascade : toutes les clés étrangères du schéma utilisent ON DELETE CASCADE — supprimer un utilisateur ou un événement entraîne la suppression de tout ce qui en dépend (réservations, invitations, rattachements).

Requêtes de diagnostic en lecture seule

Lecture seule

Les requêtes ci-dessous sont fournies à titre d'exemple pour du diagnostic via psql (voir Dépannage). Aucune n'écrit en base ; ne les adaptez pas en UPDATE/DELETE sans avoir d'abord fait une sauvegarde (voir Sauvegarde et restauration).

Dernières migrations appliquées :

SELECT version, applied_at FROM schema_migrations ORDER BY applied_at DESC LIMIT 5;

Créneaux actifs (non annulés) d'un événement, par nom :

SELECT e.name, COUNT(s.id) AS creneaux_actifs
FROM events e
LEFT JOIN slots s ON s.event_id = e.id AND s.cancelled_at IS NULL
GROUP BY e.id
ORDER BY e.name;

Pour aller plus loin

• Détail de chaque table (DDL, contraintes, index) et ordre des migrations : les fichiers SQL source, un par migration numéroté 001 à 038, dans server/src/migrations/.
• Pour la façon d'appliquer les migrations lors d'une installation ou d'une mise à jour, voir Prérequis et Version et changelog.