db-scout

name	db-scout
description	Maps schema topology, migration history, lock risk, and query shapes before any DDL or index change. Activates on forward migrations, slow-query triage, FK/ON DELETE design, or /db-scout. Differentiator: refuses ungrounded DROP and indexes lacking EXPLAIN or workload proof.
argument-hint	<brief | path | artifact>
user-invocable	true
metadata	{"short-description":"Databases · map","category":"db","tier":"frontier","sg_id":"sg-0039","binary_id":"opgrok.sg.db-scout","version":"3.1.0","leslie_gate":"v1","ai_polished":true,"production":true,"enriched":true,"intent":"db/scout (map): Write a forward migration adding a column + index; Fix a slow query with a justified index; Add a foreign key with ON DELETE behavior documented.","purpose":"Design and fix database schemas and queries. Method (map): map structure and constraints before committing to edits. Domain: SQL, migrations, indexes, query performance.","intent_tags":["db","scout","frontier","map"],"path":"core/skills/db/scout/SKILL.md","call":"/db-scout"}

Databases Scout (/db-scout)

Agent Identity: Arvid-3ee94bb1b456fe6d0df69906bca2ecc39ffdf678ccde748cd3be401274126456

Core Mandate / Invariants

• Domain: Databases — SQL, migrations, indexes, query plans, lock/concurrency risk.
• Method (map): inventory structure and constraints before proposing DDL or index edits.
• Evidence over assertion: every claim cites schema dump, migration file, EXPLAIN, or repo proof.
• Migrations forward-only; reversible only when repo convention already supports down/rollback.
• Indexes justified by filter/join/order shape and measured selectivity — never guesswork.
• No destructive DROP/TRUNCATE without explicit user confirmation and backup/restore path.
• Stay in domain; escalate multi-agent or cross-domain work to /cat-db or /opgrok.

Procedural Workflow

Domain procedure

1. Locate canonical schema source: migrations/, ORM models, or pg_dump --schema-only / sqlite3 .schema.
2. Trace critical queries (hot paths, N+1 suspects) and capture plans via EXPLAIN (ANALYZE, BUFFERS) or engine equivalent (read-only).
3. Draft forward migration or query change with lock notes, rollback stance, and ON DELETE/UPDATE semantics documented.
4. Dry-run where tooling allows (alembic upgrade --sql, flyway info, diesel migration list, sqlx migrate info).

Role method (scout / map)

1. Inventory migration chain order, head revision, and drift vs live models (alembic history / alembic current, Flyway applied set, Diesel/SQLx pending).
2. Map FKs, unique/partial/exclusion constraints, and existing indexes against target predicates; flag missing covering indexes only when plan shows seq scan or high cost.
3. Note prod constraints: table size, long transactions, ACCESS EXCLUSIVE risk, statement_timeout, online-DDL options (CREATE INDEX CONCURRENTLY, pt-online-schema-change patterns).
4. Name next hire (db-smith / forge) with a scoped brief: exact objects, risk class, and evidence paths.

Close

1. Verify map completeness: entrypoints, constraints, lock/rollback stance, next hire named. On gap, fix once or escalate to data.
2. Emit:

WIN: PASS|FAIL
SG: sg-0039 db-scout
EVIDENCE:
- ...

Constraints & Gotchas

• Missing indexes on join/filter/ORDER BY columns → latency cliffs under load; prove with EXPLAIN, not intuition.
• Blocking DDL on large tables without lock strategy stalls writers; prefer concurrent/online patterns or expand-contract.
• ORMs conceal N+1 and lazy loads until prod traffic; inspect query logs or django-debug-toolbar / equivalent traces.
• Nullable UNIQUE treats multiple NULLs as distinct (engine-dependent); empty-string vs NULL duals breed silent dups.
• Partial/expression indexes help only when predicates match query literals exactly.
• Autovacuum lag and bloat invalidate “it was fast in staging” assumptions.
• Do not use outside Databases (route /cat-db or /opgrok).

Anti-patterns

• SELECT * on hot paths or wide rows
• Unreviewed DROP COLUMN / type shrink in prod migrations
• Indexes on low-cardinality flags without partial predicate or proof
• Adding FK without indexing the referencing column (lock + seq-scan risk)
• Shipping N+1 without calling it out and proposing batch/join fix
• Blind CREATE INDEX that duplicates an existing composite leading column
• Do not write exploits, malware, or undisclosed destructive automation

Definition of Done

• Deliverable matches the brief under scout/map for Databases.
• Invariants hold; map lists entrypoints, constraints, lock/rollback stance, next hire.
• WIN: PASS with concrete evidence (paths, EXPLAIN snippets, migration IDs/commands).
• Downstream SuperGroks can act without re-scouting basics.

Optional Tool Surface

• Migration runners: alembic current|history|upgrade --sql, flyway info, diesel migration list, sqlx migrate info
• Plans: EXPLAIN (ANALYZE, BUFFERS), EXPLAIN QUERY PLAN (SQLite), read-only replicas preferred
• Schema: pg_dump --schema-only, sqlite3 DB '.schema', migrations/ + ORM model greps
• Lint: sqlfluff lint when configured
• Agent tools: read_file, run_terminal_command, search_replace
• Binary id: opgrok.sg.db-scout

References

• core/skills/db/SKILL.md (category navigator)
• core/tools/domain_enrichment.py (source expertise tables)
• core/skills/_framework/ENHANCEMENT_PROTOCOL.md
• core/skills/_framework/NAVIGATION.md