Full-result data pipeline & rich tables — design

Date: 2026-07-08 · Status: approved (user-directed) · Surfaces: staff Tanya
Data (/app/data) + apps/internal-agent

Problem (evidence: thread 019f4001-f31d-738f-8cee-56e6ad2d843e)

A staff user asked for "jumlah PT per provinsi dan per kota" categorized by
company size. The underlying query yields thousands of (kategori, lokasi, jumlah) rows. What the user actually got:

1. run_sql_query hard-caps at 200 rows into the LLM context — the model
   never sees the full result.
2. The DATA_VIZ contract allows one table; the model inlined 5 rows
   (total: 50) and shoved the rest into two truncated markdown tables
   ("Top 10 per Kategori", "Top 15 - Kategori Besar").
3. "Ekspor .csv" exports only the inline rows — 5 rows of a ~2.500-row
   result.
4. ResultTable shows a fixed 5 rows: no pagination, sorting, filtering, or
   horizontal scroll.
5. Narrative markdown tables render statically — no export, no interaction.
6. The model closes with "data lengkap tersedia namun tidak ditampilkan karena
   keterbatasan ruang" — the UI ceiling became the product.

Root cause: the full result set only reaches the browser by being re-typed
by the LLM through its token stream. No amount of prompt tuning fixes that;
the data needs a side-channel.

Design

1. Agent: result spill (side-channel)

run_sql_query (staff tools, dash/tools/sql.py):

• Execute as today; fetch up to RESULT_SPILL_MAX_ROWS (default 10.000).
• Persist the full result as JSON to RESULTS_DIR (default
  /data/results, named volume internal-agent-data):
  {result_id, created_at, database, sql, columns[], row_count, truncated, rows[][]} — result_id = 32-char hex (uuid4).
• Return to the LLM (token-light): {result_id, row_count, columns, truncated, preview} where preview = first limit rows (default 50,
  cap 200 — unchanged today's budget) plus a one-line hint to reference
  result_id in DATA_VIZ.
• Spill failure ⇒ graceful fallback to today's inline-only return. Write-time
  opportunistic GC deletes spill files older than RESULT_SPILL_TTL_DAYS
  (default 14).
• dash/tools/public_sql.py is untouched (public surface has its own
  guarded pipeline; no full-result exposure there).

2. Agent: results API

New router app/results.py, registered in app/main.py:

• GET /results/{id} → {result_id, columns, row_count, truncated, offset, limit, rows} with ?offset= / ?limit= (limit cap 1.000/page).
• GET /results/{id}/csv → full CSV (RFC 4180, UTF-8 BOM,
  Content-Disposition: attachment), streamed.
• {id} validated against ^[a-f0-9]{32}$ (no path traversal); 404 on miss.

3. DATA_VIZ contract (additive, backward compatible)

• table.result_ref?: string — the result_id backing the table. UI uses it
  for full-data fetch + server CSV.
• tables?: DataVizTable[] — multi-table answers (e.g. per-provinsi AND
  per-kota). Single table stays valid; UI merges [table, ...tables].
• Guidance updates: inline only the top ≤15 rows per table, set total to the
  real row count, ALWAYS set result_ref when a query backs the table, and
  never apologize for "keterbatasan ruang" — the table UI handles the full
  set (pagination + unduh CSV lengkap).

4. Web: results proxy

/api/data/results/[id] + /api/data/results/[id]/csv (internal-web):
verify the staff session cookie in-route (middleware matcher only covers
/admin/*), validate id format, then proxy to DATA_DASH_ADMIN_URL.

5. Web: ResultTable v2

• Sortable column headers (numeric-aware over id-ID formatted strings:
  "17.795", "+18,4", "1.234,5").
• Client pagination (default 10/page) with pager; filter input (substring,
  all columns) when >10 rows; sticky header + horizontal scroll wrapper.
• With result_ref: "Muat semua N baris" fetches the full set through the
  proxy (page-capped at 5.000 client rows), and "Ekspor .csv" downloads the
  server CSV of the complete result. Without it: today's client-side CSV
  of inline rows.
• Pure helpers (lib/data-table/) unit-tested: id-ID numeric parse, sort,
  filter, paginate.

6. Web: multi-table + markdown-table upgrade

• DataAssistantMessage renders every table in [table, ...tables].
• Narrative markdown tables (ReactMarkdown components.table override)
  upgrade to the same interactive table card (sort/paginate/filter/CSV from
  cell text). This retroactively fixes existing threads, including the
  reference thread.

Non-goals (this iteration)

• New chart primitives (grouped/stacked multi-series) — existing single-series
  chart types stay; revisit with the dataviz skill when tackled.
• Public Tanya surface changes.
• Cross-session result persistence guarantees beyond the TTL.

Security

• Results endpoints exposed to the browser only via the session-checked
  internal-web proxy; agent port stays LAN/compose-internal.
• Result ids are unguessable (128-bit) and validated by regex server-side.
• CSV formula-injection guard (=, +, -, @ prefixes quoted with ')
  on the agent CSV emitter.
• Staff-only: L4/L5-guarded public_sql path deliberately unmodified.

Rollout

Gate (pnpm check + agent pytest) → build-and-ship.sh → deploy-staging.sh
→ E2E on staging: re-run the reference question, assert result_ref present,
full CSV row count ≫ inline rows, UI renders sortable paginated tables.