3 schemas PostgreSQL pour structurer Supabase proprement

Points clés : Séparer une base de données Supabase PostgreSQL en trois schémas distincts (privat, collaborative, gamification) dès le départ évite le chaos à mesure que le projet grandit. Cet article couvre le pourquoi, le comment, les politiques RLS, les conventions de nommage, et les erreurs à ne pas reproduire.

Quand tu démarres un projet, la tentation est énorme : tu crées tes tables dans le schéma public de PostgreSQL, et tu avances. Une table ici, une autre là. En trois semaines, tu te retrouves avec 40 tables mélangées sans aucune logique, des noms incohérents, et une peur bleue de toucher quoi que ce soit.

J'ai décidé très tôt dans le développement de TAMSIV de structurer la base de données avec trois schémas séparés. Six mois et 650+ commits plus tard, c'est probablement la meilleure décision architecturale que j'ai prise. Voici pourquoi, et comment le faire concrètement avec Supabase.

Pourquoi ne pas tout mettre dans le schéma public ?

Le schéma public de PostgreSQL est le défaut. Supabase l'utilise pour ses propres tables internes. Quand tu y ajoutes tes tables, tu mélanges tes données métier avec l'infra. C'est comme ranger tes vêtements dans la cuisine — ça marche, mais c'est le chaos.

Les problèmes concrets que j'ai identifiés :

• Lisibilité : Avec 40+ tables dans un seul schéma, retrouver une table devient un jeu de devinettes.
• Sécurité : Les politiques RLS (Row Level Security) deviennent impossibles à raisonner quand des tables de domaines différents cohabitent.
• Évolution : Ajouter un nouveau domaine fonctionnel (gamification, analytics) sans toucher à l'existant est impossible si tout est dans le même sac.
• Collaboration : Quand un autre dev (ou toi-même dans 6 mois) découvre le projet, il ne sait pas par où commencer.

Comment structurer les schémas d'une app Supabase ?

Voici les trois schémas de TAMSIV et ce qu'ils contiennent :

Schéma privat — les données personnelles

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

• privat.tasks — Tâches personnelles
• privat.memos — Mémos vocaux et textuels
• privat.calendar_events — Événements d'agenda
• privat.user_profiles — Profils utilisateur
• privat.task_attachments / privat.memo_attachments — Pièces jointes

Pourquoi privat et pas private ? Parce que private est un mot réservé en SQL. J'ai appris ça à mes dépens après une migration ratée qui a cassé tout le schéma. Le message d'erreur n'était même pas clair — il a fallu 45 minutes pour comprendre que le nom du schéma était le problème.

Schéma collaborative — les groupes

Tout ce qui touche au travail en équipe :

• collaborative.groups — Groupes hiérarchiques (jusqu'à 6 niveaux)
• collaborative.group_members — Membres et rôles
• collaborative.group_tasks / collaborative.group_memos — Contenu partagé
• collaborative.checklists — Checklists avec validation

Ce schéma est le plus complexe en termes de politiques RLS car les règles d'accès dépendent du rôle de l'utilisateur dans le groupe, de la hiérarchie des groupes, et des permissions héritées. J'ai détaillé la complexité des groupes hiérarchiques dans l'article dédié.

Schéma 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 débloquables
• gamification.points_history — Historique détaillé des points
• gamification.daily_challenges — Défis quotidiens
• gamification.feed_activity — Flux d'activité social

Ce schéma a été ajouté trois mois après le début du projet. Grâce à la séparation, j'ai créé un nouveau schéma sans toucher aux deux autres. Zéro risque de casser l'existant. L'architecture de gamification est détaillée dans l'article sur le schéma de gamification.

Quels avantages concrets en pratique ?

Au-delà de la théorie, voici ce que la séparation en schémas m'a apporté au quotidien :

La lisibilité immédiate

Quand je fais SELECT * FROM privat.tasks, je sais instantanément que c'est une donnée personnelle. Quand je vois collaborative.group_members, je sais que c'est lié aux groupes. Pas besoin de documentation supplémentaire — le schéma EST la documentation.

Des politiques RLS plus simples à raisonner

Les règles du schéma privat sont limpides : tu ne vois que tes données. Point. La politique RLS tient en une ligne :

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

Celles du schéma collaborative sont plus complexes : tu vois les données des groupes dont tu es membre, selon ton rôle, avec héritage des permissions parentales. Mais comme elles sont isolées dans leur schéma, la complexité ne déborde pas sur le reste.

L'évolution sans risque

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

Comment gérer les politiques RLS avec plusieurs schémas ?

Supabase utilise Row Level Security (RLS) pour sécuriser l'accès aux données. Le principe est simple : chaque table a des règles qui déterminent qui peut lire, écrire, modifier, supprimer.

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

• Schéma privat : Politiques simples, basées sur auth.uid() = user_id.
• Schéma collaborative : Politiques complexes qui vérifient l'appartenance au groupe ET le rôle. Utilisation de sous-requêtes sur collaborative.group_members.
• Schéma gamification : Politiques mixtes — lecture publique pour le leaderboard, écriture uniquement via des fonctions RPC serveur.

Le piège le plus courant : une politique RLS trop permissive qui laisse fuiter des données entre utilisateurs. J'en parle dans l'article sur l'audit de sécurité et le rate limiting.

Quelles conventions de nommage adopter ?

Des conventions strictes dès le jour 1 évitent 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)
• Paramètres RPC : Préfixe p_ (p_start_date, p_group_id, p_user_id)
• Fonctions RPC : verbe_objet (get_consolidated_feed, add_gamification_points)

Le préfixe p_ pour les paramètres RPC est critique. Sans lui, le jour où ton paramètre user_id entre en conflit avec la colonne user_id dans une requête, PostgreSQL ne lève pas d'erreur — il prend la colonne en priorité. Le résultat : une requête qui retourne des données inattendues, un bug silencieux et dangereux.

Comment migrer vers plusieurs schémas si le projet existe déjà ?

Si tu as déjà tout dans public et que tu veux migrer, voici la stratégie :

1. Audit : Liste toutes tes tables et classe-les par domaine fonctionnel.
2. Crée les schémas : CREATE SCHEMA privat; CREATE SCHEMA collaborative;
3. Migre table par table : ALTER TABLE public.tasks SET SCHEMA privat;
4. Met à jour les politiques RLS : Elles sont liées à la table, donc elles suivent le déplacement.
5. Met à jour le code frontend : Toutes les requêtes Supabase doivent maintenant spécifier le schéma.

Attention : les foreign keys, les triggers et les fonctions RPC doivent être mis à jour manuellement. C'est un chantier, mais le gain en maintenabilité est énorme. Si tu utilises Supabase, lis aussi mon article sur la réduction de l'egress pour optimiser les coûts.

Quelles erreurs éviter lors de la structuration ?

En 6 mois de dev solo, j'ai identifié plusieurs pièges :

• Ne pas utiliser de mots réservés SQL comme noms de schéma. private, public, user sont tous réservés. Utilise des alternatives (privat, app_public, accounts).
• Ne pas oublier les permissions GRANT. Créer un schéma ne suffit pas — il faut explicitement donner les droits d'accès aux rôles Supabase (anon, authenticated, service_role).
• Ne jamais faire confiance aux fichiers de migration locaux. La base de données réelle est la seule source de vérité. Les fichiers de migration peuvent diverger après des corrections manuelles.
• Tester chaque politique RLS individuellement. 30+ politiques, ça veut dire 30+ scénarios de test minimum. C'est méthodique, ennuyeux, et absolument indispensable.

Quel impact sur les performances ?

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

En revanche, la séparation facilite l'optimisation. Tu peux ajouter des index spécifiques par domaine, configurer des paramètres de vacuum différents par schéma, et monitorer les performances par domaine fonctionnel.

Pour le frontend, le refactoring clean architecture a aussi joué un rôle crucial dans la maintenabilité du code qui interagit avec la base.

FAQ

Combien de schémas faut-il créer pour une app classique ?

Deux à quatre suffisent pour la plupart des projets. Un pour les données utilisateur, un pour les fonctionnalités partagées/collaboratives, éventuellement un pour l'analytics ou la gamification. L'important est de séparer par domaine fonctionnel, pas par type de donnée.

Est-ce que Supabase supporte bien les schémas multiples ?

Oui, mais il faut configurer les permissions manuellement. Par défaut, seul le schéma public est accessible via l'API REST. Pour exposer un autre schéma, il faut l'ajouter dans les paramètres du projet Supabase et configurer les GRANT appropriées sur les rôles anon et authenticated.

Faut-il des foreign keys entre schémas ?

Oui, et c'est parfaitement supporté par PostgreSQL. Une table dans collaborative peut référencer une table dans privat. Les contraintes de foreign key fonctionnent entre schémas sans aucune limitation.

Comment gérer les fonctions RPC qui touchent plusieurs schémas ?

Les fonctions RPC (comme get_consolidated_feed) peuvent joindre des tables de schémas différents sans problème. La bonne pratique est de créer la fonction dans le schéma du domaine principal qu'elle sert, et d'utiliser des noms pleinement qualifiés (privat.tasks) dans les requêtes.

Peut-on revenir en arrière après avoir migré vers plusieurs schémas ?

Techniquement oui, avec ALTER TABLE SET SCHEMA public. Mais en pratique, une fois que le code frontend référence les schémas, le retour en arrière est coûteux. C'est pourquoi il vaut mieux prendre cette décision tôt dans le projet.