Something went wrong. Try again.
A self-hosted household ledger.
Something went wrong. Try again.
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271/// <reference lib="deno.ns" />import { loadMigrationsFromDir, openDatabase, runMigrations } from "./db.ts";import { MIGRATIONS } from "./migrations.generated.ts";
const MIGRATIONS_DIR = new URL("../../../migrations", import.meta.url).pathname .replace( /^\/([A-Za-z]:)/, "$1", );
Deno.test("migrations apply once and are idempotent across runs", () => { const dbPath = join(Deno.makeTempDirSync(), "test.db"); const db = openDatabase(dbPath, MIGRATIONS_DIR);
const versions = db.prepare( "SELECT version, name FROM schema_version ORDER BY version", ).all(); if (versions.length === 0) throw new Error("no migrations applied");
// second run applies nothing const applied = runMigrations(db, MIGRATIONS_DIR); if (applied !== 0) { throw new Error(`expected 0 re-applied migrations, got ${applied}`); } db.close();});
Deno.test("built-in Transfer category is seeded", () => { const dbPath = join(Deno.makeTempDirSync(), "test.db"); const db = openDatabase(dbPath, MIGRATIONS_DIR); const row = db .prepare("SELECT name, kind, builtin FROM categories WHERE name = ?") .get("Transfer") as | { name: string; kind: string; builtin: number } | undefined; if (!row || row.kind !== "transfer" || row.builtin !== 1) { throw new Error( `Transfer category missing or wrong: ${JSON.stringify(row)}`, ); } db.close();});
Deno.test("foreign keys are enforced", () => { const dbPath = join(Deno.makeTempDirSync(), "test.db"); const db = openDatabase(dbPath, MIGRATIONS_DIR); let threw = false; try { db.prepare( `INSERT INTO transactions (account_id, sfin_id, amount_cents, description, created_at) VALUES ('nonexistent', 't1', 100, 'x', ?)`, ).run(new Date().toISOString()); } catch { threw = true; } if (!threw) throw new Error("expected FK violation"); db.close();});
Deno.test("csv import migration applies to a populated database", () => { // Stage only the migrations before 005, populate, then upgrade — the path an // existing install actually takes. const stageDir = Deno.makeTempDirSync(); for (const entry of Deno.readDirSync(MIGRATIONS_DIR)) { const version = Number(entry.name.match(/^(\d+)_/)?.[1]); if (Number.isFinite(version) && version < 5) { Deno.copyFileSync( join(MIGRATIONS_DIR, entry.name), join(stageDir, entry.name), ); } }
const dbPath = join(Deno.makeTempDirSync(), "test.db"); const db = openDatabase(dbPath, stageDir);
const now = new Date().toISOString(); db.prepare("INSERT INTO connections (access_url, claimed_at) VALUES (?, ?)") .run( "https://example.test", now, ); db.prepare( `INSERT INTO accounts (id, connection_id, name, currency, state, created_at) VALUES ('acct-1', 1, 'Checking', 'USD', 'ACTIVE', ?)`, ).run(now); db.prepare( `INSERT INTO transactions (account_id, sfin_id, posted, amount_cents, description, created_at) VALUES ('acct-1', 'sfin-1', 1700000000, -1234, 'COFFEE', ?)`, ).run(now);
runMigrations(db, MIGRATIONS_DIR); const applied005 = db.prepare( "SELECT 1 FROM schema_version WHERE version = 5", ).get(); if (!applied005) throw new Error("expected migration 005 to apply");
const row = db.prepare( "SELECT import_id FROM transactions WHERE sfin_id = 'sfin-1'", ).get() as { import_id: number | null; }; if (row.import_id !== null) { throw new Error( `pre-existing rows must read back as synced, got ${row.import_id}`, ); }
db.close();});
Deno.test("imports status is a closed enum", () => { const dbPath = join(Deno.makeTempDirSync(), "test.db"); const db = openDatabase(dbPath, MIGRATIONS_DIR); const now = new Date().toISOString(); db.prepare("INSERT INTO connections (access_url, claimed_at) VALUES (?, ?)") .run( "https://example.test", now, ); db.prepare( `INSERT INTO accounts (id, connection_id, name, currency, state, created_at) VALUES ('acct-1', 1, 'Checking', 'USD', 'ACTIVE', ?)`, ).run(now);
let threw = false; try { db.prepare( `INSERT INTO imports (account_id, uploaded_at, payload, status) VALUES ('acct-1', ?, 'raw', 'bogus')`, ).run(now); } catch { threw = true; } if (!threw) throw new Error("expected status CHECK violation"); db.close();});
Deno.test("api_tokens migration applies to a populated database", () => { // Stage only the migrations before 006, populate a categorization event, then // upgrade — the path an existing install actually takes through the rebuild. const stageDir = Deno.makeTempDirSync(); for (const entry of Deno.readDirSync(MIGRATIONS_DIR)) { const version = Number(entry.name.match(/^(\d+)_/)?.[1]); if (Number.isFinite(version) && version < 6) { Deno.copyFileSync( join(MIGRATIONS_DIR, entry.name), join(stageDir, entry.name), ); } }
const dbPath = join(Deno.makeTempDirSync(), "test.db"); const db = openDatabase(dbPath, stageDir);
const now = new Date().toISOString(); db.prepare("INSERT INTO connections (access_url, claimed_at) VALUES (?, ?)") .run("https://example.test", now); db.prepare( `INSERT INTO accounts (id, connection_id, name, currency, state, created_at) VALUES ('acct-1', 1, 'Checking', 'USD', 'ACTIVE', ?)`, ).run(now); db.prepare( `INSERT INTO transactions (account_id, sfin_id, posted, amount_cents, description, created_at) VALUES ('acct-1', 'sfin-1', 1700000000, -1234, 'COFFEE', ?)`, ).run(now); db.prepare("INSERT INTO users (did, handle, created_at) VALUES (?, ?, ?)") .run("did:plc:alice", "alice.test", now); const txId = (db.prepare("SELECT id FROM transactions WHERE sfin_id = 'sfin-1'") .get() as { id: number }).id; // A pre-existing manual event that the rebuild must copy verbatim. db.prepare( `INSERT INTO categorization_events (transaction_id, source, actor_did, created_at) VALUES (?, 'manual', 'did:plc:alice', ?)`, ).run(txId, now);
runMigrations(db, MIGRATIONS_DIR); const applied006 = db.prepare( "SELECT 1 FROM schema_version WHERE version = 6", ).get(); if (!applied006) throw new Error("expected migration 006 to apply");
// The pre-existing event survived the table rebuild, ids and columns intact, // and reads back as non-agent (api_token_id NULL). const event = db.prepare( "SELECT source, actor_did, api_token_id FROM categorization_events WHERE transaction_id = ?", ).get(txId) as | { source: string; actor_did: string | null; api_token_id: number | null } | undefined; if ( !event || event.source !== "manual" || event.actor_did !== "did:plc:alice" || event.api_token_id !== null ) { throw new Error( `rebuilt event wrong or lost: ${JSON.stringify(event)}`, ); }
// An agent event can now be inserted, and it must carry an api_token_id. db.prepare( `INSERT INTO api_tokens (label, token_hash, scope, created_at) VALUES ('claude', 'hash-1', 'readwrite', ?)`, ).run(now); const tokenId = (db.prepare("SELECT id FROM api_tokens WHERE token_hash = 'hash-1'") .get() as { id: number }).id; db.prepare( `INSERT INTO categorization_events (transaction_id, source, api_token_id, created_at) VALUES (?, 'agent', ?, ?)`, ).run(txId, tokenId, now);
db.close();});
Deno.test("agent categorization event requires an api_token_id", () => { const dbPath = join(Deno.makeTempDirSync(), "test.db"); const db = openDatabase(dbPath, MIGRATIONS_DIR); const now = new Date().toISOString(); db.prepare("INSERT INTO connections (access_url, claimed_at) VALUES (?, ?)") .run("https://example.test", now); db.prepare( `INSERT INTO accounts (id, connection_id, name, currency, state, created_at) VALUES ('acct-1', 1, 'Checking', 'USD', 'ACTIVE', ?)`, ).run(now); db.prepare( `INSERT INTO transactions (account_id, sfin_id, amount_cents, description, created_at) VALUES ('acct-1', 'sfin-1', -1234, 'COFFEE', ?)`, ).run(now); const txId = (db.prepare("SELECT id FROM transactions WHERE sfin_id = 'sfin-1'") .get() as { id: number }).id;
let threw = false; try { db.prepare( `INSERT INTO categorization_events (transaction_id, source, created_at) VALUES (?, 'agent', ?)`, ).run(txId, now); } catch { threw = true; } if (!threw) throw new Error("expected agent-without-token CHECK violation");
let scopeThrew = false; try { db.prepare( `INSERT INTO api_tokens (label, token_hash, scope, created_at) VALUES ('x', 'h', 'bogus', ?)`, ).run(now); } catch { scopeThrew = true; } if (!scopeThrew) throw new Error("expected api_tokens scope CHECK violation");
db.close();});
Deno.test("embedded migrations match the .sql files on disk (run gen:migrations if this fails)", () => { const onDisk = loadMigrationsFromDir(MIGRATIONS_DIR); if (JSON.stringify(MIGRATIONS) !== JSON.stringify(onDisk)) { throw new Error( "migrations.generated.ts is stale — run `deno task gen:migrations`", ); }});
function join(...parts: string[]) { return parts.join("/");}