3 schemas PostgreSQL pour structurer Supabase proprement

Points cles : Separer une base de donnees Supabase PostgreSQL en trois schemas distincts (privat, collaborative, gamification) des le depart evite le chaos a mesure que le projet grandit. Cet article couvre le pourquoi, le comment, les politiques RLS, les conventions de nommage, et les erreurs a ne pas reproduire.

Quand tu demarres un projet, la tentation est enorme : tu crees tes tables dans le schema public de PostgreSQL, et tu avances. Une table ici, une autre la. En trois semaines, tu te retrouves avec 40 tables melangees sans aucune logique, des noms incoherents, et une peur bleue de toucher quoi que ce soit.

J'ai decide tres tot dans le developpement de TAMSIV de structurer la base de donnees avec trois schemas separes. Six mois et 650+ commits plus tard, c'est probablement la meilleure decision architecturale que j'ai prise. Voici pourquoi, et comment le faire concretement avec Supabase.

Pourquoi ne pas tout mettre dans le schema public ?

Le schema public de PostgreSQL est le defaut. Supabase l'utilise pour ses propres tables internes. Quand tu y ajoutes tes tables, tu melanges tes donnees metier avec l'infra. C'est comme ranger tes vetements dans la cuisine — ca marche, mais c'est le chaos.

Les problemes concrets que j'ai identifies :

• Lisibilite : Avec 40+ tables dans un seul schema, retrouver une table devient un jeu de devinettes.
• Securite : Les politiques RLS (Row Level Security) deviennent impossibles a raisonner quand des tables de domaines differents cohabitent.
• Evolution : Ajouter un nouveau domaine fonctionnel (gamification, analytics) sans toucher a l'existant est impossible si tout est dans le meme sac.
• Collaboration : Quand un autre dev (ou toi-meme dans 6 mois) decouvre le projet, il ne sait pas par ou commencer.

Comment structurer les schemas d'une app Supabase ?

Voici les trois schemas de TAMSIV et ce qu'ils contiennent :

Schema privat — les donnees personnelles

Tout ce qui appartient a un utilisateur et a lui seul :

• privat.tasks — Taches personnelles
• privat.memos — Memos vocaux et textuels
• privat.calendar_events — Evenements d'agenda
• privat.user_profiles — Profils utilisateur
• privat.task_attachments / privat.memo_attachments — Pieces jointes

Pourquoi privat et pas private ? Parce que private est un mot reserve en SQL. J'ai appris ca a mes depens apres une migration ratee qui a casse tout le schema. Le message d'erreur n'etait meme pas clair — il a fallu 45 minutes pour comprendre que le nom du schema etait le probleme.

Schema collaborative — les groupes

Tout ce qui touche au travail en equipe :

• collaborative.groups — Groupes hierarchiques (jusqu'a 6 niveaux)
• collaborative.group_members — Membres et roles
• collaborative.group_tasks / collaborative.group_memos — Contenu partage
• collaborative.checklists — Checklists avec validation

Ce schema est le plus complexe en termes de politiques RLS car les regles d'acces dependent du role de l'utilisateur dans le groupe, de la hierarchie des groupes, et des permissions heritees. J'ai detaille la complexite des groupes hierarchiques dans l'article dedie.

Schema gamification — l'engagement

Tout ce qui rend l'app addictive (dans le bon sens) :

• gamification.user_stats — Points, niveau, streak courant
• gamification.user_badges — Badges debloquables
• gamification.points_history — Historique detaille des points
• gamification.daily_challenges — Defis quotidiens
• gamification.feed_activity — Flux d'activite social

Ce schema a ete ajoute trois mois apres le debut du projet. Grace a la separation, j'ai cree un nouveau schema sans toucher aux deux autres. Zero risque de casser l'existant. L'architecture de gamification est detaillee dans l'article sur le schema de gamification.

Quels avantages concrets en pratique ?

Au-dela de la theorie, voici ce que la separation en schemas m'a apporte au quotidien :

La lisibilite immédiate

Quand je fais SELECT * FROM privat.tasks, je sais instantanement que c'est une donnee personnelle. Quand je vois collaborative.group_members, je sais que c'est lie aux groupes. Pas besoin de documentation supplementaire — le schema EST la documentation.

Des politiques RLS plus simples a raisonner

Les regles du schema privat sont limpides : tu ne vois que tes donnees. Point. La politique RLS tient en une ligne :

CREATE POLICY "users_own_data" ON privat.tasks
  USING (user_id = auth.uid());

Celles du schema collaborative sont plus complexes : tu vois les donnees des groupes dont tu es membre, selon ton role, avec heritage des permissions parentales. Mais comme elles sont isolees dans leur schema, la complexite ne deborde pas sur le reste.

L'evolution sans risque

Ajouter la gamification n'a necessité aucune modification des schemas existants. Ajouter l'agenda avec participants et filtres non plus. Chaque nouveau domaine fonctionnel est autonome.

Comment gerer les politiques RLS avec plusieurs schemas ?

Supabase utilise Row Level Security (RLS) pour securiser l'acces aux donnees. Le principe est simple : chaque table a des regles qui determinent qui peut lire, ecrire, modifier, supprimer.

En pratique, c'est un labyrinthe. Au total, TAMSIV compte plus de 30 politiques RLS, chacune testee individuellement. Voici comment je les organise :

• Schema privat : Politiques simples, basees sur auth.uid() = user_id.
• Schema collaborative : Politiques complexes qui verifient l'appartenance au groupe ET le role. Utilisation de sous-requetes sur collaborative.group_members.
• Schema gamification : Politiques mixtes — lecture publique pour le leaderboard, ecriture uniquement via des fonctions RPC serveur.

Le piege le plus courant : une politique RLS trop permissive qui laisse fuiter des donnees entre utilisateurs. J'en parle dans l'article sur l'audit de securite et le rate limiting.

Quelles conventions de nommage adopter ?

Des conventions strictes des le jour 1 evitent des heures de confusion plus tard. Voici celles de TAMSIV :

• Tables : snake_case au pluriel (tasks, group_members, daily_challenges)
• Colonnes : snake_case (user_id, created_at, is_completed)
• Parametres RPC : Prefixe p_ (p_start_date, p_group_id, p_user_id)
• Fonctions RPC : verbe_objet (get_consolidated_feed, add_gamification_points)

Le prefixe p_ pour les parametres RPC est critique. Sans lui, le jour ou ton parametre user_id entre en conflit avec la colonne user_id dans une requete, PostgreSQL ne leve pas d'erreur — il prend la colonne en priorite. Le resultat : une requete qui retourne des donnees inattendues, un bug silencieux et dangereux.

Comment migrer vers plusieurs schemas si le projet existe deja ?

Si tu as deja tout dans public et que tu veux migrer, voici la strategie :

1. Audit : Liste toutes tes tables et classe-les par domaine fonctionnel.
2. Cree les schemas : CREATE SCHEMA privat; CREATE SCHEMA collaborative;
3. Migre table par table : ALTER TABLE public.tasks SET SCHEMA privat;
4. Met a jour les politiques RLS : Elles sont liees a la table, donc elles suivent le deplacement.
5. Met a jour le code frontend : Toutes les requetes Supabase doivent maintenant specifier le schema.

Attention : les foreign keys, les triggers et les fonctions RPC doivent etre mis a jour manuellement. C'est un chantier, mais le gain en maintenabilite est enorme. Si tu utilises Supabase, lis aussi mon article sur la reduction de l'egress pour optimiser les couts.

Quelles erreurs eviter lors de la structuration ?

En 6 mois de dev solo, j'ai identifie plusieurs pieges :

• Ne pas utiliser de mots reserves SQL comme noms de schema. private, public, user sont tous reserves. Utilise des alternatives (privat, app_public, accounts).
• Ne pas oublier les permissions GRANT. Creer un schema ne suffit pas — il faut explicitement donner les droits d'acces aux roles Supabase (anon, authenticated, service_role).
• Ne jamais faire confiance aux fichiers de migration locaux. La base de donnees reelle est la seule source de verite. Les fichiers de migration peuvent diverger apres des corrections manuelles.
• Tester chaque politique RLS individuellement. 30+ politiques, ca veut dire 30+ scenarios de test minimum. C'est methodique, ennuyeux, et absolument indispensable.

Quel impact sur les performances ?

Bonne nouvelle : les schemas PostgreSQL n'ont aucun impact sur les performances des requetes. Un SELECT FROM privat.tasks est exactement aussi rapide qu'un SELECT FROM public.tasks. Les schemas sont une organisation logique, pas physique.

En revanche, la separation facilite l'optimisation. Tu peux ajouter des index specifiques par domaine, configurer des parametres de vacuum differents par schema, et monitorer les performances par domaine fonctionnel.

Pour le frontend, le refactoring clean architecture a aussi joue un role crucial dans la maintenabilite du code qui interagit avec la base.

FAQ

Combien de schemas faut-il creer pour une app classique ?

Deux a quatre suffisent pour la plupart des projets. Un pour les donnees utilisateur, un pour les fonctionnalites partagees/collaboratives, eventuellement un pour l'analytics ou la gamification. L'important est de separer par domaine fonctionnel, pas par type de donnee.

Est-ce que Supabase supporte bien les schemas multiples ?

Oui, mais il faut configurer les permissions manuellement. Par defaut, seul le schema public est accessible via l'API REST. Pour exposer un autre schema, il faut l'ajouter dans les parametres du projet Supabase et configurer les GRANT appropriees sur les roles anon et authenticated.

Faut-il des foreign keys entre schemas ?

Oui, et c'est parfaitement supporte par PostgreSQL. Une table dans collaborative peut referencer une table dans privat. Les contraintes de foreign key fonctionnent entre schemas sans aucune limitation.

Comment gerer les fonctions RPC qui touchent plusieurs schemas ?

Les fonctions RPC (comme get_consolidated_feed) peuvent joindre des tables de schemas differents sans probleme. La bonne pratique est de creer la fonction dans le schema du domaine principal qu'elle sert, et d'utiliser des noms pleinement qualifies (privat.tasks) dans les requetes.

Peut-on revenir en arriere apres avoir migre vers plusieurs schemas ?

Techniquement oui, avec ALTER TABLE SET SCHEMA public. Mais en pratique, une fois que le code frontend reference les schemas, le retour en arriere est couteux. C'est pourquoi il vaut mieux prendre cette decision tot dans le projet.