🗄️ database — Unified Database Department

One head skill for all database work. Query read-only, diagnose the slow query, index what matters, guard the rows, keep production healthy. Every mode runs handsfree — defaults are stated inline, assumptions recorded in the deliverable, zero questions asked.

Default stack: self-hosted Postgres 16+ with pg_stat_statements enabled for the diagnose/index/guard/operate modes; the query mode works with any SQL database that has a connection config. Supabase-approved references linked per mode where applicable. Supabase platform operations (dashboard, Auth service, Edge Functions) are linked out, never duplicated.

Modes

Every invocation resolves to exactly one mode. Match the request, then load only the matched reference:

Only the resolved mode's reference is loaded — the rest stay on disk, saving tokens on every run.

Deep-dive references (load on topic, no separate mode)

Mode behavior is fixed; these extend a resolved mode when the request dips deeper than its playbook:

• Vector/hybrid search (embeddings, HNSW vs IVF, payload filters, hybrid FTS+vector, RAG chunking; Qdrant default, pgvector fallback) → references/vector-search.md
• Analytical query modes (columnar/DuckDB vs OLTP routing, aggregation-window analysis) and Neon/Supabase autoscaling + zero-downtime migration → references/analytical.md

Load alongside operate for vector basics or query for aggregation routing.

Support references (load on topic)

Loadable by topic; also routable as /database-<file> commands (not separate routed modes):

• tuning.md

Verification

Run before reporting completion:

• Query executed read-only (SELECT/SHOW/EXPLAIN only — no INSERT/UPDATE/DELETE)
• Query config path verified — references/query.md config discovery resolved
• Output row/col limits respected (default 100 rows × 10 cols)
• Single-statement enforced (no ;-chained writes)
• Migrations checked with migration guard (zero table rewrites, lock_timeout set, concurrent indexing, non-blocking FKs)
• Connection closed after query
• For diagnosis: pg_stat_statements ranked before EXPLAIN ANALYZE

For connection testing: verify-connection live-checks the configured DSN, confirms read-write intent is absent.

Full audit-mode spec: skills/references/audit-mode-guidance.md.

When to Use

• Running read-only SELECT/SHOW/EXPLAIN queries against a configured database.
• Exploring database schemas: listing tables, columns, indexes, foreign keys.
• Diagnosing a slow query, high CPU, timeouts, or connection exhaustion.
• Designing indexes: missing, composite, covering, partial, FK, or vector.
• Writing or verifying RLS policies and secure views.
• Choosing PK strategy, identifiers, and constraints for a new schema.
• Sizing connection pools, partitioning tables, triaging locks, tuning vacuum.
• Designing vector/hybrid search, routing analytical (columnar) queries, or planning Neon/Supabase autoscaling and zero-downtime migrations.

Anti-Triggers

• Backend application code around the database (queries in app code, ORM models) → webdev.
• Infrastructure and deploy pipelines (servers, CI/CD, backups, restores) → devops.
• Supabase platform operations (dashboard settings, Auth service config, Edge Functions) → link out to Supabase docs, never duplicate here.
• Pre-launch verification of the app built on the database → qa-launch.

Quick Reference

Routing ladder (decide before any mode)

Order: query → diagnose → index → guard → operate. Get the data, find the bottleneck, fix access paths, lock down rows, then harden operations.

Verification gate (every mode)

• The target is named: exact query, table, policy, or connection symptom — never "the database is slow".
• Every recommendation cites the mechanism (plan node, stat row, policy test) that proves it.
• Index changes state write cost and use CONCURRENTLY on production tables.
• RLS changes ship with a verification test showing allowed and denied rows.
• Production commands are read-only first; mutating commands are flagged before delivery.
• Query mode: credentials never appear in output; connection config is permission-checked before use.

Suite contracts

• Application code using the tuned queries → webdev.
• Deploying migrations, pools, or partitions → devops.
• Verifying the app release built on this database → qa-launch.

Procedure

1. Intake. establish the target (query text, table, policy, or symptom) and the environment (self-hosted version or Supabase project ref, which database type) before touching anything — tuning without a target optimizes noise.
2. Resolve the mode. Match the request against the mode table; exactly one mode. Ambiguous → default to diagnose, state the assumption, proceed.
3. Execute the mode playbook. Load references/<mode>.md and follow its Intake → Deliverable → Procedure → Quality gate in order.
4. Gate and deliver. Pass this file's Verification checklist plus the mode's quality gate, then deliver SQL plus evidence where the operator expects it.

Pitfalls

• Tuning without EXPLAIN (ANALYZE, BUFFERS) — guessing which index helps.
• Adding indexes for every filter — write amplification and bloat; prove with the plan.
• RLS policies calling auth.uid() per row instead of (select auth.uid()) — full-scan penalty on every query.
• SECURITY DEFINER functions or security_invoker = false views in public schemas — privilege escalation path.
• Pooling in session mode by default — exhausts connections; transaction mode fits most apps.
• Long transactions and missing FK indexes — lock queues and cascade scans.
• Random UUIDv4 PKs on large tables — index fragmentation; prefer identity or UUIDv7.
• Running write queries through the query mode — the query mode is read-only by default; INSERT/UPDATE/DELETE/MERGE are rejected at the gate.
• Credentials in error output — query mode sanitizes authentication errors before display.

Verification

• Exactly one mode resolved and its reference playbook followed end to end.
• Target named: query, table, policy, or symptom with environment.
• Every recommendation traces to plan output, stat row, or test evidence.
• Index DDL states write cost and uses CONCURRENTLY for production.
• RLS work ships passing verification tests (allowed and denied rows).
• Query mode: read-only enforced, credentials sanitized, timeout/row-cap honored.
• No Supabase platform ops duplicated; links used instead.
• App-code, infra, and release work routed to webdev, devops, qa-launch.