[Design] Deliver large AI tool results through previews and retrievable local artifacts

[openai0229]

Context

Chat2DB Community AI tools must keep tool responses small enough for the model context while still making complete execution results available when the model needs more detail.

The current tool paths use fixed bounds in several places, including datasource lists, table lists, table-schema lookup, and SQL row previews. Returning every row inline is not practical, but returning only a preview can hide whether the result is complete.

This design question surfaced while reviewing #1876.

Proposed direction

Use a two-level result model for large AI tool outputs:

1. Return a bounded structured preview in the normal tool response.
2. Materialize the complete available result through a storage-neutral Chat2DB artifact-store contract.
3. Use a local file or embedded implementation by default in Community so the feature adds no mandatory external service.
4. Return completeness metadata and an opaque artifact identifier with the preview.
5. Let the model read additional bounded chunks through a dedicated Chat2DB tool when needed.

For example:

{
  "success": true,
  "summary": "Returned 50 of 12438 rows. The complete result is available as an artifact.",
  "data": {
    "preview": {
      "columns": ["id", "name"],
      "rows": []
    },
    "completeness": {
      "complete": false,
      "returnedRows": 50,
      "totalRows": 12438
    },
    "artifact": {
      "id": "art_01K...",
      "format": "jsonl",
      "sizeBytes": 4821932,
      "expiresAt": "2026-07-24T10:00:00Z"
    }
  },
  "errorCode": null
}

The model would retrieve more data through a controlled tool such as:

read_ai_tool_artifact(artifactId, cursor, limit, columns, filters)

The artifact identifier should be opaque. The tool response should not expose an arbitrary local filesystem path because the model cannot access the host filesystem directly and Chat2DB must control which artifacts can be read.

Storage and retrieval boundary

• AI tools, the UI, and retrieval callers depend on the Chat2DB artifact contract, not on filesystem, Redis, or another storage-specific API.
• The storage port must support streaming creation, commit, abort, metadata lookup, bounded reads, expiration, and deletion without first buffering the complete payload in JVM memory.
• Community's first implementation should use local file or embedded storage and must not require an external storage service. The storage medium remains replaceable behind the same contract as Community deployment needs evolve.
• Storage and query capabilities are related but distinct. Cursor/limit reads and column projection form the common baseline. Filtering, sorting, or indexing must use bounded structured semantics, declare whether the selected implementation supports them, and return a stable unsupported-capability result rather than leaking a backend query language.
• Authorization scope, lifecycle state, retention, quota, cancellation, crash recovery, and cleanup remain Chat2DB responsibilities regardless of the selected storage implementation.

Design questions

1. Does "complete result" mean all rows already fetched by the current execution path, or should Chat2DB stream the entire query result into the artifact?
2. What lifecycle operations, states, and idempotency guarantees must the storage-neutral artifact contract expose?
3. Which default local representation best preserves column order, SQL nulls, numeric values, temporal values, and large-value metadata while supporting chunked reads?
4. Which projection, filtering, sorting, and indexing capabilities belong to the common retrieval contract, and how should implementations report unsupported capabilities?
5. Should filtering operate only on the materialized artifact, or may any operation re-execute the source query under a separate explicit contract?
6. Which AI tools should support artifacts in the first version: SQL execution only, or metadata and schema tools as well?
7. What expiration time, local quota, cleanup policy, crash-recovery behavior, and failure behavior should the Community artifact store use?
8. How should the UI expose artifact availability and cleanup without putting the complete result into the model context?

Important implementation boundary

The current SQL tool executes with a bounded page size. Writing the current ExecuteResponse to any storage implementation would preserve only the fetched page, not necessarily the complete query result. Producing a genuinely complete artifact may require a separate streaming execution or export path with cancellation, resource limits, and cleanup.

Scope

This discussion covers Chat2DB Community and its local runtime only.

Desired outcome

Agree on the Community result contract, the storage-neutral artifact lifecycle, the default local implementation, and bounded retrieval and filtering behavior before splitting the work into focused implementation issues.

Tracking

• Tracking Issue: Deliver large AI tool results through previews and controlled retrieval #1942

The Issue owns the user problem, scope, priority, acceptance criteria, and implementation tracking. This Discussion owns unresolved design questions, alternatives, and decision records.

[haizhi03]

Proposal: lock a minimal v1 decision set for #1941 / #1942

Thanks for framing this clearly. I reviewed the current AI tool paths and a local design draft aligned with this Discussion / #1942.

Current behavior (today)

• AI tools already bound results in several places (for example execute_sql uses an execution page plus a bounded row preview; datasource/table/schema listings are also capped).
• Truncation is mostly free-form or weakly signaled. The model often cannot reliably tell “preview” from “complete”.
• There is no Chat2DB-managed artifact store, no opaque retrieval id, and no controlled read tool.

So the user-visible gap in #1942 is real even before storage work starts.

Proposed v1 decisions (please accept / reject each)

I propose we treat the following as the first implementation boundary:

1. Completeness for bounded results first; artifacts second
   Every bounded AI tool result reports machine-readable preview completeness
   (COMPLETE / INCOMPLETE / UNKNOWN, returned preview count, boundedBy).
   Empty results, hard errors, and non-collection tools are not forced into this enum.
   This slice does not require storage.

2. Separate preview completeness, artifact decision, and artifact coverage
   Avoid overloading a single artifact.complete=false for limit truncation; that is easy to confuse with “artifact creation failed”.

   Suggested split:

   • preview.completeness: COMPLETE | INCOMPLETE | UNKNOWN
   • artifact.decision: CREATED | SKIPPED | DENIED | FAILED | CANCELLED
   • artifact.coverage: COMPLETE | PREFIX | UNKNOWN

   Meaning of artifact.coverage = COMPLETE:
   the result set actually produced by this streaming execution was fully materialized under the export path.
   It is not a claim of a globally consistent database snapshot under concurrent writers.

   PREFIX means export stopped early because of resource caps or cancellation after some rows were written.
   “Complete artifact” still means streamed materialization under hard caps, not “persist only the current ExecuteResponse page”.

   artifact.coverage is present only when artifact.decision = CREATED.
   A resource-capped export may be returned as CREATED + PREFIX if partial retrieval is supported.
   CANCELLED and FAILED do not expose a retrievable partial artifact in v1 and must clean up staged data.

3. v1 artifact creation is readonly execute_sql only
   Metadata/schema tools get bounded-result completeness only (no artifact) in v1.

4. Community local default store; storage-neutral service boundary
   Local files/embedded storage by default; callers use a Chat2DB service contract, never filesystem paths.

5. Controlled read tool in v1: cursor + limit + column projection only
   No filter/sort/query engine on artifacts in v1.
   If the model needs filtering, it should issue a new SQL query.

6. Synchronous tool return in v1, with hard timeouts
   execute_sql returns only after export reaches a terminal decision
   (CREATED / SKIPPED / DENIED / FAILED / CANCELLED).
   Model-visible WRITING is deferred.
   Export duration must be bounded by both maxDuration and the tool-call timeout; hitting either limit returns a terminal state and must not leave the call hanging.

7. Conservative policy defaults, with explicitOnly defined narrowly

   • Community-only
   • Feature flags default off
   • MCP: explicitOnly
   • Chat: explicitOnly or very conservative auto (maintainer choice)

   For v1, explicitOnly means artifact creation happens only when the user/runtime explicitly requests materialization
   (not merely because the model asked, and not as silent default export).
   Reading is separate: the model may read only artifacts that were returned inside the current authorization scope / tool chain.
   explicitOnly does not mean “the model must specially re-request permission on every page read” beyond normal authz checks.

8. Re-authorize every read; cursor is only a bound resume position
   Every read_ai_tool_artifact call is re-authorized against the current caller/session (and related scope bindings).
   A cursor is a signed, expiring, artifact-bound resume token for position only.
   Holding an opaque artifact id or any bearer-like grant must not be interpreted as permanent read access.

Suggested split after acceptance

1. Completeness contract for bounded results only (no store) — coordinates with feat(ai): standardize AI tool results as JSON #1876
2. Local artifact store + lifecycle/cleanup
3. Readonly SQL streaming export + artifact.decision / artifact.coverage
4. read_ai_tool_artifact
5. Flags / policy matrix
6. UI availability + prompt rules (no full result injected into model context)

Maintainer questions (blocking)

Please confirm:

1. Is streamed capped export the accepted meaning of v1 materialization (vs storing only the current page)?
2. Is the split preview.completeness / artifact.decision / artifact.coverage accepted, including the rule that coverage exists only for CREATED, and that CANCELLED / FAILED never leave a readable partial artifact in v1?
3. Is SQL-only artifact creation accepted for v1?
4. Is no filter/sort on read accepted for v1?
5. Chat default: explicitOnly or auto?
   (With explicitOnly = create-only-on-explicit-request, as defined above.)
6. Are these non-binding starter caps roughly acceptable to refine later in implementation Issues: 100k rows / 100MB / 60s, TTL 24h?
7. Should completeness-only for bounded results land as a focused Issue before artifact storage, as the first slice of Deliver large AI tool results through previews and controlled retrieval #1942?

Non-goals for this comment

I’m not asking to implement the full artifact system in one PR.
If maintainers accept the boundary above, I can open focused contribution Issues under #1942 and keep each PR reviewable.

Happy to adjust any of these decisions based on maintainer preference.