Skip to content

ericrisco/rsc-harness / skills/postgresdb

postgresdb

Verified

Use when PostgreSQL engine behaviour decides the answer — schema and type design, index choice, reading EXPLAIN on a slow query, zero-downtime DDL and backfills, or ops (roles, RLS, pooling, vacuum, partitioning, PITR). PG16, ORM-agnostic. NOT portable query logic (that is `sql`), NOT a managed provider's platform surface (that is `neon`).

FileKindSize
SKILL.mdskill md17.3 KB
evals/README.mdmarkdown2.6 KB
evals/cases.yamlconfig5.9 KB
references/migrations.mdmarkdown11.5 KB
references/operations-and-security.mdmarkdown11.5 KB
references/query-optimization.mdmarkdown13.2 KB
references/schema-and-indexing.mdmarkdown14.1 KB
scripts/verify.shexecutablescript7.1 KB

evals/cases.yaml

View on GitHub
skill: postgresdb

should_trigger:
  - prompt: "This query takes 8 seconds and the page won't load. Can you help me figure out why and speed it up?"
    why: "Slow query / latency complaint is core scope; the skill owns reading the plan and fixing scans even though the user never says SQL or index."
  - prompt: "We need to add a column to a 40-million-row table that's getting live traffic without taking the app down. What's the safe way?"
    why: "Zero-downtime migration against a large table with live traffic — expand-contract, lock_timeout, batched backfill — is explicitly in scope."
  - prompt: "Here's my EXPLAIN ANALYZE output. Why is it doing a Seq Scan on orders when I have an index on user_id?"
    why: "Reading a plan, diagnosing Seq Scan vs index usage, and stale-stats/predicate issues are the heart of query-optimization."
  - prompt: "Should I store this as an enum or a separate lookup table with a foreign key? The set of statuses keeps changing."
    why: "Schema/DDL type decision (enum-vs-lookup) is a named When-to-use case with a decision rule in the skill."
  - prompt: "Different tenants keep seeing each other's rows in our shared table. I want the database itself to enforce isolation per tenant."
    why: "This is row-level security (RLS) operational/security scope, even though the user never names RLS or Postgres explicitly."
  - prompt: "Our background workers keep grabbing the same job and processing it twice. They poll a jobs table. How do I make concurrent workers not collide?"
    why: "Postgres-as-queue with FOR UPDATE SKIP LOCKED is explicitly the one queue pattern in scope; concurrency/locking is core."
  - prompt: "I have a list view and every time I load a user's page it fires hundreds of little SELECTs, one per row. How do I fix this?"
    why: "Classic N+1 described without the term; skill owns fixing it at the SQL boundary with a set-based query / LATERAL join."
  - prompt: "We're running out of Postgres connections under load on our serverless app. How should we set up pooling?"
    why: "Connection pooling / PgBouncer is named operations scope (the engine-side answer, not vendor console clicks)."

should_not_trigger:
  - prompt: "My Prisma updateMany returns the wrong count and isn't updating the rows I expect. What's the ORM doing?"
    route_to: "none"
    why: "Explicitly out of scope: ORM-API ergonomics like Prisma's updateMany count trap belong to the ORM's own docs, not this engine-level skill."
  - prompt: "I need to add a SQLite full-text search index in my Expo mobile app's local database."
    route_to: "flutter"
    why: "Non-Postgres engine (SQLite) is explicitly out of scope — different MVCC/planner; mobile-app data lands closer to the app-framework skill, not postgresdb."
  - prompt: "How do I configure a managed Postgres instance on RDS through the AWS console and set up automated snapshots in the UI?"
    route_to: "deployment"
    why: "Cloud-vendor console clicks are explicitly out of scope; provisioning/infra-through-UI is deployment territory, not engine-level SQL."
  - prompt: "Build a FastAPI endpoint with a Pydantic request model that returns paginated results."
    route_to: "fastapi"
    why: "This is API-framework / request-validation work; the SQL it eventually emits could touch postgresdb but the prompt as stated is fastapi's domain."
  - prompt: "How do I prevent SQL injection in my Node app — is parameterizing enough, and how should I handle untrusted input generally?"
    route_to: "security-audit"
    why: "A general application-security question about untrusted input belongs to security-audit; postgresdb owns engine behavior, not app-layer input handling."
  - prompt: "Should I cache these hot query results in Redis to cut database load?"
    route_to: "none"
    why: "App-layer caching / Redis as a product is explicitly out of scope; only Postgres-as-queue via SKIP LOCKED is in scope, not caching layers."

capability:
  - scenario: "User has a query `SELECT * FROM orders WHERE user_id = $1 AND created_at >= $2 ORDER BY created_at DESC LIMIT 20` that is slow on a large orders table, and asks which index to add and how to confirm it helps."
    must_include:
      - "Proposes a composite btree index with equality column first, range/sort column second: ON orders (user_id, created_at DESC)"
      - "Explains the index ORDER BY direction must match the query to avoid a sort, and column order rationale (equality then range)"
      - "Instructs running EXPLAIN (ANALYZE, BUFFERS) and confirming an Index Cond (not a Filter / Rows Removed by Filter) before declaring success"
      - "Recommends CREATE INDEX CONCURRENTLY on a live table (cannot run inside a transaction) rather than plain CREATE INDEX"
      - "Recommends keyset/cursor pagination over large OFFSET, or at least flags OFFSET as O(n) for deep pages"
      - "Warns against blindly adding an index when selectivity is low or the column is already a left prefix of an existing index"
  - scenario: "User wants to add a NOT NULL `country` column to a 50M-row table that serves live traffic, with zero downtime, using a generic migration tool."
    must_include:
      - "Splits the change into steps (expand-contract): add nullable column first, backfill, then enforce NOT NULL — never ADD COLUMN ... NOT NULL with backfill in one shot on a large table"
      - "Notes that a constant (non-volatile) default is instant on PG11+, but a volatile default (e.g. now()/uuid) forces a full table rewrite under ACCESS EXCLUSIVE"
      - "Backfills in batches to avoid a long transaction / lock and bloat, rather than a single UPDATE"
      - "Adds the NOT NULL via a CHECK ... NOT VALID then VALIDATE CONSTRAINT (or NOT NULL after backfill) to avoid a blocking full-table scan under heavy lock"
      - "Wraps DDL with SET lock_timeout and SET statement_timeout so a blocked DDL fails fast instead of queueing behind/ahead of traffic"
      - "States migrations are forward-only in prod and never edit an already-applied migration"
deprecation-and-migrationaddyosmani/agent-skillsManages deprecation and migration. Use when removing old systems, APIs, or features. Use when migrating users from one implementation to another. Use when migrating a database schema in production, such as renaming or dropping a column without downtime (expand/contract). Use when deciding whether to maintain or sunset existing code.Stars: 119kUpdated Claude CodeCodexVerifiedInstall
performance-optimizationaddyosmani/agent-skillsOptimizes application performance across frontend, backend, queries, and databases. Use when performance requirements exist, when you suspect performance regressions, when Core Web Vitals or load times need improvement, when N+1 query patterns need fixing, or when profiling reveals bottlenecks.Stars: 119kUpdated Claude CodeCodexVerifiedInstall
agent-cost-reportthedotmack/claude-memBelievable agent cost report for any period, default the last 7 full days PT, not counting today. Measured tokens from Claude Code transcripts priced at OpenRouter list prices (ESTIMATED), measured provider spend when a sanctioned source exists, note-taker cost separate, Timing-style HTML/PDF plus report.json, line-items.csv, evidence.json.Stars: 118kUpdated Claude CodeCodexVerifiedInstall
agent-cost-reportthedotmack/claude-memBelievable agent cost report for any period, default the last 7 full days PT, not counting today. Measured tokens from Claude Code transcripts priced at OpenRouter list prices (ESTIMATED), measured provider spend when a sanctioned source exists, note-taker cost separate, Timing-style HTML/PDF plus report.json, line-items.csv, evidence.json.Stars: 118kUpdated Claude CodeCodexVerifiedInstall