A local-first personal finance app
// @ts-strict-ignore
import {
deserializeClock,
makeClientId,
makeClock,
serializeClock,
setClock,
Timestamp,
} from '@actual-app/crdt';
import type { Database, Statement } from '@jlongster/sql.js';
import { LRUCache } from 'lru-cache';
import { v4 as uuidv4 } from 'uuid';
import * as fs from '#platform/server/fs';
import * as sqlite from '#platform/server/sqlite';
import {
convertForInsert,
convertForUpdate,
convertFromSelect,
schema,
schemaConfig,
} from '#server/aql';
import {
accountGroupModel,
accountModel,
categoryGroupModel,
categoryModel,
payeeModel,
toDateRepr,
} from '#server/models';
import { batchMessages, sendMessages } from '#server/sync';
import * as monthUtils from '#shared/months';
import { groupById } from '#shared/util';
import type { TransactionEntity } from '#types/models';
import type { WithRequired } from '#types/util';
import {
shoveSortOrders,
shoveSortOrdersDescending,
SORT_INCREMENT,
TRANSACTION_SORT_INCREMENT,
} from './sort';
import type {
DbAccount,
DbAccountGroup,
DbBank,
DbCategory,
DbCategoryGroup,
DbCategoryMapping,
DbClockMessage,
DbPayee,
DbPayeeMapping,
DbTag,
DbTransaction,
DbViewTransaction,
DbViewTransactionInternalAlive,
} from './types';
export * from './types';
export { fromDateRepr, toDateRepr } from '#server/models';
let dbPath: string | null = null;
let db: Database | null = null;
// Util
export function getDatabasePath() {
return dbPath;
}
export async function openDatabase(id?: string) {
if (db) {
sqlite.closeDatabase(db);
}
dbPath = fs.join(fs.getBudgetDir(id), 'db.sqlite');
setDatabase(await sqlite.openDatabase(dbPath));
// await execQuery('PRAGMA journal_mode = WAL');
}
export function closeDatabase() {
if (db) {
sqlite.closeDatabase(db);
setDatabase(null);
}
}
export function setDatabase(db_: Database) {
db = db_;
resetQueryCache();
}
export function getDatabase() {
return db;
}
export async function loadClock() {
const row = await first<DbClockMessage>('SELECT * FROM messages_clock');
if (row) {
const clock = deserializeClock(row.clock);
setClock(clock);
} else {
// No clock exists yet (first run of the app), so create a default
// one.
const timestamp = new Timestamp(0, 0, makeClientId());
const clock = makeClock(timestamp);
setClock(clock);
runQuery('INSERT INTO messages_clock (id, clock) VALUES (?, ?)', [
1,
serializeClock(clock),
]);
}
}
// Functions
export function runQuery(
sql: string | Statement,
params?: sqlite.SqlParam[],
fetchAll?: false,
): { changes: unknown };
export function runQuery<T>(
sql: string | Statement,
params: sqlite.SqlParam[] | undefined,
fetchAll: true,
): T[];
export function runQuery<T>(
sql: string | Statement,
params: sqlite.SqlParam[],
fetchAll: boolean,
) {
if (fetchAll) {
return sqlite.runQuery<T>(db, sql, params, true);
} else {
return sqlite.runQuery(db, sql, params, false);
}
}
export function execQuery(sql: string) {
sqlite.execQuery(db, sql);
}
// This manages an LRU cache of prepared query statements. This is
// only needed in hot spots when you are running lots of queries.
let _queryCache = new LRUCache<string, Statement>({ max: 100 });
export function cache(sql: string) {
const cached = _queryCache.get(sql);
if (cached) {
return cached;
}
const prepared = sqlite.prepare(db, sql);
_queryCache.set(sql, prepared);
return prepared;
}
function resetQueryCache() {
_queryCache = new LRUCache<string, Statement>({ max: 100 });
}
export function transaction(
fn: () => void,
options?: sqlite.TransactionOptions,
) {
return sqlite.transaction(db, fn, options);
}
export function asyncTransaction(fn: () => Promise<void>) {
return sqlite.asyncTransaction(db, fn);
}
// This function is marked as async because `runQuery` is no longer
// async. We return a promise here until we've audited all the code to
// make sure nothing calls `.then` on this.
export async function all<T>(sql: string, params?: sqlite.SqlParam[]) {
return runQuery<T>(sql, params, true);
}
export async function first<T>(sql, params?: sqlite.SqlParam[]) {
const arr = runQuery<T>(sql, params, true);
return arr.length === 0 ? null : arr[0];
}
// The underlying sql system is now sync, but we can't update `first` yet
// without auditing all uses of it
export function firstSync<T>(sql, params?: sqlite.SqlParam[]) {
const arr = runQuery<T>(sql, params, true);
return arr.length === 0 ? null : arr[0];
}
// This function is marked as async because `runQuery` is no longer
// async. We return a promise here until we've audited all the code to
// make sure nothing calls `.then` on this.
export async function run(sql, params?: sqlite.SqlParam[]) {
return runQuery(sql, params);
}
export async function select(table, id) {
const rows = runQuery('SELECT * FROM ' + table + ' WHERE id = ?', [id], true);
// TODO: In the next phase, we will make this function generic
// and pass the type of the return type to `runQuery`.
// oxlint-disable-next-line typescript/no-explicit-any
return rows[0] as any;
}
export async function update(table, params) {
const fields = Object.keys(params).filter(k => k !== 'id');
if (params.id == null) {
throw new Error('update: id is required');
}
await sendMessages(
fields.map(k => {
return {
dataset: table,
row: params.id,
column: k,
value: params[k],
timestamp: Timestamp.send(),
};
}),
);
}
export async function insertWithUUID(table, row) {
if (!row.id) {
row = { ...row, id: uuidv4() };
}
await insert(table, row);
// We can't rely on the return value of insert because if the
// primary key is text, sqlite returns the internal row id which we
// don't care about. We want to return the generated UUID.
return row.id;
}
export async function insert(table, row) {
const fields = Object.keys(row).filter(k => k !== 'id');
if (row.id == null) {
throw new Error('insert: id is required');
}
await sendMessages(
fields.map(k => {
return {
dataset: table,
row: row.id,
column: k,
value: row[k],
timestamp: Timestamp.send(),
};
}),
);
}
export async function delete_(table, id) {
await sendMessages([
{
dataset: table,
row: id,
column: 'tombstone',
value: 1,
timestamp: Timestamp.send(),
},
]);
}
export async function deleteAll(table: string) {
const rows = await all<{ id: string }>(`
SELECT id FROM ${table} WHERE tombstone = 0
`);
await Promise.all(rows.map(({ id }) => delete_(table, id)));
}
export async function selectWithSchema(table, sql, params) {
const rows = runQuery(sql, params, true);
const convertedRows = rows
.map(row => convertFromSelect(schema, schemaConfig, table, row))
.filter(Boolean);
// TODO: Make convertFromSelect generic so we don't need this cast
// oxlint-disable-next-line typescript/no-explicit-any
return convertedRows as any[];
}
export async function selectFirstWithSchema(table, sql, params) {
const rows = await selectWithSchema(table, sql, params);
return rows.length > 0 ? rows[0] : null;
}
export function insertWithSchema(table, row) {
// Even though `insertWithUUID` does this, we need to do it here so
// the schema validation passes
if (!row.id) {
row = { ...row, id: uuidv4() };
}
return insertWithUUID(
table,
convertForInsert(schema, schemaConfig, table, row),
);
}
export function updateWithSchema(table, fields) {
return update(table, convertForUpdate(schema, schemaConfig, table, fields));
}
// Data-specific functions. Ideally this would be split up into
// different files
export async function getCategories(
ids?: Array<DbCategory['id']>,
): Promise<DbCategory[]> {
const whereIn = ids ? `c.id IN (${toSqlQueryParameters(ids)}) AND` : '';
const query = `SELECT c.* FROM categories c WHERE ${whereIn} c.tombstone = 0 ORDER BY c.sort_order, c.id`;
return ids
? await all<DbCategory>(query, [...ids])
: await all<DbCategory>(query);
}
export async function getCategoriesGrouped(
ids?: Array<DbCategoryGroup['id']>,
): Promise<
Array<
DbCategoryGroup & {
categories: DbCategory[];
}
>
> {
const categoryGroupWhereIn = ids
? `cg.id IN (${toSqlQueryParameters(ids)}) AND`
: '';
const categoryGroupQuery = `SELECT cg.* FROM category_groups cg WHERE ${categoryGroupWhereIn} cg.tombstone = 0
ORDER BY cg.is_income, cg.sort_order, cg.id`;
const categoryWhereIn = ids
? `c.cat_group IN (${toSqlQueryParameters(ids)}) AND`
: '';
const categoryQuery = `SELECT c.* FROM categories c WHERE ${categoryWhereIn} c.tombstone = 0
ORDER BY c.sort_order, c.id`;
const groups = ids
? await all<DbCategoryGroup>(categoryGroupQuery, [...ids])
: await all<DbCategoryGroup>(categoryGroupQuery);
const categories = ids
? await all<DbCategory>(categoryQuery, [...ids])
: await all<DbCategory>(categoryQuery);
return groups.map(group => ({
...group,
categories: categories.filter(c => c.cat_group === group.id),
}));
}
export async function insertCategoryGroup(
group: WithRequired<Partial<DbCategoryGroup>, 'name'>,
): Promise<DbCategoryGroup['id']> {
// Don't allow duplicate group
const existingGroup = await first<
Pick<DbCategoryGroup, 'id' | 'name' | 'hidden'>
>(
`SELECT id, name, hidden FROM category_groups WHERE UPPER(name) = ? and tombstone = 0 LIMIT 1`,
[group.name.toUpperCase()],
);
if (existingGroup) {
throw new Error(
`A ${
existingGroup.hidden ? 'hidden ' : ''
}'${existingGroup.name}' category group already exists.`,
);
}
const lastGroup = await first<Pick<DbCategoryGroup, 'sort_order'>>(`
SELECT sort_order FROM category_groups WHERE tombstone = 0 ORDER BY sort_order DESC, id DESC LIMIT 1
`);
const sort_order = (lastGroup ? lastGroup.sort_order : 0) + SORT_INCREMENT;
group = {
...categoryGroupModel.validate(group),
sort_order,
};
const id: DbCategoryGroup['id'] = await insertWithUUID(
'category_groups',
group,
);
return id;
}
export async function updateCategoryGroup(
group: WithRequired<Partial<DbCategoryGroup>, 'id' | 'is_income'>,
) {
// An update that does not touch the name cannot introduce a duplicate, and
// `name` is genuinely absent on one: api/category-group-update forwards the
// caller's Partial, and update() writes only the keys it is given (#8857).
if (group.name !== undefined) {
const existingGroup = await first<
Pick<DbCategoryGroup, 'id' | 'name' | 'hidden'>
>(
`SELECT id, name, hidden FROM category_groups WHERE UPPER(name) = ? AND id != ? AND tombstone = 0 LIMIT 1`,
[group.name.toUpperCase(), group.id],
);
if (existingGroup) {
throw new Error(
`A ${
existingGroup.hidden ? 'hidden ' : ''
}'${existingGroup.name}' category group already exists.`,
);
}
}
group = categoryGroupModel.validate(group, { update: true });
return update('category_groups', group);
}
export async function moveCategoryGroup(
id: DbCategoryGroup['id'],
targetId?: DbCategoryGroup['id'] | null,
) {
const groups = await all<Pick<DbCategoryGroup, 'id' | 'sort_order'>>(
`SELECT id, sort_order FROM category_groups WHERE tombstone = 0 ORDER BY sort_order, id`,
);
const { updates, sort_order } = shoveSortOrders(groups, targetId);
for (const info of updates) {
await update('category_groups', info);
}
await update('category_groups', { id, sort_order });
}
export async function deleteCategoryGroup(
group: Pick<DbCategoryGroup, 'id'>,
transferId?: DbCategory['id'] | null,
) {
const categories = await all<DbCategory>(
'SELECT * FROM categories WHERE cat_group = ?',
[group.id],
);
// Delete all the categories within a group
await Promise.all(categories.map(cat => deleteCategory(cat, transferId)));
await delete_('category_groups', group.id);
}
export async function insertCategory(
category: WithRequired<Partial<DbCategory>, 'name' | 'cat_group'>,
{ atEnd }: { atEnd?: boolean | undefined } = { atEnd: undefined },
): Promise<DbCategory['id']> {
let sort_order;
let id_: DbCategory['id'];
await batchMessages(async () => {
// Dont allow duplicated names in groups
const existingCatInGroup = await first<Pick<DbCategory, 'id'>>(
`SELECT id FROM categories WHERE cat_group = ? and UPPER(name) = ? and tombstone = 0 LIMIT 1`,
[category.cat_group, category.name.toUpperCase()],
);
if (existingCatInGroup) {
throw new Error(
`Category '${category.name}' already exists in group '${category.cat_group}'`,
);
}
if (atEnd) {
const lastCat = await first<Pick<DbCategory, 'sort_order'>>(`
SELECT sort_order FROM categories WHERE tombstone = 0 ORDER BY sort_order DESC, id DESC LIMIT 1
`);
sort_order = (lastCat ? lastCat.sort_order : 0) + SORT_INCREMENT;
} else {
// Unfortunately since we insert at the beginning, we need to shove
// the sort orders to make sure there's room for it
const categories = await all<Pick<DbCategory, 'id' | 'sort_order'>>(
`SELECT id, sort_order FROM categories WHERE cat_group = ? AND tombstone = 0 ORDER BY sort_order, id`,
[category.cat_group],
);
const { updates, sort_order: order } = shoveSortOrders(
categories,
categories.length > 0 ? categories[0].id : null,
);
for (const info of updates) {
await update('categories', info);
}
sort_order = order;
}
category = {
...categoryModel.validate(category),
sort_order,
};
const id = await insertWithUUID('categories', category);
// Create an entry in the mapping table that points it to itself
await insert('category_mapping', { id, transferId: id });
id_ = id;
});
return id_;
}
export function updateCategory(
category: WithRequired<
Partial<DbCategory>,
'name' | 'is_income' | 'cat_group'
>,
) {
category = categoryModel.validate(category, { update: true });
// Change from cat_group to group because category AQL schema named it group.
// const { cat_group: group, ...rest } = category;
return update('categories', category);
}
export async function moveCategory(
id: DbCategory['id'],
groupId: DbCategoryGroup['id'],
targetId?: DbCategory['id'] | null,
) {
if (!groupId) {
throw new Error('moveCategory: groupId is required');
}
const categories = await all<Pick<DbCategory, 'id' | 'sort_order'>>(
`SELECT id, sort_order FROM categories WHERE cat_group = ? AND tombstone = 0 ORDER BY sort_order, id`,
[groupId],
);
const { updates, sort_order } = shoveSortOrders(categories, targetId);
for (const info of updates) {
await update('categories', info);
}
await update('categories', { id, sort_order, cat_group: groupId });
}
export async function deleteCategory(
category: Pick<DbCategory, 'id'>,
transferId?: DbCategory['id'] | null,
) {
if (transferId) {
// We need to update all the deleted categories that currently
// point to the one we're about to delete so they all are
// "forwarded" to the new transferred category.
const existingTransfers = await all<DbCategoryMapping>(
'SELECT * FROM category_mapping WHERE transferId = ?',
[category.id],
);
for (const mapping of existingTransfers) {
await update('category_mapping', {
id: mapping.id,
transferId,
});
}
// Finally, map the category we're about to delete to the new one
await update('category_mapping', { id: category.id, transferId });
}
return delete_('categories', category.id);
}
export async function getPayee(id: DbPayee['id']) {
return first<DbPayee>(`SELECT * FROM payees WHERE id = ?`, [id]);
}
export async function getAccount(id: DbAccount['id']) {
return first<DbAccount>(`SELECT * FROM accounts WHERE id = ?`, [id]);
}
export async function getCategory(id: DbCategory['id']) {
return first<DbCategory>(`SELECT * FROM categories WHERE id = ?`, [id]);
}
export async function insertPayee(
payee: WithRequired<Partial<DbPayee>, 'name'>,
) {
payee = payeeModel.validate(payee);
let id: DbPayee['id'];
await batchMessages(async () => {
id = await insertWithUUID('payees', payee);
await insert('payee_mapping', { id, targetId: id });
});
return id;
}
export async function deletePayee(payee: Pick<DbPayee, 'id'>) {
const { transfer_acct } = await first<DbPayee>(
'SELECT * FROM payees WHERE id = ?',
[payee.id],
);
if (transfer_acct) {
// You should never be able to delete transfer payees
return;
}
// let mappings = await all('SELECT id FROM payee_mapping WHERE targetId = ?', [
// payee.id
// ]);
// await Promise.all(
// mappings.map(m => update('payee_mapping', { id: m.id, targetId: null }))
// );
return delete_('payees', payee.id);
}
export async function deleteTransferPayee(payee: Pick<DbPayee, 'id'>) {
// This allows deleting transfer payees
return delete_('payees', payee.id);
}
export function updatePayee(payee: WithRequired<Partial<DbPayee>, 'id'>) {
payee = payeeModel.validate(payee, { update: true });
return update('payees', payee);
}
export async function mergePayees(
target: DbPayee['id'],
ids: Array<DbPayee['id']>,
) {
// Load in payees so we can check some stuff
const dbPayees: DbPayee[] = await all<DbPayee>('SELECT * FROM payees');
const payees = groupById(dbPayees);
// Filter out any transfer payees
if (payees[target].transfer_acct != null) {
return;
}
ids = ids.filter(id => payees[id].transfer_acct == null);
await batchMessages(async () => {
await Promise.all(
ids.map(async id => {
const mappings = await all<DbPayeeMapping>(
'SELECT id FROM payee_mapping WHERE targetId = ?',
[id],
);
await Promise.all(
mappings.map(m =>
update('payee_mapping', { id: m.id, targetId: target }),
),
);
}),
);
await Promise.all(
ids.map(id =>
Promise.all([
update('payee_mapping', { id, targetId: target }),
delete_('payees', id),
]),
),
);
});
}
export function getPayees() {
return all<DbPayee & { name: DbAccount['name'] | DbPayee['name'] }>(`
SELECT p.*, COALESCE(a.name, p.name) AS name FROM payees p
LEFT JOIN accounts a ON (p.transfer_acct = a.id AND a.tombstone = 0)
WHERE p.tombstone = 0 AND (p.transfer_acct IS NULL OR a.id IS NOT NULL)
ORDER BY p.transfer_acct IS NULL DESC, p.name COLLATE NOCASE, a.offbudget, a.sort_order
`);
}
export function getCommonPayees() {
const twelveWeeksAgo = toDateRepr(
monthUtils.subWeeks(monthUtils.currentDate(), 12),
);
const limit = 10;
return all<
DbPayee & {
common: true;
transfer_acct: null;
c: number;
latest: DbViewTransactionInternalAlive['date'];
}
>(`
SELECT p.id as id, p.name as name, p.favorite as favorite,
p.category as category, TRUE as common, NULL as transfer_acct,
count(*) as c,
max(t.date) as latest
FROM payees p
LEFT JOIN v_transactions_internal_alive t on t.payee == p.id
WHERE LENGTH(p.name) > 0
AND p.tombstone = 0
AND t.date > ${twelveWeeksAgo}
GROUP BY p.id
ORDER BY c DESC ,p.transfer_acct IS NULL DESC, p.name
COLLATE NOCASE
LIMIT ${limit}
`);
}
const orphanedPayeesQuery = `
SELECT p.id
FROM payees p
LEFT JOIN payee_mapping pm ON pm.id = p.id
LEFT JOIN v_transactions_internal_alive t ON t.payee = pm.targetId
WHERE p.tombstone = 0
AND p.transfer_acct IS NULL
AND t.id IS NULL
AND NOT EXISTS (
SELECT 1
FROM rules r,
json_each(r.conditions) as cond
WHERE r.tombstone = 0
AND json_extract(cond.value, '$.field') = 'description'
AND json_extract(cond.value, '$.value') = pm.targetId
);
`;
export function syncGetOrphanedPayees() {
return all<Pick<DbPayee, 'id'>>(orphanedPayeesQuery);
}
export async function getOrphanedPayees() {
const rows = await all<Pick<DbPayee, 'id'>>(orphanedPayeesQuery);
return rows.map(row => row.id);
}
export async function getPayeeByName(name: DbPayee['name']) {
return first<DbPayee>(
`SELECT * FROM payees WHERE UNICODE_LOWER(name) = ? AND tombstone = 0`,
[name.toLowerCase()],
);
}
export function getAccounts() {
return all<
DbAccount & {
bankName: DbBank['name'];
bankId: DbBank['id'];
}
>(
`SELECT a.*, b.name as bankName, b.id as bankId FROM accounts a
LEFT JOIN banks b ON a.bank = b.id
WHERE a.tombstone = 0
ORDER BY sort_order, name`,
);
}
export async function insertAccount(account) {
const accounts = await all<DbAccount>(
'SELECT * FROM accounts WHERE offbudget = ? ORDER BY sort_order, name',
[account.offbudget ? 1 : 0],
);
// Don't pass a target in, it will default to appending at the end
const { sort_order } = shoveSortOrders(accounts);
account = accountModel.validate({ ...account, sort_order });
return insertWithUUID('accounts', account);
}
export function updateAccount(account) {
account = accountModel.validate(account, { update: true });
return update('accounts', account);
}
export function deleteAccount(account) {
return delete_('accounts', account.id);
}
export async function moveAccount(
id: DbAccount['id'],
targetId: DbAccount['id'] | null,
accountGroupId?: DbAccountGroup['id'] | null,
) {
const account = await first<DbAccount>(
'SELECT * FROM accounts WHERE id = ?',
[id],
);
let accounts;
if (account.closed) {
accounts = await all<Pick<DbAccount, 'id' | 'sort_order'>>(
`SELECT id, sort_order FROM accounts WHERE closed = 1 ORDER BY sort_order, name`,
);
} else {
accounts = await all<Pick<DbAccount, 'id' | 'sort_order'>>(
`SELECT id, sort_order FROM accounts WHERE tombstone = 0 AND offbudget = ? ORDER BY sort_order, name`,
[account.offbudget ? 1 : 0],
);
}
const { updates, sort_order } = shoveSortOrders(accounts, targetId);
await batchMessages(async () => {
for (const info of updates) {
void update('accounts', info);
}
void update('accounts', {
id,
sort_order,
...(accountGroupId !== undefined && {
account_group_id: accountGroupId,
}),
});
});
}
export function getAccountGroups() {
return all<DbAccountGroup>(
`SELECT * FROM account_groups WHERE tombstone = 0 ORDER BY sort_order, id`,
);
}
export async function insertAccountGroup(
group: WithRequired<Partial<DbAccountGroup>, 'name'>,
): Promise<DbAccountGroup['id']> {
// Don't allow duplicate group
const existingGroup = await first<Pick<DbAccountGroup, 'id' | 'name'>>(
`SELECT id, name FROM account_groups WHERE UPPER(name) = ? AND tombstone = 0 LIMIT 1`,
[group.name.toUpperCase()],
);
if (existingGroup) {
throw new Error(`An '${existingGroup.name}' account group already exists.`);
}
const lastGroup = await first<Pick<DbAccountGroup, 'sort_order'>>(`
SELECT sort_order FROM account_groups WHERE tombstone = 0 ORDER BY sort_order DESC, id DESC LIMIT 1
`);
const sort_order = (lastGroup ? lastGroup.sort_order : 0) + SORT_INCREMENT;
group = {
...accountGroupModel.validate(group),
sort_order,
};
const id: DbAccountGroup['id'] = await insertWithUUID(
'account_groups',
group,
);
return id;
}
export async function updateAccountGroup(
group: WithRequired<Partial<DbAccountGroup>, 'id' | 'name'>,
) {
const existingGroup = await first<Pick<DbAccountGroup, 'id' | 'name'>>(
`SELECT id, name FROM account_groups WHERE UPPER(name) = ? AND id != ? AND tombstone = 0 LIMIT 1`,
[group.name.toUpperCase(), group.id],
);
if (existingGroup) {
throw new Error(`An '${existingGroup.name}' account group already exists.`);
}
group = accountGroupModel.validate(group, { update: true });
return update('account_groups', group);
}
export async function moveAccountGroup(
id: DbAccountGroup['id'],
targetId?: DbAccountGroup['id'] | null,
) {
const groups = await all<Pick<DbAccountGroup, 'id' | 'sort_order'>>(
`SELECT id, sort_order FROM account_groups WHERE tombstone = 0 ORDER BY sort_order, id`,
);
const { updates, sort_order } = shoveSortOrders(groups, targetId);
await batchMessages(async () => {
for (const info of updates) {
await update('account_groups', info);
}
await update('account_groups', { id, sort_order });
});
}
export async function deleteAccountGroup(group: Pick<DbAccountGroup, 'id'>) {
const accounts = await all<Pick<DbAccount, 'id'>>(
`SELECT id FROM accounts WHERE account_group_id = ? AND tombstone = 0`,
[group.id],
);
// Clearing member refs is best-effort under CRDT sync: a concurrent
// assignment on another device can win against these nulls, so consumers
// must always treat a ref to a missing/tombstoned group as ungrouped.
await batchMessages(async () => {
for (const account of accounts) {
await update('accounts', { id: account.id, account_group_id: null });
}
await delete_('account_groups', group.id);
});
}
export async function getTransaction(id: DbViewTransaction['id']) {
const rows = await selectWithSchema(
'transactions',
'SELECT * FROM v_transactions WHERE id = ?',
[id],
);
return rows[0];
}
export async function getTransactions(accountId: DbTransaction['acct']) {
if (arguments.length > 1) {
throw new Error(
'`getTransactions` was given a second argument, it now only takes a single argument `accountId`',
);
}
return selectWithSchema(
'transactions',
'SELECT * FROM v_transactions WHERE account = ?',
[accountId],
);
}
export function insertTransaction(
transaction,
): Promise<TransactionEntity['id']> {
return insertWithSchema('transactions', transaction);
}
export function updateTransaction(transaction) {
return updateWithSchema('transactions', transaction);
}
export async function deleteTransaction(transaction) {
return delete_('transactions', transaction.id);
}
/**
* Move a transaction to a new position within the same date.
* Uses the same midpoint/shove algorithm as category reordering.
*
* @param id - The ID of the transaction to move
* @param accountId - The account the transaction belongs to
* @param targetId - The ID of the transaction to place AFTER, or null to place at top
*/
export async function moveTransaction(
id: string,
accountId: string,
targetId: string | null,
) {
await batchMessages(async () => {
const transaction = await getTransaction(id);
if (!transaction) {
throw new Error(`Transaction not found: ${id}`);
}
// Validate that the transaction belongs to the specified account
if (transaction.account !== accountId) {
throw new Error(
`Transaction ${id} does not belong to account ${accountId}`,
);
}
// Convert date string (YYYY-MM-DD) to integer format (YYYYMMDD) for SQL query
const dateInt = parseInt(transaction.date.replace(/-/g, ''), 10);
// Get transactions to reorder against.
// If this is a child transaction, scope to siblings with the same parent_id.
// Otherwise, get all parent transactions for the same date (excluding children).
// Query in DESC order to match UI display order.
const isChild = transaction.is_child && transaction.parent_id;
const transactions = await all<{ id: string; sort_order: number }>(
isChild
? `SELECT vt.id, vt.sort_order
FROM v_transactions vt
WHERE vt.parent_id = ?
ORDER BY sort_order DESC, id`
: `SELECT vt.id, vt.sort_order
FROM v_transactions vt
WHERE vt.account = ?
AND vt.date = ?
AND vt.is_child = 0
ORDER BY sort_order DESC, id`,
isChild ? [transaction.parent_id] : [accountId, dateInt],
);
// Calculate new sort_order using the descending shove algorithm
// - If targetId is null, place at TOP (highest sort_order)
// - Otherwise, place AFTER target (sort_order between target and next)
const { sort_order: newSortOrder, updates } = shoveSortOrdersDescending(
transactions,
targetId,
id,
TRANSACTION_SORT_INCREMENT,
);
// Apply updates to shuffle other transactions if needed
for (const info of updates) {
await update('transactions', info);
// Only move subtransactions for parent transactions
if (!isChild) {
await moveSubtransactions(info.id, info.sort_order, accountId, dateInt);
}
}
// Update the moved transaction
await update('transactions', { id, sort_order: newSortOrder });
// Only move subtransactions for parent transactions
if (!isChild) {
await moveSubtransactions(id, newSortOrder, accountId, dateInt);
}
});
}
/**
* Move a schedule to a new position among all non-tombstoned schedules.
* Uses the same midpoint/shove algorithm as transaction reordering.
*
* @param id - The ID of the schedule to move
* @param targetId - The ID of the schedule to place AFTER, or null to place at top
*/
export async function moveSchedule(id: string, targetId: string | null) {
await batchMessages(async () => {
const schedule = await first<{ id: string }>(
'SELECT id FROM schedules WHERE id = ? AND tombstone = 0',
[id],
);
if (!schedule) {
throw new Error(`Schedule not found: ${id}`);
}
const schedules = await all<{ id: string; sort_order: number }>(
`SELECT id, sort_order FROM schedules
WHERE tombstone = 0
ORDER BY sort_order DESC, id`,
);
const { sort_order: newSortOrder, updates } = shoveSortOrdersDescending(
schedules,
targetId,
id,
);
for (const info of updates) {
await update('schedules', info);
}
await update('schedules', { id, sort_order: newSortOrder });
});
}
/**
* Update sort_order of child/subtransactions to maintain relative ordering.
* Children are distributed evenly between the parent's sort_order and the
* next sibling's sort_order to avoid collisions.
*
* This ensures split transaction children move with their parent.
*
* @param parentId - The ID of the parent transaction
* @param parentSortOrder - The sort_order of the parent transaction
* @param accountId - The account ID to scope the next sibling lookup
* @param txnDate - The transaction date (as integer YYYYMMDD) to scope the next sibling lookup
*/
async function moveSubtransactions(
parentId: string,
parentSortOrder: number,
accountId: string,
txnDate: number,
) {
const subtransactions = await all<{ id: string }>(
'SELECT id FROM v_transactions WHERE parent_id = ? ORDER BY sort_order DESC',
[parentId],
);
if (subtransactions.length === 0) {
return;
}
// Find the next sibling's sort_order (transaction with lower sort_order that isn't a child)
// Scoped to the same account and date to avoid picking transactions from other contexts
const nextSibling = await first<{ sort_order: number }>(
`SELECT sort_order FROM v_transactions
WHERE parent_id IS NULL
AND is_child = 0
AND sort_order < ?
AND account = ?
AND date = ?
ORDER BY sort_order DESC
LIMIT 1`,
[parentSortOrder, accountId, txnDate],
);
// Calculate the available gap for distributing children
// Use a sensible fallback if no next sibling exists
const nextSiblingSortOrder =
nextSibling?.sort_order ?? parentSortOrder - TRANSACTION_SORT_INCREMENT;
const gap = parentSortOrder - nextSiblingSortOrder;
// Distribute children evenly within the gap
// Avoid rounding to prevent duplicate sort_order values in tight gaps
for (const [index, sub] of subtransactions.entries()) {
const newSortOrder =
parentSortOrder - ((index + 1) * gap) / (subtransactions.length + 1);
await update('transactions', {
id: sub.id,
sort_order: newSortOrder,
});
}
}
function toSqlQueryParameters(params: unknown[]) {
return params.map(() => '?').join(',');
}
export function getTags() {
return all<DbTag>(`
SELECT id, tag, color, description, hidden
FROM tags
WHERE tombstone = 0
`);
}
export function getAllTags() {
return all<DbTag>(`
SELECT id, tag, color, description, hidden
FROM tags
`);
}
export function insertTag(
tag: Omit<DbTag, 'id' | 'tombstone'>,
): Promise<DbTag['id']> {
return insertWithUUID('tags', tag);
}
export async function deleteTag(tag: Pick<DbTag, 'id'>) {
return delete_('tags', tag.id);
}
export function updateTag(tag: Partial<DbTag> & Pick<DbTag, 'id'>) {
return update('tags', tag);
}
export function findTags() {
return all<{ id: DbTransaction['id']; notes: string }>(
`
SELECT id, notes
FROM transactions
WHERE tombstone = 0 AND notes LIKE ?
`,
['%#%'],
);
}