Paul Trowbridge
654a368672
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>
130 lines
9.3 KiB
Markdown
130 lines
9.3 KiB
Markdown
# Pivot Forecast — CLAUDE.md
|
||
|
||
## What this app is
|
||
|
||
A web app for building named forecast scenarios against any PostgreSQL table. The workflow: load historical actuals as a baseline (optionally date-shifted into the forecast period), then apply incremental adjustments (scale, recode, clone) to build a plan. All changes are append-only, fully audited, and reversible by log entry.
|
||
|
||
Full spec: `pf_spec.md`
|
||
Data transport architecture options: `pf_perspective_options.md`
|
||
|
||
---
|
||
|
||
## Tech stack
|
||
|
||
- **Backend:** Node.js / Express (`server.js`)
|
||
- **Database:** PostgreSQL — isolated `pf` schema
|
||
- **Frontend:** React + Vite + Tailwind CSS in `ui/`; built output lands in `public/app/`
|
||
- **Pivot:** [Perspective](https://github.com/perspective-dev/perspective) (`@perspective-dev/*` distribution, **not** FINOS `@finos/perspective`) 4.4.0 loaded from CDN at runtime — see `PERSPECTIVE.md` for config/deploy guidance
|
||
- **Dev:** `npm run dev` (nodemon) in root; `npm run build` in `ui/`
|
||
|
||
---
|
||
|
||
## Project layout
|
||
|
||
```
|
||
server.js Express entry point; pg pool; type parsers for bigint/numeric
|
||
routes/
|
||
tables.js GET /api/tables, /api/tables/:schema/:tname/preview
|
||
sources.js Source registration, col_meta, SQL generation
|
||
versions.js Version CRUD, baseline/reference load, data stream
|
||
operations.js scale, recode, clone, undo — the core forecast ops
|
||
log.js GET /api/versions/:id/log, DELETE /api/log/:logid
|
||
lib/
|
||
sql_generator.js buildFilterClause, token substitution helpers
|
||
utils.js
|
||
setup_sql/
|
||
01_schema.sql pf schema DDL — run once to install
|
||
ui/src/
|
||
views/
|
||
Setup.jsx DB browser, source registration, col_meta editor
|
||
Baseline.jsx Version management, baseline workbench, reference load
|
||
Forecast.jsx Perspective pivot + operation panel (Scale/Recode/Clone)
|
||
Sidebar.jsx 3-step collapsible nav
|
||
StatusBar.jsx Source · version · row count · status
|
||
Timeline.jsx Date-range preview bar for baseline segments
|
||
```
|
||
|
||
---
|
||
|
||
## Database schema (`pf`)
|
||
|
||
- **`pf.source`** — registered source tables
|
||
- **`pf.col_meta`** — column roles: `dimension` | `value` | `units` | `date` | `filter` | `ignore`; `is_key` marks dimensions used in slice WHERE clauses; `dim_group` groups functionally dependent columns (e.g. date + its derived year/month dimensions); `dim_period_col` maps a dimension to a `pf.dim_period` column so date-adjacent values are derived at load time rather than copied raw; `in_grain` flags dimension/date columns that define the **display grain** (see below)
|
||
- **`pf.version`** — named forecast scenarios; `exclude_iters` (default `["reference"]`) blocks those iter values from all operations
|
||
- **`pf.fc_{tname}_{version_id}`** — one forecast table per version; contains both operational rows (`pf_iter = baseline|scale|recode|clone`) and reference rows (`pf_iter = reference`)
|
||
- **`pf.log`** — audit log; every write gets one entry; `slice` + `params` stored as jsonb
|
||
- **`pf.sql`** — generated SQL templates per source/operation; tokens substituted at request time
|
||
- **`pf.dim_period`** — calendar lookup table (2018–2035); one row per month keyed on `sdat` (month start date); provides cal/fiscal year, quarter, and month columns; populated by `setup_sql/gen_dim_period.sql` with a configurable fiscal year start month
|
||
|
||
### Key token substitution tokens
|
||
`{{fc_table}}`, `{{where_clause}}`, `{{exclude_clause}}`, `{{logid}}`, `{{pf_user}}`, `{{value_incr}}`, `{{units_incr}}`, `{{pct}}`, `{{set_clause}}`, `{{scale_factor}}`, `{{date_offset}}`, `{{filter_clause}}`
|
||
|
||
---
|
||
|
||
## Core data flow
|
||
|
||
### Initial load (Forecast view)
|
||
`Forecast.jsx` fetches col_meta first, then picks the endpoint:
|
||
|
||
- **grain mode** (any `in_grain` column) — `GET /api/versions/:id/agg`, rows pre-aggregated to the grain, table indexed on `pf_gkey`
|
||
- **raw mode** (no grain) — `GET /api/versions/:id/data`, raw forecast rows, table indexed on `pf_id`
|
||
|
||
Either way: Arrow IPC binary stream → `worker.table(buffer)` in Perspective WASM. `fetchArrow()` handles both.
|
||
|
||
**Why one batch (not streaming):** pg returns `bigint`/`numeric` as strings by default — type parsers in `server.js` coerce them to numbers. Per-batch Arrow encoding creates independent dictionaries that cause Perspective WASM to crash on dictionary replacement messages. Server accumulates all rows, emits one record batch.
|
||
|
||
### Display grain
|
||
Aggregating to the grain the pivot actually displays is the load-time fix — measured 534,902 → 6,154 rows on `osm_stack`. It keeps the **native** Perspective engine, so expand/collapse/depth/sort/filter all still work. Set the grain in Setup (`in_grain` per column); it is baked into `pf.sql` at Generate SQL time so load and operations agree. `grainOf()` in `lib/sql_generator.js` is the single definition of what the grain is — `Setup.jsx` and `routes/log.js` mirror it. Full design: `pf_spec.md` → §Display-grain pre-aggregation. Why not a DuckDB virtual server: `pf_perspective_options.md` → §Spike findings.
|
||
|
||
### Forecast operations
|
||
POST to `/api/versions/:id/{scale|recode|clone}` → SQL executed with `RETURNING *` → new rows returned as JSON → `pspTable.update(rows)` — no full reload. In grain mode the operation's final CTE aggregates its own new rows to grain first; since `pf_logid` is part of `pf_gkey` those keys are always new, so `update()` **appends** and the view re-sums.
|
||
|
||
### Undo
|
||
`DELETE /api/log/:logid` → removes rows by logid → `table.remove()` of the affected index values (`pf_gkeys` in grain mode, `pf_ids` in raw mode); the view re-sums. No full reload.
|
||
|
||
---
|
||
|
||
## Slice mechanics
|
||
|
||
When the user clicks a pivot cell, `perspective-click` fires. The handler in `Forecast.jsx` extracts `[col, '==', value]` filters from `detail.config.filter` — only `role = dimension` columns are kept as the slice. This slice populates the operation panel and is sent as the `slice` object in all operation POST bodies.
|
||
|
||
**Limitation:** computed columns created by Perspective's split_by (e.g. Month, YearDate) don't map back to raw rows — only native dimension columns work for slice extraction.
|
||
|
||
---
|
||
|
||
## Operation SQL patterns
|
||
|
||
All three operations follow the same structure: insert a `pf.log` row in a CTE, then insert forecast rows referencing its id. `{{where_clause}}` is built from the slice; `{{exclude_clause}}` blocks `exclude_iters` rows.
|
||
|
||
- **Scale** — distributes `value_incr`/`units_incr` proportionally across rows in the slice using window functions
|
||
- **Recode** — inserts negative rows (zero out original) + positive rows with `{{set_clause}}` dimension overrides; both share the same logid
|
||
- **Clone** — copies the slice with `{{set_clause}}` overrides and `{{scale_factor}}` multiplier; original untouched
|
||
|
||
`build_where()` validates every slice key against col_meta (only `role = dimension` allowed). Values are escaped but not parameterized — consistent with existing patterns, debuggable in pg logs.
|
||
|
||
---
|
||
|
||
## Light / dark mode
|
||
|
||
Theme state lives in `ui/src/theme.jsx` — a React context (`ThemeContext`) with a `ThemeProvider` that wraps the app in `main.jsx`.
|
||
|
||
- **Storage key:** `pf_dark` in `localStorage`; falls back to `window.matchMedia('(prefers-color-scheme: dark)')` on first visit
|
||
- **Toggle:** `setDark(d => !d)` in `StatusBar.jsx`; effect writes `localStorage` and toggles the `.dark` class on `<html>`
|
||
- **CSS:** `ui/src/index.css` defines CSS custom properties under `:root` (light) and `.dark`. All Tailwind color overrides are written as `.dark .bg-white { ... }` etc. — no Tailwind dark-mode config needed
|
||
- **Palette:** dark mode uses Perspective's "Pro Dark" colours (`--bg-primary: #242526`, panels `#2a2c2f`, gridlines `#3b3f46`, text `#c5c9d0`)
|
||
- **Perspective viewer:** `Forecast.jsx` calls `viewer.setAttribute('theme', dark ? 'Pro Dark' : 'Pro Light')` both on initial load and in a `useEffect([dark, versionId])` so the viewer stays in sync when the toggle fires
|
||
- **Consuming the theme:** `import useTheme from '../theme.jsx'` then `const { dark, setDark } = useTheme()`
|
||
|
||
## Known issues / active work
|
||
|
||
- Operation panel (Scale/Recode/Clone) SQL generation and dim_period JOIN are complete; UI wiring to API still needs completion
|
||
- Load progress bar is jittery — needs throttle (~10 updates/sec)
|
||
- Default pivot layout should be configurable per source (currently hardcodes first 2 dimensions)
|
||
- Source/version selection doesn't persist across page reload
|
||
- Col_meta / version schema drift: if col_meta roles change after a version's forecast table is created, SQL and DDL go out of sync — workaround is to delete and recreate the version
|
||
- Grain drift: changing `in_grain` after a load requires Generate SQL + a page reload, since the loaded table's index and columns are fixed at load time. `routes/log.js` derives the grain from live col_meta, so a grain changed mid-session yields `pf_gkeys` that don't match the loaded table and undo silently removes nothing
|
||
- Grain is static per source — a dimension left unflagged cannot be pivoted on. Dynamic per-cut grain (intersect the viewer's field set with the eligible set) is the additive next step; see `pf_spec.md` → §Display-grain pre-aggregation
|
||
|
||
## Deferred (not in v1)
|
||
Baseline replay (`replay: true` returns 501), approval workflow, territory filtering, export, version comparison, multi-DB connections. Live server-side aggregation (Path A / DuckDB virtual server) is parked on branch `spike/duckdb-virtual-server`.
|