Release notes

packages/loot-core/src/mocks/budget.ts

@@ -461,14 +461,14 @@ async function fillOther(handlers, account, payees, groups) {
 async function createBudget(accounts, payees, groups) {
   const primaryAccount = accounts.find(a => (a.name = 'Bank of America'));
   const earliestDate = (
-    await db.first(
-      `SELECT * FROM v_transactions t LEFT JOIN accounts a ON t.account = a.id
+    await db.first<Pick<db.DbViewTransaction, 'date'>>(
+      `SELECT t.date FROM v_transactions t LEFT JOIN accounts a ON t.account = a.id
        WHERE a.offbudget = 0 AND t.is_child = 0 ORDER BY date ASC LIMIT 1`,
     )
   ).date;
   const earliestPrimaryDate = (
-    await db.first(
-      `SELECT * FROM v_transactions t LEFT JOIN accounts a ON t.account = a.id
+    await db.first<Pick<db.DbViewTransaction, 'date'>>(
+      `SELECT t.date FROM v_transactions t LEFT JOIN accounts a ON t.account = a.id
        WHERE a.id = ? AND a.offbudget = 0 AND t.is_child = 0 ORDER BY date ASC LIMIT 1`,
       [primaryAccount.id],
     )

packages/loot-core/src/server/accounts/app.ts

@@ -11,11 +11,9 @@ import {
   AccountEntity,
   CategoryEntity,
   SyncServerGoCardlessAccount,
-  PayeeEntity,
   TransactionEntity,
   SyncServerSimpleFinAccount,
 } from '../../types/models';
-import { BankEntity } from '../../types/models/bank';
 import { createApp } from '../app';
 import * as db from '../db';
 import {
@@ -77,24 +75,27 @@ async function getAccountBalance({
   id: string;
   cutoff: string | Date;
 }) {
-  const { balance }: { balance: number } = await db.first(
+  const result = await db.first<{ balance: number }>(
     'SELECT sum(amount) as balance FROM transactions WHERE acct = ? AND isParent = 0 AND tombstone = 0 AND date <= ?',
     [id, db.toDateRepr(dayFromDate(cutoff))],
   );
-  return balance ? balance : 0;
+  return result?.balance ? result.balance : 0;
 }
 
 async function getAccountProperties({ id }: { id: AccountEntity['id'] }) {
-  const { balance }: { balance: number } = await db.first(
+  const balanceResult = await db.first<{ balance: number }>(
     'SELECT sum(amount) as balance FROM transactions WHERE acct = ? AND isParent = 0 AND tombstone = 0',
     [id],
   );
-  const { count }: { count: number } = await db.first(
+  const countResult = await db.first<{ count: number }>(
     'SELECT count(id) as count FROM transactions WHERE acct = ? AND tombstone = 0',
     [id],
   );
 
-  return { balance: balance || 0, numTransactions: count };
+  return {
+    balance: balanceResult?.balance || 0,
+    numTransactions: countResult?.count || 0,
+  };
 }
 
 async function linkGoCardlessAccount({
@@ -112,10 +113,15 @@ async function linkGoCardlessAccount({
   const bank = await link.findOrCreateBank(account.institution, requisitionId);
 
   if (upgradingId) {
-    const accRow: AccountEntity = await db.first(
+    const accRow = await db.first<db.DbAccount>(
       'SELECT * FROM accounts WHERE id = ?',
       [upgradingId],
     );
+
+    if (!accRow) {
+      throw new Error(`Account with ID ${upgradingId} not found.`);
+    }
+
     id = accRow.id;
     await db.update('accounts', {
       id,
@@ -178,10 +184,15 @@ async function linkSimpleFinAccount({
   );
 
   if (upgradingId) {
-    const accRow: AccountEntity = await db.first(
+    const accRow = await db.first<db.DbAccount>(
       'SELECT * FROM accounts WHERE id = ?',
       [upgradingId],
     );
+
+    if (!accRow) {
+      throw new Error(`Account with ID ${upgradingId} not found.`);
+    }
+
     id = accRow.id;
     await db.update('accounts', {
       id,
@@ -278,7 +289,7 @@ async function closeAccount({
   await unlinkAccount({ id });
 
   return withUndo(async () => {
-    const account: AccountEntity = await db.first(
+    const account = await db.first<db.DbAccount>(
       'SELECT * FROM accounts WHERE id = ? AND tombstone = 0',
       [id],
     );
@@ -303,11 +314,15 @@ async function closeAccount({
         true,
       );
 
-      const { id: payeeId }: Pick<PayeeEntity, 'id'> = await db.first(
+      const transferPayee = await db.first<Pick<db.DbPayee, 'id'>>(
         'SELECT id FROM payees WHERE transfer_acct = ?',
         [id],
       );
 
+      if (!transferPayee) {
+        throw new Error(`Transfer payee with account ID ${id} not found.`);
+      }
+
       await batchMessages(async () => {
         // TODO: what this should really do is send a special message that
         // automatically marks the tombstone value for all transactions
@@ -328,7 +343,7 @@ async function closeAccount({
         });
 
         db.deleteAccount({ id });
-        db.deleteTransferPayee({ id: payeeId });
+        db.deleteTransferPayee({ id: transferPayee.id });
       });
     } else {
       if (balance !== 0 && transferAccountId == null) {
@@ -340,14 +355,20 @@ async function closeAccount({
       // If there is a balance we need to transfer it to the specified
       // account (and possibly categorize it)
       if (balance !== 0 && transferAccountId) {
-        const { id: payeeId }: Pick<PayeeEntity, 'id'> = await db.first(
+        const transferPayee = await db.first<Pick<db.DbPayee, 'id'>>(
           'SELECT id FROM payees WHERE transfer_acct = ?',
           [transferAccountId],
         );
 
+        if (!transferPayee) {
+          throw new Error(
+            `Transfer payee with account ID ${transferAccountId} not found.`,
+          );
+        }
+
         await mainApp.handlers['transaction-add']({
           id: uuidv4(),
-          payee: payeeId,
+          payee: transferPayee.id,
           amount: -balance,
           account: id,
           date: monthUtils.currentDay(),
@@ -942,20 +963,21 @@ async function importTransactions({
 }
 
 async function unlinkAccount({ id }: { id: AccountEntity['id'] }) {
-  const { bank: bankId }: Pick<AccountEntity, 'bank'> = await db.first(
-    'SELECT bank FROM accounts WHERE id = ?',
+  const accRow = await db.first<db.DbAccount>(
+    'SELECT * FROM accounts WHERE id = ?',
     [id],
   );
 
+  if (!accRow) {
+    throw new Error(`Account with ID ${id} not found.`);
+  }
+
+  const bankId = accRow.bank;
+
   if (!bankId) {
     return 'ok';
   }
 
-  const accRow: AccountEntity = await db.first(
-    'SELECT * FROM accounts WHERE id = ?',
-    [id],
-  );
-
   const isGoCardless = accRow.account_sync_source === 'goCardless';
 
   await db.updateAccount({
@@ -972,7 +994,7 @@ async function unlinkAccount({ id }: { id: AccountEntity['id'] }) {
     return;
   }
 
-  const { count }: { count: number } = await db.first(
+  const accountWithBankResult = await db.first<{ count: number }>(
     'SELECT COUNT(*) as count FROM accounts WHERE bank = ?',
     [bankId],
   );
@@ -984,15 +1006,23 @@ async function unlinkAccount({ id }: { id: AccountEntity['id'] }) {
     return 'ok';
   }
 
-  if (count === 0) {
-    const { bank_id: requisitionId }: Pick<BankEntity, 'bank_id'> =
-      await db.first('SELECT bank_id FROM banks WHERE id = ?', [bankId]);
+  if (!accountWithBankResult || accountWithBankResult.count === 0) {
+    const bank = await db.first<Pick<db.DbBank, 'bank_id'>>(
+      'SELECT bank_id FROM banks WHERE id = ?',
+      [bankId],
+    );
+
+    if (!bank) {
+      throw new Error(`Bank with ID ${bankId} not found.`);
+    }
 
     const serverConfig = getServer();
     if (!serverConfig) {
       throw new Error('Failed to get server config.');
     }
 
+    const requisitionId = bank.bank_id;
+
     try {
       await post(
         serverConfig.GOCARDLESS_SERVER + '/remove-account',

packages/loot-core/src/server/accounts/link.ts

@@ -4,7 +4,7 @@ import { v4 as uuidv4 } from 'uuid';
 import * as db from '../db';
 
 export async function findOrCreateBank(institution, requisitionId) {
-  const bank = await db.first(
+  const bank = await db.first<Pick<db.DbBank, 'id' | 'bank_id'>>(
     'SELECT id, bank_id, name FROM banks WHERE bank_id = ?',
     [requisitionId],
   );

packages/loot-core/src/server/accounts/payees.ts

@@ -1,11 +1,10 @@
 // @ts-strict-ignore
-import { CategoryEntity, PayeeEntity } from '../../types/models';
 import * as db from '../db';
 
 export async function createPayee(description) {
   // Check to make sure no payee already exists with exactly the same
   // name
-  const row: Pick<PayeeEntity, 'id'> = await db.first(
+  const row = await db.first<Pick<db.DbPayee, 'id'>>(
     `SELECT id FROM payees WHERE UNICODE_LOWER(name) = ? AND tombstone = 0`,
     [description.toLowerCase()],
   );
@@ -13,19 +12,19 @@ export async function createPayee(description) {
   if (row) {
     return row.id;
   } else {
-    return (await db.insertPayee({ name: description })) as PayeeEntity['id'];
+    return (await db.insertPayee({ name: description })) as db.DbPayee['id'];
   }
 }
 
 export async function getStartingBalancePayee() {
-  let category: CategoryEntity = await db.first(`
+  let category = await db.first<db.DbCategory>(`
     SELECT * FROM categories
       WHERE is_income = 1 AND
       LOWER(name) = 'starting balances' AND
       tombstone = 0
   `);
   if (category === null) {
-    category = await db.first(
+    category = await db.first<db.DbCategory>(
       'SELECT * FROM categories WHERE is_income = 1 AND tombstone = 0',
     );
   }

packages/loot-core/src/server/accounts/sync.test.ts

@@ -24,7 +24,9 @@ beforeEach(async () => {
 });
 
 function getAllTransactions() {
-  return db.all(
+  return db.all<
+    db.DbViewTransactionInternal & { payee_name: db.DbPayee['name'] }
+  >(
     `SELECT t.*, p.name as payee_name
        FROM v_transactions_internal t
        LEFT JOIN payees p ON p.id = t.payee

packages/loot-core/src/server/accounts/sync.ts

@@ -511,7 +511,7 @@ export async function reconcileTransactions(
       }
 
       if (existing.is_parent && existing.cleared !== updates.cleared) {
-        const children = await db.all(
+        const children = await db.all<db.DbViewTransaction>(
           'SELECT id FROM v_transactions WHERE parent_id = ?',
           [existing.id],
         );
@@ -584,7 +584,7 @@ export async function matchTransactions(
   );
 
   // The first pass runs the rules, and preps data for fuzzy matching
-  const accounts: AccountEntity[] = await db.getAccounts();
+  const accounts: db.DbAccount[] = await db.getAccounts();
   const accountsMap = new Map(accounts.map(account => [account.id, account]));
 
   const transactionsStep1 = [];
@@ -603,7 +603,7 @@ export async function matchTransactions(
     // is the highest fidelity match and should always be attempted
     // first.
     if (trans.imported_id) {
-      match = await db.first(
+      match = await db.first<db.DbViewTransaction>(
         'SELECT * FROM v_transactions WHERE imported_id = ? AND account = ?',
         [trans.imported_id, acctId],
       );
@@ -624,7 +624,22 @@ export async function matchTransactions(
       // 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(
+        fuzzyDataset = await db.all<
+          Pick<
+            db.DbViewTransaction,
+            | 'id'
+            | 'is_parent'
+            | 'date'
+            | 'imported_id'
+            | 'payee'
+            | 'imported_payee'
+            | 'category'
+            | 'notes'
+            | 'reconciled'
+            | 'cleared'
+            | 'amount'
+          >
+        >(
           `SELECT id, is_parent, date, imported_id, payee, imported_payee, category, notes, reconciled, cleared, amount
           FROM v_transactions
           WHERE
@@ -641,7 +656,22 @@ export async function matchTransactions(
           ],
         );
       } else {
-        fuzzyDataset = await db.all(
+        fuzzyDataset = await db.all<
+          Pick<
+            db.DbViewTransaction,
+            | 'id'
+            | 'is_parent'
+            | 'date'
+            | 'imported_id'
+            | 'payee'
+            | 'imported_payee'
+            | 'category'
+            | 'notes'
+            | 'reconciled'
+            | 'cleared'
+            | 'amount'
+          >
+        >(
           `SELECT id, is_parent, date, imported_id, payee, imported_payee, category, notes, reconciled, cleared, amount
           FROM v_transactions
           WHERE date >= ? AND date <= ? AND amount = ? AND account = ?`,
@@ -737,7 +767,7 @@ export async function addTransactions(
     { rawPayeeName: true },
   );
 
-  const accounts: AccountEntity[] = await db.getAccounts();
+  const accounts: db.DbAccount[] = await db.getAccounts();
   const accountsMap = new Map(accounts.map(account => [account.id, account]));
 
   for (const { trans: originalTrans, subtransactions } of normalized) {
@@ -873,7 +903,7 @@ export async function syncAccount(
   acctId: string,
   bankId: string,
 ) {
-  const acctRow = await db.select('accounts', id);
+  const acctRow = await db.select<db.DbAccount>('accounts', id);
 
   const syncStartDate = await getAccountSyncStartDate(id);
   const oldestTransaction = await getAccountOldestTransaction(id);
@@ -917,7 +947,7 @@ export async function simpleFinBatchSync(
     const account = accounts[i];
     const download = res[account.account_id];
 
-    const acctRow = await db.select('accounts', account.id);
+    const acctRow = await db.select<db.DbAccount>('accounts', account.id);
     const oldestTransaction = await getAccountOldestTransaction(account.id);
     const newAccount = oldestTransaction == null;
 

packages/loot-core/src/server/api.ts

@@ -53,7 +53,7 @@ function withMutation<Params extends Array<unknown>, ReturnType>(
         const latestTimestamp = getClock().timestamp.toString();
         const result = await handler(...args);
 
-        const rows = await db.all(
+        const rows = await db.all<Pick<db.DbCrdtMessage, 'dataset'>>(
           'SELECT DISTINCT dataset FROM messages_crdt WHERE timestamp > ?',
           [latestTimestamp],
         );
@@ -94,9 +94,10 @@ async function validateExpenseCategory(debug, id) {
     throw APIError(`${debug}: category id is required`);
   }
 
-  const row = await db.first('SELECT is_income FROM categories WHERE id = ?', [
-    id,
-  ]);
+  const row = await db.first<Pick<db.DbCategory, 'is_income'>>(
+    'SELECT is_income FROM categories WHERE id = ?',
+    [id],
+  );
 
   if (!row) {
     throw APIError(`${debug}: category “${id}” does not exist`);
@@ -354,7 +355,8 @@ handlers['api/budget-month'] = async function ({ month }) {
   checkFileOpen();
   await validateMonth(month);
 
-  const groups = await db.getCategoriesGrouped();
+  const grouped = await db.getCategoriesGrouped();
+  const groups = categoryGroupModel.fromDbArray(grouped);
   const sheetName = monthUtils.sheetForMonth(month);
 
   function value(name) {
@@ -555,7 +557,7 @@ handlers['api/transaction-delete'] = withMutation(async function ({ id }) {
 
 handlers['api/accounts-get'] = async function () {
   checkFileOpen();
-  const accounts = await db.getAccounts();
+  const accounts = accountModel.fromDbArray(await db.getAccounts());
   return accounts.map(account => accountModel.toExternal(account));
 };
 

packages/loot-core/src/server/aql/exec.test.ts

@@ -261,7 +261,9 @@ describe('runQuery', () => {
   it('fetches all data required for $oneof', async () => {
     await insertTransactions();
 
-    const rows = await db.all('SELECT id FROM transactions WHERE amount < -50');
+    const rows = await db.all<Pick<db.DbTransaction, 'id'>>(
+      'SELECT id FROM transactions WHERE amount < -50',
+    );
     const ids = rows.slice(0, 3).map(row => row.id);
     ids.sort();
 

packages/loot-core/src/server/aql/schema/executors.ts

@@ -110,8 +110,8 @@ async function execTransactionsGrouped(
     return execQuery(queryState, state, s, params, outputTypes);
   }
 
-  let rows;
-  let matched = null;
+  let rows: Array<{ group_id: db.DbTransaction['id']; matched: string }>;
+  let matched: Set<db.DbTransaction['id']> = null;
 
   if (isHappyPathQuery(queryState)) {
     // This is just an optimization - we can just filter out children
@@ -171,7 +171,9 @@ async function execTransactionsGrouped(
     ${sql.orderBy}
   `;
 
-  const allRows = await db.all(finalSql);
+  const allRows = await db.all<
+    db.DbTransaction & { _parent_id: db.DbTransaction['id'] }
+  >(finalSql);
 
   // Group the parents and children up
   const { parents, children } = allRows.reduce(

packages/loot-core/src/server/aql/schema/index.test.ts

@@ -9,29 +9,59 @@ beforeEach(global.emptyDatabase());
 
 describe('schema', () => {
   test('never returns transactions without a date', async () => {
-    expect((await db.all('SELECT * FROM transactions')).length).toBe(0);
-    expect((await db.all('SELECT * FROM v_transactions')).length).toBe(0);
+    expect(
+      (await db.all<db.DbTransaction>('SELECT * FROM transactions')).length,
+    ).toBe(0);
+    expect(
+      (await db.all<db.DbViewTransaction>('SELECT * FROM v_transactions'))
+        .length,
+    ).toBe(0);
     await db.runQuery('INSERT INTO transactions (acct) VALUES (?)', ['foo']);
-    expect((await db.all('SELECT * FROM transactions')).length).toBe(1);
-    expect((await db.all('SELECT * FROM v_transactions')).length).toBe(0);
+    expect(
+      (await db.all<db.DbTransaction>('SELECT * FROM transactions')).length,
+    ).toBe(1);
+    expect(
+      (await db.all<db.DbViewTransaction>('SELECT * FROM v_transactions'))
+        .length,
+    ).toBe(0);
   });
 
   test('never returns transactions without an account', async () => {
-    expect((await db.all('SELECT * FROM transactions')).length).toBe(0);
-    expect((await db.all('SELECT * FROM v_transactions')).length).toBe(0);
+    expect(
+      (await db.all<db.DbTransaction>('SELECT * FROM transactions')).length,
+    ).toBe(0);
+    expect(
+      (await db.all<db.DbViewTransaction>('SELECT * FROM v_transactions'))
+        .length,
+    ).toBe(0);
     await db.runQuery('INSERT INTO transactions (date) VALUES (?)', [20200101]);
-    expect((await db.all('SELECT * FROM transactions')).length).toBe(1);
-    expect((await db.all('SELECT * FROM v_transactions')).length).toBe(0);
+    expect(
+      (await db.all<db.DbTransaction>('SELECT * FROM transactions')).length,
+    ).toBe(1);
+    expect(
+      (await db.all<db.DbViewTransaction>('SELECT * FROM v_transactions'))
+        .length,
+    ).toBe(0);
   });
 
   test('never returns child transactions without a parent', async () => {
-    expect((await db.all('SELECT * FROM transactions')).length).toBe(0);
-    expect((await db.all('SELECT * FROM v_transactions')).length).toBe(0);
+    expect(
+      (await db.all<db.DbTransaction>('SELECT * FROM transactions')).length,
+    ).toBe(0);
+    expect(
+      (await db.all<db.DbViewTransaction>('SELECT * FROM v_transactions'))
+        .length,
+    ).toBe(0);
     await db.runQuery(
       'INSERT INTO transactions (date, acct, isChild) VALUES (?, ?, ?)',
       [20200101, 'foo', 1],
     );
-    expect((await db.all('SELECT * FROM transactions')).length).toBe(1);
-    expect((await db.all('SELECT * FROM v_transactions')).length).toBe(0);
+    expect(
+      (await db.all<db.DbTransaction>('SELECT * FROM transactions')).length,
+    ).toBe(1);
+    expect(
+      (await db.all<db.DbViewTransaction>('SELECT * FROM v_transactions'))
+        .length,
+    ).toBe(0);
   });
 });

packages/loot-core/src/server/budget/actions.ts

@@ -35,12 +35,14 @@ function calcBufferedAmount(
   return buffered + amount;
 }
 
-function getBudgetTable(): string {
+type BudgetTable = 'reflect_budgets' | 'zero_budgets';
+
+function getBudgetTable(): BudgetTable {
   return isReflectBudget() ? 'reflect_budgets' : 'zero_budgets';
 }
 
 export function isReflectBudget(): boolean {
-  const budgetType = db.firstSync(
+  const budgetType = db.firstSync<Pick<db.DbPreference, 'value'>>(
     `SELECT value FROM preferences WHERE id = ?`,
     ['budgetType'],
   );
@@ -59,8 +61,14 @@ type BudgetData = {
   amount: number;
 };
 
-function getBudgetData(table: string, month: string): Promise<BudgetData[]> {
-  return db.all(
+function getBudgetData(
+  table: BudgetTable,
+  month: string,
+): Promise<BudgetData[]> {
+  return db.all<
+    (db.DbZeroBudget | db.DbReflectBudget) &
+      Pick<db.DbViewCategory, 'is_income'>
+  >(
     `
     SELECT b.*, c.is_income FROM v_categories c
     LEFT JOIN ${table} b ON b.category = c.id
@@ -91,7 +99,7 @@ export function getBudget({
   month: string;
 }): number {
   const table = getBudgetTable();
-  const existing = db.firstSync(
+  const existing = db.firstSync<db.DbZeroBudget | db.DbReflectBudget>(
     `SELECT * FROM ${table} WHERE month = ? AND category = ?`,
     [dbMonth(month), category],
   );
@@ -110,10 +118,12 @@ export function setBudget({
   amount = safeNumber(typeof amount === 'number' ? amount : 0);
   const table = getBudgetTable();
 
-  const existing = db.firstSync(
-    `SELECT id FROM ${table} WHERE month = ? AND category = ?`,
-    [dbMonth(month), category],
-  );
+  const existing = db.firstSync<
+    Pick<db.DbZeroBudget | db.DbReflectBudget, 'id'>
+  >(`SELECT id FROM ${table} WHERE month = ? AND category = ?`, [
+    dbMonth(month),
+    category,
+  ]);
   if (existing) {
     return db.update(table, { id: existing.id, amount });
   }
@@ -127,10 +137,12 @@ export function setBudget({
 
 export function setGoal({ month, category, goal, long_goal }): Promise<void> {
   const table = getBudgetTable();
-  const existing = db.firstSync(
-    `SELECT id FROM ${table} WHERE month = ? AND category = ?`,
-    [dbMonth(month), category],
-  );
+  const existing = db.firstSync<
+    Pick<db.DbZeroBudget | db.DbReflectBudget, 'id'>
+  >(`SELECT id FROM ${table} WHERE month = ? AND category = ?`, [
+    dbMonth(month),
+    category,
+  ]);
   if (existing) {
     return db.update(table, {
       id: existing.id,
@@ -148,7 +160,7 @@ export function setGoal({ month, category, goal, long_goal }): Promise<void> {
 }
 
 export function setBuffer(month: string, amount: unknown): Promise<void> {
-  const existing = db.firstSync(
+  const existing = db.firstSync<Pick<db.DbZeroBudget, 'id'>>(
     `SELECT id FROM zero_budget_months WHERE id = ?`,
     [month],
   );
@@ -167,10 +179,12 @@ function setCarryover(
   month: string,
   flag: boolean,
 ): Promise<void> {
-  const existing = db.firstSync(
-    `SELECT id FROM ${table} WHERE month = ? AND category = ?`,
-    [month, category],
-  );
+  const existing = db.firstSync<
+    Pick<db.DbZeroBudget | db.DbReflectBudget, 'id'>
+  >(`SELECT id FROM ${table} WHERE month = ? AND category = ?`, [
+    month,
+    category,
+  ]);
   if (existing) {
     return db.update(table, { id: existing.id, carryover: flag ? 1 : 0 });
   }
@@ -225,7 +239,7 @@ export async function copySinglePreviousMonth({
 }
 
 export async function setZero({ month }: { month: string }): Promise<void> {
-  const categories = await db.all(
+  const categories = await db.all<db.DbViewCategory>(
     'SELECT * FROM v_categories WHERE tombstone = 0',
   );
 
@@ -244,7 +258,7 @@ export async function set3MonthAvg({
 }: {
   month: string;
 }): Promise<void> {
-  const categories = await db.all(
+  const categories = await db.all<db.DbViewCategory>(
     'SELECT * FROM v_categories WHERE tombstone = 0',
   );
 
@@ -287,7 +301,7 @@ export async function set12MonthAvg({
 }: {
   month: string;
 }): Promise<void> {
-  const categories = await db.all(
+  const categories = await db.all<db.DbViewCategory>(
     'SELECT * FROM v_categories WHERE tombstone = 0',
   );
 
@@ -306,7 +320,7 @@ export async function set6MonthAvg({
 }: {
   month: string;
 }): Promise<void> {
-  const categories = await db.all(
+  const categories = await db.all<db.DbViewCategory>(
     'SELECT * FROM v_categories WHERE tombstone = 0',
   );
 
@@ -329,7 +343,7 @@ export async function setNMonthAvg({
   N: number;
   category: string;
 }): Promise<void> {
-  const categoryFromDb = await db.first(
+  const categoryFromDb = await db.first<Pick<db.DbViewCategory, 'is_income'>>(
     'SELECT is_income FROM v_categories WHERE id = ?',
     [category],
   );
@@ -361,7 +375,7 @@ export async function holdForNextMonth({
   month: string;
   amount: number;
 }): Promise<boolean> {
-  const row = await db.first(
+  const row = await db.first<Pick<db.DbZeroBudgetMonth, 'buffered'>>(
     'SELECT buffered FROM zero_budget_months WHERE id = ?',
     [month],
   );
@@ -547,9 +561,10 @@ async function addMovementNotes({
 
   const monthBudgetNotesId = `budget-${month}`;
   const existingMonthBudgetNotes = addNewLine(
-    db.firstSync(`SELECT n.note FROM notes n WHERE n.id = ?`, [
-      monthBudgetNotesId,
-    ])?.note,
+    db.firstSync<Pick<db.DbNote, 'note'>>(
+      `SELECT n.note FROM notes n WHERE n.id = ?`,
+      [monthBudgetNotesId],
+    )?.note,
   );
 
   const displayDay = monthUtils.format(monthUtils.currentDate(), 'MMMM dd');

packages/loot-core/src/server/budget/base.ts

@@ -2,6 +2,7 @@
 import * as monthUtils from '../../shared/months';
 import { getChangedValues } from '../../shared/util';
 import * as db from '../db';
+import { categoryGroupModel } from '../models';
 import * as sheet from '../sheet';
 import { resolveName } from '../spreadsheet/util';
 
@@ -391,7 +392,8 @@ export async function doTransfer(categoryIds, transferId) {
 
 export async function createBudget(months) {
   const categories = await db.getCategories();
-  const groups = await db.getCategoriesGrouped();
+  const grouped = await db.getCategoriesGrouped();
+  const groups = categoryGroupModel.fromDbArray(grouped);
 
   sheet.startTransaction();
   const meta = sheet.get().meta();
@@ -442,7 +444,7 @@ export async function createBudget(months) {
 }
 
 export async function createAllBudgets() {
-  const earliestTransaction = await db.first(
+  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 =

packages/loot-core/src/server/budget/cleanup-template.ts

@@ -64,7 +64,7 @@ async function applyGroupCleanups(
         );
         const to_budget = budgeted + Math.abs(balance);
         const categoryId = generalGroup[ii].category;
-        let carryover = await db.first(
+        let carryover = await db.first<Pick<db.DbZeroBudget, 'carryover'>>(
           `SELECT carryover FROM zero_budgets WHERE month = ? and category = ?`,
           [db_month, categoryId],
         );
@@ -132,7 +132,7 @@ async function processCleanup(month: string): Promise<Notification> {
   const db_month = parseInt(month.replace('-', ''));
 
   const category_templates = await getCategoryTemplates();
-  const categories = await db.all(
+  const categories = await db.all<db.DbViewCategory>(
     'SELECT * FROM v_categories WHERE tombstone = 0',
   );
   const sheetName = monthUtils.sheetForMonth(month);
@@ -220,7 +220,7 @@ async function processCleanup(month: string): Promise<Notification> {
         } else {
           warnings.push(category.name + ' does not have available funds.');
         }
-        const carryover = await db.first(
+        const carryover = await db.first<Pick<db.DbZeroBudget, 'carryover'>>(
           `SELECT carryover FROM zero_budgets WHERE month = ? and category = ?`,
           [db_month, category.id],
         );
@@ -249,7 +249,7 @@ async function processCleanup(month: string): Promise<Notification> {
     const budgeted = await getSheetValue(sheetName, `budget-${category.id}`);
     const to_budget = budgeted + Math.abs(balance);
     const categoryId = category.id;
-    let carryover = await db.first(
+    let carryover = await db.first<Pick<db.DbZeroBudget, 'carryover'>>(
       `SELECT carryover FROM zero_budgets WHERE month = ? and category = ?`,
       [db_month, categoryId],
     );
@@ -364,7 +364,7 @@ const TEMPLATE_PREFIX = '#cleanup ';
 async function getCategoryTemplates() {
   const templates = {};
 
-  const notes = await db.all(
+  const notes = await db.all<db.DbNote>(
     `SELECT * FROM notes WHERE lower(note) like '%${TEMPLATE_PREFIX}%'`,
   );
 

packages/loot-core/src/server/budget/goalsSchedule.ts

@@ -21,8 +21,10 @@ async function createScheduleList(
   const errors = [];
 
   for (let ll = 0; ll < template.length; ll++) {
-    const { id: sid, completed: complete } = await db.first(
-      'SELECT * FROM schedules WHERE TRIM(name) = ? AND tombstone = 0',
+    const { id: sid, completed: complete } = await db.first<
+      Pick<db.DbSchedule, 'id' | 'completed'>
+    >(
+      'SELECT id, completed FROM schedules WHERE TRIM(name) = ? AND tombstone = 0',
       [template[ll].name.trim()],
     );
     const rule = await getRuleForSchedule(sid);

packages/loot-core/src/server/budget/goaltemplates.ts

@@ -1,7 +1,6 @@
 // @ts-strict-ignore
 import { Notification } from '../../client/state-types/notifications';
 import * as monthUtils from '../../shared/months';
-import { CategoryEntity } from '../../types/models';
 import * as db from '../db';
 import { batchMessages } from '../sync';
 
@@ -26,7 +25,7 @@ export async function overwriteTemplate({ month }): Promise<Notification> {
 export async function applyMultipleCategoryTemplates({ month, categoryIds }) {
   const placeholders = categoryIds.map(() => '?').join(', ');
   const query = `SELECT * FROM v_categories WHERE id IN (${placeholders})`;
-  const categories = await db.all(query, categoryIds);
+  const categories = await db.all<db.DbViewCategory>(query, categoryIds);
   await storeTemplates();
   const categoryTemplates = await getTemplates(categories);
   const ret = await processTemplate(month, true, categoryTemplates, categories);
@@ -34,9 +33,10 @@ export async function applyMultipleCategoryTemplates({ month, categoryIds }) {
 }
 
 export async function applySingleCategoryTemplate({ month, category }) {
-  const categories = await db.all(`SELECT * FROM v_categories WHERE id = ?`, [
-    category,
-  ]);
+  const categories = await db.all<db.DbViewCategory>(
+    `SELECT * FROM v_categories WHERE id = ?`,
+    [category],
+  );
   await storeTemplates();
   const categoryTemplates = await getTemplates(categories[0]);
   const ret = await processTemplate(month, true, categoryTemplates, categories);
@@ -47,8 +47,8 @@ export function runCheckTemplates() {
   return checkTemplates();
 }
 
-async function getCategories(): Promise<CategoryEntity[]> {
-  return await db.all(
+async function getCategories(): Promise<db.DbCategory[]> {
+  return await db.all<db.DbCategory>(
     `
     SELECT categories.* FROM categories
     INNER JOIN category_groups on categories.cat_group = category_groups.id
@@ -60,7 +60,7 @@ async function getCategories(): Promise<CategoryEntity[]> {
 
 async function getTemplates(category) {
   //retrieves template definitions from the database
-  const goalDef = await db.all(
+  const goalDef = await db.all<db.DbCategory>(
     'SELECT * FROM categories WHERE goal_def IS NOT NULL',
   );
 

packages/loot-core/src/server/budget/statements.ts

@@ -28,7 +28,9 @@ export type CategoryWithTemplateNote = {
 export async function getCategoriesWithTemplateNotes(): Promise<
   CategoryWithTemplateNote[]
 > {
-  return await db.all(
+  return await db.all<
+    Pick<db.DbNote, 'note'> & Pick<db.DbCategory, 'id' | 'name'>
+  >(
     `
       SELECT c.id AS id, c.name as name, n.note AS note
       FROM notes n
@@ -42,7 +44,18 @@ export async function getCategoriesWithTemplateNotes(): Promise<
 }
 
 export async function getActiveSchedules(): Promise<DbSchedule[]> {
-  return await db.all(
+  return await db.all<
+    Pick<
+      DbSchedule,
+      | 'id'
+      | 'rule'
+      | 'active'
+      | 'completed'
+      | 'posts_transaction'
+      | 'tombstone'
+      | 'name'
+    >
+  >(
     'SELECT id, rule, active, completed, posts_transaction, tombstone, name from schedules WHERE name NOT NULL AND tombstone = 0',
   );
 }

packages/loot-core/src/server/dashboard/app.ts

@@ -5,7 +5,6 @@ import * as fs from '../../platform/server/fs';
 import { DEFAULT_DASHBOARD_STATE } from '../../shared/dashboard';
 import { q } from '../../shared/query';
 import {
-  type CustomReportEntity,
   type ExportImportDashboard,
   type ExportImportDashboardWidget,
   type ExportImportCustomReportWidget,
@@ -144,7 +143,9 @@ async function addDashboardWidget(
   // If no x & y was provided - calculate it dynamically
   // The new widget should be the very last one in the list of all widgets
   if (!('x' in widget) && !('y' in widget)) {
-    const data = await db.first(
+    const data = await db.first<
+      Pick<db.DbDashboard, 'x' | 'y' | 'width' | 'height'>
+    >(
       'SELECT x, y, width, height FROM dashboard WHERE tombstone = 0 ORDER BY y DESC, x DESC',
     );
 
@@ -176,7 +177,7 @@ async function importDashboard({ filepath }: { filepath: string }) {
 
     exportModel.validate(parsedContent);
 
-    const customReportIds: CustomReportEntity[] = await db.all(
+    const customReportIds = await db.all<Pick<db.DbCustomReport, 'id'>>(
       'SELECT id from custom_reports',
     );
     const customReportIdSet = new Set(customReportIds.map(({ id }) => id));

packages/loot-core/src/server/db/index.ts

@@ -15,7 +15,6 @@ import * as fs from '../../platform/server/fs';
 import * as sqlite from '../../platform/server/sqlite';
 import * as monthUtils from '../../shared/months';
 import { groupById } from '../../shared/util';
-import { CategoryEntity, CategoryGroupEntity } from '../../types/models';
 import {
   schema,
   schemaConfig,
@@ -35,11 +34,16 @@ import { sendMessages, batchMessages } from '../sync';
 import { shoveSortOrders, SORT_INCREMENT } from './sort';
 import {
   DbAccount,
+  DbBank,
   DbCategory,
   DbCategoryGroup,
+  DbCategoryMapping,
+  DbClockMessage,
   DbPayee,
+  DbPayeeMapping,
   DbTransaction,
   DbViewTransaction,
+  DbViewTransactionInternalAlive,
 } from './types';
 
 export * from './types';
@@ -83,7 +87,7 @@ export function getDatabase() {
 }
 
 export async function loadClock() {
-  const row = await first('SELECT * FROM messages_clock');
+  const row = await first<DbClockMessage>('SELECT * FROM messages_clock');
   if (row) {
     const clock = deserializeClock(row.clock);
     setClock(clock);
@@ -159,29 +163,20 @@ export function asyncTransaction(fn: () => Promise<void>) {
 // 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(sql, params?: (string | number)[]) {
-  // TODO: In the next phase, we will make this function generic
-  // and pass the type of the return type to `runQuery`.
-  // eslint-disable-next-line @typescript-eslint/no-explicit-any
-  return runQuery(sql, params, true) as any[];
+export async function all<T>(sql, params?: (string | number)[]) {
+  return runQuery<T>(sql, params, true);
 }
 
-export async function first(sql, params?: (string | number)[]) {
-  const arr = await runQuery(sql, params, true);
-  // TODO: In the next phase, we will make this function generic
-  // and pass the type of the return type to `runQuery`.
-  // eslint-disable-next-line @typescript-eslint/no-explicit-any
-  return arr.length === 0 ? null : (arr[0] as any);
+export async function first<T>(sql, params?: (string | number)[]) {
+  const arr = await 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(sql, params?: (string | number)[]) {
-  const arr = runQuery(sql, params, true);
-  // TODO: In the next phase, we will make this function generic
-  // and pass the type of the return type to `runQuery`.
-  // eslint-disable-next-line @typescript-eslint/no-explicit-any
-  return arr.length === 0 ? null : (arr[0] as any);
+export function firstSync<T>(sql, params?: (string | number)[]) {
+  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
@@ -191,16 +186,16 @@ export async function run(sql, params?: (string | number)[]) {
   return runQuery(sql, params);
 }
 
-export async function select(table, id) {
-  const rows = await runQuery(
+export async function select<T extends { id: string }>(
+  table: string,
+  id: T['id'],
+) {
+  const rows = await runQuery<T>(
     '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`.
-  // eslint-disable-next-line @typescript-eslint/no-explicit-any
-  return rows[0] as any;
+  return rows[0];
 }
 
 export async function update(table, params) {
@@ -269,24 +264,51 @@ export async function delete_(table, id) {
 }
 
 export async function deleteAll(table: string) {
-  const rows: Array<{ id: string }> = await all(`
+  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 = await runQuery(sql, params, true);
+/**
+ * AQL-schema aware version of `select` that converts the query results
+ * according to the schema config.
+ *
+ * @param table The name of the table in the AQL schema.
+ * @param sql The SQL query.
+ * @param params The parameters for the SQL query.
+ * @returns The results of the query, converted according to the AQL schema config.
+ */
+export async function selectWithSchema<T, TAfterConvert = T>(
+  table: keyof typeof schema,
+  sql: string,
+  params?: Array<string | number> | undefined,
+) {
+  const rows = await runQuery<T>(sql, params, true);
   const convertedRows = rows
-    .map(row => convertFromSelect(schema, schemaConfig, table, row))
+    .map(
+      row =>
+        convertFromSelect(schema, schemaConfig, table, row) as TAfterConvert,
+    )
     .filter(Boolean);
-  // TODO: Make convertFromSelect generic so we don't need this cast
-  // eslint-disable-next-line @typescript-eslint/no-explicit-any
-  return convertedRows as any[];
+  return convertedRows;
 }
 
-export async function selectFirstWithSchema(table, sql, params) {
-  const rows = await selectWithSchema(table, sql, params);
+/**
+ * AQL-schema aware version of `first` that converts the query results
+ * according to the schema config.
+ *
+ * @param table The name of the table in the AQL schema.
+ * @param sql The SQL query.
+ * @param params The parameters for the SQL query.
+ * @returns The first result of the query, converted according to the AQL schema config.
+ */
+export async function selectFirstWithSchema<T, TAfterConvert = T>(
+  table: keyof typeof schema,
+  sql: string,
+  params?: Array<string | number> | undefined,
+) {
+  const rows = await selectWithSchema<T, TAfterConvert>(table, sql, params);
   return rows.length > 0 ? rows[0] : null;
 }
 
@@ -310,19 +332,25 @@ export function updateWithSchema(table, fields) {
 // Data-specific functions. Ideally this would be split up into
 // different files
 
-// TODO: Fix return type. This should returns a DbCategory[].
 export async function getCategories(
   ids?: Array<DbCategory['id']>,
-): Promise<CategoryEntity[]> {
+): 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(query, [...ids]) : await all(query);
+  return ids
+    ? await all<DbCategory>(query, [...ids])
+    : await all<DbCategory>(query);
 }
 
-// TODO: Fix return type. This should returns a [DbCategoryGroup, ...DbCategory].
+/**
+ * Get all categories grouped by their category group.
+ * @param ids The IDs of the category groups to get.
+ * @returns The categories grouped by their category group.
+ * The first element of each tuple is the category group, and the rest are the categories that belong to that group.
+ */
 export async function getCategoriesGrouped(
   ids?: Array<DbCategoryGroup['id']>,
-): Promise<Array<CategoryGroupEntity>> {
+): Promise<Array<[DbCategoryGroup, ...DbCategory[]]>> {
   const categoryGroupWhereIn = ids
     ? `cg.id IN (${toSqlQueryParameters(ids)}) AND`
     : '';
@@ -336,24 +364,23 @@ export async function getCategoriesGrouped(
     ORDER BY c.sort_order, c.id`;
 
   const groups = ids
-    ? await all(categoryGroupQuery, [...ids])
-    : await all(categoryGroupQuery);
+    ? await all<DbCategoryGroup>(categoryGroupQuery, [...ids])
+    : await all<DbCategoryGroup>(categoryGroupQuery);
 
   const categories = ids
-    ? await all(categoryQuery, [...ids])
-    : await all(categoryQuery);
+    ? await all<DbCategory>(categoryQuery, [...ids])
+    : await all<DbCategory>(categoryQuery);
 
   return groups.map(group => {
-    return {
-      ...group,
-      categories: categories.filter(c => c.cat_group === group.id),
-    };
+    return [group, ...categories.filter(c => c.cat_group === group.id)];
   });
 }
 
 export async function insertCategoryGroup(group) {
   // Don't allow duplicate group
-  const existingGroup = await first(
+  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()],
   );
@@ -363,7 +390,7 @@ export async function insertCategoryGroup(group) {
     );
   }
 
-  const lastGroup = await first(`
+  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;
@@ -381,7 +408,7 @@ export function updateCategoryGroup(group) {
 }
 
 export async function moveCategoryGroup(id, targetId) {
-  const groups = await all(
+  const groups = await all<Pick<DbCategoryGroup, 'id' | 'sort_order'>>(
     `SELECT id, sort_order FROM category_groups WHERE tombstone = 0 ORDER BY sort_order, id`,
   );
 
@@ -393,9 +420,10 @@ export async function moveCategoryGroup(id, targetId) {
 }
 
 export async function deleteCategoryGroup(group, transferId?: string) {
-  const categories = await all('SELECT * FROM categories WHERE cat_group = ?', [
-    group.id,
-  ]);
+  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)));
@@ -411,7 +439,7 @@ export async function insertCategory(
   let id_;
   await batchMessages(async () => {
     // Dont allow duplicated names in groups
-    const existingCatInGroup = await first(
+    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()],
     );
@@ -422,14 +450,14 @@ export async function insertCategory(
     }
 
     if (atEnd) {
-      const lastCat = await first(`
+      const lastCat = await first<Pick<DbCategoryGroup, '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(
+      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],
       );
@@ -471,7 +499,7 @@ export async function moveCategory(
     throw new Error('moveCategory: groupId is required');
   }
 
-  const categories = await all(
+  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],
   );
@@ -491,7 +519,7 @@ export async function deleteCategory(
     // 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(
+    const existingTransfers = await all<DbCategoryMapping>(
       'SELECT * FROM category_mapping WHERE transferId = ?',
       [category.id],
     );
@@ -507,11 +535,11 @@ export async function deleteCategory(
 }
 
 export async function getPayee(id: DbPayee['id']) {
-  return first(`SELECT * FROM payees WHERE id = ?`, [id]);
+  return first<DbPayee>(`SELECT * FROM payees WHERE id = ?`, [id]);
 }
 
 export async function getAccount(id: DbAccount['id']) {
-  return first(`SELECT * FROM accounts WHERE id = ?`, [id]);
+  return first<DbAccount>(`SELECT * FROM accounts WHERE id = ?`, [id]);
 }
 
 export async function insertPayee(payee) {
@@ -525,9 +553,10 @@ export async function insertPayee(payee) {
 }
 
 export async function deletePayee(payee: Pick<DbPayee, 'id'>) {
-  const { transfer_acct } = await first('SELECT * FROM payees WHERE id = ?', [
-    payee.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;
@@ -558,7 +587,7 @@ export async function mergePayees(
   ids: Array<DbPayee['id']>,
 ) {
   // Load in payees so we can check some stuff
-  const dbPayees: DbPayee[] = await all('SELECT * FROM payees');
+  const dbPayees = await all<DbPayee>('SELECT * FROM payees');
   const payees = groupById(dbPayees);
 
   // Filter out any transfer payees
@@ -570,7 +599,7 @@ export async function mergePayees(
   await batchMessages(async () => {
     await Promise.all(
       ids.map(async id => {
-        const mappings = await all(
+        const mappings = await all<Pick<DbPayeeMapping, 'id'>>(
           'SELECT id FROM payee_mapping WHERE targetId = ?',
           [id],
         );
@@ -594,7 +623,7 @@ export async function mergePayees(
 }
 
 export function getPayees() {
-  return all(`
+  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)
@@ -607,7 +636,14 @@ export function getCommonPayees() {
     monthUtils.subWeeks(monthUtils.currentDate(), 12),
   );
   const limit = 10;
-  return all(`
+  return all<
+    Pick<DbPayee, 'id' | 'name' | 'favorite' | 'category'> & {
+      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,
@@ -645,23 +681,28 @@ const orphanedPayeesQuery = `
 /* eslint-enable rulesdir/typography */
 
 export function syncGetOrphanedPayees() {
-  return all(orphanedPayeesQuery);
+  return all<Pick<DbPayee, 'id'>>(orphanedPayeesQuery);
 }
 
 export async function getOrphanedPayees() {
-  const rows = await all(orphanedPayeesQuery);
+  const rows = await all<Pick<DbPayee, 'id'>>(orphanedPayeesQuery);
   return rows.map(row => row.id);
 }
 
 export async function getPayeeByName(name: DbPayee['name']) {
-  return first(
+  return first<DbPayee>(
     `SELECT * FROM payees WHERE UNICODE_LOWER(name) = ? AND tombstone = 0`,
     [name.toLowerCase()],
   );
 }
 
 export function getAccounts() {
-  return all(
+  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
@@ -670,7 +711,7 @@ export function getAccounts() {
 }
 
 export async function insertAccount(account) {
-  const accounts = await all(
+  const accounts = await all<DbAccount>(
     'SELECT * FROM accounts WHERE offbudget = ? ORDER BY sort_order, name',
     [account.offbudget ? 1 : 0],
   );
@@ -695,8 +736,11 @@ export async function moveAccount(
   id: DbAccount['id'],
   targetId: DbAccount['id'],
 ) {
-  const account = await first('SELECT * FROM accounts WHERE id = ?', [id]);
-  let accounts;
+  const account = await first<DbAccount>(
+    'SELECT * FROM accounts WHERE id = ?',
+    [id],
+  );
+  let accounts: Pick<DbAccount, 'id' | 'sort_order'>[];
   if (account.closed) {
     accounts = await all(
       `SELECT id, sort_order FROM accounts WHERE closed = 1 ORDER BY sort_order, name`,
@@ -718,7 +762,7 @@ export async function moveAccount(
 }
 
 export async function getTransaction(id: DbViewTransaction['id']) {
-  const rows = await selectWithSchema(
+  const rows = await selectWithSchema<DbViewTransaction>(
     'transactions',
     'SELECT * FROM v_transactions WHERE id = ?',
     [id],
@@ -733,7 +777,7 @@ export async function getTransactions(accountId: DbTransaction['acct']) {
     );
   }
 
-  return selectWithSchema(
+  return selectWithSchema<DbViewTransaction>(
     'transactions',
     'SELECT * FROM v_transactions WHERE account = ?',
     [accountId],

packages/loot-core/src/server/db/mappings.ts

@@ -22,12 +22,12 @@ let unlistenSync;
 export async function loadMappings() {
   // The mappings are separated into tables specific to the type of
   // data. But you know, we really could keep a global mapping table.
-  const categories = (await db.all('SELECT * FROM category_mapping')).map(
-    r => [r.id, r.transferId] as const,
-  );
-  const payees = (await db.all('SELECT * FROM payee_mapping')).map(
-    r => [r.id, r.targetId] as const,
-  );
+  const categories = (
+    await db.all<db.DbCategoryMapping>('SELECT * FROM category_mapping')
+  ).map(r => [r.id, r.transferId] as const);
+  const payees = (
+    await db.all<db.DbPayeeMapping>('SELECT * FROM payee_mapping')
+  ).map(r => [r.id, r.targetId] as const);
 
   // All ids are unique, so we can just keep a global table of mappings
   allMappings = new Map(categories.concat(payees));

packages/loot-core/src/server/db/util.ts

@@ -1,13 +1,17 @@
 // @ts-strict-ignore
-export async function incrFetch(
-  runQuery,
-  terms,
-  compare,
-  makeQuery,
-  params = [],
+export async function incrFetch<T>(
+  runQuery: (
+    query: string,
+    params?: Array<string | number> | undefined,
+    fetchAll?: true,
+  ) => Promise<T[]>,
+  terms: string[],
+  compare: (id: string) => string,
+  makeQuery: (sqlFilter: string) => string,
+  params: Array<string | number> | undefined = [],
 ) {
   const pageCount = 500;
-  let results = [];
+  let results: T[] = [];
 
   let fetchedIds = new Set();
 

packages/loot-core/src/server/filters/app.ts

@@ -42,7 +42,7 @@ const filterModel = {
 };
 
 async function filterNameExists(name, filterId, newItem) {
-  const idForName = await db.first(
+  const idForName = await db.first<Pick<db.DbTransactionFilter, 'id'>>(
     'SELECT id from transaction_filters WHERE tombstone = 0 AND name = ?',
     [name],
   );

packages/loot-core/src/server/main.test.ts

@@ -68,7 +68,9 @@ describe('Budgets', () => {
 
     // Grab the clock to compare later
     await db.openDatabase('test-budget');
-    const row = await db.first('SELECT * FROM messages_clock');
+    const row = await db.first<db.DbClockMessage>(
+      'SELECT * FROM messages_clock',
+    );
 
     const { error } = await runHandler(handlers['load-budget'], {
       id: 'test-budget',
@@ -134,7 +136,7 @@ describe('Accounts', () => {
       date: '2017-01-01',
     });
     const differ = expectSnapshotWithDiffer(
-      await db.all('SELECT * FROM transactions'),
+      await db.all<db.DbTransaction>('SELECT * FROM transactions'),
     );
 
     let transaction = await db.getTransaction(id);
@@ -143,11 +145,15 @@ describe('Accounts', () => {
       payee: 'transfer-three',
       date: '2017-01-03',
     });
-    differ.expectToMatchDiff(await db.all('SELECT * FROM transactions'));
+    differ.expectToMatchDiff(
+      await db.all<db.DbTransaction>('SELECT * FROM transactions'),
+    );
 
     transaction = await db.getTransaction(id);
     await runHandler(handlers['transaction-delete'], transaction);
-    differ.expectToMatchDiff(await db.all('SELECT * FROM transactions'));
+    differ.expectToMatchDiff(
+      await db.all<db.DbTransaction>('SELECT * FROM transactions'),
+    );
   });
 });
 

packages/loot-core/src/server/main.ts

@@ -41,6 +41,11 @@ import { APIError } from './errors';
 import { app as filtersApp } from './filters/app';
 import { handleBudgetImport } from './importers';
 import { app } from './main-app';
+import {
+  categoryGroupModel,
+  categoryModel,
+  payeeModel,
+} from './models';
 import { mutator, runHandler } from './mutators';
 import { app as notesApp } from './notes/app';
 import * as Platform from './platform';
@@ -104,8 +109,8 @@ handlers['redo'] = mutator(function () {
 
 handlers['get-categories'] = async function () {
   return {
-    grouped: await db.getCategoriesGrouped(),
-    list: await db.getCategories(),
+    grouped: categoryGroupModel.fromDbArray(await db.getCategoriesGrouped()),
+    list: categoryModel.fromDbArray(await db.getCategories()),
   };
 };
 
@@ -114,7 +119,8 @@ handlers['get-budget-bounds'] = async function () {
 };
 
 handlers['envelope-budget-month'] = async function ({ month }) {
-  const groups = await db.getCategoriesGrouped();
+  const grouped = await db.getCategoriesGrouped();
+  const groups = categoryGroupModel.fromDbArray(grouped);
   const sheetName = monthUtils.sheetForMonth(month);
 
   function value(name) {
@@ -166,7 +172,8 @@ handlers['envelope-budget-month'] = async function ({ month }) {
 };
 
 handlers['tracking-budget-month'] = async function ({ month }) {
-  const groups = await db.getCategoriesGrouped();
+  const grouped = await db.getCategoriesGrouped();
+  const groups = categoryGroupModel.fromDbArray(grouped);
   const sheetName = monthUtils.sheetForMonth(month);
 
   function value(name) {
@@ -259,7 +266,7 @@ handlers['category-delete'] = mutator(async function ({ id, transferId }) {
   return withUndo(async () => {
     let result = {};
     await batchMessages(async () => {
-      const row = await db.first(
+      const row = await db.first<Pick<db.DbCategory, 'is_income'>>(
         'SELECT is_income FROM categories WHERE id = ?',
         [id],
       );
@@ -270,9 +277,10 @@ handlers['category-delete'] = mutator(async function ({ id, transferId }) {
 
       const transfer =
         transferId &&
-        (await db.first('SELECT is_income FROM categories WHERE id = ?', [
-          transferId,
-        ]));
+        (await db.first<Pick<db.DbCategory, 'is_income'>>(
+          'SELECT is_income FROM categories WHERE id = ?',
+          [transferId],
+        ));
 
       if (!row || (transferId && !transfer)) {
         result = { error: 'no-categories' };
@@ -298,7 +306,8 @@ handlers['category-delete'] = mutator(async function ({ id, transferId }) {
 });
 
 handlers['get-category-groups'] = async function () {
-  return await db.getCategoriesGrouped();
+  const grouped = await db.getCategoriesGrouped();
+  return categoryGroupModel.fromDbArray(grouped);
 };
 
 handlers['category-group-create'] = mutator(async function ({
@@ -335,7 +344,7 @@ handlers['category-group-delete'] = mutator(async function ({
   transferId,
 }) {
   return withUndo(async () => {
-    const groupCategories = await db.all(
+    const groupCategories = await db.all<Pick<db.DbCategory, 'id'>>(
       'SELECT id FROM categories WHERE cat_group = ? AND tombstone = 0',
       [id],
     );
@@ -388,11 +397,11 @@ handlers['common-payees-get'] = async function () {
 };
 
 handlers['payees-get'] = async function () {
-  return db.getPayees();
+  return payeeModel.fromDbArray(await db.getPayees());
 };
 
 handlers['payees-get-orphaned'] = async function () {
-  return db.syncGetOrphanedPayees();
+  return await db.syncGetOrphanedPayees();
 };
 
 handlers['payees-get-rule-counts'] = async function () {
@@ -1504,9 +1513,10 @@ async function loadBudget(id: string) {
 
   // This is a bit leaky, but we need to set the initial budget type
   const { value: budgetType = 'rollover' } =
-    (await db.first('SELECT value from preferences WHERE id = ?', [
-      'budgetType',
-    ])) ?? {};
+    (await db.first<Pick<db.DbPreference, 'value'>>(
+      'SELECT value from preferences WHERE id = ?',
+      ['budgetType'],
+    )) ?? {};
   sheet.get().meta().budgetType = budgetType;
   await budget.createAllBudgets();
 

packages/loot-core/src/server/migrate/migrations.test.ts

@@ -62,14 +62,14 @@ describe('Migrations', () => {
     return withMigrationsDir(
       __dirname + '/../../mocks/migrations',
       async () => {
-        let desc = await db.first(
+        let desc = await db.first<{ sql: string }>(
           "SELECT * FROM sqlite_master WHERE name = 'poop'",
         );
         expect(desc).toBe(null);
 
         await migrate(db.getDatabase());
 
-        desc = await db.first(
+        desc = await db.first<{ sql: string }>(
           "SELECT * FROM sqlite_master WHERE name = 'poop'",
         );
         expect(desc).toBeDefined();

packages/loot-core/src/server/models.ts

@@ -3,8 +3,17 @@ import {
   CategoryEntity,
   CategoryGroupEntity,
   PayeeEntity,
+  TransactionEntity,
 } from '../types/models';
 
+import {
+  DbAccount,
+  DbCategory,
+  DbCategoryGroup,
+  DbPayee,
+  DbTransaction,
+  DbViewTransactionInternal,
+} from './db';
 import { ValidationError } from './errors';
 
 export function requiredFields<T extends object, K extends keyof T>(
@@ -50,7 +59,7 @@ export function fromDateRepr(number: number) {
 }
 
 export const accountModel = {
-  validate(account: AccountEntity, { update }: { update?: boolean } = {}) {
+  validate(account: Partial<DbAccount>, { update }: { update?: boolean } = {}) {
     requiredFields(
       'account',
       account,
@@ -60,10 +69,55 @@ export const accountModel = {
 
     return account;
   },
+  fromDbArray(accounts: DbAccount[]): AccountEntity[] {
+    return accounts.map(account => accountModel.fromDb(account));
+  },
+  fromDb(account: DbAccount): AccountEntity {
+    return {
+      id: account.id,
+      name: account.name,
+      offbudget: account.offbudget,
+      closed: account.closed,
+      sort_order: account.sort_order,
+      tombstone: account.tombstone,
+      account_id: account.account_id ?? null,
+      account_sync_source: account.account_sync_source ?? null,
+      balance_available: account.balance_available ?? null,
+      balance_current: account.balance_current ?? null,
+      balance_limit: account.balance_limit ?? null,
+      bank: account.bank ?? null,
+      mask: account.mask ?? null,
+      official_name: account.official_name ?? null,
+    } as AccountEntity;
+  },
+  toDb(account: AccountEntity): DbAccount {
+    return {
+      id: account.id,
+      name: account.name,
+      offbudget: account.offbudget ? 1 : 0,
+      closed: account.closed ? 1 : 0,
+      tombstone: account.tombstone ? 1 : 0,
+      sort_order: account.sort_order,
+      account_sync_source: account.account_sync_source,
+      account_id: account.account_id,
+      balance_available: account.balance_available,
+      balance_current: account.balance_current,
+      balance_limit: account.balance_limit,
+      bank: account.bank,
+      mask: account.mask,
+      official_name: account.official_name,
+      // No longer used
+      // type,
+      // subtype,
+    };
+  },
 };
 
 export const categoryModel = {
-  validate(category: CategoryEntity, { update }: { update?: boolean } = {}) {
+  validate(
+    category: Partial<DbCategory>,
+    { update }: { update?: boolean } = {},
+  ) {
     requiredFields(
       'category',
       category,
@@ -72,13 +126,43 @@ export const categoryModel = {
     );
 
     const { sort_order, ...rest } = category;
-    return { ...rest, hidden: rest.hidden ? 1 : 0 };
+    return { ...rest, hidden: rest.hidden ? (1 as const) : (0 as const) };
+  },
+  fromDbArray(categories: DbCategory[]): CategoryEntity[] {
+    return categories.map(category => categoryModel.fromDb(category));
+  },
+  fromDb(category: DbCategory): CategoryEntity {
+    return {
+      id: category.id,
+      name: category.name,
+      is_income: category.is_income === 1,
+      cat_group: category.cat_group,
+      sort_order: category.sort_order,
+      tombstone: category.tombstone === 1,
+      hidden: category.hidden === 1,
+      goal_def: category.goal_def ?? undefined,
+    };
+  },
+  toDb(category: CategoryEntity): DbCategory {
+    if (!category.cat_group) {
+      throw new Error('Category missing cat_group');
+    }
+    return {
+      id: category.id,
+      name: category.name,
+      is_income: category.is_income ? 1 : 0,
+      cat_group: category.cat_group,
+      sort_order: category.sort_order ?? 0,
+      tombstone: category.tombstone ? 1 : 0,
+      hidden: category.hidden ? 1 : 0,
+      goal_def: category.goal_def,
+    };
   },
 };
 
 export const categoryGroupModel = {
   validate(
-    categoryGroup: CategoryGroupEntity,
+    categoryGroup: Partial<DbCategoryGroup>,
     { update }: { update?: boolean } = {},
   ) {
     requiredFields(
@@ -91,11 +175,159 @@ export const categoryGroupModel = {
     const { sort_order, ...rest } = categoryGroup;
     return { ...rest, hidden: rest.hidden ? 1 : 0 };
   },
+  fromDbArray(
+    grouped: [DbCategoryGroup, ...DbCategory[]][],
+  ): CategoryGroupEntity[] {
+    return grouped.map(([group, ...categories]) =>
+      categoryGroupModel.fromDb(group, categories),
+    );
+  },
+  fromDb(
+    categoryGroup: DbCategoryGroup,
+    categories: DbCategory[] = [],
+  ): CategoryGroupEntity {
+    return {
+      id: categoryGroup.id,
+      name: categoryGroup.name,
+      is_income: categoryGroup.is_income === 1,
+      sort_order: categoryGroup.sort_order,
+      hidden: categoryGroup.hidden === 1,
+      tombstone: categoryGroup.tombstone === 1,
+      categories: categories
+        .filter(category => category.cat_group === categoryGroup.id)
+        .map(category => categoryModel.fromDb(category)),
+    };
+  },
 };
 
 export const payeeModel = {
-  validate(payee: PayeeEntity, { update }: { update?: boolean } = {}) {
+  validate(payee: Partial<DbPayee>, { update }: { update?: boolean } = {}) {
     requiredFields('payee', payee, ['name'], update);
     return payee;
   },
+  fromDbArray(payees: DbPayee[]): PayeeEntity[] {
+    return payees.map(payee => payeeModel.fromDb(payee));
+  },
+  fromDb(payee: DbPayee): PayeeEntity {
+    return {
+      id: payee.id,
+      name: payee.name,
+      favorite: payee.favorite,
+      learn_categories: payee.learn_categories,
+      tombstone: payee.tombstone === 1,
+      transfer_acct: payee.transfer_acct ?? undefined,
+    };
+  },
+  toDb(payee: PayeeEntity): DbPayee {
+    return {
+      id: payee.id,
+      name: payee.name,
+      favorite: payee.favorite ? 1 : 0,
+      learn_categories: payee.learn_categories ? 1 : 0,
+      tombstone: payee.tombstone ? 1 : 0,
+      transfer_acct: payee.transfer_acct,
+      // No longer used
+      // category
+    };
+  },
+};
+
+export const transactionModel = {
+  validate(
+    transaction: Partial<DbTransaction>,
+    { update }: { update?: boolean } = {},
+  ) {
+    requiredFields(
+      'transaction',
+      transaction,
+      ['date', 'amount', 'acct'],
+      update,
+    );
+    return transaction;
+  },
+  fromDbView(
+    transaction: DbViewTransactionInternal,
+    subtransactions: DbViewTransactionInternal[] = [],
+  ): TransactionEntity {
+    return {
+      id: transaction.id,
+      date: fromDateRepr(transaction.date),
+      amount: transaction.amount,
+      payee: transaction.payee ?? undefined,
+      account: transaction.account ?? undefined,
+      category: transaction.category ?? undefined,
+      transfer_id: transaction.transfer_id ?? undefined,
+      imported_id: transaction.imported_id ?? undefined,
+      error: transaction.error ? JSON.parse(transaction.error) : undefined,
+      imported_payee: transaction.imported_payee ?? undefined,
+      starting_balance_flag: transaction.starting_balance_flag === 1,
+      notes: transaction.notes ?? undefined,
+      cleared: transaction.cleared === 1,
+      reconciled: transaction.reconciled === 1,
+      subtransactions: subtransactions.map(subtransaction =>
+        transactionModel.fromDbView(subtransaction),
+      ),
+      schedule: transaction.schedule ?? undefined,
+      is_child: transaction.is_child === 1,
+      is_parent: transaction.is_parent === 1,
+      parent_id: transaction.parent_id ?? undefined,
+      sort_order: transaction.sort_order,
+      tombstone: transaction.tombstone === 1,
+    };
+  },
+  fromDb(
+    transaction: DbTransaction,
+    subtransactions: DbTransaction[] = [],
+  ): TransactionEntity {
+    return {
+      id: transaction.id,
+      date: fromDateRepr(transaction.date),
+      amount: transaction.amount,
+      // payee: transaction.payee_id,
+      account: transaction.acct,
+      category: transaction.category ?? undefined,
+      transfer_id: transaction.transferred_id ?? undefined,
+      notes: transaction.notes ?? undefined,
+      cleared: transaction.cleared === 1,
+      reconciled: transaction.reconciled === 1,
+      error: transaction.error ? JSON.parse(transaction.error) : undefined,
+      imported_id: transaction.financial_id ?? undefined,
+      imported_payee: transaction.imported_description ?? undefined,
+      starting_balance_flag: transaction.starting_balance_flag === 1,
+      schedule: transaction.schedule ?? undefined,
+      sort_order: transaction.sort_order,
+      tombstone: transaction.tombstone === 1,
+      is_child: transaction.isChild === 1,
+      is_parent: transaction.isParent === 1,
+      parent_id: transaction.parent_id ?? undefined,
+      subtransactions: subtransactions.map(subtransaction =>
+        transactionModel.fromDb(subtransaction),
+      ),
+      payee: transaction.description ?? undefined,
+    };
+  },
+  toDb(transaction: TransactionEntity): DbTransaction {
+    return {
+      id: transaction.id,
+      date: toDateRepr(transaction.date),
+      amount: transaction.amount,
+      description: transaction.payee,
+      acct: transaction.account,
+      category: transaction.category,
+      transferred_id: transaction.transfer_id,
+      notes: transaction.notes,
+      error: JSON.stringify(transaction.error),
+      financial_id: transaction.imported_id,
+      imported_description: transaction.imported_payee,
+      schedule: transaction.schedule,
+      sort_order: transaction.sort_order ?? 0,
+      tombstone: transaction.tombstone ? 1 : 0,
+      isChild: transaction.is_child ? 1 : 0,
+      isParent: transaction.is_parent ? 1 : 0,
+      parent_id: transaction.parent_id,
+      starting_balance_flag: transaction.starting_balance_flag ? 1 : 0,
+      cleared: transaction.cleared ? 1 : 0,
+      reconciled: transaction.reconciled ? 1 : 0,
+    };
+  },
 };

packages/loot-core/src/server/preferences/app.ts

@@ -19,7 +19,9 @@ const savePreferences = async ({
 };
 
 const getPreferences = async (): Promise<SyncedPrefs> => {
-  const prefs = (await db.all('SELECT id, value FROM preferences')) as Array<{
+  const prefs = (await db.all<Pick<db.DbPreference, 'id' | 'value'>>(
+    'SELECT id, value FROM preferences',
+  )) as Array<{
     id: string;
     value: string;
   }>;

packages/loot-core/src/server/reports/app.ts

@@ -85,7 +85,7 @@ async function reportNameExists(
   reportId: string,
   newItem: boolean,
 ) {
-  const idForName: { id: string } = await db.first(
+  const idForName = await db.first<Pick<db.DbCustomReport, 'id'>>(
     'SELECT id from custom_reports WHERE tombstone = 0 AND name = ?',
     [name],
   );

packages/loot-core/src/server/schedules/app.ts

@@ -140,7 +140,9 @@ export async function setNextDate({
     if (newNextDate !== nextDate) {
       // Our `update` functon requires the id of the item and we don't
       // have it, so we need to query it
-      const nd = await db.first(
+      const nd = await db.first<
+        Pick<db.DbScheduleNextDate, 'id' | 'base_next_date_ts'>
+      >(
         'SELECT id, base_next_date_ts FROM schedules_next_date WHERE schedule_id = ?',
         [id],
       );
@@ -166,7 +168,7 @@ export async function setNextDate({
 // Methods
 
 async function checkIfScheduleExists(name, scheduleId) {
-  const idForName = await db.first(
+  const idForName = await db.first<Pick<db.DbSchedule, 'id'>>(
     'SELECT id from schedules WHERE tombstone = 0 AND name = ?',
     [name],
   );

packages/loot-core/src/server/schedules/find-schedules.ts

@@ -337,7 +337,7 @@ export async function findSchedules() {
 
   for (const account of accounts) {
     // Find latest transaction-ish to start with
-    const latestTrans = await db.first(
+    const latestTrans = await db.first<db.DbViewTransaction>(
       'SELECT * FROM v_transactions WHERE account = ? AND parent_id IS NULL ORDER BY date DESC LIMIT 1',
       [account.id],
     );

packages/loot-core/src/server/sheet.ts

@@ -5,6 +5,12 @@ import { captureBreadcrumb } from '../platform/exceptions';
 import * as sqlite from '../platform/server/sqlite';
 import { sheetForMonth } from '../shared/months';
 
+import {
+  DbPreference,
+  DbReflectBudget,
+  DbZeroBudget,
+  DbZeroBudgetMonth,
+} from './db';
 import * as Platform from './platform';
 import { Spreadsheet } from './spreadsheet/spreadsheet';
 import { resolveName } from './spreadsheet/util';
@@ -189,19 +195,22 @@ export async function reloadSpreadsheet(db): Promise<Spreadsheet> {
   }
 }
 
-export async function loadUserBudgets(db): Promise<void> {
+export async function loadUserBudgets(
+  db: typeof import('./db'),
+): Promise<void> {
   const sheet = globalSheet;
 
   // TODO: Clear out the cache here so make sure future loads of the app
   // don't load any extra values that aren't set here
 
   const { value: budgetType = 'rollover' } =
-    (await db.first('SELECT value from preferences WHERE id = ?', [
-      'budgetType',
-    ])) ?? {};
+    (await db.first<Pick<DbPreference, 'value'>>(
+      'SELECT value from preferences WHERE id = ?',
+      ['budgetType'],
+    )) ?? {};
 
   const table = budgetType === 'report' ? 'reflect_budgets' : 'zero_budgets';
-  const budgets = await db.all(`
+  const budgets = await db.all<DbReflectBudget | DbZeroBudget>(`
       SELECT * FROM ${table} b
       LEFT JOIN categories c ON c.id = b.category
       WHERE c.tombstone = 0
@@ -225,7 +234,9 @@ export async function loadUserBudgets(db): Promise<void> {
 
   // For zero-based budgets, load the buffered amounts
   if (budgetType !== 'report') {
-    const budgetMonths = await db.all('SELECT * FROM zero_budget_months');
+    const budgetMonths = await db.all<DbZeroBudgetMonth>(
+      'SELECT * FROM zero_budget_months',
+    );
     for (const budgetMonth of budgetMonths) {
       const sheetName = sheetForMonth(budgetMonth.id);
       sheet.set(`${sheetName}!buffered`, budgetMonth.buffered);

packages/loot-core/src/server/sync/index.ts

@@ -744,11 +744,13 @@ async function _fullSync(
 
       if (rebuiltMerkle.trie.hash === res.merkle.hash) {
         // Rebuilding the merkle worked... but why?
-        const clocks = await db.all('SELECT * FROM messages_clock');
+        const clocks = await db.all<db.DbClockMessage>(
+          'SELECT * FROM messages_clock',
+        );
         if (clocks.length !== 1) {
           console.log('Bad number of clocks:', clocks.length);
         }
-        const hash = deserializeClock(clocks[0]).merkle.hash;
+        const hash = deserializeClock(clocks[0].clock).merkle.hash;
         console.log('Merkle hash in db:', hash);
       }
 

packages/loot-core/src/server/sync/migrate.test.ts

@@ -117,7 +117,10 @@ describe('sync migrations', () => {
           await sendMessages(msgs);
           await tracer.expect('applied');
 
-          const transactions = await db.all('SELECT * FROM transactions', []);
+          const transactions = await db.all<db.DbTransaction>(
+            'SELECT * FROM transactions',
+            [],
+          );
           for (const trans of transactions) {
             const transMsgs = msgs
               .filter(msg => msg.row === trans.id)

packages/loot-core/src/server/sync/sync.test.ts

@@ -54,8 +54,12 @@ describe('Sync', () => {
     expect(getClock().timestamp.toString()).toEqual(timestamp.toString());
     expect(mockSyncServer.getClock().merkle).toEqual(getClock().merkle);
 
-    expect(await db.all('SELECT * FROM messages_crdt')).toMatchSnapshot();
-    expect(await db.all('SELECT * FROM messages_clock')).toMatchSnapshot();
+    expect(
+      await db.all<db.DbCrdtMessage>('SELECT * FROM messages_crdt'),
+    ).toMatchSnapshot();
+    expect(
+      await db.all<db.DbClockMessage>('SELECT * FROM messages_clock'),
+    ).toMatchSnapshot();
   });
 
   it('should resend old messages to the server', async () => {

packages/loot-core/src/server/tools/app.ts

@@ -13,7 +13,11 @@ export const app = createApp<ToolsHandlers>();
 app.method('tools/fix-split-transactions', async () => {
   // 1. Check for child transactions that have a blank payee, and set
   //    the payee to whatever the parent has
-  const blankPayeeRows = await db.all(`
+  const blankPayeeRows = await db.all<
+    db.DbViewTransactionInternal & {
+      parentPayee: db.DbViewTransactionInternal['payee'];
+    }
+  >(`
     SELECT t.*, p.payee AS parentPayee FROM v_transactions_internal t
     LEFT JOIN v_transactions_internal p ON t.parent_id = p.id
     WHERE t.is_child = 1 AND t.payee IS NULL AND p.payee IS NOT NULL
@@ -29,7 +33,9 @@ app.method('tools/fix-split-transactions', async () => {
 
   // 2. Make sure the "cleared" flag is synced up with the parent
   // transactions
-  const clearedRows = await db.all(`
+  const clearedRows = await db.all<
+    Pick<db.DbViewTransactionInternal, 'id' | 'cleared'>
+  >(`
     SELECT t.id, p.cleared FROM v_transactions_internal t
     LEFT JOIN v_transactions_internal p ON t.parent_id = p.id
     WHERE t.is_child = 1 AND t.cleared != p.cleared
@@ -45,7 +51,7 @@ app.method('tools/fix-split-transactions', async () => {
 
   // 3. Mark the `tombstone` field as true on any child transactions
   //    that have a dead parent
-  const deletedRows = await db.all(`
+  const deletedRows = await db.all<db.DbViewTransactionInternal>(`
     SELECT t.* FROM v_transactions_internal t
     LEFT JOIN v_transactions_internal p ON t.parent_id = p.id
     WHERE t.is_child = 1 AND t.tombstone = 0 AND (p.tombstone = 1 OR p.id IS NULL)
@@ -74,7 +80,9 @@ app.method('tools/fix-split-transactions', async () => {
   });
 
   // 5. Fix transfers that should not have categories
-  const brokenTransfers = await db.all(`
+  const brokenTransfers = await db.all<
+    Pick<db.DbViewTransactionInternal, 'id'>
+  >(`
     SELECT t1.id
     FROM v_transactions_internal t1
            JOIN accounts a1 ON t1.account = a1.id

packages/loot-core/src/server/transactions/index.ts

@@ -12,7 +12,7 @@ import * as transfer from './transfer';
 
 async function idsWithChildren(ids: string[]) {
   const whereIds = whereIn(ids, 'parent_id');
-  const rows = await db.all(
+  const rows = await db.all<db.DbViewTransactionInternal>(
     `SELECT id FROM v_transactions_internal WHERE ${whereIds}`,
   );
   const set = new Set(ids);
@@ -23,13 +23,18 @@ async function idsWithChildren(ids: string[]) {
 }
 
 async function getTransactionsByIds(
-  ids: string[],
-): Promise<TransactionEntity[]> {
+  ids: Array<db.DbTransaction['id']>,
+): Promise<db.DbViewTransactionInternal[]> {
   // TODO: convert to whereIn
   //
   // or better yet, use ActualQL
-  return incrFetch(
-    (query, params) => db.selectWithSchema('transactions', query, params),
+  return incrFetch<db.DbViewTransactionInternal>(
+    (sql, params) =>
+      db.selectWithSchema<db.DbViewTransactionInternal>(
+        'transactions',
+        sql,
+        params,
+      ),
     ids,
     // eslint-disable-next-line rulesdir/typography
     id => `id = '${id}'`,
@@ -57,7 +62,9 @@ export async function batchUpdateTransactions({
     : [];
 
   const oldPayees = new Set<PayeeEntity['id']>();
-  const accounts = await db.all('SELECT * FROM accounts WHERE tombstone = 0');
+  const accounts = await db.all<db.DbAccount>(
+    'SELECT * FROM accounts WHERE tombstone = 0',
+  );
 
   // We need to get all the payees of updated transactions _before_
   // making changes

packages/loot-core/src/server/transactions/transaction-rules.test.ts

@@ -97,7 +97,7 @@ describe('Transaction rules', () => {
       conditions: [],
       actions: [],
     });
-    expect((await db.all('SELECT * FROM rules')).length).toBe(1);
+    expect((await db.all<db.DbRule>('SELECT * FROM rules')).length).toBe(1);
     // Make sure it was projected
     expect(getRules().length).toBe(1);
 
@@ -110,7 +110,7 @@ describe('Transaction rules', () => {
         { op: 'set', field: 'category', value: 'food' },
       ],
     });
-    expect((await db.all('SELECT * FROM rules')).length).toBe(2);
+    expect((await db.all<db.DbRule>('SELECT * FROM rules')).length).toBe(2);
     expect(getRules().length).toBe(2);
 
     const spy = jest.spyOn(console, 'warn').mockImplementation();
@@ -119,7 +119,7 @@ describe('Transaction rules', () => {
     // that will validate the input)
     await db.insertWithUUID('rules', { conditions: '{', actions: '}' });
     // It will be in the database
-    expect((await db.all('SELECT * FROM rules')).length).toBe(3);
+    expect((await db.all<db.DbRule>('SELECT * FROM rules')).length).toBe(3);
     // But it will be ignored
     expect(getRules().length).toBe(2);
 
@@ -943,11 +943,14 @@ describe('Learning categories', () => {
 
     // Internally, it should still be stored with the internal names
     // so that it's backwards compatible
-    const rawRule = await db.first('SELECT * FROM rules');
-    rawRule.conditions = JSON.parse(rawRule.conditions);
-    rawRule.actions = JSON.parse(rawRule.actions);
-    expect(rawRule.conditions[0].field).toBe('imported_description');
-    expect(rawRule.actions[0].field).toBe('description');
+    const rawRule = await db.first<db.DbRule>('SELECT * FROM rules');
+    const parsedRule = {
+      ...rawRule,
+      conditions: JSON.parse(rawRule.conditions),
+      actions: JSON.parse(rawRule.actions),
+    };
+    expect(parsedRule.conditions[0].field).toBe('imported_description');
+    expect(parsedRule.actions[0].field).toBe('description');
 
     await loadRules();
 
@@ -973,11 +976,14 @@ describe('Learning categories', () => {
     // This rule internally has been stored with the public names.
     // Making this work now allows us to switch to it by default in
     // the future
-    const rawRule = await db.first('SELECT * FROM rules');
-    rawRule.conditions = JSON.parse(rawRule.conditions);
-    rawRule.actions = JSON.parse(rawRule.actions);
-    expect(rawRule.conditions[0].field).toBe('imported_payee');
-    expect(rawRule.actions[0].field).toBe('payee');
+    const rawRule = await db.first<db.DbRule>('SELECT * FROM rules');
+    const parsedRule = {
+      ...rawRule,
+      conditions: JSON.parse(rawRule.conditions),
+      actions: JSON.parse(rawRule.actions),
+    };
+    expect(parsedRule.conditions[0].field).toBe('imported_payee');
+    expect(parsedRule.actions[0].field).toBe('payee');
 
     const [rule] = getRules();
     expect(rule.conditions[0].field).toBe('imported_payee');

packages/loot-core/src/server/transactions/transaction-rules.ts

@@ -14,7 +14,6 @@ import {
   type TransactionEntity,
   type RuleActionEntity,
   type RuleEntity,
-  AccountEntity,
 } from '../../types/models';
 import { schemaConfig } from '../aql';
 import * as db from '../db';
@@ -176,7 +175,7 @@ export function makeRule(data) {
 export async function loadRules() {
   resetState();
 
-  const rules = await db.all(`
+  const rules = await db.all<db.DbRule>(`
     SELECT * FROM rules
       WHERE conditions IS NOT NULL AND actions IS NOT NULL AND tombstone = 0
   `);
@@ -220,9 +219,10 @@ export async function updateRule(rule) {
 }
 
 export async function deleteRule(id: string) {
-  const schedule = await db.first('SELECT id FROM schedules WHERE rule = ?', [
-    id,
-  ]);
+  const schedule = await db.first<Pick<db.DbSchedule, 'id'>>(
+    'SELECT id FROM schedules WHERE rule = ?',
+    [id],
+  );
 
   if (schedule) {
     return false;
@@ -278,7 +278,7 @@ function onApplySync(oldValues, newValues) {
 // Runner
 export async function runRules(
   trans,
-  accounts: Map<string, AccountEntity> | null = null,
+  accounts: Map<string, db.DbAccount> | null = null,
 ) {
   let accountsMap = null;
   if (accounts === null) {
@@ -631,13 +631,11 @@ export async function applyActions(
     return null;
   }
 
-  const accounts: AccountEntity[] = await db.getAccounts();
+  const accounts: db.DbAccount[] = await db.getAccounts();
+  const accountsMap = new Map(accounts.map(account => [account.id, account]));
   const transactionsForRules = await Promise.all(
     transactions.map(transactions =>
-      prepareTransactionForRules(
-        transactions,
-        new Map(accounts.map(account => [account.id, account])),
-      ),
+      prepareTransactionForRules(transactions, accountsMap),
     ),
   );
 
@@ -802,7 +800,7 @@ export async function updateCategoryRules(transactions) {
 
   // Also look 180 days in the future to get any future transactions
   // (this might change when we think about scheduled transactions)
-  const register: TransactionEntity[] = await db.all(
+  const register = await db.all<db.DbViewTransaction>(
     `SELECT t.* FROM v_transactions t
      LEFT JOIN accounts a ON a.id = t.account
      LEFT JOIN payees p ON p.id = t.payee
@@ -870,12 +868,12 @@ export async function updateCategoryRules(transactions) {
 
 export type TransactionForRules = TransactionEntity & {
   payee_name?: string;
-  _account?: AccountEntity;
+  _account?: db.DbAccount;
 };
 
 export async function prepareTransactionForRules(
   trans: TransactionEntity,
-  accounts: Map<string, AccountEntity> | null = null,
+  accounts: Map<string, db.DbAccount> | null = null,
 ): Promise<TransactionForRules> {
   const r: TransactionForRules = { ...trans };
   if (trans.payee) {

packages/loot-core/src/server/transactions/transfer.test.ts

@@ -7,7 +7,7 @@ import * as transfer from './transfer';
 beforeEach(global.emptyDatabase());
 
 function getAllTransactions() {
-  return db.all(
+  return db.all<db.DbViewTransaction & { payee_name: db.DbPayee['name'] }>(
     `SELECT t.*, p.name as payee_name
        FROM v_transactions t
        LEFT JOIN payees p ON p.id = t.payee
@@ -64,10 +64,10 @@ describe('Transfer', () => {
 
     const differ = expectSnapshotWithDiffer(await getAllTransactions());
 
-    const transferTwo = await db.first(
+    const transferTwo = await db.first<db.DbPayee>(
       "SELECT * FROM payees WHERE transfer_acct = 'two'",
     );
-    const transferThree = await db.first(
+    const transferThree = await db.first<db.DbPayee>(
       "SELECT * FROM payees WHERE transfer_acct = 'three'",
     );
 
@@ -134,10 +134,10 @@ describe('Transfer', () => {
   test('transfers are properly de-categorized', async () => {
     await prepareDatabase();
 
-    const transferTwo = await db.first(
+    const transferTwo = await db.first<db.DbPayee>(
       "SELECT * FROM payees WHERE transfer_acct = 'two'",
     );
-    const transferThree = await db.first(
+    const transferThree = await db.first<db.DbPayee>(
       "SELECT * FROM payees WHERE transfer_acct = 'three'",
     );
 
@@ -179,8 +179,8 @@ describe('Transfer', () => {
     await prepareDatabase();
 
     const [transferOne, transferTwo] = await Promise.all([
-      db.first("SELECT * FROM payees WHERE transfer_acct = 'one'"),
-      db.first("SELECT * FROM payees WHERE transfer_acct = 'two'"),
+      db.first<db.DbPayee>("SELECT * FROM payees WHERE transfer_acct = 'one'"),
+      db.first<db.DbPayee>("SELECT * FROM payees WHERE transfer_acct = 'two'"),
     ]);
 
     let parent: Transaction = {

packages/loot-core/src/server/transactions/transfer.ts

@@ -2,12 +2,14 @@
 import * as db from '../db';
 
 async function getPayee(acct) {
-  return db.first('SELECT * FROM payees WHERE transfer_acct = ?', [acct]);
+  return db.first<db.DbPayee>('SELECT * FROM payees WHERE transfer_acct = ?', [
+    acct,
+  ]);
 }
 
 async function getTransferredAccount(transaction) {
   if (transaction.payee) {
-    const result = await db.first(
+    const result = await db.first<Pick<db.DbViewPayee, 'transfer_acct'>>(
       'SELECT transfer_acct FROM v_payees WHERE id = ?',
       [transaction.payee],
     );
@@ -18,14 +20,12 @@ async function getTransferredAccount(transaction) {
 }
 
 async function clearCategory(transaction, transferAcct) {
-  const { offbudget: fromOffBudget } = await db.first(
-    'SELECT offbudget FROM accounts WHERE id = ?',
-    [transaction.account],
-  );
-  const { offbudget: toOffBudget } = await db.first(
-    'SELECT offbudget FROM accounts WHERE id = ?',
-    [transferAcct],
-  );
+  const { offbudget: fromOffBudget } = await db.first<
+    Pick<db.DbAccount, 'offbudget'>
+  >('SELECT offbudget FROM accounts WHERE id = ?', [transaction.account]);
+  const { offbudget: toOffBudget } = await db.first<
+    Pick<db.DbAccount, 'offbudget'>
+  >('SELECT offbudget FROM accounts WHERE id = ?', [transferAcct]);
 
   // If the transfer is between two on budget or two off budget accounts,
   // we should clear the category, because the category is not relevant
@@ -51,7 +51,7 @@ export async function addTransfer(transaction, transferredAccount) {
     return null;
   }
 
-  const { id: fromPayee } = await db.first(
+  const { id: fromPayee } = await db.first<Pick<db.DbPayee, 'id'>>(
     'SELECT id FROM payees WHERE transfer_acct = ?',
     [transaction.account],
   );

packages/loot-core/src/server/update.ts

@@ -13,9 +13,10 @@ async function runMigrations() {
 
 async function updateViews() {
   const hashKey = 'view-hash';
-  const row = await db.first('SELECT value FROM __meta__ WHERE key = ?', [
-    hashKey,
-  ]);
+  const row = await db.first<{ value: string }>(
+    'SELECT value FROM __meta__ WHERE key = ?',
+    [hashKey],
+  );
   const { value: hash } = row || {};
 
   const views = makeViews(schema, schemaConfig);

packages/loot-core/src/types/models/category.d.ts

@@ -8,4 +8,5 @@ export interface CategoryEntity {
   sort_order?: number;
   tombstone?: boolean;
   hidden?: boolean;
+  goal_def?: string;
 }

packages/loot-core/src/types/models/index.d.ts

@@ -13,3 +13,4 @@ export type * from './schedule';
 export type * from './transaction';
 export type * from './transaction-filter';
 export type * from './user';
+export type * from './bank';

packages/loot-core/src/types/server-handlers.d.ts

@@ -86,10 +86,12 @@ export interface ServerHandlers {
     updated?;
   }) => Promise<unknown>;
 
-  'payees-check-orphaned': (arg: { ids }) => Promise<unknown>;
-  'payees-get-orphaned': () => Promise<PayeeEntity[]>;
+  'payees-check-orphaned': (arg: {
+    ids: Array<PayeeEntity['id']>;
+  }) => Promise<Array<PayeeEntity['id']>>;
+  'payees-get-orphaned': () => Promise<Array<Pick<PayeeEntity, 'id'>>>;
 
-  'payees-get-rules': (arg: { id: string }) => Promise<RuleEntity[]>;
+  'payees-get-rules': (arg: { id: PayeeEntity['id'] }) => Promise<RuleEntity[]>;
 
   'make-filters-from-conditions': (arg: {
     conditions: unknown;

upcoming-release-notes/4248.md

@@ -0,0 +1,6 @@
+---
+category: Maintenance
+authors: [joel-jeremy]
+---
+
+[TypeScript] Make `db.first` generic to make it easy to type DB query results.

upcoming-release-notes/4249.md

@@ -0,0 +1,6 @@
+---
+category: Maintenance
+authors: [joel-jeremy]
+---
+
+[TypeScript] Make `db.firstSync` generic to make it easy to type DB query results.

upcoming-release-notes/4250.md

@@ -0,0 +1,6 @@
+---
+category: Maintenance
+authors: [joel-jeremy]
+---
+
+[TypeScript] Make `db.all` generic to make it easy to type DB query results.

upcoming-release-notes/4251.md

@@ -0,0 +1,6 @@
+---
+category: Maintenance
+authors: [joel-jeremy]
+---
+
+[TypeScript] Make `db.select` functions generic to make it easy to type DB query results.