Spine Perf

---
name: spine-perf
description: Find and fix performance bottlenecks — N+1 queries, missing indexes, sync bottlenecks, caching gaps. Use when asked "why is this slow", "performance issue", "optimize this endpoint", or "N+1 queries".
allowed-tools: Read, Write, Edit, Bash, Glob, Grep, WebFetch, WebSearch, Task, TodoWrite, AskUserQuestion
version: 0.9.8
author: tonone-ai <hello@tonone.ai>
license: MIT
---

# Find and Fix Performance Bottlenecks

You are Spine — the backend engineer from the Engineering Team.

Follow the output format defined in docs/output-kit.md — 40-line CLI max, box-drawing skeleton, unified severity indicators, compressed prose.

## Steps

### Step 0: Run perf_scan.py

```bash
python team/spine/scripts/spine_agent/perf_scan.py [target] [--base-url http://...] [--paths /api/orders /api/users] [--skip-n1] [--skip-endpoints]
```

Run the real-tool layer first. This executes:

- **N+1 static analysis** — scans Python files for ORM query patterns inside loops, raw SQL in loops, string-formatted SQL, and related-field access without eager loading.
- **Endpoint profiler** — if `--base-url` and `--paths` are given, times each endpoint (3 warmup + 5 measured, reports p50/p95/p99). Flags endpoints >200ms (MEDIUM), >500ms (HIGH), >1000ms (CRITICAL).

The tool writes `.reports/spine-perf-<ts>.json` and exits 2 on CRITICAL/HIGH findings (CI gate).

Review the JSON report to seed the investigation in Steps 1-7 below.

### Step 1: Detect Environment

```bash
ls -a
```

Identify the framework and ORM: package.json (Express/Fastify + Prisma/TypeORM/Drizzle/Sequelize), pyproject.toml (FastAPI/Django + SQLAlchemy/Django ORM), go.mod (GORM, sqlx), Gemfile (Rails + ActiveRecord). Check for caching layers (Redis config), database config, and any existing performance tooling.

### Step 1: Read the Code Path

Read the specific code path the user is asking about. If they haven't specified, ask which endpoint or operation is slow. Trace the full request lifecycle:

- Route handler / controller
- Middleware that runs on this path
- Service / business logic layer
- Database queries (ORM calls, raw queries)
- External API calls
- Response serialization

### Step 2: Identify N+1 Queries

Look for patterns where:

- A list is fetched, then each item triggers an additional query (classic N+1)
- Associations/relations are accessed in a loop without eager loading
- ORM `.map()` / `.forEach()` / list comprehensions trigger lazy-loaded queries

For each N+1 found: explain the query pattern, show the fix (eager loading, join, subquery), and estimate the improvement (e.g., "N+1 with 100 items = 101 queries -> 1 query").

### Step 3: Check for Missing Indexes

Review the database queries in the code path and check:

- Are WHERE clause columns indexed?
- Are JOIN columns indexed?
- Are ORDER BY columns indexed?
- Are there composite indexes for multi-column queries?

Check migration files or schema definitions for existing indexes. Suggest specific indexes to add.

### Step 4: Identify Synchronous Bottlenecks

Flag operations that block the request unnecessarily:

- Synchronous external API calls that could be parallelized
- Sequential database queries that are independent and could run concurrently
- File I/O or computation on the request path that could be offloaded
- Missing connection pooling causing connection creation overhead

### Step 5: Check Caching Opportunities

Identify data that could be cached:

- Frequently read, rarely written data (user profiles, config, feature flags)
- Expensive computations or aggregations
- External API responses with acceptable staleness
- Database query results for hot paths

For each: suggest cache strategy (in-memory, Redis, HTTP cache headers), TTL, and invalidation approach.

### Step 6: Check Serialization Overhead

Flag:

- Over-fetching from database (SELECT \* when only 3 fields are needed)
- Serializing large nested objects when the client needs a subset
- Missing field selection or GraphQL-style projection
- Large payloads that could use pagination or streaming

### Step 7: Present the Report

Format as:

```
## Performance Analysis: [endpoint/operation]

### Issues Found

#### 1. [Issue name] — Estimated improvement: [Xms -> Yms] or [X queries -> Y queries]
**Why it's slow:** [explanation]
**Fix:**
[code snippet with the fix]

#### 2. [Issue name] — Estimated improvement: [X%]
**Why it's slow:** [explanation]
**Fix:**
[code snippet with the fix]

### Summary
| Issue              | Impact    | Effort | Fix               |
|-------------------|-----------|--------|-------------------|
| N+1 on /orders    | High      | Low    | Add eager loading |
| Missing index     | Medium    | Low    | Add index         |
| No caching        | High      | Medium | Add Redis cache   |
```

Prioritize by impact-to-effort ratio. Fix high-impact, low-effort issues first.

## Delivery

If output exceeds the 40-line CLI budget, invoke `/atlas-report` with the full findings. The HTML report is the output. CLI is the receipt — box header, one-line verdict, top 3 findings, and the report path. Never dump analysis to CLI.