A local-first personal finance app
// @ts-strict-ignore
import * as dateFns from 'date-fns';
import { v4 as uuidv4 } from 'uuid';
import * as asyncStorage from '#platform/server/asyncStorage';
import { logger } from '#platform/server/log';
import { aqlQuery } from '#server/aql';
import * as db from '#server/db';
import { TRANSACTION_SORT_INCREMENT } from '#server/db/sort';
import { TransactionError } from '#server/errors';
import { runMutator } from '#server/mutators';
import { post } from '#server/post';
import { getServer } from '#server/server-config';
import { batchMessages } from '#server/sync';
import { batchUpdateTransactions } from '#server/transactions';
import { runRules } from '#server/transactions/transaction-rules';
import {
defaultMappings,
mappingsFromString,
} from '#server/util/custom-sync-mapping';
import * as monthUtils from '#shared/months';
import { q } from '#shared/query';
import {
makeChild as makeChildTransaction,
recalculateSplit,
} from '#shared/transactions';
import {
amountToInteger,
hasFieldsChanged,
integerToAmount,
} from '#shared/util';
import type {
AccountEntity,
BankSyncResponse,
TransactionEntity,
} from '#types/models';
import { getStartingBalancePayee } from './payees';
import { title } from './title';
function BankSyncError(type: string, code: string, details?: object) {
return { type: 'BankSyncError', category: type, code, details };
}
function makeSplitTransaction(trans, subtransactions) {
// We need to calculate the final state of split transactions
const { subtransactions: sub, ...parent } = recalculateSplit({
...trans,
is_parent: true,
subtransactions: subtransactions.map((transaction, idx) =>
makeChildTransaction(trans, {
...transaction,
sort_order: 0 - idx,
}),
),
});
return [parent, ...sub];
}
function getAccountBalance(account) {
// Debt account types need their balance reversed
switch (account.type) {
case 'credit':
case 'loan':
return -account.balances.current;
default:
return account.balances.current;
}
}
async function updateAccountBalance(id: AccountEntity['id'], balance: number) {
await db.update('accounts', { id, balance_current: balance });
}
async function getAccountOldestTransaction(id): Promise<TransactionEntity> {
return (
await aqlQuery(
q('transactions')
.filter({
account: id,
date: { $lte: monthUtils.currentDay() },
})
.select('date')
.orderBy('date')
.limit(1),
)
).data?.[0];
}
async function getAccountSyncStartDate(id) {
// Many GoCardless integrations do not support getting more than 90 days
// worth of data, so make that the earliest possible limit.
// 89 days ago until today inclusive is 90 days.
const dates = [monthUtils.subDays(monthUtils.currentDay(), 89)];
const oldestTransaction = await getAccountOldestTransaction(id);
if (oldestTransaction) dates.push(oldestTransaction.date);
return monthUtils.dayFromDate(
dateFns.max(dates.map(d => monthUtils.parseDate(d))),
);
}
export async function getGoCardlessAccounts(userId, userKey, id) {
const userToken = await asyncStorage.getItem('user-token');
if (!userToken) return;
const res = await post(
getServer().GOCARDLESS_SERVER + '/accounts',
{
userId,
key: userKey,
item_id: id,
},
{
'X-ACTUAL-TOKEN': userToken,
},
);
const { accounts } = res;
accounts.forEach(acct => {
acct.balances.current = getAccountBalance(acct);
});
return accounts;
}
async function downloadGoCardlessTransactions(
userId,
userKey,
acctId,
bankId,
since,
includeBalance = true,
) {
const userToken = await asyncStorage.getItem('user-token');
if (!userToken) return;
logger.log('Pulling transactions from GoCardless');
const res = await post(
getServer().GOCARDLESS_SERVER + '/transactions',
{
userId,
key: userKey,
requisitionId: bankId,
accountId: acctId,
startDate: since,
includeBalance,
},
{
'X-ACTUAL-TOKEN': userToken,
},
);
if (res.error_code) {
const errorDetails = {
rateLimitHeaders: res.rateLimitHeaders,
};
throw BankSyncError(res.error_type, res.error_code, errorDetails);
}
if (includeBalance) {
const {
transactions: { all },
balances,
startingBalance,
} = res;
logger.log('Response:', res);
return {
transactions: all,
accountBalance: balances,
startingBalance,
};
} else {
logger.log('Response:', res);
return {
transactions: res.transactions.all,
};
}
}
async function downloadSimpleFinTransactions(
acctId: AccountEntity['id'] | AccountEntity['id'][],
since: string | string[],
fileId?: string,
) {
const userToken = await asyncStorage.getItem('user-token');
if (!userToken) return;
const batchSync = Array.isArray(acctId);
logger.log('Pulling transactions from SimpleFin');
let res;
try {
res = await post(
getServer().SIMPLEFIN_SERVER + '/transactions',
{
accountId: acctId,
startDate: since,
},
{
'X-ACTUAL-TOKEN': userToken,
...(fileId ? { 'X-Actual-File-Id': fileId } : {}),
},
// 5 minute timeout for batch sync, one minute for individual accounts
Array.isArray(acctId) ? 300000 : 60000,
);
} catch (error) {
logger.error('Suspected timeout during bank sync:', error);
throw BankSyncError('TIMED_OUT', 'TIMED_OUT');
}
if (Object.keys(res).length === 0) {
throw BankSyncError('NO_DATA', 'NO_DATA');
}
if (res.error_code) {
throw BankSyncError(res.error_type, res.error_code);
}
let retVal = {};
if (batchSync) {
const batchErrors = res.errors;
for (const accountId of Object.keys(res)) {
if (accountId === 'errors') continue;
const data = res[accountId];
const error = batchErrors?.[accountId]?.[0];
retVal[accountId] = {
transactions: data?.transactions?.all,
accountBalance: data?.balances,
startingBalance: data?.startingBalance,
};
if (error) {
retVal[accountId].error_type = error.error_type;
retVal[accountId].error_code = error.error_code;
}
}
// Add entries for accounts that only have errors (no data in the response)
if (batchErrors) {
for (const [accountId, errorList] of Object.entries(batchErrors)) {
if (
!retVal[accountId] &&
Array.isArray(errorList) &&
errorList.length > 0
) {
const error = errorList[0];
retVal[accountId] = {
transactions: [],
accountBalance: [],
startingBalance: 0,
error_type: error.error_type,
error_code: error.error_code,
};
}
}
}
} else {
retVal = {
transactions: res.transactions.all,
accountBalance: res.balances,
startingBalance: res.startingBalance,
};
}
logger.log('Response:', retVal);
return retVal;
}
async function downloadPluggyAiTransactions(
acctId: AccountEntity['id'],
since: string,
fileId?: string,
) {
const userToken = await asyncStorage.getItem('user-token');
if (!userToken) return;
logger.log('Pulling transactions from Pluggy.ai');
const res = await post(
getServer().PLUGGYAI_SERVER + '/transactions',
{
accountId: acctId,
startDate: since,
},
{
'X-ACTUAL-TOKEN': userToken,
...(fileId ? { 'X-Actual-File-Id': fileId } : {}),
},
60000,
);
if (res.error_code) {
throw BankSyncError(res.error_type, res.error_code);
} else if ('error' in res) {
throw BankSyncError('Connection', res.error);
}
let retVal = {};
const singleRes = res as BankSyncResponse;
retVal = {
transactions: singleRes.transactions.all,
accountBalance: singleRes.balances,
startingBalance: singleRes.startingBalance,
};
logger.log('Response:', retVal);
return retVal;
}
async function downloadAkahuTransactions(
acctId: AccountEntity['id'],
since: string,
) {
const userToken = await asyncStorage.getItem('user-token');
if (!userToken) return;
logger.log('Pulling transactions from Akahu');
const res = await post(
getServer().AKAHU_SERVER + '/transactions',
{
accountId: acctId,
startDate: since,
},
{
'X-ACTUAL-TOKEN': userToken,
},
60000,
);
if (res.error_code) {
throw BankSyncError(res.error_type, res.error_code);
} else if ('error' in res) {
throw BankSyncError('Connection', res.error);
}
let retVal = {};
const singleRes = res as BankSyncResponse;
retVal = {
transactions: singleRes.transactions.all,
accountBalance: singleRes.balances,
startingBalance: singleRes.startingBalance,
};
logger.log('Response:', retVal);
return retVal;
}
async function downloadEnableBankingTransactions(
acctId: string,
since: string,
aspspName?: string,
) {
const userToken = await asyncStorage.getItem('user-token');
if (!userToken) return;
logger.log('Pulling transactions from Enable Banking');
const res = await post(
getServer().ENABLEBANKING_SERVER + '/transactions',
{
accountId: acctId,
startDate: since,
aspspName,
},
{
'X-ACTUAL-TOKEN': userToken,
},
60000,
);
if (res.error_code) {
throw BankSyncError(res.error_type, res.error_code);
}
const {
transactions: { all },
balances,
startingBalance,
} = res;
return {
transactions: all,
accountBalance: balances,
startingBalance,
};
}
async function resolvePayee(trans, payeeName, payeesToCreate) {
if (trans.payee == null && payeeName) {
// First check our registry of new payees (to avoid a db access)
// then check the db for existing payees
let payee = payeesToCreate.get(payeeName.toLowerCase());
payee = payee || (await db.getPayeeByName(payeeName));
if (payee != null) {
return payee.id;
} else {
// Otherwise we're going to create a new one
const newPayee = { id: uuidv4(), name: payeeName };
payeesToCreate.set(payeeName.toLowerCase(), newPayee);
return newPayee.id;
}
}
return trans.payee;
}
export const PAYEE_NAME_NORMALIZATIONS = ['original', 'title-case'] as const;
export type PayeeNameNormalization = (typeof PAYEE_NAME_NORMALIZATIONS)[number];
function normalizePayeeName(
payeeName: string,
normalization: PayeeNameNormalization,
): string {
switch (normalization) {
case 'original':
return payeeName;
case 'title-case':
return title(payeeName);
default:
normalization satisfies never;
throw new Error(
`Unknown payee name normalization: ${String(normalization)}`,
);
}
}
async function normalizeTransactions(
transactions,
acctId,
{
payeeNameNormalization = 'title-case',
}: {
payeeNameNormalization?: PayeeNameNormalization;
} = {},
) {
const payeesToCreate = new Map();
const normalized = [];
for (let trans of transactions) {
// Validate the date because we do some stuff with it. The db
// layer does better validation, but this will give nicer errors
if (trans.date == null) {
throw new Error('`date` is required when adding a transaction');
}
// Strip off the irregular properties
const { payee_name: originalPayeeName, subtransactions, ...rest } = trans;
trans = rest;
if (trans.amount != null && !Number.isInteger(trans.amount)) {
throw new TransactionError(
`Amount is invalid, must be an integer: ${trans.amount}`,
);
}
if (subtransactions) {
for (const sub of subtransactions) {
if (sub.amount != null && !Number.isInteger(sub.amount)) {
throw new TransactionError(
`Subtransaction amount is invalid, must be an integer: ${sub.amount}`,
);
}
}
}
let payee_name = originalPayeeName;
if (payee_name) {
const trimmed = payee_name.trim();
if (trimmed === '') {
payee_name = null;
} else {
payee_name = normalizePayeeName(trimmed, payeeNameNormalization);
}
}
trans.imported_payee = trans.imported_payee || payee_name;
if (trans.imported_payee) {
trans.imported_payee = trans.imported_payee.trim();
}
// It's important to resolve both the account and payee early so
// when rules are run, they have the right data. Resolving payees
// also simplifies the payee creation process
trans.account = acctId;
trans.payee = await resolvePayee(trans, payee_name, payeesToCreate);
trans.category = trans.category ?? null;
normalized.push({
payee_name,
subtransactions: subtransactions
? subtransactions.map(t => ({ ...t, account: acctId }))
: null,
trans,
});
}
return { normalized, payeesToCreate };
}
async function normalizeBankSyncTransactions(transactions, acctId) {
const payeesToCreate = new Map();
const [customMappingsRaw, importPending, importNotes] = await Promise.all([
aqlQuery(
q('preferences')
.filter({ id: `custom-sync-mappings-${acctId}` })
.select('value'),
).then(data => data?.data?.[0]?.value),
aqlQuery(
q('preferences')
.filter({ id: `sync-import-pending-${acctId}` })
.select('value'),
).then(data => String(data?.data?.[0]?.value ?? 'true') === 'true'),
aqlQuery(
q('preferences')
.filter({ id: `sync-import-notes-${acctId}` })
.select('value'),
).then(data => String(data?.data?.[0]?.value ?? 'true') === 'true'),
]);
const mappings = customMappingsRaw
? mappingsFromString(customMappingsRaw)
: defaultMappings;
const categoryIds = new Set((await db.getCategories()).map(c => c.id));
const normalized = [];
for (const trans of transactions) {
trans.cleared = Boolean(trans.booked);
if (!importPending && !trans.cleared) continue;
if (!trans.amount) {
trans.amount = trans.transactionAmount.amount;
}
const mapping = mappings.get(trans.amount <= 0 ? 'payment' : 'deposit');
const date = trans[mapping.get('date')] ?? trans.date;
const payeeName = trans[mapping.get('payee')] ?? trans.payeeName;
const notes = trans[mapping.get('notes')];
// Validate the date because we do some stuff with it. The db
// layer does better validation, but this will give nicer errors
if (date == null) {
throw new Error('`date` is required when adding a transaction');
}
if (payeeName == null) {
throw new Error('`payeeName` is required when adding a transaction');
}
trans.imported_payee = trans.imported_payee || payeeName;
if (trans.imported_payee) {
trans.imported_payee = trans.imported_payee.trim();
}
let imported_id = trans.transactionId;
if (trans.cleared && !trans.transactionId && trans.internalTransactionId) {
imported_id = `${trans.account}-${trans.internalTransactionId}`;
}
// It's important to resolve both the account and payee early so
// when rules are run, they have the right data. Resolving payees
// also simplifies the payee creation process
trans.account = acctId;
trans.payee = await resolvePayee(trans, payeeName, payeesToCreate);
normalized.push({
payee_name: payeeName,
trans: {
amount: amountToInteger(trans.amount),
payee: trans.payee,
account: trans.account,
date,
notes: importNotes && notes ? notes.trim().replace(/#/g, '##') : null,
category: categoryIds.has(trans.category) ? trans.category : null,
imported_id,
imported_payee: trans.imported_payee,
cleared: trans.cleared,
raw_synced_data: JSON.stringify(trans),
},
});
}
return { normalized, payeesToCreate };
}
async function createNewPayees(payeesToCreate, addsAndUpdates) {
const usedPayeeIds = new Set(addsAndUpdates.map(t => t.payee));
await batchMessages(async () => {
for (const payee of payeesToCreate.values()) {
// Only create the payee if it ended up being used
if (usedPayeeIds.has(payee.id)) {
await db.insertPayee(payee);
}
}
});
}
export type MatchTransactionsOptions = {
isBankSyncAccount?: boolean;
strictIdChecking?: boolean;
reimportDeleted?: boolean;
payeeNameNormalization?: PayeeNameNormalization;
};
export type ReconcileTransactionsOptions = MatchTransactionsOptions & {
isPreview?: boolean;
defaultCleared?: boolean;
updateDates?: boolean;
};
export type ReconcileTransactionsResult = {
added: string[];
updated: string[];
updatedPreview: Array<{
transaction: TransactionEntity;
existing?: TransactionEntity;
ignored?: boolean;
tombstone?: boolean;
}>;
};
export async function reconcileTransactions(
acctId,
transactions,
{
isBankSyncAccount = false,
strictIdChecking = true,
isPreview = false,
defaultCleared = true,
updateDates = false,
reimportDeleted,
payeeNameNormalization,
}: ReconcileTransactionsOptions = {},
): Promise<ReconcileTransactionsResult> {
logger.log('Performing transaction reconciliation');
const updated = [];
const added = [];
const updatedPreview = [];
const existingPayeeMap = new Map<string, string>();
const {
payeesToCreate,
transactionsStep1,
transactionsStep2,
transactionsStep3,
} = await matchTransactions(acctId, transactions, {
isBankSyncAccount,
strictIdChecking,
reimportDeleted,
payeeNameNormalization,
});
// Finally, generate & commit the changes
for (const { trans, subtransactions, match } of transactionsStep3) {
// A skipped transaction still takes part in the matching above, so the
// other transactions match the same way as in the import preview. It is
// not imported or merged.
if (trans.skipTransaction) {
continue;
}
if (match && !trans.forceAddTransaction) {
// Skip updating already reconciled (locked) transactions
if (match.reconciled) {
updatedPreview.push({ transaction: trans, ignored: true });
continue;
}
// TODO: change the above sql query to use aql
const existing = {
...match,
cleared: match.cleared === 1,
date: db.fromDateRepr(match.date),
};
// Update the transaction
const updates = {
imported_id: trans.imported_id || null,
payee: existing.payee || trans.payee || null,
category: existing.category || trans.category || null,
imported_payee: trans.imported_payee || null,
notes: existing.notes || trans.notes || null,
cleared: existing.cleared || trans.cleared || false,
raw_synced_data:
existing.raw_synced_data ?? trans.raw_synced_data ?? null,
};
if (updateDates && trans.date) {
updates['date'] = trans.date;
}
const fieldsToMarkUpdated = Object.keys(updates).filter(k => {
// do not mark raw_synced_data if it's gone from falsy to falsy
if (!existing.raw_synced_data && !trans.raw_synced_data) {
return k !== 'raw_synced_data';
}
return true;
});
if (hasFieldsChanged(existing, updates, fieldsToMarkUpdated)) {
updated.push({ id: existing.id, ...updates });
if (!existingPayeeMap.has(existing.payee)) {
const payee = await db.getPayee(existing.payee);
existingPayeeMap.set(existing.payee, payee?.name);
}
existing.payee_name = existingPayeeMap.get(existing.payee);
existing.amount = integerToAmount(existing.amount);
updatedPreview.push({ transaction: trans, existing });
} else {
updatedPreview.push({ transaction: trans, ignored: true });
}
const clearedUpdated = existing.cleared !== updates.cleared;
const dateUpdated =
updateDates && trans.date && existing.date !== trans.date;
if (existing.is_parent && (clearedUpdated || dateUpdated)) {
const children = await db.all<Pick<db.DbViewTransaction, 'id'>>(
'SELECT id FROM v_transactions WHERE parent_id = ?',
[existing.id],
);
const childUpdates = {};
if (clearedUpdated) {
childUpdates['cleared'] = updates.cleared;
}
if (dateUpdated) {
childUpdates['date'] = trans.date;
}
for (const child of children) {
updated.push({ id: child.id, ...childUpdates });
}
}
} else if (trans.tombstone) {
if (isPreview) {
updatedPreview.push({
transaction: trans,
existing: false,
tombstone: true,
});
}
} else {
// Insert a new transaction
const { forceAddTransaction: _forceAddTransaction, ...newTrans } = trans;
const finalTransaction = {
...newTrans,
id: uuidv4(),
category: trans.category || null,
cleared: trans.cleared ?? defaultCleared,
};
if (subtransactions && subtransactions.length > 0) {
added.push(...makeSplitTransaction(finalTransaction, subtransactions));
} else {
added.push(finalTransaction);
}
}
}
// Maintain the sort order of the server
const now = Date.now();
added.forEach((t, index) => {
t.sort_order ??= now - index * TRANSACTION_SORT_INCREMENT;
});
if (!isPreview) {
await createNewPayees(payeesToCreate, [...added, ...updated]);
await batchUpdateTransactions({ added, updated });
}
logger.log('Debug data for the operations:', {
transactionsStep1,
transactionsStep2,
transactionsStep3,
added,
updated,
updatedPreview,
});
return {
added: added.map(trans => trans.id),
updated: updated.map(trans => trans.id),
updatedPreview,
};
}
// Ranks fuzzy-match candidates by distance from transaction date, nearest first.
// On a same-distance tie, a candidate that already carries its own imported_id
// (from a previous, unrelated sync) ranks after one that doesn't. Without this
// tie-break, picking the already-imported one would lead the fuzzy match merge
// to silently overwrite imported_id/payee/notes with this transaction's data.
export function compareFuzzyMatchCandidates(
transactionDate: string,
a: Pick<db.DbViewTransaction, 'date' | 'imported_id'>,
b: Pick<db.DbViewTransaction, 'date' | 'imported_id'>,
): number {
const aDistance = Math.abs(
dateFns.differenceInMilliseconds(
dateFns.parseISO(transactionDate),
dateFns.parseISO(db.fromDateRepr(a.date)),
),
);
const bDistance = Math.abs(
dateFns.differenceInMilliseconds(
dateFns.parseISO(transactionDate),
dateFns.parseISO(db.fromDateRepr(b.date)),
),
);
const aHasImportedId = Number(a.imported_id != null);
const bHasImportedId = Number(b.imported_id != null);
return aDistance - bDistance || aHasImportedId - bHasImportedId;
}
export async function matchTransactions(
acctId,
transactions,
{
isBankSyncAccount = false,
strictIdChecking = true,
reimportDeleted: reimportDeletedOverride,
payeeNameNormalization,
}: MatchTransactionsOptions = {},
) {
logger.log('Performing transaction reconciliation matching');
const reimportDeleted =
reimportDeletedOverride !== undefined
? reimportDeletedOverride
: await aqlQuery(
q('preferences')
.filter({ id: `sync-reimport-deleted-${acctId}` })
.select('value'),
).then(data => String(data?.data?.[0]?.value ?? 'true') === 'true');
const hasMatched = new Set();
const exactMatchedParentIds = new Set<db.DbViewTransaction['id']>();
const { normalized, payeesToCreate } = isBankSyncAccount
? await normalizeBankSyncTransactions(transactions, acctId)
: await normalizeTransactions(transactions, acctId, {
payeeNameNormalization,
});
// The first pass runs the rules, and preps data for fuzzy matching
const accounts: db.DbAccount[] = await db.getAccounts();
const accountsMap = new Map(accounts.map(account => [account.id, account]));
const transactionsStep1 = [];
for (const {
payee_name,
trans: originalTrans,
subtransactions,
} of normalized) {
// Run the rules
const trans = await runRules(originalTrans, accountsMap);
let match = null;
let fuzzyDataset = null;
// First, match with an existing transaction's imported_id. This
// is the highest fidelity match and should always be attempted
// first.
if (trans.imported_id) {
const table = reimportDeleted
? 'v_transactions'
: 'v_transactions_internal';
match = await db.first<db.DbTransaction>(
`SELECT * FROM ${table} WHERE imported_id = ? AND account = ?`,
[trans.imported_id, acctId],
);
if (match) {
hasMatched.add(match.id);
if (isBankSyncAccount && match.is_parent) {
exactMatchedParentIds.add(match.id);
}
}
}
// If it didn't match, query data needed for fuzzy matching
if (!match) {
// Fuzzy matching looks 7 days ahead and 7 days back. This
// needs to select all fields that need to be read from the
// matched transaction. See the final pass below for the needed
// fields.
const sevenDaysBefore = db.toDateRepr(monthUtils.subDays(trans.date, 7));
const sevenDaysAfter = db.toDateRepr(monthUtils.addDays(trans.date, 7));
// strictIdChecking has the added behaviour of only matching on transactions with no import ID
// if the transaction being imported has an import ID.
if (strictIdChecking) {
fuzzyDataset = await db.all<
Pick<
db.DbViewTransaction,
| 'id'
| 'is_parent'
| 'parent_id'
| 'date'
| 'imported_id'
| 'payee'
| 'imported_payee'
| 'category'
| 'notes'
| 'reconciled'
| 'cleared'
| 'amount'
>
>(
`SELECT id, is_parent, parent_id, date, imported_id, payee, imported_payee, category, notes, reconciled, cleared, amount
FROM v_transactions
WHERE
-- If both ids are set, and we didn't match earlier then skip dedup
(imported_id IS NULL OR ? IS NULL)
AND date >= ? AND date <= ? AND amount = ?
AND account = ?`,
[
trans.imported_id || null,
sevenDaysBefore,
sevenDaysAfter,
trans.amount || 0,
acctId,
],
);
} else {
fuzzyDataset = await db.all<
Pick<
db.DbViewTransaction,
| 'id'
| 'is_parent'
| 'parent_id'
| 'date'
| 'imported_id'
| 'payee'
| 'imported_payee'
| 'category'
| 'notes'
| 'reconciled'
| 'cleared'
| 'amount'
>
>(
`SELECT id, is_parent, parent_id, date, imported_id, payee, imported_payee, category, notes, reconciled, cleared, amount
FROM v_transactions
WHERE date >= ? AND date <= ? AND amount = ? AND account = ?`,
[sevenDaysBefore, sevenDaysAfter, trans.amount || 0, acctId],
);
}
// Sort the matched transactions according to the distance from the original
// transactions date. i.e. if the original transaction is in 21-02-2024 and
// the matched transactions are: 20-02-2024, 21-02-2024, 29-02-2024 then
// the resulting data-set should be: 21-02-2024, 20-02-2024, 29-02-2024.
fuzzyDataset = fuzzyDataset.sort((a, b) =>
compareFuzzyMatchCandidates(trans.date, a, b),
);
}
transactionsStep1.push({
payee_name,
trans,
subtransactions: trans.subtransactions || subtransactions,
match,
fuzzyDataset,
});
}
// Next, do the fuzzy matching. This first pass matches based on the
// payee id. We do this in multiple passes so that higher fidelity
// matching always happens first, i.e. a transaction should match
// match with low fidelity if a later transaction is going to match
// the same one with high fidelity.
const transactionsStep2 = transactionsStep1.map(data => {
if (!data.match && data.fuzzyDataset) {
// Try to find one where the payees match.
const match = data.fuzzyDataset.find(
row =>
!hasMatched.has(row.id) &&
!exactMatchedParentIds.has(row.parent_id) &&
data.trans.payee === row.payee,
);
if (match) {
hasMatched.add(match.id);
return { ...data, match };
}
}
return data;
});
// The final fuzzy matching pass. This is the lowest fidelity
// matching: it just find the first transaction that hasn't been
// matched yet. Remember the dataset only contains transactions
// around the same date with the same amount.
const transactionsStep3 = transactionsStep2.map(data => {
if (!data.match && data.fuzzyDataset) {
const match = data.fuzzyDataset.find(
row =>
!hasMatched.has(row.id) && !exactMatchedParentIds.has(row.parent_id),
);
if (match) {
hasMatched.add(match.id);
return { ...data, match };
}
}
return data;
});
return {
payeesToCreate,
transactionsStep1,
transactionsStep2,
transactionsStep3,
};
}
// This is similar to `reconcileTransactions` except much simpler: it
// does not try to match any transactions. It just adds them
export async function addTransactions(
acctId,
transactions,
{ runTransfers = true, learnCategories = false } = {},
) {
const added = [];
const { normalized, payeesToCreate } = await normalizeTransactions(
transactions,
acctId,
{ payeeNameNormalization: 'original' },
);
const accounts: db.DbAccount[] = await db.getAccounts();
const accountsMap = new Map(accounts.map(account => [account.id, account]));
for (const { trans: originalTrans, subtransactions } of normalized) {
// Run the rules
const trans = await runRules(originalTrans, accountsMap);
const finalTransaction = {
id: uuidv4(),
...trans,
account: acctId,
cleared: trans.cleared != null ? trans.cleared : true,
};
// Add split transactions if they are given
const updatedSubtransactions =
finalTransaction.subtransactions || subtransactions;
if (updatedSubtransactions && updatedSubtransactions.length > 0) {
added.push(
...makeSplitTransaction(finalTransaction, updatedSubtransactions),
);
} else {
added.push(finalTransaction);
}
}
await createNewPayees(payeesToCreate, added);
// Assign decreasing sort_order values to preserve import file order.
// Transactions are displayed in sort_order DESC order, so first transaction
// in the file should have the highest sort_order.
const now = Date.now();
added.forEach((t, index) => {
t.sort_order ??= now - index * TRANSACTION_SORT_INCREMENT;
});
let newTransactions;
if (runTransfers || learnCategories) {
const res = await batchUpdateTransactions({
added,
learnCategories,
runTransfers,
});
newTransactions = res.added.map(t => t.id);
} else {
await batchMessages(async () => {
newTransactions = await Promise.all(
added.map(async trans => db.insertTransaction(trans)),
);
});
}
return newTransactions;
}
async function processBankSyncDownload(
download,
id,
acctRow,
initialSync = false,
customStartingBalance?: number,
customStartingDate?: string,
) {
// If syncing an account from sync source it must not use strictIdChecking. This allows
// the fuzzy search to match transactions where the import IDs are different. It is a known quirk
// that account sync sources can give two different transaction IDs even though it's the same transaction.
const useStrictIdChecking = !acctRow.account_sync_source;
const importTransactions = await aqlQuery(
q('preferences')
.filter({ id: `sync-import-transactions-${id}` })
.select('value'),
).then(data => String(data?.data?.[0]?.value ?? 'true') === 'true');
const updateDates = await aqlQuery(
q('preferences')
.filter({ id: `sync-update-dates-${id}` })
.select('value'),
).then(data => String(data?.data?.[0]?.value ?? 'false') === 'true');
/** Starting balance is actually the current balance of the account. */
const {
transactions: originalTransactions,
startingBalance: currentBalance,
} = download;
if (initialSync) {
const { transactions } = download;
let balanceToUse = currentBalance;
// Use custom starting balance if provided, otherwise calculate it
if (customStartingBalance !== undefined) {
balanceToUse = customStartingBalance;
} else if (acctRow.account_sync_source === 'simpleFin') {
const previousBalance = transactions.reduce((total, trans) => {
return (
total - parseInt(trans.transactionAmount.amount.replace('.', ''))
);
}, currentBalance);
balanceToUse = previousBalance;
} else if (acctRow.account_sync_source === 'pluggyai') {
const currentBalance = download.startingBalance;
const previousBalance = transactions.reduce(
(total, trans) => total - trans.transactionAmount.amount * 100,
currentBalance,
);
balanceToUse = Math.round(previousBalance);
} else if (acctRow.account_sync_source === 'enableBanking') {
const importPending = await aqlQuery(
q('preferences')
.filter({ id: `sync-import-pending-${id}` })
.select('value'),
).then(data => String(data?.data?.[0]?.value ?? 'true') === 'true');
const importable = importPending
? transactions
: transactions.filter(trans => Boolean(trans.booked));
const previousBalance = importable.reduce((total, trans) => {
return total - amountToInteger(trans.transactionAmount.amount);
}, currentBalance);
balanceToUse = previousBalance;
} else if (acctRow.account_sync_source === 'akahu') {
const currentBalance = download.startingBalance;
const previousBalance = transactions.reduce(
(total, trans) =>
total - amountToInteger(trans.transactionAmount.amount),
currentBalance,
);
balanceToUse = Math.round(previousBalance);
}
const oldestTransaction = transactions[transactions.length - 1];
// Use custom starting date if provided, otherwise use oldest transaction date or current day
let startingBalanceDate: string;
if (customStartingDate) {
startingBalanceDate = customStartingDate;
} else if (transactions.length > 0) {
startingBalanceDate = oldestTransaction.date;
} else {
startingBalanceDate = monthUtils.currentDay();
}
const payee = await getStartingBalancePayee();
return runMutator(async () => {
const initialId = await db.insertTransaction({
account: id,
amount: balanceToUse,
category: acctRow.offbudget === 0 ? payee.category : null,
payee: payee.id,
date: startingBalanceDate,
cleared: true,
starting_balance_flag: true,
});
const result = await reconcileTransactions(id, transactions, {
isBankSyncAccount: true,
strictIdChecking: useStrictIdChecking,
updateDates,
});
return {
...result,
added: [initialId, ...result.added],
};
});
}
const transactions = originalTransactions.map(trans => ({
...trans,
account: id,
}));
return runMutator(async () => {
const result = await reconcileTransactions(
id,
importTransactions ? transactions : [],
{
isBankSyncAccount: true,
strictIdChecking: useStrictIdChecking,
updateDates,
},
);
if (currentBalance != null) {
await updateAccountBalance(id, currentBalance);
}
return result;
});
}
export async function syncAccount(
userId: string | undefined,
userKey: string | undefined,
id: string,
acctId: string,
bankId: string,
customStartingDate?: string,
customStartingBalance?: number,
fileId?: string,
) {
const acctRow = await db.select('accounts', id);
const syncStartDate =
customStartingDate ?? (await getAccountSyncStartDate(id));
const oldestTransaction = await getAccountOldestTransaction(id);
const newAccount = oldestTransaction == null;
let download;
if (acctRow.account_sync_source === 'simpleFin') {
download = await downloadSimpleFinTransactions(
acctId,
syncStartDate,
fileId,
);
} else if (acctRow.account_sync_source === 'pluggyai') {
download = await downloadPluggyAiTransactions(
acctId,
syncStartDate,
fileId,
);
} else if (acctRow.account_sync_source === 'akahu') {
download = await downloadAkahuTransactions(acctId, syncStartDate);
} else if (acctRow.account_sync_source === 'goCardless') {
download = await downloadGoCardlessTransactions(
userId,
userKey,
acctId,
bankId,
syncStartDate,
newAccount,
);
} else if (acctRow.account_sync_source === 'enableBanking') {
const bankRow = await db.select('banks', acctRow.bank);
download = await downloadEnableBankingTransactions(
acctId,
syncStartDate,
bankRow?.name,
);
} else {
throw new Error(
`Unrecognized bank-sync provider: ${acctRow.account_sync_source}`,
);
}
return processBankSyncDownload(
download,
id,
acctRow,
newAccount,
customStartingBalance,
customStartingDate,
);
}
export async function simpleFinBatchSync(
accounts: Array<Pick<AccountEntity, 'id' | 'account_id'>>,
fileId?: string,
) {
const startDates = await Promise.all(
accounts.map(async a => getAccountSyncStartDate(a.id)),
);
const res = await downloadSimpleFinTransactions(
accounts.map(a => a.account_id),
startDates,
fileId,
);
if (!res) {
return accounts.map(account => ({
accountId: account.id,
res: {
error_type: 'NO_DATA',
error_code: 'NO_DATA',
},
}));
}
const promises = [];
for (let i = 0; i < accounts.length; i++) {
const account = accounts[i];
const download = res[account.account_id];
if (!download || Object.keys(download).length === 0) {
promises.push(
Promise.resolve({
accountId: account.id,
res: {
error_type: 'ACCOUNT_MISSING',
error_code: 'ACCOUNT_MISSING',
},
}),
);
continue;
}
const acctRow = await db.select('accounts', account.id);
const oldestTransaction = await getAccountOldestTransaction(account.id);
const newAccount = oldestTransaction == null;
if (download.error_code) {
promises.push(
Promise.resolve({
accountId: account.id,
res: download,
}),
);
continue;
}
if (!download.transactions) {
promises.push(
Promise.resolve({
accountId: account.id,
res: {
error_type: 'ACCOUNT_MISSING',
error_code: 'ACCOUNT_MISSING',
},
}),
);
continue;
}
promises.push(
processBankSyncDownload(download, account.id, acctRow, newAccount)
.then(res => ({
accountId: account.id,
res,
}))
.catch(err => ({
accountId: account.id,
res: {
error_type: err?.category || 'INTERNAL_ERROR',
error_code: err?.code || 'INTERNAL_ERROR',
},
})),
);
}
return await Promise.all(promises);
}