phy-db-index-advisor
Static analysis tool that reads your ORM query patterns and predicts which database columns are missing indexes — before a slow query alert fires in production. Works by counting how often each column appears in .filter(), .where(), .order_by(), and JOIN conditions across your entire codebase, then cross-referencing model definitions to suppress columns already indexed.
Why This Exists
- 80% of production slow queries stem from missing indexes on columns used in WHERE clauses
User.objects.filter(email=email) running 1,000× per minute causes full table scans
- Existing linters don't know your query patterns;
EXPLAIN ANALYZE only catches issues after the fact
- This skill finds them before deployment
What It Detects
Query Patterns Scanned
| Access Pattern |
Why It Matters |
| WHERE / filter() |
Full table scan without index — O(n) per query |
| ORDER BY / order_by() |
Sort without index reads all rows then sorts in memory |
| JOIN ON column |
Nested-loop join without index is O(n²) |
| UNIQUE constraint candidates |
Columns with unique=True queries need unique indexes |
Supported ORMs
| ORM |
Language |
Patterns Detected |
| Django ORM |
Python |
.filter(col=), .get(col=), .exclude(col=), .order_by('col'), Meta.ordering |
| SQLAlchemy |
Python |
.filter(Model.col ==), .filter_by(col=), .order_by(col), join(Model, on=) |
| Peewee |
Python |
.where(Model.col ==), .order_by(Model.col) |
| TypeORM |
TypeScript |
.where("t.col = :val"), findBy({col:}), .orderBy("t.col"), @JoinColumn({name: 'col'}) |
| Prisma |
TypeScript |
where: { col: }, orderBy: { col: }, include: { relation: } |
| Sequelize |
TypeScript/JS |
where: { col: }, order: [['col', 'ASC']] |
| GORM |
Go |
.Where("col = ?"), .Order("col"), .Joins("JOIN ... ON col") |
| ActiveRecord |
Ruby |
.where(col:), .find_by(col:), .order(:col), .joins() |
Existing Index Detection (Suppression)
The scanner reads existing index definitions so it doesn't recommend indexes that already exist:
| ORM |
Where Indexes Are Found |
| Django |
db_index=True on field, Meta.indexes, Meta.unique_together |
| SQLAlchemy |
Column(index=True), Column(unique=True), Index(...) objects |
| TypeORM |
@Index() decorator, @Column({index: true}), @Unique() |
| Prisma |
@@index([col]), @@unique([col]), @unique on field |
| GORM |
gorm:"index", gorm:"uniqueIndex" struct tags |
| ActiveRecord |
add_index in migrations, index: true in column definition |
| SQL migrations |
CREATE INDEX, CREATE UNIQUE INDEX statements |
Implementation
#!/usr/bin/env python3
"""
phy-db-index-advisor — ORM query pattern analyzer for missing indexes
Usage: python3 advisor.py [path] [--json] [--min-count N]
"""
import argparse
import json
import os
import re
import sys
from collections import defaultdict
from dataclasses import dataclass, field
from pathlib import Path
from typing import Optional
# ─── Data structures ─────────────────────────────────────────────────────────
@dataclass
class QueryHit:
file: str
line: int
pattern: str
orm: str
access_type: str # WHERE, ORDER_BY, JOIN
@dataclass
class ColumnReport:
table_hint: str # Guessed model/table name
column: str
where_count: int = 0
order_count: int = 0
join_count: int = 0
files: set = field(default_factory=set)
hits: list = field(default_factory=list)
already_indexed: bool = False
@property
def total_count(self) -> int:
return self.where_count + self.order_count + self.join_count
@property
def priority(self) -> str:
if self.already_indexed:
return "INDEXED"
if self.where_count >= 10 or self.total_count >= 15:
return "CRITICAL"
if self.where_count >= 5 or self.total_count >= 8:
return "HIGH"
if self.total_count >= 3:
return "MEDIUM"
return "LOW"
# ─── Query pattern registry ───────────────────────────────────────────────────
# (orm_name, access_type, regex, model_group_idx, col_group_idx)
QUERY_PATTERNS = [
# ── Django ORM ──
("Django", "WHERE",
re.compile(r'\.(?:filter|get|exclude|count|exists)\s*\([^)]*?(\w+)__?\w*\s*='),
None, 1),
("Django", "WHERE",
re.compile(r'\.(?:filter|get|exclude)\s*\(\s*(\w+)\s*='),
None, 1),
("Django", "ORDER_BY",
re.compile(r'\.order_by\s*\(\s*['"-](\w+)['"]\s*\)'),
None, 1),
("Django", "ORDER_BY",
re.compile(r'ordering\s*=\s*\[[^\]]*?['"](\w+)['"]'),
None, 1),
# ── SQLAlchemy ──
("SQLAlchemy", "WHERE",
re.compile(r'\.filter\s*\(\s*(\w+)\.(\w+)\s*=='),
1, 2),
("SQLAlchemy", "WHERE",
re.compile(r'\.filter_by\s*\([^)]*?(\w+)\s*='),
None, 1),
("SQLAlchemy", "ORDER_BY",
re.compile(r'\.order_by\s*\(\s*(\w+)\.(\w+)'),
1, 2),
("SQLAlchemy", "ORDER_BY",
re.compile(r'\.order_by\s*\(\s*(?:asc|desc)\s*\(\s*(\w+)\.(\w+)'),
1, 2),
# ── TypeORM ──
("TypeORM", "WHERE",
re.compile(r'where\s*:\s*\{[^}]*?(\w+)\s*:'),
None, 1),
("TypeORM", "WHERE",
re.compile(r'\.where\s*\(\s*['"`](?:\w+\.)?(\w+)\s*(?:=|LIKE|IN|>|<)'),
None, 1),
("TypeORM", "ORDER_BY",
re.compile(r'\.orderBy\s*\(\s*['"`](?:\w+\.)?(\w+)['"`]'),
None, 1),
("TypeORM", "ORDER_BY",
re.compile(r'orderBy\s*:\s*\{[^}]*?(\w+)\s*:'),
None, 1),
# ── Prisma ──
("Prisma", "WHERE",
re.compile(r'where\s*:\s*\{[^}]*?(\w+)\s*:'),
None, 1),
("Prisma", "ORDER_BY",
re.compile(r'orderBy\s*:\s*\{[^}]*?(\w+)\s*:'),
None, 1),
# ── Sequelize ──
("Sequelize", "WHERE",
re.compile(r'where\s*:\s*\{[^}]*?(\w+)\s*:'),
None, 1),
("Sequelize", "ORDER_BY",
re.compile(r'order\s*:\s*\[\s*\[\s*['"`](\w+)['"`]'),
None, 1),
# ── GORM ──
("GORM", "WHERE",
re.compile(r'\.(?:Where|Find|First|Last)\s*\([^,)]*?['"`](?:\w+\.)?(\w+)\s*(?:=|LIKE|IN|>|<|\?)'),
None, 1),
("GORM", "ORDER_BY",
re.compile(r'\.Order\s*\(\s*['"`](\w+)'),
None, 1),
("GORM", "JOIN",
re.compile(r'\.Joins\s*\([^)]*?ON\s+\w+\.(\w+)\s*=\s*\w+\.(\w+)'),
None, 1),
# ── ActiveRecord (Ruby) ──
("ActiveRecord", "WHERE",
re.compile(r'\.where\s*\(\s*(\w+):\s*'),
None, 1),
("ActiveRecord", "WHERE",
re.compile(r'\.find_by\s*\(\s*(\w+):\s*'),
None, 1),
("ActiveRecord", "ORDER_BY",
re.compile(r'\.order\s*\(\s*:(\w+)\s*\)'),
None, 1),
("ActiveRecord", "ORDER_BY",
re.compile(r'\.order\s*\(\s*['"](\w+)'),
None, 1),
]
# ─── Existing index detection ─────────────────────────────────────────────────
EXISTING_INDEX_PATTERNS = [
# Django
re.compile(r'(\w+)\s*=\s*\w+Field\s*\([^)]*\bdb_index\s*=\s*True'),
re.compile(r'(\w+)\s*=\s*\w+Field\s*\([^)]*\bunique\s*=\s*True'),
re.compile(r'models\.Index\s*\(\s*fields\s*=\s*\[([^\]]+)\]'),
# SQLAlchemy
re.compile(r'Column\s*\([^)]*\bindex\s*=\s*True[^)]*\).*?#.*?(\w+)'),
re.compile(r'(\w+)\s*=\s*Column\s*\([^)]*\bindex\s*=\s*True'),
re.compile(r'(\w+)\s*=\s*Column\s*\([^)]*\bunique\s*=\s*True'),
re.compile(r'Index\s*\(\s*['"`]\w+['"`]\s*,\s*\w+\.(\w+)'),
# TypeORM
re.compile(r'@(?:Index|Unique|Column)\s*\([^)]*\bindex\s*:\s*true'),
re.compile(r'@Column\s*\([^)]*\bunique\s*:\s*true[^)]*\)\s*\w+\s*:\s*\w+\s*(\w+)'),
# Prisma
re.compile(r'@@index\s*\(\s*\[([^\]]+)\]'),
re.compile(r'@@unique\s*\(\s*\[([^\]]+)\]'),
re.compile(r'(\w+)\s+\w+\s+@unique'),
# GORM
re.compile(r'(\w+)\s+\w+\s+`[^`]*gorm:"[^"]*(?:index|uniqueIndex)[^"]*"`'),
# SQL migrations
re.compile(r'CREATE\s+(?:UNIQUE\s+)?INDEX\s+\w+\s+ON\s+\w+\s*\(([^)]+)\)', re.IGNORECASE),
re.compile(r'add_index\s+:\w+\s*,\s*:(\w+)'), # ActiveRecord migration
]
# Columns to always skip (noise)
SKIP_COLUMNS = {
"id", "pk", "uuid", "created_at", "updated_at", "deleted_at",
"created_by", "updated_by", "None", "null", "true", "false",
"True", "False", "self", "cls", "this", "kwargs", "args",
}
SKIP_DIRS = {".git", "node_modules", "vendor", "__pycache__", ".venv", "venv",
"dist", "build", "target", "migrations", "alembic"}
FILE_EXTS = {".py", ".ts", ".js", ".rb", ".go"}
def collect_existing_indexes(root: Path) -> set[str]:
"""Return set of column names already indexed."""
indexed = set()
for dirpath, dirnames, filenames in os.walk(root):
dirnames[:] = [d for d in dirnames if d not in SKIP_DIRS]
for fname in filenames:
fpath = Path(dirpath) / fname
if fpath.suffix.lower() not in FILE_EXTS | {".sql", ".rb"}:
continue
try:
text = fpath.read_text(encoding="utf-8", errors="replace")
except OSError:
continue
for pat in EXISTING_INDEX_PATTERNS:
for m in pat.finditer(text):
for grp in m.groups():
if grp:
for col in re.split(r'[\s,'"`]+', grp):
col = col.strip().strip('"'`')
if col:
indexed.add(col.lower())
return indexed
def scan_queries(root: Path) -> dict[str, ColumnReport]:
"""Scan all source files and collect query patterns per column."""
col_reports: dict[str, ColumnReport] = {}
for dirpath, dirnames, filenames in os.walk(root):
dirnames[:] = [d for d in dirnames if d not in SKIP_DIRS]
for fname in filenames:
fpath = Path(dirpath) / fname
if fpath.suffix.lower() not in FILE_EXTS:
continue
# Skip test files
if any(x in fname.lower() for x in ("test", "spec", "mock", "fixture")):
continue
try:
lines = fpath.read_text(encoding="utf-8", errors="replace").splitlines()
except OSError:
continue
full_text = "\n".join(lines)
rel_path = os.path.relpath(str(fpath))
for (orm, access_type, pat, model_grp, col_grp) in QUERY_PATTERNS:
for m in pat.finditer(full_text):
try:
col = m.group(col_grp)
except IndexError:
continue
if not col or col.lower() in SKIP_COLUMNS:
continue
if len(col) < 2 or not col.replace("_", "").isalpha():
continue
model = None
if model_grp:
try:
model = m.group(model_grp)
except IndexError:
pass
lineno = full_text[:m.start()].count("\n") + 1
key = col.lower()
if key not in col_reports:
col_reports[key] = ColumnReport(
table_hint=model or "",
column=col,
)
report = col_reports[key]
if model and not report.table_hint:
report.table_hint = model
report.files.add(rel_path)
report.hits.append(QueryHit(rel_path, lineno, m.group(0)[:80], orm, access_type))
if access_type == "WHERE":
report.where_count += 1
elif access_type == "ORDER_BY":
report.order_count += 1
elif access_type == "JOIN":
report.join_count += 1
return col_reports
def format_report(reports: list[ColumnReport], existing_indexes: set[str]) -> str:
# Mark already-indexed
for r in reports:
if r.column.lower() in existing_indexes:
r.already_indexed = True
# Filter to only non-indexed, min 3 total hits
actionable = [r for r in reports if not r.already_indexed and r.total_count >= 3]
actionable.sort(key=lambda x: x.total_count, reverse=True)
priority_order = {"CRITICAL": 0, "HIGH": 1, "MEDIUM": 2, "LOW": 3}
actionable.sort(key=lambda x: priority_order.get(x.priority, 4))
already_indexed = [r for r in reports if r.already_indexed and r.total_count >= 3]
lines = [
"━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━",
" DB INDEX ADVISOR — Missing Index Analysis",
"━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━",
f" Columns queried: {len(reports)}",
f" Missing indexes: {len(actionable)} ({sum(1 for r in actionable if r.priority=='CRITICAL')} CRITICAL)",
f" Already indexed: {len(already_indexed)} (suppressed)",
"",
]
icons = {"CRITICAL": "🔴", "HIGH": "🟠", "MEDIUM": "🟡", "LOW": "⚪"}
current_priority = None
for r in actionable:
p = r.priority
if p != current_priority:
current_priority = p
lines.append(f"\n{icons.get(p, '⚪')} {p}")
lines.append("")
table = r.table_hint or "?"
breakdown = []
if r.where_count:
breakdown.append(f"WHERE×{r.where_count}")
if r.order_count:
breakdown.append(f"ORDER_BY×{r.order_count}")
if r.join_count:
breakdown.append(f"JOIN×{r.join_count}")
# Show top 3 call sites
sample_files = sorted(r.files)[:3]
samples_str = ", ".join(sample_files)
if len(r.files) > 3:
samples_str += f" (+{len(r.files)-3} more)"
lines += [
f" {table}.{r.column} [{' | '.join(breakdown)}] across {len(r.files)} file(s)",
f" Files: {samples_str}",
f" SQL: CREATE INDEX idx_{table.lower()}_{r.column.lower()} ON {table.lower()} ({r.column});",
f" Django: {r.column} = models.{r.column.title()}Field(..., db_index=True)",
f" SQLAlchemy: {r.column} = Column(String, index=True)",
f" Prisma: @@index([{r.column}])",
"",
]
if not actionable:
lines.append(" ✅ No missing indexes detected (all queried columns are already indexed)")
lines.append("")
if already_indexed:
lines.append(f" ✅ Already indexed ({len(already_indexed)} columns): "
+ ", ".join(r.column for r in already_indexed[:8])
+ ("..." if len(already_indexed) > 8 else ""))
lines.append("")
critical_count = sum(1 for r in actionable if r.priority == "CRITICAL")
lines += [
"━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━",
f" CI gate: {'exit 1 — missing critical indexes' if critical_count else 'exit 0'}",
" Runtime verification: EXPLAIN ANALYZE your most frequent queries",
"━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━",
]
return "\n".join(lines)
def main():
parser = argparse.ArgumentParser(description="DB index advisor — finds missing indexes from ORM patterns")
parser.add_argument("path", nargs="?", default=".", help="Root directory to scan")
parser.add_argument("--json", action="store_true", help="JSON output")
parser.add_argument("--min-count", type=int, default=3,
help="Minimum query count to report (default: 3)")
parser.add_argument("--ci", action="store_true", help="Exit 1 if CRITICAL indexes missing")
args = parser.parse_args()
root = Path(args.path).resolve()
existing_indexes = collect_existing_indexes(root)
col_reports_dict = scan_queries(root)
reports = list(col_reports_dict.values())
for r in reports:
if r.column.lower() in existing_indexes:
r.already_indexed = True
actionable = sorted(
[r for r in reports if not r.already_indexed and r.total_count >= args.min_count],
key=lambda x: x.total_count,
reverse=True,
)
if args.json:
import dataclasses
output = []
for r in actionable:
d = dataclasses.asdict(r)
d["priority"] = r.priority
d["total_count"] = r.total_count
d["files"] = list(r.files)
output.append(d)
print(json.dumps(output, indent=2))
else:
print(format_report(reports, existing_indexes))
if args.ci:
has_critical = any(r.priority == "CRITICAL" for r in actionable)
sys.exit(1 if has_critical else 0)
if __name__ == "__main__":
main()
Usage
# Scan current project
python3 advisor.py
# Scan a specific path
python3 advisor.py ~/projects/myapp
# Only show columns queried 5+ times
python3 advisor.py --min-count 5
# CI fail-gate (exits 1 if CRITICAL missing indexes found)
python3 advisor.py --ci
# JSON output for dashboard/ticketing
python3 advisor.py --json | jq '[.[] | select(.priority == "CRITICAL")]'
# GitHub Actions
- name: DB Index Advisor
run: python3 .claude/skills/phy-db-index-advisor/advisor.py --ci
Sample Output
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
DB INDEX ADVISOR — Missing Index Analysis
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
Columns queried: 24
Missing indexes: 6 (2 CRITICAL)
Already indexed: 8 (suppressed)
🔴 CRITICAL
User.email [WHERE×28] across 7 file(s)
Files: api/auth.py, api/users.py, services/notifications.py (+4 more)
SQL: CREATE INDEX idx_user_email ON user (email);
Django: email = models.EmailField(..., db_index=True)
SQLAlchemy: email = Column(String, index=True)
Prisma: @@index([email])
Order.user_id [WHERE×19 | JOIN×6] across 5 file(s)
Files: api/orders.py, services/billing.py, reports/revenue.py (+2 more)
SQL: CREATE INDEX idx_order_user_id ON order (user_id);
Django: user_id = models.ForeignKey(..., db_index=True)
SQLAlchemy: user_id = Column(Integer, ForeignKey('user.id'), index=True)
Prisma: @@index([userId])
🟠 HIGH
Product.category_id [WHERE×12 | ORDER_BY×4] across 4 file(s)
SQL: CREATE INDEX idx_product_category_id ON product (category_id);
Session.token [WHERE×9] across 3 file(s)
SQL: CREATE INDEX idx_session_token ON session (token);
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
CI gate: exit 1 — missing critical indexes
Runtime verification: EXPLAIN ANALYZE your most frequent queries
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
Relationship to phy-sql-explainer
| Skill |
Input |
Output |
When to Use |
| phy-db-index-advisor |
Source code (ORM patterns) |
Missing index recommendations |
Pre-deployment: catch before slow queries appear |
| phy-sql-explainer |
EXPLAIN ANALYZE output |
Query plan diagnosis |
Post-deployment: diagnose an existing slow query |
Use both: this skill prevents index gaps from being deployed; phy-sql-explainer diagnoses what got through anyway.
Limitations & False Positives
- Dynamic columns:
.filter(**kwargs) cannot be statically analyzed — run with --min-count 5 to focus on confirmed hot paths
- FK indexes: Most databases (PostgreSQL, MySQL) do NOT automatically index foreign keys — this skill will flag them correctly
- Primary keys:
id is always excluded (databases auto-index primary keys)
- Composite indexes: When two columns always appear together in WHERE, a composite index may outperform two single-column indexes — manual review recommended
Companion Skills
| Skill |
Use Together For |
phy-sql-explainer |
Pre + post deployment DB performance sweep |
phy-db-migration-auditor |
Safe migration before applying index additions |
phy-concurrency-audit |
Race conditions + missing indexes both cause data integrity failures |
1---2name: phy-db-index-advisor3description: Database index advisor that statically analyzes ORM query patterns to predict missing indexes before they become production bottlenecks. Scans SQLAlchemy, Django ORM, TypeORM, Prisma, GORM, ActiveRecord, and Sequelize code for columns used in WHERE/filter, ORDER BY, and JOIN conditions. Cross-references existing model index definitions and migration files to suppress already-indexed columns. Ranks recommendations by query frequency and outputs ready-to-run CREATE INDEX SQL + per-ORM migration snippets. Zero competitors on ClawHub — not a single db-index-advisor SKILL.md in 13,700+ files.4license: Apache-2.05tags:6 - database7 - performance8 - orm9 - indexes10 - django11 - sqlalchemy12 - prisma13 - typeorm14 - gorm15 - activerecord16metadata:17 source: https://skillmd.com/skills/leoyeai-openclaw-master-skills/phy-db-index-advisor18 author: PHY04119 version: "1.0.0"20---2122# phy-db-index-advisor2324Static analysis tool that reads your **ORM query patterns** and predicts which database columns are missing indexes — before a slow query alert fires in production. Works by counting how often each column appears in `.filter()`, `.where()`, `.order_by()`, and JOIN conditions across your entire codebase, then cross-referencing model definitions to suppress columns already indexed.2526## Why This Exists2728- 80% of production slow queries stem from missing indexes on columns used in WHERE clauses29- `User.objects.filter(email=email)` running 1,000× per minute causes full table scans30- Existing linters don't know your query patterns; `EXPLAIN ANALYZE` only catches issues after the fact31- This skill finds them **before deployment**3233## What It Detects3435### Query Patterns Scanned36| Access Pattern | Why It Matters |37|---------------|----------------|38| **WHERE / filter()** | Full table scan without index — O(n) per query |39| **ORDER BY / order_by()** | Sort without index reads all rows then sorts in memory |40| **JOIN ON column** | Nested-loop join without index is O(n²) |41| **UNIQUE constraint candidates** | Columns with `unique=True` queries need unique indexes |4243### Supported ORMs44| ORM | Language | Patterns Detected |45|-----|----------|-------------------|46| **Django ORM** | Python | `.filter(col=)`, `.get(col=)`, `.exclude(col=)`, `.order_by('col')`, `Meta.ordering` |47| **SQLAlchemy** | Python | `.filter(Model.col ==)`, `.filter_by(col=)`, `.order_by(col)`, `join(Model, on=)` |48| **Peewee** | Python | `.where(Model.col ==)`, `.order_by(Model.col)` |49| **TypeORM** | TypeScript | `.where("t.col = :val")`, `findBy({col:})`, `.orderBy("t.col")`, `@JoinColumn({name: 'col'})` |50| **Prisma** | TypeScript | `where: { col: }`, `orderBy: { col: }`, `include: { relation: }` |51| **Sequelize** | TypeScript/JS | `where: { col: }`, `order: [['col', 'ASC']]` |52| **GORM** | Go | `.Where("col = ?")`, `.Order("col")`, `.Joins("JOIN ... ON col")` |53| **ActiveRecord** | Ruby | `.where(col:)`, `.find_by(col:)`, `.order(:col)`, `.joins()` |5455### Existing Index Detection (Suppression)56The scanner reads existing index definitions so it doesn't recommend indexes that already exist:5758| ORM | Where Indexes Are Found |59|-----|------------------------|60| Django | `db_index=True` on field, `Meta.indexes`, `Meta.unique_together` |61| SQLAlchemy | `Column(index=True)`, `Column(unique=True)`, `Index(...)` objects |62| TypeORM | `@Index()` decorator, `@Column({index: true})`, `@Unique()` |63| Prisma | `@@index([col])`, `@@unique([col])`, `@unique` on field |64| GORM | `gorm:"index"`, `gorm:"uniqueIndex"` struct tags |65| ActiveRecord | `add_index` in migrations, `index: true` in column definition |66| SQL migrations | `CREATE INDEX`, `CREATE UNIQUE INDEX` statements |6768## Implementation6970```python71#!/usr/bin/env python372"""73phy-db-index-advisor — ORM query pattern analyzer for missing indexes74Usage: python3 advisor.py [path] [--json] [--min-count N]75"""76import argparse77import json78import os79import re80import sys81from collections import defaultdict82from dataclasses import dataclass, field83from pathlib import Path84from typing import Optional8586# ─── Data structures ─────────────────────────────────────────────────────────8788@dataclass89class QueryHit:90 file: str91 line: int92 pattern: str93 orm: str94 access_type: str # WHERE, ORDER_BY, JOIN9596@dataclass97class ColumnReport:98 table_hint: str # Guessed model/table name99 column: str100 where_count: int = 0101 order_count: int = 0102 join_count: int = 0103 files: set = field(default_factory=set)104 hits: list = field(default_factory=list)105 already_indexed: bool = False106107 @property108 def total_count(self) -> int:109 return self.where_count + self.order_count + self.join_count110111 @property112 def priority(self) -> str:113 if self.already_indexed:114 return "INDEXED"115 if self.where_count >= 10 or self.total_count >= 15:116 return "CRITICAL"117 if self.where_count >= 5 or self.total_count >= 8:118 return "HIGH"119 if self.total_count >= 3:120 return "MEDIUM"121 return "LOW"122123# ─── Query pattern registry ───────────────────────────────────────────────────124125# (orm_name, access_type, regex, model_group_idx, col_group_idx)126QUERY_PATTERNS = [127 # ── Django ORM ──128 ("Django", "WHERE",129 re.compile(r'\.(?:filter|get|exclude|count|exists)\s*\([^)]*?(\w+)__?\w*\s*='),130 None, 1),131 ("Django", "WHERE",132 re.compile(r'\.(?:filter|get|exclude)\s*\(\s*(\w+)\s*='),133 None, 1),134 ("Django", "ORDER_BY",135 re.compile(r'\.order_by\s*\(\s*['"-](\w+)['"]\s*\)'),136 None, 1),137 ("Django", "ORDER_BY",138 re.compile(r'ordering\s*=\s*\[[^\]]*?['"](\w+)['"]'),139 None, 1),140141 # ── SQLAlchemy ──142 ("SQLAlchemy", "WHERE",143 re.compile(r'\.filter\s*\(\s*(\w+)\.(\w+)\s*=='),144 1, 2),145 ("SQLAlchemy", "WHERE",146 re.compile(r'\.filter_by\s*\([^)]*?(\w+)\s*='),147 None, 1),148 ("SQLAlchemy", "ORDER_BY",149 re.compile(r'\.order_by\s*\(\s*(\w+)\.(\w+)'),150 1, 2),151 ("SQLAlchemy", "ORDER_BY",152 re.compile(r'\.order_by\s*\(\s*(?:asc|desc)\s*\(\s*(\w+)\.(\w+)'),153 1, 2),154155 # ── TypeORM ──156 ("TypeORM", "WHERE",157 re.compile(r'where\s*:\s*\{[^}]*?(\w+)\s*:'),158 None, 1),159 ("TypeORM", "WHERE",160 re.compile(r'\.where\s*\(\s*['"`](?:\w+\.)?(\w+)\s*(?:=|LIKE|IN|>|<)'),161 None, 1),162 ("TypeORM", "ORDER_BY",163 re.compile(r'\.orderBy\s*\(\s*['"`](?:\w+\.)?(\w+)['"`]'),164 None, 1),165 ("TypeORM", "ORDER_BY",166 re.compile(r'orderBy\s*:\s*\{[^}]*?(\w+)\s*:'),167 None, 1),168169 # ── Prisma ──170 ("Prisma", "WHERE",171 re.compile(r'where\s*:\s*\{[^}]*?(\w+)\s*:'),172 None, 1),173 ("Prisma", "ORDER_BY",174 re.compile(r'orderBy\s*:\s*\{[^}]*?(\w+)\s*:'),175 None, 1),176177 # ── Sequelize ──178 ("Sequelize", "WHERE",179 re.compile(r'where\s*:\s*\{[^}]*?(\w+)\s*:'),180 None, 1),181 ("Sequelize", "ORDER_BY",182 re.compile(r'order\s*:\s*\[\s*\[\s*['"`](\w+)['"`]'),183 None, 1),184185 # ── GORM ──186 ("GORM", "WHERE",187 re.compile(r'\.(?:Where|Find|First|Last)\s*\([^,)]*?['"`](?:\w+\.)?(\w+)\s*(?:=|LIKE|IN|>|<|\?)'),188 None, 1),189 ("GORM", "ORDER_BY",190 re.compile(r'\.Order\s*\(\s*['"`](\w+)'),191 None, 1),192 ("GORM", "JOIN",193 re.compile(r'\.Joins\s*\([^)]*?ON\s+\w+\.(\w+)\s*=\s*\w+\.(\w+)'),194 None, 1),195196 # ── ActiveRecord (Ruby) ──197 ("ActiveRecord", "WHERE",198 re.compile(r'\.where\s*\(\s*(\w+):\s*'),199 None, 1),200 ("ActiveRecord", "WHERE",201 re.compile(r'\.find_by\s*\(\s*(\w+):\s*'),202 None, 1),203 ("ActiveRecord", "ORDER_BY",204 re.compile(r'\.order\s*\(\s*:(\w+)\s*\)'),205 None, 1),206 ("ActiveRecord", "ORDER_BY",207 re.compile(r'\.order\s*\(\s*['"](\w+)'),208 None, 1),209]210211# ─── Existing index detection ─────────────────────────────────────────────────212213EXISTING_INDEX_PATTERNS = [214 # Django215 re.compile(r'(\w+)\s*=\s*\w+Field\s*\([^)]*\bdb_index\s*=\s*True'),216 re.compile(r'(\w+)\s*=\s*\w+Field\s*\([^)]*\bunique\s*=\s*True'),217 re.compile(r'models\.Index\s*\(\s*fields\s*=\s*\[([^\]]+)\]'),218 # SQLAlchemy219 re.compile(r'Column\s*\([^)]*\bindex\s*=\s*True[^)]*\).*?#.*?(\w+)'),220 re.compile(r'(\w+)\s*=\s*Column\s*\([^)]*\bindex\s*=\s*True'),221 re.compile(r'(\w+)\s*=\s*Column\s*\([^)]*\bunique\s*=\s*True'),222 re.compile(r'Index\s*\(\s*['"`]\w+['"`]\s*,\s*\w+\.(\w+)'),223 # TypeORM224 re.compile(r'@(?:Index|Unique|Column)\s*\([^)]*\bindex\s*:\s*true'),225 re.compile(r'@Column\s*\([^)]*\bunique\s*:\s*true[^)]*\)\s*\w+\s*:\s*\w+\s*(\w+)'),226 # Prisma227 re.compile(r'@@index\s*\(\s*\[([^\]]+)\]'),228 re.compile(r'@@unique\s*\(\s*\[([^\]]+)\]'),229 re.compile(r'(\w+)\s+\w+\s+@unique'),230 # GORM231 re.compile(r'(\w+)\s+\w+\s+`[^`]*gorm:"[^"]*(?:index|uniqueIndex)[^"]*"`'),232 # SQL migrations233 re.compile(r'CREATE\s+(?:UNIQUE\s+)?INDEX\s+\w+\s+ON\s+\w+\s*\(([^)]+)\)', re.IGNORECASE),234 re.compile(r'add_index\s+:\w+\s*,\s*:(\w+)'), # ActiveRecord migration235]236237# Columns to always skip (noise)238SKIP_COLUMNS = {239 "id", "pk", "uuid", "created_at", "updated_at", "deleted_at",240 "created_by", "updated_by", "None", "null", "true", "false",241 "True", "False", "self", "cls", "this", "kwargs", "args",242}243244SKIP_DIRS = {".git", "node_modules", "vendor", "__pycache__", ".venv", "venv",245 "dist", "build", "target", "migrations", "alembic"}246247FILE_EXTS = {".py", ".ts", ".js", ".rb", ".go"}248249def collect_existing_indexes(root: Path) -> set[str]:250 """Return set of column names already indexed."""251 indexed = set()252 for dirpath, dirnames, filenames in os.walk(root):253 dirnames[:] = [d for d in dirnames if d not in SKIP_DIRS]254 for fname in filenames:255 fpath = Path(dirpath) / fname256 if fpath.suffix.lower() not in FILE_EXTS | {".sql", ".rb"}:257 continue258 try:259 text = fpath.read_text(encoding="utf-8", errors="replace")260 except OSError:261 continue262 for pat in EXISTING_INDEX_PATTERNS:263 for m in pat.finditer(text):264 for grp in m.groups():265 if grp:266 for col in re.split(r'[\s,'"`]+', grp):267 col = col.strip().strip('"'`')268 if col:269 indexed.add(col.lower())270 return indexed271272def scan_queries(root: Path) -> dict[str, ColumnReport]:273 """Scan all source files and collect query patterns per column."""274 col_reports: dict[str, ColumnReport] = {}275276 for dirpath, dirnames, filenames in os.walk(root):277 dirnames[:] = [d for d in dirnames if d not in SKIP_DIRS]278 for fname in filenames:279 fpath = Path(dirpath) / fname280 if fpath.suffix.lower() not in FILE_EXTS:281 continue282 # Skip test files283 if any(x in fname.lower() for x in ("test", "spec", "mock", "fixture")):284 continue285 try:286 lines = fpath.read_text(encoding="utf-8", errors="replace").splitlines()287 except OSError:288 continue289290 full_text = "\n".join(lines)291 rel_path = os.path.relpath(str(fpath))292293 for (orm, access_type, pat, model_grp, col_grp) in QUERY_PATTERNS:294 for m in pat.finditer(full_text):295 try:296 col = m.group(col_grp)297 except IndexError:298 continue299 if not col or col.lower() in SKIP_COLUMNS:300 continue301 if len(col) < 2 or not col.replace("_", "").isalpha():302 continue303304 model = None305 if model_grp:306 try:307 model = m.group(model_grp)308 except IndexError:309 pass310311 lineno = full_text[:m.start()].count("\n") + 1312 key = col.lower()313314 if key not in col_reports:315 col_reports[key] = ColumnReport(316 table_hint=model or "",317 column=col,318 )319 report = col_reports[key]320 if model and not report.table_hint:321 report.table_hint = model322 report.files.add(rel_path)323 report.hits.append(QueryHit(rel_path, lineno, m.group(0)[:80], orm, access_type))324325 if access_type == "WHERE":326 report.where_count += 1327 elif access_type == "ORDER_BY":328 report.order_count += 1329 elif access_type == "JOIN":330 report.join_count += 1331332 return col_reports333334def format_report(reports: list[ColumnReport], existing_indexes: set[str]) -> str:335 # Mark already-indexed336 for r in reports:337 if r.column.lower() in existing_indexes:338 r.already_indexed = True339340 # Filter to only non-indexed, min 3 total hits341 actionable = [r for r in reports if not r.already_indexed and r.total_count >= 3]342 actionable.sort(key=lambda x: x.total_count, reverse=True)343344 priority_order = {"CRITICAL": 0, "HIGH": 1, "MEDIUM": 2, "LOW": 3}345 actionable.sort(key=lambda x: priority_order.get(x.priority, 4))346347 already_indexed = [r for r in reports if r.already_indexed and r.total_count >= 3]348349 lines = [350 "━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━",351 " DB INDEX ADVISOR — Missing Index Analysis",352 "━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━",353 f" Columns queried: {len(reports)}",354 f" Missing indexes: {len(actionable)} ({sum(1 for r in actionable if r.priority=='CRITICAL')} CRITICAL)",355 f" Already indexed: {len(already_indexed)} (suppressed)",356 "",357 ]358359 icons = {"CRITICAL": "🔴", "HIGH": "🟠", "MEDIUM": "🟡", "LOW": "⚪"}360 current_priority = None361 for r in actionable:362 p = r.priority363 if p != current_priority:364 current_priority = p365 lines.append(f"\n{icons.get(p, '⚪')} {p}")366 lines.append("")367368 table = r.table_hint or "?"369 breakdown = []370 if r.where_count:371 breakdown.append(f"WHERE×{r.where_count}")372 if r.order_count:373 breakdown.append(f"ORDER_BY×{r.order_count}")374 if r.join_count:375 breakdown.append(f"JOIN×{r.join_count}")376377 # Show top 3 call sites378 sample_files = sorted(r.files)[:3]379 samples_str = ", ".join(sample_files)380 if len(r.files) > 3:381 samples_str += f" (+{len(r.files)-3} more)"382383 lines += [384 f" {table}.{r.column} [{' | '.join(breakdown)}] across {len(r.files)} file(s)",385 f" Files: {samples_str}",386 f" SQL: CREATE INDEX idx_{table.lower()}_{r.column.lower()} ON {table.lower()} ({r.column});",387 f" Django: {r.column} = models.{r.column.title()}Field(..., db_index=True)",388 f" SQLAlchemy: {r.column} = Column(String, index=True)",389 f" Prisma: @@index([{r.column}])",390 "",391 ]392393 if not actionable:394 lines.append(" ✅ No missing indexes detected (all queried columns are already indexed)")395 lines.append("")396397 if already_indexed:398 lines.append(f" ✅ Already indexed ({len(already_indexed)} columns): "399 + ", ".join(r.column for r in already_indexed[:8])400 + ("..." if len(already_indexed) > 8 else ""))401 lines.append("")402403 critical_count = sum(1 for r in actionable if r.priority == "CRITICAL")404 lines += [405 "━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━",406 f" CI gate: {'exit 1 — missing critical indexes' if critical_count else 'exit 0'}",407 " Runtime verification: EXPLAIN ANALYZE your most frequent queries",408 "━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━",409 ]410 return "\n".join(lines)411412def main():413 parser = argparse.ArgumentParser(description="DB index advisor — finds missing indexes from ORM patterns")414 parser.add_argument("path", nargs="?", default=".", help="Root directory to scan")415 parser.add_argument("--json", action="store_true", help="JSON output")416 parser.add_argument("--min-count", type=int, default=3,417 help="Minimum query count to report (default: 3)")418 parser.add_argument("--ci", action="store_true", help="Exit 1 if CRITICAL indexes missing")419 args = parser.parse_args()420421 root = Path(args.path).resolve()422 existing_indexes = collect_existing_indexes(root)423 col_reports_dict = scan_queries(root)424 reports = list(col_reports_dict.values())425426 for r in reports:427 if r.column.lower() in existing_indexes:428 r.already_indexed = True429430 actionable = sorted(431 [r for r in reports if not r.already_indexed and r.total_count >= args.min_count],432 key=lambda x: x.total_count,433 reverse=True,434 )435436 if args.json:437 import dataclasses438 output = []439 for r in actionable:440 d = dataclasses.asdict(r)441 d["priority"] = r.priority442 d["total_count"] = r.total_count443 d["files"] = list(r.files)444 output.append(d)445 print(json.dumps(output, indent=2))446 else:447 print(format_report(reports, existing_indexes))448449 if args.ci:450 has_critical = any(r.priority == "CRITICAL" for r in actionable)451 sys.exit(1 if has_critical else 0)452453if __name__ == "__main__":454 main()455```456457## Usage458459```bash460# Scan current project461python3 advisor.py462463# Scan a specific path464python3 advisor.py ~/projects/myapp465466# Only show columns queried 5+ times467python3 advisor.py --min-count 5468469# CI fail-gate (exits 1 if CRITICAL missing indexes found)470python3 advisor.py --ci471472# JSON output for dashboard/ticketing473python3 advisor.py --json | jq '[.[] | select(.priority == "CRITICAL")]'474475# GitHub Actions476- name: DB Index Advisor477 run: python3 .claude/skills/phy-db-index-advisor/advisor.py --ci478```479480## Sample Output481482```483━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━484 DB INDEX ADVISOR — Missing Index Analysis485━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━486 Columns queried: 24487 Missing indexes: 6 (2 CRITICAL)488 Already indexed: 8 (suppressed)489490🔴 CRITICAL491492 User.email [WHERE×28] across 7 file(s)493 Files: api/auth.py, api/users.py, services/notifications.py (+4 more)494 SQL: CREATE INDEX idx_user_email ON user (email);495 Django: email = models.EmailField(..., db_index=True)496 SQLAlchemy: email = Column(String, index=True)497 Prisma: @@index([email])498499 Order.user_id [WHERE×19 | JOIN×6] across 5 file(s)500 Files: api/orders.py, services/billing.py, reports/revenue.py (+2 more)501 SQL: CREATE INDEX idx_order_user_id ON order (user_id);502 Django: user_id = models.ForeignKey(..., db_index=True)503 SQLAlchemy: user_id = Column(Integer, ForeignKey('user.id'), index=True)504 Prisma: @@index([userId])505506🟠 HIGH507508 Product.category_id [WHERE×12 | ORDER_BY×4] across 4 file(s)509 SQL: CREATE INDEX idx_product_category_id ON product (category_id);510511 Session.token [WHERE×9] across 3 file(s)512 SQL: CREATE INDEX idx_session_token ON session (token);513514━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━515 CI gate: exit 1 — missing critical indexes516 Runtime verification: EXPLAIN ANALYZE your most frequent queries517━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━518```519520## Relationship to `phy-sql-explainer`521522| Skill | Input | Output | When to Use |523|-------|-------|--------|-------------|524| **phy-db-index-advisor** | Source code (ORM patterns) | Missing index recommendations | Pre-deployment: catch before slow queries appear |525| **phy-sql-explainer** | EXPLAIN ANALYZE output | Query plan diagnosis | Post-deployment: diagnose an existing slow query |526527Use **both**: this skill prevents index gaps from being deployed; `phy-sql-explainer` diagnoses what got through anyway.528529## Limitations & False Positives530531- **Dynamic columns**: `.filter(**kwargs)` cannot be statically analyzed — run with `--min-count 5` to focus on confirmed hot paths532- **FK indexes**: Most databases (PostgreSQL, MySQL) do NOT automatically index foreign keys — this skill will flag them correctly533- **Primary keys**: `id` is always excluded (databases auto-index primary keys)534- **Composite indexes**: When two columns always appear together in WHERE, a composite index may outperform two single-column indexes — manual review recommended535536## Companion Skills537538| Skill | Use Together For |539|-------|-----------------|540| `phy-sql-explainer` | Pre + post deployment DB performance sweep |541| `phy-db-migration-auditor` | Safe migration before applying index additions |542| `phy-concurrency-audit` | Race conditions + missing indexes both cause data integrity failures |