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>
GET /versions/:id/log, DELETE /log/:logid, and PATCH /log/:logid were
defined in both routes/operations.js and routes/log.js. operations.js is
registered first, so its handlers shadowed log.js entirely (dead code).
Move the authoritative implementations (value/units totals in GET,
closed-version 403 guard in DELETE) into log.js and remove the duplicates
from operations.js, keeping operations.js focused on the forecast ops.
No behavior change — the served handlers were already the operations.js
versions; they are now defined once.
Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>
- PATCH /api/log/:logid — saves note updates to pf.log (was missing, frontend call was silently failing)
- GET /api/versions/:id/log — joins fc_table to return row_count per entry so the change log modal shows rows affected instead of '—'
Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
Perspective table is now created with index: 'pf_id'. Delete endpoints
return the pf_ids they removed; the client calls table.remove(pf_ids)
in undoEntry. Avoids the full /data refetch that dominated undo time.
Co-Authored-By: Claude Opus 4.7 <noreply@anthropic.com>
- Sources page: left column with stacked DB tables + registered sources panels,
right column as full-height column mapping workbench
- Add compact table search, column search, table preview button, delete source button
- Rename fc_table system columns to pf_ prefix (pf_id, pf_iter, pf_logid,
pf_created_at) to avoid collisions with source table columns like 'id'
- Remove 'filter' col_meta role — any non-ignore column usable in baseline filters
- Replace structured filter row builder with free-form SQL WHERE clause textarea
and clickable column chips for insertion; fully flexible AND/OR logic
- Baseline segment cards now display raw WHERE clause text + offset
Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
