Paul Trowbridge
7dcd8c4b61
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>
124 lines
7.2 KiB
Markdown
124 lines
7.2 KiB
Markdown
# 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](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](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.
|