backend-development

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

Supabase Local Dev Workflow

Supabase本地开发工作流

Core Philosophy

核心理念

Schema-driven development — all structural changes go to schema files, never direct SQL
RPC-first architecture — no direct
```
supabase-js
```
table calls; all data access through RPCs
DB functions as first-class citizens — business logic lives in the database

Schema驱动开发 — 所有结构变更都要写入schema文件，绝不直接执行SQL
RPC优先架构 — 不直接调用
```
supabase-js
```
的表接口；所有数据访问都通过RPC完成
数据库函数作为一等公民 — 业务逻辑存储在数据库中

Process

流程

Phase 1: Schema Changes

第一阶段：Schema变更

Write structural changes to the appropriate schema file based on the folder structure:

supabase/schemas/
├── 10_types/        # Enums, composite types, domains
├── 20_tables/       # Table definitions
├── 30_constraints/  # Check constraints, foreign keys
├── 40_indexes/      # Index definitions
├── 50_functions/    # RPCs, auth functions, internal utils
│   ├── _internal/   # Infrastructure utilities
│   └── _auth/       # RLS policy functions
├── 60_triggers/     # Trigger definitions
├── 70_policies/     # RLS policies
└── 80_views/        # View definitions

Files are organized by entity (e.g.,

charts.sql

readings.sql

). Numeric prefixes ensure correct application order.

📋 Load Naming Conventions for table, column, and function naming rules.

根据文件夹结构，将结构变更写入对应的schema文件：

supabase/schemas/
├── 10_types/        # 枚举类型、复合类型、域
├── 20_tables/       # 表定义
├── 30_constraints/  # 检查约束、外键
├── 40_indexes/      # 索引定义
├── 50_functions/    # RPC、身份验证函数、内部工具
│   ├── _internal/   # 基础设施工具
│   └── _auth/       # RLS策略函数
├── 60_triggers/     # 触发器定义
├── 70_policies/     # RLS策略
└── 80_views/        # 视图定义

文件按实体组织（例如

charts.sql

、

readings.sql

）。数字前缀确保应用顺序正确。

📋 查看命名规范获取表、列和函数的命名规则。

Phase 2: Apply & Fix

第二阶段：应用与修复

CLI auto-applies changes (
```
supabase start
```
)
Monitor logs for errors (constraint violations, dependencies)
If errors → use
```
execute_sql
```
MCP tool for data fixes only (UPDATE, DELETE, INSERT)
Never use
```
execute_sql
```
for schema structure — only schema files

CLI自动应用变更（
```
supabase start
```
）
监控日志排查错误（约束冲突、依赖问题）
若出现错误 → 仅使用
```
execute_sql
```
MCP工具进行数据修复（UPDATE、DELETE、INSERT）
绝不要使用
```
execute_sql
```
修改schema结构 — 仅通过schema文件操作

Phase 3: Generate Types

第三阶段：生成类型

bash

supabase gen types typescript --local > src/types/database.ts

bash

supabase gen types typescript --local > src/types/database.ts

Phase 4: Iterate

第四阶段：迭代

Repeat Phases 1-3 until schema is stable and tested.

重复第一至第三阶段，直到schema稳定并通过测试。

Phase 5: Migration

第五阶段：迁移

Use
```
supabase db diff
```
to generate migration
Review migration — patch if manual SQL commands are missing

使用
```
supabase db diff
```
生成迁移文件
审核迁移文件 — 若缺少手动SQL命令则进行补丁修复

Reference Files

参考文件

Load these as needed during development:

开发过程中可按需查看以下文件：

Conventions & Patterns

规范与模式

📋 Naming Conventions — Tables, columns, functions, indexes
🔐 RPC Patterns — RPC-first architecture, auth functions, RLS policies
⚡ Edge Functions — Project structure, shared utilities, CORS, error helpers
🔧 withSupabase Wrapper — Wrapper rules, role selection, client usage patterns

📋 命名规范 — 表、列、函数、索引
🔐 RPC模式 — RPC优先架构、身份验证函数、RLS策略
⚡ 边缘函数 — 项目结构、共享工具、CORS、错误处理助手
🔧 withSupabase包装器 — 包装器规则、角色选择、客户端使用模式

Setup & Infrastructure

设置与基础设施

⚙️ Setup Guide — Vault secrets, internal utility functions

⚙️ 设置指南 — 密钥库机密、内部工具函数

Workflows

工作流

📝 Common Workflows — Adding entities, fields, creating RPCs

📝 通用工作流 — 添加实体、字段、创建RPC

Entity Tracking

实体跟踪

📊 Entity Registry Template — Track entities and schema files

📊 实体注册表模板 — 跟踪实体与schema文件

Tools & Dependencies

工具与依赖

Tool	Purpose
Supabase CLI	Local development, type generation, migrations
Supabase MCP	`execute_sql` tool for data fixes
Edge Functions	See Edge Functions for project structure and withSupabase for wrapper usage

工具	用途
Supabase CLI	本地开发、类型生成、迁移
Supabase MCP	`execute_sql` 工具用于数据修复
边缘函数	查看边缘函数了解项目结构，查看withSupabase了解包装器用法

Quick Reference

快速参考

Client-side rule — Never direct table access:

typescript

// ❌ WRONG
const { data } = await supabase.from("charts").select("*");

// ✅ CORRECT
const { data } = await supabase.rpc("chart_get_by_user", { p_user_id: userId });

Security context rule — SECURITY INVOKER by default:

sql

-- ❌ WRONG — bypasses RLS then reimplements filtering manually
CREATE FUNCTION chart_get_by_id(p_chart_id uuid)
RETURNS jsonb LANGUAGE plpgsql SECURITY DEFINER SET search_path = '' AS $$
BEGIN
  SELECT ... FROM public.charts WHERE id = p_chart_id AND user_id = auth.uid(); -- manual filter = fragile
END; $$;

-- ✅ CORRECT — RLS handles access control automatically
CREATE FUNCTION chart_get_by_id(p_chart_id uuid)
RETURNS jsonb LANGUAGE plpgsql SECURITY INVOKER SET search_path = '' AS $$
BEGIN
  SELECT ... FROM public.charts WHERE id = p_chart_id; -- RLS enforces permissions
END; $$;

When to use SECURITY DEFINER (rare exceptions):

```
_auth_*
```
functions called by RLS policies (they run during policy evaluation, need to bypass RLS to query the table they protect)
```
_internal_*
```
utility functions that need elevated access (e.g., reading vault secrets)
Multi-table operations that need cross-table access the user's role can't reach

Always document WHY with a comment:

-- SECURITY DEFINER: required because ...

Function prefixes:

Business logic:
```
{entity}_{action}
```
→
```
chart_create
```
(SECURITY INVOKER)
Auth (RLS):
```
_auth_{entity}_{check}
```
→
```
_auth_chart_can_read
```
(SECURITY DEFINER — needed by RLS)
Internal:
```
_internal_{name}
```
→
```
_internal_get_secret
```
(SECURITY DEFINER — elevated access)

客户端规则 — 绝不直接访问表：

typescript

// ❌ 错误示例
const { data } = await supabase.from("charts").select("*");

// ✅ 正确示例
const { data } = await supabase.rpc("chart_get_by_user", { p_user_id: userId });

安全上下文规则 — 默认使用SECURITY INVOKER：

sql

// ❌ 错误示例 — 绕过RLS后手动实现过滤，易出问题
CREATE FUNCTION chart_get_by_id(p_chart_id uuid)
RETURNS jsonb LANGUAGE plpgsql SECURITY DEFINER SET search_path = '' AS $$
BEGIN
  SELECT ... FROM public.charts WHERE id = p_chart_id AND user_id = auth.uid(); -- 手动过滤不可靠
END; $$;

// ✅ 正确示例 — RLS自动处理访问控制
CREATE FUNCTION chart_get_by_id(p_chart_id uuid)
RETURNS jsonb LANGUAGE plpgsql SECURITY INVOKER SET search_path = '' AS $$
BEGIN
  SELECT ... FROM public.charts WHERE id = p_chart_id; -- RLS强制执行权限控制
END; $$;

何时使用SECURITY DEFINER（罕见例外情况）：

RLS策略调用的
```
_auth_*
```
函数（它们在策略评估期间运行，需要绕过RLS才能查询所保护的表）
需要提升权限的
```
_internal_*
```
工具函数（例如读取密钥库机密）
需要跨表访问而用户角色无权限的多表操作
务必通过注释说明原因：
```
-- SECURITY DEFINER: 因...需要
```

函数前缀：

业务逻辑：
```
{entity}_{action}
```
→
```
chart_create
```
（SECURITY INVOKER）
身份验证（RLS）：
```
_auth_{entity}_{check}
```
→
```
_auth_chart_can_read
```
（SECURITY DEFINER — RLS所需）
内部：
```
_internal_{name}
```
→
```
_internal_get_secret
```
（SECURITY DEFINER — 提升权限）