type-slug

description	Extract learnings from Claude Code session history.
user-invocable	true
user-invocable-only	true
argument-hint	[--all | --since DATE]
allowed-tools	["Bash","Read","Agent"]

Retrospective — Extract Learnings from Session History

You are running the regular retrospective workflow. Mine Claude Code session history for patterns, repeated feedback, and hard-won learnings that should be preserved.

Variables

• INIT_DB: scripts/init-db.sh
• DB_FILE: ~/.claude/retro/retro.duckdb

---

Database Schema

Tables

msgs — Main message table

Column	Type	Description
uuid	uuid	Primary key
parentUuid	uuid	Parent message (for threading)
sessionId	uuid	Session identifier
timestamp	timestamp	Message time
type	varchar	user or assistant
cwd	varchar	Working directory
message	struct	Message struct with role, content fields
filename	varchar	Source file path

Access message fields: message.role, message.content (JSON)

coding_sessions — Aggregated coding activity

Column	Type	Description
session_id	uuid	Session identifier
org	varchar	GitHub org
repo	varchar	Repository name
edits	int64	Edit tool calls
writes	int64	Write tool calls
extensions	varchar[]	File extensions edited
started	timestamp	First message
ended	timestamp	Last message

Macros

Macro	Returns	Description
extract_text(message.content)	varchar	Extract text from message content JSON
thread_back(uuid, depth)	table(uuid, parentUuid, sessionId, timestamp, message, depth)	Walk parent chain
thread_forward(uuid, depth)	table(uuid, parentUuid, sessionId, timestamp, message, depth)	Walk child chain
search_user(terms, limit)	table(candidate json)	FTS on user messages
search_assistant(terms, limit)	table(candidate json)	FTS on assistant messages
search_messages(terms, limit)	table(candidate json)	Combined search

Query Patterns

-- Filter by role
SELECT uuid, extract_text(message.content) FROM msgs WHERE type = 'user';

-- FTS search
SELECT * FROM search_user('wrong folder', 20);

-- Thread expansion
SELECT uuid, extract_text(message.content), depth FROM thread_back('uuid'::uuid, 5);

---

Step 1 — Database Initialization

Run the init script with any user-provided flags from $ARGUMENTS:

bash INIT_DB $ARGUMENTS

The script handles:

• --all: Load full history
• --since DATE: Load since specific date (YYYY-MM-DD format)
• (default): Load since last run via watermark

Important: If the user provides a relative date like "last 4 weeks", "past 30 days", or "2 months ago", convert it to YYYY-MM-DD format before calling the script. Use today's date to calculate.

If 0 rows loaded, warn the user and stop.

---

Step 2 — Discovery Phase

Discovery follows a strict pipeline. Each stage feeds the next:

┌─────────┐     ┌─────────┐     ┌─────────┐     ┌──────────┐
│  Seeds  │ ──▶ │ Needles │ ──▶ │ Threads │ ──▶ │ Clusters │
└─────────┘     └─────────┘     └─────────┘     └──────────┘
   words          UUIDs        conversations     grouped
  to search      (hits)        (expanded)        threads

Critical rule: Never cluster needles directly. A needle is just a pointer — the thread is the actual conversation. Always expand before clustering.

2a. Load Seeds

Seeds are search terms that find entry points into valuable conversations.

Load custom seeds first (REQUIRED if file exists):

cat ~/.claude/retro/seeds.md 2>/dev/null || echo "No custom seeds"

Custom seeds take priority over defaults. Parse the file and use those terms.

seeds.md format:

# Custom Seeds

## Corrections
- "that's not right"
- "/nota"

## Preferences
- "I always want"

Default seeds (fallback when no custom seeds):

Category	Seeds
Decision points	"Option A" OR "Option B" OR "Pros:" OR "Cons:"
Corrections ack	"You're right" OR "Good point" OR "I should have"
User corrections	"not what I" OR "don't do" OR "no," OR "wrong"
User confirmations	"perfect" OR "exactly" OR "yes that"

2b. Find Needles

MANDATORY: Query EVERY seed category before proceeding. Quality depends on coverage.

For each category in seeds.md (or defaults), run:

duckdb DB_FILE -c "LOAD fts; SELECT * FROM search_user('<terms>', 15);"
duckdb DB_FILE -c "LOAD fts; SELECT * FROM search_assistant('<terms>', 15);"

Use search_user for user seeds, search_assistant for assistant seeds.

Filter noise: Skip messages containing task notification, /retro:spective, /init.

2b-gate. Seed Coverage Check

STOP: Before proceeding to 2c, display this summary table:

| Seed Category | Source | Queries | Needles |
|---------------|--------|---------|---------|
| Corrections (user) | seeds.md | 1 | 12 |
| Confirmations (user) | seeds.md | 1 | 8 |
| Architecture (assistant) | seeds.md | 1 | 15 |
| ... | ... | ... | ... |
| **TOTAL** | | X | Y |

If any category shows 0 queries, you missed it. Go back and query it.

Aim for 30-50 total needles across all categories.

2c. Expand Needles to Threads

For EVERY needle, expand to get the full conversation:

duckdb DB_FILE -c "SELECT * FROM thread_back('UUID'::uuid, 5);"
duckdb DB_FILE -c "SELECT * FROM thread_forward('UUID'::uuid, 10);"

Thread traversal helpers:

• thread_back(uuid, depth) — walk parent chain (find conversation start)
• thread_forward(uuid, depth) — walk child chain (find resolution)

Extract the actual message content from expanded threads. This is what you'll cluster.

Utilities:

• extract_text(content) — extract text from message content JSON, filters tool_use/tool_result

2d. Cluster Threads

Now cluster the expanded threads (not raw needles).

Use the classifier agent (.claude/agents/classifier.md):

Agent({
  subagent_type: "retro:classifier",
  prompt: `Cluster these conversation threads by topic.

INPUT:
<threads>
${threadData}
</threads>

OUTPUT SCHEMA:
[{"topic": "short description", "items": [1, 2, ...], "sources": ["seed_category", "coding:repo"], "type": "correction|preference|architectural|confirmation"}]

Target 5-10 clusters. Return JSON only.`
})

2e. Present Clusters

Show clusters with thread summaries (not just needle text). Ask user which to explore.

2f. Coding Session Discovery

For sessions with code changes, query the coding_sessions table:

Column	Type	Description
session_id	uuid	Session identifier
org	varchar	GitHub org
repo	varchar	Repository name
edits	int64	Edit tool calls
extensions	varchar[]	File extensions edited

duckdb DB_FILE -c "
  SELECT session_id, repo, edits, extensions
  FROM coding_sessions
  WHERE edits > 5
  ORDER BY edits DESC
  LIMIT 20;
"

For interesting sessions, search for needles within that session, then expand as above.

---

Step 3 — Classification Phase

For each cluster the user selected:

3a. Refine Thread Boundaries

If threads seem to span multiple topics, use classifier agent:

Agent({
  subagent_type: "retro:classifier",
  prompt: `Detect topic boundaries in this conversation.

INPUT:
${messages}

OUTPUT SCHEMA:
{"boundary": "relevant|topic_changed", "trim_at": "uuid or null"}

Return JSON only.`
})

3b. Classify Threads

Use classifier agent for each thread:

Agent({
  subagent_type: "retro:classifier",
  prompt: `Classify this conversation thread.

INPUT:
${threadText}

CATEGORIES:
- correction: user corrected Claude's approach
- preference: user stated a preference
- architectural: user made a design decision
- frustration: user expressed repeated frustration
- confirmation: user confirmed a good approach

OUTPUT SCHEMA:
{"type": "...", "summary": "one-line description", "context": "why this matters"}

Return JSON only.`
})

Track seed attribution: Note which seed term found each thread. This enables self-improvement in Step 7.

---

Step 4 — Generalization Phase

Before review, generalize findings to extract reusable principles.

4a. Generalize Findings

Use reasoner agent (Sonnet) — generalization requires nuanced abstraction:

Agent({
  subagent_type: "retro:reasoner",
  prompt: `Extract the general, reusable principle from this specific finding.

INPUT:
Specific finding: ${summary}
Context: ${context}
Project: ${project}

OUTPUT SCHEMA:
{
  "specific": "the concrete finding",
  "general": "the reusable principle",
  "scope": "cross-project|project-specific|context-dependent",
  "confidence": 0.0-1.0
}

Scope meanings:
- cross-project: applies everywhere
- project-specific: tied to this codebase's conventions
- context-dependent: needs more instances to generalize

Return JSON only.`
})

4b. Handle Low-Confidence Findings

If confidence < 0.6 or scope == "context-dependent":

• Don't promote to memory yet
• Store in staging area: ~/.claude/retro/staging/<project>/
• These await reinforcement from future sessions

---

Step 5 — Deduplication Phase

Before storing, check for semantic overlap with existing memories.

5a. Load Existing Memories

find ~/.claude/retro/memory -name "*.md" -exec cat {} \; 2>/dev/null

5b. Check for Duplicates

Use reasoner agent (Sonnet) — semantic comparison requires judgment:

Agent({
  subagent_type: "retro:reasoner",
  prompt: `Compare this new finding against existing memories. Determine overlap.

NEW FINDING:
${generalizedPrinciple}

EXISTING MEMORIES:
${existingMemorySummaries}

OUTPUT SCHEMA:
{
  "action": "new|merge|reinforce|skip",
  "existing_match": "memory-name or null",
  "reason": "why this decision"
}

Actions:
- new: no overlap, create new memory
- merge: overlaps but adds nuance, merge into existing
- reinforce: semantically identical, bump reinforcement count
- skip: already fully captured

Return JSON only.`
})

5c. Execute Action

Based on dedup result:

• new: proceed to review
• merge: show user both, propose merged version
• reinforce: increment count in existing memory, update last_seen
• skip: inform user, don't re-store

---

Step 6 — Review Workflow

Present generalized findings for user approval.

6a. Present Findings

For each finding from Step 4, display:

## Finding: [type]

**Specific**: [original finding]
**Generalized**: [extracted principle]
**Scope**: [cross-project / project-specific]
**Confidence**: [0.0-1.0]

### Thread Preview
[Show 3-5 key messages from expanded thread]

Action: [approve / reject / skip / edit]?

User can edit to refine the generalization before storing.

6b. Collect Decisions

Track user decisions:

• approve — store as-is
• reject — discard
• skip — move to staging for later
• edit — user provides refined generalization

6c. Generate Summary

After all findings reviewed, compile approved ones:

## Findings from /retro session

### New Memories
- [generalized principle] (scope, confidence)

### Reinforced
- [existing memory] — seen again (+1, total: N)

### Staged (awaiting reinforcement)
- [low-confidence finding] — needs more instances

6d. Save to Memory

Memory location: ~/.claude/retro/memory/<project>/

Memory file format:

---
name: <type>-<slug>
description: <generalized principle — one line>
metadata:
  type: <feedback|user|project>
  source: retro
  scope: <cross-project|project-specific>
  reinforcement_count: 1
  first_seen: <date>
  last_seen: <date>
  ttl_days: 90
---

<generalized principle>

**Origin**: <specific finding that led to this>
**Why:** <why this matters>
**How to apply:** <when/where this applies>

Map finding types to memory types:

• correction → feedback
• preference → feedback
• architectural → project
• confirmation → feedback
• frustration → feedback

Create directory if needed. Update ~/.claude/retro/memory/<project>/MEMORY.md index.

---

Step 7 — Memory Decay

Memories fade if not reinforced.

7a. Check for Stale Memories

At start of each retrospective session, scan for expired memories:

find ~/.claude/retro/memory -name "*.md" -mtime +90

For each memory file, check:

• last_seen + ttl_days < today → candidate for decay

7b. Decay Rules

reinforcement_count	ttl_days	action on expiry
1	90	delete
2-3	180	demote to staging
4+	365	flag for review

Higher reinforcement = longer TTL. Frequently validated memories persist.

7c. Present Decay Candidates

## Memories approaching expiry

These haven't been reinforced recently:

- [memory name] — last seen 85 days ago, expires in 5 days
  Action: [keep / let expire / refresh]?

• keep: reset TTL, keep in memory
• let expire: allow natural decay
• refresh: user confirms still valid, bump last_seen

---

Step 8 — Self-Improvement

After review, analyze patterns:

1. Seed effectiveness: Which seeds led to approved vs rejected findings?
2. New phrases: Extract common phrases from approved findings that aren't in current seeds

Use classifier agent to identify new seed candidates:

Agent({
  subagent_type: "retro:classifier",
  prompt: `Extract distinctive phrases from these approved findings that could be new search seeds.

INPUT:
${approvedFindings}

OUTPUT SCHEMA:
[{"phrase": "...", "category": "corrections|preferences|architectural|confirmation", "reason": "..."}]

Return 3-5 phrases. JSON only.`
})

If new seeds found, present to user:

## Suggested New Seeds

Based on your approved findings:
- "actually I meant" → corrections (appeared 3x in approved)
- "going forward" → preferences (appeared 2x in approved)

Add these to ~/.claude/retro/seeds.md? [y/n]

If approved, append to seeds.md (create if doesn't exist).