API

The Node backend (Express 5, plain SQL with pg, no ORM). The web app talks only to this. Every access rule lives here.

What it does

• Vendors, projects, folders. Admins create vendors and their logins in Keycloak; vendors create projects and folders.
• Files. Streams uploads to the raw bucket, hashes them, writes the registry row and audit row in one transaction. Archive, restore, reprocess, delete.
• Push API. Machine uploads with push keys.
• Analysts and SQL. Runs checked queries on Trino, exports CSV, saves views.
• Views as live APIs. A built-in refresher builds views; a gateway serves published data with API keys.
• Audit and Activity. Thin, keyset-paged logs with hard server caps.
• Search and system health.

Login model

Every request carries Authorization: Bearer <Keycloak access token>. The API verifies it with jose against the realm’s public keys, checks the issuer (<keycloak>/realms/chdc) and that the token was issued to the chdc-web client, then reads the realm role (admin, vendor or analyst). A vendor token is also tied to one vendor through the registry. Another vendor’s id answers 404, the same as a missing one; a wrong role answers 403.

Run it locally

Needs Node 20+ (24 recommended) and the SSH tunnel to the server (Local development), because PostgreSQL, S3, Trino and Keycloak live there.

cd apps/api
npm install
npm run dev          # http://127.0.0.1:4000

Settings are read from the repo-root cluster.env, then local.env on top (the tunnel ports), then real environment variables. Nothing is configured in the apps/api folder.

Test it

cd apps/api
npm test

Uses Node’s built-in test runner with fakes for PostgreSQL, Keycloak, Trino and storage, so no tunnel is needed. The suite covers upload rules, SQL access checks, the view pipeline, the gateway (including injection and limits), audit paging and the push API, and checks that every documented push call exists in the API.

Route groups

Prefix	Who	What
GET /api/public-config	anyone	Keycloak address and client for the web app to log in
GET /api/me	any login	Who you are, your role, your vendor or analyst record
GET /api/health	admin	Service checks and links for the status page
/api/vendors/...	admin; vendors for their own	Vendors, logins, projects, folders, files and file actions, push keys
/api/analysts/..., /api/projects	admin	Analysts and what they may read
/api/my/vendors	analyst	Assigned vendors and projects
/api/sql/...	analyst, admin	tables, schema, validate, run, exports
/api/views/..., /api/keys	analyst, admin	Views, builds, publishing, keys
/api/activity/...	admin, vendor	Summary, feed, one file’s journey
/api/audit/...	admin	Four audit sources, search and detail
/api/search	any login	Global search, scoped per role
/api/v1/push/...	push key	The Push API
/api/v1/data/...	API key	Published APIs

Safety rules built into the code

• SQL. Only SELECT, WITH, VALUES, TABLE. Dataset names in the text must be readable by the login; the query plan is checked again with EXPLAIN (TYPE IO). Row, size, time and concurrency caps. Every attempt is audited.
• Uploads. Streamed, never held in memory; size checked from Content-Length and again while streaming; an object is deleted again if the database insert fails.
• Published APIs. Callers cannot send SQL. Column names come from the registry; values are escaped; rate and concurrency limits per key.
• History lists. Keyset paging only (no OFFSET), a required time range, filters on indexed columns, per-query timeouts, a concurrency cap, and batch retention.
• Secrets. Keys and passwords are shown once; only hashes are stored; no log holds them.

Settings (all optional unless noted)

See Settings for the full table. The API-specific ones: WEB_API_PORT, UPLOAD_MAX_MB, the SQL_* and SQL_EXPORT_* limits, AUDIT_*, API_LOG_RETENTION_DAYS, VIEW_*, API_*, PUSH_RATE_PER_MIN, NOTEBOOK_URL.

Helper scripts (development only)

Script	What it does
node scripts/seed-dev-users.js	Creates test logins dev-admin, dev-analyst, dev-vendor in Keycloak
node scripts/smoke-login.js	Logs in as each test user through the real code + PKCE flow and calls the API
node scripts/apply-sql.js <file.sql>	Applies one migration (servers use install step 14 instead)
node scripts/seed-stress-data.js [--vendors 150] [--clean]	Creates many fake vendors, projects and folders to check pages at volume, and removes them again

Code layout

Path	What
src/server.js	Start-up: connects everything and listens
src/app.js	Express routes
src/config.js	Reads the settings files
src/<thing>.js	The rules of one area (vendors, projects, files, sql, views, audit, …)
src/<thing>-store.js	The SQL for that area
test/	One test file per area

The tables are described in SQL migrations.