Back to Details

supabase-database-ops

Compare original and translation side by side

🇺🇸

Original

English
🇨🇳

Translation

Chinese

Supabase Database Operations - Critical Guardrail

Supabase数据库操作 - 关键防护准则

Purpose

目的

CRITICAL GUARDRAIL to prevent multi-tenant data leakage and enforce database best practices in the AIProDaily platform.
关键防护准则，用于在AIProDaily平台中防止多租户数据泄露并执行数据库最佳实践。

When to Use

适用场景

This skill BLOCKS database operations until verified when:
  • Writing Supabase queries (
    supabaseAdmin.from()
    )
  • Accessing tenant-scoped tables
  • Creating API routes with database access
  • Working with campaign, article, or RSS data
当出现以下情况时，该准则会阻止数据库操作，直到验证通过：
  • 编写Supabase查询（
    supabaseAdmin.from()
    ）
  • 访问租户范围的表
  • 创建带有数据库访问权限的API路由
  • 处理营销活动、文章或RSS数据

🚨 CRITICAL RULES 🚨

🚨 核心规则 🚨

Rule #1: ALWAYS Filter by publication_id

规则1：始终通过publication_id进行过滤

EVERY query on tenant-scoped tables MUST include 
publication_id
filter.
typescript
// ✅ CORRECT - publication_id filter present
const { data, error } = await supabaseAdmin
  .from('newsletter_campaigns')
  .select('id, status, date')
  .eq('publication_id', newsletterId)  // ✅ REQUIRED
  .eq('id', campaignId)
  .single()

// ❌ WRONG - Missing publication_id filter (DATA LEAKAGE!)
const { data, error } = await supabaseAdmin
  .from('newsletter_campaigns')
  .select('id, status, date')
  .eq('id', campaignId)  // ❌ Can access other tenants' data!
  .single()
所有针对租户范围表的查询必须包含
publication_id
过滤条件。
typescript
// ✅ 正确示例 - 包含publication_id过滤条件
const { data, error } = await supabaseAdmin
  .from('newsletter_campaigns')
  .select('id, status, date')
  .eq('publication_id', newsletterId)  // ✅ 必须包含
  .eq('id', campaignId)
  .single()

// ❌ 错误示例 - 缺少publication_id过滤条件（存在数据泄露风险！）
const { data, error } = await supabaseAdmin
  .from('newsletter_campaigns')
  .select('id, status, date')
  .eq('id', campaignId)  // ❌ 可访问其他租户的数据！
  .single()

Tenant-Scoped Tables (MUST filter by publication_id):

需按租户隔离的表（必须通过publication_id过滤）：

  • newsletter_campaigns
  • articles
  • secondary_articles
  • rss_posts
  • post_ratings
  • rss_feeds
  • app_settings
  • advertisements
  • campaign_advertisements
  • archived_articles
  • archived_rss_posts
  • newsletter_campaigns
  • articles
  • secondary_articles
  • rss_posts
  • post_ratings
  • rss_feeds
  • app_settings
  • advertisements
  • campaign_advertisements
  • archived_articles
  • archived_rss_posts

Non-Scoped Tables (publication_id not needed):

无需租户隔离的表（不需要publication_id）：

  • newsletters
    (top-level tenant table)
  • System-wide configuration tables
  • newsletters
    （顶层租户表）
  • 系统级配置表

Rule #2: Use supabaseAdmin for Server-Side Operations

规则2：仅在服务器端使用supabaseAdmin

NEVER expose service role key client-side.
typescript
// ✅ CORRECT - Server-side API route or Server Action
import { supabaseAdmin } from '@/lib/supabase'

export async function POST(request: NextRequest) {
  const { data } = await supabaseAdmin
    .from('newsletter_campaigns')
    .select('*')
    .eq('publication_id', newsletterId)

  return NextResponse.json({ data })
}

// ❌ WRONG - Never in client components
'use client'
import { supabaseAdmin } from '@/lib/supabase'  // ❌ Security risk!

export default function ClientComponent() {
  // This exposes service role key to browser
  const { data } = await supabaseAdmin.from('...').select()
}
Where to use supabaseAdmin:
  • ✅ API routes (
    app/api/**/*.ts
    )
  • ✅ Server Actions (
    'use server'
    functions)
  • ✅ Server Components (without 
    'use client'
    )
  • ✅ Background jobs/cron
  • ✅ Workflow steps
Where NOT to use:
  • ❌ Client Components (
    'use client'
    )
  • ❌ Browser-executed code
  • ❌ Public-facing pages
绝对不要在客户端暴露服务角色密钥。
typescript
// ✅ 正确示例 - 服务器端API路由或Server Action
import { supabaseAdmin } from '@/lib/supabase'

export async function POST(request: NextRequest) {
  const { data } = await supabaseAdmin
    .from('newsletter_campaigns')
    .select('*')
    .eq('publication_id', newsletterId)

  return NextResponse.json({ data })
}

// ❌ 错误示例 - 绝不能在客户端组件中使用
'use client'
import { supabaseAdmin } from '@/lib/supabase'  // ❌ 存在安全风险！

export default function ClientComponent() {
  // 会将服务角色密钥暴露给浏览器
  const { data } = await supabaseAdmin.from('...').select()
}
supabaseAdmin的适用场景:
  • ✅ API路由（
    app/api/**/*.ts
    ）
  • ✅ Server Action（
    'use server'
    函数）
  • ✅ 服务器组件（无
    'use client'
    标识）
  • ✅ 后台任务/定时任务
  • ✅ 工作流步骤
supabaseAdmin的禁用场景:
  • ❌ 客户端组件（
    'use client'
    标识）
  • ❌ 浏览器执行的代码
  • ❌ 面向公众的页面

Rule #3: Avoid SELECT *

规则3：避免使用SELECT *

Only select the fields you need.
typescript
// ✅ CORRECT - Specific fields
const { data } = await supabaseAdmin
  .from('articles')
  .select('id, headline, article_text, is_active')
  .eq('publication_id', newsletterId)
  .eq('campaign_id', campaignId)

// ❌ WRONG - Fetches all columns (performance impact)
const { data } = await supabaseAdmin
  .from('articles')
  .select('*')
  .eq('publication_id', newsletterId)
  .eq('campaign_id', campaignId)
Exception: When you genuinely need all columns for data operations.
仅选择你需要的字段。
typescript
// ✅ 正确示例 - 指定字段
const { data } = await supabaseAdmin
  .from('articles')
  .select('id, headline, article_text, is_active')
  .eq('publication_id', newsletterId)
  .eq('campaign_id', campaignId)

// ❌ 错误示例 - 获取所有列（影响性能）
const { data } = await supabaseAdmin
  .from('articles')
  .select('*')
  .eq('publication_id', newsletterId)
  .eq('campaign_id', campaignId)
例外情况：当你确实需要所有列进行数据操作时。

Rule #4: Always Check for Errors

规则4：始终检查错误

Never assume database operations succeed.
typescript
// ✅ CORRECT - Check for errors
const { data, error } = await supabaseAdmin
  .from('newsletter_campaigns')
  .select('id, status')
  .eq('publication_id', newsletterId)
  .eq('id', campaignId)
  .single()

if (error) {
  console.error('[DB] Query failed:', error.message)
  throw new Error('Failed to fetch campaign')
}

if (!data) {
  console.log('[DB] No campaign found')
  return null
}

// Now safe to use data
return data

// ❌ WRONG - No error handling
const { data } = await supabaseAdmin
  .from('newsletter_campaigns')
  .select('id, status')
  .eq('id', campaignId)
  .single()

return data.status  // ❌ Crashes if error or data is null
永远不要假设数据库操作一定会成功。
typescript
// ✅ 正确示例 - 检查错误
const { data, error } = await supabaseAdmin
  .from('newsletter_campaigns')
  .select('id, status')
  .eq('publication_id', newsletterId)
  .eq('id', campaignId)
  .single()

if (error) {
  console.error('[DB] 查询失败:', error.message)
  throw new Error('获取营销活动失败')
}

if (!data) {
  console.log('[DB] 未找到营销活动')
  return null
}

// 现在可以安全使用data
return data

// ❌ 错误示例 - 未处理错误
const { data } = await supabaseAdmin
  .from('newsletter_campaigns')
  .select('id, status')
  .eq('id', campaignId)
  .single()

return data.status  // ❌ 若出现错误或data为null会崩溃

Database Query Patterns

数据库查询模式

Standard Query Pattern

标准查询模式

typescript
const { data, error } = await supabaseAdmin
  .from('table_name')
  .select('field1, field2, field3')
  .eq('publication_id', newsletterId)  // ✅ ALWAYS for tenant tables
  .eq('other_field', value)
  .single()  // or .maybeSingle() if record might not exist

if (error) {
  console.error('[DB] Query error:', error.message)
  throw new Error(`Database query failed: ${error.message}`)
}

if (!data) {
  console.log('[DB] No record found')
  return null
}

return data
typescript
const { data, error } = await supabaseAdmin
  .from('table_name')
  .select('field1, field2, field3')
  .eq('publication_id', newsletterId)  // ✅ 租户表必须包含
  .eq('other_field', value)
  .single()  // 若记录可能不存在，使用.maybeSingle()

if (error) {
  console.error('[DB] 查询错误:', error.message)
  throw new Error(`数据库查询失败: ${error.message}`)
}

if (!data) {
  console.log('[DB] 未找到记录')
  return null
}

return data

Insert Pattern

插入模式

typescript
const { data, error } = await supabaseAdmin
  .from('articles')
  .insert({
    publication_id: newsletterId,  // ✅ REQUIRED
    campaign_id: campaignId,
    headline: 'Article headline',
    article_text: 'Content here',
    is_active: false
  })
  .select()
  .single()

if (error) {
  console.error('[DB] Insert failed:', error.message)
  throw new Error('Failed to create article')
}

return data
typescript
const { data, error } = await supabaseAdmin
  .from('articles')
  .insert({
    publication_id: newsletterId,  // ✅ 必须包含
    campaign_id: campaignId,
    headline: '文章标题',
    article_text: '文章内容',
    is_active: false
  })
  .select()
  .single()

if (error) {
  console.error('[DB] 插入失败:', error.message)
  throw new Error('创建文章失败')
}

return data

Update Pattern

更新模式

typescript
const { data, error } = await supabaseAdmin
  .from('articles')
  .update({
    is_active: true,
    updated_at: new Date().toISOString()
  })
  .eq('id', articleId)
  .eq('publication_id', newsletterId)  // ✅ REQUIRED - prevents updating other tenants
  .select()
  .single()

if (error) {
  console.error('[DB] Update failed:', error.message)
  throw new Error('Failed to update article')
}

return data
typescript
const { data, error } = await supabaseAdmin
  .from('articles')
  .update({
    is_active: true,
    updated_at: new Date().toISOString()
  })
  .eq('id', articleId)
  .eq('publication_id', newsletterId)  // ✅ 必须包含 - 防止修改其他租户的数据
  .select()
  .single()

if (error) {
  console.error('[DB] 更新失败:', error.message)
  throw new Error('更新文章失败')
}

return data

Delete Pattern

删除模式

typescript
const { error } = await supabaseAdmin
  .from('rss_posts')
  .delete()
  .eq('id', postId)
  .eq('publication_id', newsletterId)  // ✅ REQUIRED - prevents deleting other tenants' data

if (error) {
  console.error('[DB] Delete failed:', error.message)
  throw new Error('Failed to delete post')
}
typescript
const { error } = await supabaseAdmin
  .from('rss_posts')
  .delete()
  .eq('id', postId)
  .eq('publication_id', newsletterId)  // ✅ 必须包含 - 防止删除其他租户的数据

if (error) {
  console.error('[DB] 删除失败:', error.message)
  throw new Error('删除帖子失败')
}

Join Pattern (Relationships)

关联查询模式

typescript
const { data, error } = await supabaseAdmin
  .from('newsletter_campaigns')
  .select(`
    id,
    status,
    date,
    articles (
      id,
      headline,
      is_active
    ),
    secondary_articles (
      id,
      headline,
      is_active
    )
  `)
  .eq('publication_id', newsletterId)  // ✅ REQUIRED on parent table
  .eq('id', campaignId)
  .single()
typescript
const { data, error } = await supabaseAdmin
  .from('newsletter_campaigns')
  .select(`
    id,
    status,
    date,
    articles (
      id,
      headline,
      is_active
    ),
    secondary_articles (
      id,
      headline,
      is_active
    )
  `)
  .eq('publication_id', newsletterId)  // ✅ 必须在父表中添加
  .eq('id', campaignId)
  .single()

Common Mistakes

常见错误

❌ Forgetting publication_id Filter

❌ 忘记添加publication_id过滤条件

typescript
// This query can access ANY campaign from ANY tenant!
const { data } = await supabaseAdmin
  .from('newsletter_campaigns')
  .select('*')
  .eq('id', campaignId)  // ❌ Missing publication_id
typescript
// 该查询可以访问任意租户的任意营销活动！
const { data } = await supabaseAdmin
  .from('newsletter_campaigns')
  .select('*')
  .eq('id', campaignId)  // ❌ 缺少publication_id

❌ Using supabaseAdmin Client-Side

❌ 在客户端使用supabaseAdmin

typescript
'use client'

// ❌ Exposes service role key to browser
export default function MyComponent() {
  const { data } = await supabaseAdmin.from('...').select()
}
typescript
'use client'

// ❌ 会将服务角色密钥暴露给浏览器
export default function MyComponent() {
  const { data } = await supabaseAdmin.from('...').select()
}

❌ No Error Handling

❌ 未处理错误

typescript
// ❌ No error check - will crash on failure
const { data } = await supabaseAdmin.from('...').select().single()
const status = data.status  // Crashes if data is null
typescript
// ❌ 未检查错误 - 失败时会崩溃
const { data } = await supabaseAdmin.from('...').select().single()
const status = data.status  // 若data为null会崩溃

❌ Using SELECT *

❌ 使用SELECT *

typescript
// ❌ Fetches unnecessary data, impacts performance
const { data } = await supabaseAdmin
  .from('articles')
  .select('*')
typescript
// ❌ 获取不必要的数据，影响性能
const { data } = await supabaseAdmin
  .from('articles')
  .select('*')

Quick Reference

快速参考

✅ DO:
  • Always filter by 
    publication_id
    on tenant-scoped tables
  • Use 
    supabaseAdmin
    only server-side
  • Select specific fields
  • Check for errors
  • Use 
    .single()
    for single records
  • Use 
    .maybeSingle()
    if record might not exist
  • Log errors with 
    [DB]
    prefix
❌ DON'T:
  • Skip 
    publication_id
    filter
  • Use 
    supabaseAdmin
    in client components
  • Use 
    SELECT *
    without reason
  • Ignore errors
  • Assume data exists
  • Expose service keys client-side
✅ 需要做:
  • 租户范围表始终通过
    publication_id
    过滤
  • 仅在服务器端使用
    supabaseAdmin
  • 选择特定字段
  • 检查错误
  • 单条记录使用
    .single()
  • 记录可能不存在时使用
    .maybeSingle()
  • 错误日志添加
    [DB]
    前缀
❌ 禁止做:
  • 省略
    publication_id
    过滤条件
  • 在客户端组件中使用
    supabaseAdmin
  • 无理由使用
    SELECT *
  • 忽略错误
  • 假设数据一定存在
  • 在客户端暴露服务密钥

Error Recovery

错误排查

If you see "Row level security policy violated":
  1. Check if you're filtering by 
    publication_id
  2. Verify you're using 
    supabaseAdmin
    (not client)
  3. Confirm you're on server-side (API route/Server Action)
If you see "column does not exist":
  1. Verify column name spelling
  2. Check if field exists in database schema
  3. Ensure you're querying the correct table
Skill Status: ACTIVE GUARDRAIL ✅ Enforcement Level: BLOCK (Critical) Line Count: < 500 ✅ Purpose: Prevent multi-tenant data leakage ✅
如果出现“Row level security policy violated”错误：
  1. 检查是否添加了
    publication_id
    过滤条件
  2. 确认使用的是
    supabaseAdmin
    （而非客户端实例）
  3. 确认代码运行在服务器端（API路由/Server Action）
如果出现“column does not exist”错误：
  1. 验证列名拼写
  2. 检查字段是否存在于数据库架构中
  3. 确保查询的是正确的表
准则状态: 启用防护 ✅ 执行级别: 阻止操作（关键） 代码行数: < 500 ✅ 目的: 防止多租户数据泄露 ✅