feat: Hook-Triggered Database Persistence (hookDBBridge v2.4)

[Number531]

Summary

Implementation specification for PostgreSQL persistence layer that intercepts SDK hook events via composable wrappers. Replaces the orchestrator-level StorageManager approach from JSON_DATABASE_MIGRATION.md with a 4-file, zero-behavior-change hook bridge.

Spec: docs/pending-updates/hookDBBridge-spec.md (v2.2, 17 sections, ~3800 lines)

Architecture

sdkHooksConfig → wrapHooksForDB() → wrapHooksForSSE() → manifest.wrapHooks() → [P0 gate] → agentQuery()
                 ↑ NEW (outermost)

Hook events already flow through sdkHooks.js. The bridge intercepts them and persists to PostgreSQL without modifying any existing files. Feature-flagged behind HOOK_DB_PERSISTENCE=false.

Database Schema (4 Core Tables)

Table	Purpose	Key Design Decisions
sessions	One row per research session	tenant_id + RLS for multi-tenant isolation
reports	All markdown content from /reports/	Application-layer type validation (no CHECK constraints)
agent_states	JSON state files for cross-session recovery	Glob pattern matching for section-writer multi-file state
hook_audit_log	All hook events (replaces file-based audit)	Denormalized session_key for direct queries

Key Technical Decisions

• State files via SubagentStop, not PostToolUse — updateStateFileWithGateChecks() uses raw fs.writeFileSync which bypasses hooks. State persistence reads from disk after SubagentStop fires.
• Circuit breaker with half-open recovery — 5 failures → open → 5min timeout → probe write → close/re-open. Module-level health signal for cross-request awareness (§16.3).
• Config-driven classification — hookDBBridgeConfig.js co-locates all type maps, validation sets, and filter configs. New agent types or report categories require changes to ONE file, zero ALTER TABLE.
• Application-layer validation — VALID_REPORT_TYPES / VALID_SESSION_STATUSES Sets replace SQL CHECK constraints. Schema evolution without migrations.
• SessionCache with Promise deduplication — pendingResolves Map prevents duplicate INSERT races from concurrent SubagentStart hooks. try/finally ensures cleanup on query error.

Enterprise Deployment (§15)

Feature	Approach
Tenant isolation	tenant_id column + PostgreSQL Row-Level Security (RLS) on all tables
Data retention	6mo live → 12mo warm (partitioned) → 24mo cold (S3/GCS) → purge
Schema versioning	schema_migrations table + node-pg-migrate (replaces ensureHookSchema())
Connection hardening	SSL, 30s query timeout, keepAlive, graceful shutdown, credential rotation
Capacity	50 tenants @ ~18GB live storage, ~$1.66/mo cold storage
Observability	10 metrics, /health/db endpoint, circuit breaker alerting

Robustness Audit (§16)

ID	Brittleness	Severity	Mitigation
B1	Config drift (state maps)	CRITICAL	CI sync test suite
B2	Sync I/O in async context	HIGH	fs/promises async I/O
B3	Request-scoped circuit breaker	HIGH	Module-level health signal
B4	RLS triple round-trip	MEDIUM	SET LOCAL in transactions
B5	No write verification	MEDIUM	Post-write hash check
B6	Partition boundary sessions	LOW	Don't partition sessions table
B7	SDK hook signature change	LOW	Pin version + CI test

After mitigations: 0 silent failure modes.

Session History API & Frontend (§17) — NEW in v2.2

Closes the read-path gap from write-only persistence to user-visible session history.

Component	Description
GET /api/sessions	Keyset-paginated session list with status/date/model filters
GET /api/sessions/:dir	Full session detail with reports + agent states (parallel fetch)
GET /api/sessions/stats	Aggregate metrics dashboard (cost, QA scores, model breakdown)
Frontend sidebar	Tabbed left panel (Sessions/Controls), session cards with status/cost/QA badges, infinite scroll, click-to-load/resume
Live auto-append	New sessions auto-prepend to list on system_init, status badge updates on final
Metrics enrichment	handleSessionEnd() enriches sessions.meta with word_count, qa_score, report/subagent counts
Historical backfill	Script to populate 59 existing disk sessions into database
Graceful degradation	503 probe auto-hides panel when DB unavailable

Implementation Phases

• Phase 1 (Week 1-2): Core bridge — hookDBBridgeConfig.js, hookDBBridge.js, postgres.js update, server wiring
• Phase 2 (Week 2-3): Session history API (R1) + frontend sidebar (R4-R6) + metrics enrichment (R2)
• Phase 3 (Week 3-4): Historical migration — backfill 59 existing sessions (R3) + post-session extraction
• Phase E1 (Week 5-6): Tenant isolation — RLS, migrations, tenant middleware
• Phase E2 (Week 7-8): Partitioning + retention — cold storage archival, cron jobs
• Phase E3 (Week 9-10): Observability + hardening — metrics, load testing, runbook

Files

Action	File	Purpose
Create	src/config/hookDBBridgeConfig.js	All type maps, validation sets, filter configs
Create	src/utils/hookDBBridge.js	wrapHooksForDB() + persistence handlers
Create	scripts/backfill-sessions.mjs	Historical session backfill
Modify	src/config/featureFlags.js	Add HOOK_DB_PERSISTENCE flag
Modify	src/db/postgres.js	Enterprise pool config + ensureHookSchema() → runMigrations()
Modify	src/server/claude-sdk-server.js	2 lines hook wiring + 3 session history API routes
Modify	test/react-frontend/index.html	Left panel tabs (Sessions/Controls)
Modify	test/react-frontend/styles.css	Session card styles, tab bar
Modify	test/react-frontend/app.js	Session list fetch, card rendering, load/resume flow

Test Coverage

• 77 unit tests — config module, extraction helpers, validation, SessionCache, CircuitBreaker, persistence handlers, dispatcher routing
• 29 integration tests — report persistence, state files, P0 artifacts, session lifecycle, concurrency, retention, tenant isolation
• 12 API tests — session list pagination, filters, detail endpoint, stats aggregation, RLS isolation, graceful degradation
• 6 frontend tests — card rendering, click-to-load, infinite scroll, live append, status update, degradation

Total: 124 tests

Related

• Replaces Sections 5-7 of JSON_DATABASE_MIGRATION.md
• Follows pattern established by hookSSEBridge.js (§1.1)
• Blocked on: nothing (implementation-ready)
• Related: Deferred tool loading for Agent SDK subagents — blocked on upstream support #14 (defer_loading), Agent SDK lacks adaptive thinking support — Opus 4.6 runs in deprecated mode #1 (Agent SDK thinking)

🤖 Generated with Claude Code

[Number531]

v2.3 — Validation Agent State Persistence

Closes the gap where 3 validation agents produced state files on disk but were not persisted to database.

Changes

STATE_FILE_MAP (8 → 11 agents):

Agent	State File	Directory
research-review-analyst	research-review-state.json	Session root ('')
fact-validator	fact-validator-state.json	review-outputs/
coverage-gap-analyzer	coverage-gap-analyzer-state.json	review-outputs/

These agents have no formal gate checks (GATE_CHECK_CONFIGS) but produce state files consumed by downstream agents (section-writer, coverage-gap-analyzer, risk-aggregator).

CI sync test updated: new VALIDATION_AGENTS set accounts for agents in STATE_FILE_MAP but not in GATE_CHECK_CONFIGS.

Impact: All state files observed in session 2026-02-24-1771919120 are now covered by the database schema. Zero state file persistence gaps remain.

[Number531]

v2.4 — G5 Citation Websearch Verifier Alignment

Pre-merge compatibility with worktree-g5-citation-websearch-verification branch (5 commits, 3486 lines).

Branch Review Findings

The G5 branch adds citation-websearch-verifier — a new Phase G5 agent that websearch-verifies footnotes after G4 citation-validation. It introduces:

• Agent: citation-websearch-verifier (feature-flagged: CITATION_WEBSEARCH_VERIFICATION)
• State file: citation-websearch-verifier-state.json (session root)
• Gate checks: 3 checks added to GATE_CHECK_CONFIGS in sdkHooks.js
• Output: qa-outputs/citation-verification-certificate.md
• hookSSEBridge: Already updated on branch (classifyAgent + classifyDocument)

Database Alignment

Config Point	Status
STATE_FILE_MAP	Fixed — added citation-websearch-verifier entry
STATE_FILE_DIR_MAP	Fixed — added session root override ('')
REPORT_TYPE_MATCHERS	Already OK — certificate lands in qa-outputs/ → maps to 'qa'
CI sync test	Will pass — agent now in both GATE_CHECK_CONFIGS and STATE_FILE_MAP

State file coverage: 11 → 12 agents (9 gate-checked + 3 validation).

The spec is now merge-ready for the G5 branch — no config drift will occur.

[Number531]

Implemented. Commits: e356445 (handleSessionEnd fallback + db-frontend spec), a004ed5 (changelog + README + feature-flags), 40d3de2 (complete implementation with merge-readiness fixes).