Paul Trowbridge 7dcd8c4b61 Consolidate documentation into docs/ and cut the duplication

Architecture, file structure, the manage.py menu, and the API reference were
each documented in two or three of README.md, SPEC.md, and CLAUDE.md — the same
drift trap the SQL just had.

SPEC.md, examples/GETTING_STARTED.md, and ui/README.md move into docs/.
PERSPECTIVE.md and docs/perspective-pivot.md merge into docs/perspective.md,
version rationale first, then the API reference. README.md becomes an entry
point that links out, and CLAUDE.md keeps only working rules and non-obvious
behaviour, pointing at docs/spec.md for the rest. examples/ keeps just the
sample CSV the tutorial loads.

Corrections found while consolidating:

- the spec's API table was missing 20 routes — every override endpoint, most of
  /api/stacks, the mapping remap routes, /health. Rebuilt from the route files
- the tutorial used port 3000 (default is 3020) and never mentioned Basic auth,
  so every curl in it would have 401'd
- the tutorial and the spec each hand-listed the SQL deploy order; both now
  point at manage.py, which is where the order actually lives
- CLAUDE.md described deduplication as an MD5 hash (it is a plain JSONB object),
  claimed 5 tables and 4 functions, and told you to run a setup.sh that has not
  existed for some time

Co-Authored-By: Claude Opus 5 <noreply@anthropic.com>

2026-07-26 21:55:58 -04:00

7.2 KiB

Raw Permalink Blame History

CLAUDE.md

This file provides guidance to Claude Code (claude.ai/code) when working with code in this repository.

Dataflow imports CSV data, extracts structure from it with regex rules, maps the extracted values to standardized output, and serves the result over a REST API and React UI. It is a simple system by design — don't over-engineer it.

Read docs/spec.md for architecture, schema, data flow, the full API, and manage.py. This file covers only what you need to work in the repo without breaking something — the rules and non-obvious behaviours that aren't visible from the code.

Where things live

Both the API routes and the SQL are one file per resource: api/routes/rules.js and database/rules.sql are two halves of the same feature. Two SQL files are shared engines rather than per-route — import.sql (CSV import, audit trail) and transform.sql (the rule/mapping engine, including the jsonb_concat_obj aggregate).

manage.py's QUERY_FILES list is the deploy order and the authoritative file list.

Rules that matter

database/*.sql is the source of truth for every database function. Never edit a function directly in the database. A live edit that isn't written back to the file is silently reverted the next time anyone runs "Redeploy SQL functions". This has already happened once: five functions drifted and sat wrong in the repo for months — see the git history of the deleted database/functions.sql.

Always run npm run build from ui/ after any change to ui/src/. The Express server serves the built output in public/; source changes are invisible until you rebuild.

Never use ON CONFLICT (constraint_key). See deduplication below — there is no unique constraint, and adding one would drop legitimate transactions.

The three data layers

Each row in records keeps its data in three JSONB columns:

data — raw imported values, never modified
transformed — rule and mapping output only (the delta)
overrides — manual edits, highest precedence

Readers merge them as data || transformed || overrides. Keeping them separate is what lets reprocess_records re-run the rules without clobbering a manual edit. Anything that writes overrides into transformed is a bug — that was the pre-May-2026 behaviour.

Deduplication

constraint_key is a JSONB object of the constraint field values — readable, no hashing
Dedup is enforced at import time in a CTE. There is no unique DB constraint on it
The constraint key is cross-batch re-import protection, not record uniqueness
Within one import batch, all rows insert even when constraint keys collide. Banks legitimately send identical-looking transactions — 11 separate Cedar Point charges on the same day are 11 real rows
On re-import of an overlapping date range, rows whose key already exists are skipped, so re-running a month-to-date export the next day doesn't double-count
Deleting an import log entry cascades to every record in that batch (import_id FK)

Error handling

API routes use try/catch and pass errors to next(err); server.js has a global handler. Database functions return JSON with a success boolean.

Light / dark mode

Theme state lives in ui/src/theme.jsx — a React context (ThemeContext) with a ThemeProvider that wraps the app in main.jsx.

Storage key: df_dark in localStorage; falls back to window.matchMedia('(prefers-color-scheme: dark)') on first visit
Toggle: button in the sidebar header in App.jsx; effect writes localStorage and toggles the .dark class on <html>
CSS: ui/src/index.css defines CSS custom properties under :root (light) and .dark. All Tailwind color overrides are written as .dark .bg-white { ... } etc.
Palette: dark mode uses Perspective's "Pro Dark" colours (--bg-primary: #242526, panels #2a2c2f, gridlines #3b3f46, text #c5c9d0)
Perspective viewer: Pivot.jsx calls viewer.setAttribute('theme', dark ? 'Pro Dark' : 'Pro Light') on initial load and in a useEffect([dark]) so the viewer stays in sync when the toggle fires
Consuming the theme: import useTheme from '../theme.jsx' then const { dark, setDark } = useTheme()

Pivot inspector panel

Clicking a data cell opens a right-hand inspector panel showing the underlying transactions for that cell. See docs/perspective.md for the Perspective API itself.

Toggle: clicking the same cell again closes the panel. The toggle key is JSON.stringify({ p: row.__ROW_PATH__, c: column_names }) — stable across source and stack views.
Listener cleanup: the perspective-click handler is stored in perspClickHandlerRef and removed via removeEventListener on effect cleanup. Without this, switching views accumulates duplicate listeners that fire multiple times per click.
split_by filter derivation: detail.config.filter from the click event may omit split_by column constraints. They are derived from column_names positionally (column_names[i] matches config.split_by[i]) and appended to the filter before querying.
Row filtering: a temporary table.view({ filter, expressions }) is used so Perspective evaluates expression/computed columns correctly. Falls back to JS-side filterRowsByConfig on error (which skips filters for fields not in raw data).
The panel is resizable via a drag handle on its left edge (paneWidth state, min 240px).
The transaction table is sortable (click header) and shows column totals for all-numeric columns.

Pivot layout persistence

Named layouts are stored in dataflow.pivot_layouts for both sources and stacks. The source_name column holds either a source name or a stack name — the FK to sources(name) was dropped to allow this. Source layouts use /api/sources/:name/layouts; stack layouts use /api/stacks/:name/layouts. Both call the same DB functions (list_pivot_layouts, save_pivot_layout, delete_pivot_layout). localStorage still remembers the last active layout for a view (the psp_layout_<name> key), but the definitions live in the DB so they persist across machines.

Adding features

One function, one job; keep functions under 100 lines
Write clear SQL, not clever SQL
Add the SQL function to the matching database/*.sql file, then the route that calls it
Update docs/spec.md when you add or change an endpoint

Troubleshooting

Database connection fails — check .env credentials, that PostgreSQL is running, and that the search path resolves to the dataflow schema.

Import succeeds but transformation does nothing — check rules exist for that source (SELECT * FROM dataflow.rules WHERE source_name = '…'), that field matches an actual key in data, and test the pattern with GET /api/rules/preview.

Everything is marked duplicate — constraint_fields probably don't match the real field names, or the batch was already imported.

History

This replaces an older system still in /opt/tps — 2,150 lines of SQL with five nearly-identical 200-line functions and trigger-based processing. Dataflow is a clean rewrite, not a refactor. Some function bodies still carry mirrors TPS … comments pointing at their counterpart there.

7.2 KiB Raw Permalink Blame History