pf_app/pf_perspective_options.md

Perspective Architecture Options

This document weighs how the Forecast view should source data for the Perspective pivot. The current implementation hits practical limits on initial load (~30s for 350k rows × ~55 cols), and growth is expected. Choosing an architecture now should account for both read (initial pivot load + interaction) and write (forecasting operations that mutate rows).

---

Current architecture

Data flow

• Transport: GET /api/versions/:id/data returns the full forecast table as Apache Arrow IPC stream. Server-side: pg cursor (FETCH 10000) accumulates all rows, tableFromJSON builds an Arrow table, tableToIPC produces one record batch, response sent with Content-Length.
• Joined columns: /data LEFT JOINs pf.log to surface pf_note (the user's note for the operation that produced each row) and pf_op (baseline/scale/recode/clone). Joined at fetch time so note edits are always live. (Added in bf85f11.)
• Client: Streams the response body to a Uint8Array, hands it to Perspective's worker.table() (@perspective-dev/client@4.4.0 from CDN). Perspective's WASM engine owns the table in browser memory; all pivots/filters/group-bys run locally.
• Progress UI: Forecast view reads the response body via response.body.getReader() and shows received-bytes / total-bytes while loading.
• Forecasting writes:
  • scale/recode/clone POST → server INSERTs new rows with RETURNING * → client receives JSON rows → tableRef.current.update(rows) appends to Perspective's local table. Fast — no reload.
  • undo (DELETE) → server removes rows by pf_logid → client calls initViewer(...) which fully reloads the table.
  • baseline reload → currently also a full reload.

Why this specific shape (the bug history)

The current "accumulate all rows, emit one record batch" approach is not accidental. Two failure modes drove it:

1. pg returns bigint (oid 20) and numeric (oid 1700) as JS strings by default. That made tableFromJSON infer Dictionary<Utf8> for ~50 of 55 columns. Fix in server.js: register type parsers that coerce both to Number so Arrow infers Int/Float64.
2. Per-batch tableFromJSON creates independent dictionaries. When we streamed batches, the writer emitted ~1230 dictionary REPLACEMENT messages between batches. Perspective's WASM Arrow reader crashes on those (RuntimeError: memory access out of bounds). Fix: accumulate rows server-side, build one Arrow table, emit a single record batch. Reference comment lives in routes/operations.js near the cursor loop.

These two bugs explain the ~10–15s server stall before the progress bar appears: the server can't send byte 1 until every row has been fetched, encoded, and the buffer is sized for Content-Length. Any redesign of the read path needs to either solve the dictionary-replacement issue (streaming with stable dictionary IDs declared up front) or replace the transport entirely (e.g., Parquet, server-side virtual table).

Implication for any redesign

The incremental update path (table.update(rows)) is what makes operations feel snappy today. Whatever architecture comes next, writes need to stay incremental — or get even cheaper. Undo's full reload is already a known wart.

---

The options

A. Stay client-side WASM; optimize the encode path

Keep the architecture. Replace the slow pieces.

• Encode: drop tableFromJSON. Build Arrow vectors directly from cols_meta types (typed arrays for numerics, dictionary builders for strings). Eliminates per-row type inference.
• Stream: declare schema up front, send dictionaries once, stream record batches as they come off the cursor. Progress bar starts within ~1s.
• Trim: request-level ?cols= parameter so the server can return only the columns the active layout needs.
• Writes: unchanged — table.update(rows) keeps working.
• Undo: same path; same wart. Could be improved by surfacing a table.remove(pf_ids) instead of initViewer.

Aspect	Impact
Initial load	~3–5× faster server encode + parallel transfer; bar appears in ~1s
Interaction	Unchanged (already instant)
Writes	Unchanged (already fast)
Browser memory ceiling	Still limited by Perspective WASM (~1–2M rows is the rough wall)
Code change	Medium: new builder code in routes/operations.js, schema declaration; UI mostly unchanged
New runtime deps	None

Right answer if: dataset stays under ~1M rows and the goal is "make it faster without rearchitecting."

---

B. DuckDB-WASM in the browser (Parquet load + DuckDBHandler)

Replace the Arrow IPC payload with a Parquet file. Browser loads it into DuckDB-WASM. Perspective's DuckDBHandler (from @perspective-dev/client/dist/esm/virtual_servers/duckdb.js) backs the viewer — every pivot interaction becomes a SQL query against the local DuckDB-WASM instance. Perspective ships the view-config-to-SQL translator; no custom code there.

• Initial transfer: Parquet for a forecast table is typically ~10–30 MB for 350k rows (vs. ~80–150 MB for Arrow IPC). Smaller download, no server-side tableFromJSON.
• Encode: server-side. DuckDB on the server can COPY (SELECT ... FROM postgres_scan(...)) TO 'foo.parquet', or pre-stage Parquet on each forecast write. Either way, no Node-side Arrow encode.
• Interaction: instant — local SQL on a columnar engine. No round trips.
• Writes: this is the hard part. After a scale/recode/clone, the server has new rows in pg but DuckDB-WASM has a stale snapshot. Options:
  1. Server returns new rows as Arrow → client does INSERT INTO forecast SELECT * FROM arrow_view in DuckDB-WASM, then notifies the DuckDBHandler to refresh views.
  2. Re-export Parquet → re-fetch. Simple but wasteful for small incremental ops.
  3. Maintain a delta log → client replays inserts/deletes by pf_logid.
• Undo: DELETE FROM forecast WHERE pf_logid = $1 against DuckDB-WASM, then refresh. Strictly faster than the current full reload.

Aspect	Impact
Initial load	Smaller payload + fast WASM ingest; likely 3–5× total
Interaction	Instant (local SQL) — same as today
Writes	New write-sync layer required (medium effort)
Browser memory ceiling	DuckDB-WASM handles 10M+ rows comfortably
Code change	Significant: new server route for Parquet, new client wiring, write-sync code
New runtime deps	DuckDB on server (Node-API or shell), @duckdb/duckdb-wasm on client

Right answer if: dataset will grow past ~1M rows but you still want local interaction speed, and you're willing to write the write-sync layer.

---

C. Server-side DuckDB as a virtual server (no client load)

DuckDB lives on the Node server. Browser uses a VirtualServerHandler implementation that proxies Perspective's view requests (tableMakeView, viewGetData, viewGetMinMax, tableSchema) to a /perspective endpoint. Server runs SQL against DuckDB which queries pg directly via postgres_scanner, or against a Parquet copy.

• Initial transfer: essentially zero. Schema + first viewport only.
• Interaction: every drag/filter/group-by is a network round trip. 50–200ms typical. Imperceptible for most operations; noticeable on rapid drag interactions.
• Writes: simplest. Operations write to pg as today. DuckDB queries pg live (via postgres_scanner) so it always sees current state. No client-side state to sync.
• Undo: same as writes — server state is the source of truth.

Aspect	Impact
Initial load	<1s regardless of dataset size
Interaction	50–200ms round trip per interaction
Writes	Simple — single source of truth on server
Browser memory ceiling	Irrelevant — data never enters the browser
Code change	Significant: custom VirtualServerHandler that talks to a new /perspective endpoint; server-side translator wiring
New runtime deps	DuckDB on server

Right answer if: dataset will outgrow browser memory (10M+ rows) or multiple users need to see real-time shared state. Pays an interaction latency tax forever.

Note: Perspective-dev also ships a Python virtual_servers/duckdb. If you're willing to add a Python sidecar, you may not need to write the JS-side handler — just stand up the Python server. Significant infra change for a Node-based app.

---

D. Hybrid — DuckDB-WASM read, pg write, server-pushed deltas

Same browser stack as B, but writes flow differently. After a forecast operation, the server pushes back an Arrow batch of new rows (or a list of pf_logids to delete for undo). The client applies it to DuckDB-WASM via SQL and refreshes the Perspective view. No re-export of Parquet on every write.

This is essentially B with the write-sync layer specified. Splitting it out because the write contract is the architectural decision worth deciding explicitly:

• Insert deltas: server returns new rows as Arrow IPC, client does INSERT INTO forecast SELECT * FROM arrow_view. Already trivial in DuckDB-WASM.
• Delete deltas: server returns {deleted_logid: N}, client does DELETE FROM forecast WHERE pf_logid = N.
• Replace deltas (e.g., note edits): if pf_note is joined at fetch time (current state after bf85f11), edits are invisible until refetch. Either accept that, or store note on the row and UPDATE.

This is the cleanest end state for a forecasting app: bulk read once, incremental sync after.

---

Comparison

	Current	A: optimize	B/D: DuckDB-WASM	C: server DuckDB
Initial load (350k rows)	~30s	~5–10s	~3–8s	<1s
Interaction latency	0	0	0	50–200ms
Write feedback	instant	instant	instant (after sync)	instant
Undo cost	full reload	full reload (or fix)	local DELETE	server-side
Browser memory ceiling	~1M rows	~1M rows	10M+ rows	none
New deps	—	—	DuckDB (server + WASM)	DuckDB (server)
Code change	—	medium	significant	significant
Risk surface	low	low	medium (write sync)	medium (translator wiring)

---

Open questions to resolve before choosing

1. Expected dataset size 12 months out. If it stays at ~350k–1M rows, option A is enough. If it goes to 5M+, A is dead in the water.
2. Parquet caching strategy if going B/D. Re-export on every write is wasteful; delta replay is more code. Pick one explicitly before building.
3. Multi-user scenarios. If two users edit the same version concurrently, options B/D need a mechanism for one user's writes to appear in another's local DuckDB-WASM. Option C gets this for free.
4. Python-or-Node decision for server-side DuckDB. Perspective-dev's Python virtual server might let you skip writing a translator entirely — at the cost of a Python runtime alongside Node. Worth investigating before committing to a JS-side custom handler.
5. Should the spec move? The spec mentions DuckDB only as a faster bulk-encode path (option A-ish, server-side). Options B/C/D are architectural shifts the spec doesn't contemplate. Whatever's chosen should be written into pf_spec.md so the reasoning isn't lost again.

---

Recommendation framing (not a decision)

• If the immediate problem is "30s loads feel bad": option A. It's the smallest change with the highest perceived impact and doesn't paint you into an architectural corner.
• If you're already planning for data growth: option D (DuckDB-WASM + delta sync). It's the right end state for a single-user-per-version forecasting tool with mid-to-large datasets.
• If multi-user real-time becomes a goal: option C. Pay the latency tax once and have a cleaner data model.

A reasonable phased path: do A first (fast, low risk, ships value this week), live with it while planning, then move to D when row counts demand it. C is a different shape and probably not warranted unless multi-user emerges as a requirement.

---

Spike findings — Option C (server-side DuckDB virtual server), 2026-06-18

A working spike was built to measure Option C on the real ~535k-row forecast (pf.fc_osm_stack_20, version 20). It is opt-in behind ?engine=duck and does not touch the default client-side WASM path.

Architecture built. Perspective's VirtualServer + GenericSQLVirtualServerModel run in the browser (reusing the already-loaded viewer WASM) and turn each interaction into SQL. That SQL is POSTed to a new server endpoint (routes/perspective.js, POST /api/perspective/sql) and executed against a persistent in-process DuckDB that has the forecast table materialized from Postgres. Only generated SQL + the aggregated viewport (Arrow IPC) cross the wire. Files: routes/perspective.js, ui/src/duckServerHandler.js, a branch in Forecast.jsx, and the duckdb node dependency.

Latency — excellent, not the bottleneck.

Measure	Result
Materialize 534,902 rows into DuckDB (one-time, startup)	~1.5–1.9 s
Pivot aggregation in DuckDB (1-col / 3-col rollup / filtered)	5–29 ms
Per-query round trip incl. HTTP + Arrow (browser ↔ server)	~12 ms
Initial load (no rows over the wire — just schema + first viewport)	sub-second

This is far below the doc's earlier 50–200 ms estimate and dwarfed by today's ~2-minute cold load on this dataset.

Blocking limitation — no interactive group-tree drill-down. Perspective's generic SQL virtual server cannot drive the expandable rollup tree this app is built around:

• group_by_depth is ignored by the SQL generator — verified by running GenericSQLVirtualServerModel headlessly: the generated tableMakeView / viewGetData SQL is byte-identical for depth 0/1/2/undefined. It always emits the full GROUP BY ROLLUP(...).
• The imperative view.set_depth() emits a ViewSetDepthReq the virtual server does not handle → Abort(): Unhandled request.
• ViewConfig has no field to express per-node expansion state, so expand/collapse cannot be communicated to the SQL model at all. The server returns a static full rollup; the grid cannot reflow it. (Per-node +/- fires the SQL round trips but produces no visual change.)

What makes expand/collapse/depth work today is Perspective's own WASM view engine computing the tree. The virtual server replaces that engine with the SQL model, which does not implement dynamic expansion.

This limitation is not Option-C-specific. Options B, C, and D all use the same DuckDBHandler / GenericSQLVirtualServerModel. Any DuckDB-backed virtual server therefore inherits the same loss of interactive tree expand/collapse/depth. Only Option A keeps rows in a native Perspective Table, preserving full interactivity.

Implementation gotchas worth recording:

• The SQL generator must be constructed from the initialized viewer WASM module (customElements.get('perspective-viewer').__wasm_module__), not the bare @perspective-dev/client default export — the latter's wasm glue is undefined and new perspective.GenericSQLVirtualServerModel() throws.
• The handler must implement both viewColumnSize (→ COUNT(*) FROM (DESCRIBE v), column count) and viewSize (→ COUNT(*) FROM v, row count). The reference duckdb.ts only shows viewColumnSize; omitting viewSize breaks row-count rendering.
• DuckDB Arrow IPC output needs INSTALL arrow FROM community; LOAD arrow; (the to_arrow_ipc table function is not built in to 1.x).
• DuckDB reads the pg forecast tables directly via postgres_scanner (ATTACH ... TYPE postgres, READ_ONLY); the spike materializes a native copy so aggregations are pure-columnar (writes would require a re-materialize/refresh).

Conclusion. The latency case for server-side DuckDB is strong, but the generic SQL virtual server cannot support this app's expandable pivot UX. Choosing any B/C/D path means either (a) writing a custom virtual server that implements depth/expansion in SQL (rebuilding what the generic model omits), or (b) accepting flat/fully-rolled-up views with no interactive drill-down. To keep the current UX, Option A (optimize the encode/transport, keep the native Perspective table) is the path.

---

Decision — display-grain pre-aggregation (2026-06-18)

The spike showed the real lever isn't the transport, it's the grain: the browser pivots at a coarse display grain (e.g. rep × customer × month) but we ship raw transaction rows. Pre-aggregating to that grain server-side (DuckDB or plain pg GROUP BY) collapses 534,902 → 4,642 rows (≈115×) on osm_stack, and adding more dimensions barely moves it (4,642 → 4,743 with director + channel) because the raw detail (part/plant/day/currency…) all rolls up.

This is the chosen direction. It is essentially "Option A done right" — reduce rows by aggregating to grain, not just trim columns — and it keeps the native Perspective engine, so expand/collapse/depth/sort/filter keep working (the capability B/C/D sacrifice). The DB does the heavy aggregation (~25 ms); the browser receives a few thousand native rows.

Why it beats the alternatives here:

• vs. current: ~250 MB / ~2 min → a few hundred KB / sub-second; render instant.
• vs. B/C/D (DuckDB virtual server): keeps interactive drill-down; no per-drag round trips; no custom SQL generator to own.
• The undo "full reload" wart disappears — a re-pull is now ~25 ms.

Trade-offs accepted: additive measures only (sums exact; averages = sum+count; distinct-counts not pre-aggregatable — fine for forecast); a view only supports the dimensions in its grain (handled by re-fetching at a new grain when the user changes group/split/filter, or by choosing a grain that covers the pivot set while avoiding high-cardinality dims like part).

This is a port of the prior Excel model. The forecasting process previously ran in Excel: pre-aggregated rows were sent to a data tab, pivot tables cut them locally any way the user wanted, double-clicking a pivot cell let VBA capture the slice and present an adjustment UI, and incremental rows were built in the DB, appended to the data tab, and the pivot cache refreshed to include them. That maps 1:1 onto native Perspective (pivot cache = the WASM view) — which is why the native engine matters and the virtual server was the wrong fit: Excel never re-queried the DB on every pivot move.

Write/read model (append deltas, let the view sum — the pivot-cache pattern; single source of truth = pg raw rows):

• Initial load — GET /api/versions/:id/agg, GROUP BY grain × pf_iter × pf_logid → Arrow → native table indexed by a synthetic pf_gkey.
• Write (scale/recode/clone) — operation's final CTE returns just the new pf_logid's rows aggregated to grain → table.update() appends them; the view re-sums. No bucket-total recomputation (keying on pf_logid keeps each op's contribution a distinct row, so appends accumulate).
• Delete (undo) — table.remove() that logid's pf_gkeys; the view re-sums. No re-aggregation, no emptied-bucket handling. (Or re-pull /agg, now cheap.)

Concrete design (schema flag col_meta.in_grain, the /agg endpoint, the synthetic pf_gkey index, and the operation/undo SQL templates) is specified in pf_spec.md → §Display-grain pre-aggregation.

---

Two candidate designs for server-side aggregation (open — 2026-06-18)

Both keep Perspective as the client and DuckDB/pg as the aggregation engine over raw pg rows (the source of truth), and both rely on additive measures (sums; avg = sum+count; no distinct-count). The target experience for both is "Excel PivotTable against a Power BI / SSAS tabular model": a good-looking pivot driven by a fast columnar engine. They differ in where the rollup happens and therefore what crosses the wire — which trades off against each other.

Path A — Live server aggregation (virtual server)

• Reads: Perspective runs as a virtual server; each interaction → SQL → DuckDB → just the viewport cells. Requires a custom depth-aware SQL generator replacing GenericSQLVirtualServerModel (honor group_by_depth, reproduce the __ROW_PATH__/__GROUPING_ID__ contract, and eventually split_by's dynamic pivot + expression columns).
• Writes/undo: write raw rows to pg; the next query reflects them (re-query / invalidate) — no client-side row bookkeeping.
• Pros: unbounded dataset — only the visible viewport ever ships; always live; the truest "any cut, any size."
• Cons: the generator is a real component to build and own; per-node expand/collapse is a hard frontier (the protocol delivers only a global depth to the JS handler, not per-node expansion state — that needs a Rust/protocol fork); per-interaction latency (~12 ms + RTT, measured in the spike).
• Excel analog: live SSAS / Power BI MDX connection.

Path B — Pre-aggregated extract per cut (native table)

• Reads: the server ships a flat GROUP BY to the current pivot's field grain; the result loads into a native Perspective table, and Perspective's own engine does all rollup/expand/collapse/depth/sort locally, with zero calls. A server round trip happens only when the field set changes (add/remove a dimension, or filter on a non-grain dimension) — not on expand/collapse/sort or moving a field between rows and columns.
• Writes/undo: append grain-aggregated delta rows keyed by pf_logid; the view sums them. Undo removes that logid's rows. (The append model — concrete design in pf_spec.md → §Display-grain pre-aggregation.)
• Pros: the server SQL is trivial and fully owned (no tree contract to reproduce); zero-latency native interaction; native look/feel; payload is small (only the pivot's columns, only distinct grain rows).
• Cons: ships the entire current-grain leaf set, not just the viewport — so a high-cardinality cut (anything × part-like) can be large and eventually hit Perspective's ~1–2M-row WASM ceiling; pays a re-fetch when the field set changes.
• Excel analog: Power Query extract → pivot cache.

Comparison

	Path A — live virtual server	Path B — pre-agg extract
Where rollup happens	server, per interaction	client (native), per cut
Crosses the wire	visible viewport only	whole current-grain leaf set (pivot cols only)
Expand/collapse/sort	server round trip each	local, zero calls
Server-side code	custom SQL generator (large)	flat GROUP BY (trivial)
Dataset ceiling	none (viewport only)	~1–2M rows per cut (WASM)
Per-node expand	needs protocol/Rust fork	native (free)
Interaction latency	~12 ms + RTT	0 (local); re-fetch only on field-set change

The deciding question

It comes down to one thing: do real cuts ever produce a leaf grain too large for the browser?

• If practical cuts stay under ~1M leaf rows → Path B — dramatically less code, all of it yours, native feel, no generator to maintain.
• If you need truly unbounded any-cut-any-size → Path A — at the cost of building/owning the generator and accepting the per-node-expand frontier.

Hybrid (future option): default to B; when a requested grain's distinct count exceeds a threshold, fall back to A (virtual server) for that cut. Best of both, more moving parts. Worth noting but not for a first build.

Next step toward deciding: map which dimensions are safe in a Path-B grain vs. which are high-cardinality landmines (part, day-level dates, etc.) on the real data — that tells us where B's ceiling actually bites.