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>
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>
- SQL generator no longer requires a units col; recode/clone/scale omit units
expressions when none is configured in col_meta
- Source registration validation drops units from required roles (value + date
are the only hard requirements)
- DELETE /api/sources/:id returns 409 when existing versions reference the source
- Setup.jsx surfaces the 409 error via flash instead of silently failing
Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
- col_meta gets dim_period_col field: maps a dimension column to its pf.dim_period counterpart (e.g. year -> cal_year, month -> cal_month)
- When the date column is is_key of a dim_group and any sibling dimension has dim_period_col set, baseline and reference SQL JOIN pf.dim_period on the shifted date instead of copying raw source values
- No dim_period config = identical SQL to before (fully backwards compatible)
- Setup UI: period col input in col_meta editor, enabled for dimension columns with a dim_group set
- Schema migration applied: dim_period_col text null on pf.col_meta
Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
Reference segments can now apply a date offset just like baselines.
SQL template gains the {{date_offset}} token; both POST /reference and
PUT baseline/:logid pass it through. Existing sources need to
regenerate SQL to pick up the new template — old stored reference SQL
ignores the token (preserving prior verbatim behavior). The Baseline
form drops the "dates land verbatim" hint and shows the offset
control for both segment types.
Editing a segment now color-codes the source row amber with a ring
and tints the form border + header amber so the active connection is
visually obvious. Header label reads "Edit segment #3 — baseline —
note" instead of just "#43" (the internal log id).
Co-Authored-By: Claude Opus 4.7 <noreply@anthropic.com>
- Fix perspective-click handler to use event filter triples instead of
__ROW_PATH__ — Perspective encodes row position as [col,'==',val] in
detail.config.filter
- buildWhere now skips unrecognised slice keys (e.g. pf_iter) instead of
throwing, so only dimension columns reach the WHERE clause
- Add draggable resize handle on the operation panel (160–480px)
Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
- Baseline.jsx: merge Reference section into Add Segment form with baseline/reference toggle; segment rows now clickable to expand stored WHERE clause + timeline; date filter inputs use type="date" for date-role columns
- Timeline.jsx: add type prop ('baseline'|'reference'); reference band uses purple; single-band height shrinks to 52px; canvas uses requestAnimationFrame to fix offsetWidth=0 on mount
- operations.js: reference route now accepts where_clause like baseline (drops date_from/date_to)
- sql_generator.js: reference SQL template uses {{filter_clause}} instead of hardcoded BETWEEN
Note: existing sources need Generate SQL re-run to pick up the new reference template.
Co-Authored-By: Claude Sonnet 4.6 <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>
- New Baseline nav view replaces the simple Load Baseline modal
- Baseline loads are now additive; each segment is independently undoable
- Filter builder: any date/filter-role column, full operator set
- Timeline preview shows source → projected period bars for date BETWEEN filters
- Clear Baseline action deletes all baseline rows and log entries
- DELETE /api/versions/:id/baseline route
- buildFilterClause() added to sql_generator
- filter role added to col_meta editor
- Reminder: re-run generate-sql for each source after this change
Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
The baseline operation now accepts a date_offset interval (e.g. "1 year",
"6 months") and applies it to every date when inserting rows, shifting
historical actuals into the target forecast period.
SQL: {date_col} + '{{date_offset}}'::interval)::date at insert time.
Route: defaults to '0 days' if omitted so existing calls are unaffected.
UI: year/month spinners with a live before→after month chip preview so
the projected landing period is visible before submitting.
Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
Instead of re-fetching all forecast data after scale/recode/clone/reference,
the routes now return the inserted rows directly. The frontend uses ag-Grid's
applyTransaction to add only the new rows, eliminating the full reload round-trip.
Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
