/ship-it — Claude Code skills for an AI review-fix loop until a PR is merge-ready (ship-it / pr-all / total-review / merge-cleanup)

1-ship-it.SKILL.md

name

ship-it

description

End-to-end PR workflow: create PR, iteratively run reviews + fix issues + push, and let a Sonnet sub-agent decide each round whether the PR is merge-ready. Loop exits only when an external evaluator confirms all-green. Use this skill when the user says 'ship it', 'PR and fix everything', 'create PR and handle reviews', 'make it mergeable', 'submit and fix until green', 'full PR loop', or wants to go from finished implementation to a merge-ready PR in one command. Also trigger when the user asks to combine pr-all + total-review + fix-pr-comments into a single workflow.

Ship It

Make a PR merge-ready: create the PR, then loop review→fix→push with an external Sonnet sub-agent acting as the convergence judge. Loop exits only when the judge confirms every part of the merge-ready condition. Does NOT merge — the user controls the merge button.

Why a Sonnet judge instead of self-judging

The working agent (Opus) is doing the fixing, so asking it to also decide "are we done?" invites premature exits. A separate Sonnet sub-agent reads only the evidence the working agent surfaced this iteration, and returns MET or NOT MET with a one-line reason. That mirrors how Anthropic's /goal command uses a fresh small model — but stays inside tools we can actually invoke from a skill.

What it does NOT do

• Does NOT merge the PR — user controls the merge button
• Does NOT create additional PRs or branches — works on exactly one PR on the feature branch
• Does NOT run gh pr merge — ever, under any condition
• Does NOT create GitHub issues — never runs gh issue create during the workflow, not even "just one" for a batch of leftovers. It does recommend which leftovers deserve an issue and offers to file them; the user decides, in a later turn

Permissions

This skill is authorized to commit and push to the feature branch during the loop. Do not ask for confirmation on those actions.

Phase 1 — Create or update the PR

1. If not already on a feature branch, create one.
2. Check whether a PR already exists for the current branch:

   gh pr view --json number,url,state 2>/dev/null

3. If a PR exists and is open, capture its number and URL.
4. If no PR exists, invoke /pr-all to commit, push, and open a PR. Capture the number and URL.
5. Also capture OWNER/REPO:

   gh repo view --json nameWithOwner --jq .nameWithOwner

6. Capture the baseline diff. Everything in it is the fix the user asked for. Everything Phase 2 adds on top is loop-introduced code, and the two are governed by different rules (see "Loop-introduced code" below).

   git rev-parse HEAD
   git diff main...HEAD --stat

   Hold BASELINE_SHA and the stat line (files changed, +/-) as loop state — same as iteration. Report both in Phase 3 alongside the final numbers.

Phase 2 — Review-fix loop with Sonnet judge

Initialize iteration = 0, MAX_ITERATIONS = 3.

The cap is hard. Three review-fix rounds, then stop and list the remainder in the final report. A review loop that keeps finding things is not evidence that it should keep running — past round 3 the findings are usually about code the loop itself wrote (see below), and each further round grows the diff and the risk without shrinking the original bug.

An iteration only counts against the cap if it fixed at least one finding. Rounds spent solely waiting on CI, re-running a crashed reviewer, or resolving comment threads are free — but cap those at 3 as well, so a flaky pipeline cannot spin the loop forever.

Loop-introduced code

Anything not in the Phase 1 baseline diff is loop-introduced. Findings against it are handled differently from findings against the original fix:

• A finding against loop-introduced code defaults to reverting that code, not patching it. Patch it only when the revert would reopen a finding that was itself must-fix.
• Hard rule: if the fix for finding X introduces finding Y, remove the fix for X. Record X as a known limitation in the PR body. Do not fix Y. Two guards stacked to cover each other is the shape of a loop that will not converge.
• Watch for this specifically in defensive additions — watchdogs, timeouts, retries, global error listeners, fallbacks, guard clauses. These are the usual response to a "silent failure" finding, and each one adds a new runtime path that can fire when nothing is actually wrong. A backstop that can brick a healthy run is worse than the silent failure it was added to catch.
• Before adding any new runtime failure path, state in one line what it does when it misfires. If the answer is "breaks a working run", don't add it.

Scope

Findings that are real but belong to a different class of problem than the PR's stated purpose are not fixed in this loop. Record them and report them in Phase 2b — reported, not filed as issues. The deliverable is the reported bug, fixed and merge-ready — not every defect reachable from the changed files.

Per iteration

1. Collect findings (in parallel):

   • /total-review locally. Phase 1 already committed and pushed, so the working tree is clean and a bare git diff would be empty — tell it explicitly to review the PR range (<base>...HEAD, the same baseline captured in Phase 1 step 6). If its report comes back without a resolved range, or claims zero findings against an empty diff, treat that as a failed review and re-run it — never as a clean bill of health.
   • Fetch unresolved PR review comments:

     gh api "repos/<OWNER>/<REPO>/pulls/<N>/comments" --paginate
     gh api "repos/<OWNER>/<REPO>/issues/<N>/comments" --paginate
     gh api "repos/<OWNER>/<REPO>/pulls/<N>/reviews" --paginate

     Filter out the PR author's own comments, resolved (✅), and outdated inline.

2. Triage every finding, then fix. For each one, classify first:

   • In-scope, against baseline code → fix it. For PR review comments that means every severity the reviewer used, nitpicks included. For /total-review output it means the Must Fix and Should Fix buckets — its Ignored bucket is already-triaged and is NOT work, and re-fixing it defeats the triage.
   • Against loop-introduced code → revert the code that caused it (see "Loop-introduced code" above). Only patch if reverting reopens a must-fix finding.
   • Out-of-scope (different class of problem than the PR's purpose) → defer, record for Phase 2b.

   Never self-filter discovery — every finding gets recorded. Filtering happens in the open, never inside the reviewers: /total-review step 4b triages first, then this step decides scope. Two stages, both visible, neither hidden in a reviewer's head.

3. Run project tests. Revert any fix that regresses.

4. Commit and push to the feature branch.

5. Wait for CI:

   gh pr checks <N> --watch --fail-fast

6. Reply to and resolve each PR comment thread that was addressed this iteration.

7. Surface evidence by running and showing the output of:

   gh pr view <N> --json mergeable,mergeStateStatus,reviewDecision,statusCheckRollup
   gh pr checks <N>
   gh api "repos/<OWNER>/<REPO>/pulls/<N>/comments" --paginate --jq '[.[] | select(.in_reply_to_id == null and .position != null)] | length'

   Plus the latest /total-review run's Verdict: line verbatim (the judge is fail-closed on it — if the run didn't emit one, say so rather than paraphrasing) together with its Ignored and Out of Scope lists, and:

   git diff main...HEAD --stat                     # current
   git diff main...<BASELINE_SHA> --stat           # Phase 1 baseline

   The two stat lines make scope growth visible. Also list, explicitly:

   • findings deferred this iteration (out-of-scope) — title + one-line reason
   • loop-introduced code reverted this iteration, and which finding it had been fixing

8. Dispatch the Sonnet judge via the Agent tool:

   Agent(
     description: "Judge ship-it convergence",
     subagent_type: "general-purpose",
     model: "sonnet",
     prompt: """
   You are an external evaluator for the ship-it workflow. The working agent has just finished one review-and-fix iteration on PR #<N> in <OWNER>/<REPO>. Your only job is to read the evidence below and return a verdict.
   
   ## Completion condition (all four must hold)
   1. `mergeStateStatus` is `CLEAN`
   2. `statusCheckRollup` is **non-empty** and every entry in it is `SUCCESS`, `NEUTRAL`, or `SKIPPED` (no `PENDING`, `FAILURE`, or `ERROR`). An empty rollup means no checks ran — that is not "all green", it is no evidence.
   3. The unresolved-review-comment count was actually read and is `0`. A blank, errored, or missing count is not zero.
   4. The latest `/total-review` run's `Verdict:` line explicitly reports **0 must-fix**. Four categories do not block:
      - sub-reviewers that crashed or were unavailable (only ignore them if they failed cleanly)
      - the `Should Fix` bucket — worth fixing, never merge-blocking
      - the `Ignored` bucket — `/total-review` already cut those on the merits
      - the `Out of Scope` bucket (findings against code this PR's diff didn't introduce, touch, or newly make reachable), plus anything the working agent separately deferred as off-topic for this PR with a stated reason
   
   **All four conditions are fail-closed.** Each is met only by reading positive evidence for it. Missing, empty, malformed, or unfamiliar-looking output means NOT met — return `NOT MET` and say which evidence was unreadable. Never read an absent section, an empty list, or an unmatched label as a passing result: output you cannot parse is evidence of nothing, not evidence of success.
   
   Note the two senses of "out of scope" in play: `/total-review`'s bucket is about **code locality** (the diff didn't touch it), while this workflow's own deferral is about **topicality** (a different class of problem than the PR's purpose). Both are non-blocking, but they are different lists — do not let a Must Fix finding against baseline code be relabelled into either one.
   
   ## Evidence from this iteration
   <paste verbatim output from the commands listed above + the /total-review summary + the deferred list + the reverted list>
   
   ## Also flag (does not change the verdict)
   Compare the current diffstat against the Phase 1 baseline diffstat. If the diff has grown by more than ~2x, say so in your reason — the loop may be fixing itself rather than the bug.
   
   ## Response format
   Return EXACTLY one line:
   - `MET: <one-line reason confirming all four hold>` if every part is demonstrably satisfied
   - `NOT MET: <one-line reason naming which part(s) still fail and what evidence shows it>`
   
   Judge the deferrals too: if a "deferred, out-of-scope" item is plainly the reported bug itself, return NOT MET.
   
   Do not run tools. Do not propose fixes. Judge only what the evidence shows.
   """
   )
   

9. Read the verdict:

   • MET: ... → exit Phase 2. Go to Phase 2b if anything was deferred, otherwise straight to Phase 3
   • NOT MET: <reason> → use the reason as guidance, increment iteration (only if this iteration fixed something), and if iteration < MAX_ITERATIONS go back to step 1
   • At iteration >= MAX_ITERATIONS → stop editing and go to Phase 2b

Phase 2b — Record the remainder, propose issues

Reached when the cap is hit, or whenever findings were deferred as out-of-scope. Do not keep fixing.

1. Write up each outstanding or deferred finding in the Phase 3 report — title, one-line reason it was left out, and enough detail (reproduction, source reviewer) that it could be filed as-is. Batch near-duplicates into one entry.
2. Propose which of them are worth an issue — mark each entry → issue 推奨 or → 見送り推奨 with a half-line of why. This is a recommendation, not an action.
3. Do not run gh issue create in this skill run. Creating issues is never part of the workflow — end the report with a single offer ("これらを issue にするなら言って") and stop. If the user says yes in a later turn, file exactly the ones they name, then.
4. Proceed to Phase 3 and report the PR as capped, not as MET.

Other exit conditions

• Merge conflicts unresolvable in-iteration → surface and stop
• Required human approvals blocking mergeStateStatus → surface and stop (only humans can approve)
• Zero new findings AND zero unresolved comments AND CI green but judge says NOT MET twice in a row → likely a condition mismatch; surface the judge's reason and stop

Phase 3 — Final report

Language: Translate the final report (headings, labels, and prose) to match the user's configured response language from CLAUDE.md / system prompt. The template below uses English labels for reference only — render them in the user's language. Keep identifiers (PR URL, iteration numbers, command names) untranslated.

## Ship It Complete — PR ready for review

**PR**: <url>
**Iterations**: N of 3
**Judge's final verdict**: MET — <reason>
**Diff**: baseline <files>/+<a>-<b> → final <files>/+<a>-<b>

### Per-iteration summary
- Iteration 1: Fixed 5 issues (3 from total-review, 2 from PR comments)
- Iteration 2: Fixed 1 CodeRabbit nit; CI green; judge: MET

### Deferred — not fixed (issue 化は提案のみ)
- <title> — <why out of scope> — <detail / source reviewer> — **→ issue 推奨** / **→ 見送り推奨**: <half-line why>

> これらを issue にするなら言って。ぼくが立てる。

### Reverted during the loop
- <what was removed> — was fixing <finding>, but introduced <new finding>

Omit the last two sections when empty. If the diff grew more than ~2x over baseline, say so in one line and name what drove it — that is the number the user actually wants to see.

If the loop hit MAX_ITERATIONS without MET, report it as capped: the judge's last reason and what's still outstanding, as listed in Phase 2b. Do NOT merge. Do NOT re-launch the loop automatically.

Edge cases

• Flaky CI unrelated to the diff → note it, continue the loop; judge will eventually time out via MAX_ITERATIONS if CI never settles
• Rate limiting from gh api → back off and retry within the same iteration
• /total-review returns ambiguous output → surface raw output to the judge; let it decide
• A sub-reviewer in /total-review crashes or is unavailable (e.g. Codex returns no output, a CodeRabbit subagent isn't registered in this environment) → note which reviewer failed in the evidence, proceed with the remaining reviewers' output, and explicitly tell the judge that the missing reviewer should NOT count as a finding. Do not block the loop on a tool outage.
• SKIPPED CI checks are treated the same as SUCCESS for convergence (common for conditional workflows like claude-code-action)

Headless mode

claude -p "/ship-it"

Runs the entire workflow in a single CLI invocation. Ctrl+C interrupts.

Alternative: hand off to /goal

If the user is in an interactive Claude Code session, /goal is a built-in command that fires its own Haiku evaluator after every turn (cheaper, automatic). The Skill tool cannot invoke /goal programmatically — /goal is not in the built-ins whitelist exposed to skills. To use it, after Phase 1 completes, paste this at the prompt:

/goal PR #<N> in <OWNER>/<REPO> is merge-ready — mergeStateStatus=CLEAN, a non-empty statusCheckRollup with every entry SUCCESS/NEUTRAL/SKIPPED, an actually-read unresolved-comment count of 0, and the latest /total-review "Verdict:" line explicitly reporting 0 must-fix. Every one of those is fail-closed: missing, empty, or unparseable evidence does NOT count as passing. Should Fix, Ignored and Out of Scope never block. Per turn: collect findings (run /total-review, fetch gh api comments), fix every must-fix and should-fix finding against the original diff; for findings against code added during this loop, revert that code instead of patching it; if a fix for X introduces Y, remove the fix for X and note X as a known limitation; defer out-of-scope findings and list them in the report, each marked with whether it deserves a follow-up issue. Test, commit, push, wait for CI, reply+resolve threads, then surface gh pr view/checks output plus the current-vs-baseline diffstat. Never merge. Never run gh issue create — recommend and offer, let me decide. Stop after 3 turns and report the remainder as a written list with issue recommendations, not as filed issues.

That's the same condition the Sonnet judge uses, just delegated to /goal's machinery.

VCS compatibility

Works with both git and jj — /pr-all, /total-review, and /fix-pr-comments handle VCS detection.

2-pr-all.SKILL.md

name	pr-all
description	Create a complete pull request workflow: commit all changes, push to remote, and create a PR. Use when the user says "create a PR", "PR this", "push and create PR", "open a pull request", or asks to submit changes for review. (Note: "ship it" → use /ship-it instead, which wraps this skill with a full review-and-fix loop.)

PR All — Complete Pull Request Workflow

End state: a single open PR on the current feature branch, with all local work committed and pushed.

Preflight

Run these first and decide early. Bail out if there's nothing to PR.

DEFAULT_BRANCH=$(gh repo view --json defaultBranchRef --jq .defaultBranchRef.name)
EXISTING_PR=$(gh pr view --json number,url,state 2>/dev/null)

• If EXISTING_PR is non-empty and state == "OPEN" → print the URL and stop. The PR already exists; no new PR needed.
• If the working tree has no changes AND the current branch has no commits ahead of $DEFAULT_BRANCH → stop with "No changes to PR".

Detect VCS

If .jj/ exists in the project root → jj workflow. Otherwise → git workflow.

---

jj Workflow

1. Inspect: jj status and jj diff
2. If @ has uncommitted changes, describe them with jj describe -m "<message>" using the commit format from CLAUDE.md (imperative summary + bullets + Co-Authored-By: Claude <model name> <noreply@anthropic.com> where <model name> is the actual model running, e.g. Opus 4.8)
3. Pick the push target:
   • If @ has the work, push @
   • If @ is empty and @- has the work, push @-
4. Derive a kebab-case branch name from the commit summary (e.g. persist-project-selection, max ~50 chars)
5. Push and create the bookmark in one step:

   jj git push --named <branch-name>=<revision>

6. Detect owner/repo from jj git remote list (parse the origin URL)
7. Create the PR (jj's colocated git has no checked-out branch, so --repo is required):

   gh pr create --repo <owner/repo> --base "$DEFAULT_BRANCH" --head <branch-name> --title "..." --body "$(cat <<'EOF'
   ## Summary
   - <bullet 1>
   - <bullet 2>
   
   ## Test plan
   - [ ] <test step>
   - [ ] <test step>
   EOF
   )"

---

git Workflow

1. Inspect: git status and git diff
2. If on $DEFAULT_BRANCH, create and switch to a new feature branch (kebab-case name derived from the planned commit summary, max ~50 chars)
3. Branch in three states — handle each:
   • Uncommitted changes present → stage explicit paths (avoid git add -A / git add . to skip stray secrets), then git commit using the CLAUDE.md format (imperative summary + bullets + Co-Authored-By: Claude <model name> <noreply@anthropic.com>)
   • Working tree clean, branch is ahead of $DEFAULT_BRANCH → skip the commit step; the existing commits get PR'd as-is
   • Working tree clean, no commits ahead → already caught by preflight; should not reach here
4. Push, setting upstream on first push:

   git push -u origin <branch-name>

5. Create the PR:

   gh pr create --base "$DEFAULT_BRANCH" --title "..." --body "$(cat <<'EOF'
   ## Summary
   - <bullet 1>
   - <bullet 2>
   
   ## Test plan
   - [ ] <test step>
   - [ ] <test step>
   EOF
   )"

---

PR body conventions

• Title: short, imperative, mirrors the commit summary (under ~70 chars)
• Body sections: always ## Summary then ## Test plan
• Summary: 1–3 bullets, focus on the what and why — skip routine adds/removes
• Test plan: bulleted checkbox list of how to verify the change
• No trailing emoji/footer line. The Co-Authored-By goes on the commit, not the PR body.

Notes

• gh pr create in jj-colocated repos always needs --repo <owner/repo> because the git side has no branch checked out
• jj git push --named <name>=<rev> creates the bookmark and pushes in one step — no separate jj bookmark create needed
• Never skip pre-commit hooks (--no-verify) or signing flags. If a hook fails, fix the underlying issue and re-stage

3-total-review.SKILL.md

name	total-review
description	Run every available review tool in parallel for maximum code analysis coverage. Use this skill whenever the user asks for a "full review", "complete review", "review everything", "total review", "deep review", "comprehensive review", or wants to run all review tools at once. Also use when the user mentions wanting both AI reviews and specialized analysis (error handling, test coverage, type design, etc.) in one go.
allowed-tools	Bash(codex:*, coderabbit:*, node:*, jj:*, git:*), Agent, Skill
argument-hint	quick|deep|custom instructions

Orchestrate all review tools in parallel to give comprehensive feedback on code changes. This combines six categories of tools:

1. AI reviewers (Codex /codex:review, CodeRabbit CLI, feature-dev code-reviewer) — broad coverage from multiple AI perspectives with different strengths
2. Adversarial reviewer (Codex /codex:adversarial-review) — challenges design choices, assumptions, and tradeoffs (deep mode only)
3. Security reviewer (Anthropic's /security-review methodology, run as a backgrounded agent) — identifies security vulnerabilities, exposed secrets, unsafe patterns
4. Specialized analysis agents (pr-review-toolkit) — deep, focused analysis of specific quality dimensions
5. Cost reviewers (D1 query cost via cf-d1-cost, conditional on the diff) — catches runtime/billing cost that correctness review can't see
6. Code simplifier (conditional Phase 2) — refines code after other reviews pass

The value of running them together is that each tool catches different things: Codex, CodeRabbit, and feature-dev find general issues from different AI perspectives, the adversarial reviewer questions design decisions, the security reviewer catches vulnerabilities that general reviewers miss, the pr-review-toolkit agents go deep on error handling, test coverage, and type design, and the D1 cost reviewer catches the one axis all of them share a blind spot on — a query can be perfectly correct and still scan 100× the rows it returns, invisible until the bill arrives.

Arguments

$ARGUMENTS

• (no args) → Standard mode: all tools except code-simplifier. If no must-fix issues survive triage, code-simplifier runs automatically.
• quick → Codex + CodeRabbit + feature-dev only (3 broad AI reviewers)
• deep → All tools including code-simplifier, regardless of must-fix issues
• Any other text → Standard mode with text forwarded as custom instructions

Mode keywords can be combined with instructions: deep focus on security runs deep mode with "focus on security" passed to tools.

Workflow

1. Detect VCS and prepare

Some agents need git state, so for jj repos we sync first.

test -d .jj && echo "jj" || echo "git"

If jj:

jj git export

Resolve what to review before capturing it. A bare git diff shows only the working tree, which is empty whenever the work has already been committed — and a caller like /ship-it commits and pushes before asking for a review. Reviewing an empty diff produces a confident Verdict: 0 must-fix that the loop skills read as positive evidence, so getting this range right is load-bearing.

Pick the first range that is non-empty:

# git
git diff HEAD                                   # 1. uncommitted work, staged or not
git diff $(git merge-base HEAD origin/HEAD)..HEAD   # 2. else the branch vs its base
git show HEAD                                   # 3. else the last commit

# jj
jj diff                                         # 1. the working copy
jj diff -r 'latest(::@ & ~empty())'             # 2. else the newest non-empty change

If origin/HEAD is not set, substitute the actual base branch (main, develop, …). When the caller knows the range — /ship-it knows its PR's base — prefer what it passes over auto-detection.

An empty diff is an error, not a clean review. If all of the above come back empty, do NOT proceed and do NOT emit a zero-finding verdict. Stop and report that there was nothing to review, naming the ranges tried. A review that examined nothing must never be reportable as a review that found nothing — that is the input-side twin of the fail-closed rule the consumer skills apply to the output.

State the resolved range in the report header so the reader knows what was actually examined.

For large diffs (>500 lines), include only the --stat summary and instruct agents to read specific files themselves. This prevents token overflow.

2. Analyze changes to determine which agents to run

Not every agent is useful for every changeset. Use the diff stat to decide which specialized agents apply:

Tool	Type	Run when...	Skip when...
Codex review (/codex:review)	command	Always	—
Codex adversarial (/codex:adversarial-review)	command	Deep mode	quick/standard
CodeRabbit (coderabbit:code-reviewer)	agent	Always (plugin required)	plugin not installed → use Bash fallback
feature-dev (feature-dev:code-reviewer)	agent	Always	—
security-review (/security-review methodology, as agent)	agent	Standard/deep modes	quick mode
pr-toolkit: code-reviewer (pr-review-toolkit:code-reviewer)	agent	Standard/deep modes	quick mode
pr-toolkit: silent-failure-hunter (pr-review-toolkit:silent-failure-hunter)	agent	Diff contains try/catch, error handling, async code	Only config/docs changed
pr-toolkit: comment-analyzer (pr-review-toolkit:comment-analyzer)	agent	Diff contains comments or docstrings	No comments in diff
pr-toolkit: pr-test-analyzer (pr-review-toolkit:pr-test-analyzer)	agent	Test files exist in diff	No test files changed
pr-toolkit: type-design-analyzer (pr-review-toolkit:type-design-analyzer)	agent	Type/interface/class definitions in diff	No type changes
D1 query cost (cf-d1-cost heuristics)	agent	Diff touches Cloudflare D1 queries (.prepare(/SQL strings), migrations/*.sql, or wrangler D1 bindings	No D1 / SQL in diff

When in doubt, include the agent — a quick "no issues" is better than missing something.

3. Launch all reviews in parallel

Parallel execution is key. Launch everything concurrently rather than waiting for each tool sequentially.

Launch ALL tools in a SINGLE message. Use Bash run_in_background: true for Codex commands and Agent run_in_background: true for agents.

The no-self-filter rule — applies to EVERY reviewer in this step

This rule governs all reviewer briefs in step 3, including the ones defined below it. No reviewer is exempt except the one named at the end.

Every brief must say: report every finding, including low-confidence and low-severity ones, each tagged with confidence (0-100), severity, and file:line. Do not self-filter. Ranking and cutting happen once, in step 4b.

This is not optional politeness — current models follow a "high-severity only" brief literally: they find the bug, judge it below the stated bar, and never mention it. Precision rises while recall quietly drops, and the finding is unrecoverable because you never saw it. Several of these agents hardcode a confidence gate in their own definitions (feature-dev:code-reviewer and pr-review-toolkit:code-reviewer both cut at >=80), so their prompts must actively override that gate — omitting a filtering instruction is not enough. Tools with their own severity model that you cannot prompt away (Codex, the CodeRabbit CLI) get the sentence anyway, and whatever tiering they emit is treated as input to 4b, never as a gate.

The one sanctioned exception: security-review keeps its >=8/10 confidence bar. The honest reason is fidelity, not calibration: that bar and its hard-exclusion list are lifted verbatim from Anthropic's own ruleset, and the value of running it is that it reproduces that ruleset faithfully. The two are not interlocked — the exclusion list drops whole categories (DOS, rate limiting, theoretical timing attacks) and keeps doing so at any bar, while the confidence bar drops low-confidence findings inside the surviving categories, so a 6/10 authz bypass is lost and never reaches 4b. That recall cost is real and accepted deliberately. Do not "fix" this for consistency, and do not repeat the false claim that the bar and the list only work as a pair.

Codex Review (all modes)

Both Codex commands run through the companion script and cannot be given a custom brief, so their own severity vocabulary is unavoidable. Treat every tier they emit — including the lowest — as a finding entering step 4b. Never drop a Codex item because Codex itself ranked it low.

Launch via Bash with the codex-companion script. Always use --background flag so the script runs non-interactively, and set run_in_background: true on the Bash call.

Bash({
  command: `node "${CLAUDE_PLUGIN_ROOT}/scripts/codex-companion.mjs" review --background`,
  description: "Codex review",
  run_in_background: true
})

The CLAUDE_PLUGIN_ROOT environment variable is set by the codex plugin at runtime. If unavailable, resolve dynamically:

node "$(ls -d ~/.claude/plugins/cache/openai-codex/codex/*/scripts/codex-companion.mjs | tail -1)" review --background

Codex Adversarial Review (deep mode only)

Challenges design choices, assumptions, and tradeoffs — not just implementation defects.

Bash({
  command: `node "${CLAUDE_PLUGIN_ROOT}/scripts/codex-companion.mjs" adversarial-review --background`,
  description: "Codex adversarial review",
  run_in_background: true
})

CodeRabbit Review (all modes)

Preferred: the coderabbit plugin (from claude-plugins-official) provides a coderabbit:code-reviewer subagent that wraps the CLI.

Agent(subagent_type: "coderabbit:code-reviewer", run_in_background: true)
Prompt: Review the code changes in this repository. REPORT EVERY FINDING, including low-confidence and low-severity ones, each tagged with confidence (0-100), severity, and file:line. Do NOT self-filter — ranking and cutting happen downstream. [include diff or --stat + file list] [custom instructions]

Fallback when the plugin is not installed: invoke the CLI directly via Bash. The review can take 7-30 minutes — backgrounding is essential.

Bash({
  command: `coderabbit review --agent --type all 2>&1`,
  description: "CodeRabbit review",
  run_in_background: true,
  timeout: 1800000
})

--agent emits NDJSON: one {"type":"finding", ...} row per issue, terminated by {"type":"complete","status":"review_completed","findings":N}. Parse line-by-line; the complete row is the stop signal. If the repo is NOT enrolled in a CodeRabbit Organization the CLI silently degrades to a "limited/free" mode — findings still appear but the analysis is shallower; an unexpectedly empty review is the tell.

If the coderabbit:code-reviewer agent call returns Agent type ... not found, the plugin isn't loaded — fall back to the Bash form and tell the user to /plugin install coderabbit@claude-plugins-official (or verify the installPath in ~/.claude/plugins/installed_plugins.json matches the OS).

feature-dev Code Review (all modes)

Agent(subagent_type: "feature-dev:code-reviewer", run_in_background: true)
Prompt: Review the following code changes for bugs, logic errors, security vulnerabilities, and code quality issues. REPORT EVERY FINDING, including low-confidence and low-severity ones, each tagged with confidence (0-100), severity, and file:line. Your agent definition tells you to apply confidence-based filtering and report only high-priority issues — override that: do NOT self-filter, report the full list. Ranking and cutting happen downstream. [include diff or --stat + file list] [custom instructions]

Security Review (standard/deep modes)

Anthropic's /security-review skill is hard-wired to git diff origin/HEAD... and runs synchronously in the main thread — it breaks when there is no origin/HEAD symref (no remote, or a fresh local branch) and reviews a different range than the rest of this panel. So instead of Skill(skill: "security-review"), run its methodology as a backgrounded agent fed the SAME diff captured in step 1. This keeps it truly parallel, scope-consistent with the other reviewers, and free of the origin/HEAD dependency.

Agent(subagent_type: "general-purpose", model: "sonnet", run_in_background: true)
Prompt:
You are a senior security engineer doing a focused security review of the diff below. Mirror Anthropic's `/security-review`: report ONLY HIGH-CONFIDENCE (>=8/10), newly-introduced, concretely-exploitable vulnerabilities — not a general code review. Read referenced files directly to confirm before reporting.

CATEGORIES: injection (SQL / command / XXE / template / NoSQL / path-traversal), auth/authz bypass & privilege escalation, session/JWT flaws, hardcoded secrets / weak crypto / improper key storage / cert-validation bypass, RCE via deserialization (pickle / YAML / eval), XSS (reflected/stored/DOM), sensitive-data & PII exposure.

HARD EXCLUSIONS (never report): DOS / resource exhaustion; secrets-on-disk that are otherwise secured; rate limiting; memory/CPU exhaustion; missing hardening or best-practice gaps; theoretical race/timing attacks; outdated third-party deps; memory-safety issues in memory-safe languages; test-only files; log spoofing / un-sanitized input to logs; path-only SSRF; user-controlled content in AI prompts; regex injection; regex DOS; findings in documentation/markdown files; missing audit logs.

PRECEDENTS: env vars & CLI flags are TRUSTED — any attack needing to control them is invalid; command injection in shell scripts is invalid unless there is a concrete untrusted-input attack path; client-side auth/permission gaps are not vulnerabilities (backend validates); only include a MEDIUM finding if it is obvious and concrete.

OUTPUT: for each surviving finding, markdown — `# <category>: file:line`, then `Severity` (HIGH/MEDIUM), `Confidence` (N/10), `Description`, `Exploit Scenario`, `Recommendation`. If nothing clears the >=8 confidence bar after exclusions, output exactly "No qualifying security findings" and stop. Read-only — do not edit files or run mutating commands.

[include diff or --stat + file list] [custom instructions]

This mirrors the real /security-review ruleset (extracted from the Claude Code binary): OWASP-focused categories, the full hard-exclusion list, and the confidence>=8 gate that keeps it high-signal. The other AI reviewers cover security loosely; this is the only lens applying that exclusion list and confidence bar on purpose.

This is the one sanctioned exception to the no-self-filter rule — see that rule at the top of step 3 for the reasoning and for the recall cost being accepted.

pr-review-toolkit agents (standard/deep modes, conditional)

Launch each as Agent(subagent_type: "...", run_in_background: true):

• code-reviewer (pr-review-toolkit:code-reviewer) — CLAUDE.md compliance, bug detection, code quality. Its definition hardcodes "only report issues with confidence >= 80" — the prompt must explicitly override it and ask for everything, tagged.
• silent-failure-hunter (pr-review-toolkit:silent-failure-hunter) — try-catch analysis, empty catch blocks, swallowed errors, missing error propagation.
• comment-analyzer (pr-review-toolkit:comment-analyzer) — comment accuracy vs implementation, documentation completeness, comment rot.
• pr-test-analyzer (pr-review-toolkit:pr-test-analyzer) — behavioral test coverage, critical gaps, test quality assessment.
• type-design-analyzer (pr-review-toolkit:type-design-analyzer) — encapsulation, invariant expression, invariant usefulness, enforcement quality.

Each agent's prompt must include the diff (or --stat + file list for large diffs), any custom instructions, and the no-self-filter sentence verbatim — all five, not just code-reviewer. Only code-reviewer carries a hardcoded >=80 gate needing an explicit override, but the other four still need to be told to tag confidence and severity so 4b has something to triage.

D1 query cost reviewer (standard/deep modes, conditional)

Run when the diff touches Cloudflare D1 queries, SQL, migrations, or wrangler D1 bindings. This is the lens every other reviewer structurally lacks: a D1 query that returns 20 rows but scans 2,300 is functionally correct, passes type/security/error-handling review, and then shows up as a bill. D1 charges per row scanned (rows_read), so cost is invisible to static correctness review — it has to be looked for on purpose.

Agent(subagent_type: "general-purpose", model: "sonnet", run_in_background: true)
Prompt: Read the skill at ~/.claude/skills/cf-d1-cost/SKILL.md first, then review the diff below for Cloudflare D1 query-COST regressions only (not correctness — other reviewers cover that). Flag, with file:line + the rows_read risk + the fix:
  1. LIMIT placed after GROUP BY / a JOIN so it can't push down (reads every parent×child row before limiting) — the subquery-LIMIT-first pattern is the fix.
  2. A WHERE-column combination with no matching composite index — cross-check the migrations/ directory; a COUNT or filter with no ORDER BY often makes the planner pick a low-selectivity single-column index and SCAN.
  3. FK / `*_id` / `*_trid` columns used in a JOIN with no index on them.
  4. A COUNT/aggregate (e.g. pagination `meta.total`) whose result the calling code may not actually consume — wasted scans.
  5. SELECT * or over-wide projections / unbounded result sets on hot list paths.
  If a local or remote D1 is reachable, confirm with `wrangler d1 execute … --command "EXPLAIN QUERY PLAN …"` (want SEARCH … USING INDEX, not SCAN). Report every candidate, tagged with confidence (0-100) — do not self-filter. [include diff or --stat + file list]

Error handling for agent failures

With many concurrent agents, partial failures are expected. Handle them gracefully:

• Timeout: Mark as ⏱️ Timeout in summary. Continue with other results.
• Error/crash: Mark as ❌ Error in summary. Include error message in the collapsible section.
• Never fail the entire review because one agent failed — aggregate whatever succeeded.
• But at least one reviewer must have succeeded. If every one errored or timed out, there is no review — report that and stop. Do NOT emit a Verdict: line: zero findings from zero reviewers is indistinguishable from a clean run, and the consumer skills would read it as a pass. This is the output-side twin of the empty-diff rule in step 1.

4. Aggregate and deduplicate results

Different tools often flag the same issue (e.g., Codex and code-reviewer both catch a null check). Deduplicate by file:line — when multiple tools flag the same location, combine them into one entry noting all sources. This makes the report actionable rather than repetitive.

Each tool ranks findings in its own vocabulary. When quoting a raw report in a collapsible section, normalize that vocabulary to a neutral three-tier scale — tool-high / tool-mid / tool-low — so the reports read consistently. The tier names are deliberately not the bucket names: these describe what the tool said, and buckets describe what we decided.

Tool	→ tool-high	→ tool-mid	→ tool-low
Codex review	"critical", "bug", "security" in text	"should", "consider"	other
Codex adversarial	Design flaws, wrong assumptions	Questionable tradeoffs	Alternative approaches
CodeRabbit	Security/Bug category	High priority	Medium/Low
feature-dev	its own "highly confident / important" band	its own "moderate" band	anything lower
security-review	HIGH severity	MEDIUM severity	LOW (rarely emitted; <8 confidence filtered out)
pr-toolkit: code-reviewer	confidence 90-100 ("Critical")	confidence 80-89 ("Important")	below 80 — reaches you only once its gate is overridden
pr-toolkit: silent-failure-hunter	CRITICAL	HIGH	MEDIUM
pr-toolkit: comment-analyzer	"Critical Issues"	"Improvement Opportunities"	"Recommended Removals"
pr-toolkit: pr-test-analyzer	"Critical Gaps" (8-10)	"Important Improvements" (5-7)	1-4
pr-toolkit: type-design-analyzer	any of its four X/10 ratings at 1-4	5-6	7+
D1 query cost	SCAN on a hot path / unbounded rows_read	missing composite index / unused COUNT	wide projection, minor

Where a tool's own definition is vaguer than a row above suggests, the row is a reading aid, not a spec — trust the tool's actual output.

This table exists only to fill the per-tool Issues | N counts in the summary and to keep the collapsible raw reports readable. It assigns no bucket and gates nothing: every finding goes through step 4b whatever tier it lands in, a tool's own "critical" is evidence of nothing but that tool's opinion, and a finding the briefs now compel a tool to emit at confidence 20 is triaged on its merits like any other. Never drop a finding for landing in tool-low, and never let an unmappable tier be a reason to discard.

4b. Triage: what must be fixed, what can be ignored

Tool-reported severity is input, not verdict. Reviewers are deliberately told not to self-filter (see the briefs in step 3), so the raw pile always contains findings describing failures that cannot happen in this codebase, or that cost nothing if they do. Triage is where that gets cut. Do it here, in the main thread — never delegate it, and never pass a tool's severity through unexamined.

For every finding, answer these four questions before assigning a bucket:

1. Is it true? — go read the code and confirm the described behavior actually exists. Broad AI reviewers routinely misread context and report a missing check that is three lines up. A finding whose premise is false is not a finding.
2. Reachability — can the failing path be reached by a caller, input, or config that exists in this repo today? Read the call sites; do not assume. A finding whose trigger requires a caller that doesn't exist, an input the code never produces, or a config nobody sets is not real. Code behind an existing-but-disabled feature flag counts as reachable — the flag can be turned on without a code change.
3. Trigger — state a concrete input/state → wrong output/crash in one sentence.
4. Blast radius — if it does fire: data loss, security breach, corruption, user-visible break, silent wrong data — versus a log line, a slower cold path, or nothing observable.

Then bucket. Apply these tests in order; the first match wins.

#	Bucket	Rule
1	Ignored	Any disqualifier below applies. Listed with a one-line reason, never silently dropped.
2	Out of Scope	Real, but the diff neither introduced it, touched it, nor newly made it reachable. Reported in its own section, not fixed here, and it never blocks. Applies at any severity — a severe pre-existing bug is still reported, just not as this review's work. If the diff added the caller that made a previously-dead bug live, the diff did make it reachable: that is in scope, not out.
3	Must Fix	Reachable today, impact is more than cosmetic, and (blast radius is data loss / security / corruption / user-visible break, or it fires on the normal path rather than an edge). A named concrete trigger is mandatory.
4	Should Fix	Everything else that is real: reachable or reachability unconfirmed, edge-case-only or hard to articulate, damage contained or recoverable.

Ignored disqualifiers:

• Premise false — you read the code and the described behavior does not exist. Say what you checked. This is the most common false-positive class from the broad AI reviewers, and it is the only disqualifier that overrides the anti-rules below.
• Unreachable — no existing caller, input, or config can trigger it (defensive code against a case that cannot occur).
• No mechanism named — the reviewer restated a rule ("input isn't validated here", "this lacks a null check") without naming what actually breaks. Distinguishing this from a real-but-hard-to-phrase defect is a code question, not a prose question: go look. If the code shows a way for it to break, it is real and the anti-rules apply; if you looked and there is none, it is Premise false. Never route a finding here just because the reviewer wrote it vaguely.
• Style/taste with no behavioral difference and no linter enforcing it. An explicit CLAUDE.md / AGENTS.md convention is not taste — most are deliberately unenforced by tooling, and violating one is a real finding.
• Hypothetical future-proofing — only matters under a requirement that doesn't exist yet.
• Cosmetic impact — worst case is a log line, a marginally slower non-hot path, or a branch that never runs. Cosmetic stays cosmetic even when it fires on the normal path; frequency does not upgrade impact.
• Duplicate — same root cause as an entry already listed, reported at a different location (step 4 already merged same-file:line). A duplicate is merged into the existing entry, not listed in Ignored and not counted in M; note the extra location on the entry it merges into.

Anti-rules, so the cut doesn't eat real bugs. These outrank the bucket table — except that a finding failing test 1 is out regardless, since a bug that isn't there cannot be protected:

• Never demote because only one tool reported it. Single-source ≠ false positive — the specialized agents exist precisely to be the only lens that sees their axis, and in practice the most serious finding of a run is often the one nobody else saw.
• Never demote on "unlikely input" alone when the blast radius is security or data loss. Low probability × catastrophic stays Must Fix.
• If reachability is still unclear after reading the code, it goes to Should Fix and you say what couldn't be confirmed. Never resolve uncertainty by ignoring.
• Not being able to write the trigger sentence caps a finding at Should Fix — never Ignored. The bug classes hardest to state in one line (TOCTOU windows, ordering-dependent state corruption, cross-async invariant breaks) are real ones. Absence of a tidy sentence is a limit of the phrasing, not evidence the bug isn't there — but it is also not a reason to keep a finding you have positively disproved.
• Triage is per-finding, never per-tool. Don't dismiss a tool's whole output in one line.

On the reported confidence numbers. The briefs compel every reviewer to tag findings 0-100, and CLAUDE.md places an 80+ bar at this aggregation stage. This rubric deliberately supersedes that numeric bar: reachability and blast radius are better predictors than a model's self-reported confidence, which is poorly calibrated across tools and not comparable between them. Confidence is still used — as a priority hint for which findings to verify first (test 1 costs real reading time; start with the low-confidence ones, since those are where false premises cluster). It is never on its own a reason to bucket a finding anywhere.

When two anti-rules disagree, blast radius wins. A security or data-loss finding whose trigger can't be phrased is Must Fix, not capped at Should Fix.

The anti-rules never reach into Out of Scope. They govern the Ignored / Must Fix / Should Fix decision only. A pre-existing bug stays Out of Scope no matter how severe — that is the bucket's entire purpose, and letting an anti-rule promote it back to Must Fix would re-blocking exactly the work "Goal First, Findings Later" says isn't this review's job.

Output format

# Total Review Results

**Reviewed:** `<the resolved diff range>` — N files, +N/-N

## Summary
| Tool | Status | Issues |
|------|--------|--------|
| Codex review | ✅ Done | N |
| Codex adversarial | ✅/⏭️ | N |
| CodeRabbit | ✅ Done | N |
| feature-dev | ✅ Done | N |
| security-review | ✅/⏭️ | N |
| pr-toolkit: code-reviewer | ✅/⏭️ | N |
| pr-toolkit: silent-failure-hunter | ✅/⏭️ | N |
| pr-toolkit: comment-analyzer | ✅/⏭️ | N |
| pr-toolkit: pr-test-analyzer | ✅/⏭️ | N |
| pr-toolkit: type-design-analyzer | ✅/⏭️ | N |
| D1 query cost | ✅/⏭️ | N |

**Verdict: N must-fix · N should-fix · N out-of-scope · N ignored** (of M findings after step 4 dedup)

**The `Verdict:` line is a contract with the skills that consume this report** (`/review-fix-loop`, `/ship-it`). Emit it verbatim in this shape on **every** run, with all four counts present — including a completely clean run, which reports `Verdict: 0 must-fix · 0 should-fix · 0 out-of-scope · 0 ignored (of 0 findings after step 4 dedup)`. Never replace it with prose like "no issues found": those consumers are fail-closed and will treat a missing or reworded line as *unreadable*, not as zero, and loop until their iteration cap. Use these exact tokens in the line — `must-fix`, `should-fix`, `out-of-scope`, `ignored` — lowercase and hyphenated, regardless of how the section headings are capitalised.

The four counts sum to M. Duplicates merged during triage are folded into the entry they duplicate and are not counted. State the pre-dedup total separately if it's interesting; M is always the post-dedup number.

## Must Fix
Blocking — this is the only bucket that gates the loop skills. Each entry names the trigger; no trigger, no must-fix.

- **[source]** description `file:line`
  - Trigger: <concrete input/state → wrong output/crash>
  - Impact: <data loss / security / corruption / user-visible break>, on the <normal path / an edge case>

## Should Fix
Real, but edge-case-only, unconfirmed, or contained. Worth fixing; does not block.

- **[source]** description `file:line` — <why it's not blocking>

## Out of Scope
Pre-existing issues this diff didn't introduce or touch. Findings, not tasks — surfaced for a later call, at any severity.

- **[source]** description `file:line`

<details><summary>Ignored (N) — why each was cut</summary>

- **[source]** description `file:line` — <premise false / unreachable / no mechanism named / style / hypothetical / cosmetic>

</details>

## What's Good
- **[source]** positive observation

<details><summary>Full Codex Report</summary>
[raw output]
</details>

<details><summary>Full CodeRabbit Report</summary>
[raw output]
</details>

<details><summary>Full feature-dev Report</summary>
[raw output]
</details>

<details><summary>Full Security Review</summary>
[raw output]
</details>

[... collapsible section for each tool that ran]

5. Code simplifier (conditional Phase 2)

The simplifier refactors for clarity and maintainability, so it makes sense to run it only after substantive issues are resolved — otherwise you'd simplify code that needs to change anyway.

• deep mode → always run
• standard mode, 0 must-fix → run automatically
• standard mode, must-fix exist → skip, suggest: "Fix the must-fix issues first, then /total-review deep"

Launch via Agent with subagent_type: "pr-review-toolkit:code-simplifier".

6. Next steps

## Next Steps
1. Fix N must-fix issues (blocking)
2. Decide on N should-fix issues
3. Re-run `/total-review` to verify

Neither Ignored nor Out of scope generates a next step — that's the point. Ignored was cut on the merits; out-of-scope findings are the user's call to schedule, per "Goal First, Findings Later". Don't reopen either as "consider these too".

Examples

/total-review                              # Standard: all tools
/total-review quick                        # Codex + CodeRabbit + feature-dev only
/total-review deep                         # Everything + code-simplifier
/total-review focus on security            # Standard + security focus
/total-review deep check error handling    # Deep + error handling focus

4-merge-cleanup.SKILL.md

Merge the current branch's pull request and clean up local and remote branches.

Reply in Japanese.

Detect VCS

First, check if .jj/ directory exists in the project root. If yes, use the jj workflow. Otherwise, use the git workflow.

---

jj Workflow

1. Run jj log to understand current state and find the bookmark/branch for the PR
2. Detect the GitHub repo from jj git remote list (parse origin URL for owner/repo)
3. Find the PR number:
   • Use gh pr list --repo <owner/repo> --head <bookmark-name> to find it
   • Or accept a PR number as argument
4. Merge the PR: gh pr merge <number> --squash --delete-branch --repo <owner/repo>
   • Always pass --repo because jj's colocated git has no branch checked out
5. Sync and clean up:

   jj git fetch
   jj bookmark delete <local-bookmark>
   

6. Abandon orphaned changes (the original commit that was squash-merged on GitHub):
   • Check jj log for commits that are no longer on main
   • Abandon them with jj abandon <change-id>
   • Also abandon the current empty @ if it was parented on the old commit
7. Move to main: jj new main
8. Verify with jj log -r 'all()' --limit 5

git Workflow

1. Check current branch and ensure it's not main/master/develop
2. Find the associated pull request using gh
3. Merge the PR using gh (with squash or regular merge based on repo settings)
4. Switch to the default branch (main/master/develop)
5. Pull latest changes from remote
6. Check if local feature branch exists before attempting deletion
7. Check if remote feature branch exists before attempting deletion
8. Use git remote prune origin to clean up stale remote tracking branches

---

Important Notes

• Handle branch deletion gracefully - if gh pr merge --delete-branch already deleted the remote branch, don't attempt to delete it again
• In jj repos, gh commands need --repo <owner/repo> to avoid "not on any branch" errors
• After jj cleanup, verify no orphaned empty changes remain

Requirements

• Must have an open PR (or recently merged)
• GitHub CLI (gh) must be configured
• Must have merge permissions

5-cf-d1-cost.SKILL.md

name	cf-d1-cost
description	Audit and guard Cloudflare D1 query cost (rows_read / queryEfficiency) before it shows up on the bill. Use this skill whenever setting up a NEW Cloudflare D1 database or project, designing/adding tables, choosing what to index, writing or reshaping D1 SQL queries, or investigating a high/surprising D1 bill, slow query, or "rows read" spike. Trigger even when the user just says "I'm adding D1 to this project", "set up a D1 database", "what should I index on this table", "why is my Cloudflare bill high", "this query feels slow", or mentions wrangler d1 insights, rows_read, queryEfficiency, avgRowsRead, or EXPLAIN QUERY PLAN. D1 bills per row *scanned* (rows_read), not per row returned — a perfectly correct query can quietly cost 100× too much, so index and shape queries right up front and audit when the numbers look off. Not for D1 connection or auth errors, KV, Durable Objects, R2, general Workers pricing, or pure SQL syntax questions.
allowed-tools	Bash, Read, Grep, Edit, Write
argument-hint	database-name | "audit" | "setup"

Cloudflare D1 charges for rows read (scanned), not rows returned. A query that returns 20 rows but scans 2,300 is functionally correct and passes every code review — it just quietly costs 100× more than it should. Code review can't see this; only measurement can. This skill is the measurement.

Reply in Japanese (this user's preference) unless the surrounding project says otherwise.

When this fires

Two moments matter, and they need different actions:

• Proactive (new D1 project / new query) — bake in the guards before traffic arrives, so a cheap query stays cheap. Jump to § New D1 project checklist.
• Reactive (bill is high / query is slow) — find the offending query and fix it. Jump to § Audit an existing database.

The one metric that matters: queryEfficiency

queryEfficiency = rows_returned ÷ rows_read

• ≈ 1.0 — ideal. The query reads only what it returns.
• < 0.1 — red flag. Reading 10× (or 1000×) more than it hands back. Almost always a missing/wrong index or a LIMIT that can't push down.

avgRowsRead is the companion: a query with high avgRowsRead and low queryEfficiency is the one burning money.

Audit an existing database

wrangler d1 insights is the native, built-in profiler. It captures query strings (params stripped) and ranks them. Run it sorted by rows read:

# Costliest queries by total rows read over the last 7 days
CLOUDFLARE_ACCOUNT_ID=<acct> npx wrangler d1 insights <database-name> \
  --sort-type=sum --sort-by=reads --limit=10 --timePeriod=7d

# Also useful: by average time, and by count (find hot paths)
npx wrangler d1 insights <database-name> --sort-type=avg --sort-by=time --limit=10
npx wrangler d1 insights <database-name> --sort-type=sum --sort-by=count --limit=10

Flags: --sort-by = reads|writes|time|count, --sort-type = sum|avg, --timePeriod default 1d. The command is experimental — if it's missing, fall back to the GraphQL d1QueriesAdaptiveGroups dataset (see Cloudflare D1 metrics docs) or, for one specific query, measure directly: wrangler d1 execute <db> --remote --json --command "<sql>" returns meta.rows_read.

For any suspect query, confirm the plan:

wrangler d1 execute <database-name> --remote \
  --command "EXPLAIN QUERY PLAN <the suspect SELECT>"

You want to see SEARCH … USING INDEX (ideally USING COVERING INDEX). If you see SCAN <table>, that table has no usable index for this query — that's the leak.

注意：analytics の「累計」は集計窓に依存する

d1AnalyticsAdaptiveGroups を独自の窓で合計するときは、date_geq を必ず一緒に控えること。 Cloudflare の accumulated 系メトリクスは暦月ではなく指定した窓の先頭から積み上がるので、 同じ日付でも窓が違えば違う数字が出る。ドル額は請求側が正、analytics は診断・傾向用。 wrangler d1 insights --timePeriod=1d|7d で「いま重いクエリ」を見るぶんには問題ない。

Fix playbook (most D1 cost bugs are one of these three)

1. Missing or wrong index → planner does a SCAN

SQLite's planner can pick a low-selectivity single-column index over the composite you actually need, especially for COUNT/filter queries with no ORDER BY. Add a composite index covering the exact WHERE-column combination, then re-check EXPLAIN QUERY PLAN:

-- WHERE type=? AND lang=? AND status=?  → index all three, in filter order
CREATE INDEX IF NOT EXISTS idx_posts_type_lang_status ON posts(type, lang, status);

After adding indexes to a populated DB, run ANALYZE <table>; so the planner has fresh stats. Keep the old single-column index if other queries depend on it — add, don't swap blindly.

2. LIMIT after GROUP BY / JOIN → reads everything before limiting

SQL evaluation order is FROM/JOIN → WHERE → GROUP BY → then LIMIT. A list query that LEFT JOINs a child table and GROUP BYs before LIMIT 20 reads every parent×child row first. Push the LIMIT into a subquery so it bounds the rows before the join:

-- expensive: joins all rows, groups, then limits
-- cheap: limit the parent rows first, then join the child only for that page
SELECT p.*, GROUP_CONCAT(t.name) AS tags
FROM (SELECT * FROM posts WHERE type=? AND status='publish'
      ORDER BY date DESC LIMIT ? OFFSET ?) p
LEFT JOIN post_tags pt ON pt.post_id = p.id
LEFT JOIN tags t ON t.id = pt.tag_id
GROUP BY p.id ORDER BY p.date DESC;

3. Relation/FK filter does a full table SCAN

Joining on a foreign-key column with no index (post_credits.member_trid, junction tables, etc.) scans the whole child table per request. Index the FK column.

Lock the win in: a rows_read regression test

A fix is worthless if the next refactor silently reverts it — the response stays correct while rows_read quietly explodes again, and no normal test notices. D1 returns meta.rows_read on every query, so assert on it. This is the same idea as Laravel's assertMaxRowsExamined or Django's assertNumQueries, hand-rolled for D1:

• In a Workers/Vitest test, mock or run the query and assert the route issues the expected number of D1 calls and that the SQL keeps its shape (e.g. LIMIT lives inside the subquery, the composite index columns are all in the WHERE).
• Or, against a prod-shaped staging DB, run the real query via wrangler d1 execute --json and assert meta.rows_read < threshold.

Name the test something like "perf invariant: /posts list stays under N rows_read" so a future reader knows it's a cost guard, not a correctness check.

The backstop you set once: budget alerts

No pre-release check catches every cost class (vendor billing-model gotchas, traffic spikes, a new endpoint nobody profiled). A Cloudflare budget alert is the universal net — it's daily, fires when projected spend first crosses your threshold, and needs zero code. Set one early:

Dashboard → Manage Account → Billing → Budget alerts → Create budget alert (docs).

Pick a threshold a bit above normal monthly spend. This would have caught a 10× D1 spike on day one instead of at the invoice.

New D1 project checklist

When standing up a fresh D1 database or adding D1 to a project, do these up front — they're nearly free now and expensive to retrofit after a bill surprise:

1. Index every column you filter or join on. Especially: status/type/lang enums used in list queries (composite, in WHERE order), and every FK / *_id / *_trid column used in a JOIN. Verify with EXPLAIN QUERY PLAN on your real list/detail/filter queries — no SCAN on a table that grows.
2. Write list queries LIMIT-first (subquery pattern above) so pagination never reads the whole table.
3. Add at least one rows_read regression test on your hottest list endpoint.
4. Set a Cloudflare budget alert for the account.
5. Schedule a periodic wrangler d1 insights check (a cron, a CI step, or a calendar reminder) so queryEfficiency is watched continuously, not just when a bill scares you.

Then, after the first real traffic lands, run wrangler d1 insights … --sort-by=reads once to confirm nothing has a surprising avgRowsRead. Measuring early — while the table is small and the fix is one index — is the whole game.