How to Scaffold Supabase Auth & Row Level Security Using AI Prompt Architect

Supabase gives you a Postgres database with built-in authentication and Row Level Security (RLS). But getting RLS policies right is notoriously tricky — one wrong policy and your entire database is either wide open or completely locked down. This guide shows you how to use AI Prompt Architect to generate battle-tested auth + RLS configurations from day one.

The RLS Problem

When you ask an AI to "add RLS to my Supabase table", you typically get:

-- ❌ The AI default — too permissive
ALTER TABLE posts ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Users can do everything" ON posts
  FOR ALL USING (auth.uid() = user_id);

This has multiple issues:

• No INSERT validation — users can set user_id to anyone else's ID
• No column restrictions — users can modify admin-only fields like is_featured
• No role differentiation — admins and regular users have identical access
• No service role bypass — background jobs can't function

Step 1: Configure Your Auth Model

# Supabase Auth configuration
auth_providers:
  - Email/Password (with email confirmation)
  - Google OAuth
  - GitHub OAuth

user_roles:
  - user (default)
  - pro (paid tier)
  - admin (full access)

role_storage: app_metadata.role (set via Edge Function, not client)

rls_principles:
  - Enable RLS on EVERY table — no exceptions
  - Separate SELECT, INSERT, UPDATE, DELETE policies
  - Validate user_id on INSERT with auth.uid()
  - Prevent role escalation — users cannot modify their own role
  - Service role bypasses RLS for background jobs

Step 2: Generate Auth-Aware Schema

The Master Prompt produces a schema where auth is baked into the table design:

-- Users profile table (extends auth.users)
CREATE TABLE public.profiles (
  id UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
  display_name TEXT NOT NULL CHECK (char_length(display_name) BETWEEN 1 AND 100),
  avatar_url TEXT,
  role TEXT NOT NULL DEFAULT 'user' CHECK (role IN ('user', 'pro', 'admin')),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

ALTER TABLE public.profiles ENABLE ROW LEVEL SECURITY;

-- SELECT: Users see own profile, admins see all
CREATE POLICY "profiles_select" ON public.profiles
  FOR SELECT USING (
    auth.uid() = id
    OR (SELECT role FROM public.profiles WHERE id = auth.uid()) = 'admin'
  );

-- UPDATE: Users update own profile, cannot change role
CREATE POLICY "profiles_update" ON public.profiles
  FOR UPDATE USING (auth.uid() = id)
  WITH CHECK (
    auth.uid() = id
    AND role = (SELECT role FROM public.profiles WHERE id = auth.uid())
  );

-- DELETE: Nobody deletes profiles via client
CREATE POLICY "profiles_delete" ON public.profiles
  FOR DELETE USING (false);

Step 3: Multi-Tenant RLS

For SaaS apps, the Master Prompt generates organisation-scoped policies:

-- Projects scoped to organisation
CREATE TABLE public.projects (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  org_id UUID NOT NULL REFERENCES public.organisations(id),
  name TEXT NOT NULL CHECK (char_length(name) BETWEEN 1 AND 200),
  created_by UUID NOT NULL REFERENCES auth.users(id),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

ALTER TABLE public.projects ENABLE ROW LEVEL SECURITY;

-- Helper function: check org membership
CREATE OR REPLACE FUNCTION public.is_org_member(org UUID)
RETURNS BOOLEAN AS $$
  SELECT EXISTS (
    SELECT 1 FROM public.org_members
    WHERE org_id = org AND user_id = auth.uid()
  );
$$ LANGUAGE sql SECURITY DEFINER;

-- SELECT: Org members only
CREATE POLICY "projects_select" ON public.projects
  FOR SELECT USING (public.is_org_member(org_id));

-- INSERT: Org members, must set created_by to own ID
CREATE POLICY "projects_insert" ON public.projects
  FOR INSERT WITH CHECK (
    public.is_org_member(org_id)
    AND created_by = auth.uid()
  );

Key Takeaways

1. Separate policies per operation — SELECT, INSERT, UPDATE, and DELETE each get their own policy
2. Validate ownership on INSERT — always check auth.uid() matches the user_id column
3. Prevent privilege escalation — use WITH CHECK to block role changes
4. Use helper functions — SECURITY DEFINER functions simplify complex membership checks
5. Test with Supabase CLI — use supabase test db to validate policies locally