Chris Olson
c3cf0f3eb8
Foundation: single arm64 container (Hono + SQLite via Drizzle, React + Vite), local sign-in with long-lived sessions and passkeys, roles (read-only, full access, admin), nightly and pre-upgrade backups, light and dark themes, date-based version numbers, and the ideas lightbulb behind IDEAS_ENABLED. Money in: accounts with balances, CSV import with bank detection, a column mapping pop-up for unknown layouts, duplicate checks, transfer pairing and undo; one-tap review with learned rules; activity with search, splits, tags, notes and cash entries. Statement files are blocked from commits by a pre-commit hook; tests use synthetic fixtures only. Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
269 lines
16 KiB
TypeScript
269 lines
16 KiB
TypeScript
import { readFileSync } from 'node:fs';
|
|
import { afterEach, beforeEach, describe, expect, it } from 'vitest';
|
|
import { cleanMerchant } from '../src/import/merchant';
|
|
import { parseCents, parseDate } from '../src/import/parse';
|
|
import { makeApp, setUpAdmin, type Client, type TestApp } from './helpers';
|
|
|
|
// All fixture files are made up, shaped like the real exports' masked profiles.
|
|
const fixture = (name: string) => readFileSync(new URL(`./fixtures/synthetic/${name}`, import.meta.url), 'utf8');
|
|
const CHASE = 'Chase9876_Activity_20261007.csv';
|
|
const DISCOVER = 'Discover-Statement-20261007.csv';
|
|
|
|
let t: TestApp;
|
|
let admin: Client;
|
|
let ids: { checking: number; savings: number; discover: number; chase: number };
|
|
|
|
async function addAccount(body: Record<string, unknown>) {
|
|
const res = await admin.post('/api/v1/accounts', body);
|
|
expect(res.status).toBe(200);
|
|
return res.json.id as number;
|
|
}
|
|
|
|
async function importFile(name: string, accountId?: number) {
|
|
const preview = await admin.upload('/api/v1/imports/files', name, fixture(name));
|
|
expect(preview.status).toBe(200);
|
|
const target = accountId ?? preview.json.account?.id;
|
|
const done = await admin.post('/api/v1/imports', { stagingId: preview.json.stagingId, accountId: target });
|
|
expect(done.status).toBe(200);
|
|
return { preview: preview.json, summary: done.json };
|
|
}
|
|
|
|
const categoryId = async (name: string) => {
|
|
const groups = (await admin.get('/api/v1/categories')).json as { categories: { id: number; name: string }[] }[];
|
|
return groups.flatMap((g) => g.categories).find((c) => c.name === name)!.id;
|
|
};
|
|
|
|
beforeEach(async () => {
|
|
t = await makeApp();
|
|
admin = await setUpAdmin(t);
|
|
ids = {
|
|
checking: await addAccount({ name: 'Checking', kind: 'checking', institution: 'Bank of America', last4: '1234' }),
|
|
savings: await addAccount({ name: 'Savings', kind: 'savings', institution: 'Bank of America', last4: '5678' }),
|
|
discover: await addAccount({ name: 'Discover', kind: 'credit_card', institution: 'Discover' }),
|
|
chase: await addAccount({ name: 'Prime Visa', kind: 'credit_card', institution: 'Chase', last4: '9876' }),
|
|
};
|
|
});
|
|
afterEach(() => t.cleanup());
|
|
|
|
describe('reading statements', () => {
|
|
it('parses money and dates the way banks write them', () => {
|
|
expect(parseCents('1,234.56')).toBe(123456);
|
|
expect(parseCents('-$5.00')).toBe(-500);
|
|
expect(parseCents('(12.50)')).toBe(-1250);
|
|
expect(parseCents('')).toBeNull();
|
|
expect(parseDate('09/03/2026')).toBe('2026-09-03');
|
|
expect(parseDate('2026-09-03')).toBe('2026-09-03');
|
|
expect(parseDate('9/3/26')).toBe('2026-09-03');
|
|
expect(parseDate('02/30/2026')).toBeNull();
|
|
});
|
|
|
|
it('cleans merchant names', () => {
|
|
expect(cleanMerchant('KROGER #123 CHARLOTTE NC').merchant).toBe('Kroger');
|
|
expect(cleanMerchant('AMAZON MKTPL*AB12CD3E4').merchant).toBe('Amazon');
|
|
expect(cleanMerchant('JOES DINER #12 CHARLOTTE NC').merchant).toBe('Joes Diner');
|
|
expect(cleanMerchant('BLUE MOON BOOKS #9 ASHEVILLE NC').merchant).toBe('Blue Moon Books');
|
|
expect(cleanMerchant('DUKE ENERGY DES:BILL PAY ID:XXXXX77777 INDN:JANE DOE').merchant).toBe('Duke Energy');
|
|
expect(cleanMerchant('SQ *CORNER BAKERY 8823 CHARLOTTE NC').merchant).toBe('Corner Bakery');
|
|
expect(cleanMerchant('Town of Riverside NC Bill Payment').merchant).toBe('Town of Riverside NC');
|
|
expect(cleanMerchant('Zelle payment to Jordan Lee Conf# h3k2l1m9').merchant).toBe('Zelle to Jordan Lee');
|
|
expect(cleanMerchant('Zelle payment from Pat Doe Conf# XyZ987abc').merchant).toBe('Zelle from Pat Doe');
|
|
expect(cleanMerchant('Online Banking payment to CRD 4321 Confirmation# 1234567890')).toEqual({ merchant: 'Payment to card 4321', mentionsLast4: '4321' });
|
|
expect(cleanMerchant('WAKE PARKS - NC123 RALEIGH NC 27601').merchant).toBe('Wake Parks');
|
|
expect(cleanMerchant('BLUE RIDGE OUTFITTERS ID1234567890ABC').merchant).toBe('Blue Ridge Outfitters');
|
|
expect(cleanMerchant('7-ELEVEN 34567 CHARLOTTE NC').merchant).toBe('7-Eleven');
|
|
expect(cleanMerchant('ACME CORP DES:PAYROLL ID:XXXXX12345 INDN:DOE,JANE CO ID:XXXXX12345 PPD').merchant).toBe('Acme Corp');
|
|
expect(cleanMerchant('CITY UTILITIES DES:UTIL PYMT ID:XXXXX123 INDN:Jane Doe CO ID:XXXXX WEB').merchant).toBe('City Utilities');
|
|
const transfer = cleanMerchant('Online Banking transfer to SAV 5678 Confirmation# XXXXX12345');
|
|
expect(transfer).toEqual({ merchant: 'Transfer to savings', mentionsLast4: '5678' });
|
|
});
|
|
|
|
it('recognizes each bank and gets money in and out the right way round', async () => {
|
|
const discover = (await admin.upload('/api/v1/imports/files', DISCOVER, fixture(DISCOVER))).json;
|
|
expect(discover.layout.key).toBe('discover-card');
|
|
expect(discover.counts).toMatchObject({ rows: 8, new: 8 });
|
|
expect(discover.newRows[0]).toMatchObject({ merchant: 'Kroger', amountCents: -5420 });
|
|
|
|
const chase = (await admin.upload('/api/v1/imports/files', CHASE, fixture(CHASE))).json;
|
|
expect(chase.layout.key).toBe('chase-card');
|
|
expect(chase.account).toMatchObject({ id: ids.chase, confidence: 'certain' }); // from the file name
|
|
expect(chase.newRows.find((r: { merchant: string; amountCents: number }) => r.amountCents === 1850)?.merchant).toBe('Amazon'); // a return
|
|
|
|
const checking = (await admin.upload('/api/v1/imports/files', 'stmt.csv', fixture('bofa-checking.csv'))).json;
|
|
expect(checking.layout.key).toBe('bofa-deposit');
|
|
expect(checking.counts).toMatchObject({ rows: 5, new: 5 }); // the "Beginning balance" line isn't a transaction
|
|
expect(checking.account).toBeNull(); // two Bank of America accounts: Spendlight asks
|
|
});
|
|
});
|
|
|
|
describe('importing', () => {
|
|
it('skips duplicates by count, so identical charges on one day both survive', async () => {
|
|
const first = await importFile(DISCOVER, ids.discover);
|
|
expect(first.summary.addedCount).toBe(8);
|
|
const coffees = (await admin.get(`/api/v1/transactions?accountId=${ids.discover}&q=starbucks`)).json;
|
|
expect(coffees.total).toBe(2);
|
|
|
|
const again = (await admin.upload('/api/v1/imports/files', DISCOVER, fixture(DISCOVER))).json;
|
|
expect(again.account).toMatchObject({ id: ids.discover, confidence: 'certain' }); // recognized by its transactions
|
|
expect(again.counts).toMatchObject({ new: 0, duplicates: 8 });
|
|
|
|
// A longer statement overlapping the first: only the extra line is new.
|
|
const longer = fixture(DISCOVER) + '09/25/2026,09/25/2026,STARBUCKS #4567 CHARLOTTE NC,5.45,Restaurants\r\n';
|
|
const overlap = (await admin.upload('/api/v1/imports/files', DISCOVER, longer)).json;
|
|
expect(overlap.counts).toMatchObject({ new: 1, duplicates: 8 });
|
|
});
|
|
|
|
it('pairs card payments and transfers between accounts, and keeps them out of spending', async () => {
|
|
await importFile('bofa-checking.csv', ids.checking);
|
|
await importFile('bofa-savings.csv', ids.savings);
|
|
await importFile(DISCOVER, ids.discover);
|
|
const chase = await importFile(CHASE);
|
|
expect(chase.summary.transferCount).toBe(1);
|
|
|
|
const checking = (await admin.get(`/api/v1/transactions?accountId=${ids.checking}`)).json.items as any[];
|
|
const toDiscover = checking.find((x) => x.amountCents === -50000);
|
|
const toSavings = checking.find((x) => x.amountCents === -30000);
|
|
const toChase = checking.find((x) => x.amountCents === -15000);
|
|
expect(toDiscover).toMatchObject({ needsReview: false, transferPeer: { accountName: 'Discover' } });
|
|
expect(toSavings).toMatchObject({ needsReview: false, transferPeer: { accountName: 'Savings' } });
|
|
expect(toChase).toMatchObject({ needsReview: false, transferPeer: { accountName: 'Prime Visa' } });
|
|
expect(toDiscover.categoryId).toBe(await categoryId('Credit Card Payment'));
|
|
expect(toSavings.categoryId).toBe(await categoryId('Transfer Between Accounts'));
|
|
|
|
// Spending totals leave transfers out.
|
|
const sums = (await admin.get(`/api/v1/transactions?accountId=${ids.checking}`)).json.sums;
|
|
expect(sums).toEqual({ inCents: 300000, outCents: -12000 });
|
|
});
|
|
|
|
it('pre-fills categories from the bank and from known merchants', async () => {
|
|
await importFile(DISCOVER, ids.discover);
|
|
const review = (await admin.get('/api/v1/review')).json as any[];
|
|
const by = (m: string) => review.find((r) => r.merchant === m);
|
|
expect(by('Kroger').categoryId).toBe(await categoryId('Groceries'));
|
|
expect(by('Shell').categoryId).toBe(await categoryId('Fuel'));
|
|
expect(by('Netflix').categoryId).toBe(await categoryId('Streaming and Subscriptions'));
|
|
expect(by('Blue Moon Books').categoryId).toBe(await categoryId('General Merchandise')); // the bank's hint
|
|
expect(by('Kroger').suggestions.length).toBeGreaterThanOrEqual(1);
|
|
});
|
|
|
|
it('learns from your picks: three agreeing picks make the next one confident', async () => {
|
|
const dining = await categoryId('Dining Out');
|
|
const coffee = await categoryId('Coffee and Snacks');
|
|
await importFile(DISCOVER, ids.discover);
|
|
const diner = ((await admin.get('/api/v1/review')).json as any[]).find((r) => r.merchant === 'Joes Diner');
|
|
const filed = await admin.patch(`/api/v1/transactions/${diner.id}`, { categoryId: dining });
|
|
expect(filed.json.similar).toBeNull();
|
|
|
|
// Coffee: file one; the prompt offers the other identical one.
|
|
const coffees = ((await admin.get('/api/v1/review')).json as any[]).filter((r) => r.merchant === 'Starbucks');
|
|
const firstCoffee = await admin.patch(`/api/v1/transactions/${coffees[0].id}`, { categoryId: coffee });
|
|
expect(firstCoffee.json.similar).toEqual({ merchant: 'Starbucks', count: 1, categoryId: coffee });
|
|
const yes = await admin.post('/api/v1/transactions/bulk', { merchant: 'Starbucks', categoryId: coffee, remember: true });
|
|
expect(yes.json.updated).toBe(1);
|
|
|
|
// Next month, the rule files Starbucks confidently.
|
|
const next = fixture(DISCOVER).replace(/09\/03\/2026/g, '10/03/2026');
|
|
const preview = (await admin.upload('/api/v1/imports/files', DISCOVER, next)).json;
|
|
await admin.post('/api/v1/imports', { stagingId: preview.stagingId, accountId: ids.discover });
|
|
const october = ((await admin.get('/api/v1/review')).json as any[]).filter((r) => r.merchant === 'Starbucks' && r.date === '2026-10-03');
|
|
expect(october).toHaveLength(2);
|
|
expect(october.every((r) => r.categoryId === coffee && r.confident)).toBe(true);
|
|
|
|
const accepted = await admin.post('/api/v1/transactions/accept', { ids: october.map((r) => r.id) });
|
|
expect(accepted.json.accepted).toBe(2);
|
|
});
|
|
|
|
it('undoes a whole import and puts the other half of a transfer back in Review', async () => {
|
|
await importFile('bofa-checking.csv', ids.checking);
|
|
const discover = await importFile(DISCOVER, ids.discover);
|
|
const del = await admin.del(`/api/v1/imports/${discover.summary.id}`);
|
|
expect(del.json.status).toBe('undone');
|
|
expect((await admin.get(`/api/v1/transactions?accountId=${ids.discover}`)).json.total).toBe(0);
|
|
const payment = ((await admin.get(`/api/v1/transactions?accountId=${ids.checking}`)).json.items as any[]).find(
|
|
(x) => x.amountCents === -50000,
|
|
);
|
|
expect(payment).toMatchObject({ transferPeer: null, needsReview: true });
|
|
});
|
|
|
|
it('keeps balances: running balance from the bank, and an entered card balance plus new charges', async () => {
|
|
await importFile('bofa-checking.csv', ids.checking);
|
|
let accounts = (await admin.get('/api/v1/accounts')).json as any[];
|
|
expect(accounts.find((a) => a.id === ids.checking)).toMatchObject({ balanceCents: 393000, balanceConfirmedOn: '2026-09-20' });
|
|
|
|
await admin.post(`/api/v1/accounts/${ids.discover}/balance`, { balanceCents: -100000, asOf: '2026-09-10' });
|
|
await importFile(DISCOVER, ids.discover);
|
|
accounts = (await admin.get('/api/v1/accounts')).json as any[];
|
|
// Owed $1,000 on Sep 10; then +$500 payment Sep 11 and charges of 47.10 + 17.99 + 22.00 after it.
|
|
expect(accounts.find((a) => a.id === ids.discover).balanceCents).toBe(-100000 + 50000 - 4710 - 1799 - 2200);
|
|
});
|
|
|
|
it('splits a purchase, adds tags and notes, and records cash by hand', async () => {
|
|
await importFile(CHASE);
|
|
const amazon = ((await admin.get(`/api/v1/transactions?accountId=${ids.chase}&q=amazon`)).json.items as any[]).find(
|
|
(x) => x.amountCents === -11245,
|
|
);
|
|
const household = await categoryId('Household Supplies');
|
|
const clothing = await categoryId('Clothing');
|
|
const bad = await admin.call('PUT', `/api/v1/transactions/${amazon.id}/split`, {
|
|
parts: [
|
|
{ categoryId: household, amountCents: -5000 },
|
|
{ categoryId: clothing, amountCents: -5000 },
|
|
],
|
|
});
|
|
expect(bad.json.error.code).toBe('split_total');
|
|
const split = await admin.call('PUT', `/api/v1/transactions/${amazon.id}/split`, {
|
|
parts: [
|
|
{ categoryId: household, amountCents: -6245, note: 'Paper towels' },
|
|
{ categoryId: clothing, amountCents: -5000 },
|
|
],
|
|
});
|
|
expect(split.json).toMatchObject({ isSplit: true, needsReview: false, categoryId: null });
|
|
expect((await admin.get(`/api/v1/transactions?categoryId=${clothing}`)).json.total).toBe(1);
|
|
|
|
const tagged = await admin.patch(`/api/v1/transactions/${amazon.id}`, { tags: ['Back to school', 'back to school', 'Kids'], note: 'For Sam' });
|
|
expect(tagged.json.transaction).toMatchObject({ tags: ['Back to school', 'Kids'], note: 'For Sam' });
|
|
expect((await admin.get('/api/v1/transactions?tag=kids')).json.total).toBe(1);
|
|
|
|
const cash = await addAccount({ name: 'Cash', kind: 'cash' });
|
|
const farmers = await admin.post('/api/v1/transactions', {
|
|
accountId: cash,
|
|
date: '2026-09-20',
|
|
amountCents: -2400,
|
|
merchant: 'Farmers market',
|
|
categoryId: await categoryId('Groceries'),
|
|
});
|
|
expect(farmers.json).toMatchObject({ source: 'manual', needsReview: false });
|
|
expect((await admin.del(`/api/v1/transactions/${amazon.id}`)).json.error.code).toBe('from_statement');
|
|
expect((await admin.del(`/api/v1/transactions/${farmers.json.id}`)).status).toBe(200);
|
|
});
|
|
|
|
it('asks how to read an unknown file once, then remembers the layout', async () => {
|
|
const cash = await addAccount({ name: 'Credit union', kind: 'checking', institution: 'Local CU' });
|
|
const first = (await admin.upload('/api/v1/imports/files', 'export.csv', fixture('unknown-bank.csv'))).json;
|
|
expect(first.layout).toBeNull();
|
|
expect(first.headerRow).toEqual(['Posting Date', 'Payee Name', 'Withdrawal', 'Deposit', 'Balance']);
|
|
expect(first.suggestedMap).toMatchObject({ date: 0, description: 1, debit: 2, credit: 3, balance: 4 });
|
|
|
|
const mapping = { ...first.suggestedMap, dateOrder: 'YMD' };
|
|
const mapped = (await admin.post('/api/v1/imports/preview', { stagingId: first.stagingId, accountId: cash, mapping })).json;
|
|
expect(mapped.counts).toMatchObject({ rows: 3, new: 3 });
|
|
expect(mapped.newRows.map((r: { amountCents: number }) => r.amountCents)).toEqual([-1250, 120000, -825]);
|
|
await admin.post('/api/v1/imports', { stagingId: first.stagingId, accountId: cash, mapping });
|
|
|
|
const second = (await admin.upload('/api/v1/imports/files', 'export (1).csv', fixture('unknown-bank.csv'))).json;
|
|
expect(second.layout).not.toBeNull();
|
|
expect(second.account).toMatchObject({ id: cash, confidence: 'certain' });
|
|
expect(second.counts).toMatchObject({ new: 0, duplicates: 3 });
|
|
});
|
|
|
|
it('lets read-only people look but not import or change anything', async () => {
|
|
const sam = await admin.post('/api/v1/people', { displayName: 'Sam', username: 'sam', access: 'read', password: 'sams-password' });
|
|
expect(sam.status).toBe(200);
|
|
const { Client } = await import('./helpers');
|
|
const reader = new Client(t);
|
|
await reader.post('/api/v1/auth/login', { username: 'sam', password: 'sams-password' });
|
|
expect((await reader.get('/api/v1/accounts')).status).toBe(200);
|
|
expect((await reader.upload('/api/v1/imports/files', DISCOVER, fixture(DISCOVER))).status).toBe(403);
|
|
expect((await reader.post('/api/v1/accounts', { name: 'X', kind: 'cash' })).status).toBe(403);
|
|
});
|
|
});
|