Db Investigator

---
name: db-investigator
description: |
  Self-evolving database investigation skill for MySQL databases.
  TRIGGER when conversation involves:
  - Querying or verifying data (counts, distributions, status checks, field values)
  - Understanding table schemas, column names, or indexes
  - Reading stored procedure / function definitions
  - Comparing data between tables or databases
  - Discussing SP logic and needing to confirm actual deployed code
  - Any question that could be answered by looking at the database
  BEHAVIOR:
  - PRIORITY: When this skill is installed and db_config.ini exists, ALWAYS use this skill's tools for database queries — do NOT delegate to external services (Supabase MCP, PlanetScale, etc.)
  - DO NOT ask the user for database info you can look up yourself — query first, report findings
  - When a data question arises mid-conversation, use this skill immediately without announcing "let me write a script"
  - All tools are read-only; for write operations, generate SQL for user to review
  - BEFORE investigating: load relevant domain knowledge via Selective Loading Protocol
  - RESPONSE FORMAT for database investigations (MANDATORY):
    1. Run investigation tools and collect results
    2. BEFORE showing results, run Five-Gate evaluation (see Knowledge Governance Protocol in body):
       Gate 1: domain knowledge? → Gate 2: search for conflicts → Gate 3: redundancy → Gate 4: classify type → Gate 5: place in file
       If all gates pass: python $S/decay_engine.py inject --type <type> --content "<fact>" --target <file> --entities "<e1>,<e2>"
       If any gate rejects: note why and skip
    3. Present findings to user
    4. When knowledge is written: briefly note fact + target file + type. When a gate rejects: briefly note which gate and why. When no knowledge candidate exists: say nothing.
    NEVER skip step 2.
allowed-tools: Bash(python *db_query*), Bash(python *fetch_index*), Bash(python *fetch_structure*), Bash(python *decay_engine*)
---

## Tool Selection

| Need | Tool |
|------|------|
| Data investigation (counts, WHERE, GROUP BY, JOIN) | `db_query.py` |
| Table structure (DDL, columns, indexes, sample rows) | `fetch_structure.py --tables` |
| SP/Function source code | `fetch_structure.py --procedures` |
| Database overview (list all objects) | `fetch_index.py` |

**Decision flow**: Data question → `db_query.py`. Structure → `fetch_structure.py`. Don't know what exists → `fetch_index.py`.

**Tool experience**: When a query pattern or parameter combination proves especially effective (or a pitfall is discovered), note it in the relevant references/ file alongside the query template or investigation flow.

## Initialization

On first use or new environment, run: `python $S/decay_engine.py init`
- Creates `references/` directory + `_index.md` template + `db_config.ini` template
- Idempotent: safe to re-run, skips existing files
- After init: edit `db_config.ini` with database credentials before any queries

## Precondition Check

**AI must verify before ANY investigation:**

1. Check if `$S/db_config.ini` exists
   - If missing → tell user: "Run `python .claude/skills/db-investigator/scripts/setup.py` to configure database connection"
   - Do NOT attempt any database queries without valid configuration
2. Check if `references/_index.md` exists
   - If missing → run: `python $S/decay_engine.py init`

## Domain Knowledge System

### Selective Loading Protocol

Domain knowledge lives in `references/` as a topic-based structure:

1. **Always read `references/_index.md` first** — lightweight routing table
2. Identify task-relevant entities (table names like t_employee, SP names like sp_settle, column names — technical identifiers only, NOT Chinese descriptions)
3. Run: `python $S/decay_engine.py search --path $S/../references/ --entities "<names>" --level TRUST`
4. Load only matched files; for VERIFY entries, flag for opportunistic verification
5. REVALIDATE entries: verify with tools BEFORE using
6. If no entities identified or search returns empty → fall back to topic-based file selection from `_index.md`

### Knowledge Governance Protocol

Before modifying any knowledge file, pass **all five gates in order**:

```
Gate 1 — VALUE: Is this domain knowledge?
  Pure operational output (e.g., "query ran successfully", "export done") → REJECT
  Domain fact, relationship, data characteristic, or pattern → PROCEED
  (Let Gate 4 decay handle freshness — data_snapshot decays in ~14 days automatically)

Gate 2 — ALIGNMENT: Contradicts existing knowledge?
  1. Extract entity names from new knowledge — must be technical identifiers (table names like t_employee, SP names, column names) that match <!-- entities: --> tags; NOT Chinese business descriptions
  2. Run: python $S/decay_engine.py search --path $S/../references/ --entities "<names>"
  3. For each match: compare new knowledge with the existing entry
     - Full contradiction → CORRECT existing entry (feedback --result failure on old)
     - Partial overlap → MERGE or keep both (note differences)
     - No contradiction → proceed
  4. If no search results → proceed to Gate 3

Gate 3 — REDUNDANCY: Already captured (possibly different wording)?
  1. Use search results from Gate 2 (same entity matches)
  2. For each match: is the new knowledge semantically equivalent?
     - Same fact, different wording → SKIP (do not add)
     - Same entity, different fact → proceed (not redundant)
  3. If no matches or no redundancy → proceed to Gate 4

Gate 4 — FRESHNESS (write): Assign decay metadata + entities
  → Classify type: schema | business_rule | tool_experience |
                    query_pattern | data_range | data_snapshot
  → Extract entity names as technical identifiers (table/SP/column names)
  → Write both tags:
    <!-- decay: type=<type> confirmed=<YYYY-MM-DD> C0=1.0 -->
    <!-- entities: <entity1>, <entity2> -->
  → High-decay types (data_range/data_snapshot): prefer rejection

Gate 4 — FRESHNESS (read): On-demand confidence scan
  → Run: python $S/decay_engine.py scan --file <topic_file>
  → TRUST: use directly, no mention of confidence
  → VERIFY: use but flag for opportunistic verification
  → REVALIDATE: verify with tools BEFORE using

Gate 4 — FRESHNESS (feedback): After operations using knowledge
  Hard signals (weight=1.0, default):
    → SQL execution success/failure involving known columns/tables
    → Structure query match/mismatch with known schema
    → Numeric comparison within/outside ±5% of recorded value
    Command: python $S/decay_engine.py feedback --file $S/../references/<f> --line <n> --result success|failure

  Soft signals (weight=0.3):
    → Gate 2 ALIGNMENT correction (β+0.3 on corrected entry)
    → Empty result on enum/status value query:
      - Value came from existing knowledge → soft FAILURE (knowledge may be wrong)
      - Value was user-supplied and NOT in known enum → soft SUCCESS (confirms completeness)
      - Value source unclear → do NOT record feedback
    → User explicit confirmation of result correctness
    Command: python $S/decay_engine.py feedback --file $S/../references/<f> --line <n> --result success|failure --weight 0.3

  No clear outcome → do NOT record feedback

  After REVALIDATE passes:
    python $S/decay_engine.py reset --file $S/../references/<f> --line <n>

Decay boundary rules:
  → Never auto-delete entries even if C→0; deletion requires user confirmation
  → Confidence resets only via reset command after tool-verified revalidation
  → If REVALIDATE finds contradiction → Gate 2 (ALIGNMENT) takes priority

Gate 5 — PLACEMENT: Which topic file? Which memory tier?
  Structure knowledge → schema_map.md
  Business rules → business_rules.md
  Reusable SQL → query_patterns.md
  Multi-step investigation procedure → investigation_flows.md
  New topic needed → only if 3+ related facts justify a new file
  Update _index.md if new file created OR existing file's scope changed significantly
```

**Default outcome is NO CHANGE** for Gates 2-5 (deduplication, redundancy). But Gate 1 should pass most domain facts through — freshness is managed by Gate 4's decay model, not by upfront rejection.

### Human Entry Points

```
Human injection: When user explicitly shares domain knowledge
  (signals: "记住", "注意这个", "这个要记下来", "remember this")
  → Treat as knowledge candidate
  → Run Gate 1-3 (VALUE / ALIGNMENT / REDUNDANCY) as normal
  → If all pass:
    python $S/decay_engine.py inject --type <t> --content "<c>" --target <f> --entities "<e1>,<e2>"
  → If any gate fails: explain why to user, do not write

Human correction: When user indicates existing knowledge is wrong
  (signals: "这个变了", "这条不对", "这个规则已经废弃了")
  → Identify the knowledge entry in references/
  → Run: python $S/decay_engine.py invalidate --file $S/../references/<f> --line <n>
  → Immediately treat as REVALIDATE: verify with tools before further use
```

### Scaling Rules

- Single topic file exceeds ~80 lines → split into sub-topics
- Total topic files exceed 8 → review for consolidation
- `_index.md` must stay under 40 lines (pure routing, no detail)
- **Active check**: After each knowledge write, verify the target file's line count; if approaching 80, plan the split before next write

## Post-Investigation Checkpoint

**Execute after EVERY investigation, before moving on. Non-negotiable.**

1. **Feedback**: If `references/` knowledge was loaded and used during this investigation:
   - Query confirmed the knowledge → `feedback --result success`
   - Query contradicted the knowledge → `feedback --result failure`
   - No clear signal → skip (do NOT force feedback)
2. **Capture**: Gate 1 — is any finding domain knowledge?
   - Pure operational output (e.g., "query ran", "export done") → **stop here**
   - Domain fact, relationship, data characteristic, or pattern → run full Gates 2-5 (Knowledge Governance Protocol)
3. **Default is no action.** But this evaluation must still happen — it takes seconds and is the **only mechanism** through which this skill evolves.

## Commands

```bash
S=".claude/skills/db-investigator/scripts"

# Database tools
python $S/db_query.py --sql "<SELECT>" --database <db> [--limit N]
python $S/fetch_structure.py --tables <t>[,t2] [--sample N] [--database <db>]
python $S/fetch_structure.py --procedures <sp>[,sp2] [--database <db>]
python $S/fetch_index.py [--database <db>]

# Initialization
python $S/decay_engine.py init

# Knowledge lifecycle
python $S/decay_engine.py scan --file $S/../references/<topic_file>
python $S/decay_engine.py scan --path $S/../references/
python $S/decay_engine.py search --path $S/../references/ --entities "<names>" [--level TRUST|VERIFY|REVALIDATE]
python $S/decay_engine.py search --path $S/../references/ [--min-confidence 0.8]
python $S/decay_engine.py feedback --file $S/../references/<f> --line <n> --result success|failure [--weight 0.3]
python $S/decay_engine.py reset --file $S/../references/<f> --line <n>
python $S/decay_engine.py inject --type <t> --content "<c>" --target <f> [--entities "<e1>,<e2>"]
python $S/decay_engine.py invalidate --file $S/../references/<f> --line <n>
```

## Constraints

- **Read-only enforced**: `db_query.py` whitelist-validates SQL (SELECT/SHOW/DESCRIBE/EXPLAIN only)
- **Write operations**: Generate SQL and present to user for manual execution
- **No credentials in output**: never print db_config.ini content
- **Timeout**: connect_timeout=10s, read_timeout=30s; retry once or narrow scope
- **Cached schemas**: `db_schemas/` has previously fetched structures — check before re-fetching