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
- 最近更新
- 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
- 参考文档references/rules/migration-workflow.md5.5 KB
- 参考文档references/rules/rls-policies.md8.1 KB
- 参考文档references/rules/schema-patterns.md4.4 KB
- 参考文档references/rules/sql-functions.md4.5 KB
- 参考文档references/rules/sql-style-guide.md4.4 KB
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.sqlin UTC.db:diffwrites the prefix automatically; if a CI ordering check rejects your migration, rename withdate -u +%Y%m%d%H%M%S(the-uflag is required). Seereferences/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 = trueon 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
- Can Supabase query builder express this? → Use query
- Is it read-only aggregation/UNION? → Use view
- Needs atomic multi-table updates? → Use function
- Needs recursive CTEs or complex joins? → Use function
- 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 examplesreferences/rules/migration-workflow.md- Migration file conventionsreferences/rules/schema-patterns.md- Table creation patternsreferences/rules/sql-functions.md- Database function patternsreferences/rules/sql-style-guide.md- SQL naming and formatting
04 同仓库更多 Skills
writing-waterfall-requirements-documents
Write a waterfall requirements document: elicit, structure and review testable, traceable requirements, then baseline the specification before design.
writing-vision-and-values-statements
Write a V2MOM vision a reader can test and a ranked list of values that settles real tradeoffs, so the rest of the document has a clear anchor.
writing-system-prompts-for-claude
Write Claude prompts and CLAUDE.md files that supply exact inputs, scope the task, and define done so the first attempt can be checked.
writing-page-specific-website-copy
Website copywriting by page type: write homepage, landing page, pricing page and feature page copy that fits each page's visitor and single job.
writing-internal-press-releases
How to write an internal press release: the one-page working backwards press release that opens a PR/FAQ document before anything is built.
writing-email-sequences-that-sell
Email sequence copywriting for nurture and sales: give each email one job, write clear subject lines, and close with one specific call to action.