Database Design with Supabase RLS

CREATE TABLE user_profiles (

id UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,

email TEXT,

full_name TEXT,

first_name TEXT,

middle_name TEXT,

last_name TEXT,

pronouns TEXT,

gender TEXT,

occupation TEXT,

address TEXT,

bio TEXT,

phone TEXT,

website TEXT,

avatar_url TEXT,

avatar_shape TEXT NOT NULL DEFAULT 'circle',

roundness TEXT NOT NULL DEFAULT 'default',

theme TEXT NOT NULL DEFAULT 'system',

newsletter_subscribed BOOLEAN NOT NULL DEFAULT false,

two_factor_enabled BOOLEAN NOT NULL DEFAULT false,

created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),

updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()

);

ALTER TABLE user_profiles ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Users can view their own profile"

ON user_profiles FOR SELECT

USING (auth.uid() = id);

CREATE POLICY "Users can update their own profile"

ON user_profiles FOR UPDATE

USING (auth.uid() = id);

CREATE TABLE app_entitlements (

id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

user_id UUID REFERENCES auth.users(id) ON DELETE SET NULL,

email TEXT NOT NULL,

product_id TEXT NOT NULL,

gumroad_sale_id TEXT UNIQUE NOT NULL,

status TEXT NOT NULL DEFAULT 'active', -- active, revoked, refunded

metadata JSONB DEFAULT '{}'::jsonb,

created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),

updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()

);

ALTER TABLE app_entitlements ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Users can view their own entitlements by user_id"

ON app_entitlements FOR SELECT

USING (auth.uid() = user_id);

CREATE POLICY "Users can view their own entitlements by email"

ON app_entitlements FOR SELECT

USING (auth.jwt() ->> 'email' = email);

-- Example: schemacraft_generations

CREATE TABLE schemacraft_generations (

id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

user_id UUID REFERENCES auth.users(id),

target_input TEXT NOT NULL,

results JSONB NOT NULL,

created_at TIMESTAMPTZ DEFAULT NOW()

);

ALTER TABLE schemacraft_generations ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Users can view own generations"

ON schemacraft_generations FOR SELECT

USING (auth.uid() = user_id);

CREATE POLICY "Users can insert own generations"

ON schemacraft_generations FOR INSERT

WITH CHECK (auth.uid() = user_id);

ALTER TABLE my_table ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Own data only"

ON my_table FOR ALL

USING (auth.uid() = user_id);

-- Read

CREATE POLICY "Read own"

ON my_table FOR SELECT

USING (auth.uid() = user_id);

-- Write

CREATE POLICY "Insert own"

ON my_table FOR INSERT

WITH CHECK (auth.uid() = user_id);

-- Update

CREATE POLICY "Update own"

ON my_table FOR UPDATE

USING (auth.uid() = user_id);

const supabase = createClient(url, serviceKey, {

auth: { persistSession: false, autoRefreshToken: false }

});

-- supabase/migrations/20261115000001_create_generations.sql

CREATE TABLE schemacraft_generations (...);