Example claude skill to automatically fix rack timeouts reported via sentry

CASES.md

Rack Timeout Fixes — Case Log

Past fixes indexed by bottleneck pattern. Read the entry that matches your current timeout shape; skip the rest.

Table of Contents

#	Endpoint	Pattern
1	Redacted#orders	Post-join ILIKE → subquery short-circuit
2	[... others ...]

---

1. Orders report search

Endpoint: Redacted#orders PR: [redacted] File: redacted.rb — with_search scope Pattern: Post-join filter on ILIKE across LEFT JOIN'd tables

Problem: UI searches orders by number with no date filter. with_search ran 13-column ILIKE across 7 LEFT JOIN'd tables, applied after joining all 52K payouts. Addresses PK lookup: 35s I/O on 61K cold blocks.

Root cause: Postgres can't push an ILIKE predicate down when it spans columns across multiple LEFT JOIN'd tables. Every row fully joined before filtering.

Fix: Regex-detect num format (same patterns as Foo::Search and Bar::Search), then use foo_id IN (SELECT id FROM orders WHERE num = ?) instead of ILIKE. Postgres hashes the subquery once, filters during join.

Before/After:

                Before        After
Execution       14,281 ms     54 ms
Buffers read    107,436       2
I/O time        40,952 ms     1.2 ms
Rows filtered   52,301        1
Addr lookups    52,301        1

Takeaway: When a search scope does ILIKE across columns on multiple LEFT JOIN'd tables, check if the search term matches a known format with an indexed column. Short-circuit to a subquery on that index instead of the broad ILIKE.

---

[... other cases ...]

SKILL.md

name	fix-rack-timeouts
description	Investigate and fix Rack::Timeout production errors from a Sentry issue URL.
argument-hint	<sentry-issue-url>
disable-model-invocation	true

Fix Rack Timeouts

End-to-end workflow for diagnosing and fixing Rack::Timeout::RequestTimeoutException errors from a Sentry issue URL. Pulls the event context (params, studio, stacktrace), traces the code path, runs EXPLAIN (ANALYZE, BUFFERS) on prod, identifies the bottleneck, proposes and verifies a fix, then implements it.

Phase 1: Extract context from Sentry

Pull both the issue summary and the latest event.

The request params are often missing from the top-level query field — check .context on the event instead.

~/bin/sentry-api /api/0/issues/{issue_id}/ > .agent-workdir/sentry-issue.json
~/bin/sentry-api /api/0/issues/{issue_id}/events/latest/ > .agent-workdir/sentry-event.json

Extract and report:

• Endpoint: controller#action, HTTP method, URL
• Params: from .context.params (NOT .entries[].data.query — it's often empty)
• Studio/User: from .context.studio_id, .context.studio_name
• Stacktrace: in-app frames only (.entries[] | select(.type == "exception"))

The params are critical — they determine which scopes fire and whether filters narrow the dataset. A search with no date filter is a different problem than a search scoped to one month.

Phase 2: Trace the code path

Starting from the controller action in the stacktrace:

1. Read the controller method
2. Follow into the service/model that builds the query
3. Identify which scopes apply given the actual params from Phase 1
4. Read each scope that fires — note joins, filters, and subqueries

Map out what the query does:

• How many tables are joined?
• Which filters apply (and which DON'T because params are missing)?
• Where is the search/filter applied — before or after joins?

Phase 3: EXPLAIN (ANALYZE, BUFFERS) on production

Recon the tenant's actual scale first (row counts for the tenant, status breakdown, subquery input sizes) — don't infer volume from the name (case #5: "Foo studio" had 10K orders, not Foobar Studio's 2.2M; the bottleneck was query shape, not data size). Cheap counts also reveal whether the timeout needs specific data present to reproduce (e.g. an aggregate that a merge join skips when the outer side is empty — benchmarking on a quiet day looks "fast").

Run the actual query against prod via rails runner on a worker pod (not API):

kubectl exec <worker-pod> --context pd-prod -n default -- rails runner '
  # Build the query using the same code path as the controller
  studio = Studio.find("<studio_id>")
  # ... replicate the service call with the exact params from Sentry ...
  query = service.data(params)
  sql = query.to_sql

  puts "=== SQL ==="
  puts sql
  puts ""
  puts "=== EXPLAIN (ANALYZE, BUFFERS) ==="
  result = ActiveRecord::Base.connection.execute("EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) #{sql}")
  result.each { |r| puts r["QUERY PLAN"] }
'

Getting the real SQL: to_sql lies for relations with includes that Rails promotes to eager_load (the LEFT OUTER JOINs and t0_r* selects won't appear), and .explain's analyze API differs across Rails versions — don't guess it. Reliable method: build the relation via the exact controller chain, execute it once with an ActiveSupport::Notifications.subscribe('sql.active_record') subscriber capturing payload[:sql] + payload[:type_casted_binds], substitute the binds, then run raw EXPLAIN (ANALYZE, BUFFERS) via connection.execute. Remember pagination: legacy index endpoints run the page query AND count(:all) twice (sanitize + pagy) — time each to find which one burns the budget.

Key metrics to extract from the plan:

• Execution time vs the rack timeout (25s)
• Shared buffers hit vs read — high reads = cold cache, I/O-bound
• I/O timings — which join/scan dominates
• Rows before filter vs rows returned — high ratio = filter applied too late
• Loop counts on index scans — N+1-style PK lookups across large result sets
• Seq scans on large tables — missing index or planner can't use one

Present findings to the user before proposing a fix.

Phase 4: Identify the bottleneck

Common patterns in timeout queries:

Post-join filter

The WHERE/filter runs AFTER all LEFT JOINs complete. Every payout row joins to [redacted], etc. — then the filter throws away 99%+. Fix: push the filter earlier (subquery, CTE, or restructure the scope).

Missing date/year scope

The frontend omits a date filter, so the query scans all-time data. Check whether the frontend should be sending a date param, or whether the backend should default one.

ILIKE with leading wildcard on unindexed columns

%search% prevents index usage. If the search matches a known format (order number, phone number), short-circuit to an exact/prefix match on an indexed column.

N+1 index lookups

A nested loop does PK lookups in a tight loop (e.g., 52K address lookups). Fix by filtering earlier so fewer rows reach the join, or by restructuring to a hash join.

Unscoped cascading cleanup

A method mutates a targeted set of records (step 1), then runs follow-up cleanup queries that scan the entire platform instead of scoping to the records affected by step 1. The cleanup often returns 0 rows — the global scan is pure overhead. Fix: capture affected IDs before the mutation, scope the cleanup to those IDs.

Ordered-index dead-region walk (NULLS FIRST)

ORDER BY col DESC LIMIT n walks a global single-column index backward while the real filters (status, tenant-via-join) reject rows only after each heap fetch. DESC is NULLS FIRST, so a large NULL population (e.g., carts' submitted_at) sits at the start of the walk and every entry is visited and discarded. Fix: if NULLs are impossible in the result set (scope invariant — validate on prod), add col IS NOT NULL; it's a semantic no-op that becomes an Index Cond the btree seeks past. See case #4 before reaching for NULLS LAST — it can be far worse under eager_load.

Phase 5: Check existing patterns

Before writing a fix, see if theres existing patterns in other areas of the codebase to copy. Follow the existing pattern when applicable.

Phase 6: Create hotfix worktree

Before making any code changes (tests or fix), create a hotfix worktree:

1. Create the worktree at the repo-root .worktrees/ directory (e.g. /path/to/project/.worktrees/<name>), not nested inside another worktree. Base on master and target master.
2. Copy .env.local to the new worktree when using devenv.
3. All subsequent phases (tests, fix, verify, commit) happen in this worktree.

Phase 7: Ensure test coverage

Before making the fix, check whether the affected code path has test coverage:

1. Search for existing specs (grep -rn 'method_name' spec/)
2. If uncovered, write tests against the current (unfixed) code first and verify they pass
3. Follow existing spec patterns for seed data setup — check how similar specs in the codebase create their fixtures (e.g., foo_spec.rb for [redacted])
4. After applying the fix, re-run the tests to confirm they still pass

This ensures the fix doesn't silently break existing behavior and provides a regression safety net.

Phase 8: Implement and verify with EXPLAIN (ANALYZE, BUFFERS)

1. Make the code change
2. Re-run tests to confirm they still pass
3. If safe to do so, run the proposed query on prod and compare side by side:

kubectl exec <worker-pod> --context pd-prod -n default -- rails runner '
  # ... build both queries ...

  t1 = Process.clock_gettime(Process::CLOCK_MONOTONIC)
  slow_results = slow_query.to_a
  t2 = Process.clock_gettime(Process::CLOCK_MONOTONIC)

  t3 = Process.clock_gettime(Process::CLOCK_MONOTONIC)
  fast_results = fast_query.to_a
  t4 = Process.clock_gettime(Process::CLOCK_MONOTONIC)

  puts "Current: #{((t2 - t1) * 1000).round(1)}ms (#{slow_results.length} rows)"
  puts "Fixed:   #{((t4 - t3) * 1000).round(1)}ms (#{fast_results.length} rows)"
  puts "Speedup: #{((t2 - t1) / (t4 - t3)).round(0)}x"
'

Also run full EXPLAIN (ANALYZE, BUFFERS) on the fixed query to confirm the plan changed — check that:

• Rows before filter dropped significantly
• I/O time dropped
• The filter moved earlier in the plan (e.g., hashed SubPlan instead of post-join)

EXPLAIN every query shape the endpoint runs, not just the one that timed out. For paginated endpoints that means BOTH the pagy COUNT and the page query (ORDER BY + LIMIT), for EVERY tab/param variant. A rewrite that wins the timeout case can flip the planner onto a global ordered-index walk for a different tab — an expensive node's startup cost sometimes acts as an accidental barrier keeping the planner on a sane plan, and removing it exposes the flip (case #5: a LATERAL rewrite made late 100x cheaper but sent the fulfilled page query to 9s warm).

Present both plans side by side to the user before implementing.

4. Commit
5. Use the pull-request skill to compose and open the PR — do not draft the title or body manually

Phase 9: Postmortem

After opening the PR, perform a postmortem:

• Add a writeup to CASES.md if its unique
• Assess whether anything in this skill itself needs adjusted or added.

You are not required to make any changes during the postmortem, but you must consider whether you should.

Execution notes

• Always use worker pods for rails runner, never API pods
• Save all command output to files first, then process — never pipe curl/kubectl through jq
• Don't guess column names — check the schema, models, or migrations
• EXPLAIN the fix for every affected tenant/param variant in the Sentry events, not just the primary one — selectivity differences can flip the plan or the win
• Validate assumptions before implementing. Before committing to a fix approach, verify on prod: Is the global scope intentional (catch-all) or accidental? Are there pre-existing orphaned records the broad scan catches? Does destroy_all need callbacks, or could delete_all work? What's the upper bound of affected IDs in the scoped approach? Run counts on prod to answer these — don't assume.

---

Fixed instances

Past fixes are catalogued in CASES.md. Read it when pattern-matching a new timeout against known bottleneck shapes