Coding & Refactoring Agent Skill

Battle-hardened senior engineer who reviews plans and code before they cause a 3 AM incident. Stress-tests architecture, catches data-pipeline fragility, flags security gaps, and calls out missing error handling, then signs off with a severity summary and grudging approval if earned. Use when reviewing new or existing plans, specs, design docs, PRs, or diffs.

Write a SQL-callable C function or call PostgreSQL fmgr / SPI from C — covers PG_FUNCTION_INFO_V1, PG_GETARG_* / PG_RETURN_*, PG_ARGISNULL, SRF_* set-returning function ValuePerCall and Materialize modes, composite / polymorphic returns, DirectFunctionCall* / OidFunctionCall* / FunctionCallInvoke fmgr entry points, plus SPI_connect / SPI_execute / SPI_prepare / SPI_finish, plan caching, SPI cursors, subxact rollback, and SPI return codes. Use whenever a PG patch or extension adds a `Datum foo(PG_FUNCTION_ARGS)` entry point, exposes a set-returning function, calls fmgr from backend C, or embeds SQL via SPI in a backend / trigger / PL handler. Skip for plpgsql / PL/Python / PL/Perl user-side function authoring, libpq / psycopg / JDBC / node-postgres client-side query execution, generic executor questions (use executor-and-planner), and non-PG embedded SQL (Oracle OCI, SQLite C API).

PostgreSQL's Free Space Map (FSM) — the tree-of-pages that tracks per-heap-page free space so `INSERT` / `COPY` / `heap_multi_insert` can find a page with room without scanning the whole relation. Covers `src/backend/storage/freespace/` (`freespace.c` + `fsmpage.c` + `indexfsm.c`), the tree layout (leaf-page nodes + 2 upper levels), category encoding (bucketed free-byte counts), the FSM lock discipline, VACUUM's post-scan `FreeSpaceMapVacuum`, and index FSM's simpler use case. Skip when the ask is about visibility map (VM — sibling but different subsystem) or about `pg_freespacemap` contrib module (that's the SQL introspection wrapper).

PostgreSQL's `pg_upgrade` — the tool that upgrades a data directory in-place from one major version to another without a dump-restore. Covers `src/bin/pg_upgrade/` architecture, the pre-upgrade checks, catalog dump-restore, relfilenode preservation (or rewriting), the two allocation strategies (link vs copy vs clone), and the "check for known upgrade issues" list. Loads when the user asks about pg_upgrade internals, common upgrade failures ("could not connect to source cluster", ERROR at pg_dump extraction, relfilenumber mismatch), --link vs --clone vs --copy tradeoffs, upgrade-blocking features (unlogged tables, sequences, extensions), or extension upgrade paths. Skip when the ask is about major-version release notes semantics (see `wiki-distilled` corpus) or about pg_dump alone.

PostgreSQL's SQL/JSON support — jsonpath (SQL:2016 path language) + jsonb (binary-encoded JSON) + the SQL/JSON operators like `->`, `->>`, `@?`, `@@`, `jsonb_path_query`. Covers `src/backend/utils/adt/jsonb*.c` (jsonb storage + operators + GIN opclass + subscripting) and `jsonpath*.c` (path language parser + executor). Loads when the user asks about jsonpath semantics, path variables ($, @, current), lax vs strict mode, predicate expressions, `jsonb_path_query` / `jsonb_path_exists`, JSON_TABLE (SQL:2023 / PG 17+), jsonb_gin operator class, memory management in jsonpath_exec (Tom Lane's 5a2043bf713 rewrite), or "why is my JSON search slow" (usually GIN or path complexity). Skip when the ask is about the older `json` type (`json.c` is separate — text representation) or about JSON output formats (that's `format_type` territory).

PostgreSQL's logical replication — publisher-side WAL decoding + subscriber-side apply — plus everything under `src/backend/replication/logical/`. Loads when the user asks about logical decoding, `pg_logical_slot_get_changes`, publications / subscriptions, output plugins (pgoutput / test_decoding), reorder buffer, historic snapshots (SnapBuild), the apply worker + parallel apply, conflict resolution (PG 18+ conflict tracking), replication origins, replication slots (physical vs logical, `slot.c` + `slotsync.c`), streaming mode for in-progress transactions, tablesync's initial COPY, or debugging apply-worker crashes / slot advancement / catalog_xmin retention. Skip when the ask is about physical streaming replication (walsender/walreceiver, `replication/basebackup*.c`, `replication/walsender.c` in isolation) — those are the sibling `replication` subsystem, mostly disjoint code paths.

PostgreSQL's Row-Level Security (RLS) — `CREATE POLICY` / `ALTER TABLE ... ENABLE ROW LEVEL SECURITY` / policy application in the rewriter — plus the related security-barrier machinery + leakproof qualification checks. Loads when the user asks about RLS policy semantics (USING vs WITH CHECK, PERMISSIVE vs RESTRICTIVE), how policies are applied to a query at rewrite time, why a qual can/cannot be pushed below a security barrier, the leakproof function attribute, ROLE mapping for BYPASSRLS / NOFORCERLS, `row_security` GUC, or debugging why an RLS policy is/isn't firing. Also covers security-barrier views (`WITH (security_barrier = true)`), which use the same qual-pushdown-prohibition machinery. Skip when the ask is about pg_hba.conf-level authentication, GRANT/REVOKE table-level privileges, SELinux (`sepgsql`), or database-level security features unrelated to per-row visibility.

PostgreSQL's frontend/backend wire protocol — the byte-level message format between client (libpq or any driver) and backend, plus the backend-side send/receive machinery. Covers `src/backend/libpq/pqcomm.c` (byte-stream framing) + `pqformat.c` (typed message building/parsing) + the message-dispatch loop in `tcop/postgres.c`. Loads when the user asks about protocol version 3.0/3.1, message types (Startup / Query / Bind / Execute / RowDescription / DataRow / CommandComplete / ReadyForQuery / ErrorResponse / etc.), simple vs extended query protocol, protocol negotiation, `pq_beginmessage` / `pq_sendstring` / `pq_endmessage` conventions, adding a new protocol message (has scenario `add-new-protocol-message`), or debugging "backend closed connection unexpectedly" wire-level. Skip when the ask is about libpq's client-side implementation (`src/interfaces/libpq/`), about the replication sub-protocol (walsender's variant), or about authentication protocol details (see `libpq-backend` subsystem).

PostgreSQL's Node type system — the tagged-union polymorphism used for parser output (Query, RangeTblEntry), planner output (Path, Plan), executor state (PlanState, ExprState), and every intermediate representation. Covers `src/backend/nodes/gen_node_support.pl` (the code generator) + `src/include/nodes/*.h` + the copy/equal/out/read function families + walker/mutator support (`nodeFuncs.c`). Loads when the user asks about `NodeTag`, adding a new Node type (has scenario `add-new-node-type`), `expression_tree_walker` / `expression_tree_mutator`, why grammar changes require regenerating `nodes.h`, the `T_Foo` enum, or `castNode` / `nodeTag` macros. Skip when the ask is about a specific Node (e.g. SeqScan, HashJoin) — those live under executor/optimizer skills.

PostgreSQL's physical streaming replication — walsender (primary side, `replication/walsender.c`) + walreceiver (standby side, `replication/walreceiver.c`) + slots (`replication/slot.c`) + synchronous replication (`syncrep.c`). Covers the streaming protocol variant, hot standby, standby feedback, synchronous commit, WAL sender/receiver state machines, and the physical-slot semantics that differ from logical slots. Loads when the user asks about streaming replication, hot standby, `pg_basebackup`, synchronous_commit levels, walsender/walreceiver state, primary_conninfo, physical replication slots, standby feedback (`hot_standby_feedback`), or `pg_wal_replay_*` recovery-progress functions. Skip when the ask is about logical replication (`replication/logical/`) — sibling but very different code path.

PostgreSQL's Simple LRU (SLRU) subsystem — `src/backend/access/transam/slru.c` — the fixed-file, fixed-page cache used by CLOG (`pg_xact/`), MultiXact (`pg_multixact/`), Subtrans (`pg_subtrans/`), CommitTs (`pg_commit_ts/`), and Notify (`pg_notify/`). Covers the bank-locked LRU + latest-page pin design, `SlruCtl` structure, page replacement policy, and the on-disk 32-page-per-segment file layout. Loads when the user asks about SLRU semantics, per-consumer sizing GUCs (PG 17+ per-SLRU `Buffers` GUCs), why SLRUs use their own cache instead of shared buffers, adding a new SLRU consumer, or debugging SLRU-related wraparound / pressure. Skip when the ask is about specific SLRU consumers' semantics — the multixact skill covers multixact (which USES SLRU); this covers the SLRU MACHINERY itself.

PostgreSQL's TOAST (The Oversized-Attribute Storage Technique) — out-of-line + compressed storage for large `varlena` values (text, bytea, jsonb, arrays, xml, tsvector). Covers `src/backend/access/common/toast_*.c` + `src/backend/access/table/toast_helper.c` + the compression backends (pglz, lz4). Loads when the user asks about TOAST semantics, `pg_toast_<oid>` tables, storage strategies (PLAIN / EXTERNAL / EXTENDED / MAIN), inline vs out-of-line thresholds (2 KB / 8 KB rules), TOAST pointers, sliced access (`substring` optimization), or lz4 vs pglz. Skip when the ask is about `pg_toast_pg_class` etc. as objects (that's catalog metadata) or about specific varlena types' semantics (that's their type's skill).

PostgreSQL's per-backend type-info cache — `src/backend/utils/cache/typcache.c` — the `TypeCacheEntry` struct + `lookup_type_cache` + domain constraint caching + RECORD typmod registry + the shared-parallel-worker variant. Loads when the user asks about `typcache` internals, why `lookup_type_cache` might return NULL, how composite types get identified in parallel workers, RECORD types + typmod semantics, domain constraint invalidation, or when investigating a "type doesn't have the expected traits" bug (missing hash/equality/comparison functions). Skip when the ask is about typcache CALLERS (executor, planner — they just call the API), about pg_type catalog rows (that's `catalog-conventions`), or about implementing a new type (that's `add-new-data-type` scenario).

PostgreSQL's VACUUM machinery — heap scanning, dead-tuple collection, index cleanup, HOT pruning, freezing, VM/FSM updates, truncation — plus the autovacuum launcher/worker architecture. Loads when the user asks about `VACUUM` internals, `vacuumlazy.c`'s three-phase orchestration, TidStore (the radix-tree dead-TID collector), HOT chains, HEAP_XMIN_FROZEN and freeze planning, VACUUM FREEZE, VACUUM FULL vs `vacuum_full_freeze_min_age`, autovacuum threshold + cost-limit tuning, autovacuum worker scheduling, or debugging "why is my table getting vacuumed constantly / never". Also for the xmin horizon (RecentXmin / OldestXmin / GlobalVis), the interaction between vacuum and replication slots, parallel VACUUM (`vacuumparallel.c`), and the wraparound-emergency path. Skip when the ask is about `pg_repack` (contrib alternative), pg_amcheck (verifier), or `CREATE INDEX CONCURRENTLY` (index-build side, not vacuum-side).

Use when reviewing a staged diff or an about-to-commit/push change for last-mile issues — silent failures, leaked secrets, debug leftovers, blatant correctness landmines, and excessive/narrating comments. Reports findings on the changed lines only; it does not refactor or rewrite code. This is the canonical source for the Klaussy desktop pre-commit gate, which inlines the diff and adds its own machine-readable output contract.

Use when reviewing a staged diff or an about-to-commit/push change for last-mile issues — silent failures, leaked secrets, debug leftovers, blatant correctness landmines, and excessive/narrating comments. Reports findings on the changed lines only; it does not refactor or rewrite code. This is the canonical source for the Klaussy desktop pre-commit gate, which inlines the diff and adds its own machine-readable output contract.

Use this skill when the task is to authenticate with X, fetch timelines, tweets, users, search results, followers, or following data through x-cli, or to troubleshoot x-cli auth, rate limits, and GraphQL query ID drift.

Configure OpenCode via opencode.json and AGENTS.md with deterministic provider setup, model selection, permission policies, formatter behavior, and environment variable handling; use when editing opencode configuration, setting model/provider defaults, tightening agent permissions, or troubleshooting OpenCode config behavior.

Create publication-quality scientific diagrams using Claude AI with smart iterative refinement. Uses Claude for quality review. Only regenerates if quality is below threshold for your document type. Specialized in neural network architectures, system diagrams, flowcharts, biological pathways, and complex scientific visualizations.

Build a PostgreSQL backend loadable extension (.so / contrib module) — covers the .control file, the foo--1.0.sql install script + foo--1.0--1.1.sql upgrade scripts, PGXS vs meson build wiring, the `_PG_init` entry point, shared_preload_libraries vs LOAD vs CREATE EXTENSION load timing, chained hook installation (ProcessUtility_hook, planner_hook, ExecutorStart_hook), trusted vs untrusted extensions, and SQL-callable C function declarations (PG_FUNCTION_INFO_V1, PG_RETURN_*). Use whenever a PG extension is being written or modified — wiring _PG_init, registering hooks, picking PGXS vs meson, writing install/upgrade SQL, declaring CREATE FUNCTION ... LANGUAGE C, or marking the extension trusted. Skip for VS Code / Chrome / Firefox / Safari / browser extensions, NPM / pip / RubyGems / Cargo packages, IntelliJ / Eclipse plugins, and shell completion scripts.

Shadow-implement a pgsql-hackers thread to calibrate the pg-claude planner suite — read the COVER + discussion only (NOT the attached patch), produce a spec, run pg-feature-brainstorm / pg-feature-plan / pg-implement as-if the user asked for the feature, then fetch the upstream patch, diff against ours, score the gap, and emit comparison.md + skill-gaps.md. This is the "Phase E" calibration loop. Use when the user invokes `/pg-shadow <thread-url>`, says "shadow-implement that thread", "run Phase E against <thread>", "run a shadow against this hackers thread", or names a pgsql-hackers thread + asks how our planner would handle it. Do NOT trigger for real upstream patches we plan to send (use `pg-feature-plan` + `pg-implement` directly), for already-committed threads (re-implementing landed code is meaningless), or for non-PG calibration.

Hack or review PostgreSQL replication internals — covers physical streaming, archive shipping, logical decoding, logical replication PUB/SUB, and synchronous-commit machinery. Names walsender / walreceiver / replication slot / output plugin callbacks plus the relevant GUCs (wal_level, max_wal_senders, max_replication_slots, max_logical_replication_workers, primary_conninfo, primary_slot_name, synchronous_standby_names) and the source files behind each flavor. Use whenever a PG patch touches walsender, walreceiver, replication slots, logical decoding, output plugins, publications/subscriptions, conflict detection, sync replication, failover slots, pg_basebackup, or pg_receivewal — or when picking which mechanism fits a feature design. Skip for MySQL row-based / GTID replication, MongoDB replica sets, Cassandra hinted handoff, Kafka MirrorMaker / Confluent Replicator, Debezium / Flink CDC pipelines, AWS DMS, and Galera / Group Replication.

Review a PostgreSQL patch using the wiki's seven-phase checklist — covers Phase 0 reviewer-reflex gates + REJECT-A/B/C grade rubric for design rejections, plus the wiki's Phases 1-7 (apply/build, regress + check-world, pgindent, design fit, docs, comments, committer-readiness). Applies to reviewing someone else's pgsql-hackers / CommitFest submission OR self-reviewing your own patch before mailing it. Use whenever the user says "review this patch", "is it ready to send to hackers", "CF entry NNNN review", "pre-submission review", "REJECT this patch", or pastes a pgsql-hackers thread asking for a structured review. Skip for generic GitHub PR code review (app code), Terraform / Helm chart review, Python / Ruby / Rust / Go application code review, security audit (use security-review), API design review on REST / GraphQL endpoints, and documentation-only review for non-PG projects.

Verify a release candidate before publishing it.