ADR-023: Read-only query tool: structured aggregation, no raw SQL

Date: 2026-06-07 Status: Accepted Author: Rômulo Febasi

Context

SYN-183 asks for a read-only query MCP tool so an agent can compute out of context (e.g. “how many tasks are blocked?”, “count projects by status”, “which tags are most used?”) without pulling every entity into the model’s context window. The ticket gates implementation behind a mandatory security spike (this ADR).

The obvious shape (“let the agent send SQL, run it read-only”) is unsafe even though the data is the user’s own and local:

• Exfiltration of internal tables. The index holds more than entities: pending (un-accepted AI proposals + their context_snapshot), the audit-adjacent rows, embeddings_meta, and (via ATTACH) the global workspaces.db registry of every workspace on the machine, including confidential, non-federated ones (BR-12.4). A raw SELECT * FROM pending or an ATTACH lets an agent read data no read tool exposes, defeating the federation redaction boundary.
• Injection. String-built SQL from model output is the canonical injection vector; a single quote in a “filter value” rewrites the query.
• Denial of service. Cartesian joins, recursive CTEs, or load_extension can hang or crash the process, or worse.
• Read-only is not enough. Opening the connection read-only stops writes but not exfiltration, ATTACH, pragma, or expensive scans.

A SQL allow-list parser (single SELECT, one sanctioned view, no ATTACH/pragma/CTE/subquery) was considered and rejected: it is a large, fragile, security-critical parser to maintain, and SQLite’s surface makes “prove this SELECT is safe” a losing game.

Decision

The query tool takes no SQL. It exposes a structured aggregation over a hard-coded allow-list:

• group_by: exactly one of type | status | owner | priority | due | tag. The value maps to a hard-coded column identifier: never a string from the request, so no identifier injection is possible.
• filter (optional): equality on type | status | owner | priority | tag. Every filter value is a bound SQL parameter, never interpolated.
• metric: count only (v0.7).
• The query only ever reads entities and tags. It cannot name pending, the audit tables, embeddings_*, or ATTACH the registry.
• limit is an integer clamped to [1, 1000].

So the entire SQL string is assembled from constants; the only request- derived data are bound parameter values. Injection, internal-table exfiltration, registry reach, and ATTACH/pragma/CTE are impossible by construction, not by validation.

Consequences

• Improves: a genuinely useful out-of-context aggregation (counts by type/status/owner/priority/due/tag, optionally filtered) with zero new attack surface. Stays inside the same entities/tags surface the existing read tools already expose: no new data is reachable.
• Limits: no arbitrary analytics: only the whitelisted group/filter dimensions and count. Joins across entities, free-form expressions, and other metrics (sum/avg over a numeric field) are out of scope until a concrete need justifies extending the allow-list (each addition is a reviewed column, not a new SQL capability).
• Open: if richer analytics are ever needed, extend the allow-list (new group/filter columns, new bounded metrics): never open a raw-SQL path. Revisit only with a concrete use case.

Alternatives considered

• Raw SQL, read-only connection. Rejected: exfiltration of internal tables + ATTACH of the registry + DoS, none of which read-only stops.
• Raw SQL against a sanctioned view + an allow-list SQL parser. Rejected: a large, fragile, security-critical parser; SQLite’s surface makes a sound allow-list impractical to maintain.
• No query tool; tell agents to page entities and count themselves. Rejected: that is exactly the out-of-context cost SYN-183 removes, and it scales badly on large workspaces.