← back to AbramsOS
tick 17: manual CSV upload route (backlog item 15)
fe7ffca46cf663f1a6a2bd001eefc121813a3878 · 2026-05-10 11:06:07 -0700 · Steve
- npm install multer
- lib/csv-parser.js: parses statement-style CSVs
· auto-detects date / amount / merchant columns (case-insensitive)
· handles quoted fields with embedded commas, dollar signs, parens-as-negative
· skips refunds (negative amounts) and bad rows; reports first 5 errors
- routes/upload.js: POST /api/upload/csv (multer in-memory, 10 MB cap)
→ parser → INSERT purchase rows + audit_log entries
- 10/10 csv-parser tests
Files touched
A lib/csv-parser.jsM package-lock.jsonM package.jsonA routes/upload.jsM server.jsA tests/csv-parser.test.js
Diff
commit fe7ffca46cf663f1a6a2bd001eefc121813a3878
Author: Steve <steve@designerwallcoverings.com>
Date: Sun May 10 11:06:07 2026 -0700
tick 17: manual CSV upload route (backlog item 15)
- npm install multer
- lib/csv-parser.js: parses statement-style CSVs
· auto-detects date / amount / merchant columns (case-insensitive)
· handles quoted fields with embedded commas, dollar signs, parens-as-negative
· skips refunds (negative amounts) and bad rows; reports first 5 errors
- routes/upload.js: POST /api/upload/csv (multer in-memory, 10 MB cap)
→ parser → INSERT purchase rows + audit_log entries
- 10/10 csv-parser tests
---
lib/csv-parser.js | 127 +++++++++++++++++++++++++++++++++++++++++++++++
package-lock.json | 101 +++++++++++++++++++++++++++++++++++++
package.json | 1 +
routes/upload.js | 70 ++++++++++++++++++++++++++
server.js | 2 +
tests/csv-parser.test.js | 85 +++++++++++++++++++++++++++++++
6 files changed, 386 insertions(+)
diff --git a/lib/csv-parser.js b/lib/csv-parser.js
new file mode 100644
index 0000000..6327f21
--- /dev/null
+++ b/lib/csv-parser.js
@@ -0,0 +1,127 @@
+// Minimal statement-style CSV parser.
+// Supports: any CSV with a header row that includes (case-insensitive)
+// date column — one of: date, transaction date, posted date, post date
+// amount column — one of: amount, debit, charge, total
+// merchant col — one of: description, merchant, payee, memo, name
+//
+// Returns { rows: [{ date, merchant, amount, raw }], skipped: N, errors: [] }.
+// Robust to:
+// - quoted fields with commas inside
+// - $ / commas in amounts
+// - negative amounts (refunds — caller can filter)
+// - blank lines
+
+function parseLine(line) {
+ // Tiny CSV tokenizer that respects double-quoted fields with embedded commas + ""
+ const fields = [];
+ let cur = '';
+ let inQuotes = false;
+ for (let i = 0; i < line.length; i++) {
+ const c = line[i];
+ if (inQuotes) {
+ if (c === '"' && line[i + 1] === '"') { cur += '"'; i++; }
+ else if (c === '"') { inQuotes = false; }
+ else cur += c;
+ } else {
+ if (c === '"') inQuotes = true;
+ else if (c === ',') { fields.push(cur); cur = ''; }
+ else cur += c;
+ }
+ }
+ fields.push(cur);
+ return fields;
+}
+
+const DATE_HEADERS = ['date', 'transaction date', 'posted date', 'post date', 'trans date', 'transaction_date'];
+const AMOUNT_HEADERS = ['amount', 'debit', 'charge', 'total', 'transaction amount', 'amt'];
+const MERCHANT_HEADERS = ['description', 'merchant', 'payee', 'memo', 'name', 'details', 'particulars'];
+
+function findColumn(header, candidates) {
+ const lc = header.map(h => h.trim().toLowerCase());
+ for (const cand of candidates) {
+ const i = lc.indexOf(cand);
+ if (i >= 0) return i;
+ }
+ return -1;
+}
+
+function parseAmount(s) {
+ if (s == null) return null;
+ const cleaned = String(s).replace(/[$,\s]/g, '').replace(/[()]/g, (c) => c === '(' ? '-' : '');
+ if (!cleaned || cleaned === '-' || isNaN(Number(cleaned))) return null;
+ return Number(cleaned);
+}
+
+function parseDate(s) {
+ if (!s) return null;
+ const t = Date.parse(s);
+ if (!isNaN(t)) return new Date(t);
+ // Common US "MM/DD/YYYY" / "MM-DD-YYYY"
+ const m = String(s).match(/^(\d{1,2})[-\/](\d{1,2})[-\/](\d{2,4})$/);
+ if (m) {
+ let [, mo, d, y] = m;
+ if (y.length === 2) y = (Number(y) > 70 ? '19' : '20') + y;
+ const t2 = Date.parse(`${y}-${mo.padStart(2,'0')}-${d.padStart(2,'0')}`);
+ if (!isNaN(t2)) return new Date(t2);
+ }
+ return null;
+}
+
+/**
+ * @param csv raw CSV string
+ * @returns { rows, skipped, errors, columns }
+ */
+function parseStatement(csv) {
+ const lines = String(csv || '')
+ .replace(/\r\n/g, '\n')
+ .split('\n')
+ .filter(l => l.trim().length > 0);
+
+ if (lines.length < 2) {
+ return { rows: [], skipped: 0, errors: ['empty or header-only csv'], columns: null };
+ }
+
+ const header = parseLine(lines[0]);
+ const dateIdx = findColumn(header, DATE_HEADERS);
+ const amountIdx = findColumn(header, AMOUNT_HEADERS);
+ const merchantIdx = findColumn(header, MERCHANT_HEADERS);
+
+ if (dateIdx < 0 || amountIdx < 0) {
+ return {
+ rows: [], skipped: 0,
+ errors: [`missing required columns (date=${dateIdx}, amount=${amountIdx}); found: ${header.join(', ')}`],
+ columns: { dateIdx, amountIdx, merchantIdx },
+ };
+ }
+
+ const rows = [];
+ const errors = [];
+ let skipped = 0;
+
+ for (let i = 1; i < lines.length; i++) {
+ const f = parseLine(lines[i]);
+ const date = parseDate(f[dateIdx]);
+ const amount = parseAmount(f[amountIdx]);
+ if (!date || amount == null) {
+ skipped += 1;
+ if (errors.length < 5) errors.push(`line ${i + 1}: bad row (date=${f[dateIdx]}, amount=${f[amountIdx]})`);
+ continue;
+ }
+ if (amount <= 0) { // refund / deposit; CSV uploads are for purchases
+ skipped += 1;
+ continue;
+ }
+ const merchantRaw = merchantIdx >= 0 ? (f[merchantIdx] || '').trim() : '';
+ const merchant = merchantRaw.replace(/\s+/g, ' ').slice(0, 200) || 'Unknown';
+ rows.push({
+ date,
+ amount,
+ merchant,
+ raw: { line: i + 1, fields: f, header },
+ });
+ }
+
+ return { rows, skipped, errors, columns: { dateIdx, amountIdx, merchantIdx } };
+}
+
+module.exports = { parseStatement, parseLine, parseAmount, parseDate };
diff --git a/package-lock.json b/package-lock.json
index 81f24ff..0926beb 100644
--- a/package-lock.json
+++ b/package-lock.json
@@ -18,6 +18,7 @@
"googleapis": "^144.0.0",
"helmet": "^8.0.0",
"morgan": "^1.10.0",
+ "multer": "^2.1.1",
"node-cron": "^4.2.1",
"otplib": "^12.0.1",
"pdf-parse": "^2.4.5",
@@ -327,6 +328,12 @@
"node": ">= 8"
}
},
+ "node_modules/append-field": {
+ "version": "1.0.0",
+ "resolved": "https://registry.npmjs.org/append-field/-/append-field-1.0.0.tgz",
+ "integrity": "sha512-klpgFSWLW1ZEs8svjfb7g4qWY0YS5imI82dTg+QahUvJ8YqAY0P10Uk8tTyh9ZGuYEZEMaeJYCF5BFuX552hsw==",
+ "license": "MIT"
+ },
"node_modules/array-flatten": {
"version": "1.1.1",
"resolved": "https://registry.npmjs.org/array-flatten/-/array-flatten-1.1.1.tgz",
@@ -518,6 +525,23 @@
"integrity": "sha512-zRpUiDwd/xk6ADqPMATG8vc9VPrkck7T07OIx0gnjmJAnHnTVXNQG3vfvWNuiZIkwu9KrKdA1iJKfsfTVxE6NA==",
"license": "BSD-3-Clause"
},
+ "node_modules/buffer-from": {
+ "version": "1.1.2",
+ "resolved": "https://registry.npmjs.org/buffer-from/-/buffer-from-1.1.2.tgz",
+ "integrity": "sha512-E+XQCRwSbaaiChtv6k6Dwgc+bx+Bs6vuKJHHl5kox/BaKbhiXzqQOwK4cO22yElGp2OCmjwVhT3HmxgyPGnJfQ==",
+ "license": "MIT"
+ },
+ "node_modules/busboy": {
+ "version": "1.6.0",
+ "resolved": "https://registry.npmjs.org/busboy/-/busboy-1.6.0.tgz",
+ "integrity": "sha512-8SFQbg/0hQ9xy3UNTB0YEnsNBbWfhf7RtnzpL7TkBiTBRfrQ9Fxcnz7VJsleJpyp6rVLvXiuORqjlHi5q+PYuA==",
+ "dependencies": {
+ "streamsearch": "^1.1.0"
+ },
+ "engines": {
+ "node": ">=10.16.0"
+ }
+ },
"node_modules/bytes": {
"version": "3.1.2",
"resolved": "https://registry.npmjs.org/bytes/-/bytes-3.1.2.tgz",
@@ -631,6 +655,21 @@
"node": ">= 0.8"
}
},
+ "node_modules/concat-stream": {
+ "version": "2.0.0",
+ "resolved": "https://registry.npmjs.org/concat-stream/-/concat-stream-2.0.0.tgz",
+ "integrity": "sha512-MWufYdFw53ccGjCA+Ol7XJYpAlW6/prSMzuPOTRnJGcGzuhLn4Scrz7qf6o8bROZ514ltazcIFJZevcfbo0x7A==",
+ "engines": [
+ "node >= 6.0"
+ ],
+ "license": "MIT",
+ "dependencies": {
+ "buffer-from": "^1.0.0",
+ "inherits": "^2.0.3",
+ "readable-stream": "^3.0.2",
+ "typedarray": "^0.0.6"
+ }
+ },
"node_modules/content-disposition": {
"version": "0.5.4",
"resolved": "https://registry.npmjs.org/content-disposition/-/content-disposition-0.5.4.tgz",
@@ -1690,6 +1729,25 @@
"integrity": "sha512-6FlzubTLZG3J2a/NVCAleEhjzq5oxgHyaCU9yYXvcLsvoVaHJq/s5xXI6/XXP6tz7R9xAOtHnSO/tXtF3WRTlA==",
"license": "MIT"
},
+ "node_modules/multer": {
+ "version": "2.1.1",
+ "resolved": "https://registry.npmjs.org/multer/-/multer-2.1.1.tgz",
+ "integrity": "sha512-mo+QTzKlx8R7E5ylSXxWzGoXoZbOsRMpyitcht8By2KHvMbf3tjwosZ/Mu/XYU6UuJ3VZnODIrak5ZrPiPyB6A==",
+ "license": "MIT",
+ "dependencies": {
+ "append-field": "^1.0.0",
+ "busboy": "^1.6.0",
+ "concat-stream": "^2.0.0",
+ "type-is": "^1.6.18"
+ },
+ "engines": {
+ "node": ">= 10.16.0"
+ },
+ "funding": {
+ "type": "opencollective",
+ "url": "https://opencollective.com/express"
+ }
+ },
"node_modules/negotiator": {
"version": "0.6.3",
"resolved": "https://registry.npmjs.org/negotiator/-/negotiator-0.6.3.tgz",
@@ -2233,6 +2291,20 @@
"node": ">= 0.8"
}
},
+ "node_modules/readable-stream": {
+ "version": "3.6.2",
+ "resolved": "https://registry.npmjs.org/readable-stream/-/readable-stream-3.6.2.tgz",
+ "integrity": "sha512-9u/sniCrY3D5WdsERHzHE4G2YCXqoG5FTHUiCC4SIbr6XcLZBY05ya9EKjYek9O5xOAwjGq+1JdGBAS7Q9ScoA==",
+ "license": "MIT",
+ "dependencies": {
+ "inherits": "^2.0.3",
+ "string_decoder": "^1.1.1",
+ "util-deprecate": "^1.0.1"
+ },
+ "engines": {
+ "node": ">= 6"
+ }
+ },
"node_modules/readdirp": {
"version": "3.6.0",
"resolved": "https://registry.npmjs.org/readdirp/-/readdirp-3.6.0.tgz",
@@ -2469,6 +2541,23 @@
"node": ">= 0.8"
}
},
+ "node_modules/streamsearch": {
+ "version": "1.1.0",
+ "resolved": "https://registry.npmjs.org/streamsearch/-/streamsearch-1.1.0.tgz",
+ "integrity": "sha512-Mcc5wHehp9aXz1ax6bZUyY5afg9u2rv5cqQI3mRrYkGC8rW2hM02jWuwjtL++LS5qinSyhj2QfLyNsuc+VsExg==",
+ "engines": {
+ "node": ">=10.0.0"
+ }
+ },
+ "node_modules/string_decoder": {
+ "version": "1.3.0",
+ "resolved": "https://registry.npmjs.org/string_decoder/-/string_decoder-1.3.0.tgz",
+ "integrity": "sha512-hkRX8U1WjJFd8LsDJ2yQ/wWWxaopEsABU1XfkM8A+j0+85JAGppt16cr1Whg6KIbb4okU6Mql6BOj+uup/wKeA==",
+ "license": "MIT",
+ "dependencies": {
+ "safe-buffer": "~5.2.0"
+ }
+ },
"node_modules/string-width": {
"version": "4.2.3",
"resolved": "https://registry.npmjs.org/string-width/-/string-width-4.2.3.tgz",
@@ -2576,6 +2665,12 @@
"node": ">= 0.6"
}
},
+ "node_modules/typedarray": {
+ "version": "0.0.6",
+ "resolved": "https://registry.npmjs.org/typedarray/-/typedarray-0.0.6.tgz",
+ "integrity": "sha512-/aCDEGatGvZ2BIk+HmLf4ifCJFwvKFNb9/JeZPMulfgFracn9QFcAf5GO8B/mweUjSoblS5In0cWhqpfs/5PQA==",
+ "license": "MIT"
+ },
"node_modules/ulid": {
"version": "2.4.0",
"resolved": "https://registry.npmjs.org/ulid/-/ulid-2.4.0.tgz",
@@ -2607,6 +2702,12 @@
"integrity": "sha512-XdVKMF4SJ0nP/O7XIPB0JwAEuT9lDIYnNsK8yGVe43y0AWoKeJNdv3ZNWh7ksJ6KqQFjOO6ox/VEitLnaVNufw==",
"license": "BSD"
},
+ "node_modules/util-deprecate": {
+ "version": "1.0.2",
+ "resolved": "https://registry.npmjs.org/util-deprecate/-/util-deprecate-1.0.2.tgz",
+ "integrity": "sha512-EPD5q1uXyFxJpCrLnCc1nHnq3gOa6DZBocAIiI2TaSCA7VCJ1UJDMagCzIkXNsUYfD1daK//LTEQ8xiIbrHtcw==",
+ "license": "MIT"
+ },
"node_modules/utils-merge": {
"version": "1.0.1",
"resolved": "https://registry.npmjs.org/utils-merge/-/utils-merge-1.0.1.tgz",
diff --git a/package.json b/package.json
index dd98305..762857b 100644
--- a/package.json
+++ b/package.json
@@ -20,6 +20,7 @@
"googleapis": "^144.0.0",
"helmet": "^8.0.0",
"morgan": "^1.10.0",
+ "multer": "^2.1.1",
"node-cron": "^4.2.1",
"otplib": "^12.0.1",
"pdf-parse": "^2.4.5",
diff --git a/routes/upload.js b/routes/upload.js
new file mode 100644
index 0000000..7663506
--- /dev/null
+++ b/routes/upload.js
@@ -0,0 +1,70 @@
+// Manual CSV upload route — drag-drop a bank-statement CSV → purchase rows.
+
+const express = require('express');
+const multer = require('multer');
+const db = require('../lib/db');
+const audit = require('../lib/audit');
+const { id } = require('../lib/ids');
+const csvParser = require('../lib/csv-parser');
+
+const router = express.Router();
+const DEV_USER_ID = 'user_steve';
+
+// In-memory storage; file size cap 10 MB; keep CSV in RAM not disk.
+const upload = multer({
+ storage: multer.memoryStorage(),
+ limits: { fileSize: 10 * 1024 * 1024 },
+});
+
+router.post('/api/upload/csv', upload.single('file'), async (req, res) => {
+ if (!req.file) return res.status(400).json({ error: 'no file uploaded; use multipart/form-data with field name "file"' });
+ const csvText = req.file.buffer.toString('utf-8');
+
+ const parsed = csvParser.parseStatement(csvText);
+ if (parsed.errors.length && parsed.rows.length === 0) {
+ return res.status(400).json({ error: 'csv parse failed', details: parsed.errors });
+ }
+
+ let inserted = 0;
+ for (const r of parsed.rows) {
+ const purchaseId = id('purchase');
+ try {
+ await db.query(
+ `INSERT INTO purchase
+ (id, user_id, source_message_id, merchant_name, merchant_domain, order_number, purchase_date, total_amount, currency, confidence, raw_extract)
+ VALUES ($1, $2, NULL, $3, NULL, NULL, $4, $5, 'USD', $6, $7)`,
+ [purchaseId, DEV_USER_ID, r.merchant, r.date, r.amount, 0.85, r]
+ );
+ await audit.log({
+ actorType: 'user',
+ actorId: DEV_USER_ID,
+ objectType: 'purchase',
+ objectId: purchaseId,
+ eventType: 'purchase_extracted',
+ metadata: { source: 'csv_upload', merchant: r.merchant, total: r.amount, line: r.raw.line, filename: req.file.originalname },
+ });
+ inserted += 1;
+ } catch (err) {
+ console.warn('[csv-upload] insert', err.message);
+ }
+ }
+
+ await audit.log({
+ actorType: 'user',
+ actorId: DEV_USER_ID,
+ objectType: 'user_account',
+ objectId: DEV_USER_ID,
+ eventType: 'csv_upload',
+ metadata: { filename: req.file.originalname, size: req.file.size, rows: parsed.rows.length, inserted, skipped: parsed.skipped },
+ });
+
+ res.json({
+ ok: true,
+ filename: req.file.originalname,
+ inserted,
+ skipped: parsed.skipped,
+ parse_errors: parsed.errors.slice(0, 5),
+ });
+});
+
+module.exports = router;
diff --git a/server.js b/server.js
index 95fc750..3214c0c 100644
--- a/server.js
+++ b/server.js
@@ -21,6 +21,7 @@ const remindersRouter = require('./routes/reminders');
const claimsRouter = require('./routes/claims');
const auditRouter = require('./routes/audit');
const recallsRouter = require('./routes/recalls');
+const uploadRouter = require('./routes/upload');
const app = express();
const PORT = parseInt(process.env.PORT || '9931', 10);
@@ -68,6 +69,7 @@ app.use(remindersRouter); // /api/reminders/upcoming, regenerate, dism
app.use(claimsRouter); // /api/claims, /api/claims/from-reminder/:id
app.use(auditRouter); // /audit, /api/audit/events
app.use(recallsRouter); // /recalls, /api/recalls
+app.use(uploadRouter); // /api/upload/csv
// Step-up-required routes (must re-verify TOTP within 60s)
app.use('/import', requireStepUp, importRouter);
diff --git a/tests/csv-parser.test.js b/tests/csv-parser.test.js
new file mode 100644
index 0000000..286b265
--- /dev/null
+++ b/tests/csv-parser.test.js
@@ -0,0 +1,85 @@
+const test = require('node:test');
+const assert = require('node:assert');
+const { parseStatement, parseAmount, parseDate } = require('../lib/csv-parser');
+
+test('parses standard amex-style export', () => {
+ const csv = `Date,Description,Amount
+01/15/2026,"AMAZON.COM, INC",24.99
+01/16/2026,WHOLE FOODS,87.42
+01/17/2026,STARBUCKS,5.50`;
+ const r = parseStatement(csv);
+ assert.strictEqual(r.rows.length, 3);
+ assert.strictEqual(r.rows[0].merchant, 'AMAZON.COM, INC');
+ assert.strictEqual(r.rows[0].amount, 24.99);
+ assert.strictEqual(r.rows[1].merchant, 'WHOLE FOODS');
+ assert.strictEqual(r.skipped, 0);
+});
+
+test('parses chase-style with extra columns', () => {
+ const csv = `Transaction Date,Post Date,Description,Category,Type,Amount,Memo
+12/01/2025,12/02/2025,APPLE.COM/BILL,Shopping,Sale,9.99,
+12/05/2025,12/06/2025,SHELL OIL,Gas,Sale,42.00,`;
+ const r = parseStatement(csv);
+ assert.strictEqual(r.rows.length, 2);
+ assert.strictEqual(r.rows[0].merchant, 'APPLE.COM/BILL');
+});
+
+test('skips refund rows (negative amounts)', () => {
+ const csv = `Date,Description,Amount
+01/01/2026,RETURN BEST BUY,-50.00
+01/02/2026,COFFEE SHOP,4.50`;
+ const r = parseStatement(csv);
+ assert.strictEqual(r.rows.length, 1);
+ assert.strictEqual(r.rows[0].merchant, 'COFFEE SHOP');
+ assert.strictEqual(r.skipped, 1);
+});
+
+test('handles dollar signs and commas in amounts', () => {
+ const csv = `Date,Description,Amount
+01/01/2026,FANCY HOTEL,"$1,234.56"`;
+ const r = parseStatement(csv);
+ assert.strictEqual(r.rows[0].amount, 1234.56);
+});
+
+test('handles parentheses for negatives (rejected as refund)', () => {
+ const csv = `Date,Description,Amount
+01/01/2026,REFUND,(45.00)`;
+ const r = parseStatement(csv);
+ assert.strictEqual(r.rows.length, 0);
+ assert.strictEqual(r.skipped, 1);
+});
+
+test('rejects when required columns missing', () => {
+ const csv = `Foo,Bar
+1,2`;
+ const r = parseStatement(csv);
+ assert.strictEqual(r.rows.length, 0);
+ assert.match(r.errors[0], /missing required columns/);
+});
+
+test('rejects empty input cleanly', () => {
+ const r = parseStatement('');
+ assert.strictEqual(r.rows.length, 0);
+ assert.ok(r.errors.length >= 1);
+});
+
+test('parseAmount edge cases', () => {
+ assert.strictEqual(parseAmount('$1,234.56'), 1234.56);
+ assert.strictEqual(parseAmount('(99.00)'), -99);
+ assert.strictEqual(parseAmount(''), null);
+ assert.strictEqual(parseAmount('not-a-number'), null);
+});
+
+test('parseDate handles common US + ISO formats', () => {
+ assert.ok(parseDate('2026-01-15') instanceof Date);
+ assert.ok(parseDate('01/15/2026') instanceof Date);
+ assert.ok(parseDate('1/15/26') instanceof Date);
+ assert.strictEqual(parseDate(''), null);
+});
+
+test('quoted fields with embedded commas', () => {
+ const csv = `Date,Description,Amount
+01/01/2026,"Smith, John & Associates",100.00`;
+ const r = parseStatement(csv);
+ assert.strictEqual(r.rows[0].merchant, 'Smith, John & Associates');
+});
← 1666967 tick 16: Compliance Guardian (URL allowlist + injection scan
·
back to AbramsOS
·
tick 18: receipt extractor regression fixtures + heuristic f 3458fe1 →