Paul Trowbridge
d1197df7d5
setup_sql/01_schema.sql was an idempotent bootstrap script — CREATE TABLE IF NOT EXISTS plus a tail of ALTER ... ADD COLUMN IF NOT EXISTS. It had no record of what any given database had applied, which is how a branch could declare col_meta.in_grain while the running database lacked it, with nothing able to detect the mismatch. The symptom would have been a confusing 'column "in_grain" does not exist' inside an unrelated request. Migrations-only, no hand-maintained current-state file to drift: - setup_sql/migrations/*.sql applied in filename order, recorded in pf.schema_version with a checksum. Split along the schema's actual evolution, so each column is declared exactly once — 01_schema.sql had grown to declare dim_group, dim_period_col and in_grain twice each. - lib/migrations.js holds the bookkeeping, shared by the CLI and the boot check. scripts/migrate.js provides up | status | baseline. - server.js refuses to start when the database is behind, listing what is pending. This converts silent drift into a clear boot message, which was the whole point. PF_SKIP_MIGRATION_CHECK=1 bypasses. - Four integrity guards, each verified to fire: a migration modified after being applied, one recorded as applied but missing from disk, one that would apply out of order, and a re-run when already current. - No IF NOT EXISTS on new migrations. The bookkeeping already guarantees one run each, and the guards hide ordering mistakes — that is exactly why 01_schema.sql had ALTERs sitting above the CREATE TABLE they depended on, broken for anyone installing from scratch. 0004 keeps the guard only because it was applied by hand before migrations existed. Verified: replaying all four migrations into a throwaway schema reproduces the live pf schema exactly, 41 columns, column for column. schema.generated.sql is a pg_dump snapshot for reading, refreshed by npm run schema:dump. It excludes the runtime fc_* tables and strips pg_dump's random \restrict token and version banner, so regenerating an unchanged schema yields an identical file rather than a spurious diff. pf.dim_period stays out of migrations — it is a parameterised data load (fiscal year start month), not a schema change. The dev database (ubm) has been baselined at all four migrations. Co-Authored-By: Claude Opus 5 <noreply@anthropic.com>
139 lines
10 KiB
Markdown
139 lines
10 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/`
|
||
- **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`.
|