Analyst tooling: eight low-severity friction and diagnosability issues

This tracker bundles eight unrelated fixes, so pick one and split it out. The smallest is item 6: the OR group in the search query at packages/opencode/src/altimate/native/schema/cache.ts:270-272 needs parentheses so the warehouse filter applies to all terms. Done means a unit test with two warehouses sharing a table name returns only the requested warehouse's tables.

Eight lower-severity issues found while auditing recorded analyst sessions. Each costs time, tokens or diagnosability rather than answers. Grouped here to keep the tracker readable; split any of them out when picked up.

1. Tool output footers are misleading or noisy ("Ensure the dispatcher is running", unused sql_analyze tip, wrong truncation and row-limit flags)

Problem

Text appended to tool results points the model at the wrong cause or adds tokens with no value.

Mechanism and evidence

1. "Ensure the dispatcher is running and a warehouse connection is configured." is appended to every SQL and schema failure (packages/opencode/src/altimate/tools/sql-execute.ts:264, schema-inspect.ts:150; similar in schema-cache-status.ts:28, lineage-check.ts:62). All 93 recorded instances followed user errors (unknown column or table), never a dispatcher fault.
2. "Tip: Use sql_analyze to check this query ..." (post-connect-suggestions.ts:90) was appended to 762 and 251 successful outputs in two sets of sessions; sql_analyze was called 0 times. A "Hint: only N row(s)" footer (sql-result-format.ts:280) was appended 228 times.
3. Output truncated: yes is shown, and the result spilled, when every row is displayed and only a column alias was clipped (7 recorded spills, sql-result-format.ts:342).
4. Result may be row-limited (requested row limit 100 ...) is shown when a Mongo limit: 2 or 3 produced the short result (23 outputs; sql-result-format.ts:340).
5. Reserved-word alias parse errors (35 cases, for example bare owner, days) carry no "quote the alias or use AS" hint.

Impact

Low: wasted tokens and a misdirected turn.

Proposed fix

• Append the dispatcher hint only when the dispatcher call itself failed.
• Suppress the sql_analyze tip and row-count hint for the analyst agent.
• Do not spill or flag truncation when all rows are shown and only header aliases were shortened; attribute row-limit warnings to the cap that actually applied.
• Add an alias-quoting hint to DuckDB parse errors on reserved words.

Acceptance check

Unit tests on the formatters: a binder error has no dispatcher hint; a one-row result with a long alias is not marked truncated; a Mongo limit: 2 result carries no "row-limited (100)" warning.

2. schema_inspect rejects scratch.X, main.X and public.X when a warehouse is given, and gives Postgres advice for MongoDB

Problem

sql_materialize tells the model a table "is also addressable as scratch.X", but schema_inspect with warehouse: "scratch" rejects both table: "scratch.X" and schema_name: "scratch". Qualified names such as main.Mutation_Data or public.clinical_info fail the same way. For MongoDB, the not-found message recommends information_schema.tables.

Mechanism

• resolveInspectionTarget returns the input unchanged whenever warehouse is provided (packages/opencode/src/altimate/native/connections/inspection-target.ts:19-22), so the dotted name is looked up as a literal table name, even with schema_name: "".
• The advice text with information_schema.tables is built in packages/opencode/src/altimate/tools/schema-inspect.ts:96. (The Mongo-specific "not found in schema public" default has not been re-verified in current source.)
• The producing message is at sql-materialize.ts:619.

Evidence

Recorded analyst sessions: 34 failed scratch.X inspections, 35 failed Mongo db.collection inspections with Postgres advice, 24 failures on qualified names in 13 sessions, plus 3 more main.X cases. The model recovered with SQL or a second call each time, costing about one turn.

Impact

Low: one wasted turn per occurrence.

Proposed fix

• When the table contains a dot and the qualifier is an existing schema of the warehouse, split it into schema_name and table; treat catalog scratch as main for the scratch warehouse.
• Give MongoDB its own not-found message that lists collections.

Acceptance check

schema_inspect({warehouse:"scratch", table:"scratch.t"}) and ({warehouse:"w", table:"main.t"}) return the table; a missing Mongo collection returns the list of collections.

3. 8-9 s process startup runs outside the time-budget clock and eats the finalization margin

Problem

Every run spends a fixed 8-9 s (p90 15 s, max 33 s) in bootstrap before the session exists. The budget clock starts later, but an external runner timeout counts from process start, so the real finalization slack is smaller than configured.

Mechanism

startWorkClock runs after bootstrap and session.create (packages/opencode/src/cli/cmd/run.ts:911-917; packages/opencode/src/cli/bootstrap.ts:13-24). With a hard deadline at 840 s inside a 900 s external timeout, the slack is 60 s minus startup, about 51 s. Startup is dominated by InstanceRuntime.load (connection registration, plugin and skill loading, schema cache); session creation to first model request adds about 1.8 s.

Evidence

Recorded runs: time before the session exists p50 8.35 s, p90 14.9 s, min 7.6 s, max 33.4 s; 2,576 s per 270-run batch. It is about 34-45% of the median short run.

Impact

Low: latency and cost per short run, and a smaller finalization window under budget pressure.

Proposed fix

• Profile InstanceRuntime.load; lazy-load plugins, skills and connections the analyst never uses.
• Start the budget clock at process start, or compute the external timeout from the budget plus measured bootstrap.

Acceptance check

Median time from process start to first model request below 3 s for the analyst agent; the hard deadline accounts for bootstrap time.

4. Tracing records tool calls only on completion, so killed or interrupted sessions hide the call that consumed the time

Problem

A tool call that is still running when the process is killed or interrupted leaves no span. The trace shows only an open generation, so hundreds of seconds appear as unexplained idle time.

Mechanism

logToolCall accepts only completed and error parts (packages/opencode/src/altimate/observability/tracing.ts:1075-1091). There is no span at running and none is closed at interrupt, although the tracer already writes a final file on interrupt.

Evidence

In ten recorded long sessions the traced tool time was about 130 s against a 852-897 s trace span; a roughly 720 s read does not appear at all. 35 of 41 non-completed sessions longer than 300 s end with open generations. This hid the long-read and lock-contention defects from trace-level analysis.

Impact

Low (observability), but it directly slows diagnosis of the highest-impact failures.

Proposed fix

Emit a tool span at running with start time and input; close it on completion, or as aborted when the process is interrupted or the session ends.

Acceptance check

Interrupting a session during a long tool call leaves a trace with that tool span, status aborted and a correct start time.

5. Sub-agents re-materialize a source that already exists in the shared scratch store

Problem

Sub-agents do not know which scratch tables already exist, so they re-read the same large source. In two recorded sessions a 127,600-document collection was read twice (235 s + 348 s; 135 s + 271 s), once with a third attempt timing out.

Mechanism

Scratch is shared across the root session (packages/opencode/src/altimate/native/connections/scratch.ts:51-61, :170) but the sub-agent prompt contains no list of existing tables or their source SQL, and sql_materialize does not detect identical source SQL.

Evidence

Two sessions, 348 s + 271 s of duplicated reading, using gpt-5.6-sol with sub-agent fan-out.

Impact

Low (time), amplified by the scratch-lock issue.

Proposed fix

List existing scratch tables with their source SQL in the sub-agent prompt, or return "already materialized as X (same source SQL)" from sql_materialize for an identical source query.

Acceptance check

A second sql_materialize with identical warehouse and SQL returns immediately with a pointer to the existing table.

6. schema_search warehouse filter is not parenthesised and constrains only the last search term

Problem

A search restricted to one warehouse can return tables from another warehouse.

Mechanism

The query is built as WHERE t1 OR t2 OR ... OR tn AND warehouse = ? (packages/opencode/src/altimate/native/schema/cache.ts:270-272). AND binds tighter than OR, so the warehouse filter applies only to the last term.

Evidence

A search limited to one warehouse returned a table from a different warehouse in 2 recorded sessions.

Impact

Low: irrelevant results in schema discovery.

Proposed fix

Parenthesise the OR group: WHERE (t1 OR ... OR tn) AND warehouse = ?.

Acceptance check

Unit test with two warehouses sharing a matching table name: a multi-token search with a warehouse filter returns only that warehouse's tables.

7. implicit_cross_join and join_without_condition lint fire on correlated table functions (json_each, unnest, range)

Problem

Advisory lint appended to successful SQL results flags correlated lateral table functions as cross joins, and flags the standard rewrite (JOIN json_each(...) ON 1=1) again.

Mechanism

The rules are listed in packages/opencode/src/altimate/tools/sql-result-lint.ts:18-19 and implemented in the core lint engine, which treats a comma/cross join to a table-valued function as missing a join condition even when the function argument references the left table.

Evidence

In recorded sessions implicit_cross_join fired 49 times (48 involved json_each, unnest or range) and join_without_condition 18 times (16 involved a correlated json_each(p.col)), covering 46 sessions. Models spent turns rewriting a correct query.

Impact

Low: noise and wasted turns.

Proposed fix

Exempt correlated table-valued functions (arguments referencing preceding tables) from both rules in the core lint engine.

Acceptance check

SELECT ... FROM t, json_each(t.col) and FROM t CROSS JOIN json_each(t.col) produce no lint findings; a true cartesian product still does.

8. Read-only analyst bash allows find / style crawls and in-place edits under /tmp

Problem

The analyst's bash tool permits filesystem-wide searches that burn wall time, and writes to scratch locations, while the intended policy is read-only analysis through the SQL tools.

Mechanism

Bash permission rules deny specific data-file paths but not broad crawls or sed -i on /tmp; there is no per-call wall-time bound for the analyst.

Evidence

In 96 recorded bash calls by Claude Opus: find / -name "*.duckdb" took 37.3 s in one session and 2-4 s in three others; sed -i on a /tmp file and reading the agent configuration file were allowed; 25 were python SQL generators and 38 were spill-file reads. Direct opens of the data files were denied.

Impact

Low: wall-time leakage (37 s in one session).

Proposed fix

Deny find / and other root-level crawls for the analyst agent, or bound bash wall time to a few seconds for that agent.

Acceptance check

find / -name x is denied or terminated within the bound for the analyst agent; spill-file reads continue to work.