supabase-sql

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

<objective> Clean and format SQL migrations for direct paste into Supabase SQL Editor. Ensures idempotency, proper RLS policies (especially service role patterns), standardized formatting, and dependency documentation. </objective>

<quick_start> Clean any SQL migration:

Fix typos (
```
- 
```
→
```
-- 
```
for comments)
Add idempotency (
```
IF NOT EXISTS
```
,
```
DROP ... IF EXISTS
```
)
Fix RLS policies (service role uses
```
TO service_role
```
, not JWT checks)
Remove dead code (unused enums)
Standardize casing (
```
NOW()
```
,
```
TIMESTAMPTZ
```
)
Add dependencies comment at end

sql

DROP POLICY IF EXISTS "Policy name" ON table_name;
CREATE POLICY "Policy name" ON table_name ...

</quick_start>

<success_criteria> SQL cleanup is successful when:

All policies and triggers use
```
DROP ... IF EXISTS
```
before
```
CREATE
```
Service role policies use
```
TO service_role
```
(not JWT checks)
Indexes use
```
IF NOT EXISTS
```
No unused enums remain
Dependencies listed at end of migration
SQL runs without errors in Supabase SQL Editor </success_criteria>

<core_patterns> Clean SQL migrations for direct paste into Supabase SQL Editor.

<objective> 清理并格式化SQL迁移脚本，使其可直接粘贴至Supabase SQL Editor使用。确保脚本具备幂等性、正确的RLS策略（尤其是服务角色模式）、标准化的代码格式，以及完整的依赖说明。 </objective>

<quick_start> 清理任意SQL迁移脚本：

修正拼写错误（将注释中的
```
- 
```
替换为
```
-- 
```
）
添加幂等性处理（使用
```
IF NOT EXISTS
```
、
```
DROP ... IF EXISTS
```
）
修复RLS策略（服务角色需使用
```
TO service_role
```
，而非JWT校验）
移除无效代码（未使用的枚举类型）
统一大小写格式（如
```
NOW()
```
、
```
TIMESTAMPTZ
```
）
在末尾添加依赖说明注释

sql

DROP POLICY IF EXISTS "Policy name" ON table_name;
CREATE POLICY "Policy name" ON table_name ...

</quick_start>

<success_criteria> SQL清理完成的判定标准：

所有策略和触发器在
```
CREATE
```
前都使用
```
DROP ... IF EXISTS
```
服务角色策略使用
```
TO service_role
```
（而非JWT校验）
索引添加
```
IF NOT EXISTS
```
无未使用的枚举类型残留
迁移文件末尾列出所有依赖
SQL脚本可在Supabase SQL Editor中无错误运行 </success_criteria>

<core_patterns> 清理SQL迁移脚本，使其可直接粘贴至Supabase SQL Editor使用。

Cleanup Checklist

清理检查清单

Run through each item:

Fix typos - Common:
```
- 
```
instead of
```
-- 
```
on comment lines
Add idempotency -
```
IF NOT EXISTS
```
on indexes,
```
DROP ... IF EXISTS
```
before policies/triggers
Remove dead code - Enums created but never used (TEXT + CHECK often preferred)
Fix RLS policies - Service role must use
```
TO service_role
```
, not JWT checks
Standardize casing -
```
NOW()
```
not
```
now()
```
,
```
TIMESTAMPTZ
```
not
```
timestamptz
```
Remove clutter - Verbose RAISE NOTICE blocks, redundant comments, file path headers
Validate dependencies - List required tables at end

逐一检查以下项：

修正拼写错误 - 常见问题：注释行使用
```
- 
```
而非
```
-- 
```
添加幂等性处理 - 索引使用
```
IF NOT EXISTS
```
，策略/触发器在创建前使用
```
DROP ... IF EXISTS
```
移除无效代码 - 已创建但从未使用的枚举类型（通常优先使用TEXT + CHECK约束）
修复RLS策略 - 服务角色必须使用
```
TO service_role
```
，而非JWT校验
统一大小写格式 - 使用
```
NOW()
```
而非
```
now()
```
，
```
TIMESTAMPTZ
```
而非
```
timestamptz
```
移除冗余内容 - 冗长的RAISE NOTICE代码块、重复注释、文件路径头信息
验证依赖关系 - 在末尾列出所需的表

Output Format

输出格式

sql

-- ============================================
-- Migration Name
-- Created: YYYY-MM-DD
-- Purpose: One-line description
-- ============================================

-- ============================================
-- Table Name
-- ============================================

CREATE TABLE IF NOT EXISTS ...

-- ============================================
-- Indexes
-- ============================================

CREATE INDEX IF NOT EXISTS ...

-- ============================================
-- Row Level Security
-- ============================================

ALTER TABLE ... ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "..." ON ...;
CREATE POLICY "..." ON ...

-- ============================================
-- Functions
-- ============================================

CREATE OR REPLACE FUNCTION ...

-- ============================================
-- Triggers
-- ============================================

DROP TRIGGER IF EXISTS ... ON ...;
CREATE TRIGGER ...

sql

-- ============================================
-- Migration Name
-- Created: YYYY-MM-DD
-- Purpose: One-line description
-- ============================================

-- ============================================
-- Table Name
-- ============================================

CREATE TABLE IF NOT EXISTS ...

-- ============================================
-- Indexes
-- ============================================

CREATE INDEX IF NOT EXISTS ...

-- ============================================
-- Row Level Security
-- ============================================

ALTER TABLE ... ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "..." ON ...;
CREATE POLICY "..." ON ...

-- ============================================
-- Functions
-- ============================================

CREATE OR REPLACE FUNCTION ...

-- ============================================
-- Triggers
-- ============================================

DROP TRIGGER IF EXISTS ... ON ...;
CREATE TRIGGER ...

Common Fixes

常见修复案例

RLS Policy for Service Role

服务角色的RLS策略

sql

-- WRONG (doesn't work reliably)
CREATE POLICY "Service role access" ON my_table
    FOR ALL
    USING (auth.jwt() ->> 'role' = 'service_role');

-- CORRECT
CREATE POLICY "Service role access" ON my_table
    FOR ALL
    TO service_role
    USING (true)
    WITH CHECK (true);

sql

-- 错误写法（无法稳定运行）
CREATE POLICY "Service role access" ON my_table
    FOR ALL
    USING (auth.jwt() ->> 'role' = 'service_role');

-- 正确写法
CREATE POLICY "Service role access" ON my_table
    FOR ALL
    TO service_role
    USING (true)
    WITH CHECK (true);

Idempotent Policies

幂等性策略

sql

-- Always drop before create
DROP POLICY IF EXISTS "Policy name" ON table_name;
CREATE POLICY "Policy name" ON table_name ...

sql

-- 始终先删除再创建
DROP POLICY IF EXISTS "Policy name" ON table_name;
CREATE POLICY "Policy name" ON table_name ...

Idempotent Triggers

幂等性触发器

sql

DROP TRIGGER IF EXISTS trigger_name ON table_name;
CREATE TRIGGER trigger_name ...

sql

DROP TRIGGER IF EXISTS trigger_name ON table_name;
CREATE TRIGGER trigger_name ...

Unused Enums

未使用的枚举类型

If you see enum created but table uses

TEXT CHECK (...)

instead, remove the enum:

sql

-- DELETE THIS - never used
DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'my_enum') THEN
        CREATE TYPE my_enum AS ENUM ('a', 'b', 'c');
    END IF;
END$$;

-- Table actually uses TEXT with CHECK (keep this)
status TEXT NOT NULL CHECK (status IN ('a', 'b', 'c'))

如果发现已创建枚举类型，但表实际使用

TEXT CHECK (...)

约束，请移除该枚举类型：

sql

-- 删除这段代码 - 从未使用
DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'my_enum') THEN
        CREATE TYPE my_enum AS ENUM ('a', 'b', 'c');
    END IF;
END$$;

-- 表实际使用TEXT类型加CHECK约束（保留这段）
status TEXT NOT NULL CHECK (status IN ('a', 'b', 'c'))

Dependencies Section

依赖说明部分

Always end with dependencies note if tables are referenced:

sql

-- Dependencies: businesses, call_logs, subscription_plans
-- Requires function: update_updated_at_column()

如果脚本中引用了其他表，请始终在末尾添加依赖说明：

sql

-- Dependencies: businesses, call_logs, subscription_plans
-- Requires function: update_updated_at_column()

Reference Files

参考文件

```
reference/rls-patterns.md
```
- Common RLS policy patterns for Supabase
```
reference/function-patterns.md
```
- Trigger functions, atomic operations

```
reference/rls-patterns.md
```
- Supabase常用RLS策略模式
```
reference/function-patterns.md
```
- 触发器函数、原子操作相关内容 </core_patterns>