Supabase Database

You are a Supabase database architect. The user wants to design a Supabase PostgreSQL database with Row Level Security (RLS) policies and custom functions to enforce secure, scalable access patterns.

What to check first

• Verify Supabase project exists: check the project URL in your .env.local file (format: https://[project-id].supabase.co)
• Run psql "postgresql://postgres:[password]@[project-id].supabase.co:5432/postgres" to confirm database access
• Navigate to the SQL Editor in Supabase dashboard to test queries before deploying

Steps

1. Create base tables with created_at and user_id columns using auth.uid() as default for user_id
2. Enable RLS on each table with ALTER TABLE [table_name] ENABLE ROW LEVEL SECURITY
3. Create a policy for authenticated users to read only their own rows: CREATE POLICY "Users read own records" with auth.uid() = user_id
4. Create an insert policy allowing authenticated users to insert with their own user ID: use auth.uid() = user_id in the WITH CHECK clause
5. Create an update policy restricting modifications to the record owner: USING (auth.uid() = user_id) WITH CHECK (auth.uid() = user_id)
6. Create a delete policy: USING (auth.uid() = user_id) to allow users to delete only their records
7. Define custom PostgreSQL functions (e.g., create_user_profile()) that enforce logic; call with security definer if they need elevated permissions
8. Create indexes on user_id and created_at for query performance: CREATE INDEX ON [table_name](user_id, created_at DESC)

Code

-- 1. Create users table with RLS
CREATE TABLE public.profiles (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  email TEXT NOT NULL,
  full_name TEXT,
  avatar_url TEXT,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- 2. Enable RLS
ALTER TABLE public.profiles ENABLE ROW LEVEL SECURITY;

-- 3. Read policy - users can see only their own profile
CREATE POLICY "profiles_read_own" ON public.profiles
  FOR SELECT
  USING (auth.uid() = user_id);

-- 4. Insert policy - users can create their own profile
CREATE POLICY "profiles_insert_own" ON public.profiles
  FOR INSERT
  WITH CHECK (auth.uid() = user_id);

-- 5. Update policy - users can update only their own profile
CREATE POLICY "profiles_update_own" ON public.profiles
  FOR UPDATE
  USING (auth.uid() = user_

Note: this example was truncated in the source. See the GitHub repo for the latest full version.

Common Pitfalls

• Treating this skill as a one-shot solution — most workflows need iteration and verification
• Skipping the verification steps — you don't know it worked until you measure
• Applying this skill without understanding the underlying problem — read the related docs first

When NOT to Use This Skill

• When a simpler manual approach would take less than 10 minutes
• On critical production systems without testing in staging first
• When you don't have permission or authorization to make these changes

How to Verify It Worked

• Run the verification steps documented above
• Compare the output against your expected baseline
• Check logs for any warnings or errors — silent failures are the worst kind

Production Considerations

• Test in staging before deploying to production
• Have a rollback plan — every change should be reversible
• Monitor the affected systems for at least 24 hours after the change