A SQLite-backed editorial state machine

Chapter 6 of 7. Prerequisite: The gated pipeline. You know the six-step gate and the automated checks that guard it. This chapter builds the state layer beneath them: the pipeline's entire state, modelled in local SQLite files you can inspect with one command and back up with cp.

Hosted workflow tools solve editorial state for teams. A card sits in exactly one column, and the board never disagrees with itself. Nothing local-first did the same job for this pipeline, because the state it tracks spans repos that hosted boards cannot see into, while the drafts themselves stay as markdown in a git repo on one machine. The gap is real and the fix is smaller than it sounds: a defined schema in a SQLite file, owned by the pipeline's own tooling. This chapter documents the schema that runs this site's pipeline, as a transferable pattern rather than an install guide.

A SQLite editorial state machine for a developer blog

The core decision is what SQLite is for in this design. It is not the source of truth. The devlog markdown stays authoritative, append-only, newest-first, exactly as the earlier chapters left it. Every database described below is a rebuildable cache over that markdown plus the published MDX. Delete the file and the next sync rebuilds it. That single rule removes the scariest failure mode of a homegrown state store: if the cache and reality disagree, reality wins and the cache is regenerated.

With that rule fixed, SQLite is the obvious store. The SQLite project's own application-file-format page makes the case directly: "An SQLite database file with a defined schema often makes an excellent application file format." The same page ranks it against the alternatives you would otherwise reach for: "But in many cases, SQLite is a far better choice than either a custom file format, a pile-of-files, or a wrapped pile-of-files." A pile of JSON status files is precisely what an editorial pipeline grows into without this decision, and every gate then reimplements its own parsing and locking.

The workload fits the engine's stated sweet spot. From Appropriate Uses For SQLite: "For device-local storage with low writer concurrency and less than a terabyte of content, SQLite is almost always a better solution. SQLite is fast and reliable and it requires no configuration or maintenance. It keeps things simple. SQLite 'just works'." An editorial pipeline on one machine has one or two writers on its busiest day. There is no server to provision and no migration away from your own laptop.

Three design principles carry the rest of the chapter:

1. Markdown authoritative, DB rebuildable. State lives in gitignored database files; schema lives in code.
2. Explicit links only. No embeddings, no fuzzy matching. The tooling never infers a relationship a model thinks might exist. If two entries should relate, they share a tag.
3. Sync on use. The CLI re-syncs registered repos on every invocation, hash-based and incremental. No cron and no daemon on the editorial-index side.

Two databases, not one

The system splits state across two SQLite files, and the split is load-bearing.

The editorial index belongs to the skill CLI. It holds parsed devlog entries, shipped articles, the citation graph between them, thesis proposals, and a workflow audit trail. Nothing long-running touches it. Every CLI invocation syncs it from the registered repos and then answers the query. The CLI prints JSON; any LLM in the loop is only a formatting layer over deterministic output.

The automation queue belongs to the pipeline daemon. It holds the work items: queued articles, thesis variants, schedule slots, spawned agent jobs, the append-only schedule-event log. One long-running process owns writes to it.

The boundary between them is a read-only crack, held deliberately narrow. The skill CLI reads the pipeline database for one purpose: backfilling citations for articles the automated path shipped. It never writes across the boundary. Ownership stays unambiguous, and either file can be rebuilt or replaced without corrupting the other.

Ownership also decides where WAL would earn its place. The daemon writes to the queue while sweeps, chat callbacks, and the read-only CLI read it, and WAL mode is the documented answer to exactly that shape: "WAL provides more concurrency as readers do not block writers and a writer does not block readers. Reading and writing can proceed concurrently." One honest note on the record, though: both of this pipeline's databases currently run SQLite's default rollback journal. The editorial index, with its single sync-on-use writer, barely needs more. The WAL advice in this chapter is pattern guidance, not a description of the live configuration: a queue file with a daemon and overlapping readers is where you would enable it first.

The schema: sixteen tables, five state machines

Sixteen substantive tables span the two files, nine on the editorial-index side and seven on the queue side. The index tables that matter most:

• repos: registered repos, with path as primary key, name, remote URL, last-synced timestamp.
• entries: parsed devlog entries, holding date, type, scope, subject, body, a source hash for incremental sync, and a soft-delete column. UNIQUE(repo_path, date, subject) makes re-syncs idempotent.
• entry_tags: one row per (entry, tag). The only relatedness mechanism in the system.
• articles: shipped articles: slug, title, published date, the locked thesis.
• article_citations: the join that makes the whole design worth building. Covered in its own section below.
• proposals: one row per topic, carrying the thesis variants, the triage decision, and the workflow state.
• events: append-only audit of every workflow command. The design doc's justification is one sentence: without it, decisions evaporate at session close.

The queue side mirrors the shape at automation scale: article_queue for work items, thesis_variants, slots as a first-class schedule entity, agent_jobs for spawned LLM jobs, schedule_events as the append-only audit log, and a key-value cadence_config.

The state machines live in ordinary columns hardened with CHECK constraints. Five of them run the pipeline. Proposals move through six states. Queue rows move through six work states and six schedule states on separate columns. Slots have five states. Agent jobs have five. Here is the proposals table, trimmed to its state core:

CREATE TABLE proposals (
  id            INTEGER PRIMARY KEY,
  repo_path     TEXT NOT NULL,
  topic         TEXT NOT NULL,
  variants_json TEXT,
  thesis        TEXT,
  triage        TEXT CHECK (triage IN ('upgrade','leave','skip','defer')),
  state         TEXT NOT NULL DEFAULT 'proposed'
                CHECK (state IN ('proposed','drafting','shipped',
                                 'deferred','skipped','left')),
  article_id    INTEGER REFERENCES articles(id),
  UNIQUE (repo_path, topic)
);

The CHECK constraint is doing gate work. A typo'd state string is a hard error at write time instead of a row that silently falls out of every query. The queue's work-state column gets the same treatment, with its cancellable subset (queued, drafting, drafted, edited) defined once and mirrored into the code that cancels sibling rows. Mirrored constants are a known hazard in this design, and the section on drift below shows what happens when a mirror goes stale.

Every transition writes two rows in one transaction

State machines earn their keep through their audit trails. The queue database defines eighteen event kinds for schedule mutations, CHECK-constrained like the states: scheduled, fired, fire_failed, expired, sibling_cancelled, slot_substituted, sla_breach, drafter_refused, and ten more. Every slot mutation writes a paired record: the state change on the entity, plus an event row carrying from_state, to_state, the actor, and the attempt number. Both rows commit in a single transaction:

BEGIN IMMEDIATE;

UPDATE slots
   SET state = 'firing',
       attempt_count = attempt_count + 1,
       last_transition_at = strftime('%s','now')
 WHERE id = :slot_id AND state = 'pending';

INSERT INTO schedule_events
       (slot_id, kind, from_state, to_state, actor, attempt)
VALUES (:slot_id, 'fired', 'pending', 'firing', 'sweep', :attempt);

COMMIT;

The pairing is only trustworthy because the commit is atomic. The SQLite atomic-commit documentation states the guarantee plainly: "Atomic commit means that either all database changes within a single transaction occur or none of them occur." The stronger clause is the one a laptop-hosted pipeline actually depends on: "SQLite has the important property that transactions appear to be atomic even if the transaction is interrupted by an operating system crash or power failure." This pipeline's daemon runs on a machine that sleeps and sometimes loses power mid-sweep. A state that changed without its event, or an event describing a change that never landed, would poison every later diagnosis. The engine forecloses both.

Transitions carry rules beyond the happy path. Illegal moves raise instead of writing: a slot in firing cannot re-enter firing without passing through substituting. Idempotency is explicit: re-asserting the same state with the same work item is a no-op, while the same state with a different work item is an error, because that is a second actor fighting for the slot. Terminal states accept repeat calls silently. Each rule sounds fussy until the audit trail shows why it exists. Before slots became a first-class table with these rules, slot identity was implicit in a timestamp column on the queue row, and one crash mid-substitution produced eleven audit events in three seconds with no rate limit and no idempotent recovery. The explicit machine added a per-slot floor of five seconds between transitions and a recovery sweep that moves anything stuck in a transient state for over fifteen minutes to breach, with a recorded reason.

Cross-repo references without a server

The citation graph is the reason a real database beats frontmatter lists. Every shipped article cites the devlog entries it draws from, and the entries can live in a different repo than the article:

CREATE TABLE article_citations (
  article_id            INTEGER NOT NULL REFERENCES articles(id),
  entry_id              INTEGER NOT NULL REFERENCES entries(id),
  source                TEXT NOT NULL CHECK (source IN
    ('auto-date','manual','tag-match','pipeline-import','forensic-backfill')),
  cited_entry_repo_path TEXT    -- NULL means same repo as the article
);

Two columns carry the design. cited_entry_repo_path makes a citation cross-repo with no server, no API, no shared hosted board: the site's repo can cite a devlog entry in a tooling repo because both are registered in the same index and synced by the same CLI. source records provenance for every edge. A citation added by date-window inference is distinguishable from one a human asserted, and manual beats auto on conflict. Provenance turns "why does this article cite that entry" from archaeology into a query.

Explicit linking is the philosophy underneath, stated in the design notes as a refusal: the tooling never uses embeddings or fuzzy matching to guess that two pieces of work relate. Tags are the entire relatedness mechanism. This costs recall and buys something worth more in an editorial system: no phantom relationships. When a gate blocks an article for overlapping a shipped one, the overlap is a set intersection over recorded entry IDs, auditable by hand. The check itself runs on a small derived table of per-article fingerprints, one row per article carrying its topic key, its thesis key, and the exact entry-ID set drawn from these citations. The duplicate rule is arithmetic on those fingerprints: same topic key with at least five shared entries and a Jaccard similarity above 0.5, or an identical thesis key.

A cache over markdown will drift from the markdown, so reconciliation is a first-class command rather than a hope. It diffs the articles directory against the database in both directions: orphan MDX exists on disk with no database row, ghost rows exist in the database with no file. Flags backfill the former and prune the latter. Deletions on the entry side are soft, via a deleted_at column, so a citation edge never dangles into a hard-deleted row.

The state stays in the file, the harness stays stateless

The clearest architectural consequence of this schema shows up in what it made unnecessary. When the pipeline needed LLM workers for thesis generation, the fork was real: adopt a multi-agent orchestration framework, or build a project-owned harness. The decision, recorded in the write-up of that fork, went to the harness: a script that spawns a terminal session and drives the model directly. No SDK dependency, no persistent worker daemon, no message broker, and stateless between invocations. Roughly twenty lines the project owns entirely.

Stateless workers are only possible because the database is doing the remembering. The harness reads its task from a row, does its work, and its output lands back as rows: a status on the agent_jobs table, draft output on the queue row, source entry IDs recorded for the citation graph. Kill the harness mid-run and nothing is lost that a state transition had recorded. Orchestration frameworks earn their complexity when workers must coordinate with each other; here they coordinate through the schema instead.

The design was validated the way reusability claims should be: by a second consumer arriving with no notice. The harness was built for thesis generation. A voice-fix rewrite layer arrived independently and used it unmodified. Two consumers, zero changes. That second consumer is itself two layers with the same stateless shape: a pure-regex tells scrubber with no model and no network call, writing a sidecar of flags next to the draft, and a rewrite agent that consumes those flags through a hand-written 10-rule voice profile. The layers stay independent at runtime. The scrubber runs without the agent; the agent reads the scrubber's output but does not require it. The voice rules themselves, and the audit that enforces them at publish time, are the subject of the companion course on AI prose voice.

Build yours

The pattern transfers without any of this site's tooling. What it needs is discipline about ownership and a schema you write down.

1. Declare the markdown authoritative and the database a cache

   Write the rule into the schema file as a comment and into the tooling as behaviour. Every table must be reconstructible from files in git. If a table cannot be rebuilt, it is state you are hosting, and it belongs in the repo instead.

2. Split by writer, not by topic

   One database per owning process. A sync-on-use CLI owns the editorial index; a daemon owns the automation queue. Cross-boundary access is read-only and narrow. Enable WAL on any file where a writer and readers overlap.

3. Model states as CHECK-constrained columns

   Enumerate every state in the constraint. Define the cancellable subset once. Name the illegal transitions and raise on them. Give terminal states idempotent re-entry.

4. Pair every transition with an audit event in one transaction

   An append-only events table with from_state, to_state, actor, and attempt, committed atomically with the state change. This trail is what you will read at 09:07 when a slot misfired at 09:00.

5. Record provenance on every relationship edge

   A source column on your citation or link table, and an explicit repo-path column for cross-repo edges. Refuse fuzzy inference. Tags you assert beat similarities a model guesses.

6. Ship a reconcile command before you need it

   Diff disk against database in both directions. Orphans get backfilled, ghosts get pruned, and the command runs on demand rather than on faith.

The whole store for this pipeline is two files a few megabytes each, gitignored, rebuildable, queryable with the sqlite3 binary already on the machine. The duplicate gate, the sibling cancel, and the SLA alarm from the prerequisite chapter all resolve to SELECTs against these tables, and every incident post-mortem on this site that involves the pipeline starts by reading the events they left behind. The next and final chapter puts the schema under its worst week: missed slots, substitute walks, and the SLA recovery stack that grew out of them.