3 PostgreSQL schemas to properly structure Supabase

Key points: Separating a Supabase PostgreSQL database into three distinct schemas (privat, collaborative, gamification) from the start prevents chaos as the project grows. This article covers why, how, RLS policies, naming conventions, and mistakes to avoid.

When you start a project, the temptation is huge: you create your tables in the public PostgreSQL schema, and you move forward. One table here, another there. In three weeks, you find yourself with 40 mixed tables without any logic, inconsistent names, and a deep fear of touching anything.

I decided very early in TAMSIV's development to structure the database with three separate schemas. Six months and 650+ commits later, it's probably the best architectural decision I've made. Here's why, and how to do it concretely with Supabase.

Why not put everything in the public schema?

The public PostgreSQL schema is the default. Supabase uses it for its own internal tables. When you add your tables there, you mix your business data with the infrastructure. It's like storing your clothes in the kitchen — it works, but it's chaos.

The concrete problems I've identified:

• Readability: With 40+ tables in a single schema, finding a table becomes a guessing game.
• Security: RLS (Row Level Security) policies become impossible to reason about when tables from different domains coexist.
• Evolution: Adding a new functional domain (gamification, analytics) without touching existing elements is impossible if everything is in the same bag.
• Collaboration: When another developer (or you in 6 months) discovers the project, they don't know where to start.

How to structure Supabase app schemas?

Here are TAMSIV's three schemas and what they contain:

privat schema — personal data

Everything that belongs to a user and only to them:

• privat.tasks — Personal tasks
• privat.memos — Voice and text memos
• privat.calendar_events — Calendar events
• privat.user_profiles — User profiles
• privat.task_attachments / privat.memo_attachments — Attachments

Why privat and not private? Because private is a reserved SQL keyword. I learned this the hard way after a failed migration that broke the entire schema. The error message wasn't even clear — it took 45 minutes to understand that the schema name was the problem.

collaborative schema — groups

Everything related to teamwork:

• collaborative.groups — Hierarchical groups (up to 6 levels)
• collaborative.group_members — Members and roles
• collaborative.group_tasks / collaborative.group_memos — Shared content
• collaborative.checklists — Checklists with validation

This schema is the most complex in terms of RLS policies because access rules depend on the user's role in the group, the group hierarchy, and inherited permissions. I detailed the complexity of hierarchical groups in the dedicated article.

gamification schema — engagement

Everything that makes the app addictive (in a good way):

• gamification.user_stats — Points, level, current streak
• gamification.user_badges — Unlockable badges
• gamification.points_history — Detailed points history
• gamification.daily_challenges — Daily challenges
• gamification.feed_activity — Social activity feed

This schema was added three months after the project started. Thanks to the separation, I created a new schema without touching the other two. Zero risk of breaking existing functionality. The gamification architecture is detailed in the article on the gamification schema.

What are the concrete benefits in practice?

Beyond theory, here's what schema separation has brought me daily:

Immediate readability

When I do SELECT * FROM privat.tasks, I instantly know it's personal data. When I see collaborative.group_members, I know it's related to groups. No need for additional documentation — the schema IS the documentation.

Easier RLS policies to reason about

The rules for the privat schema are crystal clear: you only see your data. Period. The RLS policy is one line:

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

Those for the collaborative schema are more complex: you see data from groups you are a member of, according to your role, with inheritance of parental permissions. But since they are isolated in their schema, the complexity doesn't spill over to the rest.

Risk-free evolution

Adding gamification required no modification of existing schemas. Adding the calendar with participants and filters didn't either. Each new functional domain is autonomous.

How to manage RLS policies with multiple schemas?

Supabase uses Row Level Security (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, TAMSIV has over 30 RLS policies, each tested individually. Here's how I organize them:

• privat schema: Simple policies, based on auth.uid() = user_id.
• collaborative schema: Complex policies that check group membership AND role. Uses subqueries on collaborative.group_members.
• gamification schema: Mixed policies — public read for the leaderboard, write only via server RPC functions.

The most common pitfall: an overly permissive RLS policy that leaks data between users. I discuss this in the article on security audit and rate limiting.

What naming conventions to adopt?

Strict conventions from day 1 prevent hours of confusion later. Here are TAMSIV's:

• Tables: snake_case plural (tasks, group_members, daily_challenges)
• Columns: snake_case (user_id, created_at, is_completed)
• RPC Parameters: Prefix p_ (p_start_date, p_group_id, p_user_id)
• RPC Functions: verb_object (get_consolidated_feed, add_gamification_points)

The p_ prefix for RPC parameters is critical. Without it, the day your user_id parameter conflicts with the user_id column in a query, PostgreSQL doesn't raise an error — it prioritizes the column. The result: a query that returns unexpected data, a silent and dangerous bug.

How to migrate to multiple schemas if the project already exists?

If you already have everything in public and want to migrate, here's the strategy:

1. Audit: List all your tables and classify them by functional domain.
2. Create schemas: CREATE SCHEMA privat; CREATE SCHEMA collaborative;
3. Migrate table by table: ALTER TABLE public.tasks SET SCHEMA privat;
4. Update RLS policies: They are linked to the table, so they follow the move.
5. Update frontend code: All Supabase queries must now specify the schema.

Beware: foreign keys, triggers, and RPC functions must be updated manually. It's a big undertaking, but the gain in maintainability is enormous. If you use Supabase, also read my article on reducing egress to optimize costs.

What mistakes to avoid when structuring?

In 6 months of solo development, I've identified several pitfalls:

• Do not use SQL reserved words as schema names. private, public, user are all reserved. Use alternatives (privat, app_public, accounts).
• Don't forget GRANT permissions. Creating a schema is not enough — you must explicitly grant access rights to Supabase roles (anon, authenticated, service_role).
• Never trust local migration files. The real database is the only source of truth. Migration files can diverge after manual corrections.
• Test each RLS policy individually. 30+ policies means 30+ test scenarios minimum. It's methodical, tedious, and absolutely essential.

What impact on performance?

Good news: PostgreSQL schemas have no impact on query performance. A SELECT FROM privat.tasks is exactly as fast as a SELECT FROM public.tasks. Schemas are a logical organization, not physical.

However, separation facilitates optimization. You can add specific indexes by domain, configure different vacuum parameters by schema, and monitor performance by functional domain.

For the frontend, clean architecture refactoring also played a crucial role in the maintainability of the code that interacts with the database.

FAQ

How many schemas should be created for a typical app?

Two to four are sufficient for most projects. One for user data, one for shared/collaborative features, possibly one for analytics or gamification. The important thing is to separate by functional domain, not by data type.

Does Supabase support multiple schemas well?

Yes, but you need to configure permissions manually. By default, only the public schema is accessible via the REST API. To expose another schema, you need to add it in the Supabase project settings and configure the appropriate GRANTs on the anon and authenticated roles.

Are foreign keys needed between schemas?

Yes, and it's perfectly supported by PostgreSQL. A table in collaborative can reference a table in privat. Foreign key constraints work between schemas without any limitation.

How to manage RPC functions that touch multiple schemas?

RPC functions (like get_consolidated_feed) can join tables from different schemas without issue. The best practice is to create the function in the schema of the main domain it serves, and use fully qualified names (privat.tasks) in queries.

Can you revert after migrating to multiple schemas?

Technically yes, with ALTER TABLE SET SCHEMA public. But in practice, once the frontend code references the schemas, reverting is costly. That's why it's better to make this decision early in the project.