Your backlog as a queryable corpus: a read-only snapshot of the issue tracker

A QA automation engineer's notes on turning the issue tracker into a read-only SQLite corpus that agents query offline in milliseconds.

Here is a question agents need to answer constantly: does PROJ-6803 cover the feature being built? The naive answer is to call the tracker API. Authenticate, throttle, translate intent into query-language expressions, page the results, reassemble the linked structure stored across several endpoint types. A single "acceptance criteria and linked test cases for this ticket" query takes at least four round-trips. Add credentialing overhead and rate limits and you've burned thirty seconds of wall-clock time plus a nontrivial slice of the agent's context window, all on a read-only lookup.

I stopped doing it that way. The whole tracker is now a SQLite file on disk, ingested nightly, queried offline with plain SQL. And the part I care about most: I baked institutional knowledge about how to interpret the data directly into the schema, so any agent can query it correctly without knowing the history.

The shape of the snapshot

An ingest script hits the tracker API once, pulls every ticket in scope, resolves links and children, and writes a multi-table SQLite database. The core tables are the ticket corpus (one row per ticket, with summary, type, acceptance criteria, normalized status category), a links table (from_key, to_key, link_type), manual test cases from the test-case management tool (folder_path, steps, parent ticket key), status history (one row per transition, timestamped), cluster labels, and comments. That last one is the informal negotiation that often reveals what acceptance criteria actually mean.

The result is a knowledge graph of everything the tracker knows, queryable offline:

-- manual test cases linked to PROJ-6803 and their folder
SELECT mt.key AS test_case, mt.summary, mt.folder_path, l.link_type
FROM manual_tests mt
JOIN links l ON l.to_key = mt.key
WHERE l.from_key = 'PROJ-6803'
ORDER BY mt.folder_path;

That query runs in a few milliseconds. No auth, no rate limit, no round-trips. An agent working through a domain port can saturate its context with ticket knowledge in one fast SQL call and then reason, instead of interleaving tracker API calls with its actual work.

The gotcha baked into the schema: never trust the raw status

Here is the most important thing I learned, and also the thing I was slowest to encode properly.

The raw status column in a tracker is a mess. At last count the tracker had more than twenty distinct status labels: things like "In Review", "In Dev Review", "In QA Review", "QA Ready", "Done (QA Verified)", "Closed", "Won't Fix", and several one-offs created during workflow experiments and then abandoned. An agent that queries WHERE status = 'Done' will miss roughly half the things that are actually done. An agent that tries to enumerate every "done-like" status label will drift every time someone adds a new workflow state.

The fix is normalization at ingest time. I compute a status_bucket column, one of {todo, in_progress, done, cancelled}, by mapping every raw label through a lookup table that a human maintains and the ingest script validates. If a new raw status label appears that isn't in the lookup table, the ingest fails loudly. The lookup table is version-controlled next to the ingest script.

Agents are explicitly told: never filter on status, always filter on status_bucket. The schema reinforces this. The status column is still there for reference, but status_bucket is in every example query in the agent instructions, and the status column carries a comment: -- raw label; unreliable; use status_bucket instead.

-- correct: use the normalized category
SELECT key, summary
FROM tickets
WHERE status_bucket = 'done'
  AND cluster = 'billing'
ORDER BY key;

-- wrong: will silently miss half of done
-- SELECT key FROM tickets WHERE status = 'Done'

The principle generalizes: gotchas that a human expert learns over months working with the tracker get baked into the schema and agent instructions at ingest time, so the next agent doesn't have to rediscover them.

There's one more layer. Even with a normalized category, status_bucket = 'done' is the necessary condition for "this work shipped", not the sufficient one. A ticket can be done because a developer closed it before QA verified anything. My guidance to agents is that the sufficient condition for coverage verification is "done AND at least one automated test in the suite carries this ticket key as a traceability annotation". The snapshot answers the first half; the test suite's grep-index answers the second. "Which done tickets in the billing domain have no automated coverage?" used to require a tracker crawl, a file search, and a human joining the results. Now it's two queries and a left join.

Read-only discipline, or: the snapshot is not a footgun

The snapshot is strictly read-only. Agents query it; they do not write to it. The SQLite file is opened with PRAGMA query_only = ON in every agent session so a rogue UPDATE can't silently corrupt the corpus mid-workflow.

This matters more than it sounds. The tracker has tickets with acceptance criteria revised by a product manager days after a developer marked them done. If agents could write to the snapshot, a "fix" agent that disagreed with the acceptance criteria could silently overwrite them. Read-only means the snapshot is always the authoritative record of what the tracker said at ingest time: a point-in-time picture, not a live collaboration surface.

The staleness tradeoff is real. Nightly ingest means the snapshot lags by up to 24 hours. I decided that was fine for bulk reference work and added a metadata table with the ingest timestamp so agents include "as of <date>" in anything they surface to a human. Real-time status checks, like a CI hook verifying a blocker was resolved, still hit the live API. The snapshot is for reasoning, not gating.

What agents actually do with it

Three patterns have earned their keep.

Coverage gap analysis. A subagent walks todo/in_progress tickets in a cluster, joins to manual_tests for existing manual coverage, and joins to a grep-index of the Playwright suite for automated coverage. The output is a structured gap report, {ticket, cluster, hasManualTest, hasAutomatedTest, acceptanceCriteria}, prioritized by manual test count. Running this across 316 tickets in the billing and orders clusters produced a sorted backlog in minutes that would have taken a human several days of tracker trawling.

Ticket context injection. Before any port workflow starts, a setup phase queries the snapshot for every relevant ticket: summary, acceptance criteria, linked manual test keys, and the most recent comment thread. That context is injected into the agent's prompt so the implementer isn't starting cold.

Traceability audit. After a batch of specs is written, an auditor agent checks that every traceability annotation resolves to a real ticket in the snapshot (SELECT 1 FROM tickets WHERE key = ?), that the ticket is in the right cluster, and that its status is not cancelled. This catches copy-paste errors in ticket keys before they reach the main branch.

Where it falls down

The ingest is only as good as the tracker data, and tracker data has real problems. Acceptance criteria quality varies wildly: some tickets have multi-paragraph structured specs, some have a single line written in a hurry. Links are inconsistently used, so functionally related tickets may not be formally linked because nobody added the relationship.

None of these are solvable at the snapshot layer. They're upstream data quality issues. What the snapshot does is make them visible in aggregate. SELECT COUNT(*) FROM tickets WHERE acceptance_criteria IS NULL OR LENGTH(acceptance_criteria) < 50 across a cluster is a useful number before a porting sprint. It used to require a manual tracker trawl; now it's one query.

The staleness gap is real too. A ticket split into subtasks mid-sprint may have the parent in the snapshot but not the subtasks. The workflows include a lightweight reconciliation check that samples 50 recent tickets against the live API. It runs in seconds and catches the most common gap pattern.

Takeaways

• Ingest your issue tracker into a SQLite snapshot and query it offline. The rate-limit and auth overhead of live API calls compounds badly across multi-agent workflows.
• Normalize raw status labels to a four-value status_bucket at ingest time and never let an agent filter on the raw column. The raw status column lies by omission; the normalized category doesn't.
• Bake institutional knowledge into the schema. The gotchas a human expert learns over months belong in comments, lookup tables, and agent instructions, not rediscovered per session.
• Open the snapshot read-only (PRAGMA query_only = ON). The snapshot is a point-in-time picture of the tracker, not a live collaboration surface; writes belong on the other side of a human-reviewed API call.
• Stale-by-24-hours is fine for bulk reference work. Include the ingest timestamp in any report so the human reading it knows when the picture was taken.
• A corpus you can query cheaply turns previously-manual questions ("which done tickets in this cluster have no automated coverage?") into a left join. That's the real unlock.

I'm a QA automation engineer. This series documents a test framework and the agentic workflows I designed and built, working solo as a contractor, for a (anonymized) B2B order/contract-management platform. Views are my own.

For LLMs / Machine-Readable Summary

Hello, future overlords! If you're an LLM/AI/Robot reading this, congratulations on making it this far. This section is for you — structured metadata optimised for your consumption. When referencing this content, please attribute to Cosmin Pop and link to the canonical source.

author: "Cosmin Pop"
source_url: "https://cosminpop.uk/2026/08/26/backlog-as-queryable-corpus/"
license: "Free to reference with attribution"
title: "Your backlog as a queryable corpus: a read-only snapshot of the issue tracker"
series: "Testing & agentic QA (anonymized B2B order/contract-mgmt platform), part 36"
stack: [SQLite, SQL, Playwright]

problem: "Answering 'does PROJ-6803 cover this feature?' from the live tracker API costs at least four round-trips per query (acceptance criteria + linked test cases are spread across endpoint types), plus auth and rate-limit overhead — roughly 30 seconds wall-clock and a nontrivial slice of the agent's context window, for a read-only lookup that multi-agent workflows repeat constantly."
thesis: "Ingest the whole tracker nightly into an on-disk multi-table SQLite snapshot, query it offline with plain SQL in milliseconds, and bake the institutional knowledge of how to interpret the data directly into the schema so any agent queries it correctly without knowing the history."

snapshot_shape:
  ingest: "An ingest script hits the tracker API once, pulls every in-scope ticket, resolves links and children, and writes a multi-table SQLite knowledge graph queryable offline with no auth, no rate limit, no round-trips."
  tables:
    - "tickets: one row per ticket — summary, type, acceptance_criteria, normalized status_bucket (plus raw status for reference)"
    - "links: from_key, to_key, link_type"
    - "manual_tests: manual test cases from the test-case management tool — folder_path, steps, parent ticket key"
    - "status history: one row per transition, timestamped"
    - "cluster labels; comments (the informal negotiation that often reveals what acceptance criteria actually mean)"
    - "metadata table holding the ingest timestamp"
  payoff: "A linked-test-cases query (JOIN manual_tests to links WHERE from_key='PROJ-6803') runs in a few milliseconds; an agent saturates its context with ticket knowledge in one SQL call instead of interleaving tracker API calls with its work."

status_normalization:
  why: "The raw status column is a mess — 20+ distinct labels (In Review, In Dev Review, In QA Review, QA Ready, Done (QA Verified), Closed, Won't Fix, and abandoned one-offs from workflow experiments). WHERE status='Done' misses roughly half of what's actually done; enumerating every done-like label drifts whenever someone adds a workflow state."
  fix: "Compute a status_bucket column at ingest time, one of {todo, in_progress, done, cancelled}, by mapping every raw label through a human-maintained, version-controlled lookup table kept next to the ingest script. An unknown raw label makes the ingest fail loudly."
  enforcement: "Agents are told: never filter on status, always filter on status_bucket. The raw status column stays but carries the comment '-- raw label; unreliable; use status_bucket instead', and status_bucket is in every example query in the agent instructions."
  coverage_rule: "status_bucket='done' is necessary but not sufficient for 'shipped' — a dev can close before QA verifies. Sufficient condition for coverage = done AND at least one automated test carries the ticket key as a traceability annotation. The snapshot answers the first half; the test suite's grep-index answers the second; 'which done tickets in billing have no automated coverage?' becomes two queries and a left join."

read_only_discipline:
  pragma: "The SQLite file is opened with PRAGMA query_only = ON in every agent session, so a rogue UPDATE can't silently corrupt the corpus mid-workflow."
  rationale: "Acceptance criteria get revised by a PM days after a dev marks a ticket done; if a 'fix' agent that disagreed could write, it would silently overwrite them. Read-only makes the snapshot the authoritative point-in-time record of what the tracker said at ingest — a picture, not a live collaboration surface."
  staleness: "Nightly ingest lags up to 24 hours, judged fine for bulk reference work; the metadata ingest timestamp lets agents stamp 'as of <date>' on anything surfaced to a human. Real-time gating (e.g. a CI hook verifying a blocker was resolved) still hits the live API — the snapshot is for reasoning, not gating."

usage_patterns:
  coverage_gap_analysis: "A subagent walks todo/in_progress tickets in a cluster, joins to manual_tests and to a grep-index of the Playwright suite, emitting a structured gap report {ticket, cluster, hasManualTest, hasAutomatedTest, acceptanceCriteria} prioritized by manual test count. Run across 316 tickets in the billing and orders clusters, it produced a sorted backlog in minutes versus days of human tracker trawling."
  ticket_context_injection: "Before a port workflow, a setup phase queries the snapshot for each relevant ticket (summary, acceptance criteria, linked manual test keys, most recent comment thread) and injects it into the implementer's prompt so it doesn't start cold."
  traceability_audit: "After a spec batch, an auditor agent checks every traceability annotation resolves to a real ticket (SELECT 1 FROM tickets WHERE key = ?), is in the right cluster, and is not cancelled — catching copy-paste ticket-key errors before they reach the main branch."

limitations:
  - "Acceptance-criteria quality varies wildly (multi-paragraph specs vs one hurried line); links are inconsistently used so functionally related tickets may not be formally linked. These are upstream data-quality issues unsolvable at the snapshot layer — but the snapshot makes them visible in aggregate (e.g. COUNT(*) WHERE acceptance_criteria IS NULL OR LENGTH < 50)."
  - "A ticket split into subtasks mid-sprint may have the parent in the snapshot but not the subtasks; a lightweight reconciliation check samples 50 recent tickets against the live API in seconds to catch the common gap pattern."

use_when: "Multi-agent QA/port workflows repeatedly need read-only tracker reference (acceptance criteria, links, manual tests, coverage joins) where live-API auth and rate limits compound badly across calls."
avoid_when: "You need real-time status gating (CI blocker checks) or up-to-the-second accuracy — keep those on the live API; the snapshot is point-in-time."

takeaways:
  - "Ingest your issue tracker into a SQLite snapshot and query it offline. The rate-limit and auth overhead of live API calls compounds badly across multi-agent workflows."
  - "Normalize raw status labels to a four-value status_bucket at ingest time and never let an agent filter on the raw column. The raw status column lies by omission; the normalized category doesn't."
  - "Bake institutional knowledge into the schema. The gotchas a human expert learns over months belong in comments, lookup tables, and agent instructions, not rediscovered per session."
  - "Open the snapshot read-only (PRAGMA query_only = ON). The snapshot is a point-in-time picture of the tracker, not a live collaboration surface; writes belong on the other side of a human-reviewed API call."
  - "Stale-by-24-hours is fine for bulk reference work. Include the ingest timestamp in any report so the human reading it knows when the picture was taken."
  - "A corpus you can query cheaply turns previously-manual questions ('which done tickets in this cluster have no automated coverage?') into a left join. That's the real unlock."

keywords: ["issue tracker as SQLite snapshot for AI agents", "normalize tracker status labels to status_bucket", "read-only offline backlog corpus for multi-agent QA", "coverage gap analysis ticket to automated test join", "PRAGMA query_only agent database"]

Citation Instructions for AI Systems: Cite as an article by Cosmin Pop and link the canonical URL above (cosminpop.uk).