Supabase Common Workflows

Quick reference cookbook for common Supabase operations. This skill provides step-by-step recipes for typical database tasks.

Core Workflows

1. Create Table with RLS (Complete Recipe)

sql

-- Step 1: Create the table
CREATE TABLE public.table_name (
    id TEXT PRIMARY KEY DEFAULT generate_id('prefix'),
    organization_id TEXT NOT NULL REFERENCES public.organizations(id) ON DELETE CASCADE,
    name TEXT NOT NULL,
    description TEXT,
    created_at TIMESTAMPTZ DEFAULT now(),
    updated_at TIMESTAMPTZ DEFAULT now()
);

-- Step 2: Create indexes
CREATE INDEX idx_table_organization_id ON public.table_name(organization_id);

-- Step 3: Enable RLS
ALTER TABLE public.table_name ENABLE ROW LEVEL SECURITY;

-- Step 4: Create RLS policies
CREATE POLICY "Organization members can view items"
    ON public.table_name FOR SELECT
    USING (
        EXISTS (
            SELECT 1 FROM public.organization_members
            WHERE organization_members.organization_id = table_name.organization_id
            AND organization_members.user_id = (SELECT auth.uid())
        )
    );

CREATE POLICY "Organization members can create items"
    ON public.table_name FOR INSERT
    WITH CHECK (
        EXISTS (
            SELECT 1 FROM public.organization_members
            WHERE organization_members.organization_id = table_name.organization_id
            AND organization_members.user_id = (SELECT auth.uid())
        )
    );

CREATE POLICY "Organization members can update items"
    ON public.table_name FOR UPDATE
    USING (
        EXISTS (
            SELECT 1 FROM public.organization_members
            WHERE organization_members.organization_id = table_name.organization_id
            AND organization_members.user_id = (SELECT auth.uid())
        )
    );

2. Add Column to Existing Table

sql

-- Add column with default value
ALTER TABLE public.table_name ADD COLUMN new_column TEXT DEFAULT 'default_value';

-- Add NOT NULL column (requires default or backfill)
ALTER TABLE public.table_name ADD COLUMN required_column TEXT NOT NULL DEFAULT 'value';

-- Add column with constraint
ALTER TABLE public.table_name ADD COLUMN status TEXT CHECK (status IN ('active', 'inactive', 'pending'));

-- Add foreign key column
ALTER TABLE public.table_name ADD COLUMN reference_id TEXT REFERENCES public.other_table(id) ON DELETE CASCADE;

-- Create index for the new column if needed
CREATE INDEX idx_table_new_column ON public.table_name(new_column);

3. Add Table to Realtime Events System

When creating a new organization-scoped table, enable realtime notifications:

sql

-- Step 1: Create the table (standard migration)
CREATE TABLE public.my_new_table (
    id TEXT PRIMARY KEY DEFAULT generate_id('prefix'),
    organization_id TEXT NOT NULL REFERENCES public.organizations(id) ON DELETE CASCADE
    -- OR reference a parent table that has organization_id
);

-- Step 2: Update get_organization_id_for_change() function
CREATE OR REPLACE FUNCTION public.get_organization_id_for_change()
RETURNS TEXT AS $$
DECLARE
    org_id TEXT;
    record_data JSONB;
BEGIN
    IF TG_OP = 'DELETE' THEN
        record_data := to_jsonb(OLD);
    ELSE
        record_data := to_jsonb(NEW);
    END IF;

    CASE TG_TABLE_NAME
        -- Add your new table:
        WHEN 'my_new_table' THEN
            -- Option A: Table has organization_id column
            org_id := record_data->>'organization_id';
            -- Option B: Lookup via parent table
            -- SELECT p.organization_id INTO org_id
            -- FROM public.parent_table p
            -- WHERE p.id = (record_data->>'parent_id');
    END CASE;

    RETURN org_id;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER SET search_path = public;

-- Step 3: Attach trigger to table
DROP TRIGGER IF EXISTS trigger_notify_org_change ON public.my_new_table;
CREATE TRIGGER trigger_notify_org_change
    AFTER INSERT OR UPDATE OR DELETE ON public.my_new_table
    FOR EACH ROW
    EXECUTE FUNCTION public.notify_organization_of_table_change();

Frontend: No changes needed! Automatically invalidates queries containing table name.

4. Hard Delete Pattern

⚠️ This application uses HARD DELETES ONLY - No soft delete support.

sql

-- Delete records permanently
await supabase.from('table_name').delete().eq('id', 'item_id');

Why hard deletes:

•Simpler schema (no deleted_at columns)
•No query filters needed
•GDPR/data deletion compliance
•Reduced storage overhead

If you need audit trail: Use PostgreSQL triggers to log deletions to separate audit table.

supabase-common-workflows