3 PostgreSQL schemas for a clean app — structuring Supabase from the start

When you start a project, it's tempting to put everything into PostgreSQL's public schema. A table here, another there, and in three weeks you end up with 40 mixed tables with no logic.

I decided very early on to structure TAMSIV with three separate schemas.

The three schemas

• privat — All personal data: tasks, memos, calendar events, user profiles, attachments
• collaborative — Everything related to groups: members, roles, shared tasks, group checklists
• gamification — Stats, badges, streaks, daily challenges, points history

Why privat and not private? Because private is a reserved word in SQL. I learned that the hard way after a failed migration.

Why separate?

1. Readability. When I do SELECT * FROM privat.tasks, I immediately know it's personal data.

2. Security. RLS policies are easier to reason about when tables are grouped by domain. The rules for the privat schema are simple: you only see your data. Those for the collaborative schema are more complex: you see the data of the groups you are a member of, according to your role.

3. Evolution. When I added gamification three months after the project started, I created a new schema without touching the other two. Zero risk of breaking existing functionality.

The RLS policies nightmare

Supabase uses RLS to secure data access. The principle is simple: each table has rules that determine who can read, write, modify, delete. In practice, it's a labyrinth.

In total, the project has over 30 RLS policies. Each one individually tested.

Naming conventions

I adopted strict conventions: tables in plural snake_case, columns in snake_case, RPC parameters with p_ prefix. This p_ prefix avoids collisions with column names in queries. The day your user_id parameter conflicts with the user_id column, you'll understand why.

This structure saved me a considerable amount of time later on. Each new feature naturally finds its place.