feat: `insights` CLI command with --native and --hook modes

[melagiri]

Context

Part of the Native Analysis via Claude Code Hooks feature (Phase 12, v4.8.0).
Plan: docs/plans/2026-03-28-native-analysis-hooks.md

Issue 3 of 6 — Core feature. Depends on #238 and #239.

Problem

Users need a way to trigger session analysis from the CLI, supporting both native mode (Claude Code's own LLM) and provider mode (configured API key).

Solution

Command Interface

code-insights insights <session_id> [options]

Options:
  --native         Use calling AI tool (claude -p) instead of configured LLM
  --hook           Read session context from stdin JSON (for hook invocation)
  --source <tool>  Source tool identifier (default: claude-code)
  --force          Re-analyze even if insights already exist
  --quiet, -q      Suppress output

Behavior Matrix

Invocation	session_id source	Runner	Sync
--hook --native	stdin JSON	NativeRunner	syncSingleFile
--hook (no native)	stdin JSON	ProviderRunner	syncSingleFile
<id> --native	positional arg	NativeRunner	none
<id>	positional arg	ProviderRunner	none

Key Mechanisms

1. syncSingleFile() — targeted sync of one session file (no full provider scan). Guarantees fresh data before analysis when called from hook.

2. Resume detection — stores session_message_count in analysis_usage (V8 migration). If message count hasn't changed since last analysis, skip. If changed (session was resumed), re-analyze.

3. Two analysis calls — session analysis + prompt quality, sequential. Same prompts as existing provider path.

4. Upsert — DELETE existing insights + INSERT new in a transaction. Latest analysis always wins.

Schema V8 Migration

ALTER TABLE analysis_usage ADD COLUMN session_message_count INTEGER;

New/Modified Files

File	Change
cli/src/commands/insights.ts	NEW — full command implementation
cli/src/commands/sync.ts	MODIFY — export syncSingleFile()
cli/src/db/migrate.ts	MODIFY — add V8 migration
cli/src/db/schema.ts	MODIFY — bump CURRENT_SCHEMA_VERSION to 8
cli/src/index.ts	MODIFY — register insights command

Acceptance Criteria

• code-insights insights <id> analyzes using configured LLM provider
• code-insights insights <id> --native analyzes using claude -p
• --hook reads session_id from stdin JSON, runs syncSingleFile() first
• --force re-analyzes regardless of existing insights
• --quiet suppresses all output except errors
• Resume detection: skips if message_count unchanged, re-analyzes if changed
• V8 migration adds session_message_count column
• Both analysis + PQ run sequentially, both save to SQLite
• Summary line printed: [Code Insights] Session analyzed: N insights, PQ X/100
• Integration tests with mocked runners

[melagiri]

PM Handoff — Implementation Context

Issue 3 of 6 in the Native Analysis via Claude Code Hooks feature (Phase 12, v4.8.0).

Scope Tags (Dev Verification Protocol)

• NOT VISUAL (CLI command, no UI)
• NOT OUTPUT_ARTIFACT
• NOT NEW_DEPS (all dependencies already in place)
• NOT API_CHANGE (CLI-only, no server API changes)
• Tier: 1 — Build & Test only (pnpm build zero errors + pnpm test zero failures)

TDD Requirement (MANDATORY)

Write tests first, then implementation. The test files must exist and fail (red) before you write the implementation. This is not negotiable.

Test file to create: cli/src/commands/__tests__/insights.test.ts

---

Key Files to Read Before Starting

File	Why
cli/src/analysis/runner-types.ts	AnalysisRunner interface — what both runners implement
cli/src/analysis/native-runner.ts	ClaudeNativeRunner — how claude -p is invoked
cli/src/analysis/provider-runner.ts	ProviderRunner — how configured LLM is used; ProviderRunner.fromConfig() creates from CLI config
cli/src/analysis/prompts.ts	SHARED_ANALYST_SYSTEM_PROMPT, buildCacheableConversationBlock(), buildSessionAnalysisInstructions(), buildPromptQualityInstructions()
cli/src/analysis/response-parsers.ts	parseAnalysisResponse(), parsePromptQualityResponse()
cli/src/analysis/prompt-types.ts	SessionMetadata type
cli/src/commands/sync.ts	runSync() — understand the pattern, then extract syncSingleFile()
cli/src/db/migrate.ts	applyV7() — copy pattern for applyV8()
cli/src/db/schema.ts	CURRENT_SCHEMA_VERSION = 7 → bump to 8
cli/src/index.ts	How existing commands are registered (copy the pattern)
server/src/llm/analysis-usage-db.ts	saveAnalysisUsage() — the pattern for writing to analysis_usage table
server/src/llm/analysis.ts (lines 210-250)	Upsert pattern: saveInsightsToDb() then deleteSessionInsights() with excludeIds
cli/src/commands/sync.test.ts	Existing test patterns — vi.mock, fixture usage
cli/src/analysis/__tests__/native-runner.test.ts	Test patterns for mocking execFileSync

---

V8 Migration

Add applyV8() to cli/src/db/migrate.ts following the exact same pattern as applyV6/applyV7:

ALTER TABLE analysis_usage ADD COLUMN session_message_count INTEGER;

Update MigrationResult interface to include v8Applied: boolean. Update runMigrations() to call applyV8() when currentVersion < 8. Bump CURRENT_SCHEMA_VERSION to 8 in cli/src/db/schema.ts.

---

syncSingleFile() — What it needs to do

Extract from sync.ts into an exported function with this signature:

export async function syncSingleFile(options: {
  filePath: string;    // absolute path to the session transcript
  quiet?: boolean;
}): Promise<void>

Responsibilities:

1. Get the right provider for the file type (use existing getProvider() from registry)
2. Parse the single file using that provider
3. Write to SQLite (insertSessionWithProjectAndReturnIsNew + insertMessages) — same write path as runSync
4. No spinner, no telemetry events — this is a targeted, silent sync

---

Resume Detection via session_message_count

The analysis_usage table (V7) tracks (session_id, analysis_type) pairs. After V8 migration it will also have session_message_count.

Resume detection logic for --hook mode:

1. After syncSingleFile(), query SELECT message_count FROM sessions WHERE id = ?
2. Query SELECT session_message_count FROM analysis_usage WHERE session_id = ? AND analysis_type = 'session'
3. If rows exist AND session_message_count equals current message_count → skip (already analyzed at same length)
4. If no row, or message count changed → proceed with analysis
5. After successful analysis, write session_message_count to the analysis_usage row

---

Upsert Pattern (from server/src/llm/analysis.ts)

The server does: save new insights first → then delete old ones (excludeIds prevents deleting what was just saved).

For the CLI command, replicate this in a SQLite transaction:

1. DELETE FROM insights WHERE session_id = ? AND type NOT IN ('prompt_quality') AND id NOT IN (new_ids)
2. INSERT new insights
3. INSERT OR REPLACE INTO analysis_usage (including session_message_count)

The CLI will need its own saveInsightsToDb / deleteSessionInsights functions — it cannot import from @code-insights/server (circular dependency risk). Mirror the pattern from server/src/db/insights.ts or write minimal inline SQL.

---

Two-Pass Analysis (Sequential)

Pass 1: Session analysis
  → buildSessionAnalysisInstructions() + SHARED_ANALYST_SYSTEM_PROMPT
  → runner.runAnalysis(...)
  → parseAnalysisResponse()
  → save insights + facets

Pass 2: Prompt quality
  → buildPromptQualityInstructions() + SHARED_ANALYST_SYSTEM_PROMPT
  → runner.runAnalysis(...)
  → parsePromptQualityResponse()
  → save PQ insight

Both passes use the same runner instance.

---

Summary Line Format

[Code Insights] Session analyzed: N insights, PQ X/100

Where N = count of session insights (not PQ), X = prompt quality score (integer 0-100).

In --quiet mode: suppress this line.

---

Acceptance Criteria (Testable)

Happy path:

• runInsightsCommand({ sessionId: 'x', native: false }) calls ProviderRunner.fromConfig() and runner.runAnalysis()
• runInsightsCommand({ sessionId: 'x', native: true }) calls ClaudeNativeRunner.validate() and uses native runner
• Hook mode reads { session_id, transcript_path, cwd } from stdin, calls syncSingleFile(), then analyzes
• Force mode: --force bypasses resume detection check

Boundary conditions:

• Session not found in DB after sync → exit with clear error
• --force with existing insights → deletes old, inserts new
• --quiet → no console output, no summary line

Error/rejection cases:

• ProviderRunner.fromConfig() throws (no LLM configured) → clean error message, exit 1
• ClaudeNativeRunner.validate() throws (claude not in PATH) → clean error message, exit 1
• stdin JSON malformed in --hook mode → clean error, exit 1
• Analysis pass fails → don't save partial results

Regression coverage:

• V8 migration adds session_message_count column without breaking V7 → run migrations twice, no error
• runSync still works (syncSingleFile extracted, not removed)

---

Not in Scope

• No server API changes
• No dashboard changes
• No changes to existing providers
• No new npm dependencies

[melagiri]

Completed in PR #247. Triple-layer review passed (1 FIX NOW addressed — migration function ordering). Ready for merge.