数据库迁移技能

创建幂等的Supabase数据库迁移

何时使用：添加表、修改架构、创建RLS策略、添加函数

---

流程

1. 检查现有架构

始终在创建前检查：

# 读取架构参考
cat docs/guides/schema-reference.md

# 或检查现有迁移
ls supabase/migrations/

2. 创建迁移文件

位置：supabase/migrations/YYYYMMDDHHMMSS_description.sql

命名：使用时间戳 + 描述性名称

20251230120000_add_agent_registry_table.sql

3. 编写幂等SQL

模式：使用IF NOT EXISTS和CREATE OR REPLACE

-- 表
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 NOT NULL DEFAULT '[]'::jsonb,
  created_at TIMESTAMPTZ DEFAULT NOW(),

  UNIQUE(workspace_id, agent_id)
);

-- 索引
CREATE INDEX IF NOT EXISTS idx_agent_registry_workspace
  ON agent_registry(workspace_id);

CREATE INDEX IF NOT EXISTS idx_agent_registry_agent
  ON agent_registry(agent_id, workspace_id);

-- RLS
ALTER TABLE agent_registry ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "Users can view their workspace agents" ON agent_registry;
CREATE POLICY "Users can view their workspace agents" ON agent_registry
  FOR SELECT USING (
    workspace_id IN (
      SELECT w.id FROM workspaces w
      INNER JOIN user_organizations uo ON uo.org_id = w.org_id
      WHERE uo.user_id = auth.uid()
    )
  );

DROP POLICY IF EXISTS "System can manage agents" ON agent_registry;
CREATE POLICY "System can manage agents" ON agent_registry
  FOR ALL USING (true) WITH CHECK (true);

-- 函数
CREATE OR REPLACE FUNCTION get_agent_count(p_workspace_id UUID)
RETURNS INTEGER AS $$
BEGIN
  RETURN (SELECT COUNT(*) FROM agent_registry WHERE workspace_id = p_workspace_id);
END;
$$ LANGUAGE plpgsql STABLE;

-- 注释
COMMENT ON TABLE agent_registry IS 'Registry of all active agents per workspace';

4. RLS策略模式

始终使用：user_organizations + workspaces连接（而不是workspace_members）

-- 正确模式
workspace_id IN (
  SELECT w.id FROM workspaces w
  INNER JOIN user_organizations uo ON uo.org_id = w.org_id
  WHERE uo.user_id = auth.uid()
)

-- 仅限管理员/所有者
workspace_id IN (
  SELECT w.id FROM workspaces w
  INNER JOIN user_organizations uo ON uo.org_id = w.org_id
  WHERE uo.user_id = auth.uid() AND uo.role IN ('admin', 'owner')
)

5. 应用迁移

方法：Supabase Dashboard → SQL Editor

步骤：

1. 复制迁移SQL
2. 粘贴到SQL Editor
3. 点击“运行”
4. 验证成功

替代方法：使用WORKING_MIGRATIONS.sql模式进行合并迁移

---

示例

示例1：简单表

CREATE TABLE IF NOT EXISTS my_table (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  workspace_id UUID NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  data JSONB,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_my_table_workspace ON my_table(workspace_id);

ALTER TABLE my_table ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "workspace_isolation" ON my_table;
CREATE POLICY "workspace_isolation" ON my_table
  FOR ALL USING (
    workspace_id IN (
      SELECT w.id FROM workspaces w
      INNER JOIN user_organizations uo ON uo.org_id = w.org_id
      WHERE uo.user_id = auth.uid()
    )
  );

示例2：ENUM类型

DO $$ BEGIN
  CREATE TYPE agent_status AS ENUM ('active', 'paused', 'disabled');
EXCEPTION WHEN duplicate_object THEN NULL;
END $$;

示例3：触发器

CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trigger_update_updated_at ON my_table;
CREATE TRIGGER trigger_update_updated_at
  BEFORE UPDATE ON my_table
  FOR EACH ROW
  EXECUTE FUNCTION update_updated_at();

---

常见模式

工作区隔离（必须）

CREATE TABLE table_name (
  ...
  workspace_id UUID NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
  ...
);

-- 始终在workspace_id上添加索引
CREATE INDEX IF NOT EXISTS idx_table_workspace ON table_name(workspace_id);

-- 始终添加RLS
ALTER TABLE table_name ENABLE ROW LEVEL SECURITY;

约束

-- 检查约束
CONSTRAINT valid_status CHECK (status IN ('active', 'inactive')),
CONSTRAINT valid_score CHECK (score >= 0 AND score <= 100),
CONSTRAINT valid_email CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$')

外键

-- 带有级联删除
workspace_id UUID NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,

-- 带有设置为空
created_by UUID REFERENCES auth.users(id) ON DELETE SET NULL,

-- 带有限制（如果被引用则阻止删除）
org_id UUID NOT NULL REFERENCES organizations(id) ON DELETE RESTRICT

---

清单

在应用迁移前：

• [ ] 检查了schema-reference.md是否有冲突
• [ ] 在表上使用了IF NOT EXISTS
• [ ] 在函数上使用了CREATE OR REPLACE
• [ ] 添加了workspace_id列（如果是多租户表）
• [ ] 在workspace_id上创建了索引
• [ ] 启用了RLS
• [ ] 添加了RLS策略（用户+系统）
• [ ] 使用了正确的RLS模式（user_organizations连接）
• [ ] 在适当的地方添加了约束
• [ ] 添加了注释以供文档使用
• [ ] 在本地测试了SQL语法

---

故障排除

错误：“relation workspace_members does not exist” 修复：使用user_organizations + workspaces连接（见上面的RLS模式）

错误：“already exists” 修复：使用IF NOT EXISTS或CREATE OR REPLACE

错误：“permission denied” 修复：在Supabase Dashboard中使用服务角色密钥，而不是匿名密钥

---

标准：幂等的、工作区隔离的、RLS保护的、文档良好的