sqlite-notes

What this skill does

# 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:

| Status | Meaning |
|--------|---------|
| **fleeting** | Uncaptured intuition, shower thought, might be nothing |
| **developing** | Actively thinking about, exploring, not concluded |
| **supported** | Has backing - evidence, reasoning, sources |
| **settled** | Firm belief, integrated into worldview, acting on it |

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**: Evo