Managing Supabase Schema Migrations

Managing Supabase Schema Migrations

Safe workflow for database schema changes with automatic type generation and RLS validation.

When to Use This Skill

• Adding new tables
• Modifying existing columns
• Creating indexes
• Adding or updating RLS policies
• Any database schema changes

Migration Workflow

Follow these steps for safe migrations:

Step 1: Create Migration File

# Create a new migration with descriptive name
supabase migration new add_recipe_tags_table

# Or with bash helper
bash .claude/skills/supabase-migrations/scripts/create-migration.sh "add_recipe_tags_table"

This creates: supabase/migrations/[timestamp]_add_recipe_tags_table.sql

Step 2: Write Migration SQL

Edit the generated file with your schema changes.

Example: Adding a new table

-- Create recipe_tags table
CREATE TABLE IF NOT EXISTS public.recipe_tags (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  recipe_id UUID NOT NULL REFERENCES public.saved_recipes(id) ON DELETE CASCADE,
  tag VARCHAR(50) NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW(),

  -- Ensure unique tag per recipe
  UNIQUE(recipe_id, tag)
);

-- Create index for faster lookups
CREATE INDEX idx_recipe_tags_recipe_id ON public.recipe_tags(recipe_id);
CREATE INDEX idx_recipe_tags_tag ON public.recipe_tags(tag);

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

-- RLS Policy: Users can only see tags for recipes they own
CREATE POLICY "Users can view their own recipe tags"
  ON public.recipe_tags
  FOR SELECT
  USING (
    recipe_id IN (
      SELECT id FROM public.saved_recipes WHERE user_id = auth.uid()
    )
  );

-- RLS Policy: Users can insert tags for their own recipes
CREATE POLICY "Users can insert tags for their recipes"
  ON public.recipe_tags
  FOR INSERT
  WITH CHECK (
    recipe_id IN (
      SELECT id FROM public.saved_recipes WHERE user_id = auth.uid()
    )
  );

-- RLS Policy: Users can delete tags from their own recipes
CREATE POLICY "Users can delete their recipe tags"
  ON public.recipe_tags
  FOR DELETE
  USING (
    recipe_id IN (
      SELECT id FROM public.saved_recipes WHERE user_id = auth.uid()
    )
  );

Common migration patterns: See supabase-security.md

Step 3: Validate RLS Policies

CRITICAL: Every table MUST have RLS enabled and policies defined.

# Check if migration includes RLS
bash .claude/skills/supabase-migrations/scripts/validate-rls.sh supabase/migrations/[your-migration-file].sql

Checks for:

• ALTER TABLE ... ENABLE ROW LEVEL SECURITY
• At least one CREATE POLICY statement

Step 4: Test Migration Locally

Apply migration to local Supabase instance first:

# Ensure local Supabase is running
supabase start

# Apply migration to local database
supabase db push

# Check status
supabase db diff

If errors occur:

• Fix the SQL in migration file
• Reset local DB: supabase db reset
• Try again: supabase db push

Step 5: Regenerate TypeScript Types

After successful local migration, update types:

# Generate types from local database
bash .claude/skills/supabase-migrations/scripts/update-types.sh

# Or manually:
supabase gen types typescript --local > lib/supabase/types.ts

This updates lib/supabase/types.ts with new schema.

Step 6: Update Application Code

Search codebase for files that need updating:

# Find files using the affected table
grep -r "from('old_table_name')" app/ components/ lib/

Update TypeScript code to use new types:

import type { Database } from '@/lib/supabase/types'
type RecipeTag = Database['public']['Tables']['recipe_tags']['Row']

Step 7: Test Changes

• Run build: npm run build (must pass)
• Run tests: npm test (must pass)
• Test affected features manually

Step 8: Apply to Production

Only after local testing passes:

# Push migration to remote database
supabase db push --remote

# Or via Supabase Dashboard:
# 1. Copy migration SQL
# 2. Run in SQL Editor
# 3. Verify with table view

Important: Migrations are irreversible in production. Always test locally first.

Migration Safety Checklist

Before applying to production:

• Migration tested locally with supabase db push?
• RLS enabled on all new tables?
• RLS policies created (SELECT, INSERT, UPDATE, DELETE)?
• Indexes created for foreign keys and frequent queries?
• Types regenerated with update-types.sh?
• Application code updated to use new schema?
• Build passes (npm run build)?
• Tests pass (npm test)?
• Manual testing of affected features completed?

Common Migration Patterns

Adding a Column

ALTER TABLE public.saved_recipes
ADD COLUMN difficulty VARCHAR(20) CHECK (difficulty IN ('easy', 'medium', 'hard'));

Renaming a Column

ALTER TABLE public.saved_recipes
RENAME COLUMN old_name TO new_name;

Adding an Index

CREATE INDEX idx_recipes_user_id ON public.saved_recipes(user_id);

Adding a Foreign Key

ALTER TABLE public.recipe_images
ADD CONSTRAINT fk_recipe_images_recipe_id
FOREIGN KEY (recipe_id)
REFERENCES public.saved_recipes(id)
ON DELETE CASCADE;

RLS Policy Patterns

Policy: User owns resource

CREATE POLICY "Users can view their own recipes"
  ON public.saved_recipes
  FOR SELECT
  USING (user_id = auth.uid());

Policy: Public read, authenticated write

CREATE POLICY "Anyone can view recipes"
  ON public.saved_recipes
  FOR SELECT
  USING (true);

CREATE POLICY "Authenticated users can insert"
  ON public.saved_recipes
  FOR INSERT
  WITH CHECK (auth.uid() IS NOT NULL);

Policy: Relationship-based access

CREATE POLICY "Users can view tags for their recipes"
  ON public.recipe_tags
  FOR SELECT
  USING (
    recipe_id IN (
      SELECT id FROM public.saved_recipes WHERE user_id = auth.uid()
    )
  );

More RLS patterns: See supabase-security.md

Rollback Strategy

If a migration causes issues in production:

Option 1: Create reverse migration

supabase migration new revert_add_recipe_tags

# Write SQL to undo changes
# - DROP TABLE
# - DROP COLUMN
# - etc.

Option 2: Restore from backup (via Supabase Dashboard)

• Settings → Database → Point-in-time Recovery
• Select time before migration
• Restore (creates new project)

Prevention is better: Always test locally first!

Script Usage

create-migration.sh

bash .claude/skills/supabase-migrations/scripts/create-migration.sh "migration_name"

Creates timestamped migration file.

validate-rls.sh

bash .claude/skills/supabase-migrations/scripts/validate-rls.sh supabase/migrations/[file].sql

Checks for RLS policies in migration.

update-types.sh

bash .claude/skills/supabase-migrations/scripts/update-types.sh

Regenerates TypeScript types from local database.

Common Issues

"Supabase not running"

supabase start

"Migration file not found"

Check path: supabase/migrations/[timestamp]_name.sql

"RLS validation failed"

Add ALTER TABLE ... ENABLE ROW LEVEL SECURITY and CREATE POLICY statements.

"Types not updating"

supabase db reset
supabase db push
bash .claude/skills/supabase-migrations/scripts/update-types.sh

"Build errors after migration"

• Check type imports: Database['public']['Tables']['table_name']['Row']
• Update queries to match new schema
• Fix any breaking column renames

Quick Reference

Create migration:

supabase migration new name

Test locally:

supabase db push

Update types:

supabase gen types typescript --local > lib/supabase/types.ts

Apply to production:

supabase db push --remote

Related Documentation

• Supabase security patterns: supabase-security.md
• TypeScript type safety: typescript-standards.md
• Pre-commit checks: Use pre-commit-quality skill after code changes

Converted and distributed by TomeVault — claim your Tome and manage your conversions.