pt/pf_app
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity

Compare commits

base: pt:feature/perspective-column-collapse
Branches Tags
pt:master
pt:feature/perspective-column-collapse
pt:spike/column-branch-collapse
pt:feature/column-collapse
pt:feature/migrations
pt:feature/static-grain
pt:spike/duckdb-virtual-server
..
compare: pt:master
Branches Tags
pt:feature/perspective-column-collapse
pt:spike/column-branch-collapse
pt:feature/column-collapse
pt:master
pt:feature/migrations
pt:feature/static-grain
pt:spike/duckdb-virtual-server

No commits in common. "feature/perspective-column-collapse" and "master" have entirely different histories.
feature/pe ... master

28 changed files with 80 additions and 1127 deletions
Show Stats Expand all files Collapse all files

15
.env.example
View File

@ -4,18 +4,3 @@ DB_NAME=your_database
DB_USER=your_user
DB_PASSWORD=your_password
PORT=3010
# Signs the session cookie. Generate with:
# node -e 'console.log(require("crypto").randomBytes(32).toString("hex"))'
# or let ./pf.sh config do it. Changing it signs everyone out.
SESSION_SECRET=
# Send the session cookie over HTTPS only. Keep true behind a TLS proxy;
# set false only to reach the app over plain HTTP on a trusted network.
COOKIE_SECURE=true
# Reverse proxy hops express should trust for req.ip / protocol. Default 1.
#TRUST_PROXY=1
# Only needed if the UI is served from a different origin than the API.
#CORS_ORIGIN=https://forecast.example.com

73
CLAUDE.md
View File

@ -22,9 +22,8 @@ Data transport architecture options: `pf_perspective_options.md`
## Project layout
```
server.js Express entry point; pg pool; session; type parsers for bigint/numeric
server.js Express entry point; pg pool; type parsers for bigint/numeric
routes/
auth.js POST /api/login, /api/logout, GET /api/me; login throttle
tables.js GET /api/tables, /api/tables/:schema/:tname/preview
sources.js Source registration, col_meta, SQL generation
versions.js Version CRUD, baseline/reference load, data stream
@ -32,15 +31,11 @@ routes/
log.js GET /api/versions/:id/log, DELETE /api/log/:logid
lib/
sql_generator.js buildFilterClause, token substitution helpers
auth.js scrypt hash/verify, requireAuth, sessionUser; `node lib/auth.js hash` CLI
utils.js
setup_sql/
01_schema.sql pf schema DDL — run once to install
02_auth.sql pf.app_user + pf.session
ui/src/
auth.jsx AuthProvider/useAuth; wraps fetch so any 401 returns to login
views/
Login.jsx Sign-in form
Setup.jsx DB browser, source registration, col_meta editor
Baseline.jsx Version management, baseline workbench, reference load
Forecast.jsx Perspective pivot, selection handling, operation dispatch
@ -62,8 +57,6 @@ ui/src/
- **`pf.fc_{tname}_{version_id}`** — one forecast table per version; contains both operational rows (`pf_iter = baseline|scale|recode|clone`) and reference rows (`pf_iter = reference`)
- **`pf.log`** — audit log; every write gets one entry; `slice` + `params` stored as jsonb
- **`pf.sql`** — generated SQL templates per source/operation; tokens substituted at request time
- **`pf.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
### Key token substitution tokens
@ -102,43 +95,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.
@ -151,33 +107,6 @@ All three operations follow the same structure: insert a `pf.log` row in a CTE,
---
## Authentication
Everything under `/api` except the auth routes sits behind a session; the React
app is only mounted once there is one (`Gate` in `main.jsx`), because its load
effects call the API immediately.
- **Accounts:** `pf.app_user` — scrypt hashes from `lib/auth.js`, never plaintext.
Managed with `./pf.sh add-user | passwd | list-users | disable-user | enable-user`;
the password is read on stdin and hashed before it reaches psql.
- **Sessions:** `express-session` + `connect-pg-simple` in `pf.session`, so a
restart doesn't sign everyone out and a session can be revoked by deleting its
row (`disable-user` does exactly that). Cookie `pf.sid`: httpOnly, SameSite=Lax,
Secure unless `COOKIE_SECURE=false`, 12h rolling.
- **Config:** `SESSION_SECRET` is required — the server exits at boot without one.
`TRUST_PROXY` (default 1) makes `req.ip` and secure-cookie detection correct
behind the TLS proxy. `CORS_ORIGIN` is the only way CORS is enabled at all; a
wildcard origin plus a session cookie would be cross-site request forgery by
construction.
- **Login hardening:** `routes/auth.js` throttles to 10 failures per IP per 15
minutes (in-memory), returns one message for unknown/wrong/disabled alike, and
regenerates the session id on success.
**Identity is server-side.** `pf_user`, `created_by` and `closed_by` come from
`sessionUser(req)`, never from the request body — the UI used to send a hardcoded
`pf_user: 'admin'`, which any client could have set to anything. The audit log
now names the account that made the change.
## 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`.

21
install.sh
View File

@ -26,12 +26,6 @@ echo ""
read -p "App port [3030]: " PORT
PORT=${PORT:-3030}
# Session cookies are signed with this; the server refuses to start without it.
SESSION_SECRET=$(node -e 'console.log(require("crypto").randomBytes(32).toString("hex"))')
read -p "Send session cookie over HTTPS only? [Y/n]: " SECURE_ANS
case "${SECURE_ANS:-y}" in [Nn]*) COOKIE_SECURE=false ;; *) COOKIE_SECURE=true ;; esac
# ── Write .env ────────────────────────────────────────────────
cat > .env <<EOF
DB_HOST=${DB_HOST}
@ -40,10 +34,7 @@ DB_NAME=${DB_NAME}
DB_USER=${DB_USER}
DB_PASSWORD=${DB_PASSWORD}
PORT=${PORT}
SESSION_SECRET=${SESSION_SECRET}
COOKIE_SECURE=${COOKIE_SECURE}
EOF
chmod 600 .env
echo "✓ .env written"
# ── npm install ───────────────────────────────────────────────
@ -60,23 +51,15 @@ PGPASSWORD=${DB_PASSWORD} psql \
-p "${DB_PORT}" \
-U "${DB_USER}" \
-d "${DB_NAME}" \
-v ON_ERROR_STOP=1 \
-f setup_sql/01_schema.sql \
-f setup_sql/02_auth.sql
-f setup_sql/01_schema.sql
echo "✓ schema installed"
# ── first account ─────────────────────────────────────────────
echo ""
echo "The app is behind a login. Create the first account now:"
./pf.sh add-user
# ── done ─────────────────────────────────────────────────────
echo ""
echo "========================================"
echo " Install complete"
echo " Start with: npm run dev
More accounts: ./pf.sh add-user"
echo " Start with: npm run dev"
echo " Open: http://$(hostname -I | awk '{print $1}'):${PORT}"
echo "========================================"
echo ""

70
lib/auth.js
View File

@ -1,70 +0,0 @@
// Password hashing and the route guard.
//
// Hashes are scrypt, from node's own crypto — no native build step, and the
// stored form carries its own parameters so they can be raised later without
// invalidating existing rows:
//
// scrypt$<N>$<r>$<p>$<salt base64>$<derived key base64>
const crypto = require('crypto');
const SCRYPT = { N: 16384, r: 8, p: 1, keylen: 64 };
function hashPassword(password, params = SCRYPT) {
const { N, r, p, keylen } = params;
const salt = crypto.randomBytes(16);
const dk = crypto.scryptSync(password, salt, keylen, { N, r, p, maxmem: 256 * 1024 * 1024 });
return `scrypt$${N}$${r}$${p}$${salt.toString('base64')}$${dk.toString('base64')}`;
}
// Constant-time compare. Returns false rather than throwing on a malformed or
// legacy hash, so one bad row can't 500 the login route.
function verifyPassword(password, stored) {
if (typeof stored !== 'string') return false;
const parts = stored.split('$');
if (parts.length !== 6 || parts[0] !== 'scrypt') return false;
const [, N, r, p, saltB64, dkB64] = parts;
try {
const salt = Buffer.from(saltB64, 'base64');
const expected = Buffer.from(dkB64, 'base64');
const actual = crypto.scryptSync(password, salt, expected.length, {
N: Number(N), r: Number(r), p: Number(p), maxmem: 256 * 1024 * 1024,
});
return crypto.timingSafeEqual(actual, expected);
} catch {
return false;
}
}
// Every /api route except the auth ones sits behind this.
function requireAuth(req, res, next) {
if (req.session?.user?.username) return next();
res.status(401).json({ error: 'Not authenticated' });
}
// The identity used for pf.log.pf_user and the created_by/closed_by columns.
// Read from the session only — never from the request body, which the browser
// controls and which used to carry a hardcoded 'admin'.
function sessionUser(req) {
return req.session?.user?.username || null;
}
module.exports = { hashPassword, verifyPassword, requireAuth, sessionUser, 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.
if (require.main === module) {
if (process.argv[2] !== 'hash') {
console.error('usage: node lib/auth.js hash (password on stdin)');
process.exit(2);
}
let input = '';
process.stdin.setEncoding('utf8');
process.stdin.on('data', chunk => { input += chunk; });
process.stdin.on('end', () => {
const password = input.replace(/\r?\n$/, '');
if (!password) { console.error('empty password'); process.exit(2); }
process.stdout.write(hashPassword(password) + '\n');
});
}

69
package-lock.json generated
View File

@ -7,14 +7,11 @@
"": {
"name": "pf_app",
"version": "1.0.0",
"license": "MIT",
"dependencies": {
"apache-arrow": "^21.1.0",
"connect-pg-simple": "^10.0.0",
"cors": "^2.8.5",
"dotenv": "^16.0.0",
"express": "^4.18.2",
"express-session": "^1.19.0",
"pg": "^8.11.3"
},
"devDependencies": {
@ -372,18 +369,6 @@
"node": ">=12.20.0"
}
},
"node_modules/connect-pg-simple": {
"version": "10.0.0",
"resolved": "https://registry.npmjs.org/connect-pg-simple/-/connect-pg-simple-10.0.0.tgz",
"integrity": "sha512-pBGVazlqiMrackzCr0eKhn4LO5trJXsOX0nQoey9wCOayh80MYtThCbq8eoLsjpiWgiok/h+1/uti9/2/Una8A==",
"license": "MIT",
"dependencies": {
"pg": "^8.12.0"
},
"engines": {
"node": "^18.18.0 || ^20.9.0 || >=22.0.0"
}
},
"node_modules/content-disposition": {
"version": "0.5.4",
"resolved": "https://registry.npmjs.org/content-disposition/-/content-disposition-0.5.4.tgz",
@ -597,29 +582,6 @@
"url": "https://opencollective.com/express"
}
},
"node_modules/express-session": {
"version": "1.19.0",
"resolved": "https://registry.npmjs.org/express-session/-/express-session-1.19.0.tgz",
"integrity": "sha512-0csaMkGq+vaiZTmSMMGkfdCOabYv192VbytFypcvI0MANrp+4i/7yEkJ0sbAEhycQjntaKGzYfjfXQyVb7BHMA==",
"license": "MIT",
"dependencies": {
"cookie": "~0.7.2",
"cookie-signature": "~1.0.7",
"debug": "~2.6.9",
"depd": "~2.0.0",
"on-headers": "~1.1.0",
"parseurl": "~1.3.3",
"safe-buffer": "~5.2.1",
"uid-safe": "~2.1.5"
},
"engines": {
"node": ">= 0.8.0"
},
"funding": {
"type": "opencollective",
"url": "https://opencollective.com/express"
}
},
"node_modules/fill-range": {
"version": "7.1.1",
"resolved": "https://registry.npmjs.org/fill-range/-/fill-range-7.1.1.tgz",
@ -1123,15 +1085,6 @@
"node": ">= 0.8"
}
},
"node_modules/on-headers": {
"version": "1.1.0",
"resolved": "https://registry.npmjs.org/on-headers/-/on-headers-1.1.0.tgz",
"integrity": "sha512-737ZY3yNnXy37FHkQxPzt4UZ2UWPWiCZWLvFZ4fu5cueciegX0zGPnrlY6bwRg4FdQOe9YU8MkmJwGhoMybl8A==",
"license": "MIT",
"engines": {
"node": ">= 0.8"
}
},
"node_modules/parseurl": {
"version": "1.3.3",
"resolved": "https://registry.npmjs.org/parseurl/-/parseurl-1.3.3.tgz",
@ -1152,7 +1105,6 @@
"resolved": "https://registry.npmjs.org/pg/-/pg-8.20.0.tgz",
"integrity": "sha512-ldhMxz2r8fl/6QkXnBD3CR9/xg694oT6DZQ2s6c/RI28OjtSOpxnPrUCGOBJ46RCUxcWdx3p6kw/xnDHjKvaRA==",
"license": "MIT",
"peer": true,
"dependencies": {
"pg-connection-string": "^2.12.0",
"pg-pool": "^3.13.0",
@ -1324,15 +1276,6 @@
"url": "https://github.com/sponsors/ljharb"
}
},
"node_modules/random-bytes": {
"version": "1.0.0",
"resolved": "https://registry.npmjs.org/random-bytes/-/random-bytes-1.0.0.tgz",
"integrity": "sha512-iv7LhNVO047HzYR3InF6pUcUsPQiHTM1Qal51DcGSuZFBil1aBBWG5eHPNek7bvILMaYJ/8RU1e8w1AMdHmLQQ==",
"license": "MIT",
"engines": {
"node": ">= 0.8"
}
},
"node_modules/range-parser": {
"version": "1.2.1",
"resolved": "https://registry.npmjs.org/range-parser/-/range-parser-1.2.1.tgz",
@ -1649,18 +1592,6 @@
"node": ">=12.17"
}
},
"node_modules/uid-safe": {
"version": "2.1.5",
"resolved": "https://registry.npmjs.org/uid-safe/-/uid-safe-2.1.5.tgz",
"integrity": "sha512-KPHm4VL5dDXKz01UuEd88Df+KzynaohSL9fBh096KWAxSKZQDI2uBrVqtvRM4rwrIrRRKsdLNML/lnaaVSRioA==",
"license": "MIT",
"dependencies": {
"random-bytes": "~1.0.0"
},
"engines": {
"node": ">= 0.8"
}
},
"node_modules/undefsafe": {
"version": "2.0.5",
"resolved": "https://registry.npmjs.org/undefsafe/-/undefsafe-2.0.5.tgz",

2
package.json
View File

@ -11,11 +11,9 @@
},
"dependencies": {
"apache-arrow": "^21.1.0",
"connect-pg-simple": "^10.0.0",
"cors": "^2.8.5",
"dotenv": "^16.0.0",
"express": "^4.18.2",
"express-session": "^1.19.0",
"pg": "^8.11.3"
},
"devDependencies": {

261
pf.sh
View File

@ -4,7 +4,6 @@ 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 (interactive menu)
# ---------------------------------------------------------------------------
@ -68,33 +67,13 @@ require_service() {
service_installed || die "systemd service not installed. Run: ./pf.sh install-service"
}
# The keys cmd_config manages; anything else in .env is left alone.
ENV_KEYS=(DB_HOST DB_PORT DB_NAME DB_USER DB_PASSWORD PORT SESSION_SECRET COOKIE_SECURE)
env_get() {
[[ -f "$ENV_FILE" ]] || return 0
grep -E "^$1=" "$ENV_FILE" | tail -1 | cut -d= -f2- | tr -d '"' || true
}
# psql against the DB_* connection in .env; extra args are passed through.
run_psql() {
PGPASSWORD="${DB_PASSWORD:-}" psql \
-h "${DB_HOST:-localhost}" \
-p "${DB_PORT:-5432}" \
-U "${DB_USER}" \
-d "${DB_NAME}" \
"$@"
}
db_ping() {
load_env
if [[ -z "${DB_NAME:-}" || -z "${DB_USER:-}" ]]; then
warn "DB_NAME / DB_USER not set in .env"
return 1
fi
local url="${DATABASE_URL:-}"
[[ -z "$url" ]] && { warn "DATABASE_URL not set in .env"; return 1; }
# Use psql if available for a real connectivity check
if command -v psql &>/dev/null; then
run_psql -tAc "SELECT 1" &>/dev/null && return 0 || return 1
psql "$url" -c "SELECT 1" &>/dev/null && return 0 || return 1
else
warn "psql not in PATH — skipping live DB check"
return 0
@ -189,25 +168,18 @@ cmd_logs() {
cmd_db_setup() {
require_env; load_env
[[ -n "${DB_NAME:-}" && -n "${DB_USER:-}" ]] || die "DB_NAME / DB_USER not set in .env — run: ./pf.sh config"
local url="${DATABASE_URL:-}"
[[ -z "$url" ]] && die "DATABASE_URL not set in .env"
command -v psql &>/dev/null || die "psql not found — install postgresql-client"
echo
bold "DB Setup — will run: setup_sql/01_schema.sql, setup_sql/02_auth.sql"
warn "This creates the pf schema, tables, and the account/session tables."
warn "Safe to re-run (CREATE IF NOT EXISTS)."
bold "DB Setup — will run: setup_sql/01_schema.sql"
warn "This creates the pf schema and tables. Safe to re-run (CREATE IF NOT EXISTS)."
read -rp " Continue? [y/N] " confirm
[[ "$confirm" =~ ^[Yy]$ ]] || { echo "Aborted."; return; }
run_psql -v ON_ERROR_STOP=1 -f "${APP_DIR}/setup_sql/01_schema.sql"
run_psql -v ON_ERROR_STOP=1 -f "${APP_DIR}/setup_sql/02_auth.sql"
psql "$url" -f "${APP_DIR}/setup_sql/01_schema.sql"
success "Schema applied."
local n
n=$(run_psql -tAc "SELECT count(*) FROM pf.app_user WHERE is_active" 2>/dev/null || echo 0)
if [[ "${n:-0}" == "0" ]]; then
warn "No active accounts yet — create one with: ./pf.sh add-user"
fi
}
cmd_config() {
@ -216,193 +188,41 @@ cmd_config() {
echo " File: $ENV_FILE"
echo
local cur_host cur_port cur_name cur_user cur_pass cur_app_port
cur_host=$(env_get DB_HOST)
cur_port=$(env_get DB_PORT)
cur_name=$(env_get DB_NAME)
cur_user=$(env_get DB_USER)
cur_pass=$(env_get DB_PASSWORD)
cur_app_port=$(env_get PORT)
local current_url=""
local current_port=""
local current_user=""
local input
read -rp " DB_HOST [${cur_host:-localhost}]: " input
local host="${input:-${cur_host:-localhost}}"
read -rp " DB_PORT [${cur_port:-5432}]: " input
local port="${input:-${cur_port:-5432}}"
read -rp " DB_NAME [${cur_name:-not set}]: " input
local name="${input:-$cur_name}"
[[ -z "$name" ]] && die "DB_NAME is required."
read -rp " DB_USER [${cur_user:-$USER}]: " input
local user="${input:-${cur_user:-$USER}}"
if [[ -n "$cur_pass" ]]; then
read -rsp " DB_PASSWORD [keep existing]: " input; echo
else
read -rsp " DB_PASSWORD: " input; echo
fi
local pass="${input:-$cur_pass}"
read -rp " PORT (app) [${cur_app_port:-3010}]: " input
local app_port="${input:-${cur_app_port:-3010}}"
# Session cookies are signed with this; regenerating it signs everyone out,
# so an existing secret is kept rather than re-rolled on every config run.
local secret
secret=$(env_get SESSION_SECRET)
if [[ -z "$secret" ]]; then
secret=$(node -e 'console.log(require("crypto").randomBytes(32).toString("hex"))')
success "SESSION_SECRET generated."
else
success "SESSION_SECRET kept (delete the line in .env to re-roll)."
fi
local cur_secure
cur_secure=$(env_get COOKIE_SECURE)
read -rp " COOKIE_SECURE — HTTPS-only cookie [${cur_secure:-true}]: " input
local cookie_secure="${input:-${cur_secure:-true}}"
# Rewrite the managed keys, carrying over any other lines already in .env.
local tmp
tmp=$(mktemp)
cat > "$tmp" <<EOF
DB_HOST=${host}
DB_PORT=${port}
DB_NAME=${name}
DB_USER=${user}
DB_PASSWORD=${pass}
PORT=${app_port}
SESSION_SECRET=${secret}
COOKIE_SECURE=${cookie_secure}
EOF
if [[ -f "$ENV_FILE" ]]; then
local managed
managed=$(IFS='|'; echo "${ENV_KEYS[*]}")
grep -vE "^(${managed})=" "$ENV_FILE" | grep -vE '^[[:space:]]*$' >> "$tmp" || true
current_url=$(grep -E '^DATABASE_URL=' "$ENV_FILE" | cut -d= -f2- | tr -d '"' || true)
current_port=$(grep -E '^PORT=' "$ENV_FILE" | cut -d= -f2- | tr -d '"' || true)
current_user=$(grep -E '^PF_USER=' "$ENV_FILE" | cut -d= -f2- | tr -d '"' || true)
fi
mv "$tmp" "$ENV_FILE"
read -rp " DATABASE_URL [${current_url:-not set}]: " input_url
local url="${input_url:-$current_url}"
[[ -z "$url" ]] && die "DATABASE_URL is required."
read -rp " PORT [${current_port:-3010}]: " input_port
local port="${input_port:-${current_port:-3010}}"
read -rp " PF_USER [${current_user:-$USER}]: " input_user
local pf_user="${input_user:-${current_user:-$USER}}"
cat > "$ENV_FILE" <<EOF
DATABASE_URL=${url}
PORT=${port}
PF_USER=${pf_user}
EOF
chmod 600 "$ENV_FILE"
success ".env written."
if db_ping; then
success "Database connection verified."
else
warn "Could not reach the database — double-check the DB_* settings."
warn "Could not reach the database — double-check DATABASE_URL."
fi
}
# -- Accounts ----------------------------------------------------------------
# Reads a password twice without echo and hashes it with lib/auth.js, so the
# plaintext never reaches argv, psql, or the shell history.
read_new_password() {
local p1 p2
# Prompts and their newlines go to stderr: stdout is the hash, and a stray
# newline there ends up prefixed to it by the caller's $( ).
read -rsp " Password: " p1; echo >&2
[[ -z "$p1" ]] && { error "Password cannot be empty."; return 1; }
read -rsp " Confirm : " p2; echo >&2
[[ "$p1" != "$p2" ]] && { error "Passwords do not match."; return 1; }
printf '%s' "$p1" | node "${APP_DIR}/lib/auth.js" hash
}
# psql single-quoted literal: double any embedded quote.
sql_lit() { printf "%s" "${1//\'/\'\'}"; }
cmd_add_user() {
require_env; load_env
check_node >/dev/null
echo; bold "Add account"
local username display hash
read -rp " Username: " username
[[ -z "$username" ]] && die "Username is required."
read -rp " Display name [${username}]: " display
display="${display:-$username}"
hash=$(read_new_password) || return 1
run_psql -v ON_ERROR_STOP=1 -tAc "
WITH ins AS (
INSERT INTO pf.app_user (username, pass_hash, display_name)
VALUES ('$(sql_lit "$username")', '$(sql_lit "$hash")', '$(sql_lit "$display")')
ON CONFLICT (username) DO NOTHING
RETURNING id
) SELECT id FROM ins" | grep -q . \
&& success "Account '${username}' created." \
|| die "Account '${username}' already exists — change its password with: ./pf.sh passwd"
}
cmd_passwd() {
require_env; load_env
check_node >/dev/null
echo; bold "Change password"
local username hash
read -rp " Username: " username
[[ -z "$username" ]] && die "Username is required."
hash=$(read_new_password) || return 1
run_psql -v ON_ERROR_STOP=1 -tAc "
WITH upd AS (
UPDATE pf.app_user SET pass_hash = '$(sql_lit "$hash")'
WHERE lower(username) = lower('$(sql_lit "$username")')
RETURNING id
) SELECT id FROM upd" | grep -q . \
&& success "Password updated for '${username}'." \
|| die "No such account: ${username}"
}
cmd_list_users() {
require_env; load_env
echo; bold "Accounts"
run_psql -c "
SELECT username, display_name, is_active,
to_char(last_login_at, 'YYYY-MM-DD HH24:MI') AS last_login
FROM pf.app_user ORDER BY username"
}
# 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() {
require_env; load_env
local username="${1:-}"
[[ -z "$username" ]] && { read -rp " Username to disable: " username; }
[[ -z "$username" ]] && die "Username is required."
run_psql -v ON_ERROR_STOP=1 -tAc "
WITH upd AS (
UPDATE pf.app_user SET is_active = false
WHERE lower(username) = lower('$(sql_lit "$username")')
RETURNING id
) SELECT id FROM upd" | grep -q . \
|| die "No such account: ${username}"
run_psql -v ON_ERROR_STOP=1 -c "
DELETE FROM pf.session
WHERE sess::jsonb -> 'user' ->> 'username' ILIKE '$(sql_lit "$username")'" >/dev/null
success "Account '${username}' disabled and signed out."
}
cmd_enable_user() {
require_env; load_env
local username="${1:-}"
[[ -z "$username" ]] && { read -rp " Username to enable: " username; }
[[ -z "$username" ]] && die "Username is required."
run_psql -v ON_ERROR_STOP=1 -tAc "
WITH upd AS (
UPDATE pf.app_user SET is_active = true
WHERE lower(username) = lower('$(sql_lit "$username")')
RETURNING id
) SELECT id FROM upd" | grep -q . \
&& success "Account '${username}' enabled." \
|| die "No such account: ${username}"
}
cmd_install_service() {
require_systemd
require_env
@ -483,14 +303,9 @@ interactive_menu() {
echo " 5) status service + DB + git info"
echo " 6) logs tail journald logs"
echo " 7) db-setup apply setup_sql/01_schema.sql"
echo " 8) config set DB connection + app PORT"
echo " 8) config set DATABASE_URL / PORT / PF_USER"
echo " 9) install-service create systemd unit file"
echo " 10) uninstall-service remove systemd unit file"
echo " 11) add-user create a login account"
echo " 12) passwd change an account password"
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 " q) quit"
echo
read -rp " Choice: " choice
@ -505,11 +320,6 @@ interactive_menu() {
8|config) cmd_config ;;
9|install-service) cmd_install_service ;;
10|uninstall-service) cmd_uninstall_service ;;
11|add-user) cmd_add_user ;;
12|passwd) cmd_passwd ;;
13|list-users) cmd_list_users ;;
14|disable-user) cmd_disable_user ;;
15|enable-user) cmd_enable_user ;;
q|Q|quit|exit) echo "Bye."; exit 0 ;;
*) warn "Unknown option: $choice" ;;
esac
@ -529,11 +339,6 @@ case "${1:-}" in
config) cmd_config ;;
install-service) cmd_install_service ;;
uninstall-service) cmd_uninstall_service ;;
add-user) cmd_add_user ;;
passwd) cmd_passwd ;;
list-users) cmd_list_users ;;
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" ;;
esac

107
routes/auth.js
View File

@ -1,107 +0,0 @@
const express = require('express');
const { verifyPassword } = require('../lib/auth');
// Per-IP login throttle. In-memory on purpose: it only has to blunt online
// guessing, and a counter that resets on restart is the acceptable cost of not
// writing a failed-attempt row for every knock on an internet-facing port.
const WINDOW_MS = 15 * 60 * 1000;
const MAX_ATTEMPTS = 10;
const attempts = new Map(); // ip -> { count, resetAt }
function tooManyAttempts(ip) {
const rec = attempts.get(ip);
if (!rec || Date.now() > rec.resetAt) return false;
return rec.count >= MAX_ATTEMPTS;
}
function recordFailure(ip) {
const rec = attempts.get(ip);
if (!rec || Date.now() > rec.resetAt) {
attempts.set(ip, { count: 1, resetAt: Date.now() + WINDOW_MS });
} else {
rec.count += 1;
}
}
// Keep the map from growing without bound on a long-lived process.
setInterval(() => {
const now = Date.now();
for (const [ip, rec] of attempts) if (now > rec.resetAt) attempts.delete(ip);
}, WINDOW_MS).unref();
module.exports = function(pool) {
const router = express.Router();
router.post('/login', async (req, res) => {
const ip = req.ip;
if (tooManyAttempts(ip)) {
return res.status(429).json({ error: 'Too many failed attempts. Try again later.' });
}
const username = String(req.body?.username || '').trim();
const password = String(req.body?.password || '');
if (!username || !password) {
return res.status(400).json({ error: 'Username and password are required' });
}
try {
const result = await pool.query(
`SELECT id, username, display_name, pass_hash, is_active
FROM pf.app_user WHERE lower(username) = lower($1)`,
[username]
);
const user = result.rows[0];
// Same message and roughly the same work either way: no unknown
// user / wrong password / disabled distinction to enumerate.
const ok = user && user.is_active && verifyPassword(password, user.pass_hash);
if (!ok) {
recordFailure(ip);
return res.status(401).json({ error: 'Invalid username or password' });
}
// New session id on login — an existing cookie can't be fixated.
req.session.regenerate(err => {
if (err) {
console.error(err);
return res.status(500).json({ error: 'Could not start session' });
}
req.session.user = {
id: user.id,
username: user.username,
display_name: user.display_name,
};
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));
req.session.save(err2 => {
if (err2) {
console.error(err2);
return res.status(500).json({ error: 'Could not start session' });
}
attempts.delete(ip);
res.json({ user: req.session.user });
});
});
} catch (err) {
console.error(err);
res.status(500).json({ error: err.message });
}
});
router.post('/logout', (req, res) => {
const name = req.session?.cookie && req.app.get('session cookie name');
req.session.destroy(err => {
if (err) console.error(err);
res.clearCookie(name || 'pf.sid');
res.json({ ok: true });
});
});
// The UI calls this on load to decide between the login screen and the app.
router.get('/me', (req, res) => {
if (!req.session?.user) return res.status(401).json({ error: 'Not authenticated' });
res.json({ user: req.session.user });
});
return router;
};

19
routes/operations.js
View File

@ -1,7 +1,6 @@
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 { fcTable } = require('../lib/utils');
module.exports = function(pool) {
@ -305,8 +304,7 @@ 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 pf_user = sessionUser(req);
const { where_clause, date_offset, pf_user, note, filters, raw_where } = req.body;
const dateOffset = date_offset || '0 days';
const filterClause = (raw_where || where_clause || '').trim() || 'TRUE';
try {
@ -341,8 +339,7 @@ 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 pf_user = sessionUser(req);
const { where_clause, date_offset, pf_user, note, filters, raw_where } = req.body;
const dateOffset = date_offset || '0 days';
const filterClause = (raw_where || where_clause || '').trim() || 'TRUE';
@ -448,8 +445,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 pf_user = sessionUser(req);
const { where_clause, date_offset, pf_user, note, filters, raw_where } = req.body;
const dateOffset = date_offset || '0 days';
const filterClause = (raw_where || where_clause || '').trim() || 'TRUE';
try {
@ -482,8 +478,7 @@ module.exports = function(pool) {
// target or by an increment. With several slices selected, apply_mode decides
// whether they are treated as one pool ('prorate') or independently ('each').
router.post('/versions/:id/scale', async (req, res) => {
const { note, apply_mode } = req.body;
const pf_user = sessionUser(req);
const { pf_user, note, apply_mode } = req.body;
const slices = normalizeSlices(req.body);
if (slices.length === 0) return res.status(400).json({ error: 'slice is required' });
@ -570,8 +565,7 @@ module.exports = function(pool) {
// recode dimension values on one or more slices
// inserts negative rows to zero out the original, positive rows with new dimension values
router.post('/versions/:id/recode', async (req, res) => {
const { note, set, apply_mode } = req.body;
const pf_user = sessionUser(req);
const { pf_user, note, set, apply_mode } = req.body;
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' });
@ -624,8 +618,7 @@ 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 pf_user = sessionUser(req);
const { pf_user, note, set, scale, apply_mode } = req.body;
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' });

4
routes/sources.js
View File

@ -1,6 +1,5 @@
const express = require('express');
const { generateSQL } = require('../lib/sql_generator');
const { sessionUser } = require('../lib/auth');
module.exports = function(pool) {
const router = express.Router();
@ -21,8 +20,7 @@ module.exports = function(pool) {
// register a source table
// auto-populates col_meta from information_schema with role='ignore'
router.post('/sources', async (req, res) => {
const { schema, tname, label } = req.body;
const created_by = sessionUser(req);
const { schema, tname, label, created_by } = req.body;
if (!schema || !tname) {
return res.status(400).json({ error: 'schema and tname are required' });
}

6
routes/versions.js
View File

@ -1,6 +1,5 @@
const express = require('express');
const { fcTable, mapType } = require('../lib/utils');
const { sessionUser } = require('../lib/auth');
module.exports = function(pool) {
const router = express.Router();
@ -23,8 +22,7 @@ module.exports = function(pool) {
// inserts version row, then CREATE TABLE pf.fc_{tname}_{version_id} in one transaction
router.post('/sources/:id/versions', async (req, res) => {
const sourceId = parseInt(req.params.id);
const { name, description, exclude_iters } = req.body;
const created_by = sessionUser(req);
const { name, description, created_by, exclude_iters } = req.body;
if (!name) return res.status(400).json({ error: 'name is required' });
const client = await pool.connect();
@ -264,7 +262,7 @@ ${colDefs},
// close a version — blocks further edits
router.post('/versions/:id/close', async (req, res) => {
const pf_user = sessionUser(req);
const { pf_user } = req.body;
try {
const result = await pool.query(`
UPDATE pf.version

51
server.js
View File

@ -1,10 +1,7 @@
require('dotenv').config();
const express = require('express');
const cors = require('cors');
const session = require('express-session');
const PgSession = require('connect-pg-simple')(session);
const { Pool, types } = require('pg');
const { requireAuth } = require('./lib/auth');
// Return bigint (oid 20) and numeric (oid 1700) as JS numbers instead of strings,
// so apache-arrow's tableFromJSON infers Int/Float64 rather than Dictionary<Utf8>.
@ -12,15 +9,7 @@ types.setTypeParser(20, v => v === null ? null : Number(v));
types.setTypeParser(1700, v => v === null ? null : Number(v));
const app = express();
// Sessions ride on a cookie, so a wildcard CORS origin would let any site make
// credentialed calls on behalf of a logged-in user. The UI is served from this
// same origin and needs no CORS at all; set CORS_ORIGIN only for a separate
// front-end host, and it is then allowed by name, never by wildcard.
if (process.env.CORS_ORIGIN) {
app.use(cors({ origin: process.env.CORS_ORIGIN.split(',').map(o => o.trim()), credentials: true }));
}
app.use(cors());
app.use(express.json());
app.use(express.static('public/app'));
@ -37,44 +26,6 @@ pool.on('error', (err) => {
console.error('pg pool error', err);
});
// ── Authentication ────────────────────────────────────────────
// Refuse to boot without a secret rather than fall back to a default one:
// a predictable secret means forgeable session cookies.
const sessionSecret = process.env.SESSION_SECRET;
if (!sessionSecret) {
console.error('SESSION_SECRET is not set. Run: ./pf.sh config');
process.exit(1);
}
// TLS terminates at the reverse proxy, so express has to trust its headers for
// req.ip (the login throttle) and for secure-cookie detection to be right.
app.set('trust proxy', process.env.TRUST_PROXY || 1);
const cookieSecure = process.env.COOKIE_SECURE !== 'false';
if (!cookieSecure) {
console.warn('COOKIE_SECURE=false — session cookie will be sent over plain HTTP.');
}
app.use(session({
name: 'pf.sid',
store: new PgSession({ pool, schemaName: 'pf', tableName: 'session', createTableIfMissing: false }),
secret: sessionSecret,
resave: false,
saveUninitialized: false,
rolling: true,
cookie: {
httpOnly: true,
sameSite: 'lax',
secure: cookieSecure,
maxAge: 1000 * 60 * 60 * 12,
},
}));
app.use('/api', require('./routes/auth')(pool));
// Everything below this line requires a session.
app.use('/api', requireAuth);
app.use('/api', require('./routes/tables')(pool));
app.use('/api', require('./routes/sources')(pool));
app.use('/api', require('./routes/versions')(pool));

26
setup_sql/02_auth.sql
View File

@ -1,26 +0,0 @@
-- Pivot Forecast — authentication
-- Run after 01_schema.sql: psql -d <db> -f setup_sql/02_auth.sql
-- Safe to re-run.
-- Application accounts. Passwords are scrypt hashes written by lib/auth.js;
-- the plaintext never reaches the database. Manage with ./pf.sh add-user.
CREATE TABLE IF NOT EXISTS pf.app_user (
id serial PRIMARY KEY,
username text NOT NULL UNIQUE,
pass_hash text NOT NULL,
display_name text,
is_active boolean NOT NULL DEFAULT true,
created_at timestamptz NOT NULL DEFAULT now(),
last_login_at timestamptz
);
-- Session store for express-session (connect-pg-simple layout). Sessions live
-- here rather than in memory so a restart doesn't sign everyone out, and so a
-- session can be revoked by deleting its row.
CREATE TABLE IF NOT EXISTS pf.session (
sid varchar PRIMARY KEY NOT NULL COLLATE "default",
sess json NOT NULL,
expire timestamp(6) NOT NULL
);
CREATE INDEX IF NOT EXISTS session_expire_idx ON pf.session (expire);

48
ui/package-lock.json generated
View File

@ -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": "5.2.0",
"@perspective-dev/server": "5.2.0",
"@perspective-dev/viewer": "5.2.0",
"@perspective-dev/viewer-datagrid": "5.2.0",
"react": "^19.2.5",
"react-dom": "^19.2.5"
},
@ -554,43 +554,43 @@
}
},
"node_modules/@perspective-dev/client": {
"version": "5.4.0",
"resolved": "file:vendor/perspective-dev-client-5.4.0.tgz",
"integrity": "sha512-l9xCJ0W42wm9fLlwhoU838KyTE131qQTS686uQb/VcsLs30kCVjkoermCCUs0YHCDwDTJhly1y9DfAZDbkg9Ng==",
"version": "5.2.0",
"resolved": "https://registry.npmjs.org/@perspective-dev/client/-/client-5.2.0.tgz",
"integrity": "sha512-zkJmJFwdw0wMREoJt8gUJyCsflzi7s7Yc8ZtUCOLE9XB6fdyxJmDB99cxO3Q+hno/57kLsz8VNoz8XSm6PZNrg==",
"license": "Apache-2.0",
"dependencies": {
"@perspective-dev/server": "^5.4.0",
"@perspective-dev/server": "",
"pro_self_extracting_wasm": "0.0.9",
"stoppable": "=1.1.0",
"ws": "^8.17.0"
}
},
"node_modules/@perspective-dev/server": {
"version": "5.4.0",
"resolved": "file:vendor/perspective-dev-server-5.4.0.tgz",
"integrity": "sha512-iTseRJB6TL6D9xjaMKMhh2NEKMIi9JR881J+GyQflHIQXK43fDlsIWtByUAoyZzZ7uA9KNZJZicirvONxIpLuw==",
"version": "5.2.0",
"resolved": "https://registry.npmjs.org/@perspective-dev/server/-/server-5.2.0.tgz",
"integrity": "sha512-WRBiokT2/BYM8Ipe7Dg1/2UYNb01Vsox79KBfFVI800VmwdId0GdqI95zufN5ZoFffeBfri1sr3S3AShBRFXKA==",
"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==",
"version": "5.2.0",
"resolved": "https://registry.npmjs.org/@perspective-dev/viewer/-/viewer-5.2.0.tgz",
"integrity": "sha512-IIyqdPduofzzZ/QWrteq29IadJQR8IapTRz7U6XbrtBBm7eyiFsIBKQV0wFfcqWg4TRnhuvCAUvBRieuE0R8Eg==",
"license": "Apache-2.0",
"dependencies": {
"@perspective-dev/client": "^5.4.0",
"@perspective-dev/client": "",
"pro_self_extracting_wasm": "0.0.9",
"regular-layout": "=0.6.1"
}
},
"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==",
"version": "5.2.0",
"resolved": "https://registry.npmjs.org/@perspective-dev/viewer-datagrid/-/viewer-datagrid-5.2.0.tgz",
"integrity": "sha512-v/SR/35YfyKfivO21nglJFiyG1iE5G8vMwIwWI6fQuwjW764pmNrm/zcrNWNY70x5XHkPRM94aY6LjkLsLkO2g==",
"license": "Apache-2.0",
"dependencies": {
"@perspective-dev/client": "^5.4.0",
"@perspective-dev/viewer": "^5.4.0",
"regular-table": "=0.9.1"
"@perspective-dev/client": "",
"@perspective-dev/viewer": "",
"regular-table": "=0.8.6"
}
},
"node_modules/@rolldown/binding-android-arm64": {
@ -2948,9 +2948,9 @@
"license": "Apache-2.0"
},
"node_modules/regular-table": {
"version": "0.9.1",
"resolved": "https://registry.npmjs.org/regular-table/-/regular-table-0.9.1.tgz",
"integrity": "sha512-3I/2I3NEhmyScCevW/r1AE9hyUkqrPRMDsgo/nDnGXhpXVZOxUddJQGBmEMLgyc7OSaQSzl9BtXllnMqw1MwCA==",
"version": "0.8.6",
"resolved": "https://registry.npmjs.org/regular-table/-/regular-table-0.8.6.tgz",
"integrity": "sha512-jzJzu9WLtqwMgbf5ak/VdNm/YLHUDnDSCHD5SOksnHrHLuDN6l5i2stYfe7BjsHbQV1Gx9369Ma40nTBCgOFvA==",
"license": "Apache-2.0",
"engines": {
"node": ">=16"

8
ui/package.json
View File

@ -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": "5.2.0",
"@perspective-dev/server": "5.2.0",
"@perspective-dev/viewer": "5.2.0",
"@perspective-dev/viewer-datagrid": "5.2.0",
"react": "^19.2.5",
"react-dom": "^19.2.5"
},

62
ui/src/auth.jsx
View File

@ -1,62 +0,0 @@
import { createContext, useContext, useState, useEffect, useCallback } from 'react'
const AuthContext = createContext()
// A session can expire while the app is open. Rather than teach every one of
// the app's fetch calls to check for it, wrap fetch once: any 401 from /api
// drops the whole UI back to the login screen. Cookies ride along on their own
// — fetch defaults to same-origin credentials, and the UI is served from the
// same origin as the API.
function installUnauthorizedHandler(onUnauthorized) {
const original = window.fetch
window.fetch = async (...args) => {
const res = await original(...args)
const url = typeof args[0] === 'string' ? args[0] : args[0]?.url || ''
if (res.status === 401 && url.includes('/api/') && !url.includes('/api/login')) {
onUnauthorized()
}
return res
}
return () => { window.fetch = original }
}
export function AuthProvider({ children }) {
const [user, setUser] = useState(null)
const [checking, setCheck] = useState(true)
useEffect(() => {
fetch('/api/me')
.then(r => r.ok ? r.json() : null)
.then(d => setUser(d?.user || null))
.catch(() => setUser(null))
.finally(() => setCheck(false))
}, [])
useEffect(() => installUnauthorizedHandler(() => setUser(null)), [])
const login = useCallback(async (username, password) => {
const r = await fetch('/api/login', {
method: 'POST',
headers: { 'Content-Type': 'application/json' },
body: JSON.stringify({ username, password }),
})
const data = await r.json().catch(() => ({}))
if (!r.ok) throw new Error(data.error || 'Login failed')
setUser(data.user)
return data.user
}, [])
const logout = useCallback(async () => {
try { await fetch('/api/logout', { method: 'POST' }) } catch {}
setUser(null)
}, [])
return (
<AuthContext.Provider value={{ user, checking, login, logout }}>
{children}
</AuthContext.Provider>
)
}
const useAuth = () => useContext(AuthContext)
export default useAuth

18
ui/src/components/StatusBar.jsx
View File

@ -1,10 +1,8 @@
import { useState, useEffect, useCallback } from 'react'
import useTheme from '../theme.jsx'
import useAuth from '../auth.jsx'
export default function StatusBar({ view, sources = [], sourceId, setSourceId, versions = [], versionId, setVersionId }) {
const { dark, setDark } = useTheme()
const { user, logout } = useAuth()
const showVersion = view === 'baseline' || view === 'forecast'
const selectedVersion = versions.find(v => String(v.id) === String(versionId))
@ -127,21 +125,7 @@ export default function StatusBar({ view, sources = [], sourceId, setSourceId, v
</>
)}
<div className="ml-auto flex items-center gap-2">
{user && (
<>
<span className="text-gray-500" title={`Signed in as ${user.username}`}>
{user.display_name || user.username}
</span>
<button
onClick={logout}
className="text-xs text-gray-500 hover:text-gray-700 border border-gray-200 px-2 py-0.5 rounded"
title="Sign out"
>
Sign out
</button>
</>
)}
<div className="ml-auto">
<button
onClick={() => setDark(d => !d)}
className="w-6 h-6 flex items-center justify-center rounded hover:bg-gray-100"

16
ui/src/main.jsx
View File

@ -1,27 +1,13 @@
import { StrictMode } from 'react'
import { createRoot } from 'react-dom/client'
import { ThemeProvider } from './theme.jsx'
import { AuthProvider } from './auth.jsx'
import useAuth from './auth.jsx'
import Login from './views/Login.jsx'
import './index.css'
import App from './App.jsx'
// App is mounted only once there is a session — its load effects call /api
// straight away, and mounting it logged-out would just fire a burst of 401s.
function Gate() {
const { user, checking } = useAuth()
if (checking) return <div className="flex items-center justify-center h-screen text-sm text-gray-400">Loading…</div>
if (!user) return <Login />
return <App />
}
createRoot(document.getElementById('root')).render(
<StrictMode>
<ThemeProvider>
<AuthProvider>
<Gate />
</AuthProvider>
<App />
</ThemeProvider>
</StrictMode>,
)

3
ui/src/views/Baseline.jsx
View File

@ -148,6 +148,7 @@ export default function Baseline({ sources = [], sourceId, versions = [], versio
const endpoint = isRef ? 'reference' : 'baseline'
const body = {
where_clause: clause,
pf_user: 'admin',
note: description || segNote,
date_offset: offsetStr,
...(useRaw ? { raw_where: clause } : { filters }),
@ -241,7 +242,7 @@ export default function Baseline({ sources = [], sourceId, versions = [], versio
async function closeVersion() {
const res = await fetch(`/api/versions/${versionId}/close`, {
method: 'POST', headers: { 'Content-Type': 'application/json' },
body: JSON.stringify({})
body: JSON.stringify({ pf_user: 'admin' })
})
const data = await res.json()
if (!res.ok) { flash(data.error, 'error'); return }

101
ui/src/views/Forecast.jsx
View File

@ -23,8 +23,6 @@ function cleanLayout(cfg, validCols) {
if (c.columns) c.columns = c.columns.filter(col => col == null || ok(col))
if (c.group_by) c.group_by = c.group_by.filter(ok)
if (c.split_by) c.split_by = c.split_by.filter(ok)
// the uncollapsed column hierarchy travels with the layout (see applySplitDepth)
if (c.split_full) c.split_full = c.split_full.filter(ok)
if (c.sort) c.sort = c.sort.filter(([col]) => ok(col))
if (c.filter) c.filter = c.filter.filter(([col]) => ok(col))
return c
@ -44,12 +42,6 @@ export default function Forecast({ sources = [], sourceId, versions = [], versio
const [saveAsName, setSaveAsName] = useState('')
// operation panel — a selection is a LIST of slices; one entry is the common case
// The column hierarchy and how many of its levels are showing. Mirrored into
// splitFullRef for handlers registered once; held as state so the toolbar
// re-renders when either changes.
const [splitFull, setSplitFull] = useState([])
const [splitDepth, setSplitDepth] = useState(null)
const [slices, setSlices] = useState([])
const [applyMode, setApplyMode] = useState('prorate') // 'prorate' | 'each'
const [activeOp, setActiveOp] = useState('scale')
@ -175,12 +167,6 @@ export default function Forecast({ sources = [], sourceId, versions = [], versio
const tableRef = useRef(null)
const colMetaRef = useRef([])
const expandDepthRef = useRef(null)
// The column axis has no set_depth() — collapsing it means restoring a shorter
// split_by, so the full hierarchy has to be remembered separately to expand again.
const splitFullRef = useRef([])
// set while our own restore is in flight, so the config-update listener can tell
// a collapse from the user rearranging split_by themselves
const collapsingRef = useRef(false)
const initIdRef = useRef(0)
const modifierRef = useRef(false)
// the datagrid plugin element, for reading cell coordinates and driving its
@ -550,7 +536,6 @@ export default function Forecast({ sources = [], sourceId, versions = [], versio
setLoadProgress(null)
setSlices([])
expandDepthRef.current = null
adoptSplit([], 0)
try {
const [dataResult, meta] = await Promise.all([
fetch(`/api/versions/${vid}/data`).then(async r => {
@ -631,9 +616,6 @@ export default function Forecast({ sources = [], sourceId, versions = [], versio
const { table: _t, ...rest } = cleanLayout(JSON.parse(saved), validCols)
const cfg = { ...rest, plugin_config: { ...(rest.plugin_config || {}), edit_mode: 'SELECT_REGION' } }
await viewer.restore(cfg)
// split_full outlives split_by: a layout saved while collapsed still knows
// the levels it was collapsed from
adoptSplit(cfg.split_full?.length ? cfg.split_full : cfg.split_by, (cfg.split_by || []).length)
if (cfg.expand_depth != null) await applyDepth(cfg.expand_depth)
} else {
const sourceDefault = sources.find(s => String(s.id) === String(sid))?.default_layout
@ -651,19 +633,12 @@ export default function Forecast({ sources = [], sourceId, versions = [], versio
}
}
await viewer.restore(cfg)
adoptSplit(cfg.split_full?.length ? cfg.split_full : cfg.split_by, (cfg.split_by || []).length)
}
// auto-persist viewer state (formatting, columns, etc.) to the last-used cache
if (viewer._pspUpdate) viewer.removeEventListener('perspective-config-update', viewer._pspUpdate)
viewer._pspUpdate = async () => {
try {
// A split_by change that is not ours is the user rearranging the pivot, and
// it redefines the hierarchy. Ours is a collapse, and must not overwrite it.
if (!collapsingRef.current) {
const live = await viewer.save()
adoptSplit(live.split_by || [], (live.split_by || []).length)
}
const cfg = await captureConfig()
if (cfg) await persistLayout(vid, cfg)
} catch {}
@ -715,54 +690,6 @@ export default function Forecast({ sources = [], sourceId, versions = [], versio
}
}
// Record the column hierarchy as the uncollapsed truth. Called whenever a layout
// arrives or the user rearranges split_by themselves — but never for our own
// collapse, which would otherwise overwrite the full list with the short one.
function adoptSplit(full, depth) {
const list = Array.isArray(full) ? full : []
splitFullRef.current = list
setSplitFull(list)
setSplitDepth(depth == null ? list.length : Math.min(depth, list.length))
}
// Collapse or expand the column axis to `n` split_by levels.
//
// The row axis gets this for free: its GROUP BY ROLLUP view holds every level at
// once and view.set_depth() hides the deeper ones. The column axis has no
// equivalent — there is no split_by_depth in ViewConfig and expand()/collapse()
// take a row index — so collapsing means restoring a truncated split_by, which
// rebuilds the view. Two consequences fall out of that: the row depth has to be
// re-applied afterwards (it lives on the discarded view), and it is whole-axis,
// not per-branch — every column group collapses to the same level together.
async function applySplitDepth(n) {
const viewer = viewerRef.current
const full = splitFullRef.current
if (!viewer || !full.length) return
const depth = Math.max(0, Math.min(n, full.length))
collapsingRef.current = true
try {
await viewer.restore({ split_by: full.slice(0, depth) })
setSplitDepth(depth)
// restore() rebuilt the view, so the row depth that lived on the old one is gone
if (expandDepthRef.current != null) await applyDepth(expandDepthRef.current)
} catch (err) {
console.error('[applySplitDepth]', err)
flash(err.message || String(err), 'error')
return
} finally {
collapsingRef.current = false
}
// a slice names the split_by dimensions it was cut from, and the highlight is
// keyed on grid coordinates — neither survives a column axis that just changed
setSlices([])
try {
const cfg = await captureConfig()
if (cfg) await persistLayout(versionId, cfg)
} catch (err) {
console.error('[applySplitDepth persist]', err)
}
}
async function applyDepth(d) {
const viewer = viewerRef.current
if (!viewer) return
@ -777,7 +704,7 @@ export default function Forecast({ sources = [], sourceId, versions = [], versio
const viewer = viewerRef.current
if (!viewer) return null
const cfg = await viewer.save()
return { ...cfg, expand_depth: expandDepthRef.current, split_full: splitFullRef.current }
return { ...cfg, expand_depth: expandDepthRef.current }
}
async function persistLayout(vid, cfg) {
@ -835,7 +762,6 @@ export default function Forecast({ sources = [], sourceId, versions = [], versio
const cfg = cleanLayout(layout.config, validCols)
cfg.plugin_config = { ...(cfg.plugin_config || {}), edit_mode: 'SELECT_REGION' }
await viewer.restore(cfg)
adoptSplit(cfg.split_full?.length ? cfg.split_full : cfg.split_by, (cfg.split_by || []).length)
if (cfg.expand_depth != null) await applyDepth(cfg.expand_depth)
setActiveLayoutId(layout.id)
await persistLayout(versionId, cfg)
@ -945,6 +871,7 @@ export default function Forecast({ sources = [], sourceId, versions = [], versio
}
// apply_mode only changes the maths when more than one slice is selected
let body = {
pf_user: 'admin',
tag: opTag.trim() || undefined,
slices: effectiveSlices,
...(effectiveSlices.length > 1 ? { apply_mode: applyMode } : {}),
@ -1160,30 +1087,6 @@ export default function Forecast({ sources = [], sourceId, versions = [], versio
))}
</div>
{splitFull.length > 0 && (
<>
<div className="w-px h-4 bg-gray-200 shrink-0" />
{/* Column hierarchy group — the split_by equivalent of Expand */}
<div className="flex items-center gap-1.5">
<span className="text-gray-400 uppercase tracking-wide" style={{fontSize:'10px'}}>Columns</span>
{Array.from({ length: splitFull.length + 1 }, (_, n) => {
const label = n === 0 ? 'Total' : splitFull[n - 1]
return (
<button key={n} onClick={() => applySplitDepth(n)}
title={n === 0
? 'Collapse the columns to a single total'
: `Show columns down to ${splitFull.slice(0, n).join(' › ')}`}
className={`border rounded px-1.5 py-0.5 transition-colors max-w-[9rem] truncate
${splitDepth === n ? 'border-blue-300 text-blue-600 bg-blue-50' : 'border-gray-200 text-gray-500 hover:border-gray-400'}`}>
{label}
</button>
)
})}
</div>
</>
)}
<div className="w-px h-4 bg-gray-200 shrink-0" />
{/* Data group */}

68
ui/src/views/Login.jsx
View File

@ -1,68 +0,0 @@
import { useState } from 'react'
import useAuth from '../auth.jsx'
export default function Login() {
const { login } = useAuth()
const [username, setUsername] = useState('')
const [password, setPassword] = useState('')
const [error, setError] = useState('')
const [busy, setBusy] = useState(false)
async function submit(e) {
e.preventDefault()
setError(''); setBusy(true)
try {
await login(username.trim(), password)
} catch (err) {
setError(err.message)
setPassword('')
} finally {
setBusy(false)
}
}
return (
<div className="flex items-center justify-center h-screen w-full text-sm">
<form onSubmit={submit} className="bg-white border border-gray-200 rounded p-6 w-80 flex flex-col gap-4">
<div>
<div className="text-base font-medium">Pivot Forecast</div>
<div className="text-xs text-gray-500 mt-0.5">Sign in to continue</div>
</div>
<label className="flex flex-col gap-1">
<span className="text-xs text-gray-500">Username</span>
<input
value={username}
onChange={e => setUsername(e.target.value)}
autoFocus
autoComplete="username"
className="border border-gray-200 rounded px-2 py-1.5 text-sm"
/>
</label>
<label className="flex flex-col gap-1">
<span className="text-xs text-gray-500">Password</span>
<input
type="password"
value={password}
onChange={e => setPassword(e.target.value)}
autoComplete="current-password"
className="border border-gray-200 rounded px-2 py-1.5 text-sm"
/>
</label>
{error && (
<div className="px-3 py-2 text-xs rounded font-medium bg-red-50 text-red-700">{error}</div>
)}
<button
type="submit"
disabled={busy || !username.trim() || !password}
className="bg-blue-600 text-white text-xs px-3 py-2 rounded hover:bg-blue-700 disabled:opacity-50"
>
{busy ? 'Signing in…' : 'Sign in'}
</button>
</form>
</div>
)
}

7
ui/vendor/PROVENANCE.txt vendored
View File

@ -1,7 +0,0 @@
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
Regenerate with ui/vendor/rebuild-perspective.sh

50
ui/vendor/README.md vendored
View File

@ -1,50 +0,0 @@
# Vendored Perspective
pf_app runs a **patched build of Perspective**. Upstream's C++ engine has always
implemented column-axis expand/collapse — `t_ctx2::set_depth(HEADER_COLUMN, …)`
and `open`/`close(HEADER_COLUMN, idx)` are fully written — but nothing above C++
could reach it: `set_column_pivot_depth()` was never called, and
`View<t_ctx2>::expand/collapse` hardcoded `HEADER_ROW`. The patch is wiring, not
new engine logic.
It buys two things the released packages cannot do at all:
- `split_by_depth` in `ViewConfig`, the `split_by` counterpart to `group_by_depth`
- `expand_column()` / `collapse_column()`, so one column branch can fold to its
subtotal while its siblings stay expanded — the Excel behaviour
**Source:** https://github.com/fleetside72/perspective, branch
`column-axis-expand-collapse`. See `PROVENANCE.txt` for the exact commit these
tarballs were built from.
## Why tarballs and not npm
The feature is not released upstream. Until it is, the four packages are built
from the fork and committed here as npm tarballs. `npm install` expands them
exactly as it expands anything from the registry — no special tooling, and
`pf.sh deploy` works unchanged. A deploy machine needs node and nothing else:
no emscripten, no cmake, no protoc, no Rust.
All four move together, never a subset. Perspective couples loader, package
versions, data format and `apache-arrow`; vendoring a partial set reintroduces
exactly the drift that causes trouble.
## Changing the engine
./rebuild-perspective.sh # builds the fork, repacks, rewrites PROVENANCE.txt
cd .. && npm install
git add vendor && git commit
Push the fork first — the script warns if the source tree is dirty, because a
tarball built from uncommitted code has no recoverable source.
The build itself needs cmake >= 3.29.5, protoc >= 22 (its version silently
selects which protobuf source tree gets cloned), pnpm, and the Rust nightly the
repo pins. Roughly 40 minutes cold. Only ever on a machine changing the engine.
## Getting rid of this
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`.

BIN
ui/vendor/perspective-dev-client-5.4.0.tgz vendored
View File

Binary file not shown.

BIN
ui/vendor/perspective-dev-server-5.4.0.tgz vendored
View File

Binary file not shown.

BIN
ui/vendor/perspective-dev-viewer-5.4.0.tgz vendored
View File

Binary file not shown.

BIN
ui/vendor/perspective-dev-viewer-datagrid-5.4.0.tgz vendored
View File

Binary file not shown.

102
ui/vendor/rebuild-perspective.sh vendored
View File

@ -1,102 +0,0 @@
#!/usr/bin/env bash
set -euo pipefail
# ---------------------------------------------------------------------------
# rebuild-perspective.sh — rebuild the patched Perspective and re-vendor it
#
# pf_app runs a patched build of Perspective that exposes the column axis
# expand/collapse the engine already implements (split_by_depth, and
# expand_column/collapse_column). Upstream does not ship this yet, so the
# built packages are vendored into this directory as npm tarballs.
#
# Source of truth: https://github.com/fleetside72/perspective
# branch column-axis-expand-collapse
#
# This script exists because vendored binaries are opaque: once the .tgz files
# are committed, nothing in the repo records how to regenerate them. Run this
# after changing the fork, then commit the resulting tarballs.
#
# Only needed on a machine that is changing the engine. Deploys just run
# `npm install`, which expands the committed tarballs - see ../README in this
# directory.
# ---------------------------------------------------------------------------
PSP="${PSP_DIR:-$HOME/perspective}"
BRANCH="${PSP_BRANCH:-column-axis-expand-collapse}"
VENDOR="$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)"
# The four packages that must move together. Perspective's own docs are
# emphatic that loader, packages, data format and apache-arrow are one unit;
# vendoring a subset would reintroduce exactly the drift that causes trouble.
PACKAGES=(
"rust/perspective-js"
"rust/perspective-server"
"rust/perspective-viewer"
"packages/viewer-datagrid"
)
info() { echo -e "\033[0;34m==>\033[0m $*"; }
ok() { echo -e "\033[0;32m ✓\033[0m $*"; }
die() { echo -e "\033[0;31m ✗\033[0m $*" >&2; exit 1; }
# -- preflight --------------------------------------------------------------
[[ -d "$PSP" ]] || die "No Perspective checkout at $PSP.
git clone https://github.com/fleetside72/perspective.git $PSP
cd $PSP && git checkout $BRANCH
Set PSP_DIR to use a different path."
command -v pnpm >/dev/null || die "pnpm not found. Perspective builds with pnpm, not npm."
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
(33.2 known good). Distro packages are usually far too old."
cmake_ver=$(cmake --version 2>/dev/null | head -1 | grep -oE '[0-9]+\.[0-9]+(\.[0-9]+)?') || die "cmake not found"
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'"
fi
info "Perspective checkout: $PSP"
git -C "$PSP" rev-parse --abbrev-ref HEAD | grep -qx "$BRANCH" \
|| echo " ! on branch $(git -C "$PSP" rev-parse --abbrev-ref HEAD), expected $BRANCH"
commit=$(git -C "$PSP" rev-parse --short HEAD)
dirty=$(git -C "$PSP" status --porcelain | wc -l)
echo " commit $commit$([[ $dirty -gt 0 ]] && echo " (+$dirty uncommitted files)")"
# -- build ------------------------------------------------------------------
# `metadata` first: it generates docs/expression_gen.md, which perspective-client
# includes at compile time. Building a scope without it fails on the missing file.
info "Building (this takes ~40 minutes cold, a few minutes warm)…"
( cd "$PSP" && PSP_ONCE=1 PACKAGE="metadata,server,client,viewer,viewer-datagrid" pnpm run build )
ok "build complete"
# -- pack -------------------------------------------------------------------
info "Packing tarballs into $VENDOR"
rm -f "$VENDOR"/*.tgz
for p in "${PACKAGES[@]}"; do
( cd "$PSP/$p" && npm pack --pack-destination "$VENDOR" >/dev/null )
ok "$(basename "$p")"
done
# -- record provenance ------------------------------------------------------
# A committed .tgz is an opaque binary; without this the tie back to source is
# only in someone's memory.
cat > "$VENDOR/PROVENANCE.txt" <<EOF
Built from https://github.com/fleetside72/perspective
branch $BRANCH
commit $(git -C "$PSP" rev-parse HEAD)
based on $(git -C "$PSP" describe --tags --abbrev=0 2>/dev/null || echo 'unknown')
built $(date -u +%Y-%m-%dT%H:%M:%SZ) on $(hostname)
dirty $dirty uncommitted file(s) in the source tree at build time
Regenerate with ui/vendor/rebuild-perspective.sh
EOF
echo
ls -la "$VENDOR"/*.tgz | awk '{printf " %-52s %5.1f MB\n", $NF, $5/1048576}'
echo
ok "Done. Now: cd ui && npm install && git add vendor && git commit"
[[ $dirty -gt 0 ]] && echo -e "\033[1;33m !\033[0m source tree had uncommitted changes — push them to the fork first"
exit 0