database-migration

name	database-migration
description	Guide for creating idempotent Supabase database migrations with RLS policies and workspace isolation

Database Migration Skill

Creating Idempotent Supabase Migrations

When to Use: Adding tables, modifying schemas, creating RLS policies, adding functions

---

Process

1. Check Existing Schema

ALWAYS check before creating:

# Read schema reference
cat docs/guides/schema-reference.md

# Or check existing migrations
ls supabase/migrations/

2. Create Migration File

Location: supabase/migrations/YYYYMMDDHHMMSS_description.sql

Naming: Use timestamp + descriptive name

20251230120000_add_agent_registry_table.sql

3. Write Idempotent SQL

Pattern: Use IF NOT EXISTS and CREATE OR REPLACE

-- Tables
CREATE TABLE IF NOT EXISTS agent_registry (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  workspace_id UUID NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
  agent_id TEXT NOT NULL,
  version TEXT NOT NULL,
  capabilities JSONB   ::jsonb,
  created_at TIMESTAMPTZ  NOW(),

  (workspace_id, agent_id)
);


 INDEX IF   idx_agent_registry_workspace
   agent_registry(workspace_id);

 INDEX IF   idx_agent_registry_agent
   agent_registry(agent_id, workspace_id);


 agent_registry ENABLE  LEVEL SECURITY;

 POLICY IF  "Users can view their workspace agents"  agent_registry;
 POLICY "Users can view their workspace agents"  agent_registry
     (
    workspace_id  (
       w.id  workspaces w
        user_organizations uo  uo.org_id  w.org_id
       uo.user_id  auth.uid()
    )
  );

 POLICY IF  "System can manage agents"  agent_registry;
 POLICY "System can manage agents"  agent_registry
     ()   ();


  REPLACE  get_agent_count(p_workspace_id UUID)
   $$

   ( ()  agent_registry  workspace_id  p_workspace_id);
;
$$  plpgsql STABLE;


COMMENT   agent_registry  ;