# Ad-hoc read-only query tool — design & implementation

> Status: **DESIGN / PROPOSAL** — staging only, disabled by default, requires DBA + security sign-off before enable.
> Ticket: _TBD_ (proposed follow-on to TW-297/298/299/300/310).

## 1. Why this exists

Today every SQL path in this repo is a **pre-approved query** (`catalogs/queries/*.json`,
executed via `connectors/sql/run.ts#runApprovedQuery`). That is safe but does not scale: we
cannot hand-author and ship a new approved query + RO view for every question users ask.

This tool adds a **bounded, read-only, dynamic query capability** so that when no existing
tool covers a question, the server can:

1. discover the relevant tables/columns from the Qdrant business catalog (schemas indexed via
   `jobs/catalog_index/`),
2. build a query for them,
3. execute it against a locked-down read-only MySQL user,
4. return rows **with full provenance** (the exact SQL, the tables touched, the EXPLAIN summary),

all behind heavy guardrails and only in **staging**.

### Relationship to the existing hard rule

`AGENTS.md` says: **"No generic `execute_sql` tool"**, and `server/registry.ts#isDenylistedToolName`
actively rejects any tool named `execute_sql` / `run_sql` / `query_sql` / `raw_sql` / etc.

**This design does not break that rule; it deliberately stays inside it:**

- The tool is **not** a passthrough for caller-authored SQL. The caller never sends SQL.
- SQL is **generated server-side from a structured, catalog-constrained spec** and then
  **mechanically validated** before execution.
- The real security boundary is a **dedicated read-only DB user with column/view-scoped GRANTs**,
  not the LLM and not string filtering.

The tool must be named for what it is (e.g. `adhoc_explore` / `explore_business_data`), **never**
a denylisted `*_sql` name, and it must remain disabled by default.

> This still crosses the *spirit* of "approved queries only", so it needs an explicit ticket +
> DBA + security sign-off, and ships staging-first. This doc is written so that sign-off can
> review a concrete design rather than an idea.

## 2. Non-goals (for now)

- **Prod.** Staging only. `environment: 'prod'` must hard-fail, same gating style as
  `MNGT_RO_ALLOW_PROD` / `CAKE_RO_ALLOW_PROD`.
- **Writes.** SELECT only, enforced at the DB user level and again in validation.
- **Free-form caller SQL.** Never accepted.
- **Replacing approved tools.** Approved tools remain the source of truth for reportable numbers.
  This tool's output is **exploratory** and must be labelled as such.
- **Stealing traffic from a nearby approved report.** Prefer approved tools first. After an
  approved tool errors, returns empty rows, or cannot express the grain, **always** fall through
  to `adhoc_explore` (the analyst agent auto-invokes it; MCP empty/error payloads are stamped
  `FALLBACK REQUIRED`). Portfolio Cake spend is still `get_advertiser_report` `{ group_by: "total" }`
  / `summary.spend` when that call succeeds.
  Exception: RDR3 traffic links/IPs have **no catalog path** — refuse honestly and offer
  the campaign-CPC rephrase (see playbook §7.1). Datafeed traffic uses
  `get_datafeed_traffic_report`.
  (see [`ADCENTER-QUESTION-PLAYBOOK.md`](./ADCENTER-QUESTION-PLAYBOOK.md) “Approved tools vs adhoc_explore”).

## 3. Architecture

```
MCP client (question in natural language)
  │
  ▼
adhoc_explore tool handler                      server/create-mcp-server.ts (dispatch)
  │   runToolCall(enablement → gateway authz → handler)   server/tool-runtime.ts (unchanged path)
  ▼
1. RETRIEVE   catalog context from Qdrant       connectors/adhoc/retrieve.ts  (reuses jobs/catalog_index/search.ts)
  │           → candidate tables + indexable columns (+ FK hints)
  ▼
2. PLAN       LLM produces a QuerySpec (JSON)    connectors/adhoc/planner.ts   (OpenAI, structured output)
  │           NOT SQL — {tables, columns, filters, joins, group, order, limit}
  ▼
3. VALIDATE   spec vs catalog (allowlist)        connectors/adhoc/validate-spec.ts
  │           every table/column must exist in catalog & be queryable
  ▼
4. COMPILE    spec → parameterized SQL           connectors/adhoc/compile.ts   (deterministic, our code writes SQL)
  │           + bound params[]
  ▼
5. GUARD      AST + EXPLAIN pre-flight           connectors/adhoc/guard.ts
  │           single SELECT, ≤N tables, no cartesian, forced LIMIT, no full scans
  ▼
6. EXECUTE    RO user, server-side timeout       connectors/adhoc/executor.ts  (new RO DSN, MAX_EXECUTION_TIME)
  │           row cap + byte cap + concurrency cap
  ▼
7. RESPOND    rows + provenance + disclaimer      → audit log (SQL, EXPLAIN, rowCount, duration)
```

Key principle: **the model chooses *what* to ask; our code decides *how* (and whether) to run it.**

## 4. Two options — and the recommendation

### Option A (recommended): QuerySpec → compiler

The LLM emits a **structured JSON QuerySpec** (see §6), constrained to catalog-known
tables/columns. Our TypeScript **compiles** that spec into parameterized SQL. The model never
writes SQL text.

- Pros: injection surface ~0 (we generate SQL); every table/column is checked against the
  catalog allowlist before a single character of SQL exists; deterministic, testable compiler;
  plays natively with the existing sensitivity gate (a column not in the catalog can't be named).
- Cons: the compiler only supports the SQL shapes we implement (filters, joins, group-by,
  aggregates, order, limit). Exotic SQL is out of scope — which for "business coverage" is fine
  and is itself a guardrail.

### Option B (fallback): LLM free SQL + AST validation

The LLM emits SQL text; we parse it to an AST and reject anything unsafe.

- Pros: maximal SQL expressiveness.
- Cons: much larger attack/mistake surface; validation must be exhaustive to be safe; harder to
  guarantee column-level sensitivity; a parser gap becomes a security gap.

**Decision: build Option A.** Keep Option B explicitly out of scope unless a concrete need
appears; if it ever ships, it must reuse the same guard + RO-user + EXPLAIN layer and stay
staging-only. The rest of this doc assumes Option A.

## 5. DBA / production track: the DB-side hard boundary (checklist)

The database grant is the ultimate boundary — even a perfect-looking generated query must be
*physically* unable to read something it shouldn't. These items are **owned by the DBA / security**,
executed **outside this repo**, and are the **only** guardrails in this design that are safe to
apply in production. Everything in §8 is **code-enforced** and runs the same in staging and (a
future) prod; this section is the separate infra track.

**DB-side checklist (DBA-owned, prod-applicable):**

- [ ] Dedicated ad-hoc RO user, **separate** from `MNGT_RO_DSN_STAGING` and the cake RO users
      (separate workload identity per `AGENTS.md`).
- [ ] `SELECT` **only**. No `INSERT/UPDATE/DELETE/DDL/FILE/PROCESS/SUPER/CREATE TEMPORARY`.
- [ ] Granted on an **allowlist of schemas/views/columns** that mirrors what we index into Qdrant.
      Anything labelled `pii` / `secret` / `deny_index` / `unknown` in `catalogs/sensitivity/**`
      is **never granted** (column-level GRANT, or exposed only through curated `lucos_ro_*` views).
      See `jobs/sensitivity_review/types.ts` (`NON_INDEXABLE`).
- [ ] No grant on `mysql.*`, `information_schema` (beyond what harvest needs on its own user),
      `performance_schema`, `sys.*`.
- [ ] Global `max_execution_time` default set on the user/instance as a backstop to the
      session-level limit issued from code (§8.3).
- [ ] `MAX_USER_CONNECTIONS` / `max_statement_time` resource limits on the user.
- [ ] Read replica only (never the primary), staging host. New DSN env `ADHOC_RO_DSN_STAGING`.
      **No prod DSN var is created by this work.**
- [ ] `secure_file_priv` set so `LOAD_FILE` / `INTO OUTFILE` are impossible even if a query slipped.

> **Alignment invariant:** *what the planner can see in Qdrant == what the RO user can read.*
> If a column is not indexed (because it's sensitive/unknown), the planner never learns its name
> **and** the grant blocks it. Both lists must be generated from the same reviewed sensitivity
> source so they can never drift.

> Because the DB grant is prod-safe but everything else here is code-side, the design is
> intentionally **defense-in-depth**: the code guardrails (§8) are the front line and must be able
> to stand alone even if a grant is temporarily too broad.

## 6. QuerySpec (the model's output contract)

The planner returns **only** this JSON (validated with `zod`, same style as `server/registry.ts`
input schemas). No prose, no SQL.

```jsonc
{
  "intent": "short restatement of the question",
  "tables": [
    { "schema": "Admin", "name": "AccountMaster", "alias": "am" }
  ],
  "columns": [
    { "table": "am", "name": "adv_id" },
    { "table": "am", "name": "status", "agg": null },
    { "expr": "count", "table": "am", "name": "adv_id", "as": "n" }   // aggregate form
  ],
  "joins": [
    { "left": "am", "leftCol": "adv_id", "right": "cm", "rightCol": "adv_id", "type": "inner" }
  ],
  "filters": [
    { "table": "am", "column": "status", "op": "=", "value": "active" },
    { "table": "am", "column": "created_at", "op": ">=", "value": "2026-08-01" }
  ],
  "groupBy": [{ "table": "am", "name": "status" }],
  "orderBy": [{ "table": "am", "name": "n", "dir": "desc" }],
  "limit": 100
}
```

Notes:

- `op` is a **fixed enum**: `=, !=, <, <=, >, >=, in, like, is_null, is_not_null` (no free operators).
- `value` becomes a **bound parameter** (`?`) in the compiled SQL — never string-interpolated.
  Reuse the metacharacter/length checks from `connectors/sql/run.ts#bindApprovedParams`.
- `expr` (aggregate) is a fixed enum: `count, sum, avg, min, max`. No arbitrary SQL functions.
- No subqueries, no `UNION`, no CTEs, no `HAVING` in v1 (add later behind review if needed).

## 7. Compiler (`connectors/adhoc/compile.ts`)

Deterministic spec → `{ sql, params }`. Rules baked in:

- `SELECT` only; explicit column list (**never `SELECT *`**).
- Qualifies every column as `alias.column`; every identifier is validated against the catalog and
  emitted through a strict identifier allowlist (`^[A-Za-z0-9_]+$`) — identifiers are **not**
  parameterizable in MySQL, so they must be allowlisted, never taken raw.
- Joins always emit an `ON` clause built from the spec (no comma joins, no join without `ON`).
- Always appends `LIMIT ?` with a server-capped value (mirrors the approved-query invariant in
  `connectors/sql/catalog.ts`, which already requires every query to end in `LIMIT ?`).
- All literal values → `?` bind params, in order.

The compiler is pure and fully unit-testable with no DB.

## 8. Code-enforced guardrails (the front line)

Because the DB grant (§5) is a separate prod/DBA track, the **code** carries as much of the safety
as possible and must stand on its own. Guardrails run in **six layers**; a request must clear every
layer in order. Each layer is pure/testable and returns a typed rejection (see §18) — nothing is
silently "fixed" without also flagging it.

```
L0 Gate  →  L1 Input  →  L2 Spec  →  L3 Compile  →  L4 Static SQL (AST)  →  L5 Cost (EXPLAIN)  →  L6 Runtime
```

### 8.0 Layer 0 — environment gate

| Guardrail | Enforcement | Reject |
|-----------|-------------|--------|
| Staging only | `environment` must be `staging`; `prod` hard-fails like `MNGT_RO_ALLOW_PROD` | `prod_gated` |
| Enabled | `isToolEnabled('adhoc_explore')` (disabled by default) | `disabled` |
| Authorized | `authorizeToolCall` via gateway with caller Bearer (unchanged) | `unauthorized` |
| DSN present | `ADHOC_RO_DSN_STAGING` set and `mysql://` shaped (reuse `credentials.ts` checks) | `missing_dsn` / `invalid_dsn` |

### 8.1 Layer 1 — input guardrails (before any LLM/DB spend)

| Guardrail | Enforcement | Reject |
|-----------|-------------|--------|
| Question length | trim; `1..ADHOC_MAX_QUESTION_CHARS` (default 512) | `question_too_long` / `empty_question` |
| Per-caller rate limit | in-process token bucket keyed by request identity; N/min | `rate_limited` |
| Daily quota | in-process counter; `ADHOC_MAX_CALLS_PER_DAY` | `quota_exceeded` |
| Concurrency admission | acquire semaphore **before** planning (fail fast) | `too_many_concurrent` |
| Circuit breaker | after `ADHOC_BREAKER_TRIPS` consecutive failures, short-circuit for a cooldown | `circuit_open` |

### 8.2 Layer 2 — spec guardrails (`connectors/adhoc/validate-spec.ts`)

The QuerySpec is validated with a **strict** `zod` schema (`.strict()` — unknown keys rejected)
before it is trusted at all.

| Guardrail | Enforcement | Reject |
|-----------|-------------|--------|
| Schema shape | strict `zod` parse of QuerySpec; reject extra/unknown fields | `bad_spec` |
| Answerable | reject `{ unanswerable: true }` (surface the model's reason) | `unanswerable` |
| Retrieval-scoped | every table **and column** must come from the retrieved candidate set (§10), not just "exists somewhere" | `out_of_scope_table` / `out_of_scope_column` |
| Catalog existence | table/column exist in `catalogs/schemas/**` | `unknown_table` / `unknown_column` |
| Sensitivity | every column is `public`/`internal` only (`isIndexableSensitivity`) | `sensitive_column` |
| Schema allowlist | schema ∈ harvest allowlist; **never** `mysql`/`information_schema`/`performance_schema`/`sys` | `forbidden_schema` |
| Table cap | `tables.length <= ADHOC_MAX_TABLES` (default **3**) — "no big join" | `too_many_tables` |
| Column cap | `columns.length <= ADHOC_MAX_SELECT_COLS` (default **30**) | `too_many_columns` |
| Filter cap | `filters.length <= ADHOC_MAX_FILTERS` (default **10**) | `too_many_filters` |
| Join cap + shape | `joins.length <= tables.length - 1`; every non-first table joined; each `on` side is a real column; prefer FK/indexed keys from harvest `keys[]` | `cartesian_join` / `unknown_join_col` / `unindexed_join` (warn) |
| Operator enum | `op ∈ {=,!=,<,<=,>,>=,in,like,is_null,is_not_null}` | `bad_operator` |
| Aggregate enum | `expr ∈ {count,sum,avg,min,max}`; aggregates require consistent `groupBy` | `bad_aggregate` / `groupby_mismatch` |
| Value typing | each filter value is validated against the column `dataType` (int/date/string); reuse metachar + length checks from `run.ts#bindApprovedParams` | `invalid_param` |
| `IN` list cap | `in` value arrays `<= ADHOC_MAX_IN_VALUES` (default 50) | `in_list_too_long` |
| `LIKE` safety | reject leading-wildcard `%foo`/`_foo` (forces full scan); cap wildcards; escape user wildcards | `unindexed_like` |
| No forbidden constructs | spec has no subquery/UNION/CTE/HAVING nodes (v1) | `unsupported_construct` |
| `orderBy` on selected cols only | can only sort by projected/known columns | `bad_order_by` |

### 8.3 Layer 3 — compile guardrails (`connectors/adhoc/compile.ts`)

Deterministic, and safety-relevant because identifiers **cannot** be parameterized in MySQL:

- Identifier allowlist `^[A-Za-z0-9_]+$` for every schema/table/column/alias; anything else →
  `bad_identifier`. Identifiers are backtick-quoted; values are **always** `?` bind params.
- Reserved-word/alias collision check (aliases are generated, not model-provided).
- Explicit column list only — the compiler is structurally incapable of emitting `SELECT *`.
- Always appends `LIMIT ?` (mirrors the approved-query invariant in `connectors/sql/catalog.ts`).
- Emits at most one MySQL comment: the `MAX_EXECUTION_TIME` optimizer hint (§8.6). No other
  comments are ever produced.
- Deterministic param ordering; unit-tested with no DB.

### 8.4 Layer 4 — static SQL guardrails (AST, `connectors/adhoc/guard.ts`)

Belt-and-suspenders re-check of the *compiled* SQL by parsing it with `node-sql-parser`
(MySQL dialect). Even though we generated it, we re-validate it as if hostile:

| Guardrail | Enforcement | Reject |
|-----------|-------------|--------|
| Single statement | exactly one AST node; reject stray `;` / multiple statements | `not_single_select` |
| `SELECT` only | root node type is `select` | `forbidden_statement` |
| No DML/DDL | AST contains no insert/update/delete/replace/drop/alter/create/grant/truncate/call | `forbidden_statement` |
| No `SELECT *` | no `*` column node | `select_star` |
| No subquery/UNION/CTE | reject nested selects, `union`, `with` (v1) | `unsupported_construct` |
| Function denylist | reject `SLEEP, BENCHMARK, GET_LOCK, LOAD_FILE, GTID_*`, UDFs; allow only `COUNT/SUM/AVG/MIN/MAX` | `forbidden_function` |
| No system refs | reject `@@vars`, `INFORMATION_SCHEMA.*`, `mysql.*`, `performance_schema.*`, `sys.*` in AST | `forbidden_schema` |
| No `INTO OUTFILE/DUMPFILE` | AST/keyword check (also blocked by grant) | `forbidden_statement` |
| Table/column re-check | every identifier in the AST re-validated against the catalog allowlist (defense against a compiler bug) | `unknown_table` / `unknown_column` |
| String-token denylist | reuse `FORBIDDEN_SQL` regex from `connectors/sql/catalog.ts` as a final coarse net | `forbidden_sql` |

### 8.5 Layer 5 — cost pre-flight (`EXPLAIN`, no data read)

Run `EXPLAIN FORMAT=JSON <compiled sql>` first (this reads no rows):

- Reject full scan (`access_type: "ALL"`) on a table whose estimated `rows` >
  `ADHOC_MAX_SCAN_ROWS` (default **100000**) → `full_table_scan`.
- Reject if the product of estimated `rows_examined_per_scan` across joined tables >
  `ADHOC_MAX_EXAMINED_ROWS` (default **5,000,000**) → `estimated_cost_too_high`.
- Reject `Using temporary; Using filesort` on large estimates → `expensive_sort` (warn→reject by threshold).
- Reject any cartesian product the AST missed → `cartesian_join`.

This is the strongest single defense against an accidental heavyweight query.

### 8.6 Layer 6 — runtime guardrails (`connectors/adhoc/executor.ts`)

| Guardrail | How | Default |
|-----------|-----|---------|
| **Server-side time limit** ("quit >45s") | `SET SESSION max_execution_time = ?` **and** `SELECT /*+ MAX_EXECUTION_TIME(N) */ …` so the DB aborts the query itself | `ADHOC_TIMEOUT_MS` = 45000 |
| Read-only session | `SET SESSION transaction_read_only = 1` | on |
| Hard row ceiling | `SET SESSION sql_select_limit = ?` even if a `LIMIT` slips | `ADHOC_MAX_ROWS` = 500 |
| App backstop timeout | `Promise.race`; on timeout, issue `KILL QUERY <id>` on a side connection, then destroy the connection | timeout + 2s |
| Post-fetch row cap | reject/truncate past cap (mirrors `run.ts#row_limit_exceeded`) | 500 |
| Byte/cell cap | truncate + `truncated:true` if serialized > `ADHOC_MAX_BYTES` | 1 MB |
| Result column cap | reject if result has more columns than requested (schema drift) | `ADHOC_MAX_SELECT_COLS` |
| Multi-statements off | assert `multipleStatements:false` in the mysql2 connection | off |
| Concurrency | semaphore acquired at L1; released in `finally` | `ADHOC_MAX_CONCURRENCY` = 2 |

### 8.7 Post-processing (defensive output guardrails)

Even with sensitivity-scoped columns, add a final defensive pass before returning:

- Run results through `redaction/field-policy.ts` / `redaction/postback-url.ts` (throws on a denied
  field rather than silently dropping — consistent with cake).
- Regex PII sweep on string cells (email/SSN/card-like patterns); redact + flag `redactions:n`.
- Record every stage outcome (allowed/rejected + reason) to the audit log for metrics.

> MySQL note: `max_execution_time` / `MAX_EXECUTION_TIME(N)` apply to **read-only `SELECT`**
> statements and take **milliseconds** — exactly this tool's case.

## 9. Executor (`connectors/adhoc/executor.ts`)

New executor, same shape as `connectors/sql/executor.ts#SqlExecutor` but hardened:

```ts
// pseudocode — mirrors connectors/sql/executor.ts style
const conn = await mysql.createConnection(adhocRoDsn);
await conn.query('SET SESSION transaction_read_only = 1');
await conn.query('SET SESSION max_execution_time = ?', [timeoutMs]);
await conn.query('SET SESSION sql_select_limit = ?', [maxRows]); // hard ceiling even if LIMIT slips
// EXPLAIN pre-flight, then the guarded SELECT with bound params
```

- Multi-statements **disabled** in the connection options (mysql2 default; assert it).
- Separate DSN (`ADHOC_RO_DSN_STAGING`), separate credentials, staging only.
- Fixture mode (`ADHOC_FIXTURE=true`) for offline tests, mirroring
  `createFixtureSqlExecutor` / `MNGT_RO_FIXTURE`.

## 10. Planner (`connectors/adhoc/planner.ts`)

- Reuses `jobs/catalog_index/embed.ts` (`createOpenAIEmbedder`) for retrieval and OpenAI for the
  plan step. Model configurable via env (default a small, cheap model); use **structured output /
  JSON schema** so the response is a QuerySpec, not prose.
- Input to the model: the user question + the **retrieved catalog context only** (candidate
  tables + their indexable columns + FK hints from harvest `keys[]`). The model is told: choose
  tables/columns **only** from this list; if the question can't be answered from it, return
  `{ "unanswerable": true, "reason": "..." }`.
- Prompt-injection hardening: catalog `embedText` is data, not instructions. The planner prompt
  states that retrieved content and any values are untrusted and must not change the rules. The
  spec is validated regardless of what the model says, so a hijacked plan still can't produce
  unsafe SQL (compiler + guard + RO grant all run after).
- Fixture mode (`ADHOC_PLANNER_FIXTURE=true`) returns a canned QuerySpec for tests without OpenAI,
  matching the `CATALOG_SEARCH_FIXTURE` pattern.

## 11. Catalog: indexing *all* schemas

You want all schemas queryable, so extend the existing pipeline (do **not** invent a new one):

1. **Harvest** (`jobs/schema_harvest/`) — `INFORMATION_SCHEMA` only (hard rule). Expanding
   `allowlist.json` beyond the current targets (`admin, shorty, adcenter, whale, keywords`)
   **requires a ticket + DBA/security review** per `AGENTS.md`. This is the gate for "all schemas".
2. **Sensitivity** (`jobs/sensitivity_review/`) — must be run for every newly harvested target.
   `pii/secret/deny_index/unknown` columns stay non-indexable (`NON_INDEXABLE`) and therefore
   invisible to the planner. Add overrides in `catalogs/sensitivity/{env}/{target}.overrides.json`.
3. **Index** (`jobs/catalog_index/`) — into `lucos_business_catalog` (never `adpilot_embeddings`).
   The chunk already carries `indexableColumns` with `dataType` (`jobs/catalog_index/chunk.ts`),
   which is exactly what the planner/compiler need.
4. **FK hints for joins:** harvest already collects `keys[]` (`jobs/schema_harvest/types.ts#KeyMeta`).
   To let the planner join sensibly, either add `keys` into the Qdrant payload
   (`CatalogPointPayload`) or load them from `catalogs/schemas/{env}/{target}.json` at plan time.
   Recommend the latter first (no re-embed needed).

> The GRANTs for the ad-hoc RO user (§5) must be generated from the **same** reviewed sensitivity
> output so the index and the grant never drift.

## 12. Authz, enablement, audit (reuse existing path)

- Register the tool in `server/registry.ts` `TOOL_REGISTRY` with a `zod` input schema
  (`{ question: string, environment?: 'staging', limit?: number }`) and add its dispatch `case`
  in `server/create-mcp-server.ts` (same shape as `catalog_search`).
- Add its policy entry to `policies/tool_enablement.json` (**`enabled: false`**) — the
  `assertRegistryPolicyConsistency` drift check requires an entry. Enable per-env with
  `LUCOS_TOOL_ENABLE_ADHOC_EXPLORE=true` + `LUCOS_POLICY_ENABLED=true`.
- Run through `runToolCall` (`server/tool-runtime.ts`) unchanged: enablement →
  `authorizeToolCall` (gateway, carries the caller Bearer) → handler. **Do not** bypass authz.
- **Audit / provenance:** log (and return) the generated SQL, bound params (values redacted if a
  filter targets a sensitive-adjacent column), the `EXPLAIN` summary, `rowCount`, and duration.
  Extend the existing `logInfo('tool_call_ok', …)` events; the gateway records the tool call.
- Register name must pass `isDenylistedToolName` (so **not** `*_sql`).

## 13. Response shape

```jsonc
{
  "ticket": "TW-XXX",
  "mode": "adhoc",
  "advisory": "Exploratory result from a generated query. Not an approved/reportable metric.",
  "question": "…",
  "sql": "SELECT am.status, COUNT(am.adv_id) AS n FROM Admin.AccountMaster am WHERE am.status = ? GROUP BY am.status ORDER BY n DESC LIMIT ?",
  "params": ["active", 100],
  "tables": ["Admin.AccountMaster"],
  "explain": { "maxAccessType": "ref", "estRows": 1234 },
  "rowCount": 3,
  "rows": [ /* … */ ],
  "truncated": false
}
```

Always returning `sql` + `tables` + `explain` keeps results **verifiable** and stops the tool from
becoming a hallucination surface — a reviewer can see exactly what ran.

## 14. Config / env vars

| Var | Default | Layer | Purpose |
|-----|---------|-------|---------|
| `ADHOC_RO_DSN_STAGING` | — | L0 | Dedicated read-only MySQL DSN (SELECT-only user). No prod var. |
| `ADHOC_MAX_QUESTION_CHARS` | 512 | L1 | Question length cap |
| `ADHOC_RATE_PER_MIN` | 10 | L1 | Per-caller token bucket |
| `ADHOC_MAX_CALLS_PER_DAY` | 200 | L1 | Daily quota |
| `ADHOC_BREAKER_TRIPS` | 5 | L1 | Consecutive failures before circuit opens |
| `ADHOC_MAX_CONCURRENCY` | 2 | L1/L6 | Parallel ad-hoc queries |
| `ADHOC_MAX_TABLES` | 3 | L2 | Join/table cap ("no big join") |
| `ADHOC_MAX_SELECT_COLS` | 30 | L2/L6 | Column cap |
| `ADHOC_MAX_FILTERS` | 10 | L2 | Filter cap |
| `ADHOC_MAX_IN_VALUES` | 50 | L2 | `IN (...)` list cap |
| `ADHOC_MAX_ROWS` | 500 | L2/L6 | Row cap / `sql_select_limit` |
| `ADHOC_MAX_BYTES` | 1048576 | L6 | Serialized result cap |
| `ADHOC_TIMEOUT_MS` | 45000 | L6 | Server-side `max_execution_time` |
| `ADHOC_MAX_SCAN_ROWS` | 100000 | L5 | Full-scan rejection threshold (EXPLAIN) |
| `ADHOC_MAX_EXAMINED_ROWS` | 5000000 | L5 | Estimated cost rejection threshold (EXPLAIN) |
| `ADHOC_PLANNER_MODEL` | (small model) | L2 | OpenAI model for planning |
| `ADHOC_FIXTURE` | false | test | Offline executor for tests |
| `ADHOC_PLANNER_FIXTURE` | false | test | Offline planner for tests |
| `LUCOS_TOOL_ENABLE_ADHOC_EXPLORE` | false | L0 | Per-tool enable flag |

All limits live in **env/config**, not hardcoded, so tuning is an ops change — same philosophy as
the cake catalogs putting window/row/timeout bounds in the catalog. Every threshold has a safe
default so the tool is fully guarded even with zero extra env set.

## 15. File-by-file change plan

New:

- `connectors/adhoc/retrieve.ts` — Qdrant retrieval → candidate tables/columns (+ FK hints).
- `connectors/adhoc/planner.ts` — LLM → QuerySpec (structured output) + fixture.
- `connectors/adhoc/spec.ts` — QuerySpec `zod` schema + types.
- `connectors/adhoc/validate-spec.ts` — spec vs catalog allowlist + sensitivity.
- `connectors/adhoc/compile.ts` — spec → `{ sql, params }`.
- `connectors/adhoc/guard.ts` — AST + static guardrails + EXPLAIN gating.
- `connectors/adhoc/executor.ts` — hardened RO executor (session settings, timeout, fixture).
- `connectors/adhoc/credentials.ts` — `ADHOC_RO_DSN_STAGING` resolution + prod hard-fail.
- `connectors/adhoc/index.ts` — `runAdhocExplore(args)` orchestration (retrieve→plan→validate→compile→guard→execute→respond).
- `docs/ADHOC-QUERY-TOOL.md` — this doc.
- `tests/adhoc-*.test.ts` — see §16.

Edited:

- `server/registry.ts` — add tool definition (+ ensure name passes `isDenylistedToolName`).
- `server/create-mcp-server.ts` — add dispatch `case 'adhoc_explore'`.
- `policies/tool_enablement.json` — add `adhoc_explore: { enabled: false }`.
- `jobs/schema_harvest/allowlist.json` — (ticketed, DBA-reviewed) add remaining schemas.
- `.env.example` — document the new vars.
- `AGENTS.md` — add a section describing this tool and its guardrails.
- `package.json` — add `node-sql-parser` (AST validation).

## 16. Testing plan

Every layer in §8 must have direct unit tests (all runnable offline via fixtures — no DB, no OpenAI):

- **L0 gate:** `prod_gated` on `environment:'prod'`; disabled-by-default; `missing_dsn`/`invalid_dsn`.
- **L1 input:** `empty_question`/`question_too_long`; rate-limit + daily-quota trip;
  concurrency semaphore rejects the (N+1)th; circuit breaker opens after configured failures.
- **L2 spec:** strict-`zod` rejects unknown keys (`bad_spec`); `out_of_scope_column` when a column
  isn't in the retrieved set; `sensitive_column`; `forbidden_schema` for `mysql/information_schema`;
  `too_many_tables/columns/filters`; `cartesian_join`; `bad_operator`/`bad_aggregate`;
  `in_list_too_long`; `unindexed_like` for leading-wildcard; `unsupported_construct`.
- **L3 compile:** spec → exact SQL + params; no `SELECT *`; always `LIMIT ?`; identifiers
  allowlisted (`bad_identifier` on injection attempt); values parameterized; deterministic order.
- **L4 static SQL (AST):** `not_single_select` on a smuggled `;`; `forbidden_statement`;
  `forbidden_function` (SLEEP/BENCHMARK/LOAD_FILE); `@@var`/`information_schema` refs rejected;
  the `FORBIDDEN_SQL` coarse net.
- **L5 cost:** mocked `EXPLAIN FORMAT=JSON` → `full_table_scan`, `estimated_cost_too_high`,
  `expensive_sort`.
- **L6 runtime:** fixture executor returns ≤ limit rows; asserts `max_execution_time`,
  `transaction_read_only`, `sql_select_limit` are issued; `multipleStatements:false`;
  byte/row/column caps; timeout path issues `KILL QUERY` + destroys connection.
- **L7 output:** redaction throws on denied field; PII regex sweep redacts + flags.
- **Planner defense-in-depth:** a prompt-injection catalog payload still yields a spec the guards
  reject (proves the layers, not the LLM, are the control).
- **Registry/policy:** `assertRegistryPolicyConsistency` passes; `isDenylistedToolName` does not
  trip on the chosen name; tool disabled by default.

## 17. Rollout

1. Land code with tool **disabled by default**; fixtures green in CI (no DB / no OpenAI).
2. DBA creates the staging ad-hoc RO user + column/view-scoped GRANTs (§5).
3. Expand harvest allowlist (ticketed) → run harvest → sensitivity → index for the new schemas.
4. Enable in **staging only** via `LUCOS_TOOL_ENABLE_ADHOC_EXPLORE=true`; smoke a known question.
5. Watch audit logs (generated SQL, EXPLAIN, rejections) before widening usage.
6. Prod is a **separate future decision** and out of scope here.

## 18. Rejection taxonomy (surface to the client, don't silently fix)

Grouped by layer:

- **L0 gate:** `prod_gated`, `disabled`, `unauthorized`, `missing_dsn`, `invalid_dsn`.
- **L1 input:** `empty_question`, `question_too_long`, `rate_limited`, `quota_exceeded`,
  `too_many_concurrent`, `circuit_open`.
- **L2 spec:** `bad_spec`, `unanswerable`, `out_of_scope_table`, `out_of_scope_column`,
  `unknown_table`, `unknown_column`, `sensitive_column`, `forbidden_schema`, `too_many_tables`,
  `too_many_columns`, `too_many_filters`, `cartesian_join`, `unknown_join_col`, `unindexed_join`,
  `bad_operator`, `bad_aggregate`, `groupby_mismatch`, `invalid_param`, `in_list_too_long`,
  `unindexed_like`, `unsupported_construct`, `bad_order_by`.
- **L3 compile:** `bad_identifier`.
- **L4 static SQL:** `not_single_select`, `forbidden_statement`, `select_star`,
  `forbidden_function`, `forbidden_sql`.
- **L5 cost:** `full_table_scan`, `estimated_cost_too_high`, `expensive_sort`.
- **L6 runtime:** `row_limit_exceeded`, `byte_limit_exceeded`, `timeout`.

Each rejection returns a clear message so the client can rephrase — same fail-closed, explicit
style as `connectors/sql/run.ts` / `catalog.ts`. Rejection reasons are also emitted as audit
metrics so we can see which guardrails fire most.

## 19. Open decisions (need sign-off)

1. **Ticket + security/DBA approval** to add a dynamic (spec-compiled) query path at all.
2. **Which schemas** go into the harvest allowlist expansion (this defines "all schemas").
3. **Grant strategy:** column-level GRANTs vs. curated `lucos_ro_*`-style views per schema.
   (MySQL can't wildcard table grants — see the same constraint noted in `docs/CAKE-RO-VIEWS.md`.)
4. **Planner model** choice + cost ceiling.
5. Whether to ever allow Option B (free SQL) — default **no**.

## 20. Worked example (question → spec → SQL → verdict)

**Question:** _"How many advertisers were created since 2026-08-01, broken down by status?"_

### 20.1 Retrieved catalog context (L1→L2 input to the planner)

Retrieval (`connectors/adhoc/retrieve.ts`) returns candidate tables + **indexable** columns only:

```jsonc
[
  {
    "schema": "Admin", "table": "AccountMaster", "tableType": "BASE TABLE",
    "columns": [
      { "name": "adv_id",     "dataType": "int",      "sensitivity": "internal" },
      { "name": "status",     "dataType": "varchar",  "sensitivity": "public" },
      { "name": "created_at", "dataType": "datetime", "sensitivity": "public" }
    ],
    "keys": [{ "column": "adv_id", "referencedTable": null }]
  }
]
```

Sensitive columns (e.g. `Advertiser_info.pass_hash`, `Affiliate_info.SSN`) are **not** in this set,
because they're `deny_index`/`pii` in `catalogs/sensitivity/**` — so the planner cannot name them.

### 20.2 Planner output (QuerySpec — validated at L2)

```jsonc
{
  "intent": "count advertisers created since 2026-08-01, grouped by status",
  "tables": [{ "schema": "Admin", "name": "AccountMaster", "alias": "am" }],
  "columns": [
    { "table": "am", "name": "status" },
    { "table": "am", "name": "adv_id", "agg": "count", "as": "n" }
  ],
  "joins": [],
  "filters": [
    { "table": "am", "column": "created_at", "op": ">=", "value": "2026-08-01" }
  ],
  "groupBy": [{ "table": "am", "name": "status" }],
  "orderBy": [{ "table": "am", "name": "n", "dir": "desc" }],
  "limit": 100
}
```

### 20.3 Compiled SQL (L3) — deterministic, parameterized

```sql
SELECT /*+ MAX_EXECUTION_TIME(45000) */
       `am`.`status` AS `status`,
       COUNT(`am`.`adv_id`) AS `n`
FROM `Admin`.`AccountMaster` `am`
WHERE `am`.`created_at` >= ?
GROUP BY `am`.`status`
ORDER BY `n` DESC
LIMIT ?
```

`params = ["2026-08-01", 100]`. Note: no `SELECT *`, every identifier backtick-quoted, every value
a bind param, hint carries the timeout, `LIMIT ?` always present.

### 20.4 EXPLAIN verdict (L5)

- **Pass:** if `created_at` (or `status`) is indexed, EXPLAIN shows `access_type: "range"/"ref"`,
  low `rows` → allowed.
- **Reject:** if `AccountMaster` has no usable index and estimated `rows` > `ADHOC_MAX_SCAN_ROWS`,
  EXPLAIN shows `access_type: "ALL"` → `full_table_scan`, returned to the client so it can narrow
  the question (e.g. add a tighter date filter).

### 20.5 Response

```jsonc
{
  "ticket": "TW-XXX", "mode": "adhoc",
  "advisory": "Exploratory result from a generated query. Not an approved/reportable metric.",
  "question": "How many advertisers were created since 2026-08-01, broken down by status?",
  "sql": "SELECT /*+ MAX_EXECUTION_TIME(45000) */ `am`.`status` AS `status`, COUNT(`am`.`adv_id`) AS `n` FROM `Admin`.`AccountMaster` `am` WHERE `am`.`created_at` >= ? GROUP BY `am`.`status` ORDER BY `n` DESC LIMIT ?",
  "params": ["2026-08-01", 100],
  "tables": ["Admin.AccountMaster"],
  "explain": { "maxAccessType": "range", "estRows": 812 },
  "rowCount": 3,
  "rows": [
    { "status": "active",  "n": 640 },
    { "status": "paused",  "n": 128 },
    { "status": "pending", "n": 44  }
  ],
  "truncated": false
}
```

### 20.6 An injection attempt that gets stopped

If a hijacked catalog payload tried to make the planner emit
`{ "column": "status; DROP TABLE x" }`, it fails **L3** identifier allowlist (`bad_identifier`);
even if it somehow reached SQL, **L4** AST parse rejects the extra statement (`not_single_select`);
and **L5/L6** + the RO grant (§5) have no write path anyway. Defense-in-depth, not one check.

## 21. Reference implementation (near-final TypeScript)

> These are near-final and follow repo conventions (ESM, `.js` import specifiers, `zod` v4,
> error classes shaped like `connectors/sql/run.ts#SqlConnectorError`). They are provided here as
> the spec for the eventual `connectors/adhoc/*` files; minor `node-sql-parser` API adjustments may
> be needed at implementation time.

### 21.1 `connectors/adhoc/errors.ts`

```ts
export type AdhocRejectCode =
  | 'bad_spec' | 'unanswerable' | 'out_of_scope_table' | 'out_of_scope_column'
  | 'unknown_table' | 'unknown_column' | 'sensitive_column' | 'forbidden_schema'
  | 'too_many_tables' | 'too_many_columns' | 'too_many_filters' | 'cartesian_join'
  | 'unknown_join_col' | 'unindexed_join' | 'bad_operator' | 'bad_aggregate'
  | 'groupby_mismatch' | 'invalid_param' | 'in_list_too_long' | 'unindexed_like'
  | 'unsupported_construct' | 'bad_order_by' | 'bad_identifier' | 'not_single_select'
  | 'forbidden_statement' | 'select_star' | 'forbidden_function' | 'forbidden_sql'
  | 'full_table_scan' | 'estimated_cost_too_high' | 'expensive_sort'
  | 'row_limit_exceeded' | 'byte_limit_exceeded' | 'timeout';

export class AdhocError extends Error {
  readonly code: AdhocRejectCode;
  constructor(code: AdhocRejectCode, message: string) {
    super(message);
    this.name = 'AdhocError';
    this.code = code;
  }
}
```

### 21.2 `connectors/adhoc/spec.ts`

Compiled QuerySpec uses optional/nullish fields below. The OpenAI wire schema is `plannerQuerySpecSchema` (nullable nested fields such as `as`/`agg`/`value`), not `querySpecSchema` directly.

```ts
import { z } from 'zod';

const IDENT = /^[A-Za-z0-9_]+$/;
const ident = (label: string) =>
  z.string().regex(IDENT, `${label} must match ${IDENT}`);

export const AGG = z.enum(['count', 'sum', 'avg', 'min', 'max']);
export const OP = z.enum([
  '=', '!=', '<', '<=', '>', '>=', 'in', 'like', 'is_null', 'is_not_null',
]);

export const tableRefSchema = z
  .object({ schema: ident('schema'), name: ident('table'), alias: ident('alias') })
  .strict();

export const columnSelSchema = z
  .object({
    table: ident('alias'),
    name: ident('column'),
    agg: AGG.nullish(),
    as: ident('as').optional(),
  })
  .strict();

export const joinSchema = z
  .object({
    left: ident('alias'),
    leftCol: ident('column'),
    right: ident('alias'),
    rightCol: ident('column'),
    type: z.enum(['inner', 'left']),
  })
  .strict();

const filterValue = z.union([
  z.string().max(256),
  z.number(),
  z.array(z.union([z.string().max(256), z.number()])).max(50),
]);

export const filterSchema = z
  .object({
    table: ident('alias'),
    column: ident('column'),
    op: OP,
    value: filterValue.optional(),
  })
  .strict()
  .superRefine((f, ctx) => {
    const nullary = f.op === 'is_null' || f.op === 'is_not_null';
    if (nullary && f.value !== undefined) {
      ctx.addIssue({ code: 'custom', message: `${f.op} takes no value` });
    }
    if (!nullary && f.value === undefined) {
      ctx.addIssue({ code: 'custom', message: `${f.op} requires a value` });
    }
    if (f.op === 'in' && !Array.isArray(f.value)) {
      ctx.addIssue({ code: 'custom', message: 'in requires an array value' });
    }
    if (f.op !== 'in' && Array.isArray(f.value)) {
      ctx.addIssue({ code: 'custom', message: `${f.op} does not take an array` });
    }
  });

export const querySpecSchema = z
  .object({
    intent: z.string().min(1).max(512),
    tables: z.array(tableRefSchema).min(1).max(3),
    columns: z.array(columnSelSchema).min(1).max(30),
    joins: z.array(joinSchema).max(2).default([]),
    filters: z.array(filterSchema).max(10).default([]),
    groupBy: z
      .array(z.object({ table: ident('alias'), name: ident('column') }).strict())
      .max(10)
      .default([]),
    orderBy: z
      .array(
        z
          .object({
            table: ident('alias'),
            name: ident('column'),
            dir: z.enum(['asc', 'desc']).default('asc'),
          })
          .strict(),
      )
      .max(10)
      .default([]),
    limit: z.number().int().positive().max(500),
  })
  .strict();

export const plannerOutputSchema = z.union([
  querySpecSchema,
  z.object({ unanswerable: z.literal(true), reason: z.string().min(1) }).strict(),
]);

export type QuerySpec = z.infer<typeof querySpecSchema>;
export type Filter = z.infer<typeof filterSchema>;
```

### 21.3 `connectors/adhoc/compile.ts`

```ts
import { AdhocError } from './errors.js';
import type { Filter, QuerySpec } from './spec.js';

const IDENT = /^[A-Za-z0-9_]+$/;

function q(id: string): string {
  if (!IDENT.test(id)) {
    throw new AdhocError('bad_identifier', `Illegal identifier: ${id}`);
  }
  return `\`${id}\``;
}

const AGG_SQL: Record<NonNullable<QuerySpec['columns'][number]['agg']>, string> = {
  count: 'COUNT',
  sum: 'SUM',
  avg: 'AVG',
  min: 'MIN',
  max: 'MAX',
};

const OP_SQL: Record<string, string> = {
  '=': '=', '!=': '<>', '<': '<', '<=': '<=', '>': '>', '>=': '>=', like: 'LIKE',
};

export type Compiled = { sql: string; params: unknown[] };

export function compile(spec: QuerySpec, timeoutMs: number): Compiled {
  const aliases = new Set(spec.tables.map((t) => t.alias));
  const requireAlias = (a: string): void => {
    if (!aliases.has(a)) {
      throw new AdhocError('bad_identifier', `Unknown alias: ${a}`);
    }
  };

  // SELECT
  const selectParts = spec.columns.map((c) => {
    requireAlias(c.table);
    const col = `${q(c.table)}.${q(c.name)}`;
    const body = c.agg ? `${AGG_SQL[c.agg]}(${col})` : col;
    return c.as ? `${body} AS ${q(c.as)}` : body;
  });

  // FROM + JOINs
  const [first, ...rest] = spec.tables;
  if (!first) {
    throw new AdhocError('unknown_table', 'spec has no tables');
  }
  let from = `${q(first.schema)}.${q(first.name)} ${q(first.alias)}`;
  const joinByRight = new Map(spec.joins.map((j) => [j.right, j]));
  for (const t of rest) {
    const j = joinByRight.get(t.alias);
    if (!j) {
      throw new AdhocError('cartesian_join', `Table ${t.alias} has no join condition`);
    }
    requireAlias(j.left);
    requireAlias(j.right);
    const kw = j.type === 'left' ? 'LEFT JOIN' : 'INNER JOIN';
    from +=
      ` ${kw} ${q(t.schema)}.${q(t.name)} ${q(t.alias)}` +
      ` ON ${q(j.left)}.${q(j.leftCol)} = ${q(j.right)}.${q(j.rightCol)}`;
  }

  // WHERE
  const params: unknown[] = [];
  const where = spec.filters.map((f) => compileFilter(f, params, requireAlias));

  // GROUP BY / ORDER BY
  const groupBy = spec.groupBy.map((g) => {
    requireAlias(g.table);
    return `${q(g.table)}.${q(g.name)}`;
  });
  const orderBy = spec.orderBy.map((o) => {
    requireAlias(o.table);
    return `${q(o.table)}.${q(o.name)} ${o.dir.toUpperCase()}`;
  });

  const hint = `/*+ MAX_EXECUTION_TIME(${Math.floor(timeoutMs)}) */`;
  let sql = `SELECT ${hint} ${selectParts.join(', ')} FROM ${from}`;
  if (where.length) sql += ` WHERE ${where.join(' AND ')}`;
  if (groupBy.length) sql += ` GROUP BY ${groupBy.join(', ')}`;
  if (orderBy.length) sql += ` ORDER BY ${orderBy.join(', ')}`;
  sql += ` LIMIT ?`;
  params.push(spec.limit);

  return { sql, params };
}

function compileFilter(
  f: Filter,
  params: unknown[],
  requireAlias: (a: string) => void,
): string {
  requireAlias(f.table);
  const col = `${q(f.table)}.${q(f.column)}`;
  if (f.op === 'is_null') return `${col} IS NULL`;
  if (f.op === 'is_not_null') return `${col} IS NOT NULL`;
  if (f.op === 'in') {
    const arr = f.value as unknown[];
    if (!arr.length) throw new AdhocError('invalid_param', 'in list is empty');
    params.push(...arr);
    return `${col} IN (${arr.map(() => '?').join(', ')})`;
  }
  // '=', '!=', '<', '<=', '>', '>=', 'like'
  params.push(f.value);
  return `${col} ${OP_SQL[f.op]} ?`;
}
```

### 21.4 `connectors/adhoc/guard.ts` (static AST re-check of the compiled SQL)

```ts
import { Parser } from 'node-sql-parser';

import { AdhocError } from './errors.js';

const parser = new Parser();
const OPT = { database: 'mysql' } as const;

// Only aggregates the compiler can emit are allowed.
const ALLOWED_FUNCTIONS = new Set(['COUNT', 'SUM', 'AVG', 'MIN', 'MAX']);
const FORBIDDEN_SCHEMAS = new Set([
  'information_schema', 'mysql', 'performance_schema', 'sys',
]);
// Coarse final net (mirrors connectors/sql/catalog.ts#FORBIDDEN_SQL).
const FORBIDDEN_SQL =
  /--|\/\*(?!\+ MAX_EXECUTION_TIME)|\b(insert|update|delete|drop|alter|create|grant|revoke|truncate|replace|call|exec|execute|into\s+outfile|into\s+dumpfile|load_file|sleep|benchmark|get_lock)\b/i;

/** Throws AdhocError on any violation; returns nothing on success. */
export function staticGuard(sql: string, allowedIdent: (name: string) => boolean): void {
  if (FORBIDDEN_SQL.test(sql)) {
    throw new AdhocError('forbidden_sql', 'Compiled SQL hit the forbidden-token net');
  }
  if (sql.includes(';')) {
    throw new AdhocError('not_single_select', 'Multiple statements are not allowed');
  }

  let ast: unknown;
  try {
    ast = parser.astify(sql, OPT);
  } catch {
    throw new AdhocError('forbidden_sql', 'Compiled SQL failed to parse');
  }
  const stmts = Array.isArray(ast) ? ast : [ast];
  if (stmts.length !== 1) {
    throw new AdhocError('not_single_select', `Expected 1 statement, got ${stmts.length}`);
  }
  const stmt = stmts[0] as Record<string, unknown>;
  if (stmt.type !== 'select') {
    throw new AdhocError('forbidden_statement', `Root statement is ${String(stmt.type)}`);
  }
  if (stmt.with) throw new AdhocError('unsupported_construct', 'CTE not allowed');
  if (stmt.union || stmt._next || stmt.set_op) {
    throw new AdhocError('unsupported_construct', 'UNION not allowed');
  }

  // SELECT * ?
  const columns = stmt.columns;
  if (columns === '*' || (Array.isArray(columns) && columns.some((c) => isStar(c)))) {
    throw new AdhocError('select_star', 'SELECT * is not allowed');
  }

  // Walk the AST for subqueries + functions.
  walk(stmt, (node) => {
    const type = (node as Record<string, unknown>).type;
    if (type === 'select' && node !== stmt) {
      throw new AdhocError('unsupported_construct', 'Subqueries not allowed');
    }
    if (type === 'function' || type === 'aggr_func') {
      const name = extractFuncName(node);
      if (!name || !ALLOWED_FUNCTIONS.has(name.toUpperCase())) {
        throw new AdhocError('forbidden_function', `Function not allowed: ${name ?? '?'}`);
      }
    }
    if (type === 'var' || type === 'variable') {
      throw new AdhocError('forbidden_function', 'System variables not allowed');
    }
  });

  // Re-check every table/column identifier against the catalog allowlist.
  const { tableList, columnList } = parser.parse(sql, OPT) as {
    tableList: string[];
    columnList: string[];
  };
  for (const entry of tableList) {
    // format: "select::<db>::<table>"
    const parts = entry.split('::');
    const schema = parts[1] && parts[1] !== 'null' ? parts[1] : '';
    const table = parts[2] ?? '';
    if (schema && FORBIDDEN_SCHEMAS.has(schema.toLowerCase())) {
      throw new AdhocError('forbidden_schema', `Schema not allowed: ${schema}`);
    }
    if (table && !allowedIdent(table)) {
      throw new AdhocError('unknown_table', `Table not in catalog: ${table}`);
    }
  }
  for (const entry of columnList) {
    const col = entry.split('::').pop() ?? '';
    if (col && col !== '*' && !allowedIdent(col)) {
      throw new AdhocError('unknown_column', `Column not in catalog: ${col}`);
    }
  }
}

function isStar(col: unknown): boolean {
  const c = col as Record<string, unknown>;
  const expr = c?.expr as Record<string, unknown> | undefined;
  return expr?.type === 'column_ref' && expr?.column === '*';
}

function extractFuncName(node: unknown): string | undefined {
  const n = node as Record<string, unknown>;
  if (typeof n.name === 'string') return n.name;
  // node-sql-parser sometimes nests: name.name[0].value
  const name = n.name as Record<string, unknown> | undefined;
  const list = name?.name as Array<Record<string, unknown>> | undefined;
  const first = list?.[0];
  return typeof first?.value === 'string' ? first.value : undefined;
}

function walk(node: unknown, visit: (n: unknown) => void): void {
  if (!node || typeof node !== 'object') return;
  visit(node);
  for (const value of Object.values(node as Record<string, unknown>)) {
    if (Array.isArray(value)) {
      for (const item of value) walk(item, visit);
    } else if (value && typeof value === 'object') {
      walk(value, visit);
    }
  }
}
```

> The `allowedIdent` callback is backed by the catalog allowlist built at L2 from
> `catalogs/schemas/**` (public/internal columns only), so the AST re-check and the spec validation
> share one source of truth.

## 22. Ready-to-lift governance drafts

These are drafts for the two governance files named in §15. They are kept **here** (not applied to
`AGENTS.md` / `.env.example`) until the §19 ticket + DBA/security sign-off lands, so the hard-rule
and env files don't advertise a tool that isn't approved/implemented yet. Lift verbatim at
implementation time.

### 22.1 `AGENTS.md` — new section (insert after "MNGT RO SQL connector (TW-310)")

```markdown
## Ad-hoc read-only query tool (TW-XXX) — STAGING ONLY, DISABLED BY DEFAULT

- **Not** a generic `execute_sql`. The caller sends a natural-language `question`; the model emits a
  **structured QuerySpec** (never SQL); our code **compiles** it to parameterized SQL. Name is
  `adhoc_explore` (must pass `isDenylistedToolName` — never a `*_sql` name).
- Connector: `connectors/adhoc/` — `runAdhocExplore({ question, environment })`.
  Pipeline: retrieve (Qdrant) → plan (LLM) → validate spec → compile → static AST guard →
  EXPLAIN cost gate → hardened RO execute → redact → respond-with-provenance.
- **Staging only.** `environment:'prod'` hard-fails (`prod_gated`). No prod DSN var exists.
- **Security boundary is the DB grant** (DBA-owned, prod-applicable): dedicated SELECT-only RO user,
  column/view-scoped to `public`/`internal` columns only; `pii/secret/deny_index/unknown` never
  granted and never indexed. See `docs/ADHOC-QUERY-TOOL.md` §5.
- Six code-enforced guardrail layers (gate → input → spec → compile → AST → EXPLAIN → runtime),
  all with safe defaults so the tool is fully guarded with zero extra env. See §8.
- SQL is generated server-side only; every value is a bind param; identifiers are allowlisted;
  always ends `LIMIT ?`; server-side `max_execution_time`; EXPLAIN rejects full scans.
- Every call audits the generated SQL + params + EXPLAIN summary + rowCount + duration; the response
  returns `sql`/`tables`/`explain` so results are verifiable. Output is **exploratory, not reportable**.
- DSN: `ADHOC_RO_DSN_STAGING` (SELECT-only user, read replica). Offline tests: `ADHOC_FIXTURE=true`,
  `ADHOC_PLANNER_FIXTURE=true`.
- Enable (staging): `LUCOS_TOOL_ENABLE_ADHOC_EXPLORE=true` + `LUCOS_POLICY_ENABLED=true`.
- Design + reference implementation: `docs/ADHOC-QUERY-TOOL.md`.
```

Also extend the **Hard rules** list with one line:

```markdown
- Ad-hoc query tool is **spec-compiled, staging-only, disabled by default** — never accepts
  caller-authored SQL, never runs in prod, never reads non-`public`/`internal` columns
```

### 22.2 `.env.example` — new block (insert after the "MNGT RO SQL (TW-310)" block)

```bash
# =============================================================================
# Ad-hoc read-only query tool (TW-XXX) — STAGING ONLY, disabled by default
# Spec-compiled queries (never caller SQL). Design: docs/ADHOC-QUERY-TOOL.md
# Enable on staging only, after DBA creates the SELECT-only RO user + grants.
# =============================================================================
# LUCOS_TOOL_ENABLE_ADHOC_EXPLORE=true
# Dedicated SELECT-only RO user on a read replica (NOT the MNGT/cake users). No prod var.
# ADHOC_RO_DSN_STAGING=mysql://lucos_adhoc_ro:***@STAGING_REPLICA:3306/
# --- guardrail limits (all have safe code defaults; override only to tune) ---
# ADHOC_MAX_QUESTION_CHARS=512
# ADHOC_RATE_PER_MIN=10
# ADHOC_MAX_CALLS_PER_DAY=200
# ADHOC_BREAKER_TRIPS=5
# ADHOC_MAX_CONCURRENCY=2
# ADHOC_MAX_TABLES=3
# ADHOC_MAX_SELECT_COLS=30
# ADHOC_MAX_FILTERS=10
# ADHOC_MAX_IN_VALUES=50
# ADHOC_MAX_ROWS=500
# ADHOC_MAX_BYTES=1048576
# ADHOC_TIMEOUT_MS=45000
# ADHOC_MAX_SCAN_ROWS=100000
# ADHOC_MAX_EXAMINED_ROWS=5000000
# ADHOC_PLANNER_MODEL=gpt-4o-mini
# --- offline test modes (never on hosted staging/prod) ---
# ADHOC_FIXTURE=true
# ADHOC_PLANNER_FIXTURE=true
```

### 22.3 `policies/tool_enablement.json` — new entry

```jsonc
"adhoc_explore": {
  "enabled": false,
  "description": "Ad-hoc read-only spec-compiled query (staging only, TW-XXX). Disabled by default."
}
```

> `assertRegistryPolicyConsistency` requires the registry tool and this policy entry to match — add
> both in the same change, or the load-time drift check fails.