Database Migrations Guide

node apps/database/scripts/declarative-schema-readiness.js --json

node apps/database/scripts/verify-declarative-parity.mjs --docker-container <supabase_db_container> --out a.json
node apps/database/scripts/verify-declarative-parity.mjs --compare a.json b.json

┌─────────────────────────────────────────────────────┐
│  1. Make schema changes locally                      │
│     ├─ Edit tables in Supabase Studio               │
│     ├─ OR write SQL migration manually              │
│     └─ Test changes                                  │
└─────────────────┬───────────────────────────────────┘
                  │
┌─────────────────▼───────────────────────────────────┐
│  2. Generate migration                               │
│     ├─ bun sb:diff (from Studio changes)            │
│     ├─ OR bun sb:new (blank migration)              │
│     └─ Review generated SQL                          │
└─────────────────┬───────────────────────────────────┘
                  │
┌─────────────────▼───────────────────────────────────┐
│  3. Test migration locally                           │
│     ├─ bun sb:reset (fresh DB)                      │
│     ├─ Verify schema                                │
│     └─ Test RLS policies                            │
└─────────────────┬───────────────────────────────────┘
                  │
┌─────────────────▼───────────────────────────────────┐
│  4. Commit migration                                 │
│     ├─ git add apps/database/supabase/migrations/          │
│     ├─ git commit                                    │
│     └─ Push to repository                           │
└─────────────────┬───────────────────────────────────┘
                  │
┌─────────────────▼───────────────────────────────────┐
│  5. Deploy (USER ONLY - AGENTS NEVER)               │
│     ├─ bun sb:push (production)                     │
│     ├─ OR apply via CI/CD                           │
│     └─ Verify in production                         │
└─────────────────────────────────────────────────────┘

[timestamp]_[descriptive_name].sql

20250101120000_create_tasks_table.sql
20250101130000_add_task_priority_column.sql
20250101140000_update_task_rls_policies.sql

-- Migration: [Description]
-- Created: [Date]
-- Author: [Name]

-- Create table
CREATE TABLE IF NOT EXISTS table_name (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  created_at timestamptz DEFAULT now()
);

-- Add indexes
CREATE INDEX idx_table_name_column ON table_name(column);

-- Enable RLS
ALTER TABLE table_name ENABLE ROW LEVEL SECURITY;

-- Add RLS policies
CREATE POLICY "policy_name"
ON table_name
FOR SELECT
USING (true);

-- Add comments
COMMENT ON TABLE table_name IS 'Description of table';
COMMENT ON COLUMN table_name.column IS 'Description of column';

-- Create workspace-scoped table
CREATE TABLE workspace_resources (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  ws_id text NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
  name text NOT NULL,
  description text,
  created_by uuid REFERENCES workspace_users(id) ON DELETE SET NULL,
  created_at timestamptz DEFAULT now(),
  updated_at timestamptz DEFAULT now()
);

-- Add indexes
CREATE INDEX idx_workspace_resources_ws_id ON workspace_resources(ws_id);
CREATE INDEX idx_workspace_resources_created_by ON workspace_resources(created_by);

-- Enable RLS
ALTER TABLE workspace_resources ENABLE ROW LEVEL SECURITY;

-- Add policies
CREATE POLICY "workspace_members_can_view"
ON workspace_resources FOR SELECT
USING (
  ws_id IN (
    SELECT ws_id FROM workspace_members WHERE user_id = auth.uid()
  )
);

CREATE POLICY "permission_required_to_modify"
ON workspace_resources FOR ALL
USING (
  ws_id IN (
    SELECT wm.ws_id FROM workspace_members wm
    JOIN workspace_role_permissions wrp
      ON wrp.ws_id = wm.ws_id AND wrp.role_id = wm.role
    WHERE wm.user_id = auth.uid()
      AND wrp.permission = 'manage_resources'
  )
);

-- Add nullable column
ALTER TABLE workspace_tasks
ADD COLUMN priority integer;

-- Add column with default
ALTER TABLE workspace_tasks
ADD COLUMN status text DEFAULT 'pending';

-- Add NOT NULL column (requires default or backfill)
ALTER TABLE workspace_tasks
ADD COLUMN category text DEFAULT 'general' NOT NULL;

-- Update existing rows (if needed)
UPDATE workspace_tasks
SET category = 'important'
WHERE priority >= 4;

-- Rename column
ALTER TABLE workspace_tasks
RENAME COLUMN old_name TO new_name;

-- Change column type
ALTER TABLE workspace_tasks
ALTER COLUMN priority TYPE smallint USING priority::smallint;

-- Add constraint
ALTER TABLE workspace_tasks
ADD CONSTRAINT priority_range CHECK (priority BETWEEN 0 AND 5);

-- Drop constraint
ALTER TABLE workspace_tasks
DROP CONSTRAINT priority_range;

-- Standard index
CREATE INDEX idx_tasks_ws_id ON workspace_tasks(ws_id);

-- Composite index
CREATE INDEX idx_tasks_ws_list ON workspace_tasks(ws_id, list_id);

-- Partial index
CREATE INDEX idx_incomplete_tasks
ON workspace_tasks(ws_id)
WHERE completed = false;

-- Unique index
CREATE UNIQUE INDEX idx_unique_task_name
ON workspace_tasks(ws_id, name);

-- GIN index for JSONB
CREATE INDEX idx_metadata_gin ON workspace_tasks USING GIN (metadata);

-- Text search index
CREATE INDEX idx_tasks_search
ON workspace_tasks USING GIN (to_tsvector('english', name || ' ' || COALESCE(description, '')));

-- Simple function
CREATE OR REPLACE FUNCTION get_workspace_task_count(workspace_id text)
RETURNS integer AS $$
  SELECT COUNT(*)::integer
  FROM workspace_tasks
  WHERE ws_id = workspace_id;
$$ LANGUAGE sql SECURITY DEFINER;

-- Function with complex logic
CREATE OR REPLACE FUNCTION complete_task_with_notification(task_uuid uuid)
RETURNS void AS $$
DECLARE
  task_name text;
  task_ws_id text;
BEGIN
  -- Update task
  UPDATE workspace_tasks
  SET completed = true, completed_at = now()
  WHERE id = task_uuid
  RETURNING name, ws_id INTO task_name, task_ws_id;

  -- Create notification (example)
  INSERT INTO notifications (ws_id, message, type)
  VALUES (task_ws_id, format('Task "%s" completed', task_name), 'success');
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Update updated_at timestamp trigger
CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS trigger AS $$
BEGIN
  NEW.updated_at = now();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_update_workspace_tasks_updated_at
  BEFORE UPDATE ON workspace_tasks
  FOR EACH ROW
  EXECUTE FUNCTION update_updated_at();

-- Create enum
CREATE TYPE task_status AS ENUM ('pending', 'in_progress', 'completed', 'cancelled');

-- Add enum column
ALTER TABLE workspace_tasks
ADD COLUMN status task_status DEFAULT 'pending';

-- Modify enum (add value)
ALTER TYPE task_status ADD VALUE 'on_hold';

-- Cannot remove enum values - requires recreation

-- 1. Add new column
ALTER TABLE workspace_tasks
ADD COLUMN new_priority integer;

-- 2. Backfill data
UPDATE workspace_tasks
SET new_priority = CASE
  WHEN old_priority = 'high' THEN 5
  WHEN old_priority = 'medium' THEN 3
  WHEN old_priority = 'low' THEN 1
  ELSE 0
END;

-- 3. Make NOT NULL (if needed)
ALTER TABLE workspace_tasks
ALTER COLUMN new_priority SET NOT NULL;

-- 4. Drop old column (in separate migration after verification)
-- ALTER TABLE workspace_tasks DROP COLUMN old_priority;

-- For tables with millions of rows, batch the updates
DO $$
DECLARE
  batch_size integer := 1000;
  rows_updated integer;
BEGIN
  LOOP
    UPDATE workspace_tasks
    SET new_column = calculate_value(old_column)
    WHERE id IN (
      SELECT id FROM workspace_tasks
      WHERE new_column IS NULL
      LIMIT batch_size
    );

    GET DIAGNOSTICS rows_updated = ROW_COUNT;
    EXIT WHEN rows_updated = 0;

    -- Commit after each batch
    COMMIT;
  END LOOP;
END $$;

# Reset to clean state
bun sb:reset

# Verify schema
bun sb:status

# Check migration list
ls apps/database/supabase/migrations/

# Test specific queries
psql -h localhost -U postgres -d postgres -c "SELECT * FROM workspace_tasks LIMIT 5;"

-- Set user context for testing
SET request.jwt.claim.sub = 'user-uuid-here';

-- Test query with RLS
SELECT * FROM workspace_tasks WHERE ws_id = 'workspace-123';

-- Reset context
RESET request.jwt.claim.sub;

-- Check if index is being used
EXPLAIN ANALYZE
SELECT * FROM workspace_tasks WHERE ws_id = 'workspace-123';

-- List all indexes on table
SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'workspace_tasks';

-- allow-anon-grant: public catalog read by signed-out visitors
grant select on table public.public_catalog to anon;

-- allow-definer-public: called by the pre-sign-in login flow
create or replace function public.example_login_probe() ...

# Link to remote project (one-time setup)
bun sb:link

# Push migrations to production
bun sb:push

-- Create reverse migration
-- apps/database/supabase/migrations/20250101150000_rollback_add_priority.sql

ALTER TABLE workspace_tasks DROP COLUMN priority;

-- apps/database/supabase/migrations/20250101150000_rollback_task_status_enum.sql

-- 1. Add back old column
ALTER TABLE workspace_tasks ADD COLUMN old_status text;

-- 2. Convert data
UPDATE workspace_tasks
SET old_status = CASE
  WHEN status = 'pending' THEN 'todo'
  WHEN status = 'in_progress' THEN 'doing'
  WHEN status = 'completed' THEN 'done'
END;

-- 3. Drop new column
ALTER TABLE workspace_tasks DROP COLUMN status;

-- 4. Rename old column
ALTER TABLE workspace_tasks RENAME COLUMN old_status TO status;

-- 5. Drop enum type
DROP TYPE task_status;

# Check Supabase version
bun sb:status

# Update Supabase CLI
bun add -g supabase

# Recreate local instance
bun sb:stop
bun sb:start

# Reset database
bun sb:reset

# Check migration syntax
cat apps/database/supabase/migrations/[migration-file].sql

# Fix errors and retry
bun sb:typegen

-- List all policies
SELECT policyname, permissive, roles, cmd, qual
FROM pg_policies
WHERE tablename = 'workspace_tasks';

-- Drop conflicting policy
DROP POLICY "old_policy" ON workspace_tasks;

-- tuturuuu: reviewed(function-grant): public.example(integer) review=<reference>
GRANT EXECUTE ON FUNCTION public.example(integer) TO authenticated;

-- tuturuuu: reviewed(function-grant): public.example(integer) anon-required review=<reference>
GRANT EXECUTE ON FUNCTION public.example(integer) TO anon;

-- tuturuuu: reviewed(dynamic-sql-review): <review-reference>
DO $$ BEGIN EXECUTE 'SELECT 1'; END $$;

-- tuturuuu: reviewed(alter-routine-review): <review-reference>
ALTER FUNCTION public.example(integer) SET search_path = pg_catalog, pg_temp;