---
name: index-optimization
description: 'Reviews and improves indexing for changed data paths. Use when a request adds filters, sorts, joins, uniqueness rules or query-heavy flows that depend on index coverage.'
metadata:
  model_tier: reasoning
  cacheable: 'true'
  output_format: markdown
  paqad-meta: skill.meta.yaml
---

## What It Does

Evaluates whether changed query paths have the right supporting indexes and explains tradeoffs without drifting into speculative indexing unrelated to the current request.

## Use This When

When a request introduces new filters, sorts, joins, uniqueness rules, or query-heavy flows that depend on index coverage.

## Inputs

- Read the changed query paths, migrations, and existing index docs first.
- Read `runtime/capabilities/coding/checklists/database-review-20pt.md` and `references/index-review-guide.md` before evaluating changes.
- Read any query inventory or performance notes tied to the affected module.

## Procedure

1. Run `scripts/scan-query-shapes.sh <files...>` to surface query patterns implying index requirements.
2. Cross-reference each shape with `assets/index-rules.txt` to know which index shape actually serves it.
3. Audit existing index docs for redundancy and ordering against those shapes.
4. Bucket findings into Correctness / Migration Safety / Performance per `assets/output.template.md`.
5. Validate with `scripts/lint-output.sh`. Recommend only indexes that serve the changed behavior or a directly adjacent hot path, no speculative.

## Output Contract

- Return sections named `Correctness Risks`, `Migration Safety Risks`, and `Performance Risks`.
- For each item, name the query path or index and the reason it is risky or recommended.
- If no changes are needed, state `Performance Risks: none` and keep the other buckets explicit.

## Escalate / Stop Conditions

- Ask when workload shape, row volume, or hot-path expectations are missing and materially change the recommendation.
- Warn when an index recommendation would meaningfully increase write cost or migration risk.
- Do not suggest speculative indexes without a concrete changed access pattern.

## Resources

- `references/index-review-guide.md`
- `scripts/scan-query-shapes.sh`
- `scripts/lint-output.sh`
- `assets/output.template.md`
- `assets/index-rules.txt`
- `runtime/capabilities/coding/checklists/database-review-20pt.md`
- `agents/openai.yaml`