graham.systems
A self-hosted household ledger.
1/// <reference lib="deno.ns" />
2import { openDatabase } from '../db.ts';
3import { runSync } from './sync.ts';
4import { categorizeManually } from './categorization.ts';
5import { commitImport, createDraftImport, saveMapping } from './imports.ts';
6import type { DatabaseSync } from 'node:sqlite';
7
8const MIGRATIONS_DIR = new URL('../../../../migrations', import.meta.url).pathname.replace(
9 /^\/([A-Za-z]:)/,
10 '$1'
11);
12
13function testDb(): DatabaseSync {
14 const db = openDatabase(`${Deno.makeTempDirSync()}/test.db`, MIGRATIONS_DIR);
15 db.prepare(
16 "INSERT INTO connections (access_url, claimed_at) VALUES ('https://u:p@bridge.test/simplefin', ?)"
17 ).run(new Date().toISOString());
18 db.prepare("INSERT INTO users (did, handle, created_at) VALUES ('did:plc:t', 'tester', ?)").run(
19 new Date().toISOString()
20 );
21 return db;
22}
23
24function fakeFetch(payload: unknown): typeof fetch {
25 return (() =>
26 Promise.resolve(new Response(JSON.stringify(payload), { status: 200 }))) as typeof fetch;
27}
28
29const NOW_S = Math.floor(Date.now() / 1000);
30
31function payloadWith(transactions: unknown[], accountOverrides: Record<string, unknown> = {}) {
32 return {
33 errors: [],
34 accounts: [
35 {
36 org: { name: 'Test Bank', domain: 'bank.test' },
37 id: 'act-1',
38 name: 'Checking',
39 currency: 'USD',
40 balance: '100.00',
41 'balance-date': NOW_S,
42 transactions,
43 ...accountOverrides
44 }
45 ]
46 };
47}
48
49function count(db: DatabaseSync, sql: string): number {
50 return (db.prepare(sql).get() as { n: number }).n;
51}
52
53Deno.test('a sync after a backfill leaves the imported rows alone', async () => {
54 // The hazard this guards: ingestTransactions treats its feed as authoritative
55 // for the account and soft-removes anything pending that the feed omits. A
56 // backfill CSV appears in no feed, ever. Imported rows are posted (pending = 0),
57 // which is what keeps them outside every branch of sync's authority — a
58 // structural property worth pinning down rather than rediscovering.
59 const db = testDb();
60 const pendingTxn = { id: 'p1', posted: null, amount: '-5.00', description: 'PENDING COFFEE' };
61 await runSync(db, fakeFetch(payloadWith([pendingTxn])));
62
63 // Backfill straight into the discovered account, well before the feed's reach.
64 const importId = createDraftImport(db, 'act-1', 'old.csv', 'Date,Description,Amount\n2023-02-01,ANCIENT LUNCH,-9.99\n');
65 saveMapping(db, importId, {
66 date: 'Date',
67 dateFormat: 'iso',
68 amountMode: 'signed',
69 amount: 'Amount',
70 description: 'Description'
71 });
72 commitImport(db, importId);
73
74 // A later sync of the same connection: the feed still knows nothing of 2023.
75 await runSync(db, fakeFetch(payloadWith([pendingTxn, { id: 't9', posted: NOW_S, amount: '-1.00', description: 'NEW THING' }])));
76
77 const imported = db
78 .prepare("SELECT removed_at, pending FROM transactions WHERE description = 'ANCIENT LUNCH'")
79 .get() as { removed_at: string | null; pending: number };
80 if (imported.removed_at !== null) throw new Error('a sync must not remove backfilled rows');
81 if (imported.pending !== 0) throw new Error('imported rows must stay posted');
82
83 if (count(db, "SELECT COUNT(*) n FROM transactions WHERE description = 'ANCIENT LUNCH'") !== 1) {
84 throw new Error('a sync must not duplicate a backfilled row');
85 }
86 // And the account is still healthy: the backfill did not make it look absent.
87 const account = db.prepare("SELECT state FROM accounts WHERE id = 'act-1'").get() as {
88 state: string;
89 };
90 if (account.state === 'INACTIVE') throw new Error('the account should not have gone inactive');
91 db.close();
92});
93
94Deno.test('sync archives raw payload, discovers account as NEW, snapshots balance', async () => {
95 const db = testDb();
96 const payload = payloadWith([
97 { id: 't1', posted: NOW_S - 1000, amount: '-10.00', description: 'STORE A' }
98 ]);
99 const [outcome] = await runSync(db, fakeFetch(payload));
100 if (!outcome.ok) throw new Error(`sync failed: ${outcome.error}`);
101 if (outcome.newTransactions !== 1) throw new Error('expected 1 new transaction');
102 // first sync = routine pass + deep backfill pass (new account discovered)
103 if (count(db, 'SELECT COUNT(*) n FROM raw_syncs WHERE ok = 1') !== 2)
104 throw new Error('expected both passes archived');
105 const account = db.prepare("SELECT state FROM accounts WHERE id = 'act-1'").get() as {
106 state: string;
107 };
108 if (account.state !== 'NEW') throw new Error(`expected NEW, got ${account.state}`);
109 if (count(db, 'SELECT COUNT(*) n FROM balance_snapshots') !== 2)
110 throw new Error('expected a snapshot per pass');
111 db.close();
112});
113
114Deno.test('new accounts trigger one deep backfill pass; established syncs fetch once', async () => {
115 const db = testDb();
116 const startDates: number[] = [];
117 const countingFetch = ((input: RequestInfo | URL) => {
118 startDates.push(Number(new URL(String(input)).searchParams.get('start-date')));
119 return Promise.resolve(
120 new Response(JSON.stringify(payloadWith([])), { status: 200 })
121 );
122 }) as typeof fetch;
123
124 await runSync(db, countingFetch); // discovers act-1
125 if (startDates.length !== 2) throw new Error(`expected 2 fetches on first sync, got ${startDates.length}`);
126 const daysBack = (ts: number) => Math.round((Date.now() / 1000 - ts) / 86400);
127 if (daysBack(startDates[0]) > 45) throw new Error('routine pass should stay under 45 days');
128 if (daysBack(startDates[1]) < 60) throw new Error('backfill pass should reach deep');
129
130 await runSync(db, countingFetch); // no new accounts now
131 const totalFetches: number = startDates.length;
132 if (totalFetches !== 3) throw new Error('established sync should fetch exactly once');
133 db.close();
134});
135
136Deno.test('re-syncing the same payload is a no-op for transactions', async () => {
137 const db = testDb();
138 const payload = payloadWith([
139 { id: 't1', posted: NOW_S - 1000, amount: '-10.00', description: 'STORE A' },
140 { id: 't2', posted: NOW_S - 2000, amount: '2500.00', description: 'PAYROLL' }
141 ]);
142 await runSync(db, fakeFetch(payload));
143 const [second] = await runSync(db, fakeFetch(payload));
144 if (second.newTransactions !== 0) throw new Error('second sync inserted duplicates');
145 if (count(db, 'SELECT COUNT(*) n FROM transactions') !== 2)
146 throw new Error('expected exactly 2 transactions');
147 // snapshots accumulate per pass: first sync (2 passes) + second sync (1)
148 if (count(db, 'SELECT COUNT(*) n FROM balance_snapshots') !== 3)
149 throw new Error('expected 3 snapshots');
150 db.close();
151});
152
153Deno.test('range advisories are excluded from connection-error banners', async () => {
154 const db = testDb();
155 const payload = {
156 errors: [
157 'Requested date range exceeds recommended range of 45 days. In the future, this may be capped.',
158 'Connection to Chase needs attention'
159 ],
160 accounts: []
161 };
162 await runSync(db, fakeFetch(payload));
163 const { getConnectionErrors } = await import('./sync-status.ts');
164 const errors = getConnectionErrors(db);
165 if (errors.length !== 1 || !errors[0].includes('Chase')) {
166 throw new Error(`advisory should be filtered, got: ${JSON.stringify(errors)}`);
167 }
168 db.close();
169});
170
171Deno.test('categorized pending transaction posting under a new id carries category via reconciliation event', async () => {
172 const db = testDb();
173 await runSync(
174 db,
175 fakeFetch(
176 payloadWith([
177 {
178 id: 'pend-1',
179 posted: 0,
180 pending: true,
181 transacted_at: NOW_S - 3600,
182 amount: '-25.00',
183 description: 'COFFEE SHOP (PENDING)'
184 }
185 ])
186 )
187 );
188 const pendingRow = db.prepare('SELECT id FROM transactions').get() as { id: number };
189 db.prepare("INSERT INTO categories (name, kind, created_at) VALUES ('Coffee','expense',?)").run(
190 new Date().toISOString()
191 );
192 const categoryId = Number(
193 (db.prepare("SELECT id FROM categories WHERE name='Coffee'").get() as { id: number }).id
194 );
195 categorizeManually(db, pendingRow.id, categoryId, 'did:plc:t');
196
197 // Next sync: pending vanished, posted appears under a different id.
198 const [outcome] = await runSync(
199 db,
200 fakeFetch(
201 payloadWith([
202 { id: 'post-9', posted: NOW_S, amount: '-25.00', description: 'COFFEE SHOP' }
203 ])
204 )
205 );
206 if (outcome.reconciled !== 1) throw new Error(`expected 1 reconciled, got ${outcome.reconciled}`);
207 const txn = db
208 .prepare("SELECT id, sfin_id, pending, category_id, removed_at FROM transactions")
209 .get() as Record<string, unknown>;
210 if (txn.sfin_id !== 'post-9') throw new Error('row not replaced in place');
211 if (txn.pending !== 0) throw new Error('still pending');
212 if (txn.category_id !== categoryId) throw new Error('category not carried');
213 if (txn.removed_at !== null) throw new Error('should not be removed');
214 const events = db
215 .prepare('SELECT source FROM categorization_events ORDER BY id')
216 .all() as { source: string }[];
217 if (events.map((e) => e.source).join(',') !== 'manual,reconciliation')
218 throw new Error(`unexpected event trail: ${events.map((e) => e.source)}`);
219 db.close();
220});
221
222Deno.test('stale pending transaction with no posted match is soft-removed', async () => {
223 const db = testDb();
224 await runSync(
225 db,
226 fakeFetch(
227 payloadWith([
228 { id: 'pend-2', posted: 0, pending: true, amount: '-5.00', description: 'GHOST' }
229 ])
230 )
231 );
232 const [outcome] = await runSync(db, fakeFetch(payloadWith([])));
233 if (outcome.removedPending !== 1) throw new Error('expected 1 removed pending');
234 const row = db.prepare('SELECT removed_at FROM transactions').get() as {
235 removed_at: string | null;
236 };
237 if (!row.removed_at) throw new Error('pending row not soft-removed');
238 db.close();
239});
240
241Deno.test('ACTIVE account absent from feed goes INACTIVE and returns on reappearance', async () => {
242 const db = testDb();
243 await runSync(db, fakeFetch(payloadWith([])));
244 db.prepare("UPDATE accounts SET state='ACTIVE', account_type='checking' WHERE id='act-1'").run();
245
246 // feed with a different account only
247 const otherAccount = {
248 errors: [],
249 accounts: [
250 {
251 org: { name: 'Other' },
252 id: 'act-2',
253 name: 'Savings',
254 currency: 'USD',
255 balance: '1.00',
256 transactions: []
257 }
258 ]
259 };
260 await runSync(db, fakeFetch(otherAccount));
261 let state = (db.prepare("SELECT state FROM accounts WHERE id='act-1'").get() as { state: string })
262 .state;
263 if (state !== 'INACTIVE') throw new Error(`expected INACTIVE, got ${state}`);
264
265 await runSync(db, fakeFetch(payloadWith([])));
266 state = (db.prepare("SELECT state FROM accounts WHERE id='act-1'").get() as { state: string })
267 .state;
268 if (state !== 'ACTIVE') throw new Error(`expected ACTIVE after reappearing, got ${state}`);
269 db.close();
270});
271
272Deno.test('failed fetch archives a failure row and touches nothing else', async () => {
273 const db = testDb();
274 const failingFetch = (() => Promise.reject(new Error('network down'))) as unknown as typeof fetch;
275 const [outcome] = await runSync(db, failingFetch);
276 if (outcome.ok) throw new Error('expected failure');
277 const raw = db.prepare('SELECT ok, error FROM raw_syncs').get() as {
278 ok: number;
279 error: string;
280 };
281 if (raw.ok !== 0 || !raw.error.includes('network down'))
282 throw new Error('failure not recorded');
283 if (count(db, 'SELECT COUNT(*) n FROM transactions') !== 0)
284 throw new Error('transactions should be untouched');
285 db.close();
286});