Supabase Is Simple, but It Is Not Easy: Practical Patterns for Direct-to-Supabase Architectures

Hello, I’m 0xkohe.

I run multiple workloads on Supabase. Supabase is simple, but it is not necessarily easy. There are also relatively few collections of established practices, so if you build something casually through vibe coding, you can quickly produce a working application while also introducing security risks.

In this article, I use the term Direct-to-Supabase to describe an architecture in which the client accesses the Supabase Data API directly. I personally prefer SPAs, so unless otherwise stated, this article assumes an SPA-based frontend.

Using Supabase well requires a certain amount of computer science knowledge. If you are not confident in your ability to design the security model correctly, choosing a Backend for Frontend (BFF) is usually the safer option.

My preferred stack is a React SPA on Cloudflare combined with Supabase. It can be operated at a very low cost.

I believe that roughly 80% of a typical service consists of straightforward authorization, writes, and reads. Supabase provides a very fast way to implement that 80% through Direct-to-Supabase access from the client using PostgREST and Row Level Security (RLS).

When used well, this architecture significantly speeds up implementation and makes it easier to launch, modify, and improve a service. However, if you try to solve the remaining 20% using only the same mechanism, complexity grows rapidly. Extensibility declines, maintenance becomes harder, and the risk of accidentally leaking data increases.

This article presents my personal collection of practices for avoiding those problems.

Some of these practices are also useful when Supabase is used behind a BFF.

What Does “Simple, but Not Easy” Mean?

A useful analogy is recursion. Recursive code can be concise and structurally simple, but understanding and extending it can still be difficult.

RLS-based data protection looks simple at first. However, when you try to express too many business rules through RLS alone, complexity increases quickly and mistakes become more likely.

Core Principles

In a Direct-to-Supabase architecture, use PostgREST and RLS for straightforward CRUD operations and row-level authorization.

Do not attempt to express every complex use case entirely through RLS policy expressions.

Move logic involving multiple tables, state transitions, or external services into RPC functions, views, or Edge Functions.

Local Development Environment

https://supabase.com/docs/guides/local-development

Start by creating a local environment that you can destroy and recreate as often as necessary:

supabase init && supabase start

Seed Data

If you write SQL in ./supabase/seed.sql, Supabase will load that data during the initial startup and whenever you reset the database.

Declarative Schemas

I manage nearly the entire database structure through Declarative Schemas.

https://supabase.com/docs/guides/local-development/declarative-database-schemas

With Declarative Schemas, you describe the desired final state of the database under supabase/schemas/, then use supabase db diff to generate the migration required to reach that state.

Schema files are evaluated in lexicographical order, so name them in a way that makes dependencies explicit.

Example:

create table public.tenants (
  id uuid primary key default gen_random_uuid(),
  name text not null
    check (char_length(name) between 1 and 100),

  created_by uuid not null
    references auth.users(id)
    on delete restrict,

  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

Once you create files like this, Supabase compares the desired schema with the current database state and generates a migration file containing the required differences.

A simple directory structure looks like this:

supabase/
├── config.toml
├── schemas/
│   ├── 00_types.sql
│   ├── 10_tables.sql
│   ├── 20_functions.sql
│   └── 30_policies.sql
├── migrations/
└── seed.sql

I usually operate projects with a more detailed structure like the following:

supabase/schemas/
├── 00_setup/                        # Initial setup for extensions and shared functions
│   └── 001_set_updated_at.sql
├── 01_types/                        # Enums and custom types
│   ├── 004_gender_enum.sql
│   ├── 013_tenant_role_enum.sql
│   └── 015_application_status_enum.sql
├── 02_tables/                       # Table definitions
│   ├── 001_users.sql
│   ├── 007_tenants.sql
│   ├── 008_profiles.sql
│   ├── 020_message_rooms.sql
│   ├── 022_messages.sql
│   └── 023_job_applications.sql
├── 03_functions/                    # Functions and RPCs
│   ├── 001_handle_new_auth_user.sql
│   ├── 002_check_tenant_role.sql
│   ├── 003_check_is_admin.sql
│   └── 008_create_tenant_with_profile_and_staff.sql
├── 04_policies/                     # RLS policies
│   ├── 006_users.sql
│   ├── 007_profiles.sql
│   ├── 018_messages.sql
│   └── 020_job_applications.sql
├── 05_views/                        # Views
│   ├── 001_candidate_list_view.sql
│   ├── 002_profiles_for_tenant_view.sql
│   └── 004_user_messages_view.sql
├── 06_triggers/                     # Triggers
│   ├── 007_profiles.sql
│   └── 013_messages.sql
├── 07_indexes/                      # Indexes
│   ├── 001_profiles.sql
│   ├── 007_message_rooms.sql
│   └── 009_job_applications.sql
└── 08_storage_policies/             # Access control for Storage buckets
    ├── 001_user_icon_policies.sql
    ├── 002_user_resume_policies.sql
    └── 003_tenant_icon_policies.sql

When using nested directories, define schema_paths in supabase/config.toml to make the evaluation order explicit:

[db.migrations]
schema_paths = [
  "./schemas/00_setup/*.sql",
  "./schemas/01_types/*.sql",
  "./schemas/02_tables/*.sql",
  "./schemas/03_functions/*.sql",
  "./schemas/04_policies/*.sql",
  "./schemas/05_views/*.sql",
  "./schemas/06_triggers/*.sql",
  "./schemas/07_indexes/*.sql",
  "./schemas/08_storage_policies/*.sql",
]

When migrating an existing project, first dump the current database by following the official procedure:

supabase db dump > supabase/schemas/prod.sql

Then split prod.sql into files based on responsibility.

AI can be useful as an assistant during this process. For example, you can provide the directory structure above and use a prompt such as:

I want to migrate this project to Declarative Schemas. Review the existing migration files and create files under schemas/ until supabase db diff produces no remaining differences.

However, always review generated migrations manually.

Declarative Schemas do not fully track every possible database change. DML, view ownership and privileges, column privileges, schema privileges, and some RLS changes may still require manually written migrations.

Generating Types

https://supabase.com/docs/guides/api/rest/generating-types

Supabase can generate TypeScript types directly from the database. Import these types from both the frontend and Edge Functions.

supabase gen types typescript --local \
  > app/database.types.ts

supabase gen types typescript --local \
  > supabase/functions/_shared/database.types.ts

This allows the frontend and Edge Functions to understand the database structure through the type system.

It also makes compiler-in-the-loop workflows with AI much more effective because references to nonexistent tables, columns, or values can be detected as type errors.

In CI, verify that generated types are up to date:

supabase gen types typescript --local > app/database.types.ts
git diff --exit-code app/database.types.ts

Example:

import type { Database } from "~/database.types";

export const supabase = createClient<Database>(supabaseUrl, supabaseAnonKey);

const { data } = await supabase
  .from("tenant_staffs")
  .select("*")
  .single();

// Inferred type of data:
// {
//   id: string;
//   tenant_id: string;
//   name: string;
//   email: string;
//   role: "owner" | "member";
// } | null

Row Level Security

Do not attempt to control every authorization rule through RLS.

Simple RLS policies can cover roughly 70–80% of the system. If you try to force the remaining 20–30% into RLS as well, complexity rises sharply. The policies become harder to understand, unintended mistakes become more likely, and the risk of data leakage increases.

This is another example of the 80/20 rule.

At the same time, the fact that a large part of the system can be protected with simple RLS policies is one of Supabase’s greatest strengths.

For example, in a multi-tenant system, I limit RLS to rules such as:

• Users can operate on records containing their own user ID.
• Users can operate on records belonging to a tenant of which they are a member.
• Administrators can read the relevant records.

When requirements go beyond that level, consider views, RPC functions, or Edge Functions, as described later.

Use Supabase’s built-in auth.uid() function to identify the current user.

The examples below assume that private.current_tenant_id() and private.is_admin() are custom helper functions that query membership-related tables.

user_id = (select auth.uid())

tenant_id = (select private.current_tenant_id())

(select private.is_admin())

Where RLS Becomes Difficult

Typical examples of requirements that make RLS policies complex include:

• Even within the same tenant, users may only view documents for projects they participate in.
• Confidential documents may only be viewed by specific groups.
• A document must not be shown to users blocked by its creator.
• Temporarily invited users may only access a document before the invitation expires.
• Project managers may view everything, while regular members may only view published content.

Once write and delete permissions, additional requirements, and joins are added, readability deteriorates. Maintenance becomes difficult, and extending the policy becomes increasingly risky.

A policy such as the following can certainly be written, but it is not something I would want to keep expanding:

tenant_id = (select private.current_tenant_id())
and (
  created_by = (select auth.uid())
  or exists (
    select 1
    from public.project_members
    where project_members.project_id = documents.project_id
      and project_members.user_id = (select auth.uid())
  )
  or exists (
    select 1
    from public.group_members
    where group_members.group_id = documents.allowed_group_id
      and group_members.user_id = (select auth.uid())
  )
)
and not exists (
  select 1
  from public.blocked_users
  where blocked_users.blocked_user_id = (select auth.uid())
    and blocked_users.user_id = documents.created_by
)

The situation becomes significantly harder once conditions spanning joins and write authorization are added.

Suppose we introduce the following requirements:

• If a project has been archived, only managers may view its documents.
• Invited collaborators may view a document only while the invitation is valid and their role permits viewing.
• A confidential document may be viewed only if one of the user’s groups has sufficient clearance.

The SELECT policy now effectively contains joins inside a series of existence checks:

-- SELECT policy for documents
tenant_id = (select private.current_tenant_id())
and (
  (select private.is_admin())
  or (
    exists (
      select 1
      from public.project_members pm
      join public.projects p on p.id = pm.project_id
      where pm.project_id = documents.project_id
        and pm.user_id = (select auth.uid())
        and (p.archived_at is null or pm.role = 'manager')
    )
    or exists (
      select 1
      from public.invitations inv
      where inv.document_id = documents.id
        and inv.invited_user_id = (select auth.uid())
        and inv.expires_at > now()
        and inv.role in ('viewer', 'editor')
    )
  )
  and (
    documents.is_confidential = false
    or exists (
      select 1
      from public.group_members gm
      join public.group_clearances gc on gc.group_id = gm.group_id
      where gm.user_id = (select auth.uid())
        and gc.clearance_level >= documents.confidential_level
    )
  )
)
and not exists (
  select 1
  from public.blocked_users b
  where b.blocked_user_id = (select auth.uid())
    and b.user_id = documents.created_by
)

Writes are even more difficult.

Suppose the rule is: “A document may be edited only by its creator or by an invited collaborator with the editor role, and only while the project is not archived.”

An UPDATE policy must carefully distinguish between USING and WITH CHECK:

create policy "documents_update"
on public.documents
for update
to authenticated
using (
  tenant_id = (select private.current_tenant_id())
  and (
    (select private.is_admin())
    or exists (
      select 1
      from public.projects p
      where p.id = documents.project_id
        and p.archived_at is null
    )
  )
  and (
    created_by = (select auth.uid())
    or exists (
      select 1
      from public.invitations inv
      where inv.document_id = documents.id
        and inv.invited_user_id = (select auth.uid())
        and inv.role = 'editor'
        and inv.expires_at > now()
    )
  )
)
with check (
  tenant_id = (select private.current_tenant_id())
  and status in ('draft', 'published')
);

Even in this example, status in ('draft', 'published') only prevents values other than draft and published. It does not control state transitions such as moving a document from published back to draft.

If you also need to prevent changes to fields such as created_by, project_id, allowed_group_id, or confidential_level, you must compare the values before and after the update.

These rules become difficult to read when implemented entirely through RLS. Consider using triggers or RPC functions instead.

Limits of RLS in Direct-to-Supabase Architectures

• RLS controls rows, not individual columns.
  • Consider splitting tables or exposing a view.
• Multiple table updates need to be executed as a single transaction.
  • Use an RPC function.
• The operation involves external APIs, email, payments, webhooks, or other processing outside the database.
  • Use an Edge Function.

Choosing Between Views, RPC Functions, and Edge Functions

Views

Use views to create read-oriented representations of data.

Typical use cases include:

• Limiting the columns exposed to clients.
• Building a read model that joins multiple tables.
• Intentionally creating a read API that bypasses the RLS behavior of the underlying tables.

By default, PostgreSQL views execute with the privileges of the view owner. In other words, unless a view is configured with security_invoker = true, it does not simply inherit the RLS behavior of its underlying tables. Instead, data is read using the privileges of the view owner.

When you rely on this behavior, a view should not be treated as something that accidentally bypasses RLS. It should be designed as an intentional exception API.

For example, enable RLS on the underlying table and either avoid adding a direct SELECT policy or keep it extremely restrictive. Then define the permitted columns, joins, and filtering conditions in the view, and grant only the required privilege on that view.

alter table public.profiles enable row level security;

create view public.public_profiles
as
select
  id,
  display_name,
  avatar_url
from public.profiles
where user_id = (select auth.uid());

grant select on public.public_profiles to authenticated;

With this design, a client querying the underlying public.profiles table directly receives no rows if no SELECT RLS policy exists.

Meanwhile, public.public_profiles, operating as an owner-privileged view, returns only the explicitly exposed columns and rows.

If you want a view to inherit the underlying table’s RLS behavior, PostgreSQL 15 and later support security_invoker = true.

Personally, I generally do not use views for that case. If all I need is a join that continues to respect the underlying RLS policies, I usually find it simpler to construct the query directly with supabase.js. In that situation, creating and maintaining a dedicated view provides limited benefit.

RPC Functions

Use RPC functions for operations such as:

• Updating multiple tables in a single transaction.
• Restricting which columns may be changed instead of allowing arbitrary table updates.
• Enforcing valid state transitions.
• Checking multiple authorization conditions before writing data.
• Updating columns that clients should not be allowed to modify directly.

When write operations are centralized in RPC functions, consider revoking direct INSERT and UPDATE privileges on the underlying tables and granting clients permission to execute only the relevant RPC functions.

Edge Functions

Use Edge Functions for integrations and processing outside the database, including:

• Sending email.
• Processing payments.
• Handling webhooks.
• Calling external APIs.
• Performing operations that require secret keys or the Service Role Key.
• Running workflows that cannot be completed within a database transaction alone.

Operations using the Service Role Key can bypass RLS. Therefore, the Edge Function itself must authenticate and authorize the request, and the client must not be able to execute the same privileged operation directly.

Where practical, I use a framework such as Hono to define multiple routes inside a single function so that one deployed function can handle several related operations.

Testing RLS and RPC Functions

Once you have designed the security model, test it.

Supabase documents the use of pgTAP for testing tables, functions, and RLS policies.

At a minimum, test cases such as the following:

• A user cannot SELECT data belonging to another tenant.
• A user cannot INSERT a row using another tenant’s ID.
• A user cannot change tenant_id through an UPDATE.
• Anonymous users cannot access protected resources.
• Regular users cannot execute administrator-only RPC functions.
• Views do not expose private columns.
• Expired invitations do not grant access.

Branches

https://supabase.com/docs/guides/deployment/branching

Supabase Branches require a paid plan, but they allow you to create an environment for each pull request.

Because preview branches are deleted after merging, I use the main branch as production and keep a persistent develop branch as staging.

Official Supabase Skills

https://supabase.com/docs/guides/ai-tools/ai-skills

It is worth installing Supabase’s official Skills.

They can help review RLS performance and check implementations against recommended practices.

To be honest, I do not use MCP very much. If database types are generated from the schema, AI tools can already understand most of the database structure through those generated types.

Other Recommendations

BI Tools

If you need a BI tool, I recommend connecting Google Looker Studio, formerly known as Google Data Studio. It can be used for analytics at no cost.

https://cloud.google.com/data-studio?hl=en

Skills

I have also turned the practices in this article into a Skill.

You can install it with:

gh skill install 0xkohe/supabase-skills design-direct-to-supabase

https://github.com/0xkohe/supabase-skills

That’s all.

Comments

[Mads Hansen]

This is a very practical distinction between “simple” and “easy”. The part I’d underline is testing RLS and RPC as product behavior, not just database behavior.

In direct-to-Supabase apps, a policy bug is effectively an API bug. I like having tests named after user stories rather than tables: “member cannot read another tenant’s document”, “expired invitation cannot update”, and so on.

It forces the test suite to cover the business boundary, and it makes future policy changes much less scary.