Imported from BrewHubPHL/supabase-specialist (SKILL.md). Install upstream with npx skills add BrewHubPHL/supabase-specialist. Copyright stays with the author.

Supabase Specialist

Production Postgres and Supabase guidance for agents building sovereign, self-hosted, or managed Supabase stacks. This skill prioritizes database-enforced security and server-side truth over application-layer hope.

Overview

Supabase is Postgres with batteries: Auth, RLS, Realtime, Storage, and edge-friendly clients. The specialist skill teaches agents to:

1. Treat Postgres as the single source of truth for permissions, constraints, and atomicity.
2. Load deep patterns on demand from patterns/ and references/ — never bloat the active context.
3. Apply BrewHub philosophy (sovereignty, kill switches, vertical integration) via abstract integration examples, not live fleet secrets.

Progressive disclosure map

Core Principles

1. The database enforces; the app interprets

If a rule is "the developer should remember to filter by user_id", it belongs in RLS, not markdown. Policies, constraints, and RPCs are the contract. Application code is a consumer.

2. Least privilege per client

Never return a service-role client to an LLM tool factory or pass it to client-side code.

3. Push concurrency to Postgres

Read-modify-write in application memory loses under parallel requests (POS terminals, webhooks, after() blocks, mobile retries). Use:

• INSERT … ON CONFLICT / JSONB || merges in RPCs
• pg_advisory_lock / pg_try_advisory_lock for financial idempotency
• SELECT … FOR UPDATE SKIP LOCKED for job queues

4. Serverless-aware connections

Each warm isolate may open a connection. Without pooling (Supavisor / PgBouncer transaction mode), serverless will exhaust max_connections. Size pools to (CPU cores × 2) + disk_spindles, not concurrent users.

5. Migrations are append-only contracts

Ship schema changes as versioned SQL migrations. Never rely on dashboard edits in production. Test RLS with both authenticated and anon roles before merge.

Best Practices

Schema & RLS

• Enable RLS on every user-facing table; default deny until policies exist.
• Use SECURITY DEFINER helper functions (is_staff(), is_manager()) for repeated policy logic — but audit them carefully.
• Index every column referenced in USING / WITH CHECK clauses.
• Prefer uuid PKs with gen_random_uuid(); expose human-readable codes (order_number) for lookups — never ilike on UUID columns.
• Use jsonb (not json) for flexible fields; hybrid schema = typed columns + metadata jsonb; promote hot jsonb keys via migration when queried on every path.

Triggers & side effects

• Avoid synchronous counter/cache triggers on hot write paths — use RPCs, event logs, or NOTIFY + worker (TAOP Ch 38).
• Any trigger function: SET search_path = public; ship in versioned migrations, not dashboard-only.

supabase-js

• .maybeSingle() when zero rows is valid; .single() throws and breaks polling loops.
• Select only needed columns; avoid select('*') on wide tables in hot paths.
• Use .rpc() for complex logic — keeps plans stable and permissions centralized.

Auth

• Resolve identity server-side from JWT/session; never trust customer_id from request bodies or LLM tool args.
• Data model first — Postgres is the golden record; model for the domain, not today's screen (TAOP Ch 41).
• Set JWT claims via hooks only when downstream RLS depends on them; document the claim contract in the migration.

Observability

• Enable pg_stat_statements; sample with EXPLAIN (ANALYZE, BUFFERS) on slow queries.
• Watch pg_stat_user_tables (seq scans), connection count, and replication lag on self-hosted stacks.

Common Pitfalls

Full catalog: anti-patterns.md.

BrewHub Integration

BrewHub treats Postgres as the authority layer in a tri-state stack (Next.js UI, serverless API, Python agents). Public repos document patterns, not production hostnames.

Sovereignty alignment

• Self-hostable: schema and RPCs must run on managed Supabase or self-hosted Postgres + Auth stack — no proprietary-only extensions without a fallback.
• Incumbent kill switches: features that hard-depend on a single vendor dashboard (manual RLS edits, console-only cron) are anti-patterns; everything ships as SQL in git.
• Thriving wages as infrastructure: prefer operable runbooks (migrations, pool sizing, backup restore drills) over hero debugging — see patterns/migrations.md.

Kill switches (non-alignment)

Stop and escalate when a proposed change:

• Disables RLS "temporarily" for speed
• Moves authorization solely to the frontend or LLM tool layer
• Introduces service-role reads in user-facing code paths
• Stores secrets in migration files or seed data
• Enables Postgres extensions only via dashboard (no migration) — breaks sovereign/self-host parity

Vertical integration touchpoints

Abstract wiring lives in examples/brew-hub-integration.md:

• Dual-client tool factories (anon read / writer insert)
• Rate limit RPCs (check_rate_limit)
• Payment webhook advisory locks
• Staff/manager SECURITY DEFINER gates

Optional product-specific overrides: brew-hub-overrides/ (keep redacted in public forks).

Integration Patterns with Other Specialists

When a task spans specialists, this skill owns: schema, RLS, RPC signatures, query plans, and migration ordering.

Onboarding for Book-to-Skill Outputs

Use references/book-summaries/ for modular chapter drops. Each file should follow:

---
source: "Book Title, Chapter N"
topics: [rls, indexing]
priority: high
---

## Summary
3–5 sentences.

## Actionable rules
- Rule 1 → link to patterns/*.md if we codify it

## Glossary additions
| Term | Definition |

Workflow

1. Add references/book-summaries/<slug>.md — do not paste the full book into SKILL.md.
2. Extract durable rules into patterns/ when they become team standards.
3. Move repeated mistakes into anti-patterns.md.
4. Run node scripts/validate-skill.mjs before PR.

See references/book-summaries/README.md for the template.