Menu
Database design: schemas, migrations, queries, indexes, transactions. Normalization, query optimization, migration safety.
Install with Codex or Claude Copy this prompt, paste it into Codex, Claude, or another assistant, and let it review the skill page and install it for you.
Based on SOC occupation classification
Session bootstrap + workflows for Pathfinder semantic navigation tools. Covers: discovery protocol, tool chaining patterns (explore, impact, audit, debug), search optimization, LSP degraded mode, and error recovery.
Playwright browser automation via MCP. Covers E2E testing, UI review, web scraping, screenshot capture, and general browser interaction. MCP-first — CLI is fallback only.
Safe command execution: input sanitization, timeout handling, output capture, error propagation. For spawning processes, shell commands, system calls.
Git conventions: conventional commits, branch naming, PR hygiene, release tagging.
Structured incident workflow: severity classification, triage, diagnosis, mitigation, postmortem, and prevention. Template-driven with blameless review.
Constructs, validates, and traverses a Directed Acyclic Graph (DAG) from scope cards for safe level-based parallel dispatch. Determines execution order via topological sort. Detects cycles and invalid dependencies.
| name | database-design-principles |
| description | Database design: schemas, migrations, queries, indexes, transactions. Normalization, query optimization, migration safety. |
| user-invocable | false |
Normalization: start 3NF, denormalize only with measured perf need. One entity per table. No derived data unless explicit cache.
Naming: tables=plural snake_case (users, task_assignments). Columns=singular snake_case. FKs={table_singular}_id. Indexes=idx_{table}_{cols}. Constraints={type}_{table}_{cols}.
Required columns: id (bigint IDENTITY for single-DB, UUIDv7 for distributed — avoid random UUIDv4), created_at (timestamptz, immutable), updated_at (timestamptz, auto-update).
Data type selection: timestamptz over timestamp. text over varchar(n) unless constraint needed. numeric over float for money. bigint over int for IDs.
Safety: never drop prod columns without deprecation. Never rename directly (add new → migrate → drop old). Always reversible (up + down). Test on prod data copy.
Strategy: 1. additive → 2. backfill → 3. update code → 4. drop old in future migration.
Design-level: ensure indexes cover query access patterns. Validate query plans during schema review. For SQL coding patterns (parameterized queries, SELECT hygiene, N+1 avoidance, timeouts), see @.gemini/skills/sql-idioms/SKILL.md.
Transactions: for multi-row/table ops. Choose appropriate isolation level. Never hold during user interaction or external calls. For locking patterns (deadlock prevention, advisory locks, SKIP LOCKED), see @.gemini/skills/sql-idioms/SKILL.md § Concurrency & Locking.
Tables >100M rows or time-series data → range partition by time. Enables instant partition drops vs slow DELETE.
Row-Level Security: for multi-tenant data, enable RLS + FORCE to enforce database-level tenant isolation. Never rely solely on application-layer filtering. Index columns used in RLS policies.
Least privilege: create separate roles (readonly, writer) with specific table grants. Revoke default public access. Never use superuser for application queries.
Size pool as (CPU cores × 2) + disk spindles. Set idle_in_transaction_session_timeout = 30s. In transaction-mode pooling, avoid named prepared statements (use unnamed or deallocate).
