Index Your AI Work Plans in SQLite, Not a Markdown Folder

Working with an AI assistant produces a lot of prose. Every non-trivial task starts with a plan, a design note, a checklist, and those files pile up in a folder until you have hundreds of them and no way to find the one you need. This post describes a small pattern that fixed that for me: leave the markdown exactly where it is as the source of truth, and build a SQLite index on top so you can list, filter, and full-text search the whole history in milliseconds. The index is a derived cache. You can throw it away and rebuild it from the files at any time.

The markdown graveyard problem

When you plan work with an AI, writing the plan is cheap, so you write a lot of them. One file per feature, one per bug, one per refactor. Each has a status, a rough design, and a running log of what happened. Individually they are useful. In bulk they become a graveyard.

The failure mode is specific. Three months later you remember making a decision about how to handle a credit refund edge case, but you cannot remember which plan it lived in. So you reach for grep. Grep is slow across hundreds of files, it has no sense of which document is recent or active, and it returns raw line matches with no structure around them. You get forty hits across twenty files and still have to open each one to find out which plan it was, what its status was, and whether the decision still holds.

The deeper problem is that the AI has the same problem you do. When you start a new task, the most valuable context is the set of related decisions you already made. If that context is trapped in a folder that neither you nor the assistant can query, every new task starts from a blank slate, and you re-litigate decisions you settled months ago.

Keep markdown as the source of truth, add an index on top

The fix is not to move the plans into a database. Markdown is the right storage format. It is readable by humans, readable by an AI, diffable in git, and it survives any tool you build around it. Throwing that away for rows in a table would trade the wrong thing.

Instead, keep every plan as a markdown file with a small YAML frontmatter block, and treat those files as the only source of truth. Then build a SQLite index that reads the files and mirrors two things into tables: the structured metadata from the frontmatter, and the full body text for search. The index answers queries. The files hold the truth.

A plan file looks like this:

---
id: 41
status: active
project: web-api
tags: [area:billing, tech:go, type:feature, scope:minor]
created: 2026-05-02
---

# Credit refund on subscription downgrade

## Decision

Refunds are prorated by remaining days, not by unused credits...

Because the index is derived, it goes in .gitignore. You commit the markdown, not the database. Anyone who clones the repo runs one command to rebuild the index locally, and if it ever gets out of sync you delete it and regenerate. This is the property that makes the whole pattern safe: the database can never become a second source of truth you have to protect, because it is disposable by construction.

The schema: metadata plus full text

The core of the index is two tables. One holds a row per plan with its frontmatter fields promoted to columns. The other holds the tags, one row per tag, so you can filter and join on them.

CREATE TABLE plans (
  id          INTEGER PRIMARY KEY,
  status      TEXT NOT NULL,        -- backlog, active, completed
  project     TEXT NOT NULL,
  title       TEXT NOT NULL,
  created_at  TEXT NOT NULL,
  updated_at  TEXT NOT NULL,
  body        TEXT NOT NULL
);

CREATE TABLE tags (
  plan_id  INTEGER NOT NULL REFERENCES plans(id),
  axis     TEXT NOT NULL,           -- area, tech, type, scope
  value    TEXT NOT NULL,
  PRIMARY KEY (plan_id, axis, value)
);

Promoting frontmatter to columns is what turns vague folder browsing into precise queries. "Show me the active billing plans for the web API" stops being a manual scan and becomes a WHERE clause. The body column carries the full markdown so the search index has something to read, and so show can print a plan without a second file read.

Notes, the running log of what happened during a task, get their own table so you can append to a plan without rewriting its body:

CREATE TABLE notes (
  plan_id     INTEGER NOT NULL REFERENCES plans(id),
  created_at  TEXT NOT NULL,
  text        TEXT NOT NULL
);

Each note is a timestamped line. When you finish a session, you append one sentence about what changed. Later, the note history reads like a changelog of the decision, and it is the first thing worth showing to an AI picking up the task.

FTS5 makes search instant

SQLite ships with FTS5, a full-text search extension, built in. You do not install anything. You create a virtual table that indexes the columns you want to search, point it at the real table, and query it with a MATCH clause.

CREATE VIRTUAL TABLE plans_fts USING fts5(
  title,
  body,
  content='plans',
  content_rowid='id',
  tokenize='unicode61'
);

The content='plans' option makes this an external-content index. FTS5 stores only the search structure and reads the actual text from the plans table, so you are not keeping a second copy of every plan body. A search joins back to the base table for the columns you want to display:

SELECT p.id, p.title, p.status
FROM plans_fts f
JOIN plans p ON p.id = f.rowid
WHERE plans_fts MATCH 'credit AND refund'
ORDER BY rank
LIMIT 10;

The difference from grep is not only speed, though on hundreds of documents the query returns before you lift your finger off enter. It is that the result is structured. Every hit comes back with its id, title, and status, ranked by relevance, so the active plan floats above the three abandoned ones that mention the same words. You can add snippet() to get a highlighted excerpt around the match, and you can rank with bm25() if you want to weight the title above the body.

FTS5 also handles the queries grep is bad at: multi-word phrases, boolean AND and OR, prefix matching with refund*, and near matches. The unicode61 tokenizer folds case and splits on punctuation, which is usually what you want for mixed technical text.

A tagging scheme that stays consistent

Free-form tags rot. Left alone, one plan gets billing, another gets payments, a third gets credits, and now no single filter finds all three. The fix is a small amount of structure: every tag carries a namespace prefix that names its axis.

area:billing      area:auth      area:worker
tech:go           tech:sqlite    tech:redis
type:feature      type:bugfix    type:refactor
scope:patch       scope:minor    scope:major

Each axis answers a different question. area is the part of the product, tech is the stack, type is the kind of work, scope is the size. A plan carries one or two per axis. The prefix does two useful things. It makes the vocabulary self-documenting, because you can list all values on one axis and see the whole controlled vocabulary at a glance. And it keeps filters honest, because a query for area:billing cannot accidentally match a plan tagged tech:billing-lib.

Storing tags as (axis, value) rows rather than a comma-joined string is what makes this queryable. Filtering by one tag is a join. Filtering by two is two joins. Listing the vocabulary on an axis is a SELECT DISTINCT value FROM tags WHERE axis = 'area'. None of that is possible if the tags live in a single text field.

The commands you run every day

None of this is worth much if you have to write SQL to use it. The index earns its place behind a small command-line tool that wraps the common queries. These are the ones I reach for constantly:

The pattern that matters most for AI collaboration is the first move on any task. Before reading code, before opening files, you run plan search or plan list --tag to find related past work, then plan show to load the decisions. That gives the assistant real context in one step instead of a cold start. It is the difference between "we decided this in plan 41, here is the reasoning" and re-deriving the same answer from scratch.

# Start a task by loading context, not by grepping
plan list --tag area:billing --status active
plan show 41
plan notes 41 --last 5

Why SQLite and not something bigger

SQLite fits this job almost too well. It is a single file, so the index is one artifact you can delete and regenerate, with nothing to gitignore beyond that one path. There is no server to run, no port to manage, no daemon that has to be alive before you can list your plans. FTS5 is built in, so search costs you a CREATE VIRTUAL TABLE and nothing else. And it is transactional, so a reindex either completes or leaves the old index untouched.

The larger point is that this pattern gets the best of two formats at once. Markdown is what humans and AI assistants read and write well, and it is what git tracks. A relational index is what machines query well. You do not have to choose. You write and read in markdown, and you query in SQL, and a rebuild step keeps the second in sync with the first. A heavier database would add operational weight without adding anything the job needs, because the dataset is small, local, single-writer, and disposable.

The tradeoffs, and when a folder is enough

This is a real cost, not a free win. The index has to be kept in sync. Every time the markdown changes, the database is stale until you reindex. You can hide most of that behind a file-watcher or a git hook that reindexes on commit, but the coupling is there, and a stale index that silently returns old results is worse than no index at all. You need the rebuild to be cheap and, ideally, automatic.

There is also a build-and-maintain cost. Someone has to write the parser, the schema, and the command-line wrapper, and keep them working as the frontmatter evolves. That is a fixed cost you pay once, and it only pays off past a certain volume.

So be honest about scale. If you have a few dozen plans, this is over-engineering. A folder of markdown and an editor with fuzzy file search or plain grep will find anything you need in the time it takes to type the query, and you skip the whole apparatus. The index earns its keep when the count crosses into the hundreds, when grep starts returning noise instead of answers, and when the cost of not finding a past decision is that you make it again, differently, and create a bug. That is the point where a queryable index stops being a toy and starts saving you real time on every task.

The rule I settled on is simple. Below a hundred plans, use a folder. Above it, put a SQLite index on top of the folder. Keep the markdown as the source of truth either way, so the day you outgrow the folder, adding the index is a build step, not a migration.