Sqlite Notes

SQLite Notes - Personal Thinking Environment

A minimalist sqlite skill for note-taking, thinking trails, and knowledge building.

This skill uses SQLite as a local-first, composable thinking environment that captures the story of how you ended up thinking the way you do, with full lineage and provenance tracking. SQL is the interface, schemas are DDL, and the database is portable.

Overview

sqlite-notes transforms SQLite into a personal knowledge system that:

• Captures notes, resources, clippings, and reflections with clear provenance
• Generates AI thinking trail snapshots (breadcrumbs) to track intellectual momentum
• Organizes notes through workflow stages (inbox, working, permanent, archive)
• Links entities naturally through a flexible links table and structural foreign keys
• Synthesizes insights from multiple sources with full lineage tracking
• Provides full-text search with FTS5 (ranking, boolean operators, snippets)
• Keeps all data local in a single .sqlite/notes.db file
• Works seamlessly with Claude Code for AI-assisted thinking and synthesis

Philosophy

Capture-Heavy, Not Pristine

This system prioritizes capturing the raw materials of thinking over maintaining a perfectly organized knowledge graph. Notes are allowed to be messy, incomplete, and evolving. The goal is to preserve the trail of how ideas developed, not to present a polished final product.

Story Over Structure

Rather than forcing everything into a predetermined taxonomy, this system captures the story of how you ended up thinking the way you do. Notes evolve from fleeting captures to permanent references. Breadcrumbs snapshot your thinking at a moment in time. Reflections synthesize patterns that emerge.

Clear Provenance

Every piece of content explicitly tracks its origin:

• me: You authored it
• llm: AI generated it entirely
• external: Imported from elsewhere
• llm-assisted: Collaborative creation between you and AI

This transparency ensures you always know what came from where, and can trace the lineage of any idea.

Epistemic Status

Separate from who created content is how validated it is. The epistemic field tracks confidence:

This applies equally to human and AI content. A shower thought (origin: me, epistemic: fleeting) and a Claude synthesis (origin: llm, epistemic: fleeting) both start unvalidated. Through review, either can become supported or settled.

This enables workflows like syntopic reading where Claude explores sources and generates speculative connections. Those start as fleeting, get upgraded to supported when evidence backs them, and to settled when you've reviewed and accepted them into your thinking.

AI as Thinking Partner

Breadcrumbs and reflections leverage AI to:

• Surface patterns you might miss
• Connect ideas across time
• Ask questions that push thinking forward
• Synthesize insights from scattered notes

But they preserve full context - the prompts used, the notes considered, the relationships formed - so you can verify and understand the AI's reasoning.

When to Use This Skill

Use sqlite-notes as your memory-on-disk for thinking. This skill is relevant whenever you need to:

Capture & Organize Thoughts

• Quick capture of fleeting ideas without friction
• Save interesting quotes and highlights from reading
• Track external resources (articles, papers, videos, repos)
• Organize notes through workflow stages as they mature

Track Thinking Over Time

• Generate breadcrumb snapshots of current intellectual state
• See what themes are emerging in your notes
• Identify momentum (exploring, converging, scattered, breakthrough)
• Maintain continuity between thinking sessions

Synthesize Knowledge

• Generate AI reflections from notes, breadcrumbs, and clippings
• Promote valuable reflections into permanent notes
• Build evergreen notes from accumulated insights
• Trace lineage from source material to synthesized knowledge

Link Ideas

• Connect notes through explicit relationships in the links table
• Query relationships between notes, resources, and clippings with JOINs
• Discover unexpected patterns through graph traversal queries
• Leverage SQL to find multi-hop connections

Review & Process

• Weekly review of inbox notes
• Move notes between workflow stages
• Tag and categorize during review
• Identify stale working notes

Replace Scattered Notes

Instead of maintaining notes in:

• Scattered markdown files
• Ephemeral chat messages
• Bookmarks that never get read
• Mental notes that get forgotten

Keep everything in one queryable, linkable, persistent database with full-text search.

Core Tables

notes

The atomic unit of thought. Can be anything from a fleeting idea to a permanent reference note.

Key fields: id, title, body, folder, origin, epistemic, tags (JSON array), source_url, source_title, captured_at, reviewed_at

Workflow stages (folder):

• inbox: Unsorted, recently captured
• journal: Date-bound entries
• working: Active development, exploring ideas
• permanent: Evergreen, reference-quality
• archive: Preserved but no longer active

Provenance (origin): me, llm, external, llm-assisted

Epistemic status: fleeting, developing, supported, settled

breadcrumbs

AI-generated snapshot of your thinking state at a moment in time. Analyzes recent notes to surface themes, connections, questions, and momentum. Forms a continuous trail over time through the prev_breadcrumb_id foreign key.

Key fields: id, summary, themes (JSON array), connections, questions (JSON array), momentum, window_start, window_end, notes_considered, prev_breadcrumb_id (FK to breadcrumbs)

Momentum states: exploring, converging, scattered, dormant, breakthrough

resources

External reference material (articles, books, papers, videos, repos, etc.) that you want to track and potentially extract highlights from.

Key fields: id, url, title, resource_type, status, author, domain, rating, summary, tags (JSON array), added_at, finished_at

Resource types: article, book, paper, video, podcast, repo, tool, course, thread, other

Status workflow: queued → reading → finished (or abandoned/reference)

clippings

Quote, highlight, or excerpt from a resource. Captures exact text along with location and personal annotations. Uses resource_id foreign key for structural relationship (no separate link needed).

Key fields: id, content, annotation, location, chapter, source, resource_id (FK to resources), external_id, tags (JSON array), clipped_at

Sources: readwise, kindle, manual, web, pdf, other

reflections

AI-generated synthesis of notes, breadcrumbs, and clippings. Can be weekly reviews, theme explorations, connection maps, or custom formats. Tracks full generation context and can be promoted to permanent notes via promoted_to_note_id FK.

Key fields: id, title, content, reflection_type, template_used, prompt_context, model, status, epistemic, rating, feedback, promoted_to_note_id (FK to notes), generated_at

Reflection types: weekly-review, theme-synthesis, question-exploration, connection-map, insight, custom

Status workflow: draft → reviewed → promoted (or discarded)

links

Generic relationship table for flexible many-to-many connections between any entities. This is the graph table.

Key fields: source_id, target_id, rel_type

Relationship vocabulary:

• linksTo: Conceptual connection (note → note)
• derivedFrom: Evolution/refinement (note → note)
• references: External source citation (note → resource)
• includesClipping: Embedded quote (note → clipping)
• promptedBy: AI-inspired (note → breadcrumb)
• continuesFrom: Trail continuation (breadcrumb → breadcrumb, via FK but can also be in links)
• analyzedNotes: Input for AI analysis (breadcrumb → note)
• suggestedConnections: AI recommendation (breadcrumb → note)
• mentionedIn: Reverse reference (resource → note)
• relatedTo: Conceptual similarity (resource → resource)
• usedIn: Quote usage (clipping → note)
• promptedReflection: Synthesis inspiration (clipping → reflection)
• basedOnBreadcrumbs: Synthesis input (reflection → breadcrumb)
• basedOnNotes: Synthesis input (reflection → note)
• basedOnClippings: Synthesis input (reflection → clipping)
• promotedTo: Became permanent (reflection → note, via FK but can also be in links)
• followsUp: Sequential reflection (reflection → reflection)

Setup

One-Command Setup

# Initialize database with schema and views
cd /path/to/your/notes/
./skills/sqlite-notes/scripts/setup.sh

This creates .sqlite/notes.db with all tables, indexes, FTS virtual tables, triggers, and views.

Manual Setup

# Create database directory
mkdir -p .sqlite

# Apply schema
sqlite3 .sqlite/notes.db < skills/sqlite-notes/assets/schema.sql

# Apply views
sqlite3 .sqlite/notes.db < skills/sqlite-notes/assets/views.sql

# Verify
sqlite3 .sqlite/notes.db "SELECT name FROM sqlite_master WHERE type='table' ORDER BY name;"

Workflow Patterns

Quick Capture Flow

Goal: Get thoughts out of your head with minimal friction.

# Capture a fleeting thought
sqlite3 .sqlite/notes.db <<'SQL'
INSERT INTO notes (id, body, folder, origin, captured_at)
VALUES (
  'N-' || strftime('%Y%m%d', 'now') || '-' || lower(hex(randomblob(4))),
  'Interesting thought about distributed consensus...',
  'inbox',
  'me',
  strftime('%Y-%m-%dT%H:%M:%SZ', 'now')
);
SQL

# Capture with title and tags
sqlite3 .sqlite/notes.db <<'SQL'
INSERT INTO notes (id, title, body, folder, origin, tags, captured_at)
VALUES (
  'N-' || strftime('%Y%m%d', 'now') || '-' || lower(hex(randomblob(4))),
  'Meeting notes - Product sync',
  'Key decisions:
- Ship v2 next month
- Focus on performance',
  'journal',
  'me',
  json_array('meetings', 'product'),
  strftime('%Y-%m-%dT%H:%M:%SZ', 'now')
);
SQL

# Capture from external source
sqlite3 .sqlite/notes.db <<'SQL'
INSERT INTO notes (id, title, body, folder, origin, source_url, source_title, tags, captured_at)
VALUES (
  'N-' || strftime('%Y%m%d', 'now') || '-' || lower(hex(randomblob(4))),
  'Notes from Lamport paper',
  'Key insight: [[N-a8f9B3c2]] relates to this',
  'inbox',
  'external',
  'https://example.com/paper.pdf',
  'Time, Clocks, and the Ordering of Events',
  json_array('distributed-systems'),
  strftime('%Y-%m-%dT%H:%M:%SZ', 'now')
);
SQL

Weekly Review Flow

Goal: Process inbox, move notes to appropriate folders, add links and tags.

# 1. Check what's in inbox (use view for quick access)
sqlite3 -header -column .sqlite/notes.db "SELECT * FROM v_inbox;"

# 2. Review a specific note
sqlite3 -header -column .sqlite/notes.db "SELECT * FROM notes WHERE id = 'N-20260208-a8f9b3c2';"

# 3. Move to appropriate folder, add tags, mark as reviewed
sqlite3 .sqlite/notes.db <<'SQL'
UPDATE notes
SET folder = 'working',
    tags = json_array('distributed-systems', 'consensus'),
    reviewed_at = strftime('%Y-%m-%dT%H:%M:%SZ', 'now')
WHERE id = 'N-20260208-a8f9b3c2';
SQL

# 4. Add connections to related notes
sqlite3 .sqlite/notes.db <<'SQL'
INSERT INTO links (source_id, target_id, rel_type)
VALUES ('N-20260208-a8f9b3c2', 'N-20260205-x7y8z9w0', 'linksTo');
SQL

# 5. Check for stale working notes
sqlite3 -header -column .sqlite/notes.db "SELECT * FROM v_stale_working;"

# 6. Promote mature note to permanent
sqlite3 .sqlite/notes.db <<'SQL'
UPDATE notes
SET folder = 'permanent',
    epistemic = 'supported',
    reviewed_at = strftime('%Y-%m-%dT%H:%M:%SZ', 'now')
WHERE id = 'N-20260208-a8f9b3c2';
SQL

Breadcrumb Generation Flow

Goal: Generate AI snapshot of current thinking state.

# 1. Query recent notes as JSON for AI analysis (last 7 days in inbox + working)
sqlite3 -json .sqlite/notes.db <<'SQL'
SELECT id, title, body, folder, tags, captured_at
FROM notes
WHERE (folder IN ('inbox', 'working'))
  AND captured_at >= date('now', '-7 days')
ORDER BY captured_at DESC;
SQL

# 2. Get previous breadcrumb for continuity
sqlite3 -json .sqlite/notes.db <<'SQL'
SELECT * FROM v_latest_breadcrumb;
SQL

# 3. AI analyzes the notes and generates:
#    - summary: high-level state
#    - themes: emerging topics
#    - connections: patterns noticed
#    - questions: things to explore
#    - momentum: exploring/converging/scattered/dormant/breakthrough

# 4. Create the breadcrumb
sqlite3 .sqlite/notes.db <<'SQL'
INSERT INTO breadcrumbs (
  id, summary, themes, connections, questions, momentum,
  window_start, window_end, notes_considered, prev_breadcrumb_id, generated_at
)
VALUES (
  'BC-' || strftime('%Y%m%d', 'now') || '-' || lower(hex(randomblob(4))),
  'Exploring connections between distributed systems and product design',
  json_array('distributed-systems', 'product-design', 'team-dynamics'),
  'Conways law appearing in both architecture and feature discussions',
  json_array('How does team structure affect API design?', 'What patterns cross domains?'),
  'exploring',
  strftime('%Y-%m-%dT%H:%M:%SZ', date('now', '-7 days')),
  strftime('%Y-%m-%dT%H:%M:%SZ', 'now'),
  15,
  (SELECT id FROM breadcrumbs ORDER BY generated_at DESC LIMIT 1),
  strftime('%Y-%m-%dT%H:%M:%SZ', 'now')
);
SQL

# 5. Batch link breadcrumb to all analyzed notes
# This is where SQL shines — one query creates N links
sqlite3 .sqlite/notes.db <<'SQL'
INSERT INTO links (source_id, target_id, rel_type)
SELECT
  (SELECT id FROM breadcrumbs ORDER BY generated_at DESC LIMIT 1),
  notes.id,
  'analyzedNotes'
FROM notes
WHERE (folder IN ('inbox', 'working'))
  AND captured_at >= date('now', '-7 days');
SQL

Reflection Generation and Promotion

Goal: Generate AI synthesis and promote valuable insights to permanent notes.

# 1. Generate reflection from notes and breadcrumbs
# (AI analyzes input and generates title + content)

# 2. Create the reflection
sqlite3 .sqlite/notes.db <<'SQL'
INSERT INTO reflections (
  id, title, content, reflection_type, model, status, epistemic, generated_at
)
VALUES (
  'RF-' || strftime('%Y%m%d', 'now') || '-' || lower(hex(randomblob(4))),
  'Weekly Review: Design Patterns',
  '# Key Insights

This week showed strong convergence around distributed systems patterns...

## Main Themes
- Conway''s Law in practice
- API design as team communication
- Emergent architecture

## Questions to Explore
- How to design for team evolution?',
  'weekly-review',
  'claude-sonnet-4-5-20250929',
  'draft',
  'fleeting',
  strftime('%Y-%m-%dT%H:%M:%SZ', 'now')
);
SQL

# 3. Batch link to source material (notes and breadcrumbs that informed it)
sqlite3 .sqlite/notes.db <<'SQL'
-- Link to recent breadcrumbs
INSERT INTO links (source_id, target_id, rel_type)
SELECT
  (SELECT id FROM reflections ORDER BY generated_at DESC LIMIT 1),
  bc.id,
  'basedOnBreadcrumbs'
FROM breadcrumbs bc
WHERE bc.generated_at >= date('now', '-14 days')
LIMIT 3;

-- Link to notes analyzed
INSERT INTO links (source_id, target_id, rel_type)
SELECT
  (SELECT id FROM reflections ORDER BY generated_at DESC LIMIT 1),
  n.id,
  'basedOnNotes'
FROM notes n
WHERE n.folder IN ('working', 'permanent')
  AND EXISTS (
    SELECT 1 FROM json_each(n.tags)
    WHERE value IN ('distributed-systems', 'product-design')
  );
SQL

# 4. Review and rate the reflection
sqlite3 .sqlite/notes.db <<'SQL'
UPDATE reflections
SET status = 'reviewed',
    rating = 4,
    feedback = 'Good synthesis, helped clarify thinking on Conway''s Law'
WHERE id = 'RF-20260208-a1b2c3d4';
SQL

# 5. Promote valuable reflection to permanent note
sqlite3 .sqlite/notes.db <<'SQL'
-- Create the permanent note
INSERT INTO notes (id, title, body, folder, origin, epistemic, tags, captured_at)
SELECT
  'N-' || strftime('%Y%m%d', 'now') || '-' || lower(hex(randomblob(4))),
  title,
  content,
  'permanent',
  'llm-assisted',
  'supported',
  json_array('distributed-systems', 'design-patterns'),
  strftime('%Y-%m-%dT%H:%M:%SZ', 'now')
FROM reflections
WHERE id = 'RF-20260208-a1b2c3d4';

-- Update reflection with promoted_to_note_id and status
UPDATE reflections
SET promoted_to_note_id = (
      SELECT id FROM notes WHERE title = 'Weekly Review: Design Patterns' ORDER BY captured_at DESC LIMIT 1
    ),
    status = 'promoted'
WHERE id = 'RF-20260208-a1b2c3d4';
SQL

Resource and Clipping Flow

Goal: Track external resources and extract valuable highlights.

# 1. Add resource to reading queue
sqlite3 .sqlite/notes.db <<'SQL'
INSERT INTO resources (id, url, title, resource_type, author, status, tags, added_at)
VALUES (
  'R-' || strftime('%Y%m%d', 'now') || '-' || lower(hex(randomblob(4))),
  'https://example.com/article',
  'How to Build Better Software',
  'article',
  'Jane Smith',
  'queued',
  json_array('software-engineering', 'process'),
  strftime('%Y-%m-%dT%H:%M:%SZ', 'now')
);
SQL

# 2. Start reading
sqlite3 .sqlite/notes.db <<'SQL'
UPDATE resources
SET status = 'reading'
WHERE id = 'R-20260208-x7k9m2n5';
SQL

# 3. Capture clipping from resource
# Note: resource_id FK creates structural relationship — no separate link needed
sqlite3 .sqlite/notes.db <<'SQL'
INSERT INTO clippings (id, content, annotation, location, source, resource_id, tags, clipped_at)
VALUES (
  'CL-' || strftime('%Y%m%d', 'now') || '-' || lower(hex(randomblob(4))),
  'The best code is no code at all.',
  'This resonates with minimalism in design',
  'p. 42',
  'manual',
  'R-20260208-x7k9m2n5',
  json_array('minimalism'),
  strftime('%Y-%m-%dT%H:%M:%SZ', 'now')
);
SQL

# 4. Use clipping in a note
sqlite3 .sqlite/notes.db <<'SQL'
-- Create note that references the clipping
INSERT INTO notes (id, body, folder, origin, captured_at)
VALUES (
  'N-' || strftime('%Y%m%d', 'now') || '-' || lower(hex(randomblob(4))),
  '> The best code is no code at all.

This connects to minimalism principles in architecture design.',
  'working',
  'me',
  strftime('%Y-%m-%dT%H:%M:%SZ', 'now')
);

-- Link note to clipping
INSERT INTO links (source_id, target_id, rel_type)
VALUES (
  (SELECT id FROM notes ORDER BY captured_at DESC LIMIT 1),
  'CL-20260208-a1b2c3d4',
  'includesClipping'
);
SQL

# 5. Finish resource with rating
sqlite3 .sqlite/notes.db <<'SQL'
UPDATE resources
SET status = 'finished',
    rating = 4,
    summary = 'Good overview of software minimalism principles',
    finished_at = strftime('%Y-%m-%dT%H:%M:%SZ', 'now')
WHERE id = 'R-20260208-x7k9m2n5';
SQL

Full-Text Search

FTS5 provides powerful full-text search capabilities that memhub doesn't have.

# Basic full-text search on notes
sqlite3 -header -column .sqlite/notes.db <<'SQL'
SELECT n.id, n.title, snippet(notes_fts, 1, '**', '**', '...', 20) AS snippet
FROM notes_fts
JOIN notes n ON notes_fts.rowid = n.rowid
WHERE notes_fts MATCH 'distributed AND systems'
ORDER BY rank;
SQL

# Boolean operators
sqlite3 .sqlite/notes.db <<'SQL'
-- AND: both terms must appear
SELECT id, title FROM notes WHERE rowid IN (
  SELECT rowid FROM notes_fts WHERE notes_fts MATCH 'distributed AND consensus'
);

-- OR: either term
SELECT id, title FROM notes WHERE rowid IN (
  SELECT rowid FROM notes_fts WHERE notes_fts MATCH 'distributed OR consensus'
);

-- NOT: exclude term
SELECT id, title FROM notes WHERE rowid IN (
  SELECT rowid FROM notes_fts WHERE notes_fts MATCH 'distributed NOT systems'
);

-- Phrase search
SELECT id, title FROM notes WHERE rowid IN (
  SELECT rowid FROM notes_fts WHERE notes_fts MATCH '"distributed systems"'
);
SQL

# Search with ranking and snippets
sqlite3 -header -column .sqlite/notes.db <<'SQL'
SELECT
  n.id,
  n.title,
  snippet(notes_fts, 1, '>>>', '<<<', '...', 30) AS match_context,
  bm25(notes_fts) AS relevance_score
FROM notes_fts
JOIN notes n ON notes_fts.rowid = n.rowid
WHERE notes_fts MATCH 'consensus'
ORDER BY bm25(notes_fts)
LIMIT 10;
SQL

# Search clippings
sqlite3 -header -column .sqlite/notes.db <<'SQL'
SELECT
  c.id,
  c.content,
  snippet(clippings_fts, 0, '**', '**', '...', 40) AS snippet
FROM clippings_fts
JOIN clippings c ON clippings_fts.rowid = c.rowid
WHERE clippings_fts MATCH 'minimalism'
ORDER BY rank;
SQL

# Search reflections
sqlite3 -header -column .sqlite/notes.db <<'SQL'
SELECT
  r.id,
  r.title,
  snippet(reflections_fts, 1, '**', '**', '...', 50) AS snippet
FROM reflections_fts
JOIN reflections r ON reflections_fts.rowid = r.rowid
WHERE reflections_fts MATCH 'design patterns'
ORDER BY rank;
SQL

Views (Saved Queries)

Views are pre-defined queries that you can reference like tables. They simplify common access patterns.

# Use views for common queries
sqlite3 -header -column .sqlite/notes.db "SELECT * FROM v_inbox;"
sqlite3 -header -column .sqlite/notes.db "SELECT * FROM v_working;"
sqlite3 -header -column .sqlite/notes.db "SELECT * FROM v_evergreen;"
sqlite3 -header -column .sqlite/notes.db "SELECT * FROM v_stale_working;"
sqlite3 -header -column .sqlite/notes.db "SELECT * FROM v_reading_queue;"
sqlite3 -header -column .sqlite/notes.db "SELECT * FROM v_currently_reading;"
sqlite3 -header -column .sqlite/notes.db "SELECT * FROM v_latest_breadcrumb;"
sqlite3 -header -column .sqlite/notes.db "SELECT * FROM v_draft_reflections;"

# Analytics views
sqlite3 -header -column .sqlite/notes.db "SELECT * FROM v_tag_cloud LIMIT 20;"
sqlite3 -header -column .sqlite/notes.db "SELECT * FROM v_monthly_activity;"
sqlite3 -header -column .sqlite/notes.db "SELECT * FROM v_resource_stats;"

# Graph views
sqlite3 -header -column .sqlite/notes.db "SELECT * FROM v_note_graph WHERE center_id = 'N-20260208-a1b2';"
sqlite3 -header -column .sqlite/notes.db "SELECT * FROM v_note_richness LIMIT 10;"

# Views compose — you can join them or filter them
sqlite3 -header -column .sqlite/notes.db <<'SQL'
SELECT i.*, (SELECT COUNT(*) FROM links WHERE source_id = i.id) AS link_count
FROM v_inbox i
WHERE EXISTS (SELECT 1 FROM json_each(i.tags) WHERE value = 'distributed-systems')
ORDER BY link_count DESC;
SQL

Queries That Are Better in SQL

These queries showcase SQL's advantages over a key-value store with CLI abstractions.

JOINs: Clippings from Highly-Rated Resources

# One query, leveraging FK and JOIN
sqlite3 -header -column .sqlite/notes.db <<'SQL'
SELECT
  c.id AS clipping_id,
  c.content,
  c.annotation,
  r.title AS resource_title,
  r.rating
FROM clippings c
JOIN resources r ON c.resource_id = r.id
WHERE r.rating >= 4
ORDER BY r.rating DESC, c.clipped_at DESC;
SQL

In memhub, this would require: query all clippings → extract resource IDs → query each resource → filter by rating → combine results in jq.

Aggregations: Notes Per Folder Per Origin

sqlite3 -header -column .sqlite/notes.db <<'SQL'
SELECT
  folder,
  origin,
  COUNT(*) AS note_count,
  COUNT(CASE WHEN epistemic = 'settled' THEN 1 END) AS settled_count
FROM notes
GROUP BY folder, origin
ORDER BY folder, origin;
SQL

Memhub can't aggregate in-database. You'd need to query all notes, then aggregate in jq or scripts.

Window Functions: Breadcrumb Momentum Trend

sqlite3 -header -column .sqlite/notes.db <<'SQL'
SELECT
  id,
  summary,
  momentum,
  generated_at,
  LAG(momentum) OVER (ORDER BY generated_at) AS prev_momentum,
  CASE
    WHEN momentum = 'breakthrough' THEN '!'
    WHEN momentum = LAG(momentum) OVER (ORDER BY generated_at) THEN '→'
    ELSE '↗'
  END AS trend
FROM breadcrumbs
ORDER BY generated_at DESC;
SQL

This shows momentum changes over time. Impossible in memhub without exporting data and processing externally.

CTEs: Full Lineage of a Promoted Reflection

sqlite3 -header -column .sqlite/notes.db <<'SQL'
WITH RECURSIVE lineage AS (
  -- Start with the promoted note
  SELECT id, title, 'promoted_note' AS entity_type, 0 AS depth
  FROM notes
  WHERE id = 'N-20260208-final'

  UNION ALL

  -- Traverse backwards through links
  SELECT
    CASE
      WHEN l.source_id LIKE 'RF-%' THEN l.source_id
      WHEN l.source_id LIKE 'BC-%' THEN l.source_id
      WHEN l.source_id LIKE 'N-%' THEN l.source_id
    END AS id,
    CASE
      WHEN l.source_id LIKE 'RF-%' THEN (SELECT title FROM reflections WHERE id = l.source_id)
      WHEN l.source_id LIKE 'BC-%' THEN (SELECT summary FROM breadcrumbs WHERE id = l.source_id)
      WHEN l.source_id LIKE 'N-%' THEN (SELECT title FROM notes WHERE id = l.source_id)
    END AS title,
    CASE
      WHEN l.source_id LIKE 'RF-%' THEN 'reflection'
      WHEN l.source_id LIKE 'BC-%' THEN 'breadcrumb'
      WHEN l.source_id LIKE 'N-%' THEN 'note'
    END AS entity_type,
    lineage.depth + 1
  FROM lineage
  JOIN links l ON lineage.id = l.target_id
  WHERE lineage.depth < 5
)
SELECT * FROM lineage ORDER BY depth;
SQL

This traces the entire provenance chain. Memhub has basic relationship traversal but nothing like recursive CTEs.

Cross-Entity Search: UNION ALL

# Search across all content types in one query
sqlite3 -header -column .sqlite/notes.db <<'SQL'
SELECT 'note' AS type, id, title, captured_at AS timestamp
FROM notes
WHERE body LIKE '%consensus%'

UNION ALL

SELECT 'clipping' AS type, id, NULL AS title, clipped_at AS timestamp
FROM clippings
WHERE content LIKE '%consensus%'

UNION ALL

SELECT 'reflection' AS type, id, title, generated_at AS timestamp
FROM reflections
WHERE content LIKE '%consensus%'

ORDER BY timestamp DESC;
SQL

Common Queries

Find Notes

# All inbox notes
sqlite3 -header -column .sqlite/notes.db "SELECT * FROM v_inbox;"

# Recent captures (last 7 days)
sqlite3 -header -column .sqlite/notes.db <<'SQL'
SELECT id, title, folder, captured_at
FROM notes
WHERE captured_at >= date('now', '-7 days')
ORDER BY captured_at DESC;
SQL

# Notes with specific tag
sqlite3 -header -column .sqlite/notes.db <<'SQL'
SELECT id, title, tags
FROM notes
WHERE EXISTS (
  SELECT 1 FROM json_each(tags) WHERE value = 'distributed-systems'
);
SQL

# Evergreen notes
sqlite3 -header -column .sqlite/notes.db "SELECT * FROM v_evergreen;"

# LLM-generated notes
sqlite3 -header -column .sqlite/notes.db <<'SQL'
SELECT id, title, origin, epistemic
FROM notes
WHERE origin IN ('llm', 'llm-assisted')
ORDER BY captured_at DESC;
SQL

# Stale working notes (not reviewed in 30 days)
sqlite3 -header -column .sqlite/notes.db "SELECT * FROM v_stale_working;"

# Notes by origin breakdown
sqlite3 -header -column .sqlite/notes.db <<'SQL'
SELECT origin, COUNT(*) AS count
FROM notes
GROUP BY origin
ORDER BY count DESC;
SQL

Find Resources

# Reading queue
sqlite3 -header -column .sqlite/notes.db "SELECT * FROM v_reading_queue;"

# Currently reading
sqlite3 -header -column .sqlite/notes.db "SELECT * FROM v_currently_reading;"

# Finished articles
sqlite3 -header -column .sqlite/notes.db <<'SQL'
SELECT id, title, author, rating, finished_at
FROM resources
WHERE resource_type = 'article' AND status = 'finished'
ORDER BY finished_at DESC;
SQL

# Highly rated resources
sqlite3 -header -column .sqlite/notes.db <<'SQL'
SELECT id, title, resource_type, rating, finished_at
FROM resources
WHERE rating >= 4
ORDER BY rating DESC, finished_at DESC;
SQL

Find Breadcrumbs and Reflections

# Latest breadcrumb
sqlite3 -header -column .sqlite/notes.db "SELECT * FROM v_latest_breadcrumb;"

# Breadcrumbs showing breakthrough momentum
sqlite3 -header -column .sqlite/notes.db <<'SQL'
SELECT id, summary, momentum, generated_at
FROM breadcrumbs
WHERE momentum = 'breakthrough'
ORDER BY generated_at DESC;
SQL

# Draft reflections needing review
sqlite3 -header -column .sqlite/notes.db "SELECT * FROM v_draft_reflections;"

# Promoted reflections
sqlite3 -header -column .sqlite/notes.db <<'SQL'
SELECT id, title, reflection_type, rating, promoted_to_note_id
FROM reflections
WHERE status = 'promoted'
ORDER BY generated_at DESC;
SQL

Provenance Queries

# Epistemic distribution
sqlite3 -header -column .sqlite/notes.db <<'SQL'
SELECT epistemic, COUNT(*) AS count
FROM notes
WHERE epistemic IS NOT NULL
GROUP BY epistemic
ORDER BY
  CASE epistemic
    WHEN 'fleeting' THEN 1
    WHEN 'developing' THEN 2
    WHEN 'supported' THEN 3
    WHEN 'settled' THEN 4
  END;
SQL

# Origin breakdown with epistemic cross-tab
sqlite3 -header -column .sqlite/notes.db <<'SQL'
SELECT
  origin,
  COUNT(*) AS total,
  COUNT(CASE WHEN epistemic = 'fleeting' THEN 1 END) AS fleeting,
  COUNT(CASE WHEN epistemic = 'supported' THEN 1 END) AS supported,
  COUNT(CASE WHEN epistemic = 'settled' THEN 1 END) AS settled
FROM notes
GROUP BY origin
ORDER BY total DESC;
SQL

AI-Assisted Workflows with Claude Code

Claude Code should always use full paths to the database and heredoc style for multi-line SQL.

Quick Note Capture

User: "Capture this thought: distributed systems are really about managing uncertainty"

Claude Code:

sqlite3 .sqlite/notes.db <<'SQL'
INSERT INTO notes (id, body, folder, origin, captured_at)
VALUES (
  'N-' || strftime('%Y%m%d', 'now') || '-' || lower(hex(randomblob(4))),
  'Distributed systems are really about managing uncertainty',
  'inbox',
  'me',
  strftime('%Y-%m-%dT%H:%M:%SZ', 'now')
);
SQL

Generate Breadcrumb

User: "/breadcrumb"

Claude Code:

1. Query recent notes as JSON for analysis 2. Fetch previous breadcrumb for context 3. Analyze themes, connections, momentum 4. INSERT new breadcrumb with prev_breadcrumb_id 5. Batch INSERT...SELECT into links for analyzedNotes relationships

Synthesize Reflection

User: "Generate a weekly review reflection"

Claude Code:

1. Query notes from past week 2. Fetch recent breadcrumbs 3. Generate synthesis 4. INSERT reflection 5. Batch INSERT...SELECT into links for basedOnNotes and basedOnBreadcrumbs relationships

Link Related Notes

User: "Find notes related to distributed systems and connect them"

Claude Code:

1. Query notes with relevant tags 2. Analyze content for conceptual connections 3. INSERT INTO links with linksTo relationships 4. Report on connections made

Tips & Best Practices

1. Capture First, Organize Later: Get ideas out of your head immediately. Weekly review is when you organize.

2. Trust the Folder Workflow: inbox → working → permanent mirrors how ideas mature. Don't force notes into permanent prematurely.

3. Use Origin Consistently: Always set the correct origin (me/llm/external/llm-assisted) for provenance clarity.

4. Generate Breadcrumbs Regularly: Weekly breadcrumbs create continuity and surface patterns you might miss.

5. Review Reflections: Rate and give feedback on AI-generated reflections to improve future generations.

6. Promote Selectively: Only promote reflections that genuinely add value. Not every reflection needs to become a permanent note.

7. Archive, Don't Delete: Move old working notes to archive instead of deleting. Preserves thinking history.

8. Full Lineage Always: When creating reflections or derived notes, always link back to source material.

9. Tag During Review: Don't stress about tagging during capture. Add tags during weekly review when context is clearer.

10. Leverage FTS5: Use full-text search with boolean operators for discovery. It's faster and more powerful than LIKE.

11. Use Views, Not Memorized Queries: Views are pre-optimized and self-documenting. Reference them by name.

12. JSON Output for Agent Processing: Use sqlite3 -json when Claude Code needs to process results. It's already parsed.

13. Human-Readable for Display: Use sqlite3 -header -column for tabular output that humans read.

14. Batch Link Creation: Use INSERT...SELECT to create N links at once. SQL shines here.

15. Trust CHECK Constraints: Don't validate enums in the agent. The database enforces them. If an INSERT fails, the constraint message tells you why.

Reference Files

This skill includes:

• SKILL.md: This document (philosophy, workflows, SQL patterns)
• assets/schema.sql: Complete DDL (tables, indexes, FTS, triggers)
• assets/views.sql: 13 pre-defined views for common queries
• scripts/setup.sh: One-command idempotent database initialization
• scripts/examples.sh: Workflow demonstration with sample data
• references/queries.md: Advanced query recipes (JOINs, CTEs, window functions)

Comparison: SQLite vs Memhub

Where SQLite Wins

• Full-text search: FTS5 with ranking, boolean operators, snippets. Memhub has nothing.
• JOINs: "Clippings from resources I rated 4+" is one query. Memhub needs N separate commands + jq.
• Batch linking: INSERT INTO links ... SELECT ... creates N links at once. Memhub needs N memhub link calls.
• Aggregations: Notes per folder per month, tag clouds, resource stats — all in-database.
• Views compose: A view can reference other views, use JOINs, subqueries.
• Schema is self-documenting: .schema shows everything. No separate YAML files.
• Universal tool: sqlite3 is installed everywhere. No custom CLI to distribute.

Where SQLite Loses

• Verbosity: INSERT with 8 columns is ~10 lines vs. memhub create Note '{...}'.
• Array handling: EXISTS (SELECT 1 FROM json_each(tags) WHERE value = 'x') vs. tags contains "x".
• No visualization: No --visualize or --format mermaid.
• Quoting hazards: SQL string escaping (Conway''s Law) is error-prone for agents.
• No YAML metadata: Field descriptions/examples live in SKILL.md, not alongside the schema.
• ID generation: Must compose inline every INSERT: 'N-' || strftime('%Y%m%d', 'now') || '-' || lower(hex(randomblob(4))).

The Honest Assessment

SQLite is strictly more powerful (FTS, JOINs, aggregations), but memhub is more ergonomic for single-record CRUD and relationship traversal. The CLI abstraction reduces boilerplate. The YAML schemas are self-documenting.

If your workflows are read-heavy, query-heavy, and need search/aggregations, SQLite wins. If your workflows are write-heavy with lots of one-off record creation and simple relationship traversal, memhub's CLI is faster.

For an AI agent, both are viable. SQLite requires more care with quoting and multi-line SQL, but it rewards you with composable, powerful queries. Memhub is safer (no SQL injection risk from user input) but hits a ceiling when you need JOINs or FTS.

This skill exists to test the hypothesis: "Is memhub just a skill + a database?" The answer is nuanced. Memhub adds value through its CLI ergonomics and YAML-driven schema validation. But SQLite + a good skill can achieve the same outcomes with more power and flexibility.

---

This skill demonstrates SQL-native personal knowledge management.

For customization, modify the schema and views to fit your personal thinking and note-taking style. The goal is to capture how you think, not to force your thinking into a predefined structure.

Advanced SQL Query Recipes

This document provides advanced SQL patterns for the sqlite-notes skill, organized by query technique. These go beyond the basic patterns shown in SKILL.md and demonstrate SQL's full power for knowledge management.

1. Full-Text Search Recipes

Basic Search Across Notes

Search notes for a single term with snippets showing match context.

SELECT
  n.id,
  n.title,
  snippet(notes_fts, 1, '**', '**', '...', 30) AS snippet
FROM notes_fts
JOIN notes n ON notes_fts.rowid = n.rowid
WHERE notes_fts MATCH 'consensus'
ORDER BY rank
LIMIT 10;

Boolean Search (AND, OR, NOT)

Combine terms with boolean operators for precise searching.

-- Both terms must appear
SELECT n.id, n.title
FROM notes_fts
JOIN notes n ON notes_fts.rowid = n.rowid
WHERE notes_fts MATCH 'distributed AND systems'
ORDER BY rank;

-- Either term (broader search)
SELECT n.id, n.title
FROM notes_fts
JOIN notes n ON notes_fts.rowid = n.rowid
WHERE notes_fts MATCH 'consensus OR raft OR paxos'
ORDER BY rank;

-- Exclude term (refinement)
SELECT n.id, n.title
FROM notes_fts
JOIN notes n ON notes_fts.rowid = n.rowid
WHERE notes_fts MATCH 'distributed NOT systems'
ORDER BY rank;

Phrase Matching

Search for exact phrases using double quotes.

SELECT
  n.id,
  n.title,
  snippet(notes_fts, 1, '>>>', '<<<', '...', 40) AS match_context
FROM notes_fts
JOIN notes n ON notes_fts.rowid = n.rowid
WHERE notes_fts MATCH '"Conway''s Law"'
ORDER BY rank;

Ranked Results with BM25 Scoring

Return results ranked by relevance score (lower is more relevant).

SELECT
  n.id,
  n.title,
  bm25(notes_fts) AS relevance_score,
  snippet(notes_fts, 1, '>>>', '<<<', '...', 30) AS snippet
FROM notes_fts
JOIN notes n ON notes_fts.rowid = n.rowid
WHERE notes_fts MATCH 'distributed systems'
ORDER BY bm25(notes_fts)
LIMIT 20;

Search Across All Entity Types

Search notes, clippings, and reflections in a single query using UNION ALL.

SELECT 'note' AS type, n.id, n.title, n.captured_at AS timestamp
FROM notes_fts
JOIN notes n ON notes_fts.rowid = n.rowid
WHERE notes_fts MATCH 'consensus'

UNION ALL

SELECT 'clipping' AS type, c.id, substr(c.content, 1, 60) AS title, c.clipped_at AS timestamp
FROM clippings_fts
JOIN clippings c ON clippings_fts.rowid = c.rowid
WHERE clippings_fts MATCH 'consensus'

UNION ALL

SELECT 'reflection' AS type, r.id, r.title, r.generated_at AS timestamp
FROM reflections_fts
JOIN reflections r ON reflections_fts.rowid = r.rowid
WHERE reflections_fts MATCH 'consensus'

ORDER BY timestamp DESC;

Search with Folder/Origin Filter

Combine FTS with structured filters for targeted search.

SELECT
  n.id,
  n.title,
  n.folder,
  snippet(notes_fts, 1, '**', '**', '...', 30) AS snippet
FROM notes_fts
JOIN notes n ON notes_fts.rowid = n.rowid
WHERE notes_fts MATCH 'architecture'
  AND n.folder IN ('working', 'permanent')
  AND n.origin = 'me'
ORDER BY rank;

2. JOIN Recipes

Clippings from Highly-Rated Resources

Find all highlights from resources you rated 4 or 5 stars.

SELECT
  c.id AS clipping_id,
  c.content,
  c.annotation,
  r.title AS resource_title,
  r.author,
  r.rating
FROM clippings c
JOIN resources r ON c.resource_id = r.id
WHERE r.rating >= 4
ORDER BY r.rating DESC, c.clipped_at DESC;

Notes with Their Linked Resource Titles

Show notes that reference external resources with the resource metadata.

SELECT
  n.id AS note_id,
  n.title AS note_title,
  r.title AS resource_title,
  r.author,
  r.url
FROM notes n
JOIN links l ON n.id = l.source_id
JOIN resources r ON l.target_id = r.id
WHERE l.rel_type = 'references'
ORDER BY n.captured_at DESC;

Breadcrumbs with Analyzed Note Count and Titles

Show breadcrumbs with aggregated information about the notes they analyzed.

SELECT
  bc.id AS breadcrumb_id,
  bc.summary,
  bc.momentum,
  bc.generated_at,
  COUNT(l.target_id) AS analyzed_count,
  GROUP_CONCAT(n.title, ' | ') AS analyzed_titles
FROM breadcrumbs bc
LEFT JOIN links l ON bc.id = l.source_id AND l.rel_type = 'analyzedNotes'
LEFT JOIN notes n ON l.target_id = n.id
GROUP BY bc.id, bc.summary, bc.momentum, bc.generated_at
ORDER BY bc.generated_at DESC;

Reflections with Source Material Lineage

Show reflections with all their source notes and breadcrumbs.

SELECT
  r.id AS reflection_id,
  r.title AS reflection_title,
  r.status,
  r.rating,
  GROUP_CONCAT(DISTINCT CASE WHEN l.rel_type = 'basedOnNotes' THEN n.title END, ' | ') AS source_notes,
  GROUP_CONCAT(DISTINCT CASE WHEN l.rel_type = 'basedOnBreadcrumbs' THEN bc.summary END, ' | ') AS source_breadcrumbs
FROM reflections r
LEFT JOIN links l ON r.id = l.source_id
LEFT JOIN notes n ON l.target_id = n.id AND l.rel_type = 'basedOnNotes'
LEFT JOIN breadcrumbs bc ON l.target_id = bc.id AND l.rel_type = 'basedOnBreadcrumbs'
WHERE r.status = 'promoted'
GROUP BY r.id, r.title, r.status, r.rating
ORDER BY r.generated_at DESC;

Resource → Clippings → Notes Chain

Three-table join showing which notes use clippings from which resources.

SELECT
  r.title AS resource_title,
  r.author,
  c.content AS clipping,
  n.title AS note_title,
  n.folder AS note_folder
FROM resources r
JOIN clippings c ON r.id = c.resource_id
JOIN links l ON c.id = l.target_id AND l.rel_type = 'includesClipping'
JOIN notes n ON l.source_id = n.id
WHERE r.rating >= 4
ORDER BY r.title, c.clipped_at;

3. Aggregation Recipes

Notes Per Folder Per Month

Track note creation trends over time by folder (same as v_monthly_activity view).

SELECT
  strftime('%Y-%m', captured_at) AS month,
  folder,
  COUNT(*) AS note_count
FROM notes
GROUP BY month, folder
ORDER BY month DESC, folder;

Tag Frequency with Co-occurrence

Find which tags appear together most frequently.

-- Tag pairs that co-occur
SELECT
  t1.value AS tag1,
  t2.value AS tag2,
  COUNT(*) AS co_occurrence_count
FROM notes n
JOIN json_each(n.tags) t1
JOIN json_each(n.tags) t2
WHERE json_valid(n.tags)
  AND t1.value < t2.value  -- Avoid duplicates and self-pairs
GROUP BY t1.value, t2.value
HAVING COUNT(*) >= 2
ORDER BY co_occurrence_count DESC
LIMIT 20;

Epistemic Progression by Folder

Show how many notes are at each epistemic status within each folder.

SELECT
  folder,
  COUNT(*) AS total_notes,
  COUNT(CASE WHEN epistemic = 'fleeting' THEN 1 END) AS fleeting,
  COUNT(CASE WHEN epistemic = 'developing' THEN 1 END) AS developing,
  COUNT(CASE WHEN epistemic = 'supported' THEN 1 END) AS supported,
  COUNT(CASE WHEN epistemic = 'settled' THEN 1 END) AS settled
FROM notes
WHERE epistemic IS NOT NULL
GROUP BY folder
ORDER BY folder;

Resource Completion Rate by Type

Calculate what percentage of each resource type gets finished vs abandoned.

SELECT
  resource_type,
  COUNT(*) AS total,
  COUNT(CASE WHEN status = 'finished' THEN 1 END) AS finished,
  COUNT(CASE WHEN status = 'abandoned' THEN 1 END) AS abandoned,
  ROUND(100.0 * COUNT(CASE WHEN status = 'finished' THEN 1 END) / COUNT(*), 1) AS completion_rate
FROM resources
WHERE status IN ('finished', 'abandoned')
GROUP BY resource_type
ORDER BY completion_rate DESC;

Average Time from Queued to Finished

Calculate how long resources spend in your reading queue before completion.

SELECT
  resource_type,
  COUNT(*) AS finished_count,
  ROUND(AVG(julianday(finished_at) - julianday(added_at)), 1) AS avg_days_to_finish
FROM resources
WHERE status = 'finished'
  AND finished_at IS NOT NULL
GROUP BY resource_type
ORDER BY avg_days_to_finish;

4. Window Function Recipes

Breadcrumb Momentum Trend

Show how momentum changes over time using LAG to compare with previous breadcrumb.

SELECT
  id,
  summary,
  momentum,
  generated_at,
  LAG(momentum) OVER (ORDER BY generated_at) AS prev_momentum,
  CASE
    WHEN momentum = 'breakthrough' THEN '! Breakthrough'
    WHEN momentum = LAG(momentum) OVER (ORDER BY generated_at) THEN '→ Stable'
    WHEN momentum = 'converging' AND LAG(momentum) OVER (ORDER BY generated_at) = 'exploring' THEN '↗ Focusing'
    WHEN momentum = 'exploring' AND LAG(momentum) OVER (ORDER BY generated_at) = 'converging' THEN '↙ Diverging'
    ELSE '↔ Changing'
  END AS trend
FROM breadcrumbs
ORDER BY generated_at DESC;

Running Total of Notes Captured Per Week

Calculate cumulative note capture over time.

SELECT
  strftime('%Y-W%W', captured_at) AS week,
  COUNT(*) AS notes_this_week,
  SUM(COUNT(*)) OVER (ORDER BY strftime('%Y-W%W', captured_at)) AS total_notes
FROM notes
GROUP BY week
ORDER BY week DESC
LIMIT 20;

Note Capture Velocity (Rolling 7-Day Window)

Calculate notes per day over a rolling 7-day window to see capture trends.

SELECT
  date(captured_at) AS day,
  COUNT(*) AS notes_today,
  AVG(COUNT(*)) OVER (
    ORDER BY date(captured_at)
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS avg_notes_7day
FROM notes
GROUP BY day
ORDER BY day DESC
LIMIT 30;

Rank Notes by Link Count Within Each Folder

Show the most connected notes in each folder using window functions.

SELECT
  folder,
  id,
  title,
  link_count,
  RANK() OVER (PARTITION BY folder ORDER BY link_count DESC) AS rank_in_folder
FROM (
  SELECT
    n.folder,
    n.id,
    n.title,
    (SELECT COUNT(*) FROM links WHERE source_id = n.id OR target_id = n.id) AS link_count
  FROM notes n
)
WHERE rank_in_folder <= 5
ORDER BY folder, rank_in_folder;

5. CTE Recipes

Full Lineage of a Promoted Reflection

Trace backwards from a promoted note through reflection, breadcrumbs, and source notes.

WITH RECURSIVE lineage AS (
  -- Start with the promoted note
  SELECT
    id,
    title AS text,
    'note' AS entity_type,
    captured_at AS timestamp,
    0 AS depth
  FROM notes
  WHERE id = 'N-20260208-xyz123'  -- Replace with actual note ID

  UNION ALL

  -- Follow links backwards
  SELECT
    l.source_id AS id,
    COALESCE(
      (SELECT title FROM notes WHERE id = l.source_id),
      (SELECT title FROM reflections WHERE id = l.source_id),
      (SELECT summary FROM breadcrumbs WHERE id = l.source_id)
    ) AS text,
    CASE
      WHEN l.source_id LIKE 'N-%' THEN 'note'
      WHEN l.source_id LIKE 'RF-%' THEN 'reflection'
      WHEN l.source_id LIKE 'BC-%' THEN 'breadcrumb'
    END AS entity_type,
    COALESCE(
      (SELECT captured_at FROM notes WHERE id = l.source_id),
      (SELECT generated_at FROM reflections WHERE id = l.source_id),
      (SELECT generated_at FROM breadcrumbs WHERE id = l.source_id)
    ) AS timestamp,
    lineage.depth + 1 AS depth
  FROM lineage
  JOIN links l ON lineage.id = l.target_id
  WHERE lineage.depth < 10
)
SELECT * FROM lineage
ORDER BY depth, timestamp DESC;

Breadcrumb Chain Traversal

Follow the breadcrumb trail backwards through prev_breadcrumb_id references.

WITH RECURSIVE breadcrumb_chain AS (
  -- Start with most recent breadcrumb
  SELECT
    id,
    summary,
    momentum,
    generated_at,
    prev_breadcrumb_id,
    1 AS position
  FROM breadcrumbs
  ORDER BY generated_at DESC
  LIMIT 1

  UNION ALL

  -- Follow prev_breadcrumb_id links
  SELECT
    bc.id,
    bc.summary,
    bc.momentum,
    bc.generated_at,
    bc.prev_breadcrumb_id,
    chain.position + 1
  FROM breadcrumb_chain chain
  JOIN breadcrumbs bc ON chain.prev_breadcrumb_id = bc.id
  WHERE chain.position < 10
)
SELECT * FROM breadcrumb_chain
ORDER BY position;

Orphan Detection

Find notes with no incoming or outgoing links.

WITH linked_notes AS (
  SELECT DISTINCT source_id AS id FROM links
  WHERE source_id LIKE 'N-%'
  UNION
  SELECT DISTINCT target_id AS id FROM links
  WHERE target_id LIKE 'N-%'
)
SELECT
  n.id,
  n.title,
  n.folder,
  n.captured_at
FROM notes n
WHERE n.id NOT IN (SELECT id FROM linked_notes)
  AND n.folder IN ('working', 'permanent')
ORDER BY n.captured_at DESC;

Connected Components (Note Clusters)

Find clusters of interconnected notes using recursive traversal.

WITH RECURSIVE components AS (
  -- Seed: pick an unvisited note
  SELECT
    id,
    id AS component_id,
    1 AS depth
  FROM notes
  WHERE id = 'N-20260208-abc123'  -- Replace with starting note

  UNION

  -- Traverse links bidirectionally
  SELECT
    CASE
      WHEN l.source_id LIKE 'N-%' AND l.source_id NOT IN (SELECT id FROM components) THEN l.source_id
      WHEN l.target_id LIKE 'N-%' AND l.target_id NOT IN (SELECT id FROM components) THEN l.target_id
    END AS id,
    c.component_id,
    c.depth + 1
  FROM components c
  JOIN links l ON (c.id = l.source_id OR c.id = l.target_id)
  WHERE (l.source_id LIKE 'N-%' OR l.target_id LIKE 'N-%')
    AND c.depth < 20
)
SELECT
  n.id,
  n.title,
  c.component_id,
  c.depth
FROM components c
JOIN notes n ON c.id = n.id
ORDER BY c.depth, n.title;

6. Dashboard Queries

Overall Stats Summary

Quick snapshot of database health and activity.

SELECT
  'Total Notes' AS metric,
  CAST(COUNT(*) AS TEXT) AS value
FROM notes

UNION ALL

SELECT 'Inbox Count', CAST(COUNT(*) AS TEXT)
FROM notes WHERE folder = 'inbox'

UNION ALL

SELECT 'Permanent Notes', CAST(COUNT(*) AS TEXT)
FROM notes WHERE folder = 'permanent'

UNION ALL

SELECT 'Total Resources', CAST(COUNT(*) AS TEXT)
FROM resources

UNION ALL

SELECT 'Reading Queue', CAST(COUNT(*) AS TEXT)
FROM resources WHERE status = 'queued'

UNION ALL

SELECT 'Total Clippings', CAST(COUNT(*) AS TEXT)
FROM clippings

UNION ALL

SELECT 'Total Breadcrumbs', CAST(COUNT(*) AS TEXT)
FROM breadcrumbs

UNION ALL

SELECT 'Total Reflections', CAST(COUNT(*) AS TEXT)
FROM reflections

UNION ALL

SELECT 'Promoted Reflections', CAST(COUNT(*) AS TEXT)
FROM reflections WHERE status = 'promoted';

Inbox Health Check

Monitor inbox size, oldest unreviewed item, and average age.

SELECT
  COUNT(*) AS inbox_count,
  MIN(captured_at) AS oldest_capture,
  CAST(julianday('now') - julianday(MIN(captured_at)) AS INTEGER) AS oldest_age_days,
  CAST(AVG(julianday('now') - julianday(captured_at)) AS INTEGER) AS avg_age_days
FROM notes
WHERE folder = 'inbox';

Reading Pipeline Status

Track resources at each stage of the reading workflow.

SELECT
  status,
  COUNT(*) AS count,
  ROUND(AVG(CASE WHEN rating IS NOT NULL THEN rating END), 1) AS avg_rating
FROM resources
GROUP BY status
ORDER BY
  CASE status
    WHEN 'queued' THEN 1
    WHEN 'reading' THEN 2
    WHEN 'finished' THEN 3
    WHEN 'reference' THEN 4
    WHEN 'abandoned' THEN 5
  END;

Knowledge Growth Metrics

Track permanent note creation and reflection promotion over time.

SELECT
  strftime('%Y-%m', captured_at) AS month,
  COUNT(*) AS permanent_notes_created,
  (SELECT COUNT(*)
   FROM reflections
   WHERE status = 'promoted'
     AND strftime('%Y-%m', generated_at) = strftime('%Y-%m', notes.captured_at)
  ) AS reflections_promoted
FROM notes
WHERE folder = 'permanent'
GROUP BY month
ORDER BY month DESC
LIMIT 12;

Recent Activity Summary (Last 7 Days)

Show what happened in the past week across all entity types.

SELECT
  'Notes Captured' AS activity,
  COUNT(*) AS count
FROM notes
WHERE captured_at >= date('now', '-7 days')

UNION ALL

SELECT 'Resources Added', COUNT(*)
FROM resources
WHERE added_at >= date('now', '-7 days')

UNION ALL

SELECT 'Clippings Created', COUNT(*)
FROM clippings
WHERE clipped_at >= date('now', '-7 days')

UNION ALL

SELECT 'Breadcrumbs Generated', COUNT(*)
FROM breadcrumbs
WHERE generated_at >= date('now', '-7 days')

UNION ALL

SELECT 'Reflections Created', COUNT(*)
FROM reflections
WHERE generated_at >= date('now', '-7 days')

UNION ALL

SELECT 'Links Created', COUNT(*)
FROM links
WHERE created_at >= date('now', '-7 days');

Epistemic Progress Tracker

Monitor how notes are progressing through epistemic stages over time.

SELECT
  strftime('%Y-%m', reviewed_at) AS review_month,
  COUNT(*) AS notes_reviewed,
  COUNT(CASE WHEN epistemic = 'supported' THEN 1 END) AS reached_supported,
  COUNT(CASE WHEN epistemic = 'settled' THEN 1 END) AS reached_settled
FROM notes
WHERE reviewed_at IS NOT NULL
  AND reviewed_at >= date('now', '-6 months')
GROUP BY review_month
ORDER BY review_month DESC;

---

Usage Tips

1. Test on Sample Data First: Run these queries on a test database before production to understand output format.

2. Parameterize When Needed: Replace literal IDs (like 'N-20260208-abc123') with subqueries or pass them as variables.

3. Use EXPLAIN QUERY PLAN: Prefix queries with EXPLAIN QUERY PLAN to verify index usage for performance.

4. Build from Simple to Complex: Start with simple JOINs, then add CTEs and window functions as needed.

5. Save Useful Queries as Views: If you run a query frequently, save it as a view in views.sql.

6. Combine Techniques: These recipes can be combined - use CTEs with window functions, JOINs with FTS, etc.

7. Format for Readability: Use consistent indentation and line breaks to make complex queries maintainable.

8. Comment Complex Logic: Add SQL comments (-- comment) to explain non-obvious patterns.