By Yianna Kokalas & Claude

How this paper was written. This is a working paper from a production side project, built and operated by one engineer (an enterprise engineer by day) together with Claude, Anthropic’s AI model. The system it describes was designed and built in that collaboration, and the paper itself was co-written the same way: the arguments were developed in working sessions, the evidence comes from the system’s own incident reports and measurements, and both names on the byline mean it. We publish these papers because documented, skeptical accounts of human-AI engineering collaboration are rarer than they should be.

Position: ticket prose stays as markdown files synced by git; everything else that made files feel load-bearing (state, audit, triggers, search) lives behind one small store service: SQLite plus sqlite-vec in a single file, wrapped by a thin API on the always-on host, with every agent host a client. The position is the workload split, not a database brand: any small ACID store will do for state and any vector-enabled datastore will do for the index; ours happen to share a file, which makes filtered semantic search a plain SQL join on live state. Nothing needs Kafka, a document service, or the loss of local files. The only genuinely new components are the service wrapper and a small indexer.

0. The setting (context for a cold reader)

One engineer runs a production side project, alongside a full-time enterprise engineering job, by operating a fleet of coding agents. The constraint that shaped everything in this paper is hours, not headcount: the system has to make real progress while its operator is at work or asleep, which is why so much of it is built to run headless behind explicit approval gates.

The planning side lives in a git repository of markdown files (the “vault”): tickets, decision records, discovery reports, design papers like this one. A ticket is a prose spec (objective, scope, acceptance criteria) written to be executable by an AI implementer without clarifying questions.

Three mechanisms matter for this paper:

• The drain: a headless orchestrator that takes explicitly approved tickets (“promoted” by the human) and runs each one end-to-end: implementation, code review, fix cycles, tests, merged pull request, and, when the ticket calls for it, deployment to production. No human in the loop after approval.
• The store: the single source of truth for ticket state (status, priority, blocking relationships), today a SQLite database. State moved out of the markdown files’ YAML frontmatter and into the store in an earlier migration; the files kept the prose.
• The event log: every state change is appended as an event with actor, reason, and timestamp. This already exists in the store and has paid for itself (section 3).

The question the paper answers: files and databases both feel load-bearing, and the usual advice is to pick one. Grep and file reads are the cheapest primitives an agent has, which argues for files; concurrent claims, audit, and semantic search argue for a database. Which one loses?

1. Abstract

Neither. The tension is false: it comes from treating “the ticket” as one artifact, when a ticket is actually four workloads with different owners: prose, state, audit, and retrieval. Prose belongs to files in git; state, audit, and retrieval belong to a store, and making that store a service on the always-on host (rather than a file only one host may touch) is what lets every machine keep the workload it is best at. Most of the design was settled by internal decision records (July and August 2026); what remains is the short build list of section 9. The paper names the split, walks the data flows, and bounds the failure modes.

2. The four workloads hiding inside “a ticket”

The failure class this table prevents is one we already paid for. In a July 2026 incident (the “store clobber”), 12 finished tickets were reset to todo because markdown frontmatter and the store were both authoritative for state, and a destructive “fold files into store” code path existed to reconcile them. The dual authority was not a design choice: it was an unfinished migration. We were moving state out of frontmatter into the store, finishing the cutover kept losing to shipping product, and the in-between state stood for weeks with reconciliation code waiting for a trigger. The trigger that actually fired was a test-isolation failure: six test suites resolving the production database. The postmortem is explicit that even a perfect guard on the fold path would not have prevented the incident. Two lessons, both now principles: an open-ended migration is not a neutral halfway point, it is a standing dual source of truth (principle 3); and per-workload ownership removes the fold-path class: when the store never authors prose and git never authors state, there is no fold to guard.

3. Evidence

Every claim here traces to a measurement or an incident. House rule: measured beats reasoned. When we audited the claims that had justified past decisions, the ones derived from reasoning alone mostly did not survive re-measurement.

1. Git is not the audit layer, events are. In the July clobber, markdown and the store both read todo for the 12 finished tickets; all 12 were recovered from the event log alone. Separately, a sweep of every git log / git blame / git show call in the vault’s tooling (August 2026) found exactly one read of ticket-file history: a support utility using git log -1 on a ticket file as its default done-date. Every other call targets code repos or PR state. That one timestamp seam migrates to the event log (section 9). What git-on-tickets provides in practice is sync, an offsite copy, and one timestamp. Treating that honestly is what frees the design.
2. Host-local SQLite blocks cross-host access, not scale. The store works fine at 1,585+ tickets (~20 MB); what it cannot do is be opened over a network filesystem or written from two hosts at once. The service dissolves this without abandoning SQLite: one process on the always-on host is the only thing that ever touches the file (single-writer discipline enforced by a process boundary instead of a host boundary), and every other host is an API client. We briefly designed the workaround versions first (an ssh-wrapped CLI, registration deferred to git pulls) before recognizing them as a networked store being built one seam at a time, and building the seam once.
3. Eventing needs are tiny. One producer (the store service), a handful of consumers (drain wake-up, indexer, analytics shipper). A transactional outbox (events written in the same transaction as the state change) with each consumer sweeping from its own durable cursor covers this with zero new infrastructure. Kafka’s unit of value (many producers, consumer groups, replay at retention scale) is unused here.
4. Search cost was the real file-format pain, and the corpus is small. ~10k chunks (~150 MB indexed) is small enough that sqlite-vec’s brute-force scan answers in milliseconds; no ANN index is needed at this scale. The engine is deliberately swappable behind the service’s search(query, filters) surface: a dedicated vector database (we run Qdrant for other corpora) is the named escalation if the corpus ever outgrows brute force. Engine choice is not the risk; index freshness discipline is (section 7).
5. Host placement is not the always-on unlock. We measured what actually blocks 24/7 autonomous operation: the human approval gate (deliberate) and model-quota limits, not where the box sits. This paper deliberately does not claim the architecture buys 24/7 operation; it buys placement freedom for every agent host, because state stopped being welded to one machine’s filesystem.

4. Design principles

1. Per-workload ownership, no dual source of truth. Each field has exactly one authoritative substrate (section 2). Cross-references are by value (path + content hash), never by shared authority. The vector chunks live beside the state columns they filter on, so filters read live truth rather than a mirror.
2. Events are the source of truth for state; current state is a projection. Rebuildable from the log. Provenance (actor, reason, timestamp) on every write.
3. Cutovers are hard, never dual-write. The most expensive lesson we have, earned in section 2’s incident. Any future engine swap ships behind a verification gate in one step.
4. Agents keep their cheap primitives. Prose reads stay grep/read on local files. Retrieval that files are bad at (semantic, filtered) becomes one API call that is one SQL query inside. Nothing that is a file read today becomes an API call.
5. Triggers are transactional, consumption is cursored. An event that should wake a listener is written in the same transaction as the state change (outbox), so a wake-up can be late but never lost. Each consumer owns a durable cursor row (its last processed event id) and sweeps forward from it; a shared processed-flag would break the moment there is a second consumer, so cursors are a day-one requirement, not an optimization.
6. Sync is not backup. Sync propagates deletions faithfully; backup must survive them. Two mechanisms, deliberately.

5. Architecture

Components, grouped by plane:

• Prose plane: tickets/*.md (and decision records, discovery reports, …) in the vault git repo, cloned on every host that runs agents. A hosted git remote (GitHub) is the sync hub and offsite copy. Optionally a peer-to-peer file sync tool (Syncthing) for non-git corpora and phone-side files; explicitly not for the vault working tree, which git already syncs.
• State + retrieval plane: the store service. A thin Ruby (Rack) wrapper around the existing store library, on the always-on Mac mini, bound to the mesh VPN only, with token auth. Inside: one SQLite file holding the state projection, append-only event log, claims with leases, outbox with per-consumer cursor rows, and sqlite-vec chunks keyed (slug, section, section_hash). The service process is the only thing that touches the file; the drain on the mini is a localhost client, the laptop and any future drain host are VPN clients, and the store CLI becomes the client. The DB file never enters git.
• Listeners: the drain (wakes on ready-events, claims via CAS + lease), the indexer (pulls its vault clone on wake, re-embeds changed sections into sqlite-vec in the same file), an analytics shipper (mirrors events into a ClickHouse instance used for dashboards). All idempotent, all resumable from their cursors, all clients of the service.
• Ops plane: a phone reaches the mini over a WireGuard-based mesh VPN (Tailscale) via mosh (client app: Moshi), under a hardening posture decided separately (VPN-only exposure, per-device scoping, key expiry), and lands in herdr, our terminal-multiplexer cockpit for coding agents. Host roles: the mini is the always-on headless drain host and the service’s home; the laptop is the interactive member (planning, tickets labeled as needing a human, its own drain when wanted); the phone attaches to the mini, never the laptop, and never touches the store or the git remote directly.
• Backup: restic snapshots to an S3-compatible object store for store dumps and any non-git corpora. The git remote is the offsite copy for the prose plane. The vector chunks are derived data, rebuildable by re-embedding, but they ride along in the store dump for free.
• Elsewhere in the fleet: Qdrant on the rack keeps the corpora with no state join (a chat archive, docs). It is not in the ticket path.

5.1 Topology (objective)

This diagram is the objective, not the current state: today the store is still an in-process library on the laptop, and section 9 is the build delta. Three machines, three jobs. The laptop is where the human plans: interactive human-and-agent sessions author prose locally and talk to the store service for state (register at creation, promote, status, search); it can run a drain of its own for tickets labeled as needing a human, which the headless drain’s queue refuses by design. The always-on Mac mini runs the store service and the always-on drain; more drain workers can join from any host, since claims are CAS + lease through the service and N workers race safely. The rack server is shared infrastructure: observability, backups, and the vector database serving the non-ticket corpora.

  +----------------------+  +---------------------------+  +--------------------------+
  | laptop               |  | mac mini (always on)      |  | rack server              |
  | planning member      |  | store service + drain     |  | shared infrastructure    |
  |                      |  |                           |  |                          |
  | human <-> agent      |  | store service (Ruby):     |  | ClickHouse               |
  | sessions author      |  |   one SQLite file:        |  |   (observability mirror) |
  | prose + approve work |  |   state | events | claims |  | object store (backups)   |
  | vault clone          |  |   outbox | sqlite-vec     |  | Qdrant                   |
  | optional drain       |  |   single-writer process   |  |   (non-ticket corpora:   |
  | (interactive work)   |  | drain workers | indexer   |  |   chat archive, docs)    |
  |                      |  | vault clone               |  |                          |
  +----------+-----------+  +------------+--------------+  +------------+-------------+
             |                           |                              |
             +--------------------------->                              |
             |   store API over the mesh VPN: register, promote,        |
             |   transition, status reads, semantic search              |
             |                           |                              |
             |                           +----------------------------->
             |                           |   ship events to analytics;
             |                           |   backups to object store
             |                           |
             |                           ^  phone (Moshi) -> herdr cockpit
             |                           |
             |  git push                 |  git pull (event wake + periodic sweep)
             v                           v
  +---------------------------------------------------------------------------------+
  | git remote (GitHub): prose sync hub + offsite copy                              |
  +---------------------------------------------------------------------------------+

Every link runs inside the mesh VPN. The phone’s only link is mosh/SSH into the mini, never the laptop, scoped to that host alone. Prose never travels the store API; state never travels git.

How the mini gets fresh prose: promote (and any state command about a ticket) pushes the vault from the laptop first, then calls the service. The write appends the event (with the prose ref: path + content hash) in the same transaction; the drain’s sweep wakes on it, git pulls, and verifies the named hash is present before running (section 5.4). The signal channel is the store, the content channel is git; neither needs a webhook.

5.2 Write and event flow

        PROSE WRITE                              STATE WRITE
        ===========                              ===========

  planner edits tickets/<slug>.md      client calls the store API:
            |                          transition(slug, from:, to:)
            v                                        |
     git commit + push                               v
            |                          service: CAS row update
            v                             (lease honored)
     git remote (offsite)                 [same transaction]
            |                                        |
            v                        event appended: actor, reason, ts,
     other hosts git pull                    prose ref = path + hash
                                                     |
                                                     v
                                        outbox row (same tx)
                                                     |
                     +-------------------------------+------------------------+
                     |                               |                        |
                     v                               v                        v
              drain (cursor sweep)            indexer (cursor sweep)  analytics shipper
              wake on ready-event             git pull; read .md      mirror events
              claim via CAS + lease           at event's hash         for dashboards
              run the implement               re-embed changed
              pipeline                        sections into
                     |                        sqlite-vec (same file)
                     v
              transition(...)
              [loops back to STATE WRITE]

Each of the three consumers sweeps forward from its own cursor row on wake and on a periodic tick; an in-process nudge after each write is a latency optimization, the outbox plus cursor is the delivery contract.

5.3 Read paths

  agent needs the spec                  agent needs "similar open backend tickets"
            |                                        |
            v                                        v
  grep / read local tickets/*.md        one store-API call, one SQL query inside:
  zero latency, batchable,              vector match JOIN state
  works offline                           WHERE repo='BE' AND status='todo'
                                        filters read live columns and can
                                        never be stale; add a lexical term
                                        for hybrid when needed

5.4 Isn’t pull-based sync a burden?

Git is pull-based and no webhooks are wired anywhere, so it is fair to ask who pulls, and when. The answer: the outbox event is the webhook, delivered through the store. Every consumer’s wake cycle is sweep cursor, see event, git pull, act: the drain pulls before implementing, the indexer pulls on wake, so no agent ever acts on prose staler than the event it is processing. Prose-only edits with no state event are caught by the periodic reconcile sweep, and a git fetch every minute is free at this corpus size. An inbound webhook from the git host would be the complex option here: it needs a public endpoint into the mesh VPN, to save sub-minute latency nobody is waiting on.

The continuous-sync alternative (a peer-to-peer file-sync tool like Syncthing on the working tree) loses on a sharper point than convenience: the reconciliation mechanism requires content addressing. Events carry path + hash, consumers read the file as it was at that hash, and store/file disagreement is mechanically detectable and fails loud (section 7). A file-sync tool has no “the file as of event N”, only “whatever the file is now”; its conflicts land as silent .sync-conflict-* siblings a grepping agent will never notice, where git conflicts block loudly at push and force a resolution; and it would ship every mid-edit and mid-rebase intermediate state to every host in real time. The cost accepted in exchange: prose freshness on a host is only as good as its wake cadence, and a host that is not acting may be minutes stale, because a host that is acting always pulls first.

6. Flows worth walking

1. Ticket authored. The planner (human or agent) writes tickets/<slug>.md locally, then registers it through the store API: a row is created from the file’s frontmatter (registration is a pure projection of prose), the registration event carries the prose ref, and the indexer embeds on that event. Creation itself is prose-only and works offline; registration follows at the next moment the service is reachable, and an idempotent reconcile (register any ticket file without a row) backstops anything missed.
2. Prose edited after registration. Push updates the file. The indexer picks the change up on its next wake: any event naming the slug, or its periodic reconcile sweep, which compares working-tree section hashes against the chunk keys. If the indexer’s clone has not yet pulled the commit an event’s hash names, it leaves its cursor in place and retries on the next sweep: late sync delays embedding, never loses it. The wait is bounded: after N sweeps without the named hash (the writer amended or never pushed that exact content), the indexer indexes the current working-tree state, records the divergence, and advances its cursor, so one unreachable hash can never head-of-line block every later event. A dedicated prose_changed post-push event is an optional latency optimization, not load-bearing.
3. Drain wake. Promote emits an event; drain workers sweep the outbox and race to claim; CAS + lease through the service guarantees exactly one winner, whichever hosts the workers run on; a crashed worker’s lease expires and the ticket is reclaimable. Reclaim is not a bare re-run: the reclaiming worker first establishes ground truth about the crashed run’s leftovers (branch, worktree, possibly an open PR) using the same verification checks the implement pipeline already runs, and recovers context from the dead session’s transcript rather than starting blind. This retires the current host-local, global lock file, and is what makes concurrent drain workers safe across hosts.
4. Recovery. The store restores from backup snapshots; state is additionally rebuildable by folding the event log; vector chunks are derived and rebuild by re-embedding (hash-keyed, idempotent). Prose restores from the git remote. The planes reconcile by prose refs: every event names the path + hash it acted on.
5. Phone session. Moshi (mosh) over the mesh VPN to the mini, attach to herdr (the subject of a forthcoming paper in this series), and watch or steer the fleet. The phone operates the host, never the store or the git remote directly.

7. Failure modes and mitigations

8. Rejected alternatives

Recorded so they are not re-litigated. Fuller arguments live in internal decision records; the shape of each rejection is reproduced here.

• Postgres + pgvector as the substrate (v1 of this paper): the one engine that could genuinely hold both workloads, but it put a full store migration in front of everything else. The store service gets the same consolidation (state + vectors in one transactional file) without migrating anything: the existing SQLite file and library carry over.
• A dedicated vector database for the ticket corpus (an interim revision of this paper): the service is a cost of the state plane and gets built regardless, so a separate vector store for tickets means a second stateful system in the ticket path plus the machinery to keep two systems honest (a payload mirror, a staleness class, verify-on-read), which exists only because of the split. Cancelled unbuilt; the vector database keeps the corpora with no state join, and stays the named escalation if the ticket corpus ever outgrows brute-force search.
• ssh-wrapped CLI + registration deferred to git pulls (another interim revision, briefly): host-boundary workarounds that re-implement a service one seam at a time. Recognized as the networked-store trigger firing in slow motion, and replaced by firing it deliberately.
• Kafka (or any broker) for events: unused value at one producer and ~3 consumers; operational weight without a workload. Outbox + cursor sweeps cover the need; NATS JetStream is the named escalation if consumers ever multiply beyond a cursor table’s comfort.
• Everything into the store (no local files): forfeits grep/read economics, offline operation, Obsidian, and our fixture-testing discipline, to gain nothing the split does not already provide. The network hop was never the main cost; primitive loss is.
• Everything in files, state included: files cannot do CAS, leases, or multi-writer state, and dual file/store authority for state is the mechanism that made the July incident possible. State already left frontmatter in an earlier cutover; going back is strictly worse.
• A vector database as the PRIMARY store: payload filters are not transactions; state and audit need ACID and an append-only log. Walked concretely: claims lose atomic CAS (two workers both win), transition + event lose shared transactions, and there is no ordered log to replay.
• An analytics/columnar database as primary store: no transactional CAS; wrong tool for contended row updates. It keeps the analytics-mirror role.
• Peer-to-peer file sync for the vault repo: double-syncing a git working tree that agents write to invites conflict soup, and continuous file sync cannot pin content to events the way hash-addressed commits do (section 5.4). Git is the vault’s sync; that tool’s lane is non-git corpora and phone files.
• A second document service for prose: prose already has a substrate.

9. What actually remains to be built

The point of the paper is how short this list is.

1. The store service: a thin Rack wrapper around the existing store library, on the mini, VPN-only bind, token auth. The service process is the single toucher of the SQLite file.
2. sqlite-vec inside it: chunk schema keyed (slug, section, section_hash), the embed pipeline, and a search(query, filters) endpoint that joins vectors against live state columns.
3. Client mode for the store CLI: endpoint via env var; localhost on the mini, VPN elsewhere; same verbs as today.
4. The indexer: a script, not a service. Sweeps its cursor, pulls its clone, chunks changed files by the ticket spec’s required sections, embeds, upserts. Idempotent by chunk key.
5. Prose refs on events: path + file hash stamped at registration and refreshed at claim; plus the outbox cursor rows for the three consumers.
6. Migrate the one done-date read from ticket-file git history to the event log, closing the single audit-trail use of git the sweep found (section 3).

Explicitly not in scope: any change to how prose is written, reviewed, promoted, or read; any new message broker; any engine migration; any move of tickets/*.md out of git.

10. Open questions

• Embedding model and refresh policy for the ticket corpus (a hosted embedding API vs a local model is undecided; the engine seam is decided, the model is not).
• The board view is probably dead: a kanban view regenerates on every store write, but the operator stopped using it: at 1,600+ tickets a board is a wall, not a view. The service cutover is the natural moment to retire the regeneration rather than port it. (An honest lesson for agent-native tooling: views built for human project management are the first thing to fall out of use once agents do the tracking.)
• sqlite-vec maturity watch: pre-1.0, brute-force only. Bounded by the service seam (a swap to a dedicated vector database is one contained rewrite), but worth a periodic check.
• Multi-writer prose: if concurrent drains ever need to edit the same ticket file outside PR flow, the git-conflict-as-signal stance needs revisiting. No current workflow does this.
• Phone-side prose access: a read-only vault on the phone (Obsidian plus a sync path) is attractive but unscoped; it must not become a third writer.

This is a paper in the Yianna & Claude Whitepapers series: working papers on running a production side project at the output of a team, with a fleet of AI agents, alongside a full-time engineering job, written from the system’s own measurements and incident reports. Evidence citations reference our internal decision records and postmortems; where a claim could not be traced to a measurement, the text says so.