外键与关系

-- One-to-many relationship
create table public.comments (
  id uuid default gen_random_uuid() primary key,
  created_at timestamp with time zone default now() not null,
  item_id uuid references public.items(id) on delete cascade not null,
  user_id uuid references auth.users(id) on delete cascade not null,
  content text not null
);

-- Many-to-many relationship
create table public.item_tags (
  item_id uuid references public.items(id) on delete cascade,
  tag_id uuid references public.tags(id) on delete cascade,
  primary key (item_id, tag_id)
);

行级安全

基本 RLS 模式

-- Enable RLS
alter table public.items enable row level security;

-- Users can read their own items
create policy "Users can read own items"
  on public.items for select
  using (auth.uid() = user_id);

-- Users can insert their own items
create policy "Users can insert own items"
  on public.items for insert
  with check (auth.uid() = user_id);

-- Users can update their own items
create policy "Users can update own items"
  on public.items for update
  using (auth.uid() = user_id)
  with check (auth.uid() = user_id);

-- Users can delete their own items
create policy "Users can delete own items"
  on public.items for delete
  using (auth.uid() = user_id);

高级 RLS 模式

-- Public read, authenticated write
create policy "Anyone can read published items"
  on public.items for select
  using (status = 'published');

create policy "Authenticated users can insert"
  on public.items for insert
  to authenticated
  with check (true);

-- Role-based access
create policy "Admins can do everything"
  on public.items for all
  using (
    exists (
      select 1 from public.user_roles
      where user_id = auth.uid()
      and role = 'admin'
    )
  );

-- Shared access
create policy "Users can read shared items"
  on public.items for select
  using (
    auth.uid() = user_id
    or exists (
      select 1 from public.item_shares
      where item_id = items.id
      and shared_with = auth.uid()
    )
  );

匿名/访客访问

-- Allow anonymous reads
create policy "Anonymous can read public content"
  on public.items for select
  to anon
  using (status = 'published');

-- Allow anonymous inserts (for guest mode)
create policy "Anonymous can create items"
  on public.items for insert
  to anon
  with check (true);

客户端集成

设置客户端 (Next.js)

// lib/supabase/client.ts
import { createBrowserClient } from '@supabase/ssr'

export function createClient() {
  return createBrowserClient(
    process.env.NEXT_PUBLIC_SUPABASE_URL!,
    process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!
  )
}

// lib/supabase/server.ts
import { createServerClient } from '@supabase/ssr'
import { cookies } from 'next/headers'

export function createServerClient() {
  const cookieStore = cookies()

  return createServerClient(
    process.env.NEXT_PUBLIC_SUPABASE_URL!,
    process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!,
    {
      cookies: {
        get(name: string) {
          return cookieStore.get(name)?.value
        },
      },
    }
  )
}

CRUD 操作

// Query data
const { data, error } = await supabase
  .from('items')
  .select('*')
  .eq('status', 'published')
  .order('created_at', { ascending: false })
  .limit(10)

// Insert data
const { data, error } = await supabase
  .from('items')
  .insert({ title: 'New Item', user_id: userId })
  .select()
  .single()

// Update data
const { data, error } = await supabase
  .from('items')
  .update({ title: 'Updated Title' })
  .eq('id', itemId)
  .select()
  .single()

// Delete data
const { error } = await supabase
  .from('items')
  .delete()
  .eq('id', itemId)

// Complex joins
const { data, error } = await supabase
  .from('items')
  .select(`
    *,
    comments (
      id,
      content,
      user:user_id (
        email
      )
    )
  `)
  .eq('user_id', userId)

实时订阅

// Subscribe to changes
const channel = supabase
  .channel('items-changes')
  .on(
    'postgres_changes',
    {
      event: '*',
      schema: 'public',
      table: 'items',
      filter: `user_id=eq.${userId}`,
    },
    (payload) => {
      console.log('Change received!', payload)
      // Update local state
    }
  )
  .subscribe()

// Cleanup
channel.unsubscribe()

身份验证

邮箱/密码认证

// Sign up
const { data, error } = await supabase.auth.signUp({
  email: 'user@example.com',
  password: 'password123',
  options: {
    data: {
      display_name: 'User Name',
    },
  },
})

// Sign in
const { data, error } = await supabase.auth.signInWithPassword({
  email: 'user@example.com',
  password: 'password123',
})

// Sign out
const { error } = await supabase.auth.signOut()

// Get current user
const { data: { user } } = await supabase.auth.getUser()

OAuth 提供商

// Google OAuth
const { data, error } = await supabase.auth.signInWithOAuth({
  provider: 'google',
  options: {
    redirectTo: `${window.location.origin}/auth/callback`,
  },
})

// Handle callback
// app/auth/callback/route.ts
export async function GET(request: Request) {
  const { searchParams } = new URL(request.url)
  const code = searchParams.get('code')

  if (code) {
    const supabase = createServerClient()
    await supabase.auth.exchangeCodeForSession(code)
  }

  return NextResponse.redirect(new URL('/dashboard', request.url))
}

认证中间件

// middleware.ts
import { createServerClient } from '@supabase/ssr'
import { NextResponse } from 'next/server'
import type { NextRequest } from 'next/server'

export async function middleware(request: NextRequest) {
  const response = NextResponse.next()

  const supabase = createServerClient(
    process.env.NEXT_PUBLIC_SUPABASE_URL!,
    process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!,
    {
      cookies: {
        get(name: string) {
          return request.cookies.get(name)?.value
        },
        set(name: string, value: string, options: any) {
          response.cookies.set(name, value, options)
        },
        remove(name: string, options: any) {
          response.cookies.set(name, '', { ...options, maxAge: 0 })
        },
      },
    }
  )

  const { data: { user } } = await supabase.auth.getUser()

  // Redirect to login if not authenticated
  if (!user && request.nextUrl.pathname.startsWith('/dashboard')) {
    return NextResponse.redirect(new URL('/login', request.url))
  }

  return response
}

export const config = {
  matcher: ['/dashboard/:path*'],
}

边缘函数

基本边缘函数

// supabase/functions/hello/index.ts
import { serve } from 'https://deno.land/std@0.168.0/http/server.ts'
import { createClient } from 'https://esm.sh/@supabase/supabase-js@2'

serve(async (req) => {
  try {
    // Get Supabase client
    const supabase = createClient(
      Deno.env.get('SUPABASE_URL') ?? '',
      Deno.env.get('SUPABASE_ANON_KEY') ?? '',
      {
        auth: {
          autoRefreshToken: false,
          persistSession: false,
        },
      }
    )

    // Get user from auth header
    const authHeader = req.headers.get('Authorization')
    const token = authHeader?.replace('Bearer ', '')
    const { data: { user } } = await supabase.auth.getUser(token)

    if (!user) {
      return new Response(
        JSON.stringify({ error: 'Unauthorized' }),
        { status: 401, headers: { 'Content-Type': 'application/json' } }
      )
    }

    // Your logic here
    const { data, error } = await supabase
      .from('items')
      .select('*')
      .eq('user_id', user.id)

    return new Response(
      JSON.stringify({ data }),
      { headers: { 'Content-Type': 'application/json' } }
    )
  } catch (error) {
    return new Response(
      JSON.stringify({ error: error.message }),
      { status: 500, headers: { 'Content-Type': 'application/json' } }
    )
  }
})

类型生成

# Generate TypeScript types
npx supabase gen types typescript --project-id your-project-id > types/supabase.ts

# Use in code
import { Database } from '@/types/supabase'

type Item = Database['public']['Tables']['items']['Row']
type ItemInsert = Database['public']['Tables']['items']['Insert']
type ItemUpdate = Database['public']['Tables']['items']['Update']

常见模式

软删除

alter table public.items add column deleted_at timestamp with time zone;

create policy "Users cannot see deleted items"
  on public.items for select
  using (deleted_at is null);

-- Soft delete function
create or replace function soft_delete_item(item_id uuid)
returns void as $$
begin
  update public.items
  set deleted_at = now()
  where id = item_id;
end;
$$ language plpgsql security definer;

审计日志

create table public.audit_logs (
  id uuid default gen_random_uuid() primary key,
  created_at timestamp with time zone default now() not null,
  user_id uuid references auth.users(id),
  table_name text not null,
  record_id uuid not null,
  action text not null,
  changes jsonb
);

-- Trigger function
create or replace function public.audit_trigger()
returns trigger as $$
begin
  insert into public.audit_logs (user_id, table_name, record_id, action, changes)
  values (
    auth.uid(),
    TG_TABLE_NAME,
    NEW.id,
    TG_OP,
    to_jsonb(NEW) - to_jsonb(OLD)
  );
  return NEW;
end;
$$ language plpgsql security definer;

故障排除

常见问题

1. 401 错误：检查 RLS 策略，确保用户已通过身份验证
2. 403 错误：RLS 策略阻止了操作
3. 行未找到：策略可能将其过滤掉了
4. 连接问题：检查 URL 和 API 密钥
5. 类型不匹配：模式更改后重新生成类型

调试 RLS

-- Test as specific user
set request.jwt.claims = '{"sub": "user-uuid-here"}';

-- Check what policies apply
select * from pg_policies where tablename = 'items';

-- Disable RLS temporarily (for testing only!)
alter table public.items disable row level security;

最佳实践清单

• 对所有包含用户数据的表启用 RLS
• 为外键和频繁查询的列创建索引
• 对主键使用 UUID
• 添加 created_at 和 updated_at 时间戳
• 对重要数据实现软删除
• 对枚举类字段使用检查约束
• 生成并使用 TypeScript 类型
• 彻底测试 RLS 策略
• 仅在服务器端使用服务角色密钥
• 实现适当的错误处理
• 为敏感操作添加审计日志
• 对多步骤操作使用事务

何时使用此技能

在以下情况下调用此技能：

• 设计数据库模式
• 创建或调试 RLS 策略
• 设置身份验证
• 构建边缘函数
• 实现实时功能
• 排除 Supabase 问题
• 优化数据库查询
• 设置类型生成

每周安装数

128

仓库

eddiebe147/clau…settings

首次出现

2026年1月22日

安全审计

Gen Agent Trust HubFailSocketPassSnykWarn

安装于

claude-code101

opencode82

gemini-cli78

antigravity78

cursor74

codex69

Foreign Keys & Relations

-- One-to-many relationship
create table public.comments (
  id uuid default gen_random_uuid() primary key,
  created_at timestamp with time zone default now() not null,
  item_id uuid references public.items(id) on delete cascade not null,
  user_id uuid references auth.users(id) on delete cascade not null,
  content text not null
);

-- Many-to-many relationship
create table public.item_tags (
  item_id uuid references public.items(id) on delete cascade,
  tag_id uuid references public.tags(id) on delete cascade,
  primary key (item_id, tag_id)
);

Row Level Security (RLS)

Basic RLS Patterns

-- Enable RLS
alter table public.items enable row level security;

-- Users can read their own items
create policy "Users can read own items"
  on public.items for select
  using (auth.uid() = user_id);

-- Users can insert their own items
create policy "Users can insert own items"
  on public.items for insert
  with check (auth.uid() = user_id);

-- Users can update their own items
create policy "Users can update own items"
  on public.items for update
  using (auth.uid() = user_id)
  with check (auth.uid() = user_id);

-- Users can delete their own items
create policy "Users can delete own items"
  on public.items for delete
  using (auth.uid() = user_id);

Advanced RLS Patterns

-- Public read, authenticated write
create policy "Anyone can read published items"
  on public.items for select
  using (status = 'published');

create policy "Authenticated users can insert"
  on public.items for insert
  to authenticated
  with check (true);

-- Role-based access
create policy "Admins can do everything"
  on public.items for all
  using (
    exists (
      select 1 from public.user_roles
      where user_id = auth.uid()
      and role = 'admin'
    )
  );

-- Shared access
create policy "Users can read shared items"
  on public.items for select
  using (
    auth.uid() = user_id
    or exists (
      select 1 from public.item_shares
      where item_id = items.id
      and shared_with = auth.uid()
    )
  );

Anonymous/Guest Access

-- Allow anonymous reads
create policy "Anonymous can read public content"
  on public.items for select
  to anon
  using (status = 'published');

-- Allow anonymous inserts (for guest mode)
create policy "Anonymous can create items"
  on public.items for insert
  to anon
  with check (true);

Client Integration

Setup Client (Next.js)

// lib/supabase/client.ts
import { createBrowserClient } from '@supabase/ssr'

export function createClient() {
  return createBrowserClient(
    process.env.NEXT_PUBLIC_SUPABASE_URL!,
    process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!
  )
}

// lib/supabase/server.ts
import { createServerClient } from '@supabase/ssr'
import { cookies } from 'next/headers'

export function createServerClient() {
  const cookieStore = cookies()

  return createServerClient(
    process.env.NEXT_PUBLIC_SUPABASE_URL!,
    process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!,
    {
      cookies: {
        get(name: string) {
          return cookieStore.get(name)?.value
        },
      },
    }
  )
}

CRUD Operations

// Query data
const { data, error } = await supabase
  .from('items')
  .select('*')
  .eq('status', 'published')
  .order('created_at', { ascending: false })
  .limit(10)

// Insert data
const { data, error } = await supabase
  .from('items')
  .insert({ title: 'New Item', user_id: userId })
  .select()
  .single()

// Update data
const { data, error } = await supabase
  .from('items')
  .update({ title: 'Updated Title' })
  .eq('id', itemId)
  .select()
  .single()

// Delete data
const { error } = await supabase
  .from('items')
  .delete()
  .eq('id', itemId)

// Complex joins
const { data, error } = await supabase
  .from('items')
  .select(`
    *,
    comments (
      id,
      content,
      user:user_id (
        email
      )
    )
  `)
  .eq('user_id', userId)

Real-time Subscriptions

// Subscribe to changes
const channel = supabase
  .channel('items-changes')
  .on(
    'postgres_changes',
    {
      event: '*',
      schema: 'public',
      table: 'items',
      filter: `user_id=eq.${userId}`,
    },
    (payload) => {
      console.log('Change received!', payload)
      // Update local state
    }
  )
  .subscribe()

// Cleanup
channel.unsubscribe()

Authentication

Email/Password Auth

// Sign up
const { data, error } = await supabase.auth.signUp({
  email: 'user@example.com',
  password: 'password123',
  options: {
    data: {
      display_name: 'User Name',
    },
  },
})

// Sign in
const { data, error } = await supabase.auth.signInWithPassword({
  email: 'user@example.com',
  password: 'password123',
})

// Sign out
const { error } = await supabase.auth.signOut()

// Get current user
const { data: { user } } = await supabase.auth.getUser()

OAuth Providers

// Google OAuth
const { data, error } = await supabase.auth.signInWithOAuth({
  provider: 'google',
  options: {
    redirectTo: `${window.location.origin}/auth/callback`,
  },
})

// Handle callback
// app/auth/callback/route.ts
export async function GET(request: Request) {
  const { searchParams } = new URL(request.url)
  const code = searchParams.get('code')

  if (code) {
    const supabase = createServerClient()
    await supabase.auth.exchangeCodeForSession(code)
  }

  return NextResponse.redirect(new URL('/dashboard', request.url))
}

Auth Middleware

// middleware.ts
import { createServerClient } from '@supabase/ssr'
import { NextResponse } from 'next/server'
import type { NextRequest } from 'next/server'

export async function middleware(request: NextRequest) {
  const response = NextResponse.next()

  const supabase = createServerClient(
    process.env.NEXT_PUBLIC_SUPABASE_URL!,
    process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!,
    {
      cookies: {
        get(name: string) {
          return request.cookies.get(name)?.value
        },
        set(name: string, value: string, options: any) {
          response.cookies.set(name, value, options)
        },
        remove(name: string, options: any) {
          response.cookies.set(name, '', { ...options, maxAge: 0 })
        },
      },
    }
  )

  const { data: { user } } = await supabase.auth.getUser()

  // Redirect to login if not authenticated
  if (!user && request.nextUrl.pathname.startsWith('/dashboard')) {
    return NextResponse.redirect(new URL('/login', request.url))
  }

  return response
}

export const config = {
  matcher: ['/dashboard/:path*'],
}

Edge Functions

Basic Edge Function

// supabase/functions/hello/index.ts
import { serve } from 'https://deno.land/std@0.168.0/http/server.ts'
import { createClient } from 'https://esm.sh/@supabase/supabase-js@2'

serve(async (req) => {
  try {
    // Get Supabase client
    const supabase = createClient(
      Deno.env.get('SUPABASE_URL') ?? '',
      Deno.env.get('SUPABASE_ANON_KEY') ?? '',
      {
        auth: {
          autoRefreshToken: false,
          persistSession: false,
        },
      }
    )

    // Get user from auth header
    const authHeader = req.headers.get('Authorization')
    const token = authHeader?.replace('Bearer ', '')
    const { data: { user } } = await supabase.auth.getUser(token)

    if (!user) {
      return new Response(
        JSON.stringify({ error: 'Unauthorized' }),
        { status: 401, headers: { 'Content-Type': 'application/json' } }
      )
    }

    // Your logic here
    const { data, error } = await supabase
      .from('items')
      .select('*')
      .eq('user_id', user.id)

    return new Response(
      JSON.stringify({ data }),
      { headers: { 'Content-Type': 'application/json' } }
    )
  } catch (error) {
    return new Response(
      JSON.stringify({ error: error.message }),
      { status: 500, headers: { 'Content-Type': 'application/json' } }
    )
  }
})

Type Generation

# Generate TypeScript types
npx supabase gen types typescript --project-id your-project-id > types/supabase.ts

# Use in code
import { Database } from '@/types/supabase'

type Item = Database['public']['Tables']['items']['Row']
type ItemInsert = Database['public']['Tables']['items']['Insert']
type ItemUpdate = Database['public']['Tables']['items']['Update']

Common Patterns

Soft Deletes

alter table public.items add column deleted_at timestamp with time zone;

create policy "Users cannot see deleted items"
  on public.items for select
  using (deleted_at is null);

-- Soft delete function
create or replace function soft_delete_item(item_id uuid)
returns void as $$
begin
  update public.items
  set deleted_at = now()
  where id = item_id;
end;
$$ language plpgsql security definer;

Audit Logs

create table public.audit_logs (
  id uuid default gen_random_uuid() primary key,
  created_at timestamp with time zone default now() not null,
  user_id uuid references auth.users(id),
  table_name text not null,
  record_id uuid not null,
  action text not null,
  changes jsonb
);

-- Trigger function
create or replace function public.audit_trigger()
returns trigger as $$
begin
  insert into public.audit_logs (user_id, table_name, record_id, action, changes)
  values (
    auth.uid(),
    TG_TABLE_NAME,
    NEW.id,
    TG_OP,
    to_jsonb(NEW) - to_jsonb(OLD)
  );
  return NEW;
end;
$$ language plpgsql security definer;

Troubleshooting

Common Issues

1. 401 Errors : Check RLS policies, ensure user is authenticated
2. 403 Errors : RLS policy blocking operation
3. Row not found : Policy may be filtering it out
4. Connection issues : Check URL and API keys
5. Type mismatches : Regenerate types after schema changes

Debugging RLS

-- Test as specific user
set request.jwt.claims = '{"sub": "user-uuid-here"}';

-- Check what policies apply
select * from pg_policies where tablename = 'items';

-- Disable RLS temporarily (for testing only!)
alter table public.items disable row level security;

Best Practices Checklist

• Enable RLS on all tables with user data
• Create indexes for foreign keys and frequently queried columns
• Use UUID for primary keys
• Add created_at and updated_at timestamps
• Implement soft deletes for important data
• Use check constraints for enum-like fields
• Generate and use TypeScript types
• Test RLS policies thoroughly
• Use service role key only server-side
• Implement proper error handling
• Add audit logs for sensitive operations
• Use transactions for multi-step operations

When to Use This Skill

Invoke this skill when:

• Designing database schemas
• Creating or debugging RLS policies
• Setting up authentication
• Building Edge Functions
• Implementing real-time features
• Troubleshooting Supabase issues
• Optimizing database queries
• Setting up type generation

Weekly Installs

128

Repository

eddiebe147/clau…settings

First Seen

Jan 22, 2026

Security Audits

Gen Agent Trust HubFailSocketPassSnykWarn

Installed on

claude-code101

opencode82

gemini-cli78

antigravity78

cursor74

codex69