ADR-0046: Read-only SQL tool surface over curated views

Status: Accepted — ai-engineer review APPROVE-WITH-CHANGES with all
blocking items folded into the design
(docs/superpowers/reviews/2026-08-06-ai-engineer-query-sql-surface.md);
ceo-blair sign-off APPROVE-WITH-CONDITIONS, six conditions, condition 1
(live-Postgres adversarial isolation corpus) gating customer exposure
(docs/superpowers/reviews/2026-08-06-ceo-blair-query-sql-signoff.md).
Seth’s countersignature as accountable exec is given on the PR that lands
this record.
Date: 2026-08-06
Deciders: Ronaldo Reymundo (design), Seth Shoultes (scope call on
caseproof/innovations#183)
Supersedes: the “Free-form SQL — too risky, no LLM-driven WHERE clauses
on member tables” ruling recorded in caseproof/memberintel#83’s spec.
Design: docs/superpowers/specs/2026-08-06-query-sql-power-questions-design.md

Context

caseproof/memberintel#83 shipped query_site_data on the explicit ruling
that free-form SQL was too risky. That ruling was correct for what it
compared: raw LLM-written SQL against production tables, with nothing
between the model and the data but hope. The enum tool plus #87’s filters
now cover ~90% of chat questions.

The remaining ~10% — cross-site set operations, unshaped aggregations,
multi-entity JOINs, state changes over time — don’t fit any bounded
parameter surface, and growing the target enum per question shape is the
dedicated-tool-per-question anti-pattern (caseproof/innovations#183).

Two scope calls are on the record in #183:

1. Seth (2026-08-05): this targets our own Postgres copy of the synced
   data. Live MySQL queries against customers’ WordPress sites are out.
2. From the Slack thread: not a launch blocker; valuable for single-site
   users too, not just portfolio; must be available to the advisor (i.e.
   registered in tool_loop.py’s single tool registry, which both chat
   paths share).

Decision

Add a query_sql chat tool: a single LLM-written SELECT (max 4,000
chars), executed against a dedicated Postgres schema of curated,
per-user-scoped views (advisor_sql.{sites, members, memberships, subscriptions, transactions, site_stats}), under a NOLOGIN read-only role
entered via SET LOCAL ROLE in a rollback-only transaction, run in a
threadpool on its own short-lived session, with sqlglot single-SELECT +
function-allowlist validation, a 3s statement timeout, a 100-row / 8 KB
result cap, at most 2 dispatches per turn, and audit via the existing
message_tool_results persistence plus a structured log event. Pro-only,
enforced at tool-list assembly (Free turns never carry the tool), with a
dispatcher check as defence-in-depth.

The load-bearing change from #183’s own proposal: tenant isolation lives in
the view definitions (WHERE user_id = current_setting('app.sql_tool_user_id')::uuid, security_barrier), not in
an AST rewrite of the model’s SQL. Rewriting arbitrary SQL is the
highest-risk component of the original sketch; a view predicate is
engine-enforced and has no per-query moving parts. The curated columns also
close a gap the raw-table allowlist missed: sites.mp_api_key_enc (the
encrypted connector key) and install telemetry never enter the visible
schema at all.

Where isolation actually rests (per the ai-engineer review): on the view
predicate plus revoking EXECUTE on pg_catalog.set_config from
PUBLIC — without the revoke, a validated single SELECT can rewrite the
scoping GUC from inside a materialized CTE before the predicate evaluates
(review §2.1), a cross-tenant read. Both halves are engine-enforced. The
sqlglot validator (statement shape, table allowlist, function allowlist) is
defence-in-depth and model UX, not the isolation boundary.

What makes it defensible to walk back #83’s ruling is that the risk envelope
changed, not the appetite: the model’s SQL runs against objects that cannot
express another tenant’s rows, a secret column, or a write — enforced by the
database engine, twice over (view predicate + role grants + the set_config
revoke), before any Python code is trusted.

Consequences

Positive:

• The ~10% power-question tail gets answered instead of punted, without a
  new enum target per question shape.
• Isolation guarantees are testable in isolation (seed two tenants, assert
  zero cross-tenant rows regardless of the SQL) rather than
  per-parser-edge-case; the design’s adversarial corpus runs against live
  Postgres for exactly this reason.
• The audit trail requires no new table: raw SQL and results already persist
  under ADR-0023’s tool-call snapshots (the 4,000-char SQL cap exists partly
  to guarantee that record can never be truncated away).

Negative / costs:

• A new dependency (sqlglot), a schema+role migration, and one cluster-wide
  catalog-grant change (set_config revoked from PUBLIC; every other role
  that legitimately calls it needs an explicit grant-back — documented in
  the migration and runbook).
• View maintenance has an owner and a cadence, not just an
  acknowledgement: every migration that touches the six underlying tables
  must include an explicit in/out decision for the view (default: OUT), and
  the PR checklist for sync-path schema changes gains that line. Semantics
  the shaped tools encode in prose (opaque gateway ids, status filtering,
  the 4-column membership join) are pre-resolved in the views
  (gateway_display, membership_title) rather than re-learned by the
  model per conversation.
• Standing token cost: the honest schema-carrying tool description is ~552
  tokens, +19.5% on the tool block, paid on 100% of Pro turns for a tool
  that answers ~10% of questions — ~$0.50/user/month at realistic volume
  (measured in the review, §3). Affordable inside the $6–12 Pro envelope;
  prompt caching (unbuilt today) would make it a rounding error and is
  tracked as adjacent work.
• The SET LOCAL safety argument holds on today’s SQLAlchemy QueuePool
  and under PgBouncer transaction pooling (ADR-0040’s escalation path);
  it breaks under statement pooling. Recorded so nobody re-derives it
  during an incident.

Risks accepted:

• Prompt-injected SQL (hostile strings in synced member data steering the
  model) is bounded to reading the same user’s own data — no write path, no
  cross-tenant reach, no secrets. Detection is post-hoc via the audit trail
  and a validation-rejection alert, not inline.
• The Pro-only gate is what makes the free tier’s 30-day history window
  (#684) a non-issue. Opening this tool to Free later has two
  prerequisites: window-aware view predicates AND extending the #684
  history-window honesty eval to this tool — recorded here so the second
  half isn’t skipped.

Alternatives considered

• Keep growing query_site_data targets/filters — the anti-pattern this
  issue exists to stop; each power question becomes a bespoke tool change.
• AST-rewrite scoping (the issue’s original sketch) — puts tenant
  isolation inside a Python SQL-rewriter that must be correct for every
  construct sqlglot can parse. Rejected in favor of view predicates.
• Row-level security policies on the base tables — equivalent guarantees,
  but policies on public.* affect every app query path, not just this
  tool, and don’t solve the secret-column problem; a separate schema scopes
  the blast radius of a policy mistake to the tool.
• Temp-table scope instead of a GUC (review §2.1 alternative) — a
  per-query _scope temp table the restricted role can read but not forge.
  Closes the same hole without a catalog-grant change, at the cost of
  per-query DDL. The revoke was chosen; this is the fallback if the
  cluster-wide grant change proves operationally unacceptable.
• Live WP/MySQL query endpoint in the Connect plugin — agent-written SQL
  on customers’ production databases; ruled out by Seth on #183.
• Do nothing until more demand signal — the issue’s own original stance;
  overtaken by the Slack decision to pick it up now (single-site value +
  advisor availability).