2026-02-05-andy-insights.md

Andy Insights — Implementation Plan

Created: 2026-02-05 Status: Draft — awaiting approval Goal: Build a continuous session insights system that extracts rich facets from Claude Code sessions and generates weekly/monthly reports tied to Alex's businesses, priorities, and relationships.

---

Architecture Overview

Sessions Sync (every 15 min)
    ↓
Facet Extraction (every 30 min, new sessions only)
    ↓
session_facets table (JSONB + indexed columns)
    ↓
Report Generation (weekly/monthly via cron)
    ↓
Discord summary + HTML report on DO Spaces + Admin dashboard

Key Design Decisions:

• Facet extraction lives in Andy Core as an artisan command (sessions:extract-facets) — follows memories:catch pattern exactly
• Haiku via host-bridge (same as classification/memory extraction) — no new LLM plumbing
• JSONB column for flexible facet storage + dedicated indexed columns for common queries
• Separate session_facets table (not a column on claude_sessions) to keep concerns clean and allow schema evolution

---

Phase 1: Facet Extraction Engine

Depends on: Nothing (standalone) Deliverables: Migration, model, artisan command, host-bridge endpoint, scheduled job

1a. Database Migration

Create session_facets table:

// database/migrations/2026_02_XX_create_session_facets_table.php
Schema::create('session_facets', function (Blueprint $table) {
    $table->id();
    $table->string('session_id')->index();
    $table->foreign('session_id')
          ->references('id')
          ->on('claude_sessions')
          ->onDelete('cascade');

    // Layer 1: Official /insights parity
    $table->text('underlying_goal')->nullable();
    $table->jsonb('goal_categories')->nullable();
    $table->string('outcome', 50)->nullable();        // fully_achieved, mostly_achieved, etc.
    $table->jsonb('user_satisfaction_counts')->nullable();
    $table->string('claude_helpfulness', 50)->nullable();
    $table->string('session_type', 50)->nullable();    // single_task, multi_task, etc.
    $table->jsonb('friction_counts')->nullable();
    $table->text('friction_detail')->nullable();
    $table->string('primary_success', 100)->nullable();
    $table->text('brief_summary')->nullable();

    // Layer 2a: Business & project attribution
    $table->jsonb('projects')->nullable();             // ["indy-hall-newsletter", "api-capture"]
    $table->jsonb('businesses')->nullable();            // ["indy-hall", "stb"]
    $table->string('priority_alignment', 20)->nullable(); // aligned, supportive, neutral, drift
    $table->string('priority_matched', 100)->nullable();  // growing-indy-hall, etc.

    // Layer 2b: Workflow pattern detection
    $table->string('workflow_type', 50)->nullable();   // email, research, filing, etc.
    $table->string('initiated_by', 50)->nullable();    // user-interactive, discord-mention, etc.
    $table->jsonb('completion_chain')->nullable();      // {started: 3, completed: 2, abandoned: 1}
    $table->jsonb('skills_used')->nullable();
    $table->jsonb('skills_missed')->nullable();

    // Layer 2c: Friction intelligence
    $table->integer('course_corrections')->default(0);
    $table->integer('retry_loops')->default(0);
    $table->string('pivot_trigger', 100)->nullable();
    $table->integer('mcp_failures')->default(0);
    $table->boolean('session_cut_short')->default(false);
    $table->string('resumed_from')->nullable();

    // Layer 2d: Relationship & people signals
    $table->jsonb('people_mentioned')->nullable();
    $table->jsonb('relationship_actions')->nullable();
    $table->string('outreach_type', 20)->nullable();

    // Layer 2e: Value metrics
    $table->integer('estimated_time_saved_minutes')->nullable();
    $table->integer('artifacts_created')->default(0);
    $table->integer('knowledge_filed')->default(0);
    $table->integer('decisions_made')->default(0);

    // Metadata
    $table->string('extraction_model', 50)->default('haiku');
    $table->float('extraction_cost_usd')->default(0);
    $table->timestamps();
});

// Indexes for common report queries
Schema::table('session_facets', function (Blueprint $table) {
    $table->index('outcome');
    $table->index('priority_alignment');
    $table->index('workflow_type');
    $table->index('initiated_by');
    $table->index('created_at');
});

1b. Eloquent Model

// app/Models/SessionFacet.php
// Follows ClaudeSession model pattern
class SessionFacet extends Model
{
    protected $table = 'session_facets';

    protected $fillable = [
        'session_id', 'underlying_goal', 'goal_categories', 'outcome',
        'user_satisfaction_counts', 'claude_helpfulness', 'session_type',
        'friction_counts', 'friction_detail', 'primary_success', 'brief_summary',
        'projects', 'businesses', 'priority_alignment', 'priority_matched',
        'workflow_type', 'initiated_by', 'completion_chain',
        'skills_used', 'skills_missed',
        'course_corrections', 'retry_loops', 'pivot_trigger', 'mcp_failures',
        'session_cut_short', 'resumed_from',
        'people_mentioned', 'relationship_actions', 'outreach_type',
        'estimated_time_saved_minutes', 'artifacts_created',
        'knowledge_filed', 'decisions_made',
        'extraction_model', 'extraction_cost_usd',
    ];

    protected $casts = [
        'goal_categories' => 'json',
        'user_satisfaction_counts' => 'json',
        'friction_counts' => 'json',
        'projects' => 'json',
        'businesses' => 'json',
        'completion_chain' => 'json',
        'skills_used' => 'json',
        'skills_missed' => 'json',
        'people_mentioned' => 'json',
        'relationship_actions' => 'json',
        'session_cut_short' => 'boolean',
    ];

    public function session(): BelongsTo
    {
        return $this->belongsTo(ClaudeSession::class, 'session_id');
    }
}

Add inverse relationship to ClaudeSession:

// In ClaudeSession.php
public function facet(): HasOne
{
    return $this->hasOne(SessionFacet::class, 'session_id');
}

1c. Add facets_extracted_at to claude_sessions

// New migration
$table->timestampTz('facets_extracted_at')->nullable()->after('memories_extracted_at');

This follows the memories_extracted_at pattern — tracks which sessions have been processed to avoid re-extraction.

1d. Host Bridge Endpoint

Add to host-bridge-server.ts following the /api/claude/catch-memories pattern:

// In host-bridge-server.ts
app.post("/api/claude/extract-facets", async (req, res) => {
  const { sessionId } = req.body;
  try {
    const result = await extractSessionFacets(sessionId);
    res.json(result);
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

Add to claude-service.ts:

// In claude-service.ts — follows classifySession() pattern
export async function extractSessionFacets(sessionId: string): Promise<FacetResult> {
  // 1. Read transcript from DB or disk
  const session = await getSession(sessionId); // via API or direct DB

  // 2. Sample transcript (same strategy as classifySession: 10 start, 20 middle, 10 end)
  const allEntries = await parseTranscriptEntries(session.transcriptPath);
  const sampledEntries = sampleEntriesForClassification(allEntries);
  const transcriptText = sampledEntries.map(e => e.text).join('\n---\n');

  // 3. Build context-aware prompt with Andy-specific knowledge
  const prompt = buildFacetExtractionPrompt(transcriptText, session);

  // 4. Run Haiku
  const proc = Bun.spawn([
    CLAUDE_PATH,
    "--print",
    "--model", "haiku",
    "--max-turns", "1",
    prompt
  ], {
    stdout: "pipe",
    stderr: "pipe",
    env: { ...process.env, ANTHROPIC_MODEL: "claude-3-5-haiku-latest" },
  });

  const stdout = await new Response(proc.stdout).text();

  // 5. Parse JSON response
  const jsonMatch = stdout.match(/\{[\s\S]*\}/);
  if (!jsonMatch) throw new Error("No JSON in Haiku response");

  return JSON.parse(jsonMatch[0]);
}

1e. Facet Extraction Prompt

This is the critical piece. The prompt gives Haiku context about Alex's businesses, priorities, and skills so it can attribute sessions accurately.

function buildFacetExtractionPrompt(transcript: string, session: SessionMeta): string {
  return `[ANDY-INTERNAL-TASK] [extract-facets]

You are analyzing a Claude Code session to extract structured facets for an insights system.
The user is Alex Hillman who runs multiple businesses and uses Claude Code as infrastructure.

## Context

**Alex's Businesses:**
- indy-hall: Coworking community in Philadelphia (events, memberships, operations)
- stb: Stacking the Bricks (teaching, courses, online learning, product development)
- 10k: 10k Independents (freelance, solopreneur, small business network)
- goodsomm: GoodSomm (wine education)
- personal: Personal tasks, life management
- system: Andy infrastructure, automation, tooling

**Alex's Stated Priorities:**
1. Growing Indy Hall
2. Building partnerships
3. Strengthening relationships

**Session Metadata:**
- Category: ${session.category || 'uncategorized'}
- Tools used: ${JSON.stringify(session.toolsUsed)}
- Files touched: ${JSON.stringify(session.filesTouched?.slice(0, 20))}
- Message count: ${session.messageCount}
- Duration: ${session.durationMinutes ?? 'unknown'} minutes

## Instructions

Analyze the transcript below and extract ALL of the following facets as JSON.
Be precise. Only mark things you can directly observe in the transcript.
For fields you cannot determine, use null.

**Satisfaction signals to look for:**
- Explicit: "thanks", "perfect", "that's wrong", "no", corrections
- Implicit: user moving on vs. repeatedly asking same thing

**Course corrections:** Count messages where user redirects Claude ("no", "that's wrong", "try X instead", "I meant", corrections of any kind)

**Skills gap:** If the user manually does something that a slash command handles (e.g., manually composing an email when /compose exists, manually filing a link when /file-this exists), flag it.

## Transcript

${transcript}

## Required JSON Output

{
  "underlying_goal": "string — what the user was trying to accomplish",
  "goal_categories": {"category_name": count},
  "outcome": "fully_achieved|mostly_achieved|partially_achieved|not_achieved|unclear_from_transcript",
  "user_satisfaction_counts": {"satisfied": 0, "likely_satisfied": 0, "dissatisfied": 0, "frustrated": 0},
  "claude_helpfulness": "essential|very_helpful|moderately_helpful|slightly_helpful|unhelpful",
  "session_type": "single_task|multi_task|iterative_refinement|exploration",
  "friction_counts": {"wrong_approach": 0, "misunderstanding": 0, "tool_limitation": 0, "buggy_code": 0, "no_results": 0, "loop_detected": 0},
  "friction_detail": "string or null",
  "primary_success": "string — what worked best",
  "brief_summary": "1-2 sentence summary",
  "projects": ["project-slug-1"],
  "businesses": ["indy-hall"],
  "priority_alignment": "aligned|supportive|neutral|drift",
  "priority_matched": "growing-indy-hall|building-partnerships|strengthening-relationships|system-maintenance|null",
  "workflow_type": "email|research|filing|meeting-prep|agreement|event-planning|relationship-maintenance|skill-building|debugging|content-creation|admin",
  "initiated_by": "user-interactive|discord-mention|scheduled-job|sub-agent|webhook",
  "completion_chain": {"started": 0, "completed": 0, "abandoned": 0},
  "skills_used": ["skill-name"],
  "skills_missed": ["skill-name-that-should-have-been-used"],
  "course_corrections": 0,
  "retry_loops": 0,
  "pivot_trigger": "string or null",
  "mcp_failures": 0,
  "session_cut_short": false,
  "people_mentioned": ["Person Name"],
  "relationship_actions": [{"person": "Name", "action": "type"}],
  "outreach_type": "proactive|reactive|automated|none",
  "estimated_time_saved_minutes": 0,
  "artifacts_created": 0,
  "knowledge_filed": 0,
  "decisions_made": 0
}

Respond with ONLY the JSON object. No explanation.`;
}

1f. Artisan Command

// app/Console/Commands/ExtractFacets.php
// Follows CatchMemories pattern exactly

class ExtractFacets extends Command
{
    protected const LOCK_KEY = 'facets:extract:running';
    protected const LOCK_PID_KEY = 'facets:extract:pid';

    protected $signature = 'sessions:extract-facets
        {sessionId? : Specific session ID to process}
        {--since= : Process sessions since date}
        {--limit=20 : Max sessions per run}
        {--force : Re-extract even if already done}
        {--dry-run : Show what would be extracted}
        {--json : JSON output}';

    protected $description = 'Extract insight facets from Claude Code sessions via Haiku';

    protected string $hostBridgeUrl = 'http://host.containers.internal:2640/api/claude/extract-facets';

    // ... follows CatchMemories::handle() pattern:
    // 1. Acquire lock (Cache-based with PID checking)
    // 2. Query sessions WHERE facets_extracted_at IS NULL AND message_count > 5
    // 3. For each session, POST to host bridge
    // 4. Store result in session_facets table
    // 5. Set facets_extracted_at on claude_sessions
    // 6. Return stats
}

1g. Scheduled Job

// scripts/scheduled-jobs/extract-session-facets.cjs
// Follows sync-claude-sessions.cjs pattern

const JOB_NAME = 'extract-session-facets';

module.exports = {
  name: JOB_NAME,
  interval: '15,45 * * * *', // :15 and :45 of every hour (offset from sync at :00,:15,:30,:45)
  timezone: 'America/New_York',

  async run() {
    const result = await withTracking(JOB_NAME, async () => {
      const output = await runCommand('podman', [
        'exec', 'andy-core', 'php', 'artisan',
        'sessions:extract-facets', '--limit=10', '--json'
      ]);
      return { stats: JSON.parse(output) };
    });

    // Only notify on errors or large batches
    if (!result.success) {
      await sendNotification({
        ...formatJobResult({ jobName: 'Facet Extraction', success: false, error: result.error }),
        mention: true,
      });
    }
  }
};

1h. API Endpoint

Add facets to the existing sessions API:

// In SessionController.php — update show() method
$data['facet'] = $session->facet;

// New endpoint for facet stats
Route::get('/sessions/facet-stats', [SessionController::class, 'facetStats']);

---

Phase 2: Report Generation

Depends on: Phase 1 (needs facets in database) Deliverables: Artisan command, HTML report template, Discord summary

2a. Report Artisan Command

// app/Console/Commands/GenerateInsightsReport.php

class GenerateInsightsReport extends Command
{
    protected $signature = 'insights:report
        {--period=weekly : weekly or monthly}
        {--since= : Start date}
        {--until= : End date}
        {--format=all : html, markdown, discord, or all}
        {--upload : Upload HTML to DO Spaces}';

    // 1. Query session_facets for the period
    // 2. Aggregate into report sections
    // 3. Pass aggregated data to Sonnet for narrative synthesis
    // 4. Render HTML report (following token-usage page patterns)
    // 5. Upload to DO Spaces
    // 6. Post summary to Discord
}

2b. Aggregation Queries

These run against session_facets joined with claude_sessions:

// Attention map — sessions/tokens by business
$attentionMap = SessionFacet::join('claude_sessions', 'session_facets.session_id', '=', 'claude_sessions.id')
    ->where('claude_sessions.ended_at', '>=', $since)
    ->selectRaw("
        unnest(businesses) as business,
        COUNT(*) as sessions,
        SUM(claude_sessions.input_tokens + claude_sessions.output_tokens) as total_tokens
    ")
    ->groupBy('business')
    ->orderByDesc('sessions')
    ->get();

// Priority alignment score
$alignmentScore = SessionFacet::where('created_at', '>=', $since)
    ->whereNotNull('priority_alignment')
    ->selectRaw("
        priority_alignment,
        COUNT(*) as count,
        ROUND(COUNT(*)::numeric / SUM(COUNT(*)) OVER () * 100, 1) as pct
    ")
    ->groupBy('priority_alignment')
    ->get();

// Friction trend (weekly buckets)
$frictionTrend = SessionFacet::where('created_at', '>=', $since)
    ->selectRaw("
        date_trunc('week', created_at) as week,
        SUM(course_corrections) as corrections,
        SUM(retry_loops) as loops,
        SUM(mcp_failures) as mcp_fails,
        COUNT(*) FILTER (WHERE session_cut_short) as cut_short
    ")
    ->groupBy('week')
    ->orderBy('week')
    ->get();

// Skills gap report
$skillsGap = SessionFacet::where('created_at', '>=', $since)
    ->whereNotNull('skills_missed')
    ->selectRaw("jsonb_array_elements_text(skills_missed) as skill, COUNT(*) as times_missed")
    ->groupBy('skill')
    ->orderByDesc('times_missed')
    ->get();

// Relationship pulse
$relationshipPulse = SessionFacet::where('created_at', '>=', $since)
    ->whereNotNull('people_mentioned')
    ->selectRaw("jsonb_array_elements_text(people_mentioned) as person, COUNT(*) as mentions")
    ->groupBy('person')
    ->orderByDesc('mentions')
    ->limit(20)
    ->get();

2c. Narrative Synthesis

Use Sonnet (via host-bridge) to turn aggregated stats into coaching-style narrative:

// In claude-service.ts
export async function synthesizeInsightsReport(data: ReportData): Promise<string> {
  const prompt = `[ANDY-INTERNAL-TASK] [insights-synthesis]

  Generate a coaching-style insights report from the following aggregated data.
  The user is Alex Hillman. Speak directly to him. Be specific and actionable.

  ${JSON.stringify(data)}

  Sections to write:
  1. "At a Glance" — 3-4 bullet headline observations
  2. "Where Your Attention Went" — time/session allocation analysis
  3. "Priority Alignment" — are sessions matching stated priorities?
  4. "Friction Highlights" — top friction moments with specific examples
  5. "Skills Gap Alert" — skills that should have been used
  6. "Relationship Pulse" — who you interacted with, proactive vs reactive
  7. "System Health" — automated infrastructure performance
  8. "Recommendations" — 3 specific, actionable things to try next week

  Write in markdown. Be direct, no fluff.`;

  // Use Sonnet for better narrative quality
  const proc = Bun.spawn([CLAUDE_PATH, "--print", "--model", "sonnet", "--max-turns", "1", prompt]);
  return await new Response(proc.stdout).text();
}

2d. HTML Report

Follow the existing report.html pattern from /insights but with Andy-specific sections. Use the same pure-CSS charting approach from the token-usage admin page (no external chart libraries).

2e. Discord Summary

Post a condensed version to Discord weekly:

**Andy Insights — Week of Feb 3**

📊 42 human sessions | 1.2M tokens | 87 artifacts created

🎯 **Priority Alignment:** 68% aligned (↑ from 61% last week)
- 18 sessions → Growing Indy Hall
- 9 sessions → System maintenance
- 6 sessions → Partnerships

⚠️ **Top Friction:** Course corrections up 23%
- Biggest: Safari skill looped 4x on PDF extraction
- Skills gap: `/compose` missed 3 times (manual email drafts)

👥 **People:** 12 contacts touched, 60% proactive outreach

2f. Scheduled Report Job

// scripts/scheduled-jobs/generate-insights-report.cjs
module.exports = {
  name: 'generate-insights-report',
  interval: '0 6 * * 0', // Sunday 6am ET
  timezone: 'America/New_York',
  // Weekly report
};

---

Phase 3: Admin Dashboard

Depends on: Phase 1 + Phase 2 (needs facets and aggregation queries) Deliverables: Inertia page with interactive filters

3a. Controller

// app/Http/Controllers/Admin/InsightsController.php

class InsightsController extends Controller
{
    public function index(): Response
    {
        $period = request('period', '7'); // days
        $since = now()->subDays($period);

        return Inertia::render('Admin/Insights', [
            'attentionMap' => $this->getAttentionMap($since),
            'priorityAlignment' => $this->getPriorityAlignment($since),
            'frictionTrend' => $this->getFrictionTrend($since),
            'skillsGap' => $this->getSkillsGap($since),
            'relationshipPulse' => $this->getRelationshipPulse($since),
            'topFriction' => $this->getTopFriction($since),
            'completionRate' => $this->getCompletionRate($since),
            'summary' => $this->getSummaryStats($since),
            'filters' => request()->only(['period']),
        ]);
    }
}

3b. Route

Route::get('/admin/insights', [InsightsController::class, 'index'])
    ->name('admin.insights');

3c. React Page

Follows TokenUsage.tsx layout pattern:

• Summary cards at top (sessions, tokens, alignment score, friction count)
• Attention map as stacked bar chart (pure CSS)
• Priority alignment as donut/progress bar
• Friction trend as line chart (pure CSS)
• Skills gap as list with frequency badges
• Relationship pulse as contact list with mention counts
• Period filter (7d, 14d, 30d, 90d)

---

Phase 4: Continuous Intelligence

Depends on: Phase 1 + Phase 2 Deliverables: Weekly trend tracking, anomaly detection, proactive Discord alerts

4a. Trend Storage

// Migration: create insights_snapshots table
Schema::create('insights_snapshots', function (Blueprint $table) {
    $table->id();
    $table->string('period_type', 20); // weekly, monthly
    $table->date('period_start');
    $table->date('period_end');
    $table->jsonb('metrics'); // Full snapshot of aggregated metrics
    $table->text('narrative')->nullable(); // Sonnet-generated narrative
    $table->string('report_url')->nullable(); // DO Spaces URL
    $table->timestamps();

    $table->unique(['period_type', 'period_start']);
});

4b. Anomaly Detection

Run after each facet extraction batch:

// In ExtractFacets command, after processing batch:
// Check if friction is trending up significantly
$recentFriction = SessionFacet::where('created_at', '>=', now()->subDays(1))
    ->avg('course_corrections');
$baselineFriction = SessionFacet::where('created_at', '>=', now()->subDays(14))
    ->where('created_at', '<', now()->subDays(1))
    ->avg('course_corrections');

if ($recentFriction > $baselineFriction * 1.5) {
    // Alert via Discord: "Friction spike detected — 50%+ above 2-week average"
}

4c. Monthly Deep Dive

Monthly report adds:

• Cross-month trend comparison
• System ROI calculation (skills usage × estimated time saved)
• Relationship heatmap (people × weeks → interaction frequency)
• Coaching synthesis with specific improvement targets

---

Dependency Graph

Phase 1: Facet Extraction Engine
  ├── 1a. Migration (session_facets table)
  ├── 1b. SessionFacet model
  ├── 1c. Migration (facets_extracted_at column)
  ├── 1d. Host bridge endpoint
  ├── 1e. Extraction prompt
  ├── 1f. ExtractFacets artisan command (depends on 1a-1e)
  ├── 1g. Scheduled job (depends on 1f)
  └── 1h. API endpoint (depends on 1b)

Phase 2: Report Generation (depends on Phase 1)
  ├── 2a. GenerateInsightsReport command
  ├── 2b. Aggregation queries
  ├── 2c. Narrative synthesis via Sonnet
  ├── 2d. HTML report template
  ├── 2e. Discord summary format
  └── 2f. Scheduled report job

Phase 3: Admin Dashboard (depends on Phase 1, 2)
  ├── 3a. InsightsController
  ├── 3b. Route
  └── 3c. React page

Phase 4: Continuous Intelligence (depends on Phase 1, 2)
  ├── 4a. insights_snapshots table
  ├── 4b. Anomaly detection
  └── 4c. Monthly deep dive

---

Cost Estimate

Component	Per-run	Frequency	Monthly
Facet extraction (Haiku)	~$0.001/session	~100 sessions/week	~$0.40
Weekly report synthesis (Sonnet)	~$0.05	4/month	$0.20
Monthly deep dive (Sonnet)	~$0.10	1/month	$0.10
Total			~$0.70/month

---

Testing Strategy

1. Phase 1 validation: Run sessions:extract-facets --dry-run on 10 recent sessions, review JSON output for accuracy
2. Phase 2 validation: Generate report for last 7 days, compare against manual review of known sessions
3. Prompt tuning: Iterate on the extraction prompt based on facet quality (especially business attribution and skills gap detection)

---

Open Questions

1. Backfill: Should we extract facets for all ~1,300 classified human sessions? Cost: ~$1.30 via Haiku. Gives us immediate trend data.
2. Sub-agent sessions: Extract facets for sub-agents too (separate analysis) or skip them entirely?
3. Real-time vs batch: Extract facets immediately when sessions sync, or batch every 30 minutes? Batch is simpler but delays insights.