pf_app/CLAUDE.md

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 (@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/
• Schema: forward-only SQL migrations in setup_sql/migrations/, applied by npm run migrate and tracked in pf.schema_version. server.js refuses to start when the database is behind. See setup_sql/README.md

---

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/
  README.md             migration workflow — read before changing the schema
  migrations/           ordered .sql, applied once, tracked in pf.schema_version
  gen_dim_period.sql    parameterised calendar load (not a migration)
  schema.generated.sql  pg_dump reference snapshot; generated, never edited
scripts/
  migrate.js            migration CLI (up | status | baseline)
  schema-dump.js        regenerates schema.generated.sql
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
• pf.schema_version — applied migrations (filename + checksum); owned by the migration runner, never edited by hand

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
• Migrations are forward-only — there are no down migrations. Rolling back a schema change means writing a new migration that reverses it
• 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.