Supabase Database Migrations

Toolkit for creating and managing Supabase database migrations.

Helper Scripts Available (uv scripts - no install needed):

• scripts/new_migration.py - Create migration file with proper naming
• scripts/lint_migration.py - Validate migration against conventions

uv run scripts/new_migration.py --help
uv run scripts/lint_migration.py --help

Naming Conventions

Object Prefixes (REQUIRED)

Functions MUST be versioned: fn_calculate_total_v1, fn_calculate_total_v2

Field Suffixes

No suffix: id, name, email, created_at, updated_at, deleted_at

Rules

• MUST use lowercase_snake_case
• Tables MUST use plural forms (tb_users not tb_user)

Decision Tree

Task → What type of change?
    ├─ New table → uv run scripts/new_migration.py "add_users_table"
    ├─ Alter table → uv run scripts/new_migration.py "add_avatar_to_users" --type alter
    ├─ New function → uv run scripts/new_migration.py "add_calc_fn" --type function
    └─ Before commit → uv run scripts/lint_migration.py migrations/*.sql

File Naming

supabase/migrations/YYYYMMDDHHMMSS_description.sql

Migration Template

-- Migration: [Description]
BEGIN;

-- 1. Types/Enums
DO $$ BEGIN CREATE TYPE en_status AS ENUM ('active', 'inactive');
EXCEPTION WHEN duplicate_object THEN NULL; END $$;

-- 2. Tables
CREATE TABLE IF NOT EXISTS tb_examples (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    status_cd en_status NOT NULL DEFAULT 'active',
    created_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- 3. Comments
COMMENT ON TABLE tb_examples IS 'Module: Description';

-- 4. Indexes
CREATE INDEX IF NOT EXISTS idx_examples_status ON tb_examples(status_cd);

-- 5. Triggers
DROP TRIGGER IF EXISTS tgr_update_examples_timestamp ON tb_examples;
CREATE TRIGGER tgr_update_examples_timestamp
    BEFORE UPDATE ON tb_examples FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

-- 6. RLS (use SELECT wrapper for auth functions - see below)
ALTER TABLE tb_examples ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS pc_examples_select ON tb_examples;
CREATE POLICY pc_examples_select ON tb_examples FOR SELECT
    USING (user_uid = (SELECT auth.uid()));

COMMIT;

Supabase Security

Views - MUST use security_invoker

CREATE VIEW v_active_users WITH (security_invoker) AS
SELECT * FROM tb_users WHERE deleted_at IS NULL;

SECURITY DEFINER views must be converted to functions:

Views with SECURITY DEFINER bypass RLS of the querying user. Convert to a function with explicit tenant/auth checks.

-- BAD: View with SECURITY DEFINER bypasses caller's RLS
CREATE VIEW v_line_responders WITH (security_definer) AS
SELECT tenant_id, ... FROM tb_line_events GROUP BY ...;

-- GOOD: Function with explicit auth check
CREATE OR REPLACE FUNCTION fn_get_line_responders_v1()
RETURNS TABLE (
    tenant_id uuid,
    responder_user_id text,
    message_count bigint
) LANGUAGE sql STABLE SECURITY DEFINER
SET search_path = extensions, public, pg_temp
AS $$
    SELECT tenant_id, event_payload->>'user_id', COUNT(*)
    FROM public.tb_line_events
    WHERE tenant_id = ((SELECT auth.jwt()) ->> 'tenant_id')::uuid
    GROUP BY tenant_id, event_payload->>'user_id';
$$;

REVOKE ALL ON FUNCTION fn_get_line_responders_v1() FROM PUBLIC;
GRANT EXECUTE ON FUNCTION fn_get_line_responders_v1() TO authenticated;

Functions - MUST set search_path

Mutable search_path allows object hijacking. Always set explicitly.

-- New function
CREATE OR REPLACE FUNCTION fn_example_v1()
RETURNS void AS $$ BEGIN END; $$
LANGUAGE plpgsql SECURITY DEFINER
SET search_path = extensions, public, pg_temp;

-- Fix existing function
ALTER FUNCTION fn_find_nearby_facilities(/* arg types */)
SET search_path = extensions, public, pg_temp;

Extensions - MUST use extensions schema

CREATE EXTENSION IF NOT EXISTS pg_trgm SCHEMA extensions;

RLS Performance - MUST wrap auth functions in SELECT

-- Wrap auth functions to avoid per-row re-evaluation
USING (user_uid = (SELECT auth.uid()))
USING (org_uid = ((SELECT auth.jwt()) ->> 'org_id')::uuid)

RLS Performance - Consolidate multiple permissive policies

Same role + same action = consolidate with OR (don't create multiple policies).

-- Single policy with OR instead of multiple permissive policies
CREATE POLICY pc_records_select ON tb_records FOR SELECT
TO authenticated
USING (
    ((SELECT auth.jwt()) -> 'app_metadata' ->> 'role') = 'admin'
    OR user_uid = (SELECT auth.uid())
);

Multi-tenant pattern:

CREATE POLICY pc_registrations_select ON tb_registrations FOR SELECT
TO authenticated
USING (
    tenant_id = (((SELECT auth.jwt()) -> 'app_metadata' ->> 'tenant_id')::smallint)
    AND (
        ((SELECT auth.jwt()) -> 'app_metadata' ->> 'role') = ANY (ARRAY['staff', 'admin'])
        OR user_uid = (SELECT auth.uid())
    )
);

Running Migrations

supabase db reset    # Reset and apply all
supabase db push     # Deploy to remote

References