A clever memory pattern in Copilot’s harness

My coding agent was writing SQL about its own work.

I first noticed it in GitHub Copilot Desktop: the assistant was issuing INSERT INTO todos (id, title, description, status). The model called the tool; the harness provided the SQL task-state interface. But an INSERT result could not tell me whether those tasks would still be readable after the conversation changed or the runtime stopped.

So I used the Copilot SDK to run a controlled CLI-runtime session with two fictional tasks. I supplied the exact SQL. This was not a test of Copilot Desktop or whether an agent would choose SQL on its own.

Give the plan two rows

beta depended on alpha. Both started pending. The first prompt asked the model to execute one native SQL call:

INSERT INTO todos (id, title, description, status) VALUES
  ('alpha', 'Fictional alpha', 'Synthetic alpha task', 'pending'),
  ('beta', 'Fictional beta', 'Synthetic beta task', 'pending');

INSERT INTO todo_deps (todo_id, depends_on) VALUES ('beta', 'alpha');

SELECT t.id
FROM todos t
WHERE t.status = 'pending'
  AND NOT EXISTS (
    SELECT 1
    FROM todo_deps d
    JOIN todos p ON p.id = d.depends_on
    WHERE d.todo_id = t.id AND p.status != 'done'
  )
ORDER BY t.id;

The tool reported two task rows, one dependency, and one ready task: alpha.

The last query gives "what should I do next?" a precise meaning: a pending task with no unfinished prerequisite. For this fixture, I did not need another request to summarize the plan.

The second prompt changed one field, then repeated that query:

UPDATE todos SET status = 'done' WHERE id = 'alpha';

Now it returned beta. The dependency had not changed. Its prerequisite was done.

That is the loop: INSERT a plan, UPDATE progress, SELECT what is unblocked. The work is still the agent's. The current state no longer has to live only in a paragraph about its progress.

Read the state, not the answer

The model had just seen both task names and the query. An answer saying "beta is ready" would not tell me where that answer came from.

I checked two separate things. Each native SQL start had to join a successful completion by toolCallId. Then code outside the model's answer read the tasks through the runtime:

await session.rpc.plan.readSqlTodosWithDependencies();

The controller checked the returned task fields and dependency against the fixture. It did not treat assistant prose as a receipt or replay captured SQL into another database.

An official SDK test exercises this SQL-and-readback interface too. I read that test; I did not run the upstream suite. My executed run and the public API evidence are separate.

Compact, restart, then read before prompting

After marking alpha done, I called the compaction RPC:

await session.rpc.history.compact({ trigger: 'manual' });

This was not /compact sent as chat text or a context window filled with padding. The runtime reported success. The immediate structured readback was unchanged, and a subsequent native SELECT still returned beta as ready.

Then I called client.stop(), created a new SDK-managed stdio runtime, and resumed the same synthetic session. The pinned SDK implementation requests shutdown and waits for its owned child to exit. Both stops returned zero errors. This tested a graceful runtime restart, not a crash.

Before sending another prompt, I called the structured read API again.

Readback #65 returned the updated tasks before prompt #66, the fourth programmed prompt. alpha was done. beta was pending. The dependency was intact. No new model answer or replayed INSERT was needed to make those rows readable.

The result across the tested boundaries was:

The five post-update readbacks matched on the retained task fields and dependency. That establishes equality of the projected state, not the physical mechanism that stored or restored it.

Inspect or reproduce the run

The study bundle contains the exact prompts, controller, pinned dependencies, eight offline tests, and sanitized receipts. The methods note covers runtime/model identities, event counts, the earlier zero-prompt launch failure, and the limits of the read API.

Inspect the report, events, pre-prompt readback, checksums, or source ledger. The bundle preserves the original pre-publication snapshot, including its historical titles and wording. The diagrams are separate editorial SVGs with reusable briefs.

To check the retained run without making a model call, start in a fresh directory:

curl -fLO https://siddhantkhare.com/blog/why-my-coding-agent-runs-insert-statements/copilot-sql-state-study-c3ead8d.tar
curl -fLO https://siddhantkhare.com/blog/why-my-coding-agent-runs-insert-statements/SHA256SUMS
shasum -a 256 -c SHA256SUMS
tar -xf copilot-sql-state-study-c3ead8d.tar
cd copilot-sql-state-study
npm --prefix projects/copilot-sql-state test

The offline tests check guards, redaction, hashes, matched tool calls, readback ordering, and temporary paths. They need Node, not an SDK install or Copilot connection.

For a new online reproduction, use an already authenticated Copilot account and a supported Node runtime. It can consume your existing Copilot allowance and may select a different model:

npm --prefix projects/copilot-sql-state ci --ignore-scripts --no-audit --no-fund
npm --prefix projects/copilot-sql-state test
PRIVATE_PARENT="$(mktemp -d)"
PRIVATE_PARENT="$(cd "$PRIVATE_PARENT" && pwd -P)"
npm --prefix projects/copilot-sql-state run probe -- \
  --private-parent "$PRIVATE_PARENT" \
  --out evidence/NEW_RUN

Choose a fresh output name each time. Resolving PRIVATE_PARENT to its physical path handles macOS /var/folders aliases without weakening the runner's guard. Do not upload the generated private runtime directory; only the allowlisted synthetic receipts are meant for sharing.

What this does not establish

Readable task state is not proof that rows enter the model's prompt automatically. Nor would a model recalling a task prove SQL caused that recall: the prompt, tool result, transcript, or compaction summary could supply it. I did not test crash durability, identify the physical store, or measure planning quality, token use, or cost savings.

This is agent-managed working state, not token accounting, cross-session history, repository Memory, or provider prompt caching. GitHub's CLI context and session-data docs describe related surfaces; they do not establish Desktop parity.

Build for observable recovery

The useful distinction is between the conversation about work and the current state of that work. A transcript can explain why a task changed. A queryable plan can tell you which task is done and which prerequisite still blocks the next one.

For an agent builder, I would make those reads observable alongside the writes. Match tool starts to completions. Check the returned fields against the state you expect. Then test the boundary that matters: after compaction or restart, read the plan before giving the model another turn.

A convincing answer can come from the conversation. Read the rows before you ask the model to explain them.

I also build StateSearch, a related Go prototype that reconstructs issue state from mock meeting transcripts and queries open items.

I write about agent internals and queryable state. If you're working on similar problems, connect on LinkedIn, follow me on X, or get in touch.