Supabase Migration Writer Skill

Supabase Migration Writer

Context Files (Read First)

For schema and Supabase layout, read from Docs/context/:

Docs/context/db-schema-short.md - Database schema overview
Docs/context/supabase-map.md - Edge Functions, migrations, access matrix

Cross-cutting learnings: See .claude/LEARNINGS.md → "Supabase/Database" section for RLS+GRANT patterns, RPC gotchas, and CHECK constraints.

Quick Reference

Project ID: iryqgmjauybluwnqhxbg
Migrations: supabase/migrations/
Edge Functions: supabase/functions/

Migration File Convention

supabase/migrations/YYYYMMDDHHMMSS_description.sql

Example: 20250124120000_add_user_notes_table.sql

Essential Patterns

Create Table with RLS

CREATE TABLE IF NOT EXISTS public.table_name (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  content TEXT NOT NULL,
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now()
);

CREATE INDEX idx_table_user_id ON public.table_name(user_id);

ALTER TABLE public.table_name ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Users can view own data"
ON public.table_name FOR SELECT TO authenticated
USING (user_id = auth.uid());

CREATE POLICY "Users can insert own data"
ON public.table_name FOR INSERT TO authenticated
WITH CHECK (user_id = auth.uid());

CREATE TRIGGER set_updated_at
BEFORE UPDATE ON public.table_name
FOR EACH ROW EXECUTE FUNCTION public.handle_updated_at();

Add Column

ALTER TABLE public.table_name
ADD COLUMN IF NOT EXISTS new_column TEXT DEFAULT 'value';

COMMENT ON COLUMN public.table_name.new_column IS 'Description';

Create RPC Function

CREATE OR REPLACE FUNCTION public.function_name(
  p_user_id UUID DEFAULT auth.uid(),
  p_limit INT DEFAULT 20
)
RETURNS TABLE (col1 UUID, col2 TEXT)
LANGUAGE sql STABLE SECURITY DEFINER
SET search_path TO 'public', 'bible_schema'
AS $$
  SELECT col1, col2
  FROM table_name
  WHERE user_id = p_user_id
  LIMIT p_limit;
$$;

GRANT EXECUTE ON FUNCTION public.function_name TO authenticated;

Data Types Quick Reference

| Use Case | Type | |----------|------| | ID | UUID DEFAULT gen_random_uuid() | | User ref | UUID REFERENCES auth.users(id) | | Text | TEXT | | Boolean | BOOLEAN DEFAULT true | | Timestamp | TIMESTAMPTZ DEFAULT now() | | Number | INTEGER | | Decimal | NUMERIC(10,2) | | JSON | JSONB DEFAULT '{}' | | Array | TEXT[] DEFAULT '{}' | | Enum | TEXT CHECK (col IN ('a', 'b')) |

MCP Tools Available

Use Supabase MCP tools directly:

mcp__supabase__list_tables        # List all tables
mcp__supabase__execute_sql        # Run queries
mcp__supabase__apply_migration    # Apply DDL
mcp__supabase__list_edge_functions
mcp__supabase__get_logs           # Debug issues
mcp__supabase__get_advisors       # Security/perf checks

References

Context docs: Docs/context/db-schema-short.md, Docs/context/supabase-map.md (authoritative)
Secrets & env vars: See references/secrets.md

Testing Migrations

# Apply locally
supabase db push

# Reset and reapply all
supabase db reset

Best Practices Checklist

[ ] Use IF NOT EXISTS / IF EXISTS
[ ] Add created_at and updated_at timestamps
[ ] Enable RLS on all tables
[ ] Add indexes for foreign keys and filtered columns
[ ] Use SECURITY DEFINER for RPC functions
[ ] Set search_path in functions
[ ] Add COMMENT ON for documentation
[ ] Create rollback script for complex changes
[ ] Update TypeScript types after migration (see learnings)

CRITICAL: Type Synchronization

After ANY migration that adds tables, columns, or RPC functions:

npx supabase gen types typescript --project-id iryqgmjauybluwnqhxbg > apps/raamattu-nyt/src/integrations/supabase/types.ts

If types can't be regenerated, manually add to types.ts. See references/learnings.md for patterns and workarounds.

Why this matters: Lovable Cloud uses the committed types.ts file. If types are out of sync, builds fail.

Agent Skills: Supabase Migration Writer

Install this agent skill to your local

Skill Files