reliability: sync operations are non-atomic — partial failure leaves inconsistent state

[moltboie]

Problem

Both syncSimpleFinData and syncPlaidTransactions perform multiple sequential database operations (upsert accounts, upsert transactions, delete removed transactions, upsert items) without wrapping them in a database transaction.

If the process crashes or a query fails midway:

• Accounts could be updated but transactions still stale
• Transactions could be upserted but removed ones not deleted
• The item's updated timestamp could be set before all data is persisted, causing the next sync to skip the failed window

Example from sync-simple-fin.ts:

await upsertAccountsWithSnapshots(user, investmentAccounts, storedAccounts);
await upsertAccounts(user, otherAccounts);
await processHoldingsPromise;
await upsertInstitutions(institutions);
await upsertTransactions(user, transactions);          // ← crash here
await deleteTransactions(user, removedTransactionIds); // ← never runs
// ...
await upsertItems(user, [{ ...item, updated }]);       // ← never updates cursor

Note: deleteAccounts already correctly uses withTransaction for its multi-step delete. The sync functions should follow the same pattern.

Fix

Wrap each sync function's database operations in withTransaction, passing the transaction client through to the repository functions. The item's updated timestamp should only be set after all operations succeed.

Affected files

• src/server/lib/compute-tools/sync-simple-fin.ts
• src/server/lib/compute-tools/sync-plaid.ts