AGENT SKILL

hamster-database

Supabase database patterns for Hamster Studio. Use when working with schemas, migrations, RLS policies, SQL functions, or database queries. Essential for any database-related work.

License 许可
MIT
所属仓库
gethamster/skills
最近更新
2026-11-05
Stars
★ 4
Forks
0

01 前言 Front Matter

name
hamster-database
version
1.1.0
description
Supabase database patterns for Hamster Studio. Use when working with schemas, migrations, RLS policies, SQL functions, or database queries. Essential for any database-related work.

02 资源文件 5

03 SKILL.md 正文

Hamster Database Patterns

Overview

Database conventions for the Hamster Studio multi-tenant SaaS. Covers Supabase schema design, RLS policies, migrations, and SQL functions.

When to Apply

  • Creating or modifying database tables
  • Writing RLS policies
  • Creating database functions or triggers
  • Running migrations
  • Writing SQL queries

Quick Reference

Task Pattern Reference
Create table Enable RLS, add account_id, use triggers references/rules/schema-patterns.md
Add RLS policy Use helper functions, test all operations references/rules/rls-policies.md
Create migration Use supabase:db:diff, never copy manually references/rules/migration-workflow.md
Write function SECURITY INVOKER, set search_path = '' references/rules/sql-functions.md
SQL style Lowercase keywords, snake_case names references/rules/sql-style-guide.md

Core Architecture

Multi-Tenant Data Model

accounts (personal + team)
    ↓ account_id FK
feature_tables (all user data)
    ↓ RLS policies
has_role_on_account() / has_permission()

Rule: ALL feature data MUST have account_id foreign key to accounts.

RLS Helper Functions

Function Use Case Example
has_role_on_account(account_id) Team membership SELECT, basic access
has_permission(user_id, account_id, permission) Specific capability INSERT, UPDATE, DELETE
is_account_owner(account_id) Owner-only actions Destructive operations

Standard Table Template

create table if not exists public.my_feature (
  id uuid unique not null default extensions.uuid_generate_v4(),
  account_id uuid references public.accounts(id) on delete cascade not null,
  created_at timestamptz default now() not null,
  updated_at timestamptz default now() not null,
  created_by uuid references auth.users(id),
  updated_by uuid references auth.users(id),
  -- feature columns
  primary key (id)
);

-- CRITICAL: Always enable RLS
alter table public.my_feature enable row level security;

-- Revoke defaults, grant specific
revoke all on public.my_feature from authenticated, service_role;
grant select, insert, update, delete on public.my_feature to authenticated;

-- Add standard triggers
create trigger set_timestamps
  before insert or update on public.my_feature
  for each row execute function public.trigger_set_timestamps();

create trigger set_user_tracking
  before insert or update on public.my_feature
  for each row execute function public.trigger_set_user_tracking();

Migration Workflow (CRITICAL)

ALWAYS use the diff tool, never copy schema files:

# 1. Create/edit schema file
apps/web/supabase/schemas/NN-my-feature.sql

# 2. Reset database to apply schemas
pnpm supabase:web:reset

# 3. Generate migration from diff
pnpm --filter web supabase:db:diff -f my-feature-name

# 4. Generate TypeScript types
pnpm supabase:web:typegen

Migration filenames follow YYYYMMDDHHmmss_description.sql in UTC. db:diff writes the prefix automatically; if a CI ordering check rejects your migration, rename with date -u +%Y%m%d%H%M%S (the -u flag is required). See references/rules/migration-workflow.md.

RLS Policy Patterns

Personal + Team Access (Most Common)

create policy "read" on public.my_feature for select
  to authenticated using (
    account_id = (select auth.uid()) or
    public.has_role_on_account(account_id)
  );

Permission-Based Write

create policy "write" on public.my_feature for insert
  to authenticated with check (
    public.has_permission(auth.uid(), account_id, 'feature.manage'::app_permissions)
  );

UPDATE Requires Both USING and WITH CHECK

create policy "update" on public.my_feature for update
  to authenticated
  using (public.has_permission(auth.uid(), account_id, 'feature.manage'::app_permissions))
  with check (public.has_permission(auth.uid(), account_id, 'feature.manage'::app_permissions));

Function Security

create or replace function public.my_function(param uuid)
returns setof public.my_table
language plpgsql
security invoker  -- Inherit caller's RLS (preferred)
set search_path = ''  -- Prevent injection
as $$
begin
  return query select * from public.my_table where id = param;
end;
$$;

grant execute on function public.my_function(uuid) to authenticated;

Rule: Use security definer ONLY when bypassing RLS is necessary, with explicit auth checks.

Data Access Patterns

Choose the right approach: Queries (default), Views (computed), or Functions (complex).

Pattern Use When Maintenance Cost
Query Simple CRUD, FK relations, filters Low (code change)
View Read-only aggregations, UNIONs Medium (migration)
Function Atomic transactions, complex joins, business logic High (migration)

Use Supabase Queries (Default)

Queries live in code, are easy to modify, and handle FK relationships automatically.

// FK relationships work - no function needed
const { data } = await client
  .from('brief')
  .select(`*, document:document!inner(*)`)
  .eq('account_id', accountId);

// Join through FK for related data
const { data } = await client
  .from('task_dependencies')
  .select(`
    depends_on_task_id,
    tasks!task_dependencies_depends_on_task_id_fkey (id, title, status)
  `)
  .eq('task_id', taskId);

Use Views

For read-only computed data, UNIONs, and pre-joined data.

:warning: CRITICAL: Views and RLS

Views run with the privileges of the view creator by default, bypassing RLS on underlying tables. This is a major security risk that has caused data leaks.

ALWAYS use security_invoker = true on views to inherit the caller's RLS policies.

-- UNION of multiple sources
-- MUST use security_invoker to enforce RLS on underlying tables
create view public.waitlist_merged with (security_invoker = true) as
select ... from waitlist
union all
select ... from accounts where not exists (...);

-- Pre-joined workspace data
create view public.user_accounts with (security_invoker = true) as
select account.id, account.name, membership.account_role
from accounts account
join accounts_memberships membership on account.id = membership.account_id
where membership.user_id = auth.uid();

Why security_invoker = true is mandatory:

  • Without it: View runs as creator → RLS bypassed → data leak
  • With it: View runs as caller → RLS enforced → secure

Use Functions

For atomic transactions, complex multi-table joins, or business logic.

-- Atomic multi-table update (status + positions must succeed together)
create function public.change_status_and_reorder(
  p_account_id uuid, p_task_id uuid, p_new_status text,
  p_task_updates jsonb, p_updated_by uuid
) returns void as $$
begin
  update tasks set status = p_new_status::task_status where id = p_task_id;
  for rec in select * from jsonb_array_elements(p_task_updates) loop
    update tasks set position = (rec ->> 'position')::integer
    where id = (rec ->> 'taskId')::uuid;
  end loop;
end;
$$ language plpgsql;

Call functions with .rpc():

const { data } = await client.rpc('reorder_briefs', {
  p_account_id: accountId,
  p_brief_id: briefId,
  p_new_position: newPosition,
});

Decision Checklist

  1. Can Supabase query builder express this? → Use query
  2. Is it read-only aggregation/UNION? → Use view
  3. Needs atomic multi-table updates? → Use function
  4. Needs recursive CTEs or complex joins? → Use function
  5. Enforces business invariants? → Use function (often as trigger)

Default to queries. Functions require migrations to change—avoid overusing them.

Testing

For database testing patterns, see the hamster-database-testing skill. It covers:

  • Vitest — RLS policies, constraints (via Supabase client, multi-user scenarios)
  • pgTAP — Triggers, functions, schema assertions (via SQL)

Key Files

File Purpose
apps/web/supabase/schemas/ Schema definitions (numbered)
apps/web/supabase/migrations/ Generated migrations
apps/web/supabase/CLAUDE.md Detailed database workflow
packages/supabase/src/database.types.ts Generated TypeScript types

References

  • references/rules/rls-policies.md - RLS policy guidelines and examples
  • references/rules/migration-workflow.md - Migration file conventions
  • references/rules/schema-patterns.md - Table creation patterns
  • references/rules/sql-functions.md - Database function patterns
  • references/rules/sql-style-guide.md - SQL naming and formatting

04 同仓库更多 Skills