Why does Storyie use three separate layers instead of just checking permissions in application code?

Application-layer permission checks are easy to forget. If a Server Action omits a `WHERE user_id = ?` clause, another user's data leaks — and in a fast-moving codebase, that slip happens eventually. RLS at the database layer is structural: it fires on every query regardless of which code path reaches it. We treat Layer 1 (Supabase Auth) as the identity layer, Layer 2 (RLS) as the data-access layer, and Layer 3 (RBAC via `tenant_members.role`) as the permission layer. Each has a clear responsibility, and each backs up the one above it. The result is that the database acts as the last line of defense even when application logic is incomplete.

How do cookie-based auth (web) and Bearer-token auth (mobile) work with the same RLS policies?

Both paths produce the same Supabase JWT. The difference is only in transport and storage: the Next.js middleware attaches a session from an HTTP-only cookie, while the Expo app sends the token from SecureStore as a Bearer header. Once the Supabase client on the server side validates either, it sets `auth.uid()` in the PostgreSQL session context. Every RLS policy evaluates against that `uid` — it never sees how the token arrived. This means our RLS policies have no platform-specific branches; they're identical for web and mobile.

Why does Storyie not store the user role in the JWT custom claims?

JWTs have a lifetime — typically an hour in Supabase. If we stored `role = "admin"` in the token and then demoted that user to `member`, they would retain admin access until the token expired. Instead, our RLS policies JOIN against `tenant_members` on every query. The role read is always the live value in the database, so a permission change takes effect immediately without requiring the user to log out and back in. The query overhead is real but acceptable; we mitigate it by wrapping `auth.uid()` in a subquery so PostgreSQL treats it as a constant for the duration of the statement.

What is the `(select auth.uid())` subquery pattern and why does it matter?

PostgreSQL may re-evaluate a function call for each row it scans. Writing `USING (id = auth.uid())` in a policy means the planner can call `auth.uid()` once per row. Wrapping it as `USING (id = (select auth.uid()))` lets the planner treat it as a scalar subquery — effectively a constant for the whole statement. On a small table the difference is invisible. On a table with tens of thousands of rows and a policy that touches every row, this optimization meaningfully reduces the number of function evaluations. We apply this pattern consistently across all our RLS policies.

How does Drizzle ORM fit into an RLS workflow?

Drizzle 0.36+ supports `pgPolicy()` as a table option, so we define the table columns and the RLS policies in the same TypeScript file. When a column is renamed or a table is restructured, the policy that references that column is right there in the same diff — it is hard to forget. By contrast, storing SQL migration files separately from the schema definition means policy SQL can drift out of sync with the schema when someone renames a column but forgets to update the policy text. Co-location does not fully prevent that bug, but it puts the relevant code on the same screen.

When should a solo developer or small team skip RBAC and use a simpler approach?

For an MVP with a single user type, a simple `is_admin boolean` column on the user record is perfectly fine. RBAC adds a join table, a role enum, and more complex RLS policies — overhead that is not worth it until you actually need to distinguish two or more non-overlapping permission sets. We added RBAC to Storyie early because we knew team diaries (multi-member tenants) were on the roadmap, and retrofitting role-based access onto an existing RLS policy set is genuinely painful work. If team features are not on your near-term roadmap, ship with a flag and migrate later.

Auth and authorization in Storyie: the full picture with Supabase Auth, RLS, and RBAC

Storyie has accumulated a handful of separate write-ups on individual pieces of its security model: the authentication flow across Next.js and Expo, the seven RLS policy patterns we apply to every table, how multi-tenant isolation works at the database layer, and the RBAC vs. ABAC decision for role design. Each article explains one piece well. What none of them shows is how the pieces fit together.

This post draws a single map.

TL;DR

Security in Storyie is three cooperating layers: Supabase Auth (identity), PostgreSQL RLS (row access), and a role column in tenant_members (permission boundaries).
Each layer depends on the one below it. RLS requires a valid auth.uid(), which Supabase Auth provides. RBAC policies JOIN against tenant_members, which is itself protected by RLS.
Application code does not need to explicitly enforce tenant isolation or role checks. Supabase's database client carries the JWT into the PostgreSQL session, and the policies fire automatically.
The (select auth.uid()) subquery pattern is a small but consistent optimization that keeps RLS performant as tables grow.
We co-locate RLS policy definitions with Drizzle table schemas to prevent them from drifting apart on refactors.

| Layer | Mechanism | Answers |
| ----- | --------- | ------- |
| 1. Authentication | Supabase Auth → JWT | Who are you? |
| 2. Data access control | PostgreSQL RLS | Can you read or write this row? |
| 3. Permission boundaries | tenant_members.role (RBAC) | What operations are you allowed to perform? |

The three-layer model

┌──────────────────────────────────────────────────────┐
│  Layer 1: Authentication                              │
│  Supabase Auth — establishes who the user is         │
│  OAuth (Google / Apple) → JWT → session management   │
├──────────────────────────────────────────────────────┤
│  Layer 2: Row-level access control (RLS)             │
│  PostgreSQL RLS — determines whether a row is visible│
│  auth.uid()-based policies on every table            │
├──────────────────────────────────────────────────────┤
│  Layer 3: Permission boundaries (RBAC)               │
│  tenant_members.role — controls what you can do      │
│  owner > admin > member > viewer                     │
└──────────────────────────────────────────────────────┘

The critical property of this stack is that lower layers depend on upper layers. RLS cannot evaluate without a valid auth.uid() — which means authentication must succeed first. RBAC policies JOIN tenant_members to check roles, and tenant_members is itself protected by RLS — which means a user cannot elevate their own privileges by manipulating the role table directly.

Understanding that dependency chain makes "what enforces what" considerably clearer.

Layer 1: Authentication — establishing identity

Two platforms, one JWT

Storyie ships on Next.js (web) and Expo (mobile). The authentication paths differ per platform, but both produce the same Supabase JWT, which is what matters for everything downstream.

| Platform | Auth method | Token storage |
| -------- | ----------- | ------------- |
| Web (Next.js) | OAuth redirect → cookie exchange | HTTP-only cookie |
| Mobile (Expo) | Native SDK → signInWithIdToken | SecureStore |

The design point: web is cookie-based, mobile is Bearer-token-based. Our Next.js API routes accept both. The Supabase client is initialized differently per platform — using @supabase/ssr on the web side and the standard JS client on mobile — but after initialization, all downstream logic is identical. The JWT lands in the PostgreSQL session context as auth.uid() regardless of how it arrived.

Middleware as the authentication gate

Next.js Middleware handles session refresh and route protection. If the JWT is absent or expired, the request is redirected to /login before it reaches any route handler. The authentication layer's only job is to establish a valid auth.uid() for the request. Everything about what that user can actually access is deferred to the layers below.

Layer 2: RLS — determining row access

Why enforce this at the database layer at all

An application-layer WHERE user_id = ? clause can be omitted. It can be omitted accidentally during a refactor, omitted intentionally when someone writes a quick ad-hoc query during an incident, or omitted by a new team member who does not know that a particular table needs it. Every one of those paths leaks data.

RLS is structural. Enabling it on a table means every query — regardless of origin — goes through the policy evaluation. There is no code path that bypasses it short of using the service_role key, which is a deliberate, auditable choice.

In Storyie, every table has RLS enabled. We run seven policy patterns across them:

| Pattern | Example table | Policy shape |
| ------- | ------------- | ------------ |
| Own rows only | private_profiles | id = (select auth.uid()) |
| Tenant membership | tenant_feature_usage | Subquery JOIN on tenant_members |
| Role-based write control | tenant_members | role IN ('owner', 'admin') for mutating operations |
| Public read, owner write | public_profiles | SELECT true, mutate = (select auth.uid()) |
| Server-only tables | Internal log tables | RLS blocks all client access; service_role writes server-side |

The `(select auth.uid())` subquery

Every RLS policy in Storyie wraps auth.uid() in a subquery:

-- Potentially re-evaluated per row
USING (id = auth.uid())

-- Treated as a constant for the whole statement
USING (id = (select auth.uid()))

PostgreSQL's query planner may call a function once per row when it appears inline. Wrapping it as a scalar subquery signals to the planner that the result is a constant for the duration of the statement. On small tables the difference is unmeasurable. On a diary table with tens of thousands of entries and a membership subquery JOIN, it adds up.

Layer 3: RBAC — defining what you can do

The role hierarchy

export const memberRoleEnum = pgEnum("member_role", [
  "owner",   // Billing, all member operations
  "admin",   // Member management
  "member",  // Read and write diary entries
  "viewer",  // Read-only
]);

We chose RBAC over both a simple boolean flag and ABAC (attribute-based access control). The boolean flag is fine until you need three distinct permission levels — at that point the flag becomes two flags, then three, and the logic disperses through the codebase. ABAC handles arbitrarily fine-grained permissions but requires infrastructure complexity that is not warranted for a product at this stage. RBAC hits the right point on that curve for us: a role table, a role JOIN in relevant policies, and a clear expansion path if we need additional roles later.

We added RBAC at the start rather than retrofitting it later because adding tenant_id to an existing schema and rewriting all the RLS policies for it is significantly more disruptive than having them in place from the beginning.

Where RLS and RBAC meet

The payoff of RBAC inside Supabase is that you can reference the role column directly inside an RLS policy:

-- Only owners and admins can add new members
WITH CHECK (
  tenant_id IN (
    SELECT tenant_id FROM tenant_members
    WHERE user_id = (SELECT auth.uid())
    AND role IN ('owner', 'admin')
  )
)

No application code needs to check the role before calling INSERT. If the caller is a viewer or member, the INSERT fails at the database layer. Application code can still check roles to provide better UX (disabling buttons, showing appropriate empty states), but the database does not rely on those checks for correctness.

How a diary write flows through all three layers

A concrete example makes the layer interactions tangible. When a user saves a new diary entry:

1. User submits the diary form

2. [Layer 1] Next.js Middleware validates the JWT
   → auth.uid() is established in the request context

3. [Layer 2] Supabase client executes INSERT on the diaries table
   → RLS SELECT policy confirms the user belongs to the target tenant
     by joining tenant_members on auth.uid()

4. [Layer 3] RLS INSERT policy checks tenant_members.role
   → "viewer" → policy rejects, INSERT fails with a permission error
   → "member" or above → policy passes, INSERT proceeds

5. Row is created (or the database returns a policy violation)

The application Server Action does not contain a WHERE tenant_id = ? or an IF role === 'viewer' guard. It issues a plain INSERT through the Supabase client. The JWT in the session context carries auth.uid(), and the policies do the rest.

Keeping RLS policies with the schema using Drizzle

Storyie uses Drizzle ORM's pgPolicy() to co-locate RLS policies with table definitions. A policy that references a column lives in the same file as the column it references:

export const tenantMembers = pgTable(
  "tenant_members",
  {
    tenantId: uuid("tenant_id").notNull().references(() => tenants.id),
    userId: uuid("user_id").notNull().references(() => authUsers.id),
    role: memberRoleEnum("role").notNull().default("member"),
    // ...
  },
  (table) => ({
    selectPolicy: pgPolicy("members_can_view_same_tenant", {
      for: "select",
      using: sql`${table.tenantId} IN (
        SELECT tenant_id FROM tenant_members
        WHERE user_id = (SELECT auth.uid())
      )`,
    }),
    // insert, update, delete policies defined here too
  })
);

When a column is renamed during a refactor, the policy referencing it is visible in the same diff. Keeping policy SQL in separate migration files makes it easy for column renames to go through without updating the policy text — and a policy that references a renamed column silently fails at runtime rather than at migration time.

Common failure modes

Data invisible after switching from service_role to anon key

During development it is tempting to use the service_role key everywhere, since it bypasses RLS entirely. When the code is later tested with the anon key (the one actual clients use), data that was visible before disappears. This is RLS working correctly — but the surprise can be disorienting if you are not expecting it. Test with the anon key early.

Admin UI needs to query across tenants

The admin panel needs to see all tenants' data. The right answer is to use service_role on the server side and enforce authorization in the application layer (Middleware role checks, not RLS). In Storyie, the admin dashboard route is protected at the Middleware level so that only users with owner or admin status can reach it. RLS is bypassed intentionally for those requests, and the application layer compensates.

Role changes should take effect immediately

If roles were stored as custom claims in the JWT, a demotion would not take effect until the token expired (typically one hour later). We do not store roles in the token. RLS policies JOIN tenant_members directly on every query. A role update in the database takes effect on the next request, with no need for the user to log out.

When is this level of complexity justified?

Honestly, for an MVP: it is not. An auth.uid() RLS policy on every table is sufficient to enforce basic data isolation, and that is all you actually need at launch.

The phased view:

| Phase | Do this | Skip this |
| ----- | ------- | --------- |
| MVP | Supabase Auth + RLS on every table | RBAC, multi-tenant isolation |
| Adding team features | tenant_members table + RBAC integrated into RLS | ABAC, fine-grained attribute policies |
| Enterprise scale | Audit logging, ABAC evaluation | (at this point it is no longer a solo project) |

The reason we built multi-tenant isolation and RBAC into Storyie from the start is that adding tenant_id to an already-live table, migrating existing data, and rewriting a full set of RLS policies is the kind of migration that burns a weekend and carries real risk of introducing gaps. Adding it from day one was a day of upfront design. The tradeoff was worth it for us; it may not be for every project.

Database schema design at Storyie — how we structure tables and migrations with Drizzle ORM
Multi-tenant subdomain routing — tenant isolation at the routing layer
Building a Monorepo with pnpm and TypeScript — workspace conventions that keep the web and mobile clients sharing the same database package

Try Storyie

Storyie is available on the web and on the iOS app. Your diary is private by default — the three-layer security model described here is what keeps it that way.