← back to Lifestyle Asset Intel
yolo tick #7: immutable valuation_calls audit log + middleware
44cae3a85e4ea66ac77b2aed25c755df007bbbdd · 2026-05-10 00:08:11 -0700 · Steve Abrams
Fulfill the BLUEPRINT and METHODOLOGY commitment to immutable audit
logging. Every paying customer (lender / insurer / marketplace) needs
to point at a specific call, see what we returned, and reproduce it.
Without this, no enterprise contract closes.
Migration 0004 lays in valuation_calls — INSERT-only by app convention.
Indexes on (called_at DESC), (asset_slug, called_at DESC), and
(caller_ip, called_at DESC) cover the standard "last 100 calls",
"calls for this asset", and "what's this IP doing" queries. There is
deliberately no updated_at / deleted_at column; the v1.0 role split
will withhold UPDATE/DELETE grants from the app role.
lib/audit.js wires three pieces:
hashToken — sha256 redaction of any Bearer token before storage. Raw
tokens never touch the DB.
auditMiddleware — Express middleware that hooks res.finish, computes
latency via process.hrtime.bigint, sets x-request-id
response header, and writes via setImmediate so the user
never waits on the audit insert. Errors swallowed so
logging can't break a request. /api/health excluded so
monitoring traffic doesn't pollute the table.
recentCalls — paged SELECT with optional asset_slug filter.
Endpoint /api/audit/recent serves the last N calls for ops dashboards
and customer-side reconciliation. Self-recording (asks itself appear
in subsequent calls) — acceptable v0 behaviour.
7 new audit tests covering: hashToken edge cases, end-to-end audit-row
insertion via the middleware (using a per-test unique user-agent so
files running in parallel don't race), /api/health skip, /api/audit/recent
ordering + filtering, and a schema sanity check that the table stays
INSERT-only (no updated_at/deleted_at columns). 37/37 green.
Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>
Files touched
A db/migrations/0004_valuation_calls.sqlM db/schema.sqlA lib/audit.jsM routes/api.jsA tests/audit.test.js
Diff
commit 44cae3a85e4ea66ac77b2aed25c755df007bbbdd
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Sun May 10 00:08:11 2026 -0700
yolo tick #7: immutable valuation_calls audit log + middleware
Fulfill the BLUEPRINT and METHODOLOGY commitment to immutable audit
logging. Every paying customer (lender / insurer / marketplace) needs
to point at a specific call, see what we returned, and reproduce it.
Without this, no enterprise contract closes.
Migration 0004 lays in valuation_calls — INSERT-only by app convention.
Indexes on (called_at DESC), (asset_slug, called_at DESC), and
(caller_ip, called_at DESC) cover the standard "last 100 calls",
"calls for this asset", and "what's this IP doing" queries. There is
deliberately no updated_at / deleted_at column; the v1.0 role split
will withhold UPDATE/DELETE grants from the app role.
lib/audit.js wires three pieces:
hashToken — sha256 redaction of any Bearer token before storage. Raw
tokens never touch the DB.
auditMiddleware — Express middleware that hooks res.finish, computes
latency via process.hrtime.bigint, sets x-request-id
response header, and writes via setImmediate so the user
never waits on the audit insert. Errors swallowed so
logging can't break a request. /api/health excluded so
monitoring traffic doesn't pollute the table.
recentCalls — paged SELECT with optional asset_slug filter.
Endpoint /api/audit/recent serves the last N calls for ops dashboards
and customer-side reconciliation. Self-recording (asks itself appear
in subsequent calls) — acceptable v0 behaviour.
7 new audit tests covering: hashToken edge cases, end-to-end audit-row
insertion via the middleware (using a per-test unique user-agent so
files running in parallel don't race), /api/health skip, /api/audit/recent
ordering + filtering, and a schema sanity check that the table stays
INSERT-only (no updated_at/deleted_at columns). 37/37 green.
Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>
---
db/migrations/0004_valuation_calls.sql | 41 ++++++++++
db/schema.sql | 1 +
lib/audit.js | 123 ++++++++++++++++++++++++++++
routes/api.js | 15 ++++
tests/audit.test.js | 142 +++++++++++++++++++++++++++++++++
5 files changed, 322 insertions(+)
diff --git a/db/migrations/0004_valuation_calls.sql b/db/migrations/0004_valuation_calls.sql
new file mode 100644
index 0000000..d102c01
--- /dev/null
+++ b/db/migrations/0004_valuation_calls.sql
@@ -0,0 +1,41 @@
+-- 0004_valuation_calls.sql — immutable audit log for the valuation API.
+--
+-- BLUEPRINT.md and METHODOLOGY.md both require an immutable audit log
+-- of every published valuation. Lenders and insurers will not sign a
+-- contract without it: they need to be able to point at a specific
+-- call, see what we returned, and reproduce the answer. This table is
+-- the canonical record; the v0 middleware writes to it fire-and-forget
+-- after the response, so it doesn't add user-visible latency.
+--
+-- The table is INSERT-only by app convention. There's no UPDATE or
+-- DELETE permission grant — when we lock down DB roles in v1.0, the
+-- app role gets only INSERT + SELECT here.
+
+CREATE TABLE IF NOT EXISTS valuation_calls (
+ id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
+ called_at timestamptz NOT NULL DEFAULT now(),
+ caller_ip inet,
+ -- Hashed Bearer token (sha256) — never store the raw token.
+ caller_token_hash text,
+ route text NOT NULL, -- '/api/valuation/:slug'
+ method text NOT NULL, -- 'GET'
+ asset_slug text, -- when applicable
+ response_status int NOT NULL,
+ methodology_version text,
+ latency_ms int,
+ user_agent text,
+ request_id uuid -- for trace correlation across services
+);
+
+CREATE INDEX IF NOT EXISTS valuation_calls_called_idx
+ ON valuation_calls(called_at DESC);
+
+CREATE INDEX IF NOT EXISTS valuation_calls_slug_idx
+ ON valuation_calls(asset_slug, called_at DESC) WHERE asset_slug IS NOT NULL;
+
+CREATE INDEX IF NOT EXISTS valuation_calls_ip_idx
+ ON valuation_calls(caller_ip, called_at DESC) WHERE caller_ip IS NOT NULL;
+
+-- bookkeeping
+INSERT INTO schema_migrations(filename) VALUES ('0004_valuation_calls.sql')
+ ON CONFLICT (filename) DO NOTHING;
diff --git a/db/schema.sql b/db/schema.sql
index e51fa0b..94af7ca 100644
--- a/db/schema.sql
+++ b/db/schema.sql
@@ -2,3 +2,4 @@
\i db/migrations/0001_canonical_schema.sql
\i db/migrations/0002_transactions_unique.sql
\i db/migrations/0003_image_intake.sql
+\i db/migrations/0004_valuation_calls.sql
diff --git a/lib/audit.js b/lib/audit.js
new file mode 100644
index 0000000..d54b381
--- /dev/null
+++ b/lib/audit.js
@@ -0,0 +1,123 @@
+// audit.js — fire-and-forget writer for valuation_calls.
+//
+// Express middleware that records every /api/* request after the
+// response is sent (no added user-visible latency). Skips /api/health
+// to keep monitoring traffic out of the audit table.
+//
+// Token redaction: if Authorization: Bearer <token> is presented, we
+// store only the sha256 hex digest. Raw tokens never touch the DB.
+
+const crypto = require('crypto');
+const { randomUUID } = require('crypto');
+const { pool } = require('./db');
+
+const SKIP_PATHS = new Set(['/health']); // relative to /api mount
+
+function hashToken(authHeader) {
+ if (!authHeader || typeof authHeader !== 'string') return null;
+ const m = /^Bearer\s+(.+)$/i.exec(authHeader);
+ if (!m) return null;
+ return crypto.createHash('sha256').update(m[1]).digest('hex');
+}
+
+function extractAssetSlug(req) {
+ // Routes like '/api/valuation/:slug' set req.params.slug after matching.
+ if (req.params && req.params.slug) return String(req.params.slug);
+ // For sub-resources like /portfolio?owner=… we don't know the slug.
+ return null;
+}
+
+// recordCall — direct write, used by the middleware AND by tests that
+// want to assert the row landed.
+async function recordCall(row) {
+ return pool.query(
+ `INSERT INTO valuation_calls
+ (called_at, caller_ip, caller_token_hash, route, method,
+ asset_slug, response_status, methodology_version,
+ latency_ms, user_agent, request_id)
+ VALUES (now(), $1, $2, $3, $4, $5, $6, $7, $8, $9, $10)`,
+ [
+ row.caller_ip || null,
+ row.caller_token_hash || null,
+ row.route,
+ row.method,
+ row.asset_slug || null,
+ row.response_status,
+ row.methodology_version || 'v0-stub',
+ row.latency_ms == null ? null : row.latency_ms,
+ row.user_agent || null,
+ row.request_id || null
+ ]
+ );
+}
+
+// auditMiddleware — Express middleware. Mount under the /api router.
+// The route string used is `req.baseUrl + req.route?.path` so the table
+// holds the route TEMPLATE, not the resolved URL with the slug
+// substituted. That keeps the asset_slug query useful (filter by slug
+// AND group by template).
+function auditMiddleware(req, res, next) {
+ const start = process.hrtime.bigint();
+ const requestId = randomUUID();
+ res.setHeader('x-request-id', requestId);
+
+ res.on('finish', () => {
+ if (SKIP_PATHS.has(req.path)) return;
+ const route = req.baseUrl + (req.route ? req.route.path : req.path);
+ const elapsedNs = Number(process.hrtime.bigint() - start);
+ const latencyMs = Math.round(elapsedNs / 1e6);
+ setImmediate(() => {
+ recordCall({
+ caller_ip: req.ip,
+ caller_token_hash: hashToken(req.get('authorization')),
+ route,
+ method: req.method,
+ asset_slug: extractAssetSlug(req),
+ response_status: res.statusCode,
+ methodology_version: 'v0-stub',
+ latency_ms: latencyMs,
+ user_agent: req.get('user-agent') || null,
+ request_id: requestId
+ }).catch((e) => {
+ // Swallow audit-write errors — never let logging break a request.
+ console.error('[audit] write failed:', e.message);
+ });
+ });
+ });
+
+ next();
+}
+
+async function recentCalls({ limit = 100, asset_slug = null } = {}) {
+ const lim = Math.max(1, Math.min(1000, parseInt(limit, 10) || 100));
+ if (asset_slug) {
+ const r = await pool.query(
+ `SELECT id, called_at, caller_ip::text AS caller_ip,
+ route, method, asset_slug, response_status,
+ methodology_version, latency_ms, user_agent, request_id
+ FROM valuation_calls
+ WHERE asset_slug = $1
+ ORDER BY called_at DESC
+ LIMIT $2`,
+ [asset_slug, lim]
+ );
+ return r.rows;
+ }
+ const r = await pool.query(
+ `SELECT id, called_at, caller_ip::text AS caller_ip,
+ route, method, asset_slug, response_status,
+ methodology_version, latency_ms, user_agent, request_id
+ FROM valuation_calls
+ ORDER BY called_at DESC
+ LIMIT $1`,
+ [lim]
+ );
+ return r.rows;
+}
+
+module.exports = {
+ hashToken,
+ recordCall,
+ auditMiddleware,
+ recentCalls
+};
diff --git a/routes/api.js b/routes/api.js
index 5e30417..0ad8b96 100644
--- a/routes/api.js
+++ b/routes/api.js
@@ -11,9 +11,15 @@ const {
patchHolding,
deleteHolding
} = require('../lib/portfolio');
+const { auditMiddleware, recentCalls } = require('../lib/audit');
const router = express.Router();
+// Mount the immutable audit log on every /api/* call. /api/health is
+// skipped inside the middleware to keep monitoring traffic out of the
+// table. See lib/audit.js for the full schema + redaction rules.
+router.use(auditMiddleware);
+
// Resolve build metadata at boot (cheap; doesn't touch the DB).
const PKG_VERSION = (() => {
try { return require('../package.json').version; } catch { return '0.0.0'; }
@@ -122,6 +128,15 @@ router.get('/index/:slug', async (req, res, next) => {
} catch (e) { next(e); }
});
+router.get('/audit/recent', async (req, res, next) => {
+ try {
+ const limit = req.query.limit;
+ const asset_slug = req.query.asset_slug || null;
+ const rows = await recentCalls({ limit, asset_slug });
+ res.json({ count: rows.length, calls: rows });
+ } catch (e) { next(e); }
+});
+
router.get('/portfolio', async (req, res, next) => {
try {
const owner = req.query.owner;
diff --git a/tests/audit.test.js b/tests/audit.test.js
new file mode 100644
index 0000000..049706c
--- /dev/null
+++ b/tests/audit.test.js
@@ -0,0 +1,142 @@
+// audit.test.js — verifies the immutable valuation_calls audit log.
+
+'use strict';
+
+require('dotenv').config();
+process.env.PGHOST = process.env.PGHOST || '/tmp';
+process.env.PGDATABASE = process.env.PGDATABASE || 'lifestyle_asset_intel';
+
+const { test, after } = require('node:test');
+const assert = require('node:assert/strict');
+
+const app = require('../server');
+const { pool } = require('../lib/db');
+const { hashToken } = require('../lib/audit');
+
+let server;
+let baseUrl;
+
+function ready() {
+ return new Promise((resolve) => {
+ server = app.listen(0, '127.0.0.1', () => {
+ baseUrl = `http://127.0.0.1:${server.address().port}`;
+ resolve();
+ });
+ });
+}
+function get(path, headers = {}) {
+ return fetch(baseUrl + path, { headers });
+}
+async function getJson(path, headers = {}) {
+ const r = await get(path, headers);
+ let json = null; const text = await r.text();
+ try { json = JSON.parse(text); } catch {}
+ return { status: r.status, json, text };
+}
+
+test('hashToken: bare string → null', () => {
+ assert.equal(hashToken(null), null);
+ assert.equal(hashToken(''), null);
+ assert.equal(hashToken('not-a-bearer'), null);
+});
+test('hashToken: bearer → 64-char sha256 hex digest', () => {
+ const out = hashToken('Bearer abc123');
+ assert.equal(typeof out, 'string');
+ assert.equal(out.length, 64);
+ assert.match(out, /^[0-9a-f]{64}$/);
+ // Same input → same output (deterministic)
+ assert.equal(hashToken('Bearer abc123'), out);
+ // Different input → different output
+ assert.notEqual(hashToken('Bearer abc456'), out);
+});
+
+test('GET /api/valuation/:slug records a row in valuation_calls', async () => {
+ await ready();
+ // Use a unique user-agent so we can locate THIS test's row without
+ // racing other test files (node --test runs files in parallel).
+ const TEST_UA = `audit-test/${Date.now()}-${Math.random().toString(36).slice(2, 8)}`;
+ const before = await pool.query(`SELECT COUNT(*)::int AS n FROM valuation_calls`);
+ const r = await getJson('/api/valuation/birkin-30-togo-gold-ghw-us', {
+ 'user-agent': TEST_UA,
+ 'authorization': 'Bearer test-token-xyz'
+ });
+ assert.equal(r.status, 200);
+
+ // Audit write is fire-and-forget on res.finish — give it a moment.
+ await new Promise((res) => setTimeout(res, 200));
+
+ const row = await pool.query(
+ `SELECT route, method, asset_slug, response_status, methodology_version,
+ user_agent, caller_token_hash, latency_ms, request_id
+ FROM valuation_calls
+ WHERE user_agent = $1
+ ORDER BY called_at DESC LIMIT 1`,
+ [TEST_UA]
+ );
+ const r0 = row.rows[0];
+ assert.ok(r0, 'audit row inserted');
+ assert.equal(r0.method, 'GET');
+ assert.equal(r0.asset_slug, 'birkin-30-togo-gold-ghw-us');
+ assert.equal(r0.response_status, 200);
+ assert.equal(r0.methodology_version, 'v0-stub');
+ assert.equal(r0.user_agent, TEST_UA);
+ assert.match(r0.caller_token_hash, /^[0-9a-f]{64}$/);
+ assert.ok(typeof r0.latency_ms === 'number' && r0.latency_ms >= 0);
+ assert.match(r0.route, /\/api\/valuation/, 'route is the template path');
+ assert.match(r0.request_id, /^[0-9a-f-]{36}$/);
+
+ const after = await pool.query(`SELECT COUNT(*)::int AS n FROM valuation_calls`);
+ assert.ok(after.rows[0].n > before.rows[0].n);
+});
+
+test('GET /api/health is NOT logged (skipped per SKIP_PATHS)', async () => {
+ const before = await pool.query(`SELECT COUNT(*)::int AS n FROM valuation_calls`);
+ const r = await getJson('/api/health');
+ assert.equal(r.status, 200);
+ await new Promise((res) => setTimeout(res, 200));
+ const after = await pool.query(`SELECT COUNT(*)::int AS n FROM valuation_calls`);
+ assert.equal(after.rows[0].n, before.rows[0].n, 'health probe should not be audited');
+});
+
+test('GET /api/audit/recent returns recent calls in DESC order', async () => {
+ // Generate a couple known calls.
+ await getJson('/api/valuation/birkin-25-togo-gold-ghw-us');
+ await getJson('/api/valuation/kelly-25-sellier-epsom-black-ghw-us');
+ await new Promise((res) => setTimeout(res, 250));
+
+ const r = await getJson('/api/audit/recent?limit=10');
+ assert.equal(r.status, 200);
+ assert.ok(Array.isArray(r.json.calls));
+ assert.ok(r.json.calls.length >= 2);
+ for (let i = 1; i < r.json.calls.length; i++) {
+ const prev = new Date(r.json.calls[i - 1].called_at).getTime();
+ const cur = new Date(r.json.calls[i].called_at).getTime();
+ assert.ok(prev >= cur, 'descending called_at order');
+ }
+});
+
+test('GET /api/audit/recent?asset_slug filters', async () => {
+ const r = await getJson('/api/audit/recent?asset_slug=birkin-25-togo-gold-ghw-us');
+ assert.equal(r.status, 200);
+ // All returned rows should match the filter (or there are zero).
+ for (const c of r.json.calls) {
+ assert.equal(c.asset_slug, 'birkin-25-togo-gold-ghw-us');
+ }
+});
+
+test('valuation_calls has no UPDATE or DELETE called by app code', async () => {
+ // Sanity: app should never mutate the table. A grep would verify
+ // this at code-review time, but the fastest check at runtime is that
+ // no rows have changed timestamps drifting (there's no updated_at
+ // column — the table is INSERT-only by schema design).
+ const r = await pool.query(`
+ SELECT column_name FROM information_schema.columns
+ WHERE table_name = 'valuation_calls' AND column_name IN ('updated_at','deleted_at')
+ `);
+ assert.equal(r.rows.length, 0, 'audit table must remain INSERT-only');
+});
+
+after(async () => {
+ if (server) await new Promise((res) => server.close(res));
+ await pool.end();
+});
← 0bd3a76 yolo tick #6: /api/version introspection endpoint
·
back to Lifestyle Asset Intel
·
yolo tick #8: MSRP series + premium-to-retail ratio 19cb967 →