Filter aggregates with the rest of the layout

cleanLayout's job is to let a layout outlive the columns it names, and it
walked every config field that carries a column name except aggregates --
which carries one as its key and, in the multi-arg form, a second as the
weight. Empty in practice today, so nothing was breaking; the first
explicit aggregate would have made a later column change abort the whole
restore rather than lose one entry.

Co-Authored-By: Claude Opus 5 (1M context) <noreply@anthropic.com>

CLAUDE.md

@@ -14,7 +14,7 @@ Data transport architecture options: `pf_perspective_options.md`
 - **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`) 5.2.0, **bundled inline via the `/inline` entrypoints — never from a CDN** (the 4.x CDN bundle resolves its server WASM to an unversioned path and silently pulls whatever is newest). See `PERSPECTIVE.md`.
+- **Pivot:** [Perspective](https://github.com/perspective-dev/perspective) (`@perspective-dev/*` distribution, **not** FINOS `@finos/perspective`) 5.4.0 from a **patched build vendored in `ui/vendor`** — see its README for what is patched and which host-tool versions the rebuild needs; **bundled inline via the `/inline` entrypoints — never from a CDN** (the 4.x CDN bundle resolves its server WASM to an unversioned path and silently pulls whatever is newest). See `PERSPECTIVE.md`.
 - **Dev:** `npm run dev` (nodemon) in root; `npm run build` in `ui/`
 
 ---
@@ -30,6 +30,7 @@ routes/
   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
+  layouts.js            Named pivot layouts — list per version, create, patch, delete
 lib/
   sql_generator.js      buildFilterClause, token substitution helpers
   auth.js               scrypt hash/verify, requireAuth, sessionUser; `node lib/auth.js hash` CLI
@@ -45,6 +46,7 @@ ui/src/
     Baseline.jsx        Version management, baseline workbench, reference load
     Forecast.jsx        Perspective pivot, selection handling, operation dispatch
   components/
+    LayoutMenu.jsx      The Layout ▾ control — Published / Mine, with the write actions
     OperationPanel.jsx  The adjustment workbench — ledger + scale/recode/clone forms
     BridgeView.jsx      Baseline → current waterfall by tag (exports buildSteps/layoutSteps)
     Sidebar.jsx         3-step collapsible nav
@@ -57,15 +59,43 @@ ui/src/
 ## 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.col_meta`** — column roles: `dimension` | `value` | `units` | `date` | `filter` | `ignore`; `dim_group` groups functionally dependent columns (e.g. a part and its attributes, or a date and 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); `is_key` is described under §`is_key` 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.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`). Indexed on `pf_logid`, which is how undo, the change-log aggregate and the grain key all find their rows — without it each is a sequential scan of the whole table. Tables created before that index was added do not have it.
 - **`pf.log`** — audit log; every write gets one entry; `slice` + `params` stored as jsonb
+- **`pf.layout`** — named Perspective view configs; see §Pivot layouts
 - **`pf.sql`** — generated SQL templates per source/operation; tokens substituted at request time
 - **`pf.app_user`** — login accounts; scrypt `pass_hash`, `is_active`, `last_login_at`
 - **`pf.session`** — express-session store (connect-pg-simple layout)
 - **`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
 
+### `is_key`
+
+Read in four places, and **not** the one it sounds like — slices are validated
+against `filterCols`, which is every `dimension` plus every `date` column
+regardless of `is_key`.
+
+1. **The key of a `dim_group`** — `resolveGroup()` in `routes/sources.js` takes
+   `members.find(c => c.is_key)`, the column every other member is keyed on for
+   `pf.dim_member`
+2. **Value completion** — only `is_key` columns get a dropdown, and
+   `/sources/:id/values/:col` refuses anything else
+3. **Sibling autofill** — fires on blur only when `is_key && dim_group`
+4. **The `dim_period` anchor** — `role === 'date' && is_key && dim_group` picks the
+   date whose siblings are derived from the calendar
+
+Uses 2 and 3 want several columns flagged; use 1 needs exactly one per group.
+**When a group has more than one, `.find()` silently takes the lowest `opos`.**
+That is not hypothetical: `segment_new` (opos 12) outranked `part` (opos 15) in
+the `part` group, so a refresh keyed on a column that is null throughout, matched
+nothing, and reported success with zero members. `customer`'s group has four keys
+and picks the right one only by `opos` luck.
+
+The two meanings want separating — a per-group key choice, or a rule that the
+group key is the column named by the group (which these groups nearly follow
+already, except `sdate` → `sdate_e`). Until then, a refresh that finds two keys
+should refuse rather than guess.
+
 ### 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}}`
 
@@ -86,14 +116,257 @@ Either way: Arrow IPC binary stream → `worker.table(buffer)` in Perspective WA
 ### 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.
 
+### Segment and note columns
+`/data` and `/agg` both LEFT JOIN `pf.log` and emit two columns the forecast table
+does not itself carry:
+
+- **`pf_segment`** — `pf.log.label`, else `tag`, else `note`, else `Unlabeled`;
+  `'99 - Adjustments'` for an adjustment that has no label of its own
+- **`pf_note`** — the free text on a scale/recode/clone; null on loads
+
+They are deliberately separate: commingling a segment name with an adjustment note
+makes neither pivotable. In grain mode `pf_logid` is part of the grain, so the join
+adds no rows. The operation routes stamp the same two fields onto the rows they push
+back incrementally, since those come from `RETURNING *` and would otherwise arrive
+without them.
+
+### Column order is stored text
+
+Perspective orders column groups by the value string, and `SortDir`'s `col asc` /
+`col desc` only reverses that — so Prior Year → Plan → Actual → Forecast is
+alphabetical in neither direction and expressible as neither. A `"01 - "` prefix
+is the only lever, and it lives in **`pf.log.label`** (and `pf.log.bucket`),
+typed by whoever names the segment. Nothing derives it.
+
+`SEGMENT_EXPR` / `BUCKET_EXPR` / `NOTE_EXPR` in `lib/sql_generator.js` are the
+single definition, shared with the `/data` cursor in `routes/operations.js` —
+`/agg` is generated, `/data` is not, and the two have to agree.
+
+The one hardcoded ordinal is `ADJUSTMENT_SEGMENT` = `'99 - Adjustments'`, which
+keeps unlabelled adjustments after every *numbered* segment. That proviso is the
+whole scheme, not a caveat on it: ordering is string ordering, so `99` only lands
+last once the loads carry `01`–`0n`, and an unnumbered segment sorts after it
+(digits precede letters — `9` is `0x39`, `A` is `0x41`). The old `'(adjustment)'`
+sorted *first* for the same reason read the other way, `(` being `0x28`.
+Unlabelled loads read plain `Unlabeled` and so land at the very end, which is
+where a segment nobody has named belongs. Labelling an adjustment's own log row
+overrides the fallback, which is how one kind of adjustment is split out from the
+rest.
+
+### Hardcoded display names
+
+Every name the pivot can show that does not come from `pf.log`. If a segment or
+bucket appears under a name nobody typed, it is one of these. All three are in
+the `DISPLAY DEFAULTS` block at the top of `lib/sql_generator.js`, exported so
+the `/data` cursor and the operation routes' incremental row stamps use the same
+values the generated `/agg` does.
+
+| constant | `pf.version` column | built-in | applies to |
+|---|---|---|---|
+| `ADJUSTMENT_SEGMENT` | `adjustment_segment` | `99 - Adjustments` | `pf_segment` for a scale/recode/clone with no `label` |
+| `ADJUSTMENT_BUCKET` | `adjustment_bucket` | `04 - Forecast` | `pf_bucket` for a scale/recode/clone with no `bucket` |
+| `UNLABELED_LOAD` | `unlabeled_load` | `Unlabeled` | `pf_segment` and `pf_bucket` for a load with no `label`, `tag` or `note` |
+
+Each is set per scenario on the Baseline page, under **Fallback names**; blank
+falls back to the built-in. Anything typed on the log row overrides both, so
+none of these appears once a segment is named.
+
+**Why the join rather than a token.** `pf.sql` is keyed on
+`(source_id, operation)` — one template shared by every version of a source — so
+a value baked in at Generate SQL time could not vary by version, and
+regenerating for one version would silently change the others. The names are
+therefore read through `VERSION_JOIN` at query time, which also means changing
+one takes effect on the next load with nothing regenerated.
+
+The built-ins are still a convention guess: `ADJUSTMENT_BUCKET`'s `04 - ` only
+suits one numbering. A version that numbers its buckets differently sets its
+own rather than inheriting that.
+
+**What this replaced.** The prefix used to be computed client-side, as
+Perspective expression columns (`pf_bucket_ord`, `pf_segment_ord`) built from
+`pf.log.seq` and `pf.version.bucket_order`. It ordered the pivot and nothing
+else, so every other reader disagreed with it; `restore()` replaces
+`expressions` wholesale, so it had to be re-applied after every layout load; and
+ExprTK's string scanner tests each *byte* with `isprint()`, so a label
+containing anything outside printable ASCII could not be ordered at all (`·` is
+two bytes, of which `isprint(0xC2)` is false). `DEAD_ORDER_EXPRS` in
+`Forecast.jsx` strips the expression names out of layouts saved under that
+scheme. `pf.log.seq` and `pf.version.bucket_order` are no longer read; the
+columns remain.
+
+Relabelling now needs a page reload to show, because the label is part of the
+aggregated row rather than something the pivot can re-derive. Editing a label
+afterwards is a `PATCH /api/log/:logid` and needs nothing regenerated, but a
+source registered before this change needs **Generate SQL** run once, so its
+stored load templates write `label` and `bucket` onto the log row at all.
+
 ### 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.
 
+### Price or volume (`plug`)
+A sales figure alone does not say which of price or volume moved, so scale takes
+`plug` — `'price'` (default, volume holds) or `'volume'` (price holds, units scale
+in proportion). Resolved in `resolveIncrs()` in `routes/operations.js`; the panel
+only offers it when the edit is dollars-only, because naming units or price has
+already answered it. The semantics come from the predecessor Excel model,
+`/opt/forecast_api/VBA/fpvt.frm` → `calc_val` / `calc_price`:
+
+```
+plug volume:  pchange = fVal/(pVal+bVal); fVol = (pVol+bVol)*pchange
+plug price:   fVol = pVol + bVol
+```
+
+A `target_price` with a `target_units` alongside is that form's Edit Price mode,
+where both are inputs and dollars fall out. The ledger's **Result** line previews
+value, units and price together using the same rules, so what you see is what
+gets written.
+
 ### 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.
 
 ---
 
+## Axis depth (collapse / expand)
+
+Both axes collapse the same way: a **depth in `ViewConfig`**, set through
+`restore()`.
+
+- **Rows** — `group_by_depth`, driven by the `EXPAND 0 1 2 3` buttons via
+  `applyDepth()`
+- **Columns** — `split_by_depth`, driven by the `COLUMNS` buttons via
+  `applySplitDepth()`
+
+Both are **1-based**: they count the levels to show, where the imperative
+`view.set_depth()` counts the boundary below them. `server.cpp` does
+`ctx1->set_depth(row_pivot_depth - 1)` and
+`ctx2->set_depth(HEADER_COLUMN, column_pivot_depth - 1)`, so the toolbar sends
+`d + 1` and subtracts one again when reading a layout back.
+
+Because a depth lives in the config, it survives every view rebuild, rides into
+the persisted and named layouts through `viewer.save()`, and needs nothing
+re-applied afterwards.
+
+**This required patching the engine.** `ViewConfig::apply_update` applied ten
+fields and neither depth, so a depth sent through `restore()` was accepted,
+deserialized and dropped — and since `Session::update_view_config` returns early
+when `apply_update` reports no change, no view was rebuilt at all. See
+`ui/vendor/0001-apply-depth-fields-on-config-update.patch`; the vendored build
+carries it.
+
+### What this replaced
+
+Worth knowing, because a lot of machinery existed to work around it and is now
+gone:
+
+- Row depth used to be imperative — `getView()` then `view.set_depth()` — which
+  put it on an object the viewer discards whenever it re-renders. Restoring it
+  meant guessing when that had happened: an `observerShim.js` patching
+  `window.IntersectionObserver` and `window.ResizeObserver`, focus and
+  visibility listeners, a retry loop for `getView()` throwing `No table set`
+  while `getTable()` resolved, and a flag tracking whether the viewer had "gone
+  away". That guesswork produced three distinct visible faults — the tree fully
+  expanding on refocus, snapping on any reflow, and snapping when Perspective's
+  settings sidebar opened.
+- Column collapse used to restore a **truncated `split_by`**. The discarded
+  levels therefore had to be remembered separately (`splitFull`, persisted as
+  `split_full`), our own collapse had to be told apart from the user rearranging
+  the pivot (`collapsingRef` plus a prefix test), and the selection was cleared
+  on every collapse because the axis was changing shape.
+
+`split_full` is still *read* on load, for layouts saved under the old scheme.
+
+### Auto-pause is off
+
+`<perspective-viewer>` auto-pauses by default: an `IntersectionObserver` on
+itself plus the document's `visibilitychange` drive `AutoPauseState::apply()`,
+and pausing **deletes the view** (`session.set_pause(true)` →
+`view_sub.take().delete()`). Returning to the tab is therefore not a redraw but
+`restore_and_render()` — a fresh view and a fresh traversal of the whole grain,
+which on a large one is a multi-second chug on every tab switch, and takes any
+per-node expansion with it.
+
+`initViewer()` calls `viewer.setAutoPause(false)` right after `viewer.load()`.
+Nothing updates the table while the tab is hidden — every operation is driven
+from this page — so the pause bought nothing and cost a rebuild. The view is now
+held while the tab is backgrounded.
+
+### Still not solved: per-node expansion
+
+Expanding one specific branch is view state with no config representation, and
+the API has **no getter**:
+
+```
+expand(row_index: number): Promise<number>
+collapse(row_index: number): Promise<number>
+```
+
+It can be set but not read, so it cannot be captured and replayed — this is why
+it is lost on every rebuild, and why no client-side fix has worked. The honest
+route is a `ViewConfig` field carrying expanded row *paths* (indices shift as
+the tree opens), applied in `server.cpp` where the depths are. Bigger than the
+depth patch: it needs a way to enumerate expanded nodes in the C++ traversal, a
+proto field, and the apply step.
+
+The fork's own `header_click.ts` / `expand_column` / `collapse_column` is the
+column-axis equivalent and has the same limitation.
+
+**Limitation that remains either way:** depth is whole-axis. Excel can collapse
+2025 while 2026 stays expanded; a depth collapses every group at that level
+together. `columns` selects which *measures* appear, not individual split
+combinations.
+
+
+## Pivot layouts
+
+A layout is a named `ViewConfig`, stored in **`pf.layout`** and owned by an
+account. Two kinds, one table:
+
+- **published** — everyone on the forecast lists it and can apply it; only its
+  owner or an admin may change it
+- **private** — yours, nobody else lists it
+
+Scope is the **version** by default, because that is where people enter the app.
+`version_id IS NULL` means the layout applies to every version of the source;
+that is where the old source default went, and what a brand-new version picks up
+before anyone has published anything for it. `is_default` (at most one per scope,
+by partial unique index) is what `initViewer()` restores on a first load — a
+version-scoped default beating a source-wide one, the narrower answer winning.
+
+**Permissions are the `pf.log` rule verbatim** — your own, or an admin's
+override, and the UI greys out the rest rather than offering a click that answers
+403. `can_edit` rides on every row so the menu knows which. Applying is never
+restricted: the guarantee is that a published layout cannot be *changed* out from
+under people, not that it cannot be adapted — Save is withheld on a layout that
+isn't yours, Save as… forks it into your own.
+
+**No territory clause.** A layout is display config, and territory restricts rows,
+not columns; `cleanLayout()` already drops anything the live schema lacks.
+
+**What is still local.** `LAYOUT_KEY` (`pf_layout_v{vid}`) — the unnamed
+last-used config — stays in `localStorage`, because it is per-browser session
+continuity rather than a thing anyone names or shares. `LAYOUTS_KEY`
+(`pf_layouts_v{vid}`) is the old named list; `loadLayouts()` lifts it into
+`pf.layout` as private rows once per version and then clears the key.
+
+**The dirty dot is a comparison, not a flag.** `restore()` itself fires
+`perspective-config-update`, so anything set unconditionally in that handler
+would light up the moment a layout was applied. `activeConfigRef` holds what the
+pivot last matched and `sameConfig()` compares against it, ignoring `table`
+(the per-load table name, different on every refresh).
+
+**What this replaced.** Named layouts lived only in `localStorage` — invisible to
+anyone else, gone on another machine. The one server-side layout was
+`pf.source.default_layout`, a single anonymous blob that `PUT
+/sources/:id/default-layout` let *any* account overwrite for *every* account: a
+published layout with no owner. That route is gone; the column remains, migrated
+and read by nothing.
+
+`cleanLayout()` guards `aggregates` along with the axes — including the weight
+column of the multi-arg form — and drops the offending *entry* rather than the
+layout, since `restore()` is all-or-nothing.
+
+---
+
 ## 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` and `role = date` columns are kept as the slice. A plain click replaces the selection; ctrl/⌘/shift-click toggles a slice in or out of it, so the panel holds a **list** of slices sent as `slices` in operation POST bodies (the single `slice` object is still accepted server-side).
@@ -110,43 +383,6 @@ Turning a region back into slices re-derives, per cell, the same filters Perspec
 
 ---
 
-## Column hierarchy (collapse / expand)
-
-The two pivot axes collapse by completely different mechanisms, and the asymmetry is a
-Perspective constraint, not a choice:
-
-- **Rows.** The `GROUP BY ROLLUP` view holds every level at once; `view.set_depth()` — which
-  lives on the view, not the config — hides the deeper ones. That is what the `EXPAND 0 1 2 3`
-  buttons drive, via `applyDepth()`.
-- **Columns.** There is no equivalent. `expand()` / `collapse()` take a **row index**,
-  `ViewConfig` has `group_by_depth` but no `split_by_depth`, and `split_rollup_mode`
-  (`'flat' | 'rollup'`) only chooses whether subtotal column groups are *emitted* — it is a
-  view shape, not an interaction. So `applySplitDepth(n)` collapses by restoring a
-  **truncated `split_by`**, which rebuilds the view.
-
-Three things follow from the rebuild, and each is handled:
-
-1. The full hierarchy has to be remembered separately — once collapsed, `viewer.save()`
-   only reports the short `split_by`. `splitFullRef` / `splitFull` hold it, and it is
-   persisted into the saved layout as `split_full` so a reload while collapsed can still
-   expand back. `adoptSplit()` is the single place it is set.
-2. `perspective-config-update` fires for our own restore as well as the user rearranging
-   the pivot. `collapsingRef` distinguishes them — without it, a collapse would overwrite
-   the full hierarchy with the truncated one and the deeper levels would be unreachable.
-3. Row depth lives on the discarded view, so `applyDepth(expandDepthRef.current)` is
-   re-applied afterwards — the same wart as the refocus re-apply.
-
-The selection is cleared on every change: slices name the split_by dimensions they were
-cut from, and the highlight is keyed on grid coordinates. Neither survives a column axis
-that just changed shape.
-
-**Limitation:** this is whole-axis, not per-branch. Excel can collapse 2025 while 2026
-stays expanded; truncating `split_by` collapses every column group at that level together.
-Per-branch is not reachable — `columns` selects which *measures* appear, not individual
-split combinations.
-
----
-
 ## 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.
@@ -186,6 +422,70 @@ effects call the API immediately.
 `pf_user: 'admin'`, which any client could have set to anything. The audit log
 now names the account that made the change.
 
+## Source columns come from pg_catalog
+
+`information_schema.columns` omits **materialized views** — they are not in the
+SQL standard — and `gs.osm_skinny` is one. So the source the whole app is built
+on looked like it had no columns: registering it seeded nothing, and creating a
+version failed with "No usable columns in col_meta" while col_meta plainly held
+thirty-six.
+
+`RELATION_COLUMNS_SQL` in `lib/utils.js` is the replacement, used by version
+creation, source registration and the table preview. It returns the same shape
+information_schema did, so `mapType` and the callers were unchanged:
+`data_type` is `format_type` with the modifier stripped, which gives the same
+spelling (`character varying`, `numeric`), and precision and scale are unpacked
+from `atttypmod`. The table browser lists from `pg_class` by `relkind` for the
+same reason.
+
+## Territory scoping
+
+An account sees and changes only its own territory. The list lives on
+`pf.app_user.territory` (jsonb array) with `is_admin` for the accounts that see
+everything, and `col_meta.is_territory` marks which column of a given source
+the values belong to — one per source, flagged rather than named in code so a
+second source can be divided by something other than a sales rep.
+
+**Fail closed.** No territory and not an admin means no rows.
+`buildTerritoryClause()` returns `FALSE`, not `TRUE`, for an empty list or an
+unflagged source: an account somebody forgot to configure sees nothing instead
+of the whole book.
+
+**Built from the session, never the request.** This is the difference between
+it and `scope`, which the browser sends and which is right to send, being a
+filter the user chose. A permission cannot come from the thing it restrains, so
+the territory predicate is ANDed on last, in `sliceUnits()` for writes and per
+route for reads, where nothing in the payload can remove it.
+
+Enforced at:
+
+- `/data` — clause on the cursor *and* on the count behind `X-Row-Count`
+- `/agg` — a `{{territory_clause}}` token applied **before** the GROUP BY, since
+  the territory column need not be part of the grain and may not survive it
+- every operation, through `sliceUnits()`
+- `/sources/:id/values/:col` — completion reads the *source* table, which no
+  scope has touched, so without it a dropdown enumerates the whole business
+- `DELETE /log/:logid` and `PATCH /log/:logid` — by owner, not territory. Undo
+  removes an entry's rows wholesale, and half-undoing one would leave a state
+  nothing describes. The PATCH looks like a private annotation and is not:
+  `label` and `bucket` name the pivot's columns for everyone in the version, so
+  unguarded it let any account rename the company's segments. Your own entries,
+  or an admin's override, and the UI greys out the rest rather than offering a
+  click that answers 403.
+- recode's `set` — a scoped account cannot set the territory column at all.
+  Moving a row between territories is reassignment, not forecasting, and it
+  would vanish from the view that would have shown what happened.
+
+**The change log shows an entry's full impact**, not the reader's share. The
+totals stamped on `pf.log` are company-wide, so an admin's version-wide scale
+reads the same in every account — deliberate, and labelled, rather than
+re-aggregating per territory.
+
+Managed with `./pf.sh set-territory | set-admin | orphan-territory`.
+`orphan-territory` lists values present in the data that no account owns; work
+under one is invisible to everyone but an admin, which a typo causes easily and
+nothing inside the app reveals.
+
 ## 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`.
@@ -197,10 +497,52 @@ Theme state lives in `ui/src/theme.jsx` — a React context (`ThemeContext`) wit
 - **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()`
 
+## After a change: restart, and Generate SQL
+
+Two steps, and which one you need depends on what changed.
+
+- **Restart the server** for anything in `routes/`, `lib/` or `server.js`.
+- **Generate SQL** (Setup, per source) whenever `lib/sql_generator.js` changes.
+  The templates are *stored* in `pf.sql`, so editing the generator changes
+  nothing until they are rebuilt — and a template carrying a token the running
+  code does not substitute fails at the database rather than in JS, which reads
+  as an unrelated client-side error.
+
+The order matters: restart first, then Generate SQL, or the old code writes the
+templates.
+
+Schema changes are applied to the live database directly and mirrored into
+`setup_sql/` for a fresh install; `01_schema.sql` is idempotent but is not a
+migration runner, so running it is not how an existing database gets a new
+column.
+
 ## 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)
+- **Zero-row operations report success.** Scale refuses with "Nothing to
+  scale…" when its slice matches nothing; recode and clone commit an empty log
+  entry and return `rows_affected: 0`. A recode of a rep whose rows are all
+  `reference` looked like it worked and did nothing
+- **The change log does not show an entry's id**, so there is no way to name
+  one when asking about it
+- **Territory is read onto the session at login**, so granting or changing one
+  does not reach a signed-in account until it signs in again. Re-reading it per
+  request in `requireAuth` would also make disabling someone immediate
+- **Depth buttons rebuild the view.** A depth lives in `ViewConfig`, so changing
+  it goes through `restore()` and `Session::update_view_config` tears down and
+  rebuilds the view — a full traversal — where a manual collapse mutates the
+  existing one in place. Noticeable on a large grain. The fix is to detect a
+  depth-only change and call `view.set_depth()` imperatively while still writing
+  it to the config, at the cost of the two being able to drift
+
+- **Load time is dominated by row count, not payload size.** On `fc_osm_skinny_29`
+  (2.56M raw rows) a 24-column grain still yields 285,685 rows: ~2s to aggregate in
+  pg, but ~15s to serialise those rows out of Postgres and parse them into JS, then
+  ~3s to build Arrow. Halving the payload (the `pf_gkey` md5) barely moved it. The
+  remaining lever is **dynamic grain** — group by the fields the current pivot
+  actually uses rather than every `in_grain` column; see `pf_spec.md` →
+  §Display-grain pre-aggregation, "the dynamic variant"
+- Per-node expand/collapse is lost whenever the view rebuilds — set-only API, no
+  getter; see §Axis depth
 - Default pivot layout should be configurable per source (currently hardcodes first 2 dimensions)
 - Source/version selection persists in `localStorage` (`pf_sourceId` / `pf_versionId`,
   `App.jsx`). It is re-validated against the live list whenever that list changes, so a

PERSPECTIVE.md

@@ -257,17 +257,43 @@ it would retire the `if(...)`-expression workaround. It costs the d3fc charts, t
   exist in the current dataset (plus any `expressions`). dataflow's `cleanLayout()` is
   the reference implementation; a stale layout referencing a dropped column otherwise
   throws on restore.
-- **`aggregates` needs the same guard, and doesn't currently have it** (verified 2026-08,
-  pf_app). Both existing `cleanLayout()` implementations filter
-  `columns`/`group_by`/`split_by`/`sort`/`filter` but leave `aggregates` untouched. That
-  is harmless *today* only because `viewer.save()` emits `aggregates: {}` until someone
-  sets one explicitly. The moment a layout adopts the weighted-mean pattern (§3a), a
+- **`aggregates` needs the same guard.** pf_app's `cleanLayout()` has it as of 2026-09;
+  **dataflow's does not** and should adopt it. Filtering
+  `columns`/`group_by`/`split_by`/`sort`/`filter` and stopping there is harmless only
+  while `viewer.save()` emits `aggregates: {}`, which it does until someone sets an
+  aggregate explicitly. The moment a layout adopts the weighted-mean pattern (§3a), a
   dropped column aborts the entire restore — both `table.view()` and `viewer.restore()`
   throw `Could not get dtype for column 'X' as it does not exist in the schema`. An
   aggregate entry references a *target* column and, in the multi-arg form, a *weight*
-  column; both need validating, and the entry should be dropped rather than the layout.
+  column; both need validating, and the entry is what gets dropped, never the layout.
   Add this guard as part of adopting §3a, not after.
 
+### Auto-pause deletes the view — turn it off for a static pivot
+
+`<perspective-viewer>` auto-pauses by default: an `IntersectionObserver` on itself
+(scrolled out of the viewport, `display: none`) combined with the document's
+`visibilitychange` (backgrounded tab, minimized window). "Pause" is not a paint
+optimisation — `session.set_pause(true)` runs `view_sub.take().delete()`, so the
+**view object is destroyed**. Becoming visible again calls
+`restore_and_render(…, ViewerConfigUpdate::default())`: a new view and a full
+traversal, every time.
+
+For a viewer streaming live updates nobody is watching, that is the right trade.
+For a pivot over a large static table it is the wrong one — on pf_app's
+`fc_osm_skinny_29` grain it is a multi-second stall on every tab switch, and it
+silently discards per-node expand/collapse (§"Not fixed by any of this"), which
+has no config representation and so cannot be restored.
+
+```js
+await viewer.load(table)
+try { if (viewer.setAutoPause) await viewer.setAutoPause(false) } catch {}
+```
+
+Guard the call: it is a method on the custom element and absent on older builds.
+Leave auto-pause **on** where the table is fed by a live stream the user does not
+need to have kept up with while away. This is long-standing viewer behaviour, not
+a 5.x regression — worth knowing before blaming a rebuild on your own code.
+
 ---
 
 ## 6. Build & deploy (target)

lib/auth.js

@@ -50,7 +50,29 @@ function sessionUser(req) {
     return req.session?.user?.username || null;
 }
 
-module.exports = { hashPassword, verifyPassword, requireAuth, sessionUser, SCRYPT };
+// What this account may see and change, read from the session for the same
+// reason the username is: the browser must not be able to widen it.
+//
+// Returns { admin: true } for an account that sees everything, or
+// { admin: false, values: [...] } for a scoped one. An empty list is a real
+// answer meaning "nothing", not a missing one meaning "everything" -- an
+// account created without a territory sees no rows until it is granted some.
+function sessionTerritory(req) {
+    const u = req.session?.user;
+    if (!u) return { admin: false, values: [] };
+    if (u.is_admin) return { admin: true, values: null };
+    return { admin: false, values: Array.isArray(u.territory) ? u.territory : [] };
+}
+
+function requireAdmin(req, res, next) {
+    if (req.session?.user?.is_admin) return next();
+    res.status(403).json({ error: 'Administrator access required' });
+}
+
+module.exports = {
+    hashPassword, verifyPassword, requireAuth, requireAdmin,
+    sessionUser, sessionTerritory, SCRYPT,
+};
 
 // CLI: `node lib/auth.js hash` reads a password on stdin and prints its hash,
 // so ./pf.sh can create users without the plaintext touching argv or psql.

lib/sql_generator.js

@@ -8,9 +8,100 @@
 // Tokens baked in at generation time:  column names, source schema.table
 // Tokens substituted at request time:  {{fc_table}}, {{where_clause}}, {{exclude_clause}},
 //                                      {{version_id}}, {{logid}}, {{pf_user}}, {{note}},
+//                                      {{label}}, {{bucket}}, {{tag}}, {{territory_clause}},
 //                                      {{params}}, {{slice}}, {{date_from}}, {{date_to}},
 //                                      {{value_incr}}, {{units_incr}}, {{set_clause}}, {{scale_factor}}
 
+// What the pivot shows for a row's segment and its bucket.
+//
+// The ordering prefix is part of the stored text, not computed here. Perspective
+// orders column groups by the value string, so "01 - Actual" is the only way an
+// arbitrary order can be expressed -- and l.label is where a person types it.
+// Nothing derives it, which is deliberate: an earlier design built the prefix from
+// a separate seq column, as Perspective expressions on the client, and the prefix
+// then existed only inside the pivot -- so every other reader disagreed with it,
+// and a label that could not be expressed in ExprTK's printable-ASCII-per-byte
+// string scanner could not be ordered at all. Stored text has neither problem.
+//
+// The single exception is the adjustment fallback, whose 99 keeps unlabelled
+// adjustments last. Labelling an adjustment's log row overrides it, which is how
+// one kind of adjustment is split out from the rest -- l.label rather than tag or
+// note, so a segment name stays separable from adjustment commentary (pf_note).
+//
+// ---------------------------------------------------------------------------
+// DISPLAY DEFAULTS -- every hardcoded name the pivot can show.
+//
+// These are the values a row falls back to when nobody has named it. They are
+// the complete list: if a segment or bucket appears in the pivot under a name
+// that is not in pf.log, it came from here. CLAUDE.md has the same list under
+// "Hardcoded display names".
+//
+// They live on pf.version -- adjustment_segment, adjustment_bucket,
+// unlabeled_load -- and the constants below are only the fallback for a version
+// that has not set one. Read through a join at query time rather than
+// substituted at generation: pf.sql templates are keyed on (source_id,
+// operation) and shared by every version of a source, so a value baked in could
+// not vary by version and regenerating for one would change the others.
+//
+// Exported because /data builds its own statement in routes/operations.js while
+// /agg is generated here, and the two have to agree.
+// ---------------------------------------------------------------------------
+
+// An adjustment with no label of its own. The 99 keeps it after every numbered
+// segment -- ordering is string ordering, so this only works while the loads
+// carry 01-0n. Labelling an adjustment's log row overrides it, which is how one
+// kind of adjustment is split out from the rest.
+const ADJUSTMENT_SEGMENT = '99 - Adjustments';
+
+// What an adjustment counts toward. Prefixed to match the segments it adjusts:
+// unprefixed it read 'Forecast' while the loads read '04 - Forecast', and the
+// column split in two -- the adjustments sitting apart from the rows they
+// adjust. The number is a guess at the convention in use, which is the clearest
+// argument for making this per-version.
+const ADJUSTMENT_BUCKET = '04 - Forecast';
+
+// A load nobody named. No prefix, so it sorts after everything numbered --
+// letters follow digits in ASCII. The old '(unlabeled load)' sorted *first*,
+// since '(' is 0x28 and digits begin at 0x30.
+const UNLABELED_LOAD = 'Unlabeled';
+
+const LOAD_SEGMENT = `COALESCE(NULLIF(l.label, ''), NULLIF(l.tag, ''), NULLIF(l.note, ''),
+                          NULLIF(v.unlabeled_load, ''), '${UNLABELED_LOAD}')`;
+
+const SEGMENT_EXPR = `CASE WHEN l.operation IN ('baseline','reference')
+          THEN ${LOAD_SEGMENT}
+          ELSE COALESCE(NULLIF(l.label, ''), NULLIF(v.adjustment_segment, ''), '${ADJUSTMENT_SEGMENT}')
+     END`;
+
+// What the row counts towards. A load falls back to its own name until it is
+// bucketed; an adjustment falls back to the forecast bucket, because that is
+// what an adjustment is -- exclude_iters keeps operations off the reference
+// segments, so there is no adjustment that is not part of the forecast.
+const BUCKET_EXPR = `COALESCE(NULLIF(l.bucket, ''),
+               CASE WHEN l.operation IN ('baseline','reference')
+                    THEN ${LOAD_SEGMENT}
+                    ELSE COALESCE(NULLIF(v.adjustment_bucket, ''), '${ADJUSTMENT_BUCKET}')
+               END)`;
+
+const NOTE_EXPR = `CASE WHEN l.operation IN ('baseline','reference')
+          THEN NULL
+          ELSE COALESCE(NULLIF(l.tag, ''), NULLIF(l.note, ''))
+     END`;
+
+// Every pf.log column the two expressions above read, for /agg's GROUP BY: they
+// are functionally dependent on pf_logid, which is in the grain, but Postgres
+// will not infer that.
+const LABEL_GROUP_COLS = ['l.operation', 'l.label', 'l.tag', 'l.note', 'l.bucket',
+                          'v.adjustment_segment', 'v.adjustment_bucket', 'v.unlabeled_load'];
+
+// The version carries the fallback names, so every statement that reads the
+// expressions above needs it in scope as `v`. LEFT, not inner: a forecast row
+// whose log entry somehow has no version should still come back, named by the
+// constants.
+const VERSION_JOIN = `
+LEFT JOIN pf.version v
+    ON v.id = l.version_id`;
+
 // wrap a column name in double quotes for safe use in SQL
 function q(name) { return `"${name}"`; }
 
@@ -51,6 +142,42 @@ function grainOf(colMeta) {
     return { cols, key, groupCols };
 }
 
+// Date columns that anchor a dim_group, each with the dimensions derived from
+// pf.dim_period for it. Shared so the generator and the routes agree on both the
+// membership and the join aliases -- the same reason grainOf is a single function.
+function dateGroupsOf(colMeta) {
+    return colMeta
+        .filter(c => c.role === 'date' && c.is_key && c.dim_group)
+        .map((keyCol, i) => ({
+            alias:   `dp${i + 1}`,
+            dateCol: keyCol.cname,
+            group:   keyCol.dim_group,
+            derived: colMeta
+                .filter(c => c.role === 'dimension'
+                          && c.dim_group === keyCol.dim_group
+                          && c.dim_period_col)
+                .map(c => ({ cname: c.cname, periodCol: c.dim_period_col })),
+        }))
+        .filter(g => g.derived.length > 0);
+}
+
+// cname -> which join it comes from and which of its columns
+function dimPeriodMapOf(dateGroups) {
+    return new Map(
+        dateGroups.flatMap(g => g.derived.map(d => [d.cname, { alias: g.alias, periodCol: d.periodCol }]))
+    );
+}
+
+// The joins themselves, against a date that has already had {{date_offset}}
+// applied. LEFT because a null date -- an order not yet shipped has no ship date
+// -- must leave the period columns empty rather than drop the row.
+function dimPeriodJoins(dateGroups, alias = 's') {
+    return dateGroups.map(g =>
+        `\n    LEFT JOIN pf.dim_period ${g.alias}`
+        + ` ON ${g.alias}.drange @> (${alias}."${g.dateCol}" + '{{date_offset}}'::interval)::date`
+    ).join('');
+}
+
 function generateSQL(source, colMeta) {
     const dims = colMeta
         .filter(c => c.role === 'dimension')
@@ -88,21 +215,47 @@ function generateSQL(source, colMeta) {
     // Baseline and reference copy the source row wholesale, so they carry every
     // measure and every date — not just the primary one the operations act on.
     // Dropping the others would leave those columns null for the life of the version.
+    // Clone carries every date column, not just the primary one, because it is the
+    // operation that moves rows through time: {{date_offset}} shifts them all
+    // together, and the period dimensions are re-derived from pf.dim_period against
+    // the shifted dates rather than copied from the row being cloned. Cloning last
+    // year's mix forward a year otherwise produces rows dated 2027 still labelled
+    // with 2026's periods.
+    const cloneCols = [...dims, ...dateCols, effectiveValue, effectiveUnits].filter(Boolean);
+    const cloneInsertCols = [...cloneCols.map(q), 'pf_iter', 'pf_logid', 'pf_user', 'pf_created_at'].join(', ');
+
+    // An adjustment writes one row per *coordinate* it touches, not one per row
+    // it reads.
+    //
+    // Reading rows one-for-one meant every operation inherited the row count of
+    // everything before it: the baseline's rows plus every prior adjustment's
+    // rows at the same coordinate, so the table grew super-linearly with how
+    // much work had been done on it. Eight Pull Forward entries and the next
+    // scale over the same slice writes nine times what it needs to.
+    //
+    // Collapsing is over pf_logid and pf_iter only -- every stored dimension and
+    // date stays in the GROUP BY -- so no column goes null and nothing becomes
+    // unsliceable later. The distribution maths is untouched either way, since
+    // the window sums see the same totals whether or not the rows underneath
+    // them have been added up first.
+    const groupCols       = (cols) => cols.map(q).join(',\n        ');
+
     const loadCols        = [...dims, ...dateCols, ...valueCols, ...unitsCols];
     const loadInsertCols  = [...loadCols.map(q), 'pf_iter', 'pf_logid', 'pf_user', 'pf_created_at'].join(', ');
     const dateColSet      = new Set(dateCols);
 
-    // dim_period JOIN support: if the date column is the is_key of a dim_group,
-    // dimension siblings with dim_period_col set are derived from pf.dim_period
-    // instead of being copied raw from the source on baseline/reference load.
-    const dateKeyGroup = colMeta.find(c => c.role === 'date' && c.is_key && c.dim_group)?.dim_group;
-    const dimPeriodMap = new Map(
-        dateKeyGroup
-            ? colMeta
-                .filter(c => c.role === 'dimension' && c.dim_group === dateKeyGroup && c.dim_period_col)
-                .map(c => [c.cname, c.dim_period_col])
-            : []
-    );
+    // dim_period JOIN support: a date column that is the is_key of a dim_group
+    // anchors that group, and dimension siblings with dim_period_col set are
+    // derived from pf.dim_period instead of copied raw. Derivation is against the
+    // date *after* {{date_offset}}, which is the whole point -- shift a baseline
+    // forward a year and its period columns follow, rather than still naming the
+    // year it came from.
+    //
+    // Every such group, not just the first. This used to be a find(), so a source
+    // with order, requested and ship date groups derived the order one and copied
+    // the other two raw -- shifted dates against unshifted period labels.
+    const dateGroups   = dateGroupsOf(colMeta);
+    const dimPeriodMap = dimPeriodMapOf(dateGroups);
     const hasDimPeriod = dimPeriodMap.size > 0;
 
     // display grain — when set, initial load and operations both return rows
@@ -148,21 +301,17 @@ function generateSQL(source, colMeta) {
         return `
 SELECT
     ${grainSelect('t.')}
-    ,CASE WHEN l.operation IN ('baseline','reference')
-          THEN COALESCE(NULLIF(l.tag, ''), NULLIF(l.note, ''), '(unlabeled load)')
-          ELSE '(adjustment)' END                                  AS pf_segment
-    ,CASE WHEN l.operation IN ('baseline','reference')
-          THEN NULL
-          ELSE COALESCE(NULLIF(l.tag, ''), NULLIF(l.note, '')) END AS pf_note
-    ,l.operation                                                   AS pf_op
+    ,${SEGMENT_EXPR} AS pf_segment
+    ,${BUCKET_EXPR} AS pf_bucket
+    ,${NOTE_EXPR} AS pf_note
+    ,l.operation AS pf_op
 FROM {{fc_table}} t
 LEFT JOIN pf.log l
-    ON l.id = t.pf_logid
+    ON l.id = t.pf_logid${VERSION_JOIN}
+WHERE {{territory_clause}}
 GROUP BY
     ${grain.groupCols('t.').join('\n    ,')}
-    ,l.operation
-    ,l.tag
-    ,l.note`.trim();
+    ,${LABEL_GROUP_COLS.join('\n    ,')}`.trim();
     }
 
     // grain columns + pf_gkey + summed measures, in the leading-comma style the
@@ -194,23 +343,26 @@ GROUP BY
         // The offset shifts every date column, so order date and ship date stay in step.
         return loadCols.map(c => {
             if (dateColSet.has(c))      return `(${pfx}${q(c)} + '{{date_offset}}'::interval)::date`;
-            if (dimPeriodMap.has(c))    return `dp.${q(dimPeriodMap.get(c))} AS ${q(c)}`;
+            if (dimPeriodMap.has(c)) {
+                const { alias, periodCol } = dimPeriodMap.get(c);
+                return `${alias}.${q(periodCol)} AS ${q(c)}`;
+            }
             return `${pfx}${q(c)}`;
         }).join(',\n        ');
     }
 
     function buildFromClause() {
         if (!hasDimPeriod) return srcTable;
-        return `${srcTable} s\n    JOIN pf.dim_period dp`
-            + ` ON dp.drange @> (s.${q(dateCol)} + '{{date_offset}}'::interval)::date`;
+        return srcTable + ' s' + dimPeriodJoins(dateGroups);
     }
 
     function buildBaseline() {
         return `
 WITH
 ilog AS (
-    INSERT INTO pf.log (version_id, pf_user, operation, slice, params, note)
-    VALUES ({{version_id}}, '{{pf_user}}', 'baseline', NULL, '{{params}}'::jsonb, '{{note}}')
+    INSERT INTO pf.log (version_id, pf_user, operation, slice, params, note, label, bucket, tag)
+    VALUES ({{version_id}}, '{{pf_user}}', 'baseline', NULL, '{{params}}'::jsonb, '{{note}}',
+            NULLIF('{{label}}', ''), NULLIF('{{bucket}}', ''), NULLIF('{{tag}}', ''))
     RETURNING id
 )
 ,ins AS (
@@ -222,15 +374,16 @@ ilog AS (
     WHERE {{filter_clause}}
     RETURNING *
 )
-SELECT count(*) AS rows_affected FROM ins`.trim();
+SELECT count(*) AS rows_affected, (SELECT id FROM ilog) AS log_id FROM ins`.trim();
     }
 
     function buildReference() {
         return `
 WITH
 ilog AS (
-    INSERT INTO pf.log (version_id, pf_user, operation, slice, params, note)
-    VALUES ({{version_id}}, '{{pf_user}}', 'reference', NULL, '{{params}}'::jsonb, '{{note}}')
+    INSERT INTO pf.log (version_id, pf_user, operation, slice, params, note, label, bucket, tag)
+    VALUES ({{version_id}}, '{{pf_user}}', 'reference', NULL, '{{params}}'::jsonb, '{{note}}',
+            NULLIF('{{label}}', ''), NULLIF('{{bucket}}', ''), NULLIF('{{tag}}', ''))
     RETURNING id
 )
 ,ins AS (
@@ -242,7 +395,7 @@ ilog AS (
     WHERE {{filter_clause}}
     RETURNING *
 )
-SELECT count(*) AS rows_affected FROM ins`.trim();
+SELECT count(*) AS rows_affected, (SELECT id FROM ilog) AS log_id FROM ins`.trim();
     }
 
     function buildScale() {
@@ -252,13 +405,16 @@ SELECT count(*) AS rows_affected FROM ins`.trim();
         const uSel = effectiveUnits
             ? `round((${q(effectiveUnits)} / NULLIF(total_units, 0)) * {{units_incr}}, 5)`
             : `0`;
+        // sum(sum(x)) OVER () is the aggregate of the aggregates: the window runs
+        // after the GROUP BY, so the total is over collapsed coordinates and
+        // comes to the same figure the ungrouped window produced.
         const baseSelectParts = [
             ...dimsJoined ? [dimsJoined] : [],
             q(dateCol),
-            effectiveValue  ? q(effectiveValue)  : null,
-            effectiveUnits  ? q(effectiveUnits)  : null,
-            effectiveValue  ? `sum(${q(effectiveValue)}) OVER () AS total_value`  : null,
-            effectiveUnits  ? `sum(${q(effectiveUnits)}) OVER () AS total_units` : null
+            effectiveValue  ? `sum(${q(effectiveValue)}) AS ${q(effectiveValue)}` : null,
+            effectiveUnits  ? `sum(${q(effectiveUnits)}) AS ${q(effectiveUnits)}` : null,
+            effectiveValue  ? `sum(sum(${q(effectiveValue)})) OVER () AS total_value` : null,
+            effectiveUnits  ? `sum(sum(${q(effectiveUnits)})) OVER () AS total_units` : null
         ].filter(Boolean).join(',\n        ');
         return `
 WITH
@@ -273,6 +429,8 @@ ilog AS (
     FROM {{fc_table}}
     WHERE {{where_clause}}
     {{exclude_clause}}
+    GROUP BY
+        ${groupCols([...dims, dateCol])}
 )
 ,ins AS (
     INSERT INTO {{fc_table}} (${insertCols})
@@ -294,10 +452,14 @@ ilog AS (
     RETURNING id
 )
 ,src AS (
-    SELECT ${selectData}
+    SELECT
+        ${dimsJoined},
+        ${q(dateCol)}${effectiveValue ? `,\n        sum(${q(effectiveValue)}) AS ${q(effectiveValue)}` : ''}${effectiveUnits ? `,\n        sum(${q(effectiveUnits)}) AS ${q(effectiveUnits)}` : ''}
     FROM {{fc_table}}
     WHERE {{where_clause}}
     {{exclude_clause}}
+    GROUP BY
+        ${groupCols([...dims, dateCol])}
 )
 ,neg AS (
     INSERT INTO {{fc_table}} (${insertCols})
@@ -322,6 +484,16 @@ ${opTail('allrows')}` : 'SELECT * FROM neg UNION ALL SELECT * FROM ins'}`.trim()
     }
 
     function buildClone() {
+        const select = [
+            // dims: whatever {{set_clause}} resolves them to. The route builds it,
+            // and substitutes the dim_period expression for any derived dimension
+            // the caller has not overridden outright.
+            '{{set_clause}}',
+            ...dateCols.map(c => `(s.${q(c)} + '{{date_offset}}'::interval)::date`),
+            effectiveValue ? `round(s.${q(effectiveValue)} * {{scale_factor}}, 2)` : null,
+            effectiveUnits ? `round(s.${q(effectiveUnits)} * {{scale_factor}}, 5)` : null,
+        ].filter(Boolean).join(',\n        ');
+
         return `
 WITH
 ilog AS (
@@ -330,15 +502,19 @@ ilog AS (
     RETURNING id
 )
 ,ins AS (
-    INSERT INTO {{fc_table}} (${insertCols})
+    INSERT INTO {{fc_table}} (${cloneInsertCols})
     SELECT
-        {{set_clause}},
-        ${q(dateCol)},
-        ${effectiveValue ? `round(${q(effectiveValue)} * {{scale_factor}}, 2)` : '0'}${effectiveUnits ? `,\n        round(${q(effectiveUnits)} * {{scale_factor}}, 5)` : ''},
+        ${select},
         'clone', (SELECT id FROM ilog), '{{pf_user}}', now()
-    FROM {{fc_table}}
-    WHERE {{where_clause}}
-    {{exclude_clause}}
+    FROM (
+        SELECT
+            ${groupCols([...dims, ...dateCols])}${effectiveValue ? `,\n            sum(${q(effectiveValue)}) AS ${q(effectiveValue)}` : ''}${effectiveUnits ? `,\n            sum(${q(effectiveUnits)}) AS ${q(effectiveUnits)}` : ''}
+        FROM {{fc_table}}
+        WHERE {{where_clause}}
+        {{exclude_clause}}
+        GROUP BY
+            ${groupCols([...dims, ...dateCols])}
+    ) s${hasDimPeriod ? dimPeriodJoins(dateGroups) : ''}
     RETURNING *
 )
 ${opTail('ins')}`.trim();
@@ -368,13 +544,53 @@ function applyTokens(sql, tokens) {
 
 // build a SQL WHERE clause string from a slice object
 // only dimension columns are included; unrecognised keys are silently skipped
-function buildWhere(slice, dimCols) {
+// pf_segment and pf_bucket are not columns on the forecast table -- they are
+// computed at read time from the row's pf.log entry -- so a slice naming one
+// cannot be compared directly. It resolves to a set of log ids instead, which is
+// exact: the name lives on the log row, and every forecast row carries the
+// pf_logid that points at it.
+//
+// Without this they were dropped from the slice, and clicking a single bucket's
+// cell scaled every bucket at that dimension intersection while the panel showed
+// only the one clicked.
+const COMPUTED_SLICE_COLS = { pf_segment: SEGMENT_EXPR, pf_bucket: BUCKET_EXPR };
+
+function computedSlicePredicate(col, val, versionId) {
+    if (versionId == null) {
+        const err = new Error(`Cannot filter on ${col} without a version`);
+        err.status = 500;
+        throw err;
+    }
+    const vals = (Array.isArray(val) ? val : [val]).map(v => `'${esc(v)}'`).join(', ');
+    return `pf_logid IN (
+    SELECT l.id
+    FROM pf.log l${VERSION_JOIN}
+    WHERE l.version_id = ${parseInt(versionId)}
+      AND ${COMPUTED_SLICE_COLS[col]} IN (${vals})
+)`;
+}
+
+function buildWhere(slice, dimCols, versionId) {
     if (!slice || Object.keys(slice).length === 0) return 'TRUE';
 
     const allowed = new Set(dimCols);
     const parts = [];
 
     for (const [col, val] of Object.entries(slice)) {
+        if (COMPUTED_SLICE_COLS[col]) {
+            parts.push(computedSlicePredicate(col, val, versionId));
+            continue;
+        }
+        // A pf_ key this does not understand is refused rather than skipped.
+        // Skipping is how a selection silently widened: the operation ran against
+        // everything the dropped key would have excluded. pf_iter is the one
+        // exception -- the client strips it deliberately, since two cells that
+        // differ only by iter band are the same slice.
+        if (col.startsWith('pf_') && col !== 'pf_iter') {
+            const err = new Error(`Slice names ${col}, which cannot be filtered on`);
+            err.status = 400;
+            throw err;
+        }
         if (!allowed.has(col)) continue;
         if (Array.isArray(val)) {
             const escaped = val.map(v => esc(v));
@@ -387,17 +603,92 @@ function buildWhere(slice, dimCols) {
     return parts.length ? parts.join('\nAND ') : 'TRUE';
 }
 
+// The pivot's own filter, carried alongside the slices so an operation writes
+// exactly the rows the ledger counted.
+//
+// A slice is {col: value} and can only ever mean equality, so a view filtered to
+// sseas_e <= 2027 could not be expressed as one. Refusing it was safe but
+// useless -- a bounded season is an ordinary way to scope a forecast -- so the
+// operators travel as [col, op, value] triples instead.
+//
+// Perspective's operator names, not SQL's, since that is where these come from.
+// Anything outside this list is refused rather than ignored: a scope silently
+// dropped is a write that is wider than the panel that authorised it.
+const SCOPE_OPS = {
+    '==':          (c, v) => `${c} = ${v[0]}`,
+    '!=':          (c, v) => `${c} != ${v[0]}`,
+    '>':           (c, v) => `${c} > ${v[0]}`,
+    '>=':          (c, v) => `${c} >= ${v[0]}`,
+    '<':           (c, v) => `${c} < ${v[0]}`,
+    '<=':          (c, v) => `${c} <= ${v[0]}`,
+    'in':          (c, v) => `${c} IN (${v.join(', ')})`,
+    'not in':      (c, v) => `${c} NOT IN (${v.join(', ')})`,
+    'is null':     (c)    => `${c} IS NULL`,
+    'is not null': (c)    => `${c} IS NOT NULL`,
+};
+
+function buildScopeClause(scope, dimCols, versionId) {
+    if (!Array.isArray(scope) || scope.length === 0) return '';
+    const allowed = new Set(dimCols);
+    const parts = scope.map((entry) => {
+        if (!Array.isArray(entry) || entry.length < 2) {
+            const err = new Error(`Malformed scope entry ${JSON.stringify(entry)}`);
+            err.status = 400; throw err;
+        }
+        const [col, op, ...rest] = entry;
+        const vals = (Array.isArray(rest[0]) ? rest[0] : rest).filter(v => v !== undefined);
+
+        if (COMPUTED_SLICE_COLS[col]) {
+            if (op !== '==' && op !== 'in') {
+                const err = new Error(`${col} can only be scoped with == or in, not ${op}`);
+                err.status = 400; throw err;
+            }
+            return computedSlicePredicate(col, vals, versionId);
+        }
+        if (!allowed.has(col)) {
+            const err = new Error(`Column "${col}" is not available for filtering`);
+            err.status = 400; throw err;
+        }
+        const fn = SCOPE_OPS[op];
+        if (!fn) {
+            const err = new Error(`Unsupported filter operator "${op}" on ${col}`);
+            err.status = 400; throw err;
+        }
+        return fn(`"${col}"`, vals.map(v => `'${esc(String(v))}'`));
+    });
+    return parts.join('\nAND ');
+}
+
+// The territory predicate: what this account may see and change.
+//
+// Server-side by construction. Today's `scope` is sent by the browser, which is
+// right for a filter the user chose and would be fatal for a permission -- so
+// this one is built from the session and ANDed on last, where nothing in the
+// request can remove it.
+//
+// FALSE, not TRUE, for an account with no territory. The whole point is that a
+// missing grant means no rows: an empty list that fell through to TRUE would
+// hand the entire book to the first account somebody forgot to configure.
+function buildTerritoryClause(territory, territoryCol, alias = '') {
+    if (!territory || territory.admin) return '';
+    if (!territoryCol) return 'FALSE';
+    const vals = (territory.values || []).filter(v => v != null && v !== '');
+    if (vals.length === 0) return 'FALSE';
+    const pfx = alias ? `${alias}.` : '';
+    return `${pfx}"${territoryCol}" IN (${vals.map(v => `'${esc(String(v))}'`).join(', ')})`;
+}
+
 // build a WHERE clause spanning several slices — an OR of AND-groups.
 // A union of slices cannot be flattened into one IN list per column: slices
 // {Region:East, State:NY} and {Region:West, State:CA} would become
 // Region IN (East,West) AND State IN (NY,CA), which also matches East/CA.
-function buildWhereAny(slices, dimCols) {
+function buildWhereAny(slices, dimCols, versionId) {
     const list = (slices || []).filter(s => s && Object.keys(s).length > 0);
     if (list.length === 0) return 'TRUE';
-    if (list.length === 1) return buildWhere(list[0], dimCols);
+    if (list.length === 1) return buildWhere(list[0], dimCols, versionId);
 
     const groups = list
-        .map(s => buildWhere(s, dimCols))
+        .map(s => buildWhere(s, dimCols, versionId))
         .filter(w => w !== 'TRUE');
 
     // any slice that reduced to TRUE selects everything, so the union does too
@@ -425,12 +716,21 @@ function buildExcludeClause(excludeIters) {
 
 // build the dimension columns portion of a SELECT for recode/clone
 // replaces named dimensions with literal values, passes others through unchanged
-function buildSetClause(dimCols, setObj) {
+// derivedExprs: cname -> a SQL expression to use when the caller has not set the
+// column outright. Clone passes the dim_period expressions through here, so a
+// cloned row's period dimensions come from the calendar against its shifted date
+// rather than from the row it was copied from.
+function buildSetClause(dimCols, setObj, opts = {}) {
+    const { derivedExprs, alias } = opts;
+    const pfx = alias ? `${alias}.` : '';
     return dimCols.map(col => {
         if (setObj && setObj[col] !== undefined) {
             return `'${esc(setObj[col])}' AS "${col}"`;
         }
-        return `"${col}"`;
+        if (derivedExprs && derivedExprs[col]) {
+            return `${derivedExprs[col]} AS "${col}"`;
+        }
+        return `${pfx}"${col}"`;
     }).join(', ');
 }
 
@@ -474,4 +774,6 @@ function esc(val) {
     return String(val).replace(/'/g, "''");
 }
 
-module.exports = { generateSQL, grainOf, applyTokens, buildWhere, buildWhereAny, buildExcludeClause, buildExcludePredicate, buildSetClause, buildFilterClause, esc };
+module.exports = { generateSQL, grainOf, COMPUTED_SLICE_COLS, buildScopeClause, buildTerritoryClause,
+    SEGMENT_EXPR, BUCKET_EXPR, NOTE_EXPR, LABEL_GROUP_COLS, VERSION_JOIN,
+    ADJUSTMENT_SEGMENT, ADJUSTMENT_BUCKET, UNLABELED_LOAD, dateGroupsOf, dimPeriodMapOf, dimPeriodJoins, applyTokens, buildWhere, buildWhereAny, buildExcludeClause, buildExcludePredicate, buildSetClause, buildFilterClause, esc };

lib/utils.js

@@ -3,7 +3,41 @@ function fcTable(tname, versionId) {
     return `pf.fc_${tname}_${versionId}`;
 }
 
-// map information_schema data_type to a clean postgres column type
+// The columns of a relation, from pg_catalog rather than information_schema.
+//
+// information_schema.columns omits materialized views -- they are not in the
+// SQL standard -- so a source built on one looked like it had no columns at
+// all: registering it seeded nothing, and creating a version failed with "No
+// usable columns in col_meta" while col_meta plainly had thirty-six.
+//
+// The shape matches what information_schema returned, so mapType and the
+// callers did not have to change. data_type is format_type with the modifier
+// stripped, which gives the same spelling information_schema uses ('character
+// varying', 'numeric'), and the numeric precision and scale are unpacked from
+// atttypmod the way information_schema does internally.
+//
+// Takes $1 = schema, $2 = relation name.
+const RELATION_COLUMNS_SQL = `
+    SELECT a.attname                                              AS column_name
+          ,regexp_replace(format_type(a.atttypid, a.atttypmod), '\\(.*\\)$', '') AS data_type
+          ,a.attnum                                               AS ordinal_position
+          ,CASE WHEN a.attnotnull THEN 'NO' ELSE 'YES' END        AS is_nullable
+          ,CASE WHEN a.atttypid = 'numeric'::regtype AND a.atttypmod > 4
+                THEN ((a.atttypmod - 4) >> 16) & 65535 END        AS numeric_precision
+          ,CASE WHEN a.atttypid = 'numeric'::regtype AND a.atttypmod > 4
+                THEN (a.atttypmod - 4) & 65535 END                AS numeric_scale
+    FROM pg_attribute a
+    JOIN pg_class     c ON c.oid = a.attrelid
+    JOIN pg_namespace n ON n.oid = c.relnamespace
+    WHERE TRUE
+      AND n.nspname = $1
+      AND c.relname = $2
+      AND c.relkind IN ('r', 'v', 'm', 'f', 'p')
+      AND a.attnum > 0
+      AND NOT a.attisdropped
+`;
+
+// map a data_type name to a clean postgres column type
 function mapType(dataType, numericPrecision, numericScale) {
     switch (dataType) {
         case 'character varying':
@@ -36,4 +70,4 @@ function mapType(dataType, numericPrecision, numericScale) {
     }
 }
 
-module.exports = { fcTable, mapType };
+module.exports = { fcTable, mapType, RELATION_COLUMNS_SQL };

pf.sh

@@ -5,6 +5,7 @@ set -euo pipefail
 # pf.sh — Pivot Forecast management script
 # Usage: ./pf.sh [deploy|start|stop|restart|status|logs|db-setup|config]
 #        ./pf.sh [add-user|passwd|list-users|disable-user|enable-user]
+#        ./pf.sh [set-territory|set-admin|orphan-territory]
 #        ./pf.sh          (interactive menu)
 # ---------------------------------------------------------------------------
 
@@ -360,11 +361,102 @@ cmd_list_users() {
     require_env; load_env
     echo; bold "Accounts"
     run_psql -c "
-        SELECT username, display_name, is_active,
+        SELECT username, display_name, is_active, is_admin,
+               coalesce(jsonb_array_length(territory), 0) AS territory_values,
                to_char(last_login_at, 'YYYY-MM-DD HH24:MI') AS last_login
         FROM pf.app_user ORDER BY username"
 }
 
+# Territory is what an account may see and change, as a list of values in the
+# source's is_territory column. No territory and not an admin means no rows --
+# so a new account is blind until this is run, which is the intended direction
+# to fail in.
+#
+# Values are given comma-separated and have to match the column exactly, since
+# that is what the SQL compares. set-territory with no values clears it.
+cmd_set_territory() {
+    require_env; load_env
+    local username="${1:-}"; shift || true
+    [[ -z "$username" ]] && { read -rp "  Username: " username; }
+    [[ -z "$username" ]] && die "Username is required."
+    local values="${*:-}"
+    [[ -z "$values" ]] && { read -rp "  Territory values (comma separated, blank to clear): " values; }
+
+    local json="null"
+    if [[ -n "$values" ]]; then
+        json=$(python3 - "$values" <<'PYEOF'
+import json, sys
+vals = [v.strip() for v in sys.argv[1].split(',') if v.strip()]
+print(json.dumps(vals))
+PYEOF
+)
+    fi
+
+    run_psql -v ON_ERROR_STOP=1 -tAc "
+        WITH upd AS (
+            UPDATE pf.app_user SET territory = $(if [[ "$json" == "null" ]]; then echo NULL; else echo "'$(sql_lit "$json")'::jsonb"; fi)
+            WHERE lower(username) = lower('$(sql_lit "$username")')
+            RETURNING username
+        )
+        SELECT count(*) FROM upd" | grep -q '^1$' \
+        || die "No such account: $username"
+    success "Territory updated for $username"
+    run_psql -c "SELECT username, is_admin, territory FROM pf.app_user WHERE lower(username) = lower('$(sql_lit "$username")')"
+}
+
+# An admin sees and changes everything, and is the only account that can recode
+# the territory column or undo someone else's entry.
+cmd_set_admin() {
+    require_env; load_env
+    local username="${1:-}" flag="${2:-true}"
+    [[ -z "$username" ]] && { read -rp "  Username: " username; }
+    [[ -z "$username" ]] && die "Username is required."
+    [[ "$flag" != "true" && "$flag" != "false" ]] && die "Second argument must be true or false."
+
+    run_psql -v ON_ERROR_STOP=1 -tAc "
+        WITH upd AS (
+            UPDATE pf.app_user SET is_admin = $flag
+            WHERE lower(username) = lower('$(sql_lit "$username")')
+            RETURNING username
+        )
+        SELECT count(*) FROM upd" | grep -q '^1$' \
+        || die "No such account: $username"
+    success "$username is_admin = $flag"
+}
+
+# Territory values present in the data that belong to no account. Work under one
+# is invisible to everybody but an admin, which is easy to cause by a typo and
+# impossible to notice from inside the app.
+cmd_orphan_territory() {
+    require_env; load_env
+    local source_id="${1:-}"
+    [[ -z "$source_id" ]] && { read -rp "  Source id: " source_id; }
+    [[ -z "$source_id" ]] && die "Source id is required."
+
+    # The column and table are data, so the query is built in two steps rather
+    # than one clever one: read the names, then run the listing.
+    local meta col schema tname
+    meta=$(run_psql -tAF'|' -c "
+        SELECT m.cname, x.schema, x.tname
+        FROM pf.col_meta m JOIN pf.source x ON x.id = m.source_id
+        WHERE m.source_id = $source_id AND m.is_territory")
+    [[ -z "$meta" ]] && die "Source $source_id has no column marked is_territory."
+    IFS='|' read -r col schema tname <<< "$meta"
+
+    echo; bold "Territory values in $schema.$tname with no account"
+    run_psql -c "
+        SELECT DISTINCT s.\"$col\" AS unassigned
+        FROM \"$schema\".\"$tname\" s
+        WHERE TRUE
+          AND s.\"$col\" IS NOT NULL
+          AND s.\"$col\"::text NOT IN (
+              SELECT jsonb_array_elements_text(territory)
+              FROM pf.app_user
+              WHERE territory IS NOT NULL
+          )
+        ORDER BY 1"
+}
+
 # Deactivating leaves the row (and its history) in place, and drops any live
 # session so the account loses access immediately rather than at cookie expiry.
 cmd_disable_user() {
@@ -491,6 +583,9 @@ interactive_menu() {
         echo " 13) list-users     show accounts"
         echo " 14) disable-user   deactivate an account and sign it out"
         echo " 15) enable-user    reactivate an account"
+        echo " 16) set-territory  grant an account the territory values it may see"
+        echo " 17) set-admin      make an account an administrator"
+        echo " 18) orphan-territory  territory values no account owns"
         echo "  q) quit"
         echo
         read -rp "  Choice: " choice
@@ -510,6 +605,9 @@ interactive_menu() {
             13|list-users)       cmd_list_users ;;
             14|disable-user)     cmd_disable_user ;;
             15|enable-user)      cmd_enable_user ;;
+            16|set-territory)    cmd_set_territory ;;
+            17|set-admin)        cmd_set_admin ;;
+            18|orphan-territory) cmd_orphan_territory ;;
             q|Q|quit|exit)       echo "Bye."; exit 0 ;;
             *) warn "Unknown option: $choice" ;;
         esac
@@ -532,8 +630,11 @@ case "${1:-}" in
     add-user)           cmd_add_user ;;
     passwd)             cmd_passwd ;;
     list-users)         cmd_list_users ;;
+    set-territory)      shift; cmd_set_territory "$@" ;;
+    set-admin)          shift; cmd_set_admin "$@" ;;
+    orphan-territory)   shift; cmd_orphan_territory "$@" ;;
     disable-user)       cmd_disable_user "${2:-}" ;;
     enable-user)        cmd_enable_user "${2:-}" ;;
     "")                 interactive_menu ;;
-    *) die "Unknown command: $1. Valid: deploy start stop restart status logs db-setup config install-service uninstall-service add-user passwd list-users disable-user enable-user" ;;
+    *) die "Unknown command: $1. Valid: deploy start stop restart status logs db-setup config install-service uninstall-service add-user passwd list-users disable-user enable-user set-territory set-admin orphan-territory" ;;
 esac

routes/auth.js

@@ -46,7 +46,8 @@ module.exports = function(pool) {
 
         try {
             const result = await pool.query(
-                `SELECT id, username, display_name, pass_hash, is_active
+                `SELECT id, username, display_name, pass_hash, is_active,
+                        is_admin, territory
                  FROM pf.app_user WHERE lower(username) = lower($1)`,
                 [username]
             );
@@ -70,6 +71,8 @@ module.exports = function(pool) {
                     id:           user.id,
                     username:     user.username,
                     display_name: user.display_name,
+                    is_admin:     !!user.is_admin,
+                    territory:    Array.isArray(user.territory) ? user.territory : [],
                 };
                 pool.query(`UPDATE pf.app_user SET last_login_at = now() WHERE id = $1`, [user.id])
                     .catch(e => console.error('last_login_at update failed', e));

routes/layouts.js

@@ -0,0 +1,193 @@
+const express = require('express');
+const { sessionUser } = require('../lib/auth');
+
+// Named Perspective view configs. Two kinds, one table:
+//
+//   private    — yours, nobody else lists it
+//   published  — everyone on the version sees it; only the owner or an admin
+//                may change it, the same rule pf.log uses for its entries
+//
+// Scope is the version by default, because that is where people enter the app.
+// A layout with version_id NULL applies to every version of the source, which
+// is where the old pf.source.default_layout went and what a brand-new version
+// picks up before anyone has published anything for it.
+module.exports = function(pool) {
+    const router = express.Router();
+
+    const SELECT_COLS = `id, source_id, version_id, name, config, owner,
+                         visibility, is_default, created_at, updated_at`;
+
+    // What the client needs to know about a row it cannot write, so the UI can
+    // grey the control out rather than offer a click that answers 403.
+    const decorate = (row, req) => ({
+        ...row,
+        scope:    row.version_id == null ? 'source' : 'version',
+        can_edit: !!req.session?.user?.is_admin || row.owner === sessionUser(req)
+    });
+
+    // The owner/admin gate, shared by every write. Returns the row, or sends the
+    // response itself and returns null.
+    async function ownedLayout(req, res) {
+        const id = parseInt(req.params.lid);
+        const { rows } = await pool.query(
+            `SELECT ${SELECT_COLS} FROM pf.layout WHERE id = $1`, [id]
+        );
+        if (!rows.length) { res.status(404).json({ error: 'Layout not found' }); return null; }
+        const row = rows[0];
+        if (!req.session?.user?.is_admin && row.owner !== sessionUser(req)) {
+            res.status(403).json({
+                error: `“${row.name}” belongs to ${row.owner} — only they or an administrator can change it`
+            });
+            return null;
+        }
+        return row;
+    }
+
+    // Everything applicable to a version: its own published layouts, the
+    // source-wide published ones, and the caller's own private layouts at either
+    // scope. Deliberately not territory-filtered — a layout is display config,
+    // and territory restricts rows, not columns.
+    router.get('/versions/:id/layouts', async (req, res) => {
+        const versionId = parseInt(req.params.id);
+        try {
+            const ver = await pool.query(
+                `SELECT source_id FROM pf.version WHERE id = $1`, [versionId]
+            );
+            if (!ver.rows.length) return res.status(404).json({ error: 'Version not found' });
+            const sourceId = ver.rows[0].source_id;
+
+            const { rows } = await pool.query(
+                `SELECT ${SELECT_COLS}
+                 FROM pf.layout
+                 WHERE TRUE
+                   AND source_id = $1
+                   AND (version_id = $2 OR version_id IS NULL)
+                   AND (visibility = 'published' OR owner = $3)
+                 ORDER BY visibility DESC, is_default DESC, lower(name)`,
+                [sourceId, versionId, sessionUser(req)]
+            );
+            res.json(rows.map(r => decorate(r, req)));
+        } catch (err) {
+            res.status(500).json({ error: err.message });
+        }
+    });
+
+    // Create. scope 'source' saves it against every version of the source;
+    // anything else is this version.
+    router.post('/versions/:id/layouts', async (req, res) => {
+        const versionId = parseInt(req.params.id);
+        const { name, config, visibility = 'private', scope = 'version' } = req.body || {};
+        if (!name || !String(name).trim()) return res.status(400).json({ error: 'Name is required' });
+        if (!config) return res.status(400).json({ error: 'Config is required' });
+        if (!['private', 'published'].includes(visibility)) {
+            return res.status(400).json({ error: 'visibility must be private or published' });
+        }
+        try {
+            const ver = await pool.query(
+                `SELECT source_id FROM pf.version WHERE id = $1`, [versionId]
+            );
+            if (!ver.rows.length) return res.status(404).json({ error: 'Version not found' });
+
+            const { rows } = await pool.query(
+                `INSERT INTO pf.layout (source_id, version_id, name, config, owner, visibility)
+                 VALUES ($1, $2, $3, $4, $5, $6)
+                 RETURNING ${SELECT_COLS}`,
+                [ver.rows[0].source_id, scope === 'source' ? null : versionId,
+                 String(name).trim(), config, sessionUser(req), visibility]
+            );
+            res.json(decorate(rows[0], req));
+        } catch (err) {
+            // the partial unique indexes, reported in the terms the user typed
+            if (err.code === '23505') {
+                return res.status(409).json({ error: `A layout named “${name}” already exists here` });
+            }
+            res.status(500).json({ error: err.message });
+        }
+    });
+
+    // Update any of name / config / visibility / is_default. Each is optional;
+    // a missing key leaves the column alone, which is why the flag-and-value
+    // pair is used rather than COALESCE on the value.
+    router.patch('/layouts/:lid', async (req, res) => {
+        const { name, config, visibility, is_default, scope } = req.body || {};
+        if (name === undefined && config === undefined && visibility === undefined
+            && is_default === undefined && scope === undefined) {
+            return res.status(400).json({ error: 'Nothing to update' });
+        }
+        if (visibility !== undefined && !['private', 'published'].includes(visibility)) {
+            return res.status(400).json({ error: 'visibility must be private or published' });
+        }
+        const client = await pool.connect();
+        try {
+            const row = await ownedLayout(req, res);
+            if (!row) return;
+
+            // Unpublishing a default would leave a default nobody can see, which
+            // the table's CHECK refuses; clear the flag with it rather than
+            // failing on a constraint the user never mentioned.
+            const clearsDefault = visibility === 'private';
+
+            // One default per scope, enforced by a partial unique index. Stand
+            // the others down first rather than letting the update collide --
+            // and in one transaction, or a name clash on the second statement
+            // leaves the version with no default at all.
+            const target_version = scope === undefined ? row.version_id
+                                 : (scope === 'source' ? null : row.version_id);
+            await client.query('BEGIN');
+            if (is_default === true) {
+                await client.query(
+                    `UPDATE pf.layout SET is_default = false
+                     WHERE TRUE
+                       AND source_id = $1
+                       AND COALESCE(version_id, 0) = COALESCE($2::int, 0)
+                       AND id <> $3
+                       AND is_default`,
+                    [row.source_id, target_version, row.id]
+                );
+            }
+
+            const { rows } = await client.query(
+                `UPDATE pf.layout SET
+                     name       = CASE WHEN $2::bool THEN $3::text  ELSE name       END,
+                     config     = CASE WHEN $4::bool THEN $5::jsonb ELSE config     END,
+                     visibility = CASE WHEN $6::bool THEN $7::text  ELSE visibility END,
+                     is_default = CASE WHEN $10::bool THEN false
+                                       WHEN $8::bool  THEN $9::bool ELSE is_default END,
+                     version_id = CASE WHEN $11::bool THEN $12::int ELSE version_id END,
+                     updated_at = now()
+                 WHERE id = $1
+                 RETURNING ${SELECT_COLS}`,
+                [row.id,
+                 name !== undefined,       name !== undefined ? String(name).trim() : null,
+                 config !== undefined,     config !== undefined ? config : null,
+                 visibility !== undefined, visibility !== undefined ? visibility : null,
+                 is_default !== undefined, is_default === true,
+                 clearsDefault,
+                 scope !== undefined,      target_version]
+            );
+            await client.query('COMMIT');
+            res.json(decorate(rows[0], req));
+        } catch (err) {
+            await client.query('ROLLBACK').catch(() => {});
+            if (err.code === '23505') {
+                return res.status(409).json({ error: 'A layout with that name already exists here' });
+            }
+            res.status(500).json({ error: err.message });
+        } finally {
+            client.release();
+        }
+    });
+
+    router.delete('/layouts/:lid', async (req, res) => {
+        try {
+            const row = await ownedLayout(req, res);
+            if (!row) return;
+            await pool.query(`DELETE FROM pf.layout WHERE id = $1`, [row.id]);
+            res.json({ deleted: row.id });
+        } catch (err) {
+            res.status(500).json({ error: err.message });
+        }
+    });
+
+    return router;
+};

routes/log.js

@@ -1,5 +1,6 @@
 const express = require('express');
 const { grainOf } = require('../lib/sql_generator');
+const { sessionUser } = require('../lib/auth');
 const { fcTable } = require('../lib/utils');
 
 module.exports = function(pool) {
@@ -30,6 +31,21 @@ module.exports = function(pool) {
                 unitsCol ? `sum(f."${unitsCol}")::float8 AS units_total` : `NULL::float8 AS units_total`
             ].join(', ');
 
+            // The totals are stamped onto the entry when it is written, so the
+            // normal read is a scan of a few dozen log rows rather than a join
+            // against millions of forecast rows.
+            //
+            // ?recount=1 does it the old way. Stored totals are fixed at write
+            // time and cannot drift on their own, but nothing stops someone
+            // deleting forecast rows by hand, and a stored figure has no way to
+            // notice. This is the way back -- and the backfill for entries
+            // written before the columns existed.
+            const recount = req.query.recount === '1' || req.query.recount === 'true';
+            const stamped = !recount && (await pool.query(
+                `SELECT count(*)::int AS n FROM pf.log
+                 WHERE version_id = $1 AND row_count IS NULL`, [versionId]
+            )).rows[0].n === 0;
+
             // ?kind=adjustments drops the baseline and reference entries. That is not
             // only about what gets listed: the aggregate below joins the whole
             // forecast table, and on a real version the load entries own almost
@@ -41,18 +57,70 @@ module.exports = function(pool) {
                 ? `AND l.operation NOT IN ('baseline', 'reference')`
                 : '';
 
-            const result = await pool.query(`
-                SELECT l.*, ${aggCols},
-                       $2::text AS value_col,
-                       $3::text AS units_col
-                FROM pf.log l
-                LEFT JOIN ${table} f ON f.pf_logid = l.id
-                WHERE l.version_id = $1
-                ${opFilter}
-                GROUP BY l.id
-                ORDER BY l.id DESC
-            `, [versionId, valueCol || null, unitsCol || null]);
-            res.json(result.rows);
+            const result = stamped
+                ? await pool.query(`
+                    SELECT l.*,
+                           $2::text AS value_col,
+                           $3::text AS units_col
+                    FROM pf.log l
+                    WHERE l.version_id = $1
+                    ${opFilter}
+                    ORDER BY l.id DESC
+                `, [versionId, valueCol || null, unitsCol || null])
+                : await pool.query(`
+                    SELECT l.*, ${aggCols},
+                           $2::text AS value_col,
+                           $3::text AS units_col
+                    FROM pf.log l
+                    LEFT JOIN ${table} f ON f.pf_logid = l.id
+                    WHERE l.version_id = $1
+                    ${opFilter}
+                    GROUP BY l.id
+                    ORDER BY l.id DESC
+                `, [versionId, valueCol || null, unitsCol || null]);
+
+            // A recount is also a repair: write back what it found, so the next
+            // read is cheap again and the stored figure matches the rows.
+            if (recount) {
+                for (const r of result.rows) {
+                    await pool.query(
+                        `UPDATE pf.log SET row_count = $2, value_total = $3, units_total = $4,
+                                           measure_cols = $5::jsonb
+                         WHERE id = $1`,
+                        [r.id, r.row_count, r.value_total, r.units_total,
+                         JSON.stringify({ value: valueCol || null, units: unitsCol || null })]
+                    );
+                }
+            }
+            // The statement is kilobytes per entry and the list is opened to scan,
+            // not to read SQL. It stays in the row for the debug endpoint below.
+            res.json(result.rows.map(({ sql_text, ...r }) => ({
+                ...r, has_sql: !!sql_text,
+            })));
+        } catch (err) {
+            console.error(err);
+            res.status(err.status || 500).json({ error: err.message });
+        }
+    });
+
+    // Everything about one entry, for when the rows look wrong: what was asked
+    // for (params), what it ran against (env), and the statement that actually
+    // executed with territory and scope resolved into it (sql_text).
+    //
+    // Both the intent and the SQL, because the translation between them is
+    // exactly what is in doubt when a result is surprising.
+    router.get('/log/:logid/debug', async (req, res) => {
+        const logId = parseInt(req.params.logid);
+        try {
+            const { rows } = await pool.query(
+                `SELECT l.*, v.name AS version_name, s.schema, s.tname
+                 FROM pf.log l
+                 JOIN pf.version v ON v.id = l.version_id
+                 JOIN pf.source s  ON s.id = v.source_id
+                 WHERE l.id = $1`, [logId]
+            );
+            if (!rows.length) return res.status(404).json({ error: 'Log entry not found' });
+            res.json(rows[0]);
         } catch (err) {
             console.error(err);
             res.status(err.status || 500).json({ error: err.message });
@@ -73,6 +141,17 @@ module.exports = function(pool) {
             if (!logResult.rows.length) return res.status(404).json({ error: 'Log entry not found' });
             const log = logResult.rows[0];
             if (log.status === 'closed') return res.status(403).json({ error: 'Version is closed' });
+
+            // Undo deletes rows wholesale by logid, so it cannot be territory
+            // filtered the way a read or a write can -- half-undoing an entry
+            // would leave the version in a state nothing describes. Ownership
+            // instead: your own entries, or an admin's override. Territory alone
+            // would not do it anyway, since two accounts can share one.
+            if (!req.session?.user?.is_admin && log.pf_user !== sessionUser(req)) {
+                return res.status(403).json({
+                    error: `That entry was made by ${log.pf_user || 'someone else'} — only they or an administrator can undo it`
+                });
+            }
             const table = fcTable(log.tname, log.version_id);
 
             // In grain mode the client's table is indexed on pf_gkey, so undo has to
@@ -131,22 +210,44 @@ module.exports = function(pool) {
     // a closed version, where relabelling history is still legitimate.
     router.patch('/log/:logid', async (req, res) => {
         const logId = parseInt(req.params.logid);
-        const { note, tag } = req.body;
-        if (note === undefined && tag === undefined) {
-            return res.status(400).json({ error: 'Nothing to update — send note and/or tag' });
+        const { note, tag, bucket, label } = req.body;
+        if (note === undefined && tag === undefined
+            && bucket === undefined && label === undefined) {
+            return res.status(400).json({
+                error: 'Nothing to update — send note, tag, bucket and/or label'
+            });
         }
         try {
+            // Same rule as undo: your own entries, or an admin's. These are
+            // annotations, but label and bucket name the pivot's columns for
+            // everyone who opens the version, so an unguarded PATCH let any
+            // account rename the company's segments -- including on loads whose
+            // rows it cannot see.
+            const owner = await pool.query(
+                `SELECT pf_user FROM pf.log WHERE id = $1`, [logId]
+            );
+            if (!owner.rows.length) return res.status(404).json({ error: 'Log entry not found' });
+            if (!req.session?.user?.is_admin && owner.rows[0].pf_user !== sessionUser(req)) {
+                return res.status(403).json({
+                    error: `That entry was made by ${owner.rows[0].pf_user || 'someone else'} — only they or an administrator can change it`
+                });
+            }
+
             // COALESCE on the flag, not the value: an explicit null or '' must be
             // able to clear a field, which COALESCE on the value alone would ignore
             const result = await pool.query(
                 `UPDATE pf.log SET
-                     note = CASE WHEN $2::bool THEN $3::text ELSE note END,
-                     tag  = CASE WHEN $4::bool THEN $5::text ELSE tag  END
+                     note   = CASE WHEN $2::bool THEN $3::text ELSE note   END,
+                     tag    = CASE WHEN $4::bool THEN $5::text ELSE tag    END,
+                     bucket = CASE WHEN $6::bool THEN $7::text ELSE bucket END,
+                     label  = CASE WHEN $8::bool THEN $9::text ELSE label  END
                  WHERE id = $1 RETURNING *`,
                 [
                     logId,
-                    note !== undefined, note === undefined ? null : (String(note).trim() || null),
-                    tag  !== undefined, tag  === undefined ? null : (String(tag).trim()  || null),
+                    note   !== undefined, note   === undefined ? null : (String(note).trim()   || null),
+                    tag    !== undefined, tag    === undefined ? null : (String(tag).trim()    || null),
+                    bucket !== undefined, bucket === undefined ? null : (String(bucket).trim() || null),
+                    label  !== undefined, label  === undefined ? null : (String(label).trim()  || null),
                 ]
             );
             if (!result.rows.length) return res.status(404).json({ error: 'Log entry not found' });

routes/operations.js

@@ -1,7 +1,9 @@
 const express = require('express');
 const { tableFromArrays, tableToIPC } = require('apache-arrow');
-const { applyTokens, buildWhere, buildWhereAny, buildExcludeClause, buildExcludePredicate, buildSetClause, esc } = require('../lib/sql_generator');
-const { sessionUser } = require('../lib/auth');
+const { applyTokens, buildWhere, buildWhereAny, COMPUTED_SLICE_COLS, buildScopeClause, buildTerritoryClause, buildExcludeClause, buildExcludePredicate, buildSetClause, dateGroupsOf, dimPeriodMapOf, esc,
+        SEGMENT_EXPR, BUCKET_EXPR, NOTE_EXPR, VERSION_JOIN,
+        ADJUSTMENT_SEGMENT, ADJUSTMENT_BUCKET } = require('../lib/sql_generator');
+const { sessionUser, sessionTerritory } = require('../lib/auth');
 const { fcTable } = require('../lib/utils');
 
 module.exports = function(pool) {
@@ -32,12 +34,60 @@ module.exports = function(pool) {
         return clean;
     }
 
+    // How a multi-slice request is split into statements.
+    //
+    //   'each'    — one statement per slice, so every slice reaches its target on
+    //               its own and gets its own log entry
+    //   'prorate' — a single statement over all of them, letting the SQL's
+    //               sum() OVER () distribute across the whole pool
+    //
+    // Only scale has a target to prorate towards; recode and clone rewrite rows
+    // rather than distribute an amount, so for them this only decides whether the
+    // work lands as one log entry or several.
+    // The scope is ANDed onto every unit rather than folded into the slices: it
+    // applies to all of them equally, and under apply_mode 'each' folding it in
+    // would repeat the same predicate in every statement for no gain.
+    function sliceUnits(slices, ctx, applyMode, scope, req) {
+        const vid   = ctx.version.id;
+        const scl   = buildScopeClause(scope, ctx.filterCols, vid);
+        const terr  = req ? territoryOf(req, ctx) : '';
+        const and   = (w) => {
+            let out = w;
+            for (const extra of [scl, terr]) {
+                if (!extra) continue;
+                out = (out === 'TRUE' || !out) ? extra : `${out}\nAND ${extra}`;
+            }
+            return out;
+        };
+        return applyMode === 'each'
+            ? slices.map(sl => ({ slices: [sl], where: and(buildWhere(sl, ctx.filterCols, vid)) }))
+            : [{ slices, where: and(buildWhereAny(slices, ctx.filterCols, vid)) }];
+    }
+
+    // The offset is interpolated into the statement as an interval literal, so a
+    // typo would surface as a Postgres parse error from the middle of a CTE. Ask
+    // Postgres to parse it alone first, where the failure is cheap and can name the
+    // field it came from. Negative intervals are valid and useful -- '-90 days'
+    // pulls a plan back a quarter -- so this checks validity, not sign.
+    async function assertInterval(value, res) {
+        try {
+            await pool.query(`SELECT $1::interval`, [value]);
+            return true;
+        } catch {
+            res.status(400).json({
+                error: `"${value}" is not a valid interval. Try something like `
+                     + `"4 months", "1 year", "-90 days" or "0 days".`
+            });
+            return false;
+        }
+    }
+
     // A slice is only meaningful if at least one of its keys is a filterable
     // column. buildWhere silently drops unknown keys, so {"typo": "x"} would
     // otherwise reduce to TRUE and apply the operation to the whole version.
     // Refuse rather than let a malformed selection rewrite every row.
     function assertSelective(slices, ctx) {
-        const allowed = new Set(ctx.filterCols);
+        const allowed = new Set([...ctx.filterCols, ...Object.keys(COMPUTED_SLICE_COLS)]);
         slices.forEach((sl, i) => {
             const hits = Object.keys(sl).filter(k => allowed.has(k));
             if (hits.length === 0) {
@@ -51,10 +101,76 @@ module.exports = function(pool) {
         });
     }
 
+    // Stamp what the entry did onto the entry itself.
+    //
+    // An indexed lookup on pf_logid, run once at write time, in place of the
+    // change log joining the whole forecast table on every open. Safe to store
+    // rather than derive because these rows never change: only this operation
+    // inserts them, and the only thing that removes them is undo, which deletes
+    // the log row too.
+    //
+    // Best-effort by design. A failure here must not roll back a write that
+    // succeeded -- the totals can always be recomputed, the adjustment cannot.
+    async function stampLogTotals(client, ctx, logId, extra = {}) {
+        if (!logId) return;
+        const v = ctx.valueCol, u = ctx.unitsCol;
+        // The state the write ran against, none of which can be reconstructed
+        // later: territory and exclude_iters are mutable rows elsewhere, and the
+        // template is overwritten in place every time Generate SQL runs. The
+        // generation timestamp is a fingerprint, not a version -- it cannot bring
+        // the old template back, only tell you the entry did not run under this
+        // one.
+        const env = {
+            territory:      extra.territory ?? null,
+            territory_col:  ctx.territoryCol || null,
+            exclude_iters:  ctx.version.exclude_iters ?? null,
+            sql_generated_at: ctx.sqlGeneratedAt || null,
+        };
+        try {
+            await client.query(`
+                UPDATE pf.log SET
+                    row_count    = t.n,
+                    value_total  = t.v,
+                    units_total  = t.u,
+                    measure_cols = $2::jsonb,
+                    env          = $3::jsonb,
+                    sql_text     = $4::text
+                FROM (
+                    SELECT count(*)::int AS n
+                          ,${v ? `sum(f."${v}")::float8` : 'NULL::float8'} AS v
+                          ,${u ? `sum(f."${u}")::float8` : 'NULL::float8'} AS u
+                    FROM ${ctx.table} f
+                    WHERE f.pf_logid = $1
+                ) t
+                WHERE pf.log.id = $1
+            `, [logId, JSON.stringify({ value: v || null, units: u || null }),
+                 JSON.stringify(env), extra.sql || null]);
+        } catch (err) {
+            console.error('[stampLogTotals]', err);
+        }
+    }
+
+    // Moving a row between territories is reassignment, not forecasting, so a
+    // scoped account cannot set the territory column -- otherwise a rep could
+    // recode work into their own book, or quietly out of it, and the row would
+    // be gone from the view that would have shown what happened.
+    //
+    // Reads and writes are already scoped, so the *source* rows are safely the
+    // account's own; this is only about the destination.
+    function assertMayRecodeTerritory(req, ctx, set, res) {
+        if (!ctx.territoryCol) return true;
+        if (req.session?.user?.is_admin) return true;
+        if (!set || set[ctx.territoryCol] === undefined) return true;
+        res.status(403).json({
+            error: `Only an administrator can recode ${ctx.territoryCol} — that moves rows between territories`
+        });
+        return false;
+    }
+
     // echo back what the caller asked for, for the audit log
     function pickIntent(body) {
         const keys = ['mode', 'target_basis', 'value_incr', 'units_incr', 'value_pct', 'units_pct', 'pct',
-                      'target_value', 'target_units', 'target_price'];
+                      'target_value', 'target_units', 'target_price', 'scope'];
         const out = {};
         for (const k of keys) if (body[k] !== undefined && body[k] !== null && body[k] !== '') out[k] = body[k];
         return out;
@@ -244,7 +360,7 @@ module.exports = function(pool) {
         const unitsCol = colMeta.find(c => c.role === 'units')?.cname;
 
         const sqlResult = await pool.query(
-            `SELECT sql FROM pf.sql WHERE source_id = $1 AND operation = $2`,
+            `SELECT sql, generated_at FROM pf.sql WHERE source_id = $1 AND operation = $2`,
             [version.source_id, operation]
         );
         if (sqlResult.rows.length === 0) {
@@ -261,10 +377,26 @@ module.exports = function(pool) {
             filterCols: [...dimCols, ...dateCols],
             valueCol,
             unitsCol,
-            sql: sqlResult.rows[0].sql
+            territoryCol: colMeta.find(c => c.is_territory)?.cname || null,
+            sql: sqlResult.rows[0].sql,
+            sqlGeneratedAt: sqlResult.rows[0].generated_at
         };
     }
 
+    // Every read and every write goes through this, so a scoped account cannot
+    // reach a row outside its territory by any route. ANDed on last, after the
+    // slice and the client's own scope, where nothing in the request can undo
+    // it.
+    function territoryOf(req, ctx) {
+        return buildTerritoryClause(sessionTerritory(req), ctx.territoryCol);
+    }
+
+    function andTerritory(where, req, ctx) {
+        const t = territoryOf(req, ctx);
+        if (!t) return where;
+        return (!where || where === 'TRUE') ? t : `${where}\nAND ${t}`;
+    }
+
     function guardOpen(version, res) {
         if (version.status === 'closed') {
             res.status(403).json({ error: 'Version is closed' });
@@ -287,7 +419,19 @@ module.exports = function(pool) {
             }
             const tbl = fcTable(verResult.rows[0].tname, versionId);
 
-            const { rows: [{ count }] } = await pool.query(`SELECT COUNT(*) FROM ${tbl}`);
+            // /data does not go through getContext, so it resolves the territory
+            // column itself. The count is scoped too, or the progress bar
+            // promises rows this account will never be sent.
+            const terrCol = (await pool.query(
+                `SELECT cname FROM pf.col_meta WHERE source_id = $1 AND is_territory LIMIT 1`,
+                [verResult.rows[0].source_id]
+            )).rows[0]?.cname || null;
+            const territory = sessionTerritory(req);
+            const terrBare  = buildTerritoryClause(territory, terrCol);
+            const terrAlias = buildTerritoryClause(territory, terrCol, 't');
+            const terrWhere = terrBare ? `WHERE ${terrBare}` : '';
+
+            const { rows: [{ count }] } = await pool.query(`SELECT COUNT(*) FROM ${tbl} ${terrWhere}`);
             const rowCount = parseInt(count);
 
             res.setHeader('Content-Type', 'application/vnd.apache.arrow.stream');
@@ -300,15 +444,13 @@ module.exports = function(pool) {
             await client.query(`
                 DECLARE pf_cur CURSOR FOR
                 SELECT t.*
-                      ,CASE WHEN l.operation IN ('baseline','reference')
-                            THEN COALESCE(NULLIF(l.tag, ''), NULLIF(l.note, ''), '(unlabeled load)')
-                            ELSE '(adjustment)' END                                  AS pf_segment
-                      ,CASE WHEN l.operation IN ('baseline','reference')
-                            THEN NULL
-                            ELSE COALESCE(NULLIF(l.tag, ''), NULLIF(l.note, '')) END AS pf_note
+                      ,${SEGMENT_EXPR} AS pf_segment
+                      ,${BUCKET_EXPR} AS pf_bucket
+                      ,${NOTE_EXPR} AS pf_note
                 FROM ${tbl} t
                 LEFT JOIN pf.log l
-                    ON l.id = t.pf_logid
+                    ON l.id = t.pf_logid${VERSION_JOIN}
+                ${terrAlias ? `WHERE ${terrAlias}` : ''}
             `);
 
             // Accumulate into column arrays (not row objects) to avoid allocating one JS
@@ -352,7 +494,13 @@ module.exports = function(pool) {
     router.get('/versions/:id/agg', async (req, res) => {
         try {
             const ctx = await getContext(parseInt(req.params.id), 'get_agg');
-            const sql = applyTokens(ctx.sql, { fc_table: ctx.table });
+            // Before the GROUP BY, not after: the territory column need not be
+            // part of the grain, so an aggregated row may not carry it at all.
+            const sql = applyTokens(ctx.sql, {
+                fc_table: ctx.table,
+                territory_clause:
+                    buildTerritoryClause(sessionTerritory(req), ctx.territoryCol, 't') || 'TRUE',
+            });
             const { rows } = await runSQL(sql);
 
             res.setHeader('Content-Type', 'application/vnd.apache.arrow.stream');
@@ -377,9 +525,10 @@ module.exports = function(pool) {
 
     // load baseline rows from source table — additive, no delete
     router.post('/versions/:id/baseline', async (req, res) => {
-        const { where_clause, date_offset, note, filters, raw_where } = req.body;
+        const { where_clause, date_offset, note, filters, raw_where, label, bucket, tag } = req.body;
         const pf_user = sessionUser(req);
         const dateOffset    = date_offset  || '0 days';
+        if (!await assertInterval(dateOffset, res)) return;
         const filterClause  = (raw_where || where_clause || '').trim() || 'TRUE';
         try {
             const ctx = await getContext(parseInt(req.params.id), 'baseline');
@@ -394,12 +543,16 @@ module.exports = function(pool) {
                 version_id:    ctx.version.id,
                 pf_user:       esc(pf_user || ''),
                 note:          esc(note    || ''),
+                label:         esc(label   || ''),
+                bucket:        esc(bucket  || ''),
+                tag:           esc(tag     || ''),
                 params:        esc(paramsJson),
                 filter_clause: filterClause,
                 date_offset:   esc(dateOffset)
             });
 
             const result = await runSQL(sql);
+            await stampLogTotals(pool, ctx, result.rows[0]?.log_id, { sql, territory: sessionTerritory(req) });
             res.json(result.rows[0]);
         } catch (err) {
             console.error(err);
@@ -413,9 +566,10 @@ module.exports = function(pool) {
     router.put('/versions/:id/baseline/:logid', async (req, res) => {
         const versionId = parseInt(req.params.id);
         const logid     = parseInt(req.params.logid);
-        const { where_clause, date_offset, note, filters, raw_where } = req.body;
+        const { where_clause, date_offset, note, filters, raw_where, label, bucket, tag } = req.body;
         const pf_user = sessionUser(req);
         const dateOffset   = date_offset  || '0 days';
+        if (!await assertInterval(dateOffset, res)) return;
         const filterClause = (raw_where || where_clause || '').trim() || 'TRUE';
 
         const client = await pool.connect();
@@ -451,11 +605,21 @@ module.exports = function(pool) {
                 date_offset:  dateOffset,
                 ...(raw_where ? { raw_where } : (filters ? { filters } : {}))
             });
+            // This route deletes the log row and inserts a fresh one, so every
+            // annotation on it has to be handed back or it is lost. `??`, not `||`:
+            // an empty string is the form clearing a field on purpose, undefined is
+            // the form not carrying it at all -- the segment form has no tag input,
+            // so tag is always the latter and must survive an edit made for any
+            // other reason.
+            const keep = (sent, prior) => esc(sent ?? prior ?? '');
             const sql = applyTokens(ctx.sql, {
                 fc_table:      ctx.table,
                 version_id:    ctx.version.id,
                 pf_user:       esc(pf_user || ''),
                 note:          esc(note    || ''),
+                label:         keep(label,  oldLog.label),
+                bucket:        keep(bucket, oldLog.bucket),
+                tag:           keep(tag,    oldLog.tag),
                 params:        esc(paramsJson),
                 filter_clause: filterClause,
                 date_offset:   esc(dateOffset)
@@ -469,6 +633,7 @@ module.exports = function(pool) {
             await client.query(`DELETE FROM pf.log WHERE id = $1`, [logid]);
             const insResult = await client.query(sql);
             await client.query('COMMIT');
+            await stampLogTotals(pool, ctx, insResult.rows[0]?.log_id, { sql, territory: sessionTerritory(req) });
 
             res.json({
                 rows_deleted: delRows.rowCount,
@@ -520,7 +685,7 @@ module.exports = function(pool) {
 
     // load reference rows from source table (additive — does not clear prior reference rows)
     router.post('/versions/:id/reference', async (req, res) => {
-        const { where_clause, date_offset, note, filters, raw_where } = req.body;
+        const { where_clause, date_offset, note, filters, raw_where, label, bucket, tag } = req.body;
         const pf_user = sessionUser(req);
         const dateOffset   = date_offset || '0 days';
         const filterClause = (raw_where || where_clause || '').trim() || 'TRUE';
@@ -537,12 +702,16 @@ module.exports = function(pool) {
                 version_id:    ctx.version.id,
                 pf_user:       esc(pf_user || ''),
                 note:          esc(note    || ''),
+                label:         esc(label   || ''),
+                bucket:        esc(bucket  || ''),
+                tag:           esc(tag     || ''),
                 params:        esc(paramsJson),
                 filter_clause: filterClause,
                 date_offset:   esc(dateOffset)
             });
 
             const result = await runSQL(sql);
+            await stampLogTotals(pool, ctx, result.rows[0]?.log_id, { sql, territory: sessionTerritory(req) });
             res.json(result.rows[0]);
         } catch (err) {
             console.error(err);
@@ -572,15 +741,15 @@ module.exports = function(pool) {
             // sum() OVER () distribute the increment across the whole pool.
             // 'each' runs the same statement once per slice, so every slice
             // reaches the target on its own and gets its own log entry.
-            const units = applyMode === 'each'
-                ? slices.map(sl => ({ slices: [sl], where: buildWhere(sl, ctx.filterCols) }))
-                : [{ slices, where: buildWhereAny(slices, ctx.filterCols) }];
+            const units = sliceUnits(slices, ctx, applyMode, req.body.scope, req);
 
             const client = await pool.connect();
             let committed = false;
             try {
                 await client.query('BEGIN');
                 const allRows = [];
+                // the statement that produced each entry, for pf.log.sql_text
+                const sqlByLogId = new Map();
                 let applied = 0;
                 const skipped = [];
 
@@ -610,6 +779,9 @@ module.exports = function(pool) {
                     });
                     const result = await runSQL(sql, client);
                     await tagLog(client, result.rows, req.body.tag);
+                    for (const r of result.rows) {
+                        if (r.pf_logid != null) sqlByLogId.set(r.pf_logid, sql);
+                    }
                     allRows.push(...result.rows);
                 }
 
@@ -622,8 +794,15 @@ module.exports = function(pool) {
 
                 await client.query('COMMIT');
                 committed = true;
+                // one log id per unit: apply_mode 'each' writes an entry per slice
+                for (const id of new Set(allRows.map(r => r.pf_logid).filter(Boolean))) {
+                    await stampLogTotals(pool, ctx, id, {
+                        sql: sqlByLogId.get(id) || null,
+                        territory: sessionTerritory(req),
+                    });
+                }
                 const opLabel = (req.body.tag || '').trim() || note || null;
-                const rows = allRows.map(r => ({ ...r, pf_segment: '(adjustment)', pf_note: opLabel, pf_op: 'scale' }));
+                const rows = allRows.map(r => ({ ...r, pf_segment: ADJUSTMENT_SEGMENT, pf_bucket: ADJUSTMENT_BUCKET, pf_note: opLabel, pf_op: 'scale' }));
                 res.json({
                     rows,
                     rows_affected:  rows.length,
@@ -653,16 +832,19 @@ module.exports = function(pool) {
             const ctx = await getContext(parseInt(req.params.id), 'recode');
             if (!guardOpen(ctx.version, res)) return;
             assertSelective(slices, ctx);
+            if (!assertMayRecodeTerritory(req, ctx, set, res)) return;
 
             const excludeClause = buildExcludeClause(ctx.version.exclude_iters);
             const setClause     = buildSetClause(ctx.dimCols, set);
-            const units         = sliceUnits(slices, ctx, apply_mode);
+            const units         = sliceUnits(slices, ctx, apply_mode === 'each' ? 'each' : 'prorate', req.body.scope, req);
 
             const client = await pool.connect();
             let committed = false;
             try {
                 await client.query('BEGIN');
                 const allRows = [];
+                // the statement that produced each entry, for pf.log.sql_text
+                const sqlByLogId = new Map();
                 for (const unit of units) {
                     const loggedSlice = unit.slices.length === 1 ? unit.slices[0] : unit.slices;
                     const sql = applyTokens(ctx.sql, {
@@ -678,12 +860,22 @@ module.exports = function(pool) {
                     });
                     const result = await runSQL(sql, client);
                     await tagLog(client, result.rows, req.body.tag);
+                    for (const r of result.rows) {
+                        if (r.pf_logid != null) sqlByLogId.set(r.pf_logid, sql);
+                    }
                     allRows.push(...result.rows);
                 }
                 await client.query('COMMIT');
                 committed = true;
+                // one log id per unit: apply_mode 'each' writes an entry per slice
+                for (const id of new Set(allRows.map(r => r.pf_logid).filter(Boolean))) {
+                    await stampLogTotals(pool, ctx, id, {
+                        sql: sqlByLogId.get(id) || null,
+                        territory: sessionTerritory(req),
+                    });
+                }
                 const opLabel = (req.body.tag || '').trim() || note || null;
-                const rows = allRows.map(r => ({ ...r, pf_segment: '(adjustment)', pf_note: opLabel, pf_op: 'recode' }));
+                const rows = allRows.map(r => ({ ...r, pf_segment: ADJUSTMENT_SEGMENT, pf_bucket: ADJUSTMENT_BUCKET, pf_note: opLabel, pf_op: 'recode' }));
                 res.json({ rows, rows_affected: rows.length, slices_applied: units.length });
             } finally {
                 if (!committed) try { await client.query('ROLLBACK'); } catch {}
@@ -698,27 +890,62 @@ module.exports = function(pool) {
     // clone one or more slices as new business under new dimension values
     // does not offset the original slice
     router.post('/versions/:id/clone', async (req, res) => {
-        const { note, set, scale, apply_mode } = req.body;
+        const { note, set, scale, apply_mode, from_logid, date_offset } = req.body;
         const pf_user = sessionUser(req);
         const slices = normalizeSlices(req.body);
         if (slices.length === 0) return res.status(400).json({ error: 'slice is required' });
-        if (!set || Object.keys(set).length === 0) return res.status(400).json({ error: 'set is required' });
 
         try {
             const ctx = await getContext(parseInt(req.params.id), 'clone');
             if (!guardOpen(ctx.version, res)) return;
             assertSelective(slices, ctx);
 
-            const scaleFactor   = (scale != null) ? parseFloat(scale) : 1.0;
-            const excludeClause = buildExcludeClause(ctx.version.exclude_iters);
-            const setClause     = buildSetClause(ctx.dimCols, set);
-            const units         = sliceUnits(slices, ctx, apply_mode);
+            const scaleFactor = (scale != null) ? parseFloat(scale) : 1.0;
+            const dateOffset  = (date_offset || '0 days').trim() || '0 days';
+
+            if (!await assertInterval(dateOffset, res)) return;
+
+            // exclude_iters deliberately does not apply here. It exists to stop
+            // operations *modifying* reference rows: scale would attribute forecast
+            // movement to prior-year rows by distributing across them, and recode
+            // writes negative rows that zero the original out. Clone does neither --
+            // it reads rows and writes new pf_iter = 'clone' rows, leaving the source
+            // untouched. Copying a plan or a prior year out of reference and into
+            // adjustments is the operation working as intended, and excluding them
+            // meant a visible, deliberate selection silently produced nothing.
+            //
+            // from_logid narrows instead: a selection spanning AOP and Prior Year
+            // where only one is wanted.
+            let excludeClause = '';
+            if (from_logid != null) {
+                const srcLog = await pool.query(
+                    `SELECT id FROM pf.log WHERE id = $1 AND version_id = $2`,
+                    [parseInt(from_logid), ctx.version.id]
+                );
+                if (!srcLog.rows.length) {
+                    return res.status(400).json({ error: `No log entry ${from_logid} on this version` });
+                }
+                excludeClause = `AND pf_logid = ${parseInt(from_logid)}`;
+            }
+
+            // Period dimensions come from the calendar against the shifted date, not
+            // from the row being copied -- otherwise a mix moved forward a year
+            // keeps last year's period labels. An explicit set wins over both.
+            const dateGroups   = dateGroupsOf(ctx.colMeta);
+            const derivedExprs = Object.fromEntries(
+                [...dimPeriodMapOf(dateGroups)].map(([cname, { alias, periodCol }]) =>
+                    [cname, `${alias}."${periodCol}"`])
+            );
+            const setClause = buildSetClause(ctx.dimCols, set, { derivedExprs, alias: 's' });
+            const units         = sliceUnits(slices, ctx, apply_mode === 'each' ? 'each' : 'prorate', req.body.scope, req);
 
             const client = await pool.connect();
             let committed = false;
             try {
                 await client.query('BEGIN');
                 const allRows = [];
+                // the statement that produced each entry, for pf.log.sql_text
+                const sqlByLogId = new Map();
                 for (const unit of units) {
                     const loggedSlice = unit.slices.length === 1 ? unit.slices[0] : unit.slices;
                     const sql = applyTokens(ctx.sql, {
@@ -726,21 +953,36 @@ module.exports = function(pool) {
                         version_id:     ctx.version.id,
                         pf_user:        esc(pf_user || ''),
                         note:           esc(note    || ''),
-                        params:         esc(JSON.stringify({ slices: unit.slices, set, scale: scaleFactor, apply_mode: unit.mode })),
+                        params:         esc(JSON.stringify({
+                            slices: unit.slices, set, scale: scaleFactor, apply_mode: unit.mode,
+                            date_offset: dateOffset,
+                            ...(from_logid != null ? { from_logid: parseInt(from_logid) } : {}),
+                        })),
                         slice:          esc(JSON.stringify(loggedSlice)),
                         where_clause:   unit.where,
                         exclude_clause: excludeClause,
                         set_clause:     setClause,
-                        scale_factor:   scaleFactor
+                        scale_factor:   scaleFactor,
+                        date_offset:    esc(dateOffset)
                     });
                     const result = await runSQL(sql, client);
                     await tagLog(client, result.rows, req.body.tag);
+                    for (const r of result.rows) {
+                        if (r.pf_logid != null) sqlByLogId.set(r.pf_logid, sql);
+                    }
                     allRows.push(...result.rows);
                 }
                 await client.query('COMMIT');
                 committed = true;
+                // one log id per unit: apply_mode 'each' writes an entry per slice
+                for (const id of new Set(allRows.map(r => r.pf_logid).filter(Boolean))) {
+                    await stampLogTotals(pool, ctx, id, {
+                        sql: sqlByLogId.get(id) || null,
+                        territory: sessionTerritory(req),
+                    });
+                }
                 const opLabel = (req.body.tag || '').trim() || note || null;
-                const rows = allRows.map(r => ({ ...r, pf_segment: '(adjustment)', pf_note: opLabel, pf_op: 'clone' }));
+                const rows = allRows.map(r => ({ ...r, pf_segment: ADJUSTMENT_SEGMENT, pf_bucket: ADJUSTMENT_BUCKET, pf_note: opLabel, pf_op: 'clone' }));
                 res.json({ rows, rows_affected: rows.length, slices_applied: units.length });
             } finally {
                 if (!committed) try { await client.query('ROLLBACK'); } catch {}

routes/sources.js

@@ -1,5 +1,7 @@
 const express = require('express');
-const { generateSQL } = require('../lib/sql_generator');
+const { generateSQL, buildTerritoryClause } = require('../lib/sql_generator');
+const { RELATION_COLUMNS_SQL } = require('../lib/utils');
+const { sessionTerritory } = require('../lib/auth');
 const { sessionUser } = require('../lib/auth');
 
 module.exports = function(pool) {
@@ -41,15 +43,14 @@ module.exports = function(pool) {
             );
             const source = src.rows[0];
 
-            // seed col_meta from information_schema
+            // seed col_meta from the source's real columns
             await client.query(`
                 INSERT INTO pf.col_meta (source_id, cname, role, opos)
-                SELECT $1, column_name, 'dimension', ordinal_position
-                FROM information_schema.columns
-                WHERE table_schema = $2 AND table_name = $3
+                SELECT $3, column_name, 'dimension', ordinal_position
+                FROM (${RELATION_COLUMNS_SQL}) c
                 ORDER BY ordinal_position
                 ON CONFLICT (source_id, cname) DO NOTHING
-            `, [source.id, schema, tname]);
+            `, [schema, tname, source.id]);
 
             await client.query('COMMIT');
             res.status(201).json(source);
@@ -86,13 +87,23 @@ module.exports = function(pool) {
         if (!Array.isArray(cols)) {
             return res.status(400).json({ error: 'body must be an array' });
         }
+        // Exactly one per source: the scope is a single IN list against a single
+        // column, and two flagged would silently mean whichever one a .find()
+        // reached first -- the trap is_key already fell into (see CLAUDE.md).
+        const territoryCols = cols.filter(c => c.is_territory).map(c => c.cname);
+        if (territoryCols.length > 1) {
+            return res.status(400).json({
+                error: `Only one column can be the territory. Flagged: ${territoryCols.join(', ')}`
+            });
+        }
+
         const client = await pool.connect();
         try {
             await client.query('BEGIN');
             for (const col of cols) {
                 await client.query(`
-                    INSERT INTO pf.col_meta (source_id, cname, label, role, is_key, dim_group, dim_period_col, in_grain, opos)
-                    VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)
+                    INSERT INTO pf.col_meta (source_id, cname, label, role, is_key, dim_group, dim_period_col, in_grain, is_territory, opos)
+                    VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)
                     ON CONFLICT (source_id, cname) DO UPDATE SET
                         label          = EXCLUDED.label,
                         role           = EXCLUDED.role,
@@ -100,6 +111,7 @@ module.exports = function(pool) {
                         dim_group      = EXCLUDED.dim_group,
                         dim_period_col = EXCLUDED.dim_period_col,
                         in_grain       = EXCLUDED.in_grain,
+                        is_territory   = EXCLUDED.is_territory,
                         opos           = EXCLUDED.opos
                 `, [
                     sourceId,
@@ -110,6 +122,7 @@ module.exports = function(pool) {
                     col.dim_group      || null,
                     col.dim_period_col || null,
                     col.in_grain       || false,
+                    col.is_territory   || false,
                     col.opos           || null
                 ]);
             }
@@ -225,10 +238,38 @@ module.exports = function(pool) {
                 return res.status(400).json({ error: `"${col}" is not a key column` });
             }
 
+            // ?q= narrows, ?limit= caps. A key column can be very wide -- part on
+            // osm_skinny has 11,290 distinct values -- so returning the lot to fill a
+            // completion list is both a slow query and a large response for a control
+            // that can only usefully show a handful.
             const { schema, tname } = srcResult.rows[0];
+            const q     = (req.query.q || '').trim();
+            const limit = Math.min(parseInt(req.query.limit) || 5000, 5000);
+
+            const params = [];
+            let filter = `WHERE "${col}" IS NOT NULL`;
+
+            // Completion reads the *source* table, which no territory scope has
+            // touched -- so without this a scoped account could enumerate every
+            // customer, part and rep in the business from a dropdown, having
+            // been shown none of their rows.
+            const terrRow = (await pool.query(
+                `SELECT cname FROM pf.col_meta WHERE source_id = $1 AND is_territory LIMIT 1`,
+                [req.params.id]
+            )).rows[0];
+            const terrClause = buildTerritoryClause(sessionTerritory(req), terrRow?.cname || null);
+            if (terrClause) filter += ` AND ${terrClause}`;
+
+            if (q) {
+                params.push(`%${q}%`);
+                filter += ` AND "${col}"::text ILIKE $${params.length}`;
+            }
+            params.push(limit);
+
             const result = await pool.query(
-                `SELECT DISTINCT "${col}" AS val FROM ${schema}.${tname}
-                 WHERE "${col}" IS NOT NULL ORDER BY "${col}"`
+                `SELECT DISTINCT "${col}"::text AS val FROM ${schema}.${tname}
+                 ${filter} ORDER BY 1 LIMIT $${params.length}`,
+                params
             );
             res.json(result.rows.map(r => r.val));
         } catch (err) {
@@ -237,6 +278,142 @@ module.exports = function(pool) {
         }
     });
 
+    // Resolve a dim_group to its key column and siblings, or explain why it cannot be.
+    async function resolveGroup(sourceId, group) {
+        const { rows: meta } = await pool.query(
+            `SELECT * FROM pf.col_meta WHERE source_id = $1 ORDER BY opos`, [sourceId]);
+        const members = meta.filter(c => c.dim_group === group);
+        if (!members.length) {
+            const err = new Error(`No columns are grouped as "${group}" on this source`);
+            err.status = 404; throw err;
+        }
+        const keyCol = members.find(c => c.is_key);
+        if (!keyCol) {
+            const err = new Error(
+                `Group "${group}" has no is_key column, so its members have nothing to be keyed on`);
+            err.status = 400; throw err;
+        }
+        return {
+            keyCol,
+            siblings:  members.filter(c => c.cname !== keyCol.cname),
+            // recency column: the source's primary date, the same one the generator
+            // treats as the date for loads
+            dateCol: meta.find(c => c.role === 'date')?.cname || null,
+        };
+    }
+
+    // The member list for a group, as one array. Small enough to send whole --
+    // 11,290 parts on osm_skinny -- so the client holds it and filters locally
+    // instead of querying per keystroke.
+    router.get('/sources/:id/dim/:group', async (req, res) => {
+        try {
+            const sourceId = parseInt(req.params.id);
+            const { keyCol, siblings } = await resolveGroup(sourceId, req.params.group);
+            const includeInactive = req.query.all === '1';
+
+            const { rows } = await pool.query(`
+                SELECT key_value, attrs, is_active, source_seen
+                FROM pf.dim_member
+                WHERE source_id = $1 AND dim_group = $2
+                ${includeInactive ? '' : 'AND is_active'}
+                ORDER BY key_value
+            `, [sourceId, req.params.group]);
+
+            res.json({
+                group:    req.params.group,
+                key_col:  keyCol.cname,
+                siblings: siblings.map(c => c.cname),
+                members:  rows,
+            });
+        } catch (err) {
+            console.error(err);
+            res.status(err.status || 500).json({ error: err.message });
+        }
+    });
+
+    // Rebuild a group's members from the source. A merge, not a replace: curation
+    // (a member deactivated by hand, or added before it ever sold) has to survive a
+    // refresh, so absent members are marked source_seen = false rather than deleted.
+    //
+    // Slow by nature -- it reads the whole source, which for a view over a
+    // transaction table is millions of rows -- so it is a deliberate action rather
+    // than something that happens on a page load.
+    router.post('/sources/:id/dim/:group/refresh', async (req, res) => {
+        const sourceId = parseInt(req.params.id);
+        const group    = req.params.group;
+        try {
+            const srcResult = await pool.query(
+                `SELECT schema, tname FROM pf.source WHERE id = $1`, [sourceId]);
+            if (!srcResult.rows.length) return res.status(404).json({ error: 'Source not found' });
+            const { schema, tname } = srcResult.rows[0];
+
+            const { keyCol, siblings, dateCol } = await resolveGroup(sourceId, group);
+            if (!siblings.length) {
+                return res.status(400).json({ error: `Group "${group}" has no sibling columns to store` });
+            }
+
+            const q      = (n) => `"${n}"`;
+            const attrs  = siblings.map(c => `'${c.cname}', s.${q(c.cname)}::text`).join(', ');
+            // A key can carry more than one attribute set across history -- 11,290
+            // parts against 13,662 combinations on osm_skinny. Take the most recent
+            // by the source's date column, which is the live definition.
+            const recency = dateCol ? `s.${q(dateCol)} DESC NULLS LAST` : `1`;
+
+            const started = Date.now();
+            // An explicit stamp rather than now(): inside a transaction now() is the
+            // transaction's start time, so "refreshed in this run" and "refreshed in
+            // a run that began at the same instant" would be indistinguishable.
+            const runAt   = new Date();
+            const client  = await pool.connect();
+            try {
+                await client.query('BEGIN');
+                const { rows: [{ n }] } = await client.query(`
+                    WITH ranked AS (
+                        SELECT s.${q(keyCol.cname)}::text AS key_value,
+                               jsonb_build_object(${attrs}) AS attrs,
+                               row_number() OVER (
+                                   PARTITION BY s.${q(keyCol.cname)} ORDER BY ${recency}
+                               ) AS rn
+                        FROM ${q(schema)}.${q(tname)} s
+                        WHERE s.${q(keyCol.cname)} IS NOT NULL
+                    )
+                    ,upserted AS (
+                        INSERT INTO pf.dim_member
+                            (source_id, dim_group, key_value, attrs, refreshed_at, source_seen)
+                        SELECT $1, $2, key_value, attrs, $3, true
+                        FROM ranked WHERE rn = 1
+                        ON CONFLICT (source_id, dim_group, key_value) DO UPDATE SET
+                            attrs        = EXCLUDED.attrs,
+                            source_seen  = true,
+                            refreshed_at = $3,
+                            updated_at   = now()
+                        RETURNING 1
+                    )
+                    SELECT count(*)::int AS n FROM upserted
+                `, [sourceId, group, runAt]);
+
+                // anything this run did not touch is no longer in the source
+                const { rowCount: dropped } = await client.query(`
+                    UPDATE pf.dim_member
+                    SET source_seen = false, updated_at = now()
+                    WHERE source_id = $1 AND dim_group = $2 AND source_seen
+                      AND refreshed_at IS DISTINCT FROM $3
+                `, [sourceId, group, runAt]);
+
+                await client.query('COMMIT');
+                res.json({ group, members: n, no_longer_in_source: dropped, ms: Date.now() - started });
+            } catch (err) {
+                await client.query('ROLLBACK');
+                throw err;
+            } finally {
+                client.release();
+            }
+        } catch (err) {
+            console.error(err);
+            res.status(err.status || 500).json({ error: err.message });
+        }
+    });
+
     // given a key column value, look up sibling dim_group column values from source
     // returns { sibling_col: value, ... } if exactly one match, null if none or ambiguous
     router.get('/sources/:id/lookup', async (req, res) => {
@@ -272,23 +449,12 @@ module.exports = function(pool) {
         }
     });
 
-    // set or clear the default Perspective layout for a source.
-    // Body: a Perspective view config (group_by, split_by, columns, plugin_config, …).
-    // Pass null or {} to clear.
-    router.put('/sources/:id/default-layout', async (req, res) => {
-        try {
-            const layout = req.body && Object.keys(req.body).length > 0 ? req.body : null;
-            const result = await pool.query(
-                `UPDATE pf.source SET default_layout = $1 WHERE id = $2 RETURNING *`,
-                [layout, req.params.id]
-            );
-            if (result.rows.length === 0) return res.status(404).json({ error: 'Source not found' });
-            res.json(result.rows[0]);
-        } catch (err) {
-            console.error(err);
-            res.status(500).json({ error: err.message });
-        }
-    });
+    // PUT /sources/:id/default-layout is gone. It wrote pf.source.default_layout,
+    // one anonymous blob per source that any account could overwrite for every
+    // other account -- a published layout with no owner. Its successor is
+    // pf.layout: named, owned, and writable only by its owner or an admin. The
+    // old column is left in place, already migrated into pf.layout by
+    // setup_sql/01_schema.sql, and read by nothing.
 
     // deregister a source — does not drop existing forecast tables
     router.get('/dim-period/cols', async (req, res) => {

routes/tables.js

@@ -1,4 +1,5 @@
 const express = require('express');
+const { RELATION_COLUMNS_SQL } = require('../lib/utils');
 
 module.exports = function(pool) {
     const router = express.Router();
@@ -7,15 +8,18 @@ module.exports = function(pool) {
     router.get('/tables', async (req, res) => {
         try {
             const result = await pool.query(`
+                -- pg_class, not information_schema.tables, which omits
+                -- materialized views: gs.osm_skinny is one, and the browser
+                -- could not offer what the app is already built on.
                 SELECT
-                    t.table_schema  AS schema,
-                    t.table_name    AS tname,
+                    n.nspname           AS schema,
+                    c.relname           AS tname,
                     c.reltuples::bigint AS row_estimate
-                FROM information_schema.tables t
-                LEFT JOIN pg_namespace n ON n.nspname = t.table_schema
-                LEFT JOIN pg_class c ON c.relname = t.table_name AND c.relnamespace = n.oid
-                WHERE t.table_schema NOT IN ('pg_catalog', 'information_schema', 'pf')
-                ORDER BY t.table_schema, t.table_name
+                FROM pg_class c
+                JOIN pg_namespace n ON n.oid = c.relnamespace
+                WHERE c.relkind IN ('r', 'v', 'm', 'f', 'p')
+                  AND n.nspname NOT IN ('pg_catalog', 'information_schema', 'pf')
+                ORDER BY n.nspname, c.relname
             `);
             res.json(result.rows);
         } catch (err) {
@@ -31,12 +35,8 @@ module.exports = function(pool) {
             return res.status(400).json({ error: 'Invalid schema or table name' });
         }
         try {
-            const cols = await pool.query(`
-                SELECT column_name, data_type, is_nullable, ordinal_position
-                FROM information_schema.columns
-                WHERE table_schema = $1 AND table_name = $2
-                ORDER BY ordinal_position
-            `, [schema, tname]);
+            const cols = await pool.query(
+                `${RELATION_COLUMNS_SQL} ORDER BY ordinal_position`, [schema, tname]);
 
             const rows = await pool.query(
                 `SELECT * FROM ${schema}.${tname} LIMIT 5`

routes/versions.js

@@ -1,6 +1,7 @@
 const express = require('express');
-const { fcTable, mapType } = require('../lib/utils');
-const { sessionUser } = require('../lib/auth');
+const { fcTable, mapType, RELATION_COLUMNS_SQL } = require('../lib/utils');
+const { sessionUser, sessionTerritory } = require('../lib/auth');
+const { buildTerritoryClause } = require('../lib/sql_generator');
 
 module.exports = function(pool) {
     const router = express.Router();
@@ -38,8 +39,9 @@ module.exports = function(pool) {
             }
             const source = srcResult.rows[0];
 
-            // fetch col_meta joined to information_schema for data types
+            // col_meta joined to the source's real columns, for the data types
             const colResult = await client.query(`
+                WITH cols AS (${RELATION_COLUMNS_SQL})
                 SELECT
                     m.cname,
                     m.role,
@@ -48,14 +50,11 @@ module.exports = function(pool) {
                     i.numeric_precision,
                     i.numeric_scale
                 FROM pf.col_meta m
-                JOIN information_schema.columns i
-                    ON  i.table_schema = $2
-                    AND i.table_name   = $3
-                    AND i.column_name  = m.cname
-                WHERE m.source_id = $1
+                JOIN cols i ON i.column_name = m.cname
+                WHERE m.source_id = $3
                   AND m.role NOT IN ('ignore')
                 ORDER BY m.opos
-            `, [sourceId, source.schema, source.tname]);
+            `, [source.schema, source.tname, sourceId]);
 
             if (colResult.rows.length === 0) {
                 return res.status(400).json({
@@ -127,10 +126,74 @@ ${colDefs},
     // where this version's writes actually land: the physical forecast table,
     // its current row count, and the source table rows are read from.
     // Surfaced in the status bar so the write target is never a mystery.
+    // Distinct values of a dimension as they appear in one version's forecast
+    // table, for completing recode and clone.
+    //
+    // Deliberately not the source: the source view reaches back over all of
+    // history, so completing from it offers parts discontinued years ago. The
+    // version holds what was actually loaded, which is what a forecast is being
+    // written against.
+    //
+    // Held in memory because the scan is not cheap -- 2.0s for 11,290 parts across
+    // 2.5M rows on fc_osm_skinny_29 -- and completion is typed into. Keyed on the
+    // version's latest log id, so any load, adjustment or undo rebuilds it on the
+    // next request without anything having to remember to invalidate.
+    const valueCache = new Map();
+
+    router.get('/versions/:id/values/:col', async (req, res) => {
+        const versionId = parseInt(req.params.id);
+        const col = req.params.col;
+        try {
+            const verResult = await pool.query(`
+                SELECT v.id, s.tname, s.id AS source_id
+                FROM pf.version v JOIN pf.source s ON s.id = v.source_id
+                WHERE v.id = $1
+            `, [versionId]);
+            if (!verResult.rows.length) return res.status(404).json({ error: 'Version not found' });
+            const { tname, source_id } = verResult.rows[0];
+
+            // the column name is interpolated, so it has to be one col_meta names
+            const okCol = await pool.query(`
+                SELECT 1 FROM pf.col_meta
+                WHERE source_id = $1 AND cname = $2 AND role IN ('dimension', 'date')
+            `, [source_id, col]);
+            if (!okCol.rows.length) {
+                return res.status(400).json({ error: `"${col}" is not a dimension on this source` });
+            }
+
+            const table = fcTable(tname, versionId);
+            const { rows: [{ rev }] } = await pool.query(
+                `SELECT coalesce(max(id), 0)::text AS rev FROM pf.log WHERE version_id = $1`,
+                [versionId]
+            );
+
+            const key = `${versionId}:${col}`;
+            let entry = valueCache.get(key);
+            if (!entry || entry.rev !== rev) {
+                const { rows } = await pool.query(
+                    `SELECT DISTINCT "${col}"::text AS val FROM ${table}
+                     WHERE "${col}" IS NOT NULL ORDER BY 1`
+                );
+                entry = { rev, values: rows.map(r => r.val) };
+                valueCache.set(key, entry);
+            }
+
+            const q      = (req.query.q || '').trim().toLowerCase();
+            const limit  = Math.min(parseInt(req.query.limit) || 50, 500);
+            const picked = q
+                ? entry.values.filter(v => v.toLowerCase().includes(q))
+                : entry.values;
+            res.json(picked.slice(0, limit));
+        } catch (err) {
+            console.error(err);
+            res.status(err.status || 500).json({ error: err.message });
+        }
+    });
+
     router.get('/versions/:id/table-info', async (req, res) => {
         try {
             const verResult = await pool.query(`
-                SELECT v.id, v.name, v.status, s.schema, s.tname
+                SELECT v.id, v.name, v.status, s.schema, s.tname, s.id AS source_id
                 FROM pf.version v
                 JOIN pf.source s ON s.id = v.source_id
                 WHERE v.id = $1
@@ -146,10 +209,23 @@ ${colDefs},
             );
             const exists = existsResult.rows[0].exists;
 
+            // Scoped like every other read. Unscoped it reported the whole
+            // table to an account that can see a fraction of it -- the status
+            // bar's row count, and the figure the load progress promises, both
+            // came from here: a rep with 330k rows was told the load was
+            // fetching 2.8M.
+            const terrCol = (await pool.query(
+                `SELECT cname FROM pf.col_meta WHERE source_id = $1 AND is_territory LIMIT 1`,
+                [v.source_id]
+            )).rows[0]?.cname || null;
+            const terr = buildTerritoryClause(sessionTerritory(req), terrCol);
+
             let rows = null, byIter = [];
             if (exists) {
                 const countResult = await pool.query(
-                    `SELECT pf_iter, count(*)::int AS n FROM ${fc} GROUP BY pf_iter ORDER BY pf_iter`
+                    `SELECT pf_iter, count(*)::int AS n FROM ${fc}
+                     ${terr ? `WHERE ${terr}` : ''}
+                     GROUP BY pf_iter ORDER BY pf_iter`
                 );
                 byIter = countResult.rows;
                 rows   = byIter.reduce((a, r) => a + r.n, 0);
@@ -243,22 +319,39 @@ ${colDefs},
         }
     });
 
-    // update version name, description, or exclude_iters
+    // update version name, description, exclude_iters, or the fallback display
+    // names.
+    //
+    // bucket_order is deliberately not settable: the bucket column order is the
+    // text in pf.log.bucket now, so a stored order would be a second answer to
+    // the same question, and a silent one -- nothing reads it.
+    //
+    // The three name columns are flag-and-value pairs rather than COALESCE:
+    // clearing one back to the built-in means writing null, which COALESCE on
+    // the value alone cannot tell from "not mentioned".
     router.put('/versions/:id', async (req, res) => {
-        const { name, description, exclude_iters } = req.body;
+        const { name, description, exclude_iters,
+                adjustment_segment, adjustment_bucket, unlabeled_load } = req.body;
+        const set = (v) => (v === undefined ? null : (String(v).trim() || null));
         try {
             const result = await pool.query(`
                 UPDATE pf.version SET
-                    name          = COALESCE($2, name),
-                    description   = COALESCE($3, description),
-                    exclude_iters = COALESCE($4, exclude_iters)
+                    name               = COALESCE($2, name),
+                    description        = COALESCE($3, description),
+                    exclude_iters      = COALESCE($4, exclude_iters),
+                    adjustment_segment = CASE WHEN $5::bool  THEN $6::text  ELSE adjustment_segment END,
+                    adjustment_bucket  = CASE WHEN $7::bool  THEN $8::text  ELSE adjustment_bucket  END,
+                    unlabeled_load     = CASE WHEN $9::bool  THEN $10::text ELSE unlabeled_load     END
                 WHERE id = $1
                 RETURNING *
             `, [
                 req.params.id,
                 name          || null,
                 description   || null,
-                exclude_iters ? JSON.stringify(exclude_iters) : null
+                exclude_iters ? JSON.stringify(exclude_iters) : null,
+                adjustment_segment !== undefined, set(adjustment_segment),
+                adjustment_bucket  !== undefined, set(adjustment_bucket),
+                unlabeled_load     !== undefined, set(unlabeled_load)
             ]);
             if (result.rows.length === 0) {
                 return res.status(404).json({ error: 'Version not found' });

server.js

@@ -80,6 +80,7 @@ app.use('/api', require('./routes/sources')(pool));
 app.use('/api', require('./routes/versions')(pool));
 app.use('/api', require('./routes/operations')(pool));
 app.use('/api', require('./routes/log')(pool));
+app.use('/api', require('./routes/layouts')(pool));
 
 
 const port = process.env.PORT || 3010;

setup_sql/01_schema.sql

@@ -81,6 +81,119 @@ WHERE TRUE
     AND note <> ''
     AND operation IN ('baseline', 'reference');
 
+-- Display order for the pivot's segment and bucket columns.
+--
+-- The segment's display name in the pivot, falling back to tag then note.
+--
+-- Separate from both because those have jobs already -- tag groups adjustments
+-- into initiatives for the bridge, note is free commentary -- and because the
+-- label carries the sort order. Perspective orders column groups by the value
+-- string, so a leading "01 - " is how ordering is expressed; putting that in the
+-- note would put it in every note.
+ALTER TABLE pf.log ADD COLUMN IF NOT EXISTS label text;
+
+-- Vestigial, both of them. They held the ordinal when the "01 - " prefix was
+-- computed for the pivot rather than typed into label and bucket: bucket_order
+-- sequenced the bucket columns, log.seq the segments within them. Nothing reads
+-- either now, and nothing writes them -- kept only because dropping a column is
+-- not worth a migration to reclaim two that cost nothing.
+ALTER TABLE pf.version ADD COLUMN IF NOT EXISTS bucket_order jsonb;
+ALTER TABLE pf.log     ADD COLUMN IF NOT EXISTS seq integer;
+
+-- What a segment contributes to, independent of pf_iter.
+--
+-- pf_iter answers "can operations write to these rows"; bucket answers "does this
+-- belong in the forecast number". Those are not the same question -- Open Orders is
+-- loaded as reference so nothing adjusts it, yet it is part of the forecast -- so
+-- neither can be derived from the other.
+--
+-- Free text with suggested values (Forecast / Prior Year / Prior Prior Year / Plan)
+-- rather than an enum, so a new banner does not need a migration. Blank by default:
+-- until a segment is labelled, the pivot falls back to showing its own name.
+ALTER TABLE pf.log ADD COLUMN IF NOT EXISTS bucket text;
+CREATE INDEX IF NOT EXISTS log_bucket_idx ON pf.log (bucket) WHERE bucket IS NOT NULL;
+
+-- The names a row falls back to when nobody has named it, per scenario. Null
+-- means "use the built-in", which is the DISPLAY DEFAULTS block in
+-- lib/sql_generator.js. Read through a join at query time, not baked into
+-- pf.sql: those templates are keyed on (source_id, operation) and shared by
+-- every version of a source.
+ALTER TABLE pf.version ADD COLUMN IF NOT EXISTS adjustment_segment text;
+ALTER TABLE pf.version ADD COLUMN IF NOT EXISTS adjustment_bucket  text;
+ALTER TABLE pf.version ADD COLUMN IF NOT EXISTS unlabeled_load     text;
+
+-- What the entry did, stamped when it did it.
+--
+-- Not a cache: a log entry's forecast rows never change after it is written.
+-- Only the operation that owns the logid inserts them, and the only thing that
+-- removes them is undo, which deletes this row too -- so these totals are fixed
+-- at write time rather than derived from something that can move underneath
+-- them. The change log was joining the whole forecast table to recompute them
+-- on every open, 2.5M rows to total a few thousand.
+--
+-- measure_cols records which columns they are denominated in, since the value
+-- and units roles can be reassigned in col_meta and the numbers would otherwise
+-- quietly come to mean something else.
+ALTER TABLE pf.log ADD COLUMN IF NOT EXISTS row_count    integer;
+ALTER TABLE pf.log ADD COLUMN IF NOT EXISTS value_total  double precision;
+ALTER TABLE pf.log ADD COLUMN IF NOT EXISTS units_total  double precision;
+ALTER TABLE pf.log ADD COLUMN IF NOT EXISTS measure_cols jsonb;
+
+-- The column an account's territory is expressed in -- the rep, the region,
+-- whatever this source divides ownership by. Exactly one per source.
+--
+-- Flagged here rather than named in code because it is source-specific, the
+-- same way the grain is: a second source should not need a code change to be
+-- scoped by something other than a sales rep.
+--
+-- It does two jobs. The server scopes every read and write to the values on the
+-- account, and recode refuses to *set* this column unless the account is an
+-- admin -- moving a row between territories is reassignment, not forecasting.
+ALTER TABLE pf.col_meta ADD COLUMN IF NOT EXISTS is_territory boolean NOT NULL DEFAULT false;
+
+-- The debug path: what the entry was trying to do, and what that became.
+--
+-- Both, deliberately. params records the intent -- the slice, the scope, the
+-- resolved increments -- and sql_text records the statement as executed, with
+-- territory and scope already resolved into it. Keeping only one of them
+-- assumes the translation between them is correct, which is exactly the
+-- assumption in doubt when the rows look wrong.
+--
+-- env captures the state the intent was executed against that cannot be
+-- reconstructed afterwards: the territory in force, the version's
+-- exclude_iters, and when the template was generated. All three are mutable
+-- rows elsewhere, and nothing remembers what they were.
+--
+-- The template generation is a fingerprint, not a version: it cannot reproduce
+-- the old template, but it can tell you the entry ran under a different one,
+-- which is what would otherwise make a replay quietly wrong.
+ALTER TABLE pf.log ADD COLUMN IF NOT EXISTS env      jsonb;
+ALTER TABLE pf.log ADD COLUMN IF NOT EXISTS sql_text text;
+
+-- Master data for a dim_group: one row per key value, with its sibling columns.
+--
+-- The source is transactional and often a view over all history, so deriving a
+-- member list from it is both slow and wrong -- slow because it means scanning
+-- millions of rows, wrong because it can only describe what was transacted and
+-- has no way to say a part is discontinued or that a new one exists before it
+-- has sold. This table is the app's own list, refreshed from the source but
+-- curatable independently of it.
+CREATE TABLE IF NOT EXISTS pf.dim_member (
+    source_id    integer NOT NULL REFERENCES pf.source(id) ON DELETE CASCADE,
+    dim_group    text    NOT NULL,   -- matches pf.col_meta.dim_group
+    key_value    text    NOT NULL,   -- the is_key column's value
+    attrs        jsonb   NOT NULL DEFAULT '{}'::jsonb,  -- the sibling columns
+    is_active    boolean NOT NULL DEFAULT true,
+    source_seen  boolean NOT NULL DEFAULT true,  -- present in the source at last refresh
+    added_at     timestamptz NOT NULL DEFAULT now(),
+    updated_at   timestamptz NOT NULL DEFAULT now(),
+    refreshed_at timestamptz,
+    PRIMARY KEY (source_id, dim_group, key_value)
+);
+
+CREATE INDEX IF NOT EXISTS dim_member_active_idx
+    ON pf.dim_member (source_id, dim_group) WHERE is_active;
+
 -- generated operation SQL per source, stored after col_meta is configured
 CREATE TABLE IF NOT EXISTS pf.sql (
     id           serial PRIMARY KEY,
@@ -90,3 +203,61 @@ CREATE TABLE IF NOT EXISTS pf.sql (
     generated_at timestamptz NOT NULL DEFAULT now(),
     UNIQUE (source_id, operation)
 );
+
+-- pf.layout: named Perspective view configs.
+--
+-- Replaces the per-browser localStorage lists (pf_layouts_v*) and the single
+-- anonymous pf.source.default_layout. A layout is either `private` -- visible
+-- only to its owner -- or `published`, visible to everyone on the version and
+-- writable only by its owner or an admin, the same rule pf.log already uses.
+CREATE TABLE IF NOT EXISTS pf.layout (
+    id          serial PRIMARY KEY,
+    source_id   integer NOT NULL REFERENCES pf.source(id)  ON DELETE CASCADE,
+    -- null = applies to every version of the source; that is where the old
+    -- source default lives, and what a new version picks up before anyone has
+    -- published a layout of its own.
+    version_id  integer          REFERENCES pf.version(id) ON DELETE CASCADE,
+    name        text    NOT NULL,
+    config      jsonb   NOT NULL,
+    owner       text    NOT NULL,
+    visibility  text    NOT NULL DEFAULT 'private',   -- private | published
+    is_default  boolean NOT NULL DEFAULT false,       -- applied on first load
+    created_at  timestamptz NOT NULL DEFAULT now(),
+    updated_at  timestamptz NOT NULL DEFAULT now(),
+    CHECK (visibility IN ('private', 'published')),
+    -- only a published layout can be the default: a private one would be a
+    -- default nobody else could see.
+    CHECK (NOT is_default OR visibility = 'published')
+);
+
+-- COALESCE rather than the bare column: version_id is nullable, and in a unique
+-- index NULLs are distinct, so source-wide rows would not be constrained at all.
+CREATE UNIQUE INDEX IF NOT EXISTS layout_private_name
+    ON pf.layout (source_id, COALESCE(version_id, 0), owner, lower(name))
+    WHERE visibility = 'private';
+
+CREATE UNIQUE INDEX IF NOT EXISTS layout_published_name
+    ON pf.layout (source_id, COALESCE(version_id, 0), lower(name))
+    WHERE visibility = 'published';
+
+-- one default per scope
+CREATE UNIQUE INDEX IF NOT EXISTS layout_one_default
+    ON pf.layout (source_id, COALESCE(version_id, 0))
+    WHERE is_default;
+
+CREATE INDEX IF NOT EXISTS layout_lookup ON pf.layout (source_id, version_id);
+
+-- Carry the old single source default across as a published, source-wide row.
+-- Idempotent: skipped once a default exists for that source.
+INSERT INTO pf.layout (source_id, version_id, name, config, owner, visibility, is_default)
+SELECT s.id, NULL, 'Source default', s.default_layout, COALESCE(s.created_by, 'admin'), 'published', true
+FROM pf.source s
+WHERE TRUE
+  AND s.default_layout IS NOT NULL
+  AND NOT EXISTS (
+      SELECT 1 FROM pf.layout l
+      WHERE TRUE
+        AND l.source_id = s.id
+        AND l.version_id IS NULL
+        AND l.is_default
+  );

setup_sql/02_auth.sql

@@ -24,3 +24,15 @@ CREATE TABLE IF NOT EXISTS pf.session (
 );
 
 CREATE INDEX IF NOT EXISTS session_expire_idx ON pf.session (expire);
+
+-- What an account may see and change.
+--
+-- territory is the list of values allowed in the source's territory column
+-- (col_meta.is_territory). It is a filter the server applies to every read and
+-- every write; it is never sent by the client, which could remove it.
+--
+-- Fail closed: no territory and not an admin means no rows. An account created
+-- without one sees nothing until someone grants it, rather than seeing the whole
+-- book because a column was left null.
+ALTER TABLE pf.app_user ADD COLUMN IF NOT EXISTS territory jsonb;
+ALTER TABLE pf.app_user ADD COLUMN IF NOT EXISTS is_admin  boolean NOT NULL DEFAULT false;

ui/package-lock.json

@@ -8,10 +8,10 @@
       "name": "ui",
       "version": "0.0.0",
       "dependencies": {
-        "@perspective-dev/client": "file:./vendor/perspective-dev-client-5.4.0.tgz",
-        "@perspective-dev/server": "file:./vendor/perspective-dev-server-5.4.0.tgz",
-        "@perspective-dev/viewer": "file:./vendor/perspective-dev-viewer-5.4.0.tgz",
-        "@perspective-dev/viewer-datagrid": "file:./vendor/perspective-dev-viewer-datagrid-5.4.0.tgz",
+        "@perspective-dev/client": "file:vendor/perspective-dev-client-5.4.0.tgz",
+        "@perspective-dev/server": "file:vendor/perspective-dev-server-5.4.0.tgz",
+        "@perspective-dev/viewer": "file:vendor/perspective-dev-viewer-5.4.0.tgz",
+        "@perspective-dev/viewer-datagrid": "file:vendor/perspective-dev-viewer-datagrid-5.4.0.tgz",
         "react": "^19.2.5",
         "react-dom": "^19.2.5"
       },
@@ -578,7 +578,7 @@
     "node_modules/@perspective-dev/client": {
       "version": "5.4.0",
       "resolved": "file:vendor/perspective-dev-client-5.4.0.tgz",
-      "integrity": "sha512-l9xCJ0W42wm9fLlwhoU838KyTE131qQTS686uQb/VcsLs30kCVjkoermCCUs0YHCDwDTJhly1y9DfAZDbkg9Ng==",
+      "integrity": "sha512-DOk5TM4+SpDW29w6wJoHtH6oZVSon9YZjkMfKap4Lmf8SLp2PYwec/LouetkMfg4CvveGJemG4domsl8XeNafA==",
       "license": "Apache-2.0",
       "dependencies": {
         "@perspective-dev/server": "^5.4.0",
@@ -590,13 +590,13 @@
     "node_modules/@perspective-dev/server": {
       "version": "5.4.0",
       "resolved": "file:vendor/perspective-dev-server-5.4.0.tgz",
-      "integrity": "sha512-iTseRJB6TL6D9xjaMKMhh2NEKMIi9JR881J+GyQflHIQXK43fDlsIWtByUAoyZzZ7uA9KNZJZicirvONxIpLuw==",
+      "integrity": "sha512-Z5oGEOYqTHMGoMElYbrfJL3YCtoGrAX6+22+3KwrxWDDLB5OlOOdsctfeEAdGqVeJ1/WFhqZp0fWBPEn4vcXrQ==",
       "license": "Apache-2.0"
     },
     "node_modules/@perspective-dev/viewer": {
       "version": "5.4.0",
       "resolved": "file:vendor/perspective-dev-viewer-5.4.0.tgz",
-      "integrity": "sha512-7D6jNn7tDZ3W84MsydplWAJbqqpXIz5OlsHx5YG4sHvJ5q8ngZQvP+oZdxLQXL4hIbaxpskMld1gJbGltpcvUw==",
+      "integrity": "sha512-2y2xRb5k9BedpnRXcaweRYC2LiP/mblNqbFaRtpqNhnVmp+c3pwO6kx57oRuLhyzkcmNZUAzlqZvs1MJNDn50g==",
       "license": "Apache-2.0",
       "dependencies": {
         "@perspective-dev/client": "^5.4.0",
@@ -607,7 +607,7 @@
     "node_modules/@perspective-dev/viewer-datagrid": {
       "version": "5.4.0",
       "resolved": "file:vendor/perspective-dev-viewer-datagrid-5.4.0.tgz",
-      "integrity": "sha512-7ITOmrIh1ZpiQbUR/KmHYck1sLA4cpNgpVMI/qJjQc9k/uypkT1vJmKYxv4MKR24TmEkOouBLNYiO+ItFKs8vg==",
+      "integrity": "sha512-ccJOioKVPAGp/lv7RGSP2rtYj0PfwkJrNZWFMRJkmdz+eAPiHViDZhIHudOuA/2DOmcz6LbD617XaPasTU0Uog==",
       "license": "Apache-2.0",
       "dependencies": {
         "@perspective-dev/client": "^5.4.0",

ui/package.json

@@ -10,10 +10,10 @@
     "preview": "vite preview"
   },
   "dependencies": {
-    "@perspective-dev/client": "file:./vendor/perspective-dev-client-5.4.0.tgz",
-    "@perspective-dev/server": "file:./vendor/perspective-dev-server-5.4.0.tgz",
-    "@perspective-dev/viewer": "file:./vendor/perspective-dev-viewer-5.4.0.tgz",
-    "@perspective-dev/viewer-datagrid": "file:./vendor/perspective-dev-viewer-datagrid-5.4.0.tgz",
+    "@perspective-dev/client": "file:vendor/perspective-dev-client-5.4.0.tgz",
+    "@perspective-dev/server": "file:vendor/perspective-dev-server-5.4.0.tgz",
+    "@perspective-dev/viewer": "file:vendor/perspective-dev-viewer-5.4.0.tgz",
+    "@perspective-dev/viewer-datagrid": "file:vendor/perspective-dev-viewer-datagrid-5.4.0.tgz",
     "react": "^19.2.5",
     "react-dom": "^19.2.5"
   },

ui/src/components/BridgeView.jsx

@@ -49,25 +49,85 @@ function niceTicks(min, max, count = 5) {
 // Turn raw forecast rows into the walk: baseline anchor, one floating step per
 // initiative tag, current anchor. Pure and exported so the arithmetic can be
 // checked against real data without a browser.
-export function buildSteps(rows, { valueCol, unitsCol, logMeta = {}, excludeIters = ['reference'] }) {
+// The walk from a basis to the current forecast.
+//
+// Without a basis this is the forecast's own composition: its loads as the
+// opening anchor, then one step per adjustment tag.
+//
+// With one -- Plan when building a forecast, Prior Year when building the AOP --
+// it starts there instead, and the identity that makes it exact is
+//
+//     Forecast - Basis = (Forecast loads - Basis) + adjustments
+//
+// so the opening step is the difference between the forecast's own loads and the
+// basis, and every tagged adjustment explains the rest. No residual, no plug: the
+// bars sum to the endpoint by construction rather than by hoping the tags cover
+// everything.
+//
+// Membership comes from pf_bucket, not pf_iter. Those answer different questions
+// -- Open Orders is loaded as reference so nothing adjusts it, and is still part
+// of the forecast -- so a bridge keyed on iter silently dropped it.
+export function buildSteps(rows, {
+  valueCol, unitsCol, logMeta = {},
+  excludeIters = ['reference'],   // kept for callers with no bucket data
+  basis = null,                   // a pf_bucket name, or null for composition
+  forecastBucket,
+}) {
+  const hasBuckets = rows.some(r => r.pf_bucket != null)
   const excl = new Set(excludeIters)
-  const baseline = { value: 0, units: 0, rows: 0 }
-  const byTag = new Map()
+
+  // Which bucket is the forecast. It used to be the literal 'Forecast', and the
+  // moment the buckets were renamed to carry their sort prefix -- '04 - Forecast'
+  // -- no row matched it: every row counted as a comparison, the walk had no
+  // steps, and the bridge showed the basis cancelling itself to zero.
+  //
+  // The caller passes the version's adjustment_bucket, which is the same value
+  // an unbucketed adjustment is labelled with, so the two agree by construction.
+  // Falling back to whichever bucket actually holds the adjustments keeps a
+  // renamed or unconfigured version working rather than silently empty.
+  const fcBucket = (() => {
+    if (forecastBucket && rows.some(r => r.pf_bucket === forecastBucket)) return forecastBucket
+    const adjusted = rows.find(r => r.pf_bucket && !['baseline', 'reference'].includes(r.pf_iter))
+    if (adjusted) return adjusted.pf_bucket
+    const baseline = rows.find(r => r.pf_bucket && r.pf_iter === 'baseline')
+    return baseline ? baseline.pf_bucket : (forecastBucket || 'Forecast')
+  })()
+
+  const num = (r, col) => (col ? (parseFloat(r[col]) || 0) : 0)
+  const blank = () => ({ value: 0, units: 0, rows: 0 })
+
+  const loads   = blank()          // the forecast's own segments
+  const basisT  = blank()          // the comparison bucket
+  const byTag   = new Map()
+  const buckets = new Map()        // every bucket, for the picker and the markers
 
   for (const r of rows) {
-    const iter = r.pf_iter
-    if (excl.has(iter)) continue
-    const v = parseFloat(r[valueCol]) || 0
-    const u = unitsCol ? (parseFloat(r[unitsCol]) || 0) : 0
+    const v = num(r, valueCol)
+    const u = num(r, unitsCol)
+    const bucket = hasBuckets ? (r.pf_bucket || '') : null
 
-    if (iter === 'baseline') {
-      baseline.value += v; baseline.units += u; baseline.rows += 1
+    if (bucket != null) {
+      const b = buckets.get(bucket) || blank()
+      b.value += v; b.units += u; b.rows += 1
+      buckets.set(bucket, b)
+    }
+
+    // Anything outside the forecast is a comparison, never a step.
+    if (hasBuckets && bucket !== fcBucket) {
+      if (basis && bucket === basis) { basisT.value += v; basisT.units += u; basisT.rows += 1 }
       continue
     }
+    if (!hasBuckets && excl.has(r.pf_iter)) continue
+
+    const isLoad = r.pf_iter === 'baseline' || r.pf_iter === 'reference'
+    if (isLoad) { loads.value += v; loads.units += u; loads.rows += 1; continue }
+
     const meta  = logMeta[r.pf_logid] || {}
-    const tag   = (meta.tag || '').trim()
+    // label first, the same precedence pf_segment uses, so the bridge and the
+    // pivot call a step by the same name
+    const tag   = (meta.label || meta.tag || '').trim()
     const label = tag || (meta.note || '').trim() ||
-      `${(meta.operation || iter || 'adj')}${r.pf_logid != null ? ` #${r.pf_logid}` : ''}`
+      `${(meta.operation || r.pf_iter || 'adj')}${r.pf_logid != null ? ` #${r.pf_logid}` : ''}`
     const key = tag ? `tag:${tag}` : `log:${r.pf_logid}`
     const g = byTag.get(key) ||
       { key, label, tagged: !!tag, value: 0, units: 0, rows: 0, logIds: new Set(), first: r.pf_logid }
@@ -77,31 +137,96 @@ export function buildSteps(rows, { valueCol, unitsCol, logMeta = {}, excludeIter
   }
 
   const mid = [...byTag.values()].sort((a, b) => (a.first ?? 0) - (b.first ?? 0))
+  const useBasis = !!basis && basisT.rows > 0
 
-  let running = baseline.value
-  const out = [{
-    key: 'baseline', label: 'Baseline', kind: 'anchor',
-    delta: baseline.value, start: 0, end: baseline.value,
-    units: baseline.units, rows: baseline.rows, entries: 1,
-  }]
+  const out = []
+  if (useBasis) {
+    out.push({
+      key: 'basis', label: basis, kind: 'anchor',
+      delta: basisT.value, start: 0, end: basisT.value,
+      units: basisT.units, rows: basisT.rows, entries: 1,
+    })
+    const gap = loads.value - basisT.value
+    out.push({
+      // "Loads" is our word for a segment import and means nothing to anyone
+      // reading a waterfall. This step is simply where the forecast began
+      // relative to the comparison.
+      //
+      // tagged, though it has no tag: the flag drives an "· untagged" suffix
+      // meant for adjustments nobody grouped into an initiative, and this is
+      // not an adjustment at all.
+      key: 'loads-vs-basis', label: `Starting point vs ${basis}`, kind: 'step',
+      tagged: true,
+      delta: gap, start: basisT.value, end: loads.value,
+      units: loads.units - basisT.units, rows: loads.rows, entries: 1,
+    })
+  } else {
+    out.push({
+      key: 'baseline', label: hasBuckets ? fcBucket + ' loads' : 'Baseline', kind: 'anchor',
+      delta: loads.value, start: 0, end: loads.value,
+      units: loads.units, rows: loads.rows, entries: 1,
+    })
+  }
+
+  let running = loads.value
   for (const g of mid) {
     const start = running
     running += g.value
     out.push({ ...g, kind: 'step', delta: g.value, start, end: running, entries: g.logIds.size })
   }
+
   out.push({
-    key: 'current', label: 'Current', kind: 'anchor',
+    key: 'current', label: hasBuckets ? fcBucket : 'Current', kind: 'anchor',
     delta: running, start: 0, end: running,
-    units: out.reduce((a, s) => a + (s.kind === 'anchor' ? 0 : s.units || 0), baseline.units),
-    rows: rows.filter(r => !excl.has(r.pf_iter)).length,
+    units: loads.units + mid.reduce((a, g) => a + (g.units || 0), 0),
+    rows: loads.rows + mid.reduce((a, g) => a + g.rows, 0),
     entries: mid.reduce((a, g) => a + g.logIds.size, 0) + 1,
   })
+
+  // Every bucket present, so the view can offer them as bases and show the ones
+  // that are not the basis as comparison markers.
+  out.buckets = [...buckets.entries()]
+    .map(([name, t]) => ({ name, ...t }))
+    .sort((a, b) => b.value - a.value)
   return out
 }
 
 // Plot geometry, also pure: given the steps and a canvas size, where does each
 // bar and label land? Exported so collisions and overflow can be checked.
-export function layoutSteps(steps, width, H = 340, PAD = { t: 24, r: 16, b: 64, l: 68 }) {
+// Break a label to the bar's width, on word boundaries.
+//
+// SVG text does not wrap, so the labels were cut at twelve characters and
+// "04 - Forecast" and "04 - Forecasting" read the same. Character width is
+// estimated rather than measured -- measuring means a DOM round trip per label
+// on every render, and at this font a digit is about 0.55em, which is close
+// enough for a centred label with a bar's width to play with.
+//
+// Three lines maximum, and a word longer than the line is left to overflow
+// rather than broken: a truncated word helps nobody, and the bars are wide
+// enough that it only happens to things like a pasted part number.
+function wrapLabel(label, barW, fontSize = 10, maxLines = 3) {
+  const perLine = Math.max(6, Math.floor(barW / (fontSize * 0.55)))
+  const words = String(label || '').split(/\s+/).filter(Boolean)
+  const lines = []
+  let line = ''
+  for (const w of words) {
+    const next = line ? `${line} ${w}` : w
+    if (next.length <= perLine) { line = next; continue }
+    if (line) lines.push(line)
+    line = w
+    if (lines.length === maxLines) break
+  }
+  if (line && lines.length < maxLines) lines.push(line)
+  if (!lines.length) return ['']
+  // anything that did not fit is marked on the last line rather than dropped
+  const used = lines.join(' ').length
+  if (used < String(label).replace(/\s+/g, ' ').length) {
+    lines[lines.length - 1] = `${lines[lines.length - 1]}…`
+  }
+  return lines
+}
+
+export function layoutSteps(steps, width, H = 340, PAD = { t: 24, r: 16, b: 84, l: 68 }) {
   const plotW = Math.max(120, width - PAD.l - PAD.r)
   const plotH = H - PAD.t - PAD.b
   const values = steps.flatMap(s => [s.start, s.end])
@@ -127,11 +252,14 @@ export function layoutSteps(steps, width, H = 340, PAD = { t: 24, r: 16, b: 64,
 export default function BridgeView({
   open, onClose, tableRef, viewerRef, logMeta = {},
   valueCol, unitsCol, colMeta = [], slices = [],
-  excludeIters = ['reference'], versionName,
+  excludeIters = ['reference'], versionName, forecastBucket,
 }) {
   const hasSelection = slices.length > 0
   // 'selection' | 'filtered' | 'all'
   const [scope, setScope] = useState(hasSelection ? 'selection' : 'filtered')
+  // Which bucket the walk starts from. Plan when building a forecast, Prior Year
+  // when building the AOP; empty means show the forecast's own composition.
+  const [basis, setBasis] = useState(() => localStorage.getItem('pf_bridge_basis') || '')
   const [asTable, setAsTable] = useState(false)
   const [steps,   setSteps]   = useState(null)
   const [loading, setLoading] = useState(false)
@@ -160,6 +288,8 @@ export default function BridgeView({
             ...Object.entries(sl).filter(([c]) => dateNames.has(c)).map(([c, v]) => [c, '==', Number(v)]),
           ]
           if (!f.length) continue
+          // No expressions needed here: these filters are built from col_meta
+          // names, so they only ever reference real columns.
           const view = await tableRef.current.view({ filter: f })
           const part = await view.to_json()
           await view.delete()
@@ -171,28 +301,39 @@ export default function BridgeView({
         }
       } else {
         let filter = []
+        // Expression columns have to come with the filter that uses them: a
+        // filter can name a column that exists only as an expression, and a view
+        // built without it cannot resolve the column and fails outright.
+        let expressions = {}
         if (scope === 'filtered' && viewerRef?.current) {
           const cfg = await viewerRef.current.save()
           filter = (cfg.filter || []).filter(f => Array.isArray(f) && f.length >= 2)
+          expressions = cfg.expressions || {}
         }
-        const view = await tableRef.current.view(filter.length ? { filter } : {})
+        const viewCfg = {}
+        if (filter.length) viewCfg.filter = filter
+        if (Object.keys(expressions).length) viewCfg.expressions = expressions
+        const view = await tableRef.current.view(viewCfg)
         rows = await view.to_json()
         await view.delete()
       }
 
-      setSteps(buildSteps(rows, { valueCol, unitsCol, logMeta, excludeIters }))
+      setSteps(buildSteps(rows, {
+        valueCol, unitsCol, logMeta, excludeIters, basis: basis || null, forecastBucket,
+      }))
           } catch (err) {
       setError(err.message || String(err))
       setSteps(null)
     } finally {
       setLoading(false)
     }
-  }, [tableRef, viewerRef, scope, logMeta, valueCol, unitsCol, excludeIters, slices, colMeta])
+  }, [tableRef, viewerRef, scope, logMeta, valueCol, unitsCol, excludeIters, slices, colMeta, basis, forecastBucket])
 
   useEffect(() => {
     if (!hasSelection && scope === 'selection') setScope('filtered')
   }, [hasSelection, scope])
 
+  useEffect(() => { try { localStorage.setItem('pf_bridge_basis', basis) } catch {} }, [basis])
   useEffect(() => { if (open) compute() }, [open, compute])
 
   useEffect(() => {
@@ -247,6 +388,23 @@ export default function BridgeView({
               </button>
             ))}
           </div>
+          {/* Offered only once something carries a bucket, and only listing buckets
+              other than the forecast itself -- a walk from Forecast to Forecast is
+              the composition view, which is what the empty option gives. */}
+          {(steps?.buckets || []).some(b => b.name && b.name !== 'Forecast') && (
+            <>
+              <div className="w-px h-4 bg-gray-200" />
+              <span className="text-gray-600">Compare to</span>
+              <select value={basis} onChange={e => setBasis(e.target.value)}
+                className="border border-gray-200 rounded px-2 py-1 text-gray-700 bg-white">
+                <option value="">nothing — show composition</option>
+                {(steps?.buckets || [])
+                  .filter(b => b.name && b.name !== 'Forecast')
+                  .map(b => <option key={b.name} value={b.name}>{b.name}</option>)}
+              </select>
+            </>
+          )}
+
           <div className="w-px h-4 bg-gray-200" />
           <button onClick={() => setAsTable(t => !t)}
             className="border border-gray-200 rounded px-2 py-1 text-gray-700 hover:bg-gray-50">
@@ -302,6 +460,8 @@ export default function BridgeView({
                   const h    = Math.max(2, bot - top)
                   const x    = xOf(i)
                   const on   = hover?.key === s.key
+                  const labelLines = wrapLabel(s.label, barW)
+                  const subY = PAD.t + plotH + 16 + labelLines.length * 11 + 2
                   return (
                     <g key={s.key}
                        onMouseEnter={() => setHover({ ...s, x: x + barW / 2, y: top })}
@@ -322,15 +482,18 @@ export default function BridgeView({
                         {isAnchor ? fmt(s.end, 0) : fmtSigned(s.delta, 0)}
                       </text>
                       <text x={x + barW / 2} y={PAD.t + plotH + 16} textAnchor="middle" fontSize="10" fill={INK}>
-                        {s.label.length > 12 ? `${s.label.slice(0, 11)}…` : s.label}
+                        {labelLines.map((ln, li) => (
+                          <tspan key={li} x={x + barW / 2} dy={li === 0 ? 0 : 11}>{ln}</tspan>
+                        ))}
                       </text>
+                      {/* below the label, however many lines it took */}
                       {!isAnchor && s.entries > 1 && (
-                        <text x={x + barW / 2} y={PAD.t + plotH + 29} textAnchor="middle" fontSize="9" fill={INK_DIM}>
+                        <text x={x + barW / 2} y={subY} textAnchor="middle" fontSize="9" fill={INK_DIM}>
                           ×{s.entries}
                         </text>
                       )}
                       {!s.tagged && !isAnchor && (
-                        <text x={x + barW / 2} y={PAD.t + plotH + 29} textAnchor="middle" fontSize="9" fill={INK_DIM}>
+                        <text x={x + barW / 2} y={subY} textAnchor="middle" fontSize="9" fill={INK_DIM}>
                           untagged
                         </text>
                       )}

ui/src/components/LayoutMenu.jsx

@@ -0,0 +1,190 @@
+import { useEffect, useRef, useState } from 'react'
+
+// The pivot layout, as one control rather than a row of equal chips.
+//
+// Two lists, because a layout is one of two different things: Published, which
+// everyone on this forecast sees and only its owner or an admin may change, and
+// Mine, which nobody else lists. Everything you cannot write is still there to
+// apply -- it is only the write controls that are withheld, so a click never
+// answers 403.
+export default function LayoutMenu({
+  layouts = [], activeLayoutId, dirty,
+  onApply, onSave, onSaveAs, onSetVisibility, onSetDefault, onRename, onDelete, onReset
+}) {
+  const [open,       setOpen]       = useState(false)
+  const [saveName,   setSaveName]   = useState('')
+  const [savePublic, setSavePublic] = useState(false)
+  const [renaming,   setRenaming]   = useState(null)
+  const [renameTo,   setRenameTo]   = useState('')
+  const boxRef = useRef(null)
+
+  // Close on an outside click or Escape. Capture phase: the pivot lives in a
+  // shadow root and retargets its events, so a bubbled listener on document
+  // sees the host rather than the real target and cannot tell inside from out.
+  useEffect(() => {
+    if (!open) return
+    const onDown = (e) => { if (!boxRef.current?.contains(e.target)) setOpen(false) }
+    const onKey  = (e) => { if (e.key === 'Escape') setOpen(false) }
+    document.addEventListener('mousedown', onDown, true)
+    window.addEventListener('keydown', onKey)
+    return () => {
+      document.removeEventListener('mousedown', onDown, true)
+      window.removeEventListener('keydown', onKey)
+    }
+  }, [open])
+
+  const active    = layouts.find(l => l.id === activeLayoutId)
+  const published = layouts.filter(l => l.visibility === 'published')
+  const mine      = layouts.filter(l => l.visibility === 'private')
+
+  const submitSaveAs = () => {
+    const name = saveName.trim()
+    if (!name) return
+    onSaveAs(name, savePublic ? 'published' : 'private')
+    setSaveName('')
+    setOpen(false)
+  }
+
+  const submitRename = (l) => {
+    const name = renameTo.trim()
+    if (name && name !== l.name) onRename(l, name)
+    setRenaming(null)
+  }
+
+  const iconBtn = 'px-1 rounded text-gray-400 hover:text-gray-700 hover:bg-gray-100 leading-none'
+
+  const row = (l) => (
+    <div key={l.id}
+      className={`group flex items-center gap-1 px-2 py-1 rounded cursor-pointer
+        ${l.id === activeLayoutId ? 'bg-blue-50 text-blue-700' : 'hover:bg-gray-50 text-gray-700'}`}
+      onClick={() => { if (renaming !== l.id) { onApply(l); setOpen(false) } }}>
+
+      {renaming === l.id ? (
+        <input autoFocus value={renameTo} onClick={e => e.stopPropagation()}
+          onChange={e => setRenameTo(e.target.value)}
+          onBlur={() => submitRename(l)}
+          onKeyDown={e => {
+            if (e.key === 'Enter')  submitRename(l)
+            if (e.key === 'Escape') setRenaming(null)
+          }}
+          className="flex-1 min-w-0 border border-blue-300 rounded px-1 py-0 outline-none bg-white" />
+      ) : (
+        <>
+          <span className="flex-1 min-w-0 truncate">{l.name}</span>
+          {l.is_default && (
+            <span title="Applied when this forecast is opened for the first time"
+              className="text-amber-500 shrink-0">★</span>
+          )}
+          {l.scope === 'source' && (
+            <span title="Applies to every forecast of this source"
+              className="text-gray-300 shrink-0" style={{fontSize:'9px'}}>ALL</span>
+          )}
+        </>
+      )}
+
+      {/* Withheld rather than offered-and-refused: the server allows a write only
+          to the owner or an admin, and can_edit says which this is. */}
+      {l.can_edit && renaming !== l.id && (
+        <span className="hidden group-hover:flex items-center gap-0.5 shrink-0"
+          onClick={e => e.stopPropagation()}>
+          <button className={iconBtn} title="Rename"
+            onClick={() => { setRenaming(l.id); setRenameTo(l.name) }}>✎</button>
+          {l.visibility === 'private' ? (
+            <button className={iconBtn} title="Publish — everyone on this forecast sees it"
+              onClick={() => onSetVisibility(l, 'published')}>↑</button>
+          ) : (
+            <>
+              {!l.is_default && (
+                <button className={iconBtn} title="Apply this when the forecast is first opened"
+                  onClick={() => onSetDefault(l)}>★</button>
+              )}
+              <button className={iconBtn} title="Unpublish — keep it, but only for you"
+                onClick={() => onSetVisibility(l, 'private')}>↓</button>
+            </>
+          )}
+          <button className={`${iconBtn} hover:text-red-500`} title="Delete"
+            onClick={() => onDelete(l)}>×</button>
+        </span>
+      )}
+
+      {!l.can_edit && (
+        <span className="hidden group-hover:inline text-gray-300 shrink-0 truncate"
+          style={{fontSize:'9px'}} title={`Published by ${l.owner}`}>{l.owner}</span>
+      )}
+    </div>
+  )
+
+  return (
+    <div ref={boxRef} className="relative flex items-center gap-1.5">
+      <span className="text-gray-400 uppercase tracking-wide" style={{fontSize:'10px'}}>Layout</span>
+
+      <button onClick={() => setOpen(o => !o)}
+        className={`flex items-center gap-1 border rounded px-2 py-0.5 max-w-[14rem] transition-colors
+          ${open ? 'border-blue-300 text-blue-700 bg-blue-50'
+                 : 'border-gray-200 text-gray-600 hover:border-gray-400'}`}>
+        <span className="truncate">{active ? active.name : 'Unsaved'}</span>
+        {dirty && <span title="Changed since it was saved" className="text-amber-500 leading-none">•</span>}
+        <span className="text-gray-400 leading-none">▾</span>
+      </button>
+
+      {/* Save is the common action and stays outside the menu, but only when
+          there is something to save it to and it is yours to overwrite. */}
+      {active?.can_edit && dirty && (
+        <button onClick={() => onSave(active)}
+          className="border border-blue-200 text-blue-600 hover:text-blue-800 rounded px-2 py-0.5">
+          Save
+        </button>
+      )}
+
+      {open && (
+        <div className="absolute top-full left-0 mt-1 z-50 w-72 bg-white border border-gray-200
+                        rounded shadow-lg py-1 text-xs">
+
+          {published.length > 0 && (
+            <>
+              <div className="px-2 pt-1 pb-0.5 text-gray-400 uppercase tracking-wide"
+                style={{fontSize:'9px'}}>Published</div>
+              <div className="px-1">{published.map(row)}</div>
+            </>
+          )}
+
+          {mine.length > 0 && (
+            <>
+              <div className="px-2 pt-2 pb-0.5 text-gray-400 uppercase tracking-wide"
+                style={{fontSize:'9px'}}>Mine</div>
+              <div className="px-1">{mine.map(row)}</div>
+            </>
+          )}
+
+          {!layouts.length && (
+            <div className="px-3 py-2 text-gray-400">No saved layouts yet</div>
+          )}
+
+          <div className="border-t border-gray-100 mt-1 pt-1 px-2">
+            <div className="flex items-center gap-1 py-1">
+              <input value={saveName} onChange={e => setSaveName(e.target.value)}
+                onKeyDown={e => { if (e.key === 'Enter') submitSaveAs() }}
+                placeholder="Save current view as…"
+                className="flex-1 min-w-0 border border-gray-300 rounded px-1.5 py-0.5
+                           outline-none focus:border-blue-400" />
+              <button onClick={submitSaveAs} disabled={!saveName.trim()}
+                className="text-blue-600 hover:text-blue-800 px-1 disabled:opacity-30">Save</button>
+            </div>
+            <label className="flex items-center gap-1.5 py-0.5 text-gray-500 cursor-pointer">
+              <input type="checkbox" checked={savePublic}
+                onChange={e => setSavePublic(e.target.checked)} />
+              Publish to everyone on this forecast
+            </label>
+          </div>
+
+          <div className="border-t border-gray-100 mt-1 pt-1 px-2 pb-0.5">
+            <button onClick={() => { onReset(); setOpen(false) }}
+              className="text-gray-400 hover:text-gray-700 py-0.5">
+              Reset to a blank pivot
+            </button>
+          </div>
+        </div>
+      )}
+    </div>
+  )
+}

ui/src/components/OperationPanel.jsx

@@ -11,7 +11,7 @@
 //      flex-1 buttons grew to absurd sizes; everything here is fixed-width and
 //      left-aligned instead.
 
-import { useState } from 'react'
+import { useState, useEffect, useRef, useLayoutEffect } from 'react'
 
 const INPUT = 'border border-gray-200 rounded px-2 py-1 text-xs bg-white w-28 text-right font-mono tabular-nums'
 const TEXT  = 'border border-gray-200 rounded px-2 py-1 text-xs bg-white w-40 font-mono'
@@ -61,6 +61,34 @@ function Button({ onClick, active, children, title }) {
   )
 }
 
+// A label and its control, on a grid. Every row in the panel uses the same label
+// width, so the controls line up down the column instead of each row starting
+// wherever its label happens to end -- which was the whole problem with
+// "copy rows from" sitting above "scale cloned rows by" above "tag".
+const LABEL_W = 'w-24'
+
+function Field({ label, children, hint }) {
+  return (
+    <div className="flex items-start gap-2">
+      <span className={`text-gray-600 whitespace-nowrap shrink-0 pt-1 ${LABEL_W}`}>{label}</span>
+      <div className="flex flex-col gap-1 min-w-0">
+        <div className="flex items-center gap-2 flex-wrap">{children}</div>
+        {hint && <p className="text-gray-500 text-[11px] leading-snug max-w-xs">{hint}</p>}
+      </div>
+    </div>
+  )
+}
+
+// Same 10px uppercase as the ledger table headers, so the groupings read as part
+// of the same family rather than as a second style.
+function SectionLabel({ children }) {
+  return (
+    <div className="text-gray-400 uppercase tracking-wide pt-1" style={{ fontSize: '10px' }}>
+      {children}
+    </div>
+  )
+}
+
 function Segmented({ options, value, onChange }) {
   return (
     <div className="inline-flex rounded border border-gray-200 overflow-hidden w-auto self-start">
@@ -71,22 +99,37 @@ function Segmented({ options, value, onChange }) {
   )
 }
 
-function Submit({ onClick, children, disabled }) {
+function Submit({ onClick, children, disabled, busy }) {
   return (
-    <button onClick={onClick} disabled={disabled}
+    <button onClick={onClick} disabled={disabled || busy}
       className="self-start px-4 py-1.5 rounded text-xs font-medium bg-blue-600 text-white hover:bg-blue-700
-                 disabled:bg-gray-200 disabled:text-gray-600 disabled:cursor-not-allowed whitespace-nowrap">
+                 disabled:bg-gray-200 disabled:text-gray-600 disabled:cursor-not-allowed whitespace-nowrap
+                 inline-flex items-center gap-2">
+      {busy && (
+        <span className="inline-block w-3 h-3 rounded-full border-2 border-white/40 border-t-white animate-spin" />
+      )}
       {children}
     </button>
   )
 }
 
 // ── 1. Selection ────────────────────────────────────────────────────────────
-function SelectionList({ slices, currentTotals, onRemove, onClear }) {
+function SelectionList({ slices, viewScope = [], currentTotals, onRemove, onClear }) {
   const multi    = slices.length > 1
   const perSlice = currentTotals?.perSlice || []
   const valueCol = currentTotals?.valueCol
 
+  // The pivot's own filter. Shown because it scopes every figure below and
+  // every row the operation writes, while appearing in none of the slices --
+  // perspective-click reports only the cell's own dimensions, so without this
+  // the panel prints a selection wider than the one it is acting on.
+  const scopeLine = viewScope
+    .map(([col, op, ...rest]) => {
+      const vals = (Array.isArray(rest[0]) ? rest[0] : rest).filter(v => v !== undefined)
+      return `${col} ${op}${vals.length ? ' ' + vals.join(', ') : ''}`
+    })
+    .join('  ·  ')
+
   if (!slices.length) {
     return (
       <p className="text-gray-600 italic leading-relaxed">
@@ -98,6 +141,12 @@ function SelectionList({ slices, currentTotals, onRemove, onClear }) {
 
   return (
     <div className="min-w-0">
+      {scopeLine && (
+        <div className="mb-1 flex items-baseline gap-1.5 text-[11px]">
+          <span className="text-gray-400 uppercase tracking-wide shrink-0">within</span>
+          <span className="font-mono text-gray-600 truncate" title={scopeLine}>{scopeLine}</span>
+        </div>
+      )}
       <div className="overflow-auto max-h-36 -mx-1 px-1">
         <table className="w-full">
           <tbody>
@@ -214,11 +263,59 @@ function derive(current, edit, dp = 2) {
   return out
 }
 
+// Grouped while you type, because -2000000 and -20000000 are the same shape at
+// a glance and the ledger deals in both.
+//
+// Formats for display only: what leaves here is always the raw string, so the
+// arithmetic upstream never sees a comma. A partly-typed number has to survive
+// intact -- "1." and "-" and "1.50" are all states on the way to a value, and
+// reformatting them into something else as you type makes the field unusable.
+function groupDigits(raw) {
+  const str = String(raw ?? '')
+  if (str === '' || str === '-') return str
+  const neg = str.startsWith('-')
+  const body = neg ? str.slice(1) : str
+  const dot = body.indexOf('.')
+  const whole = (dot === -1 ? body : body.slice(0, dot)).replace(/\D/g, '')
+  const frac  = dot === -1 ? null : body.slice(dot + 1).replace(/\D/g, '')
+  if (whole === '' && frac === null) return neg ? '-' : ''
+  const grouped = whole.replace(/\B(?=(\d{3})+(?!\d))/g, ',')
+  return `${neg ? '-' : ''}${grouped}${dot === -1 ? '' : `.${frac}`}`
+}
+
 function LedgerInput({ value, active, onChange, onFocus, suffix }) {
+  const ref     = useRef(null)
+  const caret   = useRef(null)
+  const display = groupDigits(value)
+
+  // The commas shift every character after them, so a remembered offset lands
+  // in the wrong place. Count digits instead -- those are what the caret is
+  // actually sitting between -- and find that many digits into the new text.
+  useLayoutEffect(() => {
+    const el = ref.current
+    if (!el || caret.current == null) return
+    const wanted = caret.current
+    caret.current = null
+    let seen = 0, pos = display.length
+    for (let i = 0; i < display.length; i++) {
+      if (/[\d.-]/.test(display[i])) seen++
+      if (seen === wanted) { pos = i + 1; break }
+    }
+    if (wanted === 0) pos = 0
+    try { el.setSelectionRange(pos, pos) } catch {}
+  }, [display])
+
   return (
     <span className="inline-flex items-center gap-1">
       <input
-        type="text" inputMode="decimal" value={value} onChange={e => onChange(e.target.value)}
+        ref={ref}
+        type="text" inputMode="decimal" value={display}
+        onChange={e => {
+          const el = e.target
+          const upto = el.value.slice(0, el.selectionStart ?? el.value.length)
+          caret.current = (upto.match(/[\d.-]/g) || []).length
+          onChange(el.value.replace(/,/g, ''))
+        }}
         onFocus={onFocus} placeholder="—"
         className={`border rounded px-2 py-0.5 text-xs w-24 text-right font-mono tabular-nums
           ${active ? 'border-blue-400 bg-blue-50/40 text-gray-800' : 'border-gray-200 bg-white text-gray-700'}`} />
@@ -242,9 +339,16 @@ function ScaleLedger({ currentTotals, scaleInputs, setScaleInputs, scalePlug, se
     const loose    = []
     const byTag    = new Map()
     for (const e of entries) {
-      if (e.key === 'baseline') { baseline.push({ ...e, label: 'Baseline', kind: 'baseline' }); continue }
       const meta = logMeta[e.logid] || {}
-      const tag  = (meta.tag || '').trim()
+      // Named like every other line: the label the pivot shows, then the older
+      // fallbacks. "Baseline" was hardcoded, so a segment called 03 - New Orders
+      // everywhere else read as "Baseline" here alone.
+      if (e.key === 'baseline') {
+        const name = (meta.label || meta.tag || meta.note || '').trim()
+        baseline.push({ ...e, label: name || 'Baseline', kind: 'baseline' })
+        continue
+      }
+      const tag  = (meta.label || meta.tag || '').trim()
       if (tag) {
         const g = byTag.get(tag) ||
           { key: `tag:${tag}`, label: tag, kind: 'tag', value: 0, units: 0, count: 0, first: e.logid }
@@ -257,7 +361,8 @@ function ScaleLedger({ currentTotals, scaleInputs, setScaleInputs, scalePlug, se
         const op = meta.operation || e.iter || 'adjustment'
         loose.push({
           ...e, kind: 'entry', count: 1,
-          label: (meta.note || '').trim() || `${op.charAt(0).toUpperCase()}${op.slice(1)} #${e.logid}`,
+          label: (meta.label || meta.note || '').trim()
+              || `${op.charAt(0).toUpperCase()}${op.slice(1)} #${e.logid}`,
         })
       }
     }
@@ -271,7 +376,22 @@ function ScaleLedger({ currentTotals, scaleInputs, setScaleInputs, scalePlug, se
   const hasExcl  = excl.rows > 0 && (excl.value !== 0 || excl.units !== 0)
   const onTotal  = hasExcl && targetBasis !== 'adjustable'   // 'selected total' is the default
   const grand    = { value: total.value + excl.value, units: total.units + excl.units }
-  const exclName = (currentTotals?.excludedIters || []).join(' / ') || 'excluded'
+  // Named by the segments themselves where we have them -- "02 - Prior Year"
+  // reads as a thing a forecaster recognises, where "reference" names only the
+  // iter band that happens to exclude it.
+  const exclName = (currentTotals?.excluded?.names || []).join(' · ')
+                || (currentTotals?.excludedIters || []).join(' / ')
+                || 'excluded'
+
+  // Everything in the selection is immovable. Worth saying outright: the panel
+  // otherwise prints a row of zeros and leaves the reason to be worked out.
+  const nothingToAdjust = hasExcl && !total.value && !total.units
+
+  // One line per immovable segment. Falls back to the combined figure for a
+  // selection whose rows carry no segment name.
+  const exclLines = (currentTotals?.excluded?.bySegment?.length
+    ? currentTotals.excluded.bySegment
+    : (hasExcl ? [{ name: exclName, ...excl }] : []))
 
   // the basis decides which line the editable rows are measured from
   const basisOf = (key) => {
@@ -367,6 +487,27 @@ function ScaleLedger({ currentTotals, scaleInputs, setScaleInputs, scalePlug, se
       return { ...prev, [key]: { field, raw: cur ? raw : '' } }
     })
 
+  // A running total across the walk, in the primary measure. The lines sum to
+  // Adjustable, and without this you are adding eight-digit numbers in your
+  // head to check that they do.
+  //
+  // Value only: a cumulative price is meaningless -- prices do not add -- and a
+  // second running column for units doubles the width to say something the
+  // value column already implies.
+  // Accumulates in display order across everything: the immovable segments
+  // first, then the walk. So it reads as the whole number being built up --
+  // billed, then booked, then the baseline, then each adjustment -- and closes
+  // on Selected total rather than on Adjustable, which is only the part of it
+  // that can still move.
+  const runningByKey = (() => {
+    const out = new Map()
+    let acc = 0
+    for (const seg of exclLines)  { acc += seg.value || 0; out.set(`final:${seg.name}`, acc) }
+    for (const e of lines)        { acc += e.value   || 0; out.set(e.key, acc) }
+    return out
+  })()
+  const showRunning = !!valueCol && (lines.length + exclLines.length) > 1
+
   const numCell = 'text-right font-mono tabular-nums whitespace-nowrap px-2'
   const rule    = <td className="p-0"><div className="border-t border-gray-300 my-1" /></td>
 
@@ -382,9 +523,42 @@ function ScaleLedger({ currentTotals, scaleInputs, setScaleInputs, scalePlug, se
                 {m.hint && <span className="text-gray-500"> · {m.hint}</span>}
               </th>
             ))}
+            {showRunning && (
+              <th className="text-right font-normal pb-1 px-2 whitespace-nowrap text-gray-500">
+                running
+              </th>
+            )}
           </tr>
         </thead>
         <tbody>
+          {/* What cannot move comes first: it is the constraint the rest is
+              worked out against. Then the walk, which sums to Adjustable, and
+              the two together make the selected total. */}
+          {exclLines.map(seg => (
+            <tr key={seg.name} className="text-amber-700">
+              <td className="pr-3 whitespace-nowrap max-w-[16rem] truncate" title={seg.name}>
+                {seg.name}
+                <span className="ml-1.5 px-1 py-0.5 rounded bg-amber-50 text-amber-700 text-[10px] uppercase tracking-wide">
+                  final
+                </span>
+              </td>
+              {measures.map(m => (
+                <td key={m.key} className={`${numCell} text-amber-700`}>
+                  {m.key === 'price' ? fmtNum(priceOf(seg), m.dp) : fmtNum(seg[m.key], m.dp)}
+                </td>
+              ))}
+              {showRunning && (
+                <td className={`${numCell} text-amber-700`}>
+                  {fmtNum(runningByKey.get(`final:${seg.name}`), 0)}
+                </td>
+              )}
+            </tr>
+          ))}
+
+          {exclLines.length > 0 && (
+            <tr>{rule}{measures.map(m => <td key={m.key} className="p-0 px-2"><div className="border-t border-gray-300 my-1" /></td>)}{showRunning && <td className="p-0 px-2"><div className="border-t border-gray-300 my-1" /></td>}</tr>
+          )}
+
           {/* the walk from baseline to current, by initiative */}
           {lines.map(e => (
             <tr key={e.key} className="text-gray-600">
@@ -398,10 +572,13 @@ function ScaleLedger({ currentTotals, scaleInputs, setScaleInputs, scalePlug, se
                   {m.key === 'price' ? fmtNum(priceOf(e), m.dp) : fmtNum(e[m.key], m.dp)}
                 </td>
               ))}
+              {showRunning && (
+                <td className={`${numCell} text-gray-500`}>{fmtNum(runningByKey.get(e.key), 0)}</td>
+              )}
             </tr>
           ))}
 
-          <tr>{rule}{measures.map(m => <td key={m.key} className="p-0 px-2"><div className="border-t border-gray-300 my-1" /></td>)}</tr>
+          <tr>{rule}{measures.map(m => <td key={m.key} className="p-0 px-2"><div className="border-t border-gray-300 my-1" /></td>)}{showRunning && <td className="p-0 px-2"><div className="border-t border-gray-300 my-1" /></td>}</tr>
 
           <tr className={onTotal ? 'text-gray-600' : 'font-semibold text-gray-700'}>
             <td className="pr-3 whitespace-nowrap">
@@ -410,22 +587,9 @@ function ScaleLedger({ currentTotals, scaleInputs, setScaleInputs, scalePlug, se
             {measures.map(m => (
               <td key={m.key} className={numCell}>{fmtNum(m.current, m.dp)}</td>
             ))}
+            {showRunning && <td className={numCell} />}
           </tr>
 
-          {/* Rows the pivot shows but operations cannot write. Listed so the
-              panel's figures reconcile with what the grid displays. */}
-          {hasExcl && (
-            <tr className="text-gray-600">
-              <td className="pr-3 whitespace-nowrap">
-                {exclName} <span className="text-gray-500">· fixed</span>
-              </td>
-              {measures.map(m => (
-                <td key={m.key} className={numCell}>
-                  {m.key === 'price' ? fmtNum(priceOf(excl), m.dp) : fmtNum(excl[m.key], m.dp)}
-                </td>
-              ))}
-            </tr>
-          )}
 
           {hasExcl && (
             <tr className={onTotal ? 'font-semibold text-gray-700' : 'text-gray-600'}>
@@ -437,10 +601,26 @@ function ScaleLedger({ currentTotals, scaleInputs, setScaleInputs, scalePlug, se
                     : fmtNum(grand[m.key], m.dp)}
                 </td>
               ))}
+              {showRunning && (
+                <td className={`${numCell} font-semibold text-gray-700`}>{fmtNum(grand.value, 0)}</td>
+              )}
             </tr>
           )}
 
-          <tr>{rule}{measures.map(m => <td key={m.key} className="p-0 px-2"><div className="border-t border-gray-300 my-1" /></td>)}</tr>
+          {/* Zeros in the Adjustable row are a true answer to the wrong question:
+              they say how much can move, not why none of it can. Spell it out
+              where the eye already is, rather than leaving the edit rows to fail
+              silently below. */}
+          {nothingToAdjust && (
+            <tr>
+              <td colSpan={measures.length + 1 + (showRunning ? 1 : 0)} className="pt-2 text-amber-700 leading-snug">
+                Nothing in this selection can be adjusted — all of it is {exclName},
+                loaded as {(currentTotals?.excludedIters || []).join(' / ') || 'reference'}.
+              </td>
+            </tr>
+          )}
+
+          <tr>{rule}{measures.map(m => <td key={m.key} className="p-0 px-2"><div className="border-t border-gray-300 my-1" /></td>)}{showRunning && <td className="p-0 px-2"><div className="border-t border-gray-300 my-1" /></td>}</tr>
 
           {/* the edit — three equivalent ways to say the same thing */}
           {FIELDS.map(([field, label]) => (
@@ -467,7 +647,7 @@ function ScaleLedger({ currentTotals, scaleInputs, setScaleInputs, scalePlug, se
 
           {outcome && (
             <>
-              <tr>{rule}{measures.map(m => <td key={m.key} className="p-0 px-2"><div className="border-t border-gray-300 my-1" /></td>)}</tr>
+              <tr>{rule}{measures.map(m => <td key={m.key} className="p-0 px-2"><div className="border-t border-gray-300 my-1" /></td>)}{showRunning && <td className="p-0 px-2"><div className="border-t border-gray-300 my-1" /></td>}</tr>
               <tr className="font-semibold text-gray-700">
                 <td className="pr-3 whitespace-nowrap">Result</td>
                 {measures.map(m => (
@@ -538,9 +718,62 @@ function ScaleLedger({ currentTotals, scaleInputs, setScaleInputs, scalePlug, se
   )
 }
 
+// Completion for a key dimension. The list is fetched as you type rather than up
+// front: part alone has 11,290 distinct values, and a native datalist given all of
+// them is slow to open and no easier to read than a short filtered one.
+function DimValueInput({ col, members, versionId, value, onChange, onBlur, className }) {
+  const [fallback, setFallback] = useState([])
+  const listId  = `pf-vals-${col.cname}`
+  const hasList = !!members?.length
+
+  // Without a member list for this group -- never refreshed, or the column is not
+  // in one -- fall back to the version's own values. That is a 2s scan held in
+  // memory server-side, so it stays debounced rather than firing per keystroke.
+  useEffect(() => {
+    if (hasList || !versionId || !col.is_key) return
+    let cancelled = false
+    const t = setTimeout(async () => {
+      try {
+        const url = `/api/versions/${versionId}/values/${encodeURIComponent(col.cname)}`
+                  + `?limit=50${value ? `&q=${encodeURIComponent(value)}` : ''}`
+        const rows = await fetch(url).then(r => r.ok ? r.json() : [])
+        if (!cancelled) setFallback(Array.isArray(rows) ? rows : [])
+      } catch { if (!cancelled) setFallback([]) }
+    }, 200)
+    return () => { cancelled = true; clearTimeout(t) }
+  }, [hasList, versionId, col.cname, col.is_key, value])
+
+  // The member list is already in memory, so filtering it costs nothing and needs
+  // no debounce -- the options move with the keystroke.
+  const options = hasList
+    ? (() => {
+        const q = (value || '').trim().toLowerCase()
+        const all = members.map(m => m.key_value)
+        return (q ? all.filter(v => v.toLowerCase().includes(q)) : all).slice(0, 50)
+      })()
+    : fallback
+
+  return (
+    <>
+      <input
+        value={value}
+        list={col.is_key ? listId : undefined}
+        onChange={onChange}
+        onBlur={onBlur}
+        placeholder="keep"
+        className={className} />
+      {col.is_key && (
+        <datalist id={listId}>
+          {options.map(o => <option key={o} value={o} />)}
+        </datalist>
+      )}
+    </>
+  )
+}
+
 // ── 2b. Recode / clone form ─────────────────────────────────────────────────
 // Same pairing: the dimension's current value sits beside the box that replaces it.
-function DimForm({ dimCols, setObj, setSet, slices, lookupDerivedCols, extra }) {
+function DimForm({ dimCols, setObj, setSet, slices, lookupDerivedCols, dimMembers, versionId, extra }) {
   const multi = slices.length > 1
   const first = slices[0] || {}
   return (
@@ -563,13 +796,15 @@ function DimForm({ dimCols, setObj, setSet, slices, lookupDerivedCols, extra })
                 <td className="pr-3 py-0.5 text-gray-500 whitespace-nowrap" title={c.cname}>{c.label || c.cname}</td>
                 <td className="px-2 py-0.5 font-mono text-gray-600 max-w-[10rem] truncate" title={String(cur)}>{cur}</td>
                 <td className="pl-2 py-0.5">
-                  <input
+                  <DimValueInput
+                    col={c}
+                    members={c.dim_group ? dimMembers?.[c.dim_group]?.members : null}
+                    versionId={versionId}
                     value={setObj[c.cname] || ''}
                     onChange={e => setSet(s => ({ ...s, [c.cname]: e.target.value }))}
                     onBlur={c.is_key && c.dim_group
                       ? e => lookupDerivedCols(c.cname, e.target.value, setSet)
                       : undefined}
-                    placeholder="keep"
                     className={TEXT} />
                 </td>
               </tr>
@@ -584,9 +819,16 @@ function DimForm({ dimCols, setObj, setSet, slices, lookupDerivedCols, extra })
 
 // Recode and clone change dimensions rather than amounts, but you still want to
 // see how much is on the move — and for clone, what it becomes after scaling.
-function MovingTotal({ currentTotals, verb, factor }) {
-  const t = currentTotals?.total
-  if (!t) return null
+// includeExcluded: clone reads reference rows too, so its preview has to count
+// them. Reporting the adjustable total alone said "Copying 0.00" for a selection
+// made entirely of prior year or plan -- the exact case clone exists for.
+function MovingTotal({ currentTotals, verb, factor, includeExcluded }) {
+  const adj = currentTotals?.total
+  if (!adj) return null
+  const ex = currentTotals?.excluded
+  const t = includeExcluded && ex
+    ? { value: (adj.value || 0) + (ex.value || 0), units: (adj.units || 0) + (ex.units || 0) }
+    : adj
   const { valueCol, unitsCol } = currentTotals
   const scaled = factor != null && factor !== 1
   return (
@@ -648,6 +890,8 @@ function RequestPreview({ payload }) {
 export default function OperationPanel({
   dock,
   slices, setSlices, distinctSlices,
+  viewScope = [],
+  opBusy = null,
   applyMode, setApplyMode,
   currentTotals,
   activeOp, setActiveOp,
@@ -660,8 +904,9 @@ export default function OperationPanel({
   recodeNote, setRecodeNote,
   cloneSet, setCloneSet,
   cloneScale, setCloneScale,
+  cloneFrom, setCloneFrom, cloneOffset, setCloneOffset, cloneSources,
   cloneNote, setCloneNote,
-  dimCols, lookupDerivedCols,
+  dimCols, lookupDerivedCols, dimMembers, versionId,
   buildPayload, submitOp,
 }) {
   const hasSlice   = slices.length > 0
@@ -669,8 +914,10 @@ export default function OperationPanel({
   const horizontal = dock === 'bottom'
 
   const note    = activeOp === 'scale' ? scaleNote : activeOp === 'recode' ? recodeNote : cloneNote
+  const shifting = !!cloneOffset && cloneOffset.trim() !== '' && cloneOffset.trim() !== '0 days'
   const setNote = activeOp === 'scale' ? setScaleNote : activeOp === 'recode' ? setRecodeNote : setCloneNote
   const OP_LABEL = { scale: 'Apply Scale', recode: 'Apply Recode', clone: 'Apply Clone' }
+  const OP_BUSY_LABEL = { scale: 'Applying…', recode: 'Recoding…', clone: 'Cloning…' }
 
   return (
     <div className={horizontal ? 'flex flex-row items-start p-3 gap-5 min-w-0' : 'flex flex-col p-3 gap-3 min-w-0'}>
@@ -688,6 +935,7 @@ export default function OperationPanel({
         )}
         <SelectionList
           slices={slices}
+          viewScope={viewScope}
           currentTotals={currentTotals}
           onRemove={(i) => setSlices(prev => prev.filter((_, x) => x !== i))}
           onClear={() => setSlices([])}
@@ -722,21 +970,76 @@ export default function OperationPanel({
             )}
             {activeOp === 'recode' && (
               <DimForm dimCols={dimCols} setObj={recodeSet} setSet={setRecodeSet}
-                       slices={slices} lookupDerivedCols={lookupDerivedCols}
-                       extra={<MovingTotal currentTotals={currentTotals} verb="Moving" />} />
+                       slices={slices} lookupDerivedCols={lookupDerivedCols} dimMembers={dimMembers} versionId={versionId}
+                       extra={
+                         <div className="pt-1 border-t border-gray-100 mt-1">
+                           <MovingTotal currentTotals={currentTotals} verb="Moving" />
+                         </div>
+                       } />
             )}
             {activeOp === 'clone' && (
               <DimForm dimCols={dimCols} setObj={cloneSet} setSet={setCloneSet}
-                       slices={slices} lookupDerivedCols={lookupDerivedCols}
+                       slices={slices} lookupDerivedCols={lookupDerivedCols} dimMembers={dimMembers} versionId={versionId}
                        extra={
-                         <div className="flex flex-col gap-2">
-                           <div className="flex items-center gap-2">
-                             <span className="text-gray-500">scale cloned rows by</span>
+                         <div className="flex flex-col gap-2 pt-1 border-t border-gray-100 mt-1">
+                           <SectionLabel>source</SectionLabel>
+
+                           {/* The selection is the SOURCE, not the destination: pick the
+                               cells you want to copy -- last December, say -- and the
+                               shift is what lands them in the target period. Naming a
+                               segment narrows that selection to one entry, including the
+                               reference ones operations are normally kept away from,
+                               which is the point: a period with no baseline borrows its
+                               shape from prior year or plan. */}
+                           <Field label="rows from"
+                                  hint={cloneFrom
+                                    ? 'Only rows from that segment, out of everything selected.'
+                                    : undefined}>
+                             <select value={cloneFrom} onChange={e => setCloneFrom(e.target.value)}
+                                     className={`${TEXT} w-48`}>
+                               <option value="">the whole selection</option>
+                               {(cloneSources || []).map(s => (
+                                 <option key={s.id} value={s.id}>
+                                   {s.label || `${s.operation} #${s.id}`}
+                                 </option>
+                               ))}
+                             </select>
+                           </Field>
+
+                           <SectionLabel>changes</SectionLabel>
+
+                           <Field label="shift dates"
+                                  hint={shifting
+                                    ? 'Every date column moves; season and month are re-derived from the calendar. So select the period you are copying from, not the one you are filling.'
+                                    : undefined}>
+                             <input value={cloneOffset} list="pf-clone-offsets"
+                                    onChange={e => setCloneOffset(e.target.value)}
+                                    placeholder="0 days" className={`${TEXT} w-28`} />
+                             <datalist id="pf-clone-offsets">
+                               <option value="12 months" />
+                               <option value="24 months" />
+                               <option value="-90 days" />
+                               <option value="-12 months" />
+                               <option value="0 days" />
+                             </datalist>
+                           </Field>
+
+                           <Field label="scale by">
                              <input type="number" step="any" value={cloneScale}
-                                    onChange={e => setCloneScale(e.target.value)} className={INPUT} />
+                                    onChange={e => setCloneScale(e.target.value)}
+                                    className={`${INPUT} w-28`} />
+                             <span className="text-gray-400 text-[11px]">×</span>
+                           </Field>
+
+                           {/* Sits under the controls it reflects rather than floating
+                               after them, and counts the reference rows clone can now
+                               read -- it used to report the adjustable total, which is
+                               zero when the selection is entirely prior year or plan. */}
+                           <div className="pt-1">
+                             <MovingTotal currentTotals={currentTotals} verb="Copying"
+                                          factor={parseFloat(cloneScale) || 1}
+                                          includeExcluded />
                            </div>
-                           <MovingTotal currentTotals={currentTotals} verb="Copying"
-                                        factor={parseFloat(cloneScale) || 1} />
                          </div>
                        } />
             )}
@@ -747,11 +1050,12 @@ export default function OperationPanel({
       {hasSlice && (
         <Block horizontal={horizontal}>
           <div className="flex flex-col gap-2.5 min-w-0">
+            <SectionLabel>label this change</SectionLabel>
+
             {/* Tag first: it is the field that gives an adjustment meaning later,
                 in the ledger and in the bridge. Completes from initiatives already
                 used on this source; free text is still accepted. */}
-            <div className="flex items-center gap-2">
-              <span className="text-gray-600 whitespace-nowrap w-9">tag</span>
+            <Field label="tag">
               <input
                 value={opTag} onChange={e => setOpTag(e.target.value)}
                 list="pf-tag-options" placeholder="initiative, e.g. reduce_spend"
@@ -760,27 +1064,42 @@ export default function OperationPanel({
                 <button onClick={() => setOpTag('')} title="Clear tag"
                         className="text-gray-500 hover:text-red-500 leading-none px-1">×</button>
               )}
-            </div>
+            </Field>
+
+            {/* Under the tag field and inside the same column, so they read as
+                values for it rather than as a row of unexplained buttons. */}
             {knownTags.length > 0 && (
-              <div className="flex items-center gap-1 flex-wrap">
-                {knownTags.slice(0, 6).map(t => (
-                  <button key={t.tag} onClick={() => setOpTag(t.tag)}
-                    title={`${t.uses} previous use${t.uses === 1 ? '' : 's'}`}
-                    className={`px-2 py-0.5 rounded-full border text-xs whitespace-nowrap ${
-                      opTag.trim() === t.tag
-                        ? 'bg-blue-600 border-blue-600 text-white'
-                        : 'bg-white border-gray-300 text-gray-700 hover:border-blue-400 hover:text-blue-700'}`}>
-                    {t.tag}
-                  </button>
-                ))}
-              </div>
+              <Field label="">
+                <div className="flex items-center gap-1 flex-wrap">
+                  {knownTags.slice(0, 6).map(t => (
+                    <button key={t.tag} onClick={() => setOpTag(t.tag)}
+                      title={`${t.uses} previous use${t.uses === 1 ? '' : 's'}`}
+                      className={`px-2 py-0.5 rounded-full border text-xs whitespace-nowrap ${
+                        opTag.trim() === t.tag
+                          ? 'bg-blue-600 border-blue-600 text-white'
+                          : 'bg-white border-gray-300 text-gray-700 hover:border-blue-400 hover:text-blue-700'}`}>
+                      {t.tag}
+                    </button>
+                  ))}
+                </div>
+              </Field>
             )}
 
-            <div className="flex items-center gap-2">
-              <span className="text-gray-600 whitespace-nowrap w-9">note</span>
-              <input value={note} onChange={e => setNote(e.target.value)} placeholder="optional" className={TEXT} />
+            <Field label="note">
+              <input value={note} onChange={e => setNote(e.target.value)}
+                     placeholder="optional" className={`${TEXT} w-48`} />
+            </Field>
+
+            <div className="pt-1 flex items-center gap-3">
+              <Submit onClick={() => submitOp(activeOp)} busy={!!opBusy}>
+                {opBusy ? OP_BUSY_LABEL[opBusy] : OP_LABEL[activeOp]}
+              </Submit>
+              {opBusy && (
+                <span className="text-gray-500 leading-snug">
+                  Writing rows — the pivot updates when it finishes.
+                </span>
+              )}
             </div>
-            <Submit onClick={() => submitOp(activeOp)}>{OP_LABEL[activeOp]}</Submit>
             <RequestPreview payload={buildPayload(activeOp)} />
           </div>
         </Block>

ui/src/components/Timeline.jsx

@@ -32,11 +32,16 @@ function roundRect(ctx, x, y, w, h, r, fill, stroke) {
   if (stroke) ctx.stroke()
 }
 
-export default function Timeline({ dateFrom, dateTo, offsetYr, offsetMo, type = 'baseline' }) {
+export default function Timeline({ dateFrom, dateTo, offsetMonths = 0, offsetDays = 0, type = 'baseline' }) {
   const canvasRef = useRef(null)
 
-  const offsetMoTotal = (offsetYr || 0) * 12 + (offsetMo || 0)
-  const twoBands = type === 'baseline' && offsetMoTotal > 0
+  // Months and days are kept apart because they are not interchangeable: a month
+  // shift lands on the same day of a different month, a day shift can cross a
+  // month boundary. The preview adds months first, then days, as Postgres does.
+  const offsetMoTotal = offsetMonths || 0
+  const offsetDayTotal = offsetDays || 0
+  const shifted = offsetMoTotal !== 0 || offsetDayTotal !== 0
+  const twoBands = type === 'baseline' && shifted
   const canvasH  = twoBands ? 90 : 52
 
   useEffect(() => {
@@ -61,8 +66,9 @@ export default function Timeline({ dateFrom, dateTo, offsetYr, offsetMo, type =
       const srcEnd   = parseDate(dateTo)
       if (!srcStart || !srcEnd || isNaN(srcStart) || isNaN(srcEnd)) return
 
-      const projStart = addMonths(srcStart, offsetMoTotal)
-      const projEnd   = addMonths(srcEnd,   offsetMoTotal)
+      const addDays = (d, n) => { const x = new Date(d); x.setDate(x.getDate() + n); return x }
+      const projStart = addDays(addMonths(srcStart, offsetMoTotal), offsetDayTotal)
+      const projEnd   = addDays(addMonths(srcEnd,   offsetMoTotal), offsetDayTotal)
 
       const winStart = addMonths(srcStart, -1)
       const winEnd   = addMonths(twoBands ? projEnd : srcEnd, 1)
@@ -147,7 +153,14 @@ export default function Timeline({ dateFrom, dateTo, offsetYr, offsetMo, type =
         ctx.lineTo(px1 - 4, arrowY + 4)
         ctx.closePath()
         ctx.fill()
-        const offsetLabel = '+' + (offsetYr ? offsetYr + 'yr ' : '') + (offsetMo ? offsetMo + 'mo' : '')
+        const yrs  = Math.trunc(offsetMoTotal / 12)
+        const mos  = offsetMoTotal % 12
+        const sign = (offsetMoTotal + offsetDayTotal) < 0 ? '' : '+'
+        const offsetLabel = sign + [
+          yrs ? `${yrs}yr` : '',
+          mos ? `${mos}mo` : '',
+          offsetDayTotal ? `${offsetDayTotal}d` : '',
+        ].filter(Boolean).join(' ')
         ctx.fillStyle = '#64748b'
         ctx.font = '9px system-ui'
         ctx.textAlign = 'center'
@@ -156,7 +169,7 @@ export default function Timeline({ dateFrom, dateTo, offsetYr, offsetMo, type =
     }
     raf = requestAnimationFrame(draw)
     return () => cancelAnimationFrame(raf)
-  }, [dateFrom, dateTo, offsetYr, offsetMo, type, twoBands, canvasH])
+  }, [dateFrom, dateTo, offsetMoTotal, offsetDayTotal, type, twoBands, canvasH])
 
   return <canvas ref={canvasRef} height={canvasH} style={{ width: '100%', display: 'block' }} />
 }

ui/src/main.jsx

@@ -1,9 +1,3 @@
-// MUST be first: it swaps window.IntersectionObserver / window.ResizeObserver
-// for wrappers, and Perspective's viewer captures those constructors when its
-// module is evaluated. Any import that reaches perspective-viewer before this
-// one leaves the shim with nothing to intercept.
-import './observerShim.js'
-
 import { StrictMode } from 'react'
 import { createRoot } from 'react-dom/client'
 import { ThemeProvider } from './theme.jsx'

ui/src/observerShim.js

@@ -1,113 +0,0 @@
-// Perspective's viewer captures the observer constructors at module-evaluation
-// time:
-//
-//     var it = window.ResizeObserver; var st = window.IntersectionObserver;
-//
-// so replacing them on `window` before that module is imported puts us in front
-// of every callback it receives. This file must therefore be the FIRST import in
-// main.jsx -- ES modules evaluate in declaration order, and an import that lands
-// after the chain reaching perspective-viewer is too late to matter.
-//
-// Why bother: the WASM engine has no notion of focus or tab visibility. All it
-// ever sees is an observer callback saying "you are visible again, at this
-// size", and it re-renders in response -- which discards the view, and with it
-// set_depth() and per-node expansion, neither of which lives in ViewConfig.
-// Listening for window 'focus' is only a guess at when that happens. This is the
-// actual event.
-//
-// Everything is passed through to the native observer untouched. We add one
-// CustomEvent, and only for targets that are (or contain) a perspective-viewer,
-// so nothing else on the page pays for this.
-
-export const PF_OBSERVER_EVENT = 'pf-viewer-observed'
-
-function dbg(msg, extra) {
-    let on = false
-    try { on = !!localStorage.getItem('pf_debug') } catch { /* private mode */ }
-    if (!on) return
-    const t = new Date().toISOString().slice(11, 23)
-    if (extra !== undefined) console.log(`[pf-obs ${t}] ${msg}`, extra)
-    else                     console.log(`[pf-obs ${t}] ${msg}`)
-}
-
-// Perspective observes elements inside its own shadow root, and closest() stops
-// at a shadow boundary -- it will not climb from a shadow child out to the host.
-// So walk the tree explicitly, hopping host to host, or the match never fires.
-function touchesViewer(target) {
-    if (!(target instanceof Element)) return false
-    let node = target
-    for (let hops = 0; node && hops < 20; hops++) {
-        if (node.tagName === 'PERSPECTIVE-VIEWER') return true
-        if (node.closest?.('perspective-viewer'))  return true
-        const root = node.getRootNode?.()
-        node = root && root.host ? root.host : node.parentElement
-    }
-    return !!target.querySelector?.('perspective-viewer')
-}
-
-function describe(target) {
-    if (!(target instanceof Element)) return String(target)
-    const root = target.getRootNode?.()
-    return `${target.tagName.toLowerCase()}${target.className ? '.' + String(target.className).split(' ')[0] : ''}`
-        + (root && root.host ? ` (in shadow of ${root.host.tagName.toLowerCase()})` : '')
-}
-
-function wrap(Native, kind) {
-    if (typeof Native !== 'function') return Native
-
-    return class PfObserver extends Native {
-        constructor(callback, options) {
-            super((entries, observer) => {
-                // Perspective's own handler runs first and unchanged. If it throws,
-                // that is its business -- we still report, so a failure upstream is
-                // visible rather than silently swallowing our notification too.
-                try {
-                    callback(entries, observer)
-                } finally {
-                    const hit = entries.some(e => touchesViewer(e.target))
-                    dbg(`${kind} fired on ${entries.length} entr${entries.length === 1 ? 'y' : 'ies'}`
-                        + ` -> ${hit ? 'MATCHED viewer' : 'no viewer match'}`,
-                        entries.map(e => describe(e.target)))
-                    if (hit) {
-                        window.dispatchEvent(new CustomEvent(PF_OBSERVER_EVENT, {
-                            detail: {
-                                kind,
-                                at: Date.now(),
-                                // IntersectionObserver entries carry visibility; Resize
-                                // ones carry geometry. Report whichever exists so the
-                                // listener can tell a re-show from a relayout.
-                                entries: entries.map(e => ({
-                                    isIntersecting:   e.isIntersecting,
-                                    intersectionRatio: e.intersectionRatio,
-                                    width:  e.contentRect?.width,
-                                    height: e.contentRect?.height,
-                                })),
-                            },
-                        }))
-                    }
-                }
-            }, options)
-        }
-    }
-}
-
-let installed = false
-
-export function installObserverShim() {
-    if (installed) return
-    installed = true
-    try {
-        if (window.IntersectionObserver) {
-            window.IntersectionObserver = wrap(window.IntersectionObserver, 'intersection')
-        }
-        if (window.ResizeObserver) {
-            window.ResizeObserver = wrap(window.ResizeObserver, 'resize')
-        }
-    } catch {
-        // A browser that refuses the assignment just means we fall back to the
-        // focus/visibilitychange listeners, which still work -- they are only
-        // less precise about when the rebuild happened.
-    }
-}
-
-installObserverShim()

ui/src/views/Baseline.jsx

@@ -1,5 +1,6 @@
 import { useState, useEffect } from 'react'
 import Timeline from '../components/Timeline.jsx'
+import useAuth from '../auth.jsx'
 
 const OPERATORS = ['BETWEEN', '=', '!=', 'IN', 'NOT IN', 'IS NULL', 'IS NOT NULL']
 
@@ -48,11 +49,28 @@ function getDateRange(groups) {
   return null
 }
 
-function parseOffset(offsetStr) {
-  if (!offsetStr || offsetStr === '0 days') return { yr: 0, mo: 0 }
-  const yr = parseInt(offsetStr.match(/(\d+)\s+year/)?.[1] || 0)
-  const mo = parseInt(offsetStr.match(/(\d+)\s+month/)?.[1] || 0)
-  return { yr, mo }
+// The offset is stored and sent as a Postgres interval, so it is typed as one --
+// "4 months", "1 year", "-90 days". This only parses it far enough to draw the
+// timeline preview; Postgres remains the authority on what is valid, and the
+// server rejects anything it will not accept.
+//
+// It replaced a pair of year/month number spinners, which could not express days
+// at all and were clamped at zero, so a segment could only ever be shifted
+// forward in whole months.
+export function parseInterval(str) {
+  let months = 0, days = 0
+  if (!str) return { months, days }
+  const re = /([+-]?\d+(?:\.\d+)?)\s*(years?|yrs?|y|months?|mons?|mo|weeks?|wks?|w|days?|d)\b/gi
+  for (const [, n, unit] of str.matchAll(re)) {
+    const v = parseFloat(n)
+    const u = unit.toLowerCase()
+    if      (u.startsWith('y'))  months += v * 12
+    else if (u.startsWith('mo') || u === 'mons' || u === 'mon') months += v
+    else if (u.startsWith('w'))  days   += v * 7
+    else if (u.startsWith('d'))  days   += v
+    else                         months += v      // 'm' alone reads as months here
+  }
+  return { months, days }
 }
 
 function emptyCondition(cols) {
@@ -69,6 +87,7 @@ function normalizeFilters(stored) {
 }
 
 export default function Baseline({ sources = [], sourceId, versions = [], versionId, setVersionId, refreshVersions }) {
+  const { user: me } = useAuth()
   const [filterCols,     setFilterCols]     = useState([])
   const [log,            setLog]            = useState([])
 
@@ -80,13 +99,15 @@ export default function Baseline({ sources = [], sourceId, versions = [], versio
 
   // segment form
   const [segType,        setSegType]        = useState('baseline')
-  const [description,    setDescription]    = useState('')
   const [filters,        setFilters]        = useState([])  // [[cond,...], [cond,...]]
   const [useRaw,         setUseRaw]         = useState(false)
   const [rawSql,         setRawSql]         = useState('')
-  const [offsetYr,       setOffsetYr]       = useState(0)
-  const [offsetMo,       setOffsetMo]       = useState(0)
+  const [offset,         setOffset]         = useState('0 days')
   const [segNote,        setSegNote]        = useState('')
+  // Presentation, not definition: what the segment counts toward and how it is
+  // labelled in the pivot. Safe to set at any time, unlike its filters.
+  const [segBucket,      setSegBucket]      = useState('')
+  const [segLabel,       setSegLabel]       = useState('')
   const [submitting,     setSubmitting]     = useState(false)
   const [editingLogId,   setEditingLogId]   = useState(null)
   const [showAddForm,    setShowAddForm]    = useState(false)
@@ -113,10 +134,65 @@ export default function Baseline({ sources = [], sourceId, versions = [], versio
     loadLog()
   }, [versionId])
 
+  // A segment's banner: what it counts toward, independent of pf_iter. Held
+  // locally while typing so the field does not fight the fetched value, and
+  // written on blur.
+  const [buckets, setBuckets] = useState({})
+  // Buckets already in use on this version, for the datalist. There is no stored
+  // bucket order any more: the order is whatever the typed text sorts as, so a
+  // bucket is named "02 - Forecast" and that is the whole mechanism.
+  const [bucketsInUse, setBucketsInUse] = useState([])
+
+  // Label and bucket are presentation, not definition: they change what the pivot
+  // shows and what the segment counts toward, never which rows were loaded. So
+  // they stay editable after adjustments exist, unlike the filters and offset,
+  // where an edit would silently recalibrate scales sized against the old rows.
+  //
+  // Both are only read at load time, hence the reload in the confirmation: the
+  // label is part of the aggregated row the pivot holds, not something it can
+  // re-derive in place.
+  // Same rule the server enforces: your own entries, or an admin's. A segment's
+  // label and bucket name the pivot's columns for everyone in the version, so
+  // they are not the private annotation they look like.
+  const canEdit = (entry) => !!me && (me.is_admin || entry.pf_user === me.username)
+
+  async function saveLogField(entry, field, value) {
+    const next = value.trim()
+    if (next === (entry[field] || '')) return
+    try {
+      const res = await fetch(`/api/log/${entry.id}`, {
+        method:  'PATCH',
+        headers: { 'Content-Type': 'application/json' },
+        body:    JSON.stringify({ [field]: next }),
+      })
+      if (!res.ok) { const d = await res.json(); flash(d.error, 'error'); return }
+      loadLog()
+      flash(next ? `Saved — reload the Forecast view to see it` : 'Cleared')
+    } catch (err) { flash(err.message, 'error') }
+  }
+
+  // Column widths read off the content rather than guessed. ch is the width of a
+  // '0', so for proportional text it runs slightly generous -- which is what is
+  // wanted for an input you are about to type a longer name into. The floors keep
+  // an empty table from collapsing its headers; the ceilings keep one long note
+  // from pushing the numbers off the side.
+  function widthCh(values, min, max) {
+    const longest = values.reduce((n, v) => Math.max(n, String(v || '').length), 0)
+    return `${Math.min(max, Math.max(min, longest + 2))}ch`
+  }
+  const labelW  = widthCh(log.map(e => e.label || e.tag || e.note), 18, 34)
+  const bucketW = widthCh(log.map(e => e.bucket), 16, 24)
+  const noteW   = widthCh(log.map(e => e.note), 22, 44)
+
   function loadLog() {
     fetch(`/api/versions/${versionId}/log`).then(r => r.json()).then(data => {
       setLog(data.filter(e => e.operation === 'baseline' || e.operation === 'reference'))
       setHasForecastOps(data.some(e => ['scale', 'recode', 'clone'].includes(e.operation)))
+
+      // Every bucket in use, adjustments included: typing one on a new segment
+      // should offer the ones already there rather than inviting a near-miss
+      // spelling, which would silently split the column in two.
+      setBucketsInUse([...new Set(data.map(e => (e.bucket || '').trim()).filter(Boolean))].sort())
     })
   }
 
@@ -148,12 +224,14 @@ export default function Baseline({ sources = [], sourceId, versions = [], versio
     const clause = useRaw ? rawSql.trim() : buildFilterClause(filters)
     if (!clause) { flash(useRaw ? 'Enter a WHERE clause' : 'Add at least one filter', 'error'); return }
     const isRef = segType === 'reference'
-    const offsetStr = [offsetYr > 0 ? `${offsetYr} year` : '', offsetMo > 0 ? `${offsetMo} month` : ''].filter(Boolean).join(' ') || '0 days'
+    const offsetStr = offset.trim() || '0 days'
     const endpoint = isRef ? 'reference' : 'baseline'
     const body = {
       where_clause: clause,
-      note: description || segNote,
+      note: segNote,
       date_offset: offsetStr,
+      label:  segLabel.trim(),
+      bucket: segBucket.trim(),
       ...(useRaw ? { raw_where: clause } : { filters }),
     }
     setSubmitting(true)
@@ -189,10 +267,9 @@ export default function Baseline({ sources = [], sourceId, versions = [], versio
     const params = entry.params || {}
     setSegType(entry.operation)
     setSegNote(entry.note || '')
-    setDescription('')
-    const off = parseOffset(params.date_offset)
-    setOffsetYr(off.yr)
-    setOffsetMo(off.mo)
+    setSegLabel(entry.label || '')
+    setSegBucket(entry.bucket || '')
+    setOffset(params.date_offset || '0 days')
     const groups = normalizeFilters(params.filters)
     if (groups) {
       setUseRaw(false)
@@ -218,8 +295,9 @@ export default function Baseline({ sources = [], sourceId, versions = [], versio
   function cancelEdit() {
     setEditingLogId(null)
     setShowAddForm(false)
-    setDescription('')
     setSegNote('')
+    setSegLabel('')
+    setSegBucket('')
     setOffsetYr(0)
     setOffsetMo(0)
     setUseRaw(false)
@@ -276,11 +354,33 @@ export default function Baseline({ sources = [], sourceId, versions = [], versio
     setTimeout(() => setMsg(null), 3000)
   }
 
+  // The names a row falls back to when nobody has named it. Blank means "use the
+  // built-in", so these save on blur like the segment fields and an empty box is
+  // a meaningful value rather than a missing one.
+  async function saveVersionName(field, value) {
+    const next = value.trim()
+    if (next === (selectedVersion?.[field] || '')) return
+    try {
+      const res = await fetch(`/api/versions/${versionId}`, {
+        method:  'PUT',
+        headers: { 'Content-Type': 'application/json' },
+        body:    JSON.stringify({ [field]: next }),
+      })
+      if (!res.ok) { const d = await res.json(); flash(d.error, 'error'); return }
+      await refreshVersions(sourceId)
+      flash('Saved — reload the Forecast view to see it')
+    } catch (err) { flash(err.message, 'error') }
+  }
+
   const selectedVersion = versions.find(v => String(v.id) === versionId)
 
   return (
     <div className="h-full overflow-y-auto bg-gray-50">
-      <div className="p-4 flex flex-col gap-4 max-w-4xl">
+      {/* No page-wide cap and no stretching: at max-w-4xl (896px) the eleven-column
+          segment table always had something smashed, and uncapped it ran to the
+          window. items-start makes each block as wide as its own content needs,
+          which for the table is the measured column widths below. */}
+      <div className="p-4 flex flex-col gap-4 items-start max-w-full">
 
         {msg && (
           <div className={`px-3 py-2 text-xs rounded font-medium ${msg.type === 'error' ? 'bg-red-50 text-red-700' : 'bg-green-50 text-green-700'}`}>
@@ -304,6 +404,31 @@ export default function Baseline({ sources = [], sourceId, versions = [], versio
           )}
         </div>
 
+        {/* Fallback names. Not part of any segment -- they are what the pivot shows
+            for rows nobody has named, so they belong to the version rather than to
+            a log entry. Blank falls back to the built-in in sql_generator's
+            DISPLAY DEFAULTS block. */}
+        {versionId && (
+          <div className="bg-white border border-gray-200 rounded p-3 flex items-end gap-3 flex-wrap">
+            <span className="text-xs text-gray-500 uppercase tracking-wide w-full">Fallback names</span>
+            {[
+              ['adjustment_segment', 'Adjustment segment', '99 - Adjustments'],
+              ['adjustment_bucket',  'Adjustment bucket',  '04 - Forecast'],
+              ['unlabeled_load',     'Unlabeled load',     'Unlabeled'],
+            ].map(([field, label, builtin]) => (
+              <div key={field} className="flex flex-col gap-1">
+                <label className="text-xs text-gray-500">{label}</label>
+                <input
+                  key={`${field}-${versionId}-${selectedVersion?.[field] || ''}`}
+                  defaultValue={selectedVersion?.[field] || ''}
+                  onBlur={e => saveVersionName(field, e.target.value)}
+                  placeholder={builtin}
+                  className="border border-gray-200 rounded px-2 py-1 text-sm w-48" />
+              </div>
+            ))}
+          </div>
+        )}
+
         {showNewVersion && (
           <div className="bg-white border border-gray-200 rounded p-3 flex flex-col gap-3">
             <div className="flex items-end gap-3">
@@ -329,17 +454,27 @@ export default function Baseline({ sources = [], sourceId, versions = [], versio
         {versionId && <>
 
           {/* Segments loaded */}
-          <div className="bg-white border border-gray-200 rounded">
+          <div className="bg-white border border-gray-200 rounded max-w-full">
             <div className="px-3 py-2 border-b border-gray-100 text-xs font-medium text-gray-500 uppercase tracking-wide flex items-center justify-between">
               <span>Segments loaded</span>
               <button onClick={clearBaseline} className="text-red-400 hover:text-red-600 text-xs normal-case font-normal">Clear all baseline</button>
             </div>
-            <table className="w-full text-xs">
+            <datalist id="pf-bucket-options">
+              {[...new Set([...bucketsInUse,
+                            '01 - Prior Prior Year', '02 - Prior Year',
+                            '03 - Plan', '04 - Forecast'])].map(b => (
+                <option key={b} value={b} />
+              ))}
+            </datalist>
+            <table className="text-xs">
               <thead className="bg-gray-50">
                 <tr className="text-left text-gray-400 border-b border-gray-100">
                   <th className="px-3 py-1.5 font-medium w-6"></th>
                   <th className="px-3 py-1.5 font-medium">#</th>
-                  <th className="px-3 py-1.5 font-medium">note</th>
+                  <th className="px-3 py-1.5 font-medium w-20">kind</th>
+                  <th className="px-3 py-1.5 font-medium" style={{ width: labelW }}>label</th>
+                  <th className="px-3 py-1.5 font-medium" style={{ width: noteW }}>note</th>
+                  <th className="px-3 py-1.5 font-medium" style={{ width: bucketW }}>counts toward</th>
                   <th className="px-3 py-1.5 font-medium text-right">rows</th>
                   <th className="px-3 py-1.5 font-medium text-right">{log[0]?.value_col || 'value'}</th>
                   <th className="px-3 py-1.5 font-medium">by</th>
@@ -349,11 +484,11 @@ export default function Baseline({ sources = [], sourceId, versions = [], versio
               </thead>
               <tbody>
                 {log.length === 0 && (
-                  <tr><td colSpan={8} className="px-3 py-3 text-gray-300 italic">No segments loaded yet</td></tr>
+                  <tr><td colSpan={11} className="px-3 py-3 text-gray-300 italic">No segments loaded yet</td></tr>
                 )}
                 {!showAddForm && !editingLogId && (
                   <tr className="border-t border-gray-100">
-                    <td colSpan={8} className="p-0">
+                    <td colSpan={11} className="p-0">
                       <button
                         onClick={() => setShowAddForm(true)}
                         className="w-full px-3 py-2 text-xs text-blue-600 hover:bg-blue-50 text-left font-medium"
@@ -375,11 +510,49 @@ export default function Baseline({ sources = [], sourceId, versions = [], versio
                       >
                         <td className="px-3 py-2 text-gray-400 w-6"><span className="text-gray-300 text-xs">{isOpen ? '▾' : '▸'}</span></td>
                         <td className="px-3 py-2 text-gray-400">{log.length - i}</td>
+                        {/* The operation badge gets its own column. Sharing one with
+                            the note put "reference" hard against "YTD Sales" as soon
+                            as the note column lost width to label and bucket. */}
                         <td className="px-3 py-2">
-                          <span className={`inline-block mr-2 px-1.5 py-0.5 rounded text-xs font-medium ${entry.operation === 'reference' ? 'bg-purple-50 text-purple-600' : 'bg-blue-50 text-blue-600'}`}>
+                          <span className={`inline-block px-1.5 py-0.5 rounded text-xs font-medium ${entry.operation === 'reference' ? 'bg-purple-50 text-purple-600' : 'bg-blue-50 text-blue-600'}`}>
                             {entry.operation}
                           </span>
-                          {entry.note || <span className="text-gray-300">—</span>}
+                        </td>
+                        <td className="px-3 py-2" onClick={e => e.stopPropagation()}>
+                          <input
+                            defaultValue={entry.label || ''}
+                            key={`label-${entry.id}-${entry.label || ''}`}
+                            onBlur={e => saveLogField(entry, 'label', e.target.value)}
+                            readOnly={!canEdit(entry)}
+                            title={canEdit(entry) ? '' : `${entry.pf_user || 'Another account'} made this segment`}
+                            placeholder={entry.tag || entry.note || '—'}
+                            className="w-full border border-transparent hover:border-gray-200
+                                       focus:border-blue-400 rounded px-1 py-0.5 text-xs
+                                       focus:outline-none bg-transparent" />
+                        </td>
+                        {/* One line, clipped against the measured width above. The
+                            note is provenance and can run long, so left to itself it
+                            wrapped and pushed every row to two or three lines.
+                            Expanding the row shows it in full. The cap is on the div,
+                            not the cell: a max-width on a cell in an auto-layout table
+                            is only a hint, and the column can still collapse to
+                            min-content or grow past it. */}
+                        <td className="px-3 py-2">
+                          {entry.note
+                            ? <div className="truncate" style={{ maxWidth: noteW }} title={entry.note}>{entry.note}</div>
+                            : <span className="text-gray-300">—</span>}
+                        </td>
+                        <td className="px-3 py-2" onClick={e => e.stopPropagation()}>
+                          <input
+                            value={buckets[entry.id] ?? entry.bucket ?? ''}
+                            list="pf-bucket-options"
+                            onChange={e => setBuckets(b => ({ ...b, [entry.id]: e.target.value }))}
+                            onBlur={e => saveLogField(entry, 'bucket', e.target.value)}
+                            readOnly={!canEdit(entry)}
+                            title={canEdit(entry) ? '' : `${entry.pf_user || 'Another account'} made this segment`}
+                            placeholder="—"
+                            className="w-full border border-transparent hover:border-gray-200 focus:border-blue-400
+                                       rounded px-1 py-0.5 text-xs focus:outline-none bg-transparent" />
                         </td>
                         <td className="px-3 py-2 text-right text-gray-700 font-mono">
                           {entry.row_count != null ? entry.row_count.toLocaleString() : '—'}
@@ -398,7 +571,7 @@ export default function Baseline({ sources = [], sourceId, versions = [], versio
                       </tr>
                       {isOpen && (
                         <tr key={`${entry.id}-detail`} className="bg-blue-50 border-t border-blue-100">
-                          <td colSpan={6} className="px-2 py-2">
+                          <td colSpan={9} className="px-2 py-2">
                             <div className="bg-white border border-gray-200 rounded">
                               <SegmentForm mode="view" {...view} filterCols={filterCols} />
                             </div>
@@ -437,10 +610,10 @@ export default function Baseline({ sources = [], sourceId, versions = [], versio
                 filters={filters} setFilters={setFilters}
                 useRaw={useRaw} setUseRaw={setUseRaw}
                 rawSql={rawSql} setRawSql={setRawSql}
-                description={description} setDescription={setDescription}
                 segNote={segNote} setSegNote={setSegNote}
-                offsetYr={offsetYr} setOffsetYr={setOffsetYr}
-                offsetMo={offsetMo} setOffsetMo={setOffsetMo}
+                segBucket={segBucket} setSegBucket={setSegBucket}
+                segLabel={segLabel} setSegLabel={setSegLabel}
+                offset={offset} setOffset={setOffset}
                 filterCols={filterCols}
                 onSubmit={loadSegment}
                 submitting={submitting}
@@ -459,17 +632,16 @@ export default function Baseline({ sources = [], sourceId, versions = [], versio
 // derive view-mode props for a saved segment
 function segmentValuesFor(entry, filterCols) {
   const params = entry.params || {}
-  const off = parseOffset(params.date_offset)
   const groups = normalizeFilters(params.filters)
   return {
     segType: entry.operation === 'reference' ? 'reference' : 'baseline',
     filters: groups || (filterCols.length > 0 ? [emptyGroup(filterCols)] : []),
     useRaw: !groups && !!params.where_clause,
     rawSql: params.where_clause || '',
-    description: '',
     segNote: entry.note || '',
-    offsetYr: off.yr,
-    offsetMo: off.mo,
+    segBucket: entry.bucket || '',
+    segLabel: entry.label || '',
+    offset: params.date_offset || '0 days',
   }
 }
 
@@ -479,10 +651,10 @@ function SegmentForm({
   filters, setFilters,
   useRaw, setUseRaw,
   rawSql, setRawSql,
-  description, setDescription,
   segNote, setSegNote,
-  offsetYr, setOffsetYr,
-  offsetMo, setOffsetMo,
+  segBucket, setSegBucket,
+  segLabel, setSegLabel,
+  offset, setOffset,
   filterCols,
   onSubmit,
   submitting,
@@ -552,14 +724,6 @@ function SegmentForm({
         </div>
       </div>
 
-      {/* Description (edit only) */}
-      {mode === 'edit' && (
-        <div className="flex items-center gap-3">
-          <label className="text-xs text-gray-500 w-28 shrink-0">Description</label>
-          <input value={description} onChange={e => setDescription(e.target.value)} placeholder="e.g. FY25 actuals +1yr" className="border border-gray-200 rounded px-2 py-1.5 text-sm flex-1 max-w-sm" />
-        </div>
-      )}
-
       {/* Filters */}
       <div>
         <div className="flex items-center justify-between mb-2">
@@ -659,10 +823,19 @@ function SegmentForm({
       <div className="flex items-center gap-3">
         <label className="text-xs text-gray-500 w-28 shrink-0">Date offset</label>
         <div className="flex items-center gap-2">
-          <input disabled={disabled} type="number" value={offsetYr} min={0} onChange={e => setOffsetYr(parseInt(e.target.value) || 0)} className={`${baseInp} text-sm w-16 text-center`} />
-          <span className="text-xs text-gray-500">yr</span>
-          <input disabled={disabled} type="number" value={offsetMo} min={0} max={11} onChange={e => setOffsetMo(parseInt(e.target.value) || 0)} className={`${baseInp} text-sm w-16 text-center`} />
-          <span className="text-xs text-gray-500">mo</span>
+          <input disabled={disabled} value={offset} list="pf-offset-options"
+                 onChange={e => setOffset(e.target.value)}
+                 placeholder="0 days"
+                 className={`${baseInp} text-sm w-32`} />
+          <datalist id="pf-offset-options">
+            <option value="12 months" />
+            <option value="1 year" />
+            <option value="4 months" />
+            <option value="-90 days" />
+            <option value="-12 months" />
+            <option value="0 days" />
+          </datalist>
+          <span className="text-xs text-gray-400">any Postgres interval</span>
         </div>
       </div>
 
@@ -673,16 +846,32 @@ function SegmentForm({
             <Timeline
               dateFrom={dateRange.from}
               dateTo={dateRange.to}
-              offsetYr={offsetYr}
-              offsetMo={offsetMo}
+              offsetMonths={parseInterval(offset).months}
+              offsetDays={parseInterval(offset).days}
               type={segType}
             />
           </div>
         </div>
       )}
 
-      {/* Note + submit */}
-      <div className="flex items-end gap-3">
+      {/* Label, bucket, note + submit.
+          Label and bucket are presentation: the label is what the pivot shows for
+          this segment, the bucket is what it counts toward. Both are free text and
+          both sort by what is typed, so a leading "01 - " is how ordering is set —
+          which is why they belong here, at the point the segment is defined, as
+          well as being editable in the list afterwards. */}
+      <div className="flex items-end gap-3 flex-wrap">
+        <div className="flex flex-col gap-1 max-w-xs">
+          <label className="text-xs text-gray-500">Label</label>
+          <input disabled={disabled} value={segLabel} onChange={e => setSegLabel(e.target.value)}
+                 placeholder="defaults to the note" className={`${baseInp} text-sm py-1.5`} />
+        </div>
+        <div className="flex flex-col gap-1 max-w-xs">
+          <label className="text-xs text-gray-500">Counts toward</label>
+          <input disabled={disabled} value={segBucket} onChange={e => setSegBucket(e.target.value)}
+                 list="pf-bucket-options" placeholder="e.g. 02 - Forecast"
+                 className={`${baseInp} text-sm py-1.5`} />
+        </div>
         <div className="flex flex-col gap-1 flex-1 max-w-xs">
           <label className="text-xs text-gray-500">Note</label>
           <input disabled={disabled} value={segNote} onChange={e => setSegNote(e.target.value)} placeholder="optional" className={`${baseInp} text-sm py-1.5`} />

ui/src/views/Setup.jsx

@@ -25,6 +25,7 @@ export default function Setup({ refreshSources }) {
   const [sqlStatus,      setSqlStatus]      = useState({})     // sourceId -> bool
   const [saving,         setSaving]         = useState(false)
   const [generating,     setGenerating]     = useState(false)
+  const [refreshingDims, setRefreshingDims] = useState(false)
   const [msg,            setMsg]            = useState(null)
   const [dimPeriodCols,  setDimPeriodCols]  = useState([])
   const [openPeriodIdx,  setOpenPeriodIdx]  = useState(null)
@@ -153,6 +154,35 @@ export default function Setup({ refreshSources }) {
     }
   }
 
+  // Rebuild every keyed dim_group's member list from the source. Deliberate rather
+  // than automatic: it reads the whole source, which for a view over a transaction
+  // table is millions of rows, and the answer only changes when the catalogue does.
+  async function refreshDimMembers() {
+    const groups = [...new Set(cols.filter(c => c.dim_group && c.is_key).map(c => c.dim_group))]
+    if (groups.length === 0) {
+      flash('No dim_group has an is_key column, so there is nothing to build a list from', 'error')
+      return
+    }
+    setRefreshingDims(true)
+    try {
+      const done = []
+      for (const g of groups) {
+        const res  = await fetch(`/api/sources/${selectedSource.id}/dim/${encodeURIComponent(g)}/refresh`,
+                                 { method: 'POST' })
+        const data = await res.json()
+        if (!res.ok) { flash(`${g}: ${data.error}`, 'error'); return }
+        done.push(`${g}: ${data.members.toLocaleString()} members`
+                  + (data.no_longer_in_source ? `, ${data.no_longer_in_source} no longer in source` : '')
+                  + ` (${(data.ms / 1000).toFixed(1)}s)`)
+      }
+      flash(done.join(' · '))
+    } catch (err) {
+      flash(err.message, 'error')
+    } finally {
+      setRefreshingDims(false)
+    }
+  }
+
   async function deleteSource(id, e) {
     e.stopPropagation()
     if (!confirm('Deregister this source? Existing forecast tables are not affected.')) return
@@ -295,6 +325,17 @@ export default function Setup({ refreshSources }) {
                       {saving ? 'Saving…' : 'Save'}
                     </button>
                   )}
+                  {cols.some(c => c.dim_group && c.is_key) && (
+                    <button
+                      onClick={refreshDimMembers}
+                      disabled={refreshingDims || colsDirty}
+                      className="text-xs border border-gray-200 px-3 py-1 rounded hover:bg-gray-50 disabled:opacity-50"
+                      title={colsDirty ? 'Save col meta first'
+                                       : 'Rebuild the member list for each keyed dim_group from the source. Reads the whole source, so it takes a while.'}
+                    >
+                      {refreshingDims ? 'Refreshing…' : 'Refresh master data'}
+                    </button>
+                  )}
                   <button
                     onClick={generateSQL}
                     disabled={generating || colsDirty}
@@ -313,6 +354,7 @@ export default function Setup({ refreshSources }) {
                       <th className="px-3 py-1.5 font-medium">role</th>
                       <th className="px-3 py-1.5 font-medium text-center">key</th>
                       <th className="px-3 py-1.5 font-medium text-center" title="Include this column in the display grain — the load is pre-aggregated to the flagged columns">grain</th>
+                      <th className="px-3 py-1.5 font-medium text-center" title="The column an account's territory is expressed in. Accounts see and change only rows whose value here is on their list. One per source.">territory</th>
                       <th className="px-3 py-1.5 font-medium">group</th>
                       <th className="px-3 py-1.5 font-medium">period col</th>
                       <th className="px-3 py-1.5 font-medium">label</th>
@@ -349,6 +391,25 @@ export default function Setup({ refreshSources }) {
                             className="cursor-pointer disabled:opacity-20"
                           />
                         </td>
+                        {/* Radio, not a checkbox: exactly one column per source,
+                            and the shape of the control should say so rather than
+                            leaving it to a save-time error. */}
+                        <td className="px-3 py-1.5 text-center">
+                          <input
+                            type="radio"
+                            name="pf-territory-col"
+                            checked={!!col.is_territory}
+                            onChange={() => {}}
+                            onClick={() => setEditedCols(prev => {
+                              // clicking the chosen one again clears it, since a
+                              // radio otherwise has no way back to "no territory"
+                              const already = !!prev[i].is_territory
+                              return prev.map((c, x) => ({ ...c, is_territory: !already && x === i }))
+                            })}
+                            disabled={col.role !== 'dimension'}
+                            className="cursor-pointer disabled:opacity-20"
+                          />
+                        </td>
                         <td className="px-3 py-1.5">
                           <input
                             type="text"

ui/vendor/0001-apply-depth-fields-on-config-update.patch

@@ -0,0 +1,45 @@
+diff --git a/rust/perspective-client/src/rust/config/view_config.rs b/rust/perspective-client/src/rust/config/view_config.rs
+index fa4f36e..360b816 100644
+--- a/rust/perspective-client/src/rust/config/view_config.rs
++++ b/rust/perspective-client/src/rust/config/view_config.rs
+@@ -526,6 +526,23 @@ impl ViewConfig {
+         }
+     }
+ 
++    /// `_apply` for a field which is itself `Option`, where `None` in the update
++    /// means "not mentioned" rather than "clear it". `Option<Option<T>>` would be
++    /// needed to express both, and the wire format cannot carry the difference:
++    /// these fields are `skip_serializing_if = "Option::is_none"`, so an absent
++    /// field and an explicit null arrive identically. To lift a depth, send the
++    /// number of levels on that axis rather than clearing it.
++    fn _apply_optional<T: PartialEq>(field: &mut Option<T>, update: Option<T>) -> bool {
++        match update {
++            None => false,
++            Some(_) if *field == update => false,
++            Some(_) => {
++                *field = update;
++                true
++            },
++        }
++    }
++
+     pub fn reset(&mut self, reset_expressions: bool) {
+         let mut config = Self::default();
+         if !reset_expressions {
+@@ -568,6 +585,16 @@ impl ViewConfig {
+         changed = Self::_apply(&mut self.windows, update.windows) || changed;
+         changed = Self::_apply(&mut self.group_rollup_mode, update.group_rollup_mode) || changed;
+         changed = Self::_apply(&mut self.split_rollup_mode, update.split_rollup_mode) || changed;
++
++        // Without these two, a depth can be set when a view is created --
++        // `table.view({ group_by_depth })` -- but never through `restore()`, which
++        // merges a `ViewConfigUpdate` onto the live config. The field arrives,
++        // deserializes, and is then dropped here, so the viewer's config and the
++        // engine never see it and nothing happens. That makes expand/collapse
++        // unreachable for anything driven by `restore()`, which is how a viewer
++        // changes its own configuration.
++        changed = Self::_apply_optional(&mut self.group_by_depth, update.group_by_depth) || changed;
++        changed = Self::_apply_optional(&mut self.split_by_depth, update.split_by_depth) || changed;
+         if self.group_rollup_mode == GroupRollupMode::Total && !self.group_by.is_empty() {
+             tracing::info!("`total` incompatible with `group_by`");
+             changed = true;

ui/vendor/PROVENANCE.txt

@@ -1,7 +1,8 @@
 Built from https://github.com/fleetside72/perspective
-  branch  column-axis-expand-collapse
-  commit  2e3901d652650a33eaf19c2ddf049f7e525ea95b
-  based on v5.4.0
-  built   2026-09-14T02:40:11Z on r710.hptrow.me
+  branch  apply-depth-on-config-update
+  commit  71d1a7508aa86d7e7224ad91e247afe4c201313d
+  based on unknown
+  built   2026-09-17T14:34:21Z on usmidsap02
+  dirty   0 uncommitted file(s) in the source tree at build time
 
 Regenerate with ui/vendor/rebuild-perspective.sh

ui/vendor/README.md

@@ -48,3 +48,74 @@ This is a fork, with the maintenance that implies. The exit is upstream taking
 the change — the patch is small and additive, and the engine work is already
 theirs. When a release ships it, delete this directory and put normal version
 ranges back in `ui/package.json`.
+
+## Applied: depth fields on a config update
+
+`0001-apply-depth-fields-on-config-update.patch` is **in** the vendored
+tarballs as of the 2026-09-17 build, from branch
+`apply-depth-on-config-update`. It fixes an upstream bug in
+`rust/perspective-client/src/rust/config/view_config.rs`:
+`ViewConfig::apply_update` applies ten fields and **neither `group_by_depth`
+nor `split_by_depth` is among them**. So a depth can be set when a view is
+created (`table.view({ group_by_depth: 1 })` — which is what the fork's own
+`depth_test.mjs` exercises) but never through `restore()`, which is how a
+viewer changes its own configuration. The field arrives, deserializes, and is
+discarded before the engine sees it.
+
+`group_by_depth` and the omission are both upstream; the fork mirrored
+`split_by_depth` alongside it faithfully, including the omission.
+
+Note the semantics, from `server.cpp`:
+
+```cpp
+ctx1->set_depth(row_pivot_depth - 1);                        // one-sided
+ctx2->set_depth(t_header::HEADER_ROW, row_pivot_depth - 1);  // two-sided
+```
+
+The config field counts **levels to show**; `view.set_depth()` counts the
+boundary below them. So `group_by_depth: n` equals `set_depth(n - 1)`, and
+`Forecast.jsx` sends `d + 1`.
+
+To apply:
+
+```bash
+cd $PSP_DIR                       # default ~/perspective
+git apply /path/to/pf_app/ui/vendor/0001-apply-depth-fields-on-config-update.patch
+./ui/vendor/rebuild-perspective.sh
+cd ui && npm install
+```
+
+`cargo check` on this patch was clean — the 125 errors it reports without
+`protoc` installed are unresolved generated protobuf modules, none of them in
+`view_config.rs`.
+
+`applyDepth()` in `Forecast.jsx` reads the config back after restoring it and
+falls back to the imperative `view.set_depth()` when the value did not stick.
+With the patched engine installed that fallback should no longer run; it is kept
+for now so an unpatched engine still works, and can be removed once the
+declarative path is confirmed.
+
+### Host tools this tree needs
+
+The repo pins its Rust toolchain (`nightly-2026-06-01`), Emscripten (4.0.9) and
+Binaryen (132) exactly, and pins **nothing** for the host tools — no
+`packageManager`, no `.nvmrc`. So `npm i -g pnpm` and `pip install cmake` both
+give versions this tree was not written against. The script's preflight now
+rejects them, but for the record:
+
+- **pnpm 10.x.** 11+ rejects a repeated `--if-present`, which
+  `sh_perspective.mjs:189` emits once per package in scope.
+- **cmake 3.x**, not 4. Both satisfy the `>= 3.29.5` floor; CMake 4 removed
+  support for `cmake_minimum_required < 3.5`, which the C++ dependencies still
+  declare, and fails partway through the Arrow build.
+- **protoc >= 22** (33.2 known good).
+- **`pnpm pack`, not `npm pack`.** These packages depend on each other as
+  `workspace:^`, and only pnpm rewrites that to a concrete version. An
+  `npm pack` tarball is rejected at install: `Unsupported URL Type
+  "workspace:"`.
+
+Two commits on the build branch are local build concessions rather than
+fixes, and should be dropped before the real patch goes anywhere upstream:
+removing the `postinstall:playwright` step (it runs `playwright install
+--with-deps`, which apt-installs system libraries and so needs root), and
+filling in the `allowBuilds` block pnpm 12 demanded.

ui/vendor/rebuild-perspective.sh

@@ -46,6 +46,18 @@ die()  { echo -e "\033[0;31m  ✗\033[0m $*" >&2; exit 1; }
     Set PSP_DIR to use a different path."
 
 command -v pnpm >/dev/null || die "pnpm not found. Perspective builds with pnpm, not npm."
+
+# The repo pins its Rust toolchain, emsdk and Binaryen exactly, but pins nothing
+# for the host tools -- no packageManager, no .nvmrc -- so `npm i -g pnpm` gets
+# whatever is newest and that is not what this tree was written against. pnpm 11+
+# rejects a repeated `--if-present`, which sh_perspective.mjs emits once per
+# package in scope, and the build dies with:
+#     error: the argument '--if-present' cannot be used multiple times
+pnpm_major=$(pnpm --version | cut -d. -f1)
+if (( pnpm_major > 10 )); then
+    die "pnpm $(pnpm --version) is too new; this tree needs pnpm 10.x.
+    npm i -g pnpm@10"
+fi
 command -v protoc >/dev/null || die "protoc not found.
     Its VERSION selects which protobuf source tree the build clones, and a
     version below 22 pulls a layout the build cannot consume. Needs >= 22
@@ -55,7 +67,16 @@ cmake_ver=$(cmake --version 2>/dev/null | head -1 | grep -oE '[0-9]+\.[0-9]+(\.[
 cmake_major=${cmake_ver%%.*}; cmake_minor=$(echo "$cmake_ver" | cut -d. -f2)
 if (( cmake_major < 3 || (cmake_major == 3 && cmake_minor < 29) )); then
     die "cmake $cmake_ver is too old; Perspective needs >= 3.29.5.
-    A user-level install works: pip3 install --user 'cmake>=3.29.5'"
+    A user-level install works: pip3 install --user 'cmake==3.31.*'"
+fi
+# And not too new: CMake 4 dropped compatibility with cmake_minimum_required
+# below 3.5, which Perspective's C++ dependencies still declare. `pip install
+# cmake` gives 4.x by default, which satisfies the floor above and then fails
+# in the middle of the Arrow build.
+if (( cmake_major >= 4 )); then
+    die "cmake $cmake_ver is too new; CMake 4 removed support for
+    cmake_minimum_required < 3.5, which the C++ dependencies still use.
+    pip3 install --user 'cmake==3.31.*'"
 fi
 
 info "Perspective checkout: $PSP"
@@ -73,10 +94,14 @@ info "Building (this takes ~40 minutes cold, a few minutes warm)…"
 ok "build complete"
 
 # -- pack -------------------------------------------------------------------
+# pnpm pack, not npm pack. These packages depend on each other as
+# `workspace:^`, and only pnpm rewrites that to the concrete version on pack.
+# npm leaves it, and npm install then refuses the tarball outright:
+#     npm error Unsupported URL Type "workspace:": workspace:^
 info "Packing tarballs into $VENDOR"
 rm -f "$VENDOR"/*.tgz
 for p in "${PACKAGES[@]}"; do
-    ( cd "$PSP/$p" && npm pack --pack-destination "$VENDOR" >/dev/null )
+    ( cd "$PSP/$p" && pnpm pack --pack-destination "$VENDOR" >/dev/null )
     ok "$(basename "$p")"
 done