Ship rows pre-aggregated to the grain the pivot displays instead of raw
forecast rows. This is Path B from pf_perspective_options.md: it keeps
Perspective's native WASM engine — so expand/collapse/depth/sort/filter
all still work — and fixes load time by cutting rows, not transport.
Measured on pf.fc_osm_stack_20 at pending_rep x customer x smon:
534,902 -> 6,154 rows (~87x), pf_gkey unique across all 6,154, and both
measures reconcile exactly to the raw totals.
The grain is static: flagged once per source in Setup and baked into the
stored pf.sql templates, so load and operations agree by construction.
Sources with no flagged column keep the previous raw-row behaviour, so
this is backward compatible.
- pf.col_meta gains in_grain; grainOf() in lib/sql_generator.js is the
single definition of the grain and is reused by routes/log.js.
- New get_agg template + GET /api/versions/:id/agg, generated only when a
grain is defined. Regenerating drops templates no longer produced, so
clearing the grain falls back to /data.
- scale/recode/clone now aggregate their own new rows to grain before
returning. Because pf_logid is part of pf_gkey those keys are always
new, so table.update() appends and the view re-sums — the Excel
pivot-cache pattern, no bucket recomputation.
- Undo reports pf_gkeys (RETURNING cannot take DISTINCT, so the delete
feeds a CTE that reduces to distinct keys); the client removes those
index values and the view re-sums.
- pf_gkey is concat_ws(chr(31), COALESCE(col::text, chr(30)), ...).
The separator and NULL sentinel are load-bearing: plain concat_ws skips
NULLs, so ('a',NULL) and (NULL,'a') would collide and silently merge two
groups into one indexed row.
- Forecast.jsx reads col_meta first to pick /agg vs /data; the Arrow
streaming logic is extracted to fetchArrow() since both share it.
- Setup.jsx gains a grain checkbox and shows the resulting grain.
- 01_schema.sql: move the col_meta ALTERs after its CREATE TABLE — they
referenced the table before it existed on a fresh install.
All six generated statements verified to plan against the real forecast
table; the in_grain column has been added to the dev database.
Co-Authored-By: Claude Opus 5 <noreply@anthropic.com>
Document the outcome of the Option C spike (server-side DuckDB as a
Perspective virtual server) and the resulting architecture decision.
pf_perspective_options.md:
- Spike findings: latency is excellent (~12ms round trip, 5-29ms
aggregation, ~1.5-1.9s to materialize 534,902 rows) but Perspective's
GenericSQLVirtualServerModel ignores group_by_depth and has no
ViewConfig field for per-node expansion state, so interactive
drill-down is not achievable. This affects options B, C and D alike
since they share that SQL model.
- Decision: the real lever is grain, not transport. Pre-aggregating to
display grain collapses 534,902 -> 4,642 rows (~115x) on osm_stack
while keeping the native Perspective engine, so expand/collapse/
depth/sort/filter continue to work.
- Two candidate designs (Path A live virtual server vs Path B
pre-aggregated extract) with the deciding question: do real cuts ever
exceed the browser's leaf-row ceiling?
pf_spec.md:
- Concrete Path B design: pf.col_meta.in_grain, GET /api/versions/:id/agg,
synthetic pf_gkey index, and the append-deltas write/undo model that
mirrors the prior Excel pivot-cache workflow.
Drop pf_ux_mockup.md — an ASCII mockup of UI that is now built; the
views in ui/src/views are the current reference.
Co-Authored-By: Claude Opus 5 <noreply@anthropic.com>
- units role is now optional; spec and CLAUDE.md reflect conditionality in SQL patterns
- pf.col_meta gains dim_group and dim_period_col fields (documented in both files)
- pf.dim_period calendar table added to schema docs
- pf.source default_layout column added to spec DDL
- Forecast table metadata columns corrected to pf_iter/pf_logid/pf_created_at throughout spec
- SQL patterns updated with correct CTE structure and RETURNING * to match generated code
- Project status updated to 2026-06-12; stale Arrow IPC open question removed
- todo.md deleted; open items retained in CLAUDE.md known issues
Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
- ui/: React + Vite + Tailwind app (Setup, Baseline, Forecast views, collapsible sidebar, status bar, canvas timeline)
- server.js: serve built UI from public/app/
- package.json: add build script (cd ui && npm run build)
- routes/sources.js: default new col_meta role to 'dimension' instead of 'ignore'
- .gitignore: exclude public/app/ build output
- pf_spec.md: update tech stack, nav, frontend section, and project status to reflect current implementation
Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
- HTML mockup with collapsible side nav, 3-step flow (Setup/Baseline/Forecast)
- Canvas-based timeline preview in baseline segment form
- Table peek modal, status bar, help popovers
- Spec updated: 3-step mental model, AG Grid replacement note
Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
Removes the redundant date_from/date_to/date_col fields from the request
body. Period selection is now expressed as a filter condition in the
filters array like any other condition. SQL pattern updated to match
(single {{filter_clause}} token instead of date_range + filter split).
Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
Period selection (date range, season, etc.) is now expressed as a
filter condition like any other — no separate date range section.
Preview uses a timeline/number-line bar instead of month chips.
Documents the unified filter builder approach.
Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
Covers architecture, data model, API routes, SQL patterns, and UI design.
Includes baseline workbench design with multi-segment additive loads,
filter role for col_meta, and date offset for projecting actuals into
the forecast period.
Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
