A local-first personal finance app
import { aqlQuery } from '#server/aql';
import * as db from '#server/db';
import * as sheet from '#server/sheet';
import { resolveName } from '#server/spreadsheet/util';
// @ts-strict-ignore
import * as monthUtils from '#shared/months';
import { q } from '#shared/query';
import { getChangedValues } from '#shared/util';
import type { CategoryGroupEntity } from '#types/models';
import * as budgetActions from './actions';
import * as envelopeBudget from './envelope';
import * as trackingBudget from './tracking';
// How far ahead budget months are available. Planning past the minimum
// keeps a few empty months after the latest allocation, up to a hard cap
// so stray far-future data can't create an unbounded number of months.
const MIN_MONTHS_AHEAD = 12;
const MONTHS_AFTER_LATEST_ALLOCATION = 3;
const MAX_MONTHS_AHEAD = 60;
export function getBudgetType() {
const meta = sheet.get().meta();
return meta.budgetType || 'envelope';
}
export function getBudgetRange(
start: string,
end: string,
latestAllocatedMonth?: string | null,
) {
start = monthUtils.getMonth(start);
end = monthUtils.getMonth(end);
// The start date should never be after the end date. If that
// happened, the month range might be a valid range and weird
// things happen
if (start > end) {
start = end;
}
const maxEnd = monthUtils.addMonths(end, MAX_MONTHS_AHEAD);
// Budgets should exist 3 months before the earliest needed date
// (either the oldest transaction or the current month if no
// transactions yet), and at least a year from the current date.
start = monthUtils.subMonths(start, 3);
end = monthUtils.addMonths(end, MIN_MONTHS_AHEAD);
if (latestAllocatedMonth) {
const plannedEnd = monthUtils.addMonths(
latestAllocatedMonth,
MONTHS_AFTER_LATEST_ALLOCATION,
);
if (plannedEnd > end) {
end = plannedEnd < maxEnd ? plannedEnd : maxEnd;
}
}
return { start, end, range: monthUtils.rangeInclusive(start, end) };
}
// The latest month with a non-zero amount in the active budget type.
// Envelope and tracking amounts are stored separately, so each type
// extends its own range.
async function getLatestAllocatedMonth(): Promise<string | null> {
const table =
getBudgetType() === 'tracking' ? 'reflect_budgets' : 'zero_budgets';
const row = await db.first<{ month: number | null }>(
`SELECT MAX(b.month) AS month FROM ${table} b
JOIN categories c ON c.id = b.category
WHERE c.tombstone = 0 AND b.amount != 0`,
);
if (!row?.month) {
return null;
}
const month = String(row.month);
return `${month.slice(0, 4)}-${month.slice(4)}`;
}
// The day range spanning the given months. They aren't guaranteed to be
// sorted, so find the first and last explicitly ('YYYY-MM' strings compare
// chronologically).
function getMonthSpanBounds(months: string[]) {
let firstMonth = months[0];
let lastMonth = months[0];
for (const month of months) {
if (month < firstMonth) {
firstMonth = month;
}
if (month > lastMonth) {
lastMonth = month;
}
}
return {
rangeStart: monthUtils.bounds(firstMonth).start,
rangeEnd: monthUtils.bounds(lastMonth).end,
};
}
// Computes the spend total for every category in every month within the
// given day range using a single grouped query. This is used to seed the
// `sum-amount` cells on a cold build so we avoid running one
// `SELECT SUM(amount)` query per category per month (which scales as
// categories × months and dominates load time for budgets with many years
// of data). The filters must match the per-cell query in `createCategory`
// exactly so balances stay identical.
function getSumAmountsByMonth(
rangeStart: number,
rangeEnd: number,
): Map<string, number> {
const rows = db.runQuery<{ month: number; category: string; amount: number }>(
`SELECT t.category AS category,
t.date / 100 AS month,
SUM(t.amount) AS amount
FROM v_transactions_internal_alive t
LEFT JOIN accounts a ON a.id = t.account
WHERE t.date >= ${rangeStart} AND t.date <= ${rangeEnd}
AND t.category IS NOT NULL
AND a.offbudget = 0
GROUP BY t.category, t.date / 100`,
[],
true,
);
const sums = new Map<string, number>();
for (const row of rows) {
sums.set(`${row.month}-${row.category}`, row.amount || 0);
}
return sums;
}
// Reseeds the `sum-amount` cells for the given months from the same single
// grouped query a cold build uses, and returns the names of the cells that
// were seeded. Resetting the budget cache recomputes every cell, which would
// otherwise run one `SELECT SUM(amount)` per category per month; the caller
// skips recomputing these and caches them directly instead.
export async function reseedSumAmounts(months: string[]): Promise<string[]> {
if (months.length === 0) {
return [];
}
const { rangeStart, rangeEnd } = getMonthSpanBounds(months);
const sums = getSumAmountsByMonth(rangeStart, rangeEnd);
const { data: groups }: { data: CategoryGroupEntity[] } = await aqlQuery(
q('category_groups').select('*'),
);
const categories = groups.flatMap(group => group.categories);
const seededCells: string[] = [];
for (const month of months) {
const sheetName = monthUtils.sheetForMonth(month);
const dbMonth = parseInt(month.replace('-', ''));
for (const cat of categories) {
const name = resolveName(sheetName, `sum-amount-${cat.id}`);
// A cell the spreadsheet doesn't have stays out of the seed set and is
// left to be recomputed normally.
if (!sheet.get().hasCell(name)) {
continue;
}
sheet.get().load(name, sums.get(`${dbMonth}-${cat.id}`) || 0);
seededCells.push(name);
}
}
return seededCells;
}
export function createCategory(cat, sheetName, prevSheetName, start, end) {
sheet.get().createDynamic(sheetName, 'sum-amount-' + cat.id, {
initialValue: 0,
run: () => {
// Making this sync is faster!
const rows = db.runQuery<{ amount: number }>(
`SELECT SUM(amount) as amount FROM v_transactions_internal_alive t
LEFT JOIN accounts a ON a.id = t.account
WHERE t.date >= ${start} AND t.date <= ${end}
AND category = '${cat.id}' AND a.offbudget = 0`,
[],
true,
);
const row = rows[0];
const amount = row ? row.amount : 0;
return amount || 0;
},
});
if (getBudgetType() === 'envelope') {
envelopeBudget.createCategory(cat, sheetName, prevSheetName);
} else {
void trackingBudget.createCategory(cat, sheetName, prevSheetName);
}
}
function handleAccountChange(months, oldValue, newValue) {
if (!oldValue || oldValue.offbudget !== newValue.offbudget) {
const rows = db.runQuery<Pick<db.DbTransaction, 'category'>>(
`
SELECT DISTINCT(category) as category FROM transactions
WHERE acct = ?
`,
[newValue.id],
true,
);
months.forEach(month => {
const sheetName = monthUtils.sheetForMonth(month);
rows.forEach(row => {
sheet
.get()
.recompute(resolveName(sheetName, 'sum-amount-' + row.category));
});
});
}
}
function handleTransactionChange(transaction, changedFields) {
if (
(changedFields.has('date') ||
changedFields.has('acct') ||
changedFields.has('amount') ||
changedFields.has('category') ||
changedFields.has('tombstone') ||
changedFields.has('isParent')) &&
transaction.date &&
transaction.category
) {
const month = monthUtils.monthFromDate(db.fromDateRepr(transaction.date));
const sheetName = monthUtils.sheetForMonth(month);
sheet
.get()
.recompute(resolveName(sheetName, 'sum-amount-' + transaction.category));
}
}
function handleCategoryMappingChange(months, oldValue, newValue) {
months.forEach(month => {
const sheetName = monthUtils.sheetForMonth(month);
if (oldValue) {
sheet
.get()
.recompute(resolveName(sheetName, 'sum-amount-' + oldValue.transferId));
}
sheet
.get()
.recompute(resolveName(sheetName, 'sum-amount-' + newValue.transferId));
});
}
function handleBudgetMonthChange(budget) {
const sheetName = monthUtils.sheetForMonth(budget.id);
sheet.get().set(`${sheetName}!buffered`, budget.buffered);
}
function handleBudgetChange(budget) {
if (budget.category) {
const sheetName = monthUtils.sheetForMonth(budget.month.toString());
sheet
.get()
.set(`${sheetName}!budget-${budget.category}`, budget.amount || 0);
sheet
.get()
.set(
`${sheetName}!carryover-${budget.category}`,
budget.carryover === 1 ? true : false,
);
sheet.get().set(`${sheetName}!goal-${budget.category}`, budget.goal);
sheet
.get()
.set(`${sheetName}!long-goal-${budget.category}`, budget.long_goal);
}
}
export function triggerBudgetChanges(oldValues, newValues) {
const { createdMonths = new Set() } = sheet.get().meta();
const budgetType = getBudgetType();
sheet.startTransaction();
try {
newValues.forEach((items, table) => {
const old = oldValues.get(table);
items.forEach(newValue => {
const oldValue = old && old.get(newValue.id);
if (table === 'zero_budget_months') {
handleBudgetMonthChange(newValue);
} else if (table === 'zero_budgets' || table === 'reflect_budgets') {
handleBudgetChange(newValue);
} else if (table === 'transactions') {
const changed = new Set(
Object.keys(getChangedValues(oldValue || {}, newValue) || {}),
);
if (oldValue) {
handleTransactionChange(oldValue, changed);
}
handleTransactionChange(newValue, changed);
} else if (table === 'category_mapping') {
handleCategoryMappingChange(createdMonths, oldValue, newValue);
} else if (table === 'categories') {
if (budgetType === 'envelope') {
envelopeBudget.handleCategoryChange(
createdMonths,
oldValue,
newValue,
);
} else {
trackingBudget.handleCategoryChange(
createdMonths,
oldValue,
newValue,
);
}
} else if (table === 'category_groups') {
if (budgetType === 'envelope') {
envelopeBudget.handleCategoryGroupChange(
createdMonths,
oldValue,
newValue,
);
} else {
trackingBudget.handleCategoryGroupChange(
createdMonths,
oldValue,
newValue,
);
}
} else if (table === 'accounts') {
handleAccountChange(createdMonths, oldValue, newValue);
}
});
});
} finally {
sheet.endTransaction();
}
}
export async function doTransfer(categoryIds, transferId) {
const { createdMonths: months } = sheet.get().meta();
[...months].forEach(month => {
const totalValue = categoryIds
.map(id => {
return budgetActions.getBudget({ month, category: id });
})
.reduce((total, value) => total + value, 0);
const transferValue = budgetActions.getBudget({
month,
category: transferId,
});
void budgetActions.setBudget({
month,
category: transferId,
amount: totalValue + transferValue,
});
});
}
export async function createBudget(months) {
const { data: groups }: { data: CategoryGroupEntity[] } = await aqlQuery(
q('category_groups').select('*'),
);
const categories = groups.flatMap(group => group.categories);
sheet.startTransaction();
const meta = sheet.get().meta();
meta.createdMonths = meta.createdMonths || new Set();
const budgetType = getBudgetType();
if (budgetType === 'envelope') {
envelopeBudget.createBudget(meta, categories, months);
}
// Only months that don't already exist need to be created and seeded.
const monthsToCreate = months.filter(month => !meta.createdMonths.has(month));
// Spend totals for every category in the months being created, computed
// once via a single grouped query and used to seed the `sum-amount` cells so
// they don't each run their own query. Scoped to the uncached month span so
// extending the budget horizon doesn't rescan the entire transaction
// history, and loaded lazily so warm loads never touch the database here.
let sumAmounts: Map<string, number> | null = null;
const getSumAmounts = () => {
if (!sumAmounts) {
const { rangeStart, rangeEnd } = getMonthSpanBounds(monthsToCreate);
sumAmounts = getSumAmountsByMonth(rangeStart, rangeEnd);
}
return sumAmounts;
};
const seededCells: string[] = [];
monthsToCreate.forEach(month => {
const prevMonth = monthUtils.prevMonth(month);
const { start, end } = monthUtils.bounds(month);
const sheetName = monthUtils.sheetForMonth(month);
const prevSheetName = monthUtils.sheetForMonth(prevMonth);
const dbMonth = parseInt(month.replace('-', ''));
categories.forEach(cat => {
// Seed the spend total before creating the dynamic cell so the cell
// skips its per-category query. Only happens on a cold build, when
// the value hasn't been restored from cache.
const sumCell = `sum-amount-${cat.id}`;
if (sheet.get().getCellValueLoose(sheetName, sumCell) == null) {
const name = resolveName(sheetName, sumCell);
sheet
.get()
.load(name, getSumAmounts().get(`${dbMonth}-${cat.id}`) || 0);
seededCells.push(name);
}
createCategory(cat, sheetName, prevSheetName, start, end);
});
groups.forEach(group => {
if (budgetType === 'envelope') {
envelopeBudget.createCategoryGroup(group, sheetName);
} else {
trackingBudget.createCategoryGroup(group, sheetName);
}
});
if (budgetType === 'envelope') {
envelopeBudget.createSummary(
groups,
categories,
prevSheetName,
sheetName,
);
} else {
trackingBudget.createSummary(groups, sheetName);
}
meta.createdMonths.add(month);
});
sheet.get().setMeta(meta);
sheet.endTransaction();
// Persist the seeded spend totals to the cache. Because they were loaded
// directly (rather than recomputed) they aren't part of the computation
// queue that normally gets cached, so without this a warm load would have
// to recompute them. The cells already hold their final values here.
if (seededCells.length > 0) {
sheet.get().saveCachedCells(seededCells);
}
// Wait for the spreadsheet to finish computing. Normally this won't
// do anything (as values are cached) but on first run this need to
// show the loading screen while it initially sets up.
await sheet.waitOnSpreadsheet();
}
export async function createAllBudgets() {
const earliestTransaction = await db.first<db.DbTransaction>(
'SELECT * FROM transactions WHERE isChild=0 AND date IS NOT NULL ORDER BY date ASC LIMIT 1',
);
const earliestDate =
earliestTransaction && db.fromDateRepr(earliestTransaction.date);
const currentMonth = monthUtils.currentMonth();
// Get the range based off of the earliest transaction and the
// current month. If no transactions currently exist the current
// month is also used as the starting month
const { start, end, range } = getBudgetRange(
earliestDate || currentMonth,
currentMonth,
await getLatestAllocatedMonth(),
);
const meta = sheet.get().meta();
const createdMonths = meta.createdMonths || new Set();
const newMonths = range.filter(m => !createdMonths.has(m));
if (newMonths.length > 0) {
await createBudget(range);
}
// The range only grows while the budget is open, so every created month
// stays reachable. Clearing allocations shrinks it on the next load.
let lastMonth = end;
for (const month of sheet.get().meta().createdMonths) {
if (month > lastMonth) {
lastMonth = month;
}
}
return { start, end: lastMonth };
}
export async function setType(type) {
const meta = sheet.get().meta();
if (type === meta.budgetType) {
return;
}
meta.budgetType = type;
meta.createdMonths = new Set();
// Go through and force all the cells to be recomputed
const nodes = sheet.get().getNodes();
db.transaction(() => {
for (const name of nodes.keys()) {
const [sheetName, cellName] = name.split('!');
if (sheetName.match(/^budget\d+/)) {
sheet.get().deleteCell(sheetName, cellName);
}
}
});
sheet.get().startCacheBarrier();
void sheet.loadUserBudgets(db);
const bounds = await createAllBudgets();
sheet.get().endCacheBarrier();
return bounds;
}