Base de données

L'API garde toutes ses données dans PostgreSQL 17, à travers Ecto, la bibliothèque d'accès aux bases d'Elixir. La structure des tables est créée et modifiée par les migrations de timemanager-api/priv/repo/migrations/, appliquées à chaque démarrage du conteneur (voir Docker).

Le schéma

Source : docs/database.mmd, inclus tel quel ci-dessous. Chaque colonne y a un commentaire qui dit son rôle. Une version en ligne est aussi disponible sur mermaid.ai.

Lire un schéma entité-relation

Chaque boîte est une table, chaque ligne une colonne (PK clé primaire, FK clé étrangère, UK valeur unique). Les traits relient les tables : ||--o{ veut dire « un » d'un côté et « zéro ou plusieurs » de l'autre. Par exemple, un utilisateur a zéro ou plusieurs pointages. Clique sur le schéma pour l'agrandir.

Les tables en bref

Table	Contenu
users	Les comptes : email, nom, rôle, statut (invité ou actif), empreinte du mot de passe
password_tokens	Les liens d'invitation, de réinitialisation et de changement d'email. Seule l'empreinte du lien est gardée.
refresh_tokens	Les sessions de 30 jours, pour le web ou pour le mobile (client)
teams, team_members	Les équipes, leur manager et leurs membres
clocks	Chaque pointage brut : arrivée ou départ, à quelle heure, et quand le serveur l'a reçu
workingtime	Une journée de travail, de l'arrivée au départ
breaks	Les pauses, à l'intérieur d'une journée
correction_requests	Les demandes de correction et leur traitement. Un client_id évite d'enregistrer deux fois une demande renvoyée par le téléphone.
working_time_changes	L'historique de chaque modification manuelle des heures (avant, après, par qui)
absences	Un jour d'absence : congé, maladie ou autre, sans motif
week_validations	Les semaines validées par un manager, et leur réouverture
notifications	Ce qui est montré dans la cloche et envoyé par email

Toutes les dates sont en UTC, au format AAAA-MM-JJ hh:mm:ss (règle BR-09).

Deux sortes de dates

Type Ecto	Format renvoyé	Où
naive_datetime	2026-09-30T06:00:00, sans fuseau	les heures des journées et des pauses (start, end), au format demandé par le sujet
utc_datetime	2026-09-30T06:00:00Z, avec le Z d'UTC	tout le reste : un pointage, quand quelque chose a été enregistré, reçu, validé…

Dans les deux cas, l'heure est en UTC dans la base. C'est l'affichage qui la convertit : le site et l'application dans le fuseau de l'appareil, l'API dans celui de la mairie (Europe/Paris) quand elle doit savoir à quel jour appartient une heure (une semaine validée, une absence).

Ce que la base garantit elle-même

Le code vérifie déjà ces règles, mais la base les garantit aussi, pour toute écriture, quel que soit son chemin : c'est la défense en profondeur. Si un bug laissait passer un doublon, PostgreSQL refuserait l'écriture. Quand le changeset déclare la contrainte (unique_constraint), Ecto transforme ce refus en erreur 422 (code taken) ; sinon, la requête échoue sans rien écrire.

Contrainte	Table	Ce qu'elle garantit
users_email_lower_index	users	un e-mail par compte, sans tenir compte de la casse (Bruce@… = bruce@…)
teams_name_lower_index	teams	un nom d'équipe unique, sans tenir compte de la casse
workingtime_one_open_per_user	workingtime	une seule journée en cours par personne
breaks_one_open_per_working_time	breaks	une seule pause en cours par journée
absences_user_id_date_index	absences	une seule absence par personne et par jour
week_validations_in_force_index	week_validations	une seule validation en vigueur par semaine et par personne
clocks_user_id_client_id_index	clocks	un pointage envoyé deux fois par le téléphone n'est enregistré qu'une fois
breaks_start_client_id_index, breaks_end_client_id_index	breaks	la même chose pour le début et la fin d'une pause
correction_requests_user_id_client_id_index	correction_requests	la même chose pour une demande de correction
password_tokens_token_hash_index, refresh_tokens_token_hash_index		chaque jeton est unique

Les index partiels

Plusieurs de ces index sont partiels : ils ne portent que sur certaines lignes, grâce à une clause WHERE. Par exemple :

CREATE UNIQUE INDEX workingtime_one_open_per_user ON workingtime (user_id) WHERE "end" IS NULL;

Une personne a des centaines de journées terminées (avec une fin), mais au plus une sans fin. De même, client_id IS NOT NULL laisse libres les pointages du site web, qui n'ont pas d'identifiant d'appareil.

Les index sur une expression

lower(email) : l'index porte sur l'e-mail en minuscules. Deux adresses qui ne diffèrent que par la casse tombent sur la même entrée, et la seconde est refusée.

Quand un compte est supprimé

Une clé étrangère dit ce que devient une ligne quand la ligne qu'elle désigne disparaît. Deux comportements sont utilisés, selon ce qu'on veut garder :

Comportement	Tables	Pourquoi
CASCADE : la ligne est supprimée avec le compte	clocks, password_tokens, refresh_tokens, team_members, absences, week_validations, notifications	ces données n'ont plus de sens sans la personne
SET NULL : la ligne reste, le lien devient vide	workingtime, working_time_changes, correction_requests (et les champs « par qui » : recorded_by_id, validated_by_id, reviewed_by_id…)	les heures et leur historique sont gardés : les totaux des équipes et le journal des modifications restent justes
RESTRICT : la suppression est refusée	teams.manager_id	une équipe ne peut pas rester sans manager : il faut d'abord la confier à quelqu'un d'autre

Quand le lien devient vide, qui a fait quoi reste lisible grâce aux copies : changed_by_name, reviewed_by_name, user_email… sont recopiés au moment de l'action, justement pour survivre à une suppression.

Les migrations

Une migration est un fichier qui décrit un changement de structure : créer une table, ajouter une colonne, un index. Les migrations sont appliquées dans l'ordre de leur date (le début du nom de fichier), et la table schema_migrations retient celles qui sont déjà passées.

mix ecto.gen.migration add_reason_to_absences   # crée le fichier, à remplir
mix ecto.migrate                                 # applique les nouvelles
mix ecto.rollback                                # annule la dernière (en dev)

En production, l'image Docker les applique au démarrage, avec Timemanager.Release.migrate/0 (lib/timemanager/release.ex).

Les règles d'une bonne migration

• Additive autant que possible : ajouter une colonne ou une table, plutôt que renommer ou supprimer. L'ancienne version de l'API, encore en service pendant le déploiement, continue de fonctionner.
• Jamais modifier une migration déjà fusionnée : elle a déjà tourné sur d'autres bases. On en écrit une nouvelle.
• Mettre à jour docs/database.mmd dans la même PR, avec un commentaire pour chaque nouvelle colonne.