DB models

packages/desktop-client/src/components/autocomplete/AccountAutocomplete.tsx

@@ -127,8 +127,8 @@ export function AccountAutocomplete({
     })
     .sort(
       (a, b) =>
-        a.closed - b.closed ||
-        a.offbudget - b.offbudget ||
+        (a.closed ? 1 : 0) - (b.closed ? 1 : 0) ||
+        (a.offbudget ? 1 : 0) - (b.offbudget ? 1 : 0) ||
         a.sort_order - b.sort_order,
     );
 

packages/desktop-client/src/components/autocomplete/PayeeAutocomplete.test.tsx

@@ -39,7 +39,7 @@ function makePayee(name: string, options?: { favorite: boolean }): PayeeEntity {
   return {
     id: name.toLowerCase() + '-id',
     name,
-    favorite: options?.favorite ? 1 : 0,
+    favorite: options?.favorite ? true : false,
     transfer_acct: undefined,
   };
 }

packages/desktop-client/src/components/autocomplete/PayeeAutocomplete.tsx

@@ -319,7 +319,7 @@ export function PayeeAutocomplete({
       return filteredSuggestions;
     }
 
-    return [{ id: 'new', favorite: 0, name: '' }, ...filteredSuggestions];
+    return [{ id: 'new', favorite: false, name: '' }, ...filteredSuggestions];
   }, [commonPayees, payees, focusTransferPayees, accounts, hasPayeeInput]);
 
   const dispatch = useDispatch();

packages/desktop-client/src/components/mobile/accounts/Accounts.tsx

@@ -230,8 +230,8 @@ function AccountList({
   const { t } = useTranslation();
   const failedAccounts = useFailedAccounts();
   const syncingAccountIds = useSelector(state => state.account.accountsSyncing);
-  const onBudgetAccounts = accounts.filter(account => account.offbudget === 0);
-  const offBudgetAccounts = accounts.filter(account => account.offbudget === 1);
+  const onBudgetAccounts = accounts.filter(account => !account.offbudget);
+  const offBudgetAccounts = accounts.filter(account => account.offbudget);
 
   return (
     <Page

packages/desktop-client/src/components/modals/CategoryGroupMenuModal.tsx

@@ -61,7 +61,7 @@ export function CategoryGroupMenuModal({
   };
 
   const _onAddCategory = () => {
-    onAddCategory?.(group.id, group.is_income);
+    onAddCategory?.(group.id, !!group.is_income);
   };
 
   const _onEditNotes = () => {

packages/desktop-client/src/components/modals/CloseAccountModal.tsx

@@ -30,11 +30,11 @@ function needsCategory(
   accounts: AccountEntity[],
 ) {
   const acct = accounts.find(a => a.id === currentTransfer);
-  const isOffBudget = acct && acct.offbudget === 1;
+  const isOffBudget = acct && acct.offbudget;
 
   // The user must select a category if transferring from a budgeted
   // account to an off budget account
-  return account.offbudget === 0 && isOffBudget;
+  return !account.offbudget && isOffBudget;
 }
 
 type CloseAccountModalProps = {
@@ -49,7 +49,7 @@ export function CloseAccountModal({
   canDelete,
 }: CloseAccountModalProps) {
   const { t } = useTranslation(); // Initialize translation hook
-  const accounts = useAccounts().filter(a => a.closed === 0);
+  const accounts = useAccounts().filter(a => !a.closed);
   const { grouped: categoryGroups, list: categories } = useCategories();
   const [loading, setLoading] = useState(false);
   const [transferAccountId, setTransferAccountId] = useState('');

packages/desktop-client/src/components/payees/ManagePayees.tsx

@@ -154,16 +154,16 @@ export const ManagePayees = ({
   function onFavorite() {
     const allFavorited = [...selected.items]
       .map(id => payeesById[id].favorite)
-      .every(f => f === 1);
+      .every(f => f === true);
     if (allFavorited) {
       onBatchChange({
-        updated: [...selected.items].map(id => ({ id, favorite: 0 })),
+        updated: [...selected.items].map(id => ({ id, favorite: false })),
         added: [],
         deleted: [],
       });
     } else {
       onBatchChange({
-        updated: [...selected.items].map(id => ({ id, favorite: 1 })),
+        updated: [...selected.items].map(id => ({ id, favorite: true })),
         added: [],
         deleted: [],
       });
@@ -174,16 +174,22 @@ export const ManagePayees = ({
   function onLearn() {
     const allLearnCategories = [...selected.items]
       .map(id => payeesById[id].learn_categories)
-      .every(f => f === 1);
+      .every(f => f === true);
     if (allLearnCategories) {
       onBatchChange({
-        updated: [...selected.items].map(id => ({ id, learn_categories: 0 })),
+        updated: [...selected.items].map(id => ({
+          id,
+          learn_categories: false,
+        })),
         added: [],
         deleted: [],
       });
     } else {
       onBatchChange({
-        updated: [...selected.items].map(id => ({ id, learn_categories: 1 })),
+        updated: [...selected.items].map(id => ({
+          id,
+          learn_categories: true,
+        })),
         added: [],
         deleted: [],
       });

packages/desktop-client/src/components/payees/PayeeTableRow.tsx

@@ -179,13 +179,13 @@ export const PayeeTableRow = memo(
                   onDelete(id);
                   break;
                 case 'favorite':
-                  onUpdate(id, 'favorite', payee.favorite ? 0 : 1);
+                  onUpdate(id, 'favorite', payee.favorite ? false : true);
                   break;
                 case 'learn':
                   onUpdate(
                     id,
                     'learn_categories',
-                    payee.learn_categories ? 0 : 1,
+                    payee.learn_categories ? false : true,
                   );
                   break;
                 case 'view-rules':

packages/desktop-client/src/components/schedules/ScheduleDetails.tsx

@@ -198,7 +198,7 @@ export function ScheduleDetails({ id, transaction }: ScheduleDetailsProps) {
               amount: schedule._amount || 0,
               amountOp: schedule._amountOp || 'isapprox',
               date: schedule._date ?? null,
-              posts_transaction: action.schedule.posts_transaction ?? false,
+              posts_transaction: !!action.schedule.posts_transaction,
               name: schedule.name ?? null,
             },
           };

packages/desktop-client/src/components/transactions/TransactionsTable.test.tsx

@@ -52,16 +52,16 @@ const payees: PayeeEntity[] = [
   {
     id: 'bob-id',
     name: 'Bob',
-    favorite: 1,
+    favorite: true,
   },
   {
     id: 'alice-id',
     name: 'Alice',
-    favorite: 1,
+    favorite: true,
   },
   {
     id: 'guy',
-    favorite: 0,
+    favorite: false,
     name: 'This guy on the side of the road',
   },
 ];

packages/desktop-client/src/hooks/useClosedAccounts.ts

@@ -4,8 +4,5 @@ import { useAccounts } from './useAccounts';
 
 export function useClosedAccounts() {
   const accounts = useAccounts();
-  return useMemo(
-    () => accounts.filter(account => account.closed === 1),
-    [accounts],
-  );
+  return useMemo(() => accounts.filter(account => account.closed), [accounts]);
 }

packages/desktop-client/src/hooks/useOffBudgetAccounts.ts

@@ -5,10 +5,7 @@ import { useAccounts } from './useAccounts';
 export function useOffBudgetAccounts() {
   const accounts = useAccounts();
   return useMemo(
-    () =>
-      accounts.filter(
-        account => account.closed === 0 && account.offbudget === 1,
-      ),
+    () => accounts.filter(account => !account.closed && account.offbudget),
     [accounts],
   );
 }

packages/desktop-client/src/hooks/useOnBudgetAccounts.ts

@@ -5,10 +5,7 @@ import { useAccounts } from './useAccounts';
 export function useOnBudgetAccounts() {
   const accounts = useAccounts();
   return useMemo(
-    () =>
-      accounts.filter(
-        account => account.closed === 0 && account.offbudget === 0,
-      ),
+    () => accounts.filter(account => !account.closed && !account.offbudget),
     [accounts],
   );
 }

packages/loot-core/src/client/accounts/accountsSlice.ts

@@ -220,7 +220,7 @@ export const syncAccounts = createAppAsyncThunk(
           .sort((a, b) =>
             a.offbudget === b.offbudget
               ? a.sort_order - b.sort_order
-              : a.offbudget - b.offbudget,
+              : (a.offbudget ? 1 : 0) - (b.offbudget ? 1 : 0),
           )
           .map(({ id }) => id);
 

packages/loot-core/src/client/data-hooks/schedules.tsx

@@ -38,7 +38,7 @@ function loadStatuses(
           s.id,
           getStatus(
             s.next_date,
-            s.completed,
+            !!s.completed,
             hasTrans.has(s.id),
             upcomingLength,
           ),

packages/loot-core/src/client/transfer.test.ts

@@ -1,5 +1,6 @@
 // @ts-strict-ignore
 import * as db from '../server/db';
+import { transactionModel } from '../server/models';
 
 import * as transfer from './transfer';
 
@@ -22,7 +23,7 @@ async function createTransaction(account: string, amount: number, extra = {}) {
     ...extra,
   };
   transaction.id = await db.insertTransaction(transaction);
-  return await db.getTransaction(transaction.id);
+  return transactionModel.fromDbView(await db.getTransaction(transaction.id));
 }
 
 describe('Transfer', () => {

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

@@ -461,20 +461,20 @@ 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(
+    await db.first<db.DbViewTransaction>(
       `SELECT * 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(
+    await db.first<db.DbViewTransaction>(
       `SELECT * 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],
     )
   ).date;
 
-  const start = monthUtils.monthFromDate(db.fromDateRepr(earliestDate));
+  const start = monthUtils.monthFromDate(db.fromDateRepr(Number(earliestDate)));
   const end = monthUtils.currentMonth();
   const months = monthUtils.rangeInclusive(start, end);
 
@@ -507,7 +507,7 @@ async function createBudget(accounts, payees, groups) {
       for (const month of months) {
         if (
           month >=
-          monthUtils.monthFromDate(db.fromDateRepr(earliestPrimaryDate))
+          monthUtils.monthFromDate(db.fromDateRepr(Number(earliestPrimaryDate)))
         ) {
           setBudget(month, category('Food'), 40000);
           setBudget(month, category('Restaurants'), 30000);
@@ -549,7 +549,9 @@ async function createBudget(accounts, payees, groups) {
       for (const month of months) {
         if (
           month >=
-            monthUtils.monthFromDate(db.fromDateRepr(earliestPrimaryDate)) &&
+            monthUtils.monthFromDate(
+              db.fromDateRepr(Number(earliestPrimaryDate)),
+            ) &&
           month <= monthUtils.currentMonth()
         ) {
           const sheetName = monthUtils.sheetForMonth(month);
@@ -691,7 +693,7 @@ export async function createTestBudget(handlers: Handlers) {
     for (const group of newCategoryGroups) {
       const groupId = await handlers['category-group-create']({
         name: group.name,
-        isIncome: group.is_income,
+        isIncome: !!group.is_income,
       });
 
       categoryGroups.push({
@@ -700,10 +702,11 @@ export async function createTestBudget(handlers: Handlers) {
         categories: [],
       });
 
-      for (const category of group.categories) {
+      for (const { is_income, hidden, ...category } of group.categories) {
         const categoryId = await handlers['category-create']({
           ...category,
-          isIncome: category.is_income ? 1 : 0,
+          isIncome: !!is_income,
+          hidden: !!hidden,
           groupId,
         });
 

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

@@ -25,10 +25,10 @@ export function generateAccount(
     name,
     bankId: null,
     bankName: null,
-    offbudget: offbudget ? 1 : 0,
+    offbudget: offbudget ? true : false,
     sort_order: 0,
-    tombstone: 0,
-    closed: 0,
+    tombstone: false,
+    closed: false,
     ...emptySyncFields(),
   };
 
@@ -96,12 +96,12 @@ export function generateCategoryGroups(
   definition: Partial<NewCategoryGroupEntity>[],
 ): CategoryGroupEntity[] {
   return definition.map(group => {
-    const g = generateCategoryGroup(group.name ?? '', group.is_income);
+    const g = generateCategoryGroup(group.name ?? '', !!group.is_income);
 
     return {
       ...g,
       categories: group.categories?.map(cat =>
-        generateCategory(cat.name, g.id, cat.is_income),
+        generateCategory(cat.name, g.id, !!cat.is_income),
       ),
     };
   });

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/parse-file.test.ts

@@ -22,7 +22,7 @@ afterAll(() => {
 });
 
 async function getTransactions(accountId) {
-  return db.runQuery(
+  return db.runQuery<db.DbTransaction>(
     'SELECT * FROM transactions WHERE acct = ?',
     [accountId],
     true,

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

@@ -4,7 +4,7 @@ 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 = 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()],
   );
@@ -17,14 +17,14 @@ export async function createPayee(description) {
 }
 
 export async function getStartingBalancePayee() {
-  let category = 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,7 @@ beforeEach(async () => {
 });
 
 function getAllTransactions() {
-  return db.all(
+  return db.all<db.DbViewTransactionInternal & { payee_name: string }>(
     `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

@@ -454,7 +454,7 @@ export async function reconcileTransactions(
       }
 
       if (existing.is_parent && existing.cleared !== updates.cleared) {
-        const children = await db.all(
+        const children = await db.all<Pick<db.DbViewTransaction, 'id'>>(
           'SELECT id FROM v_transactions WHERE parent_id = ?',
           [existing.id],
         );
@@ -527,7 +527,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 = await db.getAccounts();
   const accountsMap = new Map(accounts.map(account => [account.id, account]));
 
   const transactionsStep1 = [];
@@ -546,7 +546,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],
       );
@@ -680,7 +680,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) {
@@ -816,7 +816,7 @@ export async function syncAccount(
   acctId: string,
   bankId: string,
 ) {
-  const acctRow = await db.select('accounts', id);
+  const acctRow = await db.select<AccountEntity>('accounts', id);
 
   const syncStartDate = await getAccountSyncStartDate(id);
   const oldestTransaction = await getAccountOldestTransaction(id);

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

@@ -1,5 +1,6 @@
 // @ts-strict-ignore
 import { q } from '../../shared/query';
+import { RuleEntity } from '../../types/models';
 import { runQuery } from '../aql';
 import * as db from '../db';
 import { loadMappings } from '../db/mappings';
@@ -377,10 +378,7 @@ describe('Transaction rules', () => {
       cat_group: categoryGroupId,
     });
     const krogerId = await db.insertPayee({ name: 'kroger' });
-    const lowesId = await db.insertPayee({
-      name: 'lowes',
-      category: foodCategoryId,
-    });
+    const lowesId = await db.insertPayee({ name: 'lowes' });
 
     await db.insertTransaction({
       id: '1',
@@ -943,11 +941,17 @@ 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: RuleEntity = {
+      ...rawRule,
+      conditions: JSON.parse(rawRule.conditions),
+      actions: JSON.parse(rawRule.actions),
+      conditionsOp: rawRule.conditions_op as RuleEntity['conditionsOp'],
+      stage: rawRule.stage as RuleEntity['stage'],
+    };
+    expect(parsedRule.conditions[0].field).toBe('imported_description');
+    const action = parsedRule.actions[0];
+    expect('field' in action ? action.field : null).toBe('description');
 
     await loadRules();
 
@@ -973,11 +977,17 @@ 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: RuleEntity = {
+      ...rawRule,
+      conditions: JSON.parse(rawRule.conditions),
+      actions: JSON.parse(rawRule.actions),
+      conditionsOp: rawRule.conditions_op as RuleEntity['conditionsOp'],
+      stage: rawRule.stage as RuleEntity['stage'],
+    };
+    expect(parsedRule.conditions[0].field).toBe('imported_payee');
+    const action = parsedRule.actions[0];
+    expect('field' in action ? action.field : null).toBe('payee');
 
     const [rule] = getRules();
     expect(rule.conditions[0].field).toBe('imported_payee');

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

@@ -13,7 +13,6 @@ import {
   type TransactionEntity,
   type RuleActionEntity,
   type RuleEntity,
-  AccountEntity,
 } from '../../types/models';
 import { schemaConfig } from '../aql';
 import * as db from '../db';
@@ -175,14 +174,13 @@ 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
   `);
 
   for (let i = 0; i < rules.length; i++) {
     const desc = rules[i];
-    // These are old stages, can be removed before release
     if (desc.stage === 'cleanup' || desc.stage === 'modify') {
       desc.stage = 'pre';
     }
@@ -219,9 +217,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<db.DbSchedule>(
+    'SELECT id FROM schedules WHERE rule = ?',
+    [id],
+  );
 
   if (schedule) {
     return false;
@@ -277,7 +276,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) {
@@ -627,7 +626,7 @@ export async function applyActions(
     return null;
   }
 
-  const accounts: AccountEntity[] = await db.getAccounts();
+  const accounts = await db.getAccounts();
   const transactionsForRules = await Promise.all(
     transactions.map(transactions =>
       prepareTransactionForRules(
@@ -866,12 +865,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/accounts/transactions.ts

@@ -1,7 +1,7 @@
 // @ts-strict-ignore
 import * as connection from '../../platform/server/connection';
 import { Diff } from '../../shared/util';
-import { PayeeEntity, TransactionEntity } from '../../types/models';
+import { TransactionEntity } from '../../types/models';
 import * as db from '../db';
 import { incrFetch, whereIn } from '../db/util';
 import { batchMessages } from '../sync';
@@ -11,7 +11,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<Pick<db.DbViewTransactionInternal, 'id'>>(
     `SELECT id FROM v_transactions_internal WHERE ${whereIds}`,
   );
   const set = new Set(ids);
@@ -22,8 +22,8 @@ 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
@@ -55,8 +55,10 @@ export async function batchUpdateTransactions({
     ? await idsWithChildren(deleted.map(d => d.id))
     : [];
 
-  const oldPayees = new Set<PayeeEntity['id']>();
-  const accounts = await db.all('SELECT * FROM accounts WHERE tombstone = 0');
+  const oldPayees = new Set<db.DbPayee['id']>();
+  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/accounts/transfer.test.ts

@@ -1,6 +1,8 @@
 // @ts-strict-ignore
 import { expectSnapshotWithDiffer } from '../../mocks/util';
+import { TransactionEntity } from '../../types/models';
 import * as db from '../db';
+import { transactionModel } from '../models';
 
 import * as transfer from './transfer';
 
@@ -35,22 +37,11 @@ async function prepareDatabase() {
   });
 }
 
-type Transaction = {
-  account: string;
-  amount: number;
-  category?: string;
-  date: string;
-  id?: string;
-  notes?: string;
-  payee: string;
-  transfer_id?: string;
-};
-
 describe('Transfer', () => {
   test('transfers are properly inserted/updated/deleted', async () => {
     await prepareDatabase();
 
-    let transaction: Transaction = {
+    let transaction: Partial<TransactionEntity> = {
       account: 'one',
       amount: 5000,
       payee: await db.insertPayee({ name: 'Non-transfer' }),
@@ -61,10 +52,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'",
     );
 
@@ -79,7 +70,9 @@ describe('Transfer', () => {
     differ.expectToMatchDiff(await getAllTransactions());
 
     // Fill the transaction out
-    transaction = await db.getTransaction(transaction.id);
+    transaction = transactionModel.fromDbView(
+      await db.getTransaction(transaction.id),
+    );
     expect(transaction.transfer_id).toBeDefined();
 
     transaction = {
@@ -108,7 +101,9 @@ describe('Transfer', () => {
     differ.expectToMatchDiff(await getAllTransactions());
 
     // Make sure it's not a linked transaction anymore
-    transaction = await db.getTransaction(transaction.id);
+    transaction = transactionModel.fromDbView(
+      await db.getTransaction(transaction.id),
+    );
     expect(transaction.transfer_id).toBeNull();
 
     // Re-transfer it
@@ -120,7 +115,9 @@ describe('Transfer', () => {
     await transfer.onUpdate(transaction);
     differ.expectToMatchDiff(await getAllTransactions());
 
-    transaction = await db.getTransaction(transaction.id);
+    transaction = transactionModel.fromDbView(
+      await db.getTransaction(transaction.id),
+    );
     expect(transaction.transfer_id).toBeDefined();
 
     await db.deleteTransaction(transaction);
@@ -131,14 +128,14 @@ 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'",
     );
 
-    let transaction: Transaction = {
+    let transaction: Partial<TransactionEntity> = {
       account: 'one',
       amount: 5000,
       payee: await db.insertPayee({ name: 'Non-transfer' }),
@@ -151,7 +148,7 @@ describe('Transfer', () => {
     const differ = expectSnapshotWithDiffer(await getAllTransactions());
 
     transaction = {
-      ...(await db.getTransaction(transaction.id)),
+      ...transactionModel.fromDbView(await db.getTransaction(transaction.id)),
       payee: transferThree.id,
       notes: 'hi',
     };
@@ -160,7 +157,7 @@ describe('Transfer', () => {
     differ.expectToMatchDiff(await getAllTransactions());
 
     transaction = {
-      ...(await db.getTransaction(transaction.id)),
+      ...transactionModel.fromDbView(await db.getTransaction(transaction.id)),
       payee: transferTwo.id,
     };
     await db.updateTransaction(transaction);

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

@@ -1,30 +1,29 @@
 // @ts-strict-ignore
 import * as db from '../db';
 
-async function getPayee(acct) {
-  return db.first('SELECT * FROM payees WHERE transfer_acct = ?', [acct]);
+async function getPayee(acct: db.DbPayee['transfer_acct']) {
+  return db.first<db.DbPayee>('SELECT * FROM payees WHERE transfer_acct = ?', [
+    acct,
+  ]);
 }
 
 async function getTransferredAccount(transaction) {
   if (transaction.payee) {
-    const { transfer_acct } = await db.first(
-      'SELECT id, transfer_acct FROM v_payees WHERE id = ?',
-      [transaction.payee],
-    );
+    const { transfer_acct } = await db.first<
+      Pick<db.DbViewPayee, 'transfer_acct'>
+    >('SELECT transfer_acct FROM v_payees WHERE id = ?', [transaction.payee]);
     return transfer_acct;
   }
   return null;
 }
 
 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
@@ -50,14 +49,16 @@ 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],
   );
 
   // We need to enforce certain constraints with child transaction transfers
   if (transaction.parent_id) {
-    const row = await db.first(
+    const row = await db.first<
+      Pick<db.DbViewTransaction, 'id'> & Pick<db.DbPayee, 'transfer_acct'>
+    >(
       `
         SELECT p.id, p.transfer_acct FROM v_transactions t
         LEFT JOIN payees p ON p.id = t.payee

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

@@ -29,10 +29,10 @@ export const accountModel = {
   fromExternal(account: APIAccountEntity) {
     const result = { ...account } as unknown as AccountEntity;
     if ('offbudget' in account) {
-      result.offbudget = account.offbudget ? 1 : 0;
+      result.offbudget = account.offbudget ? true : false;
     }
     if ('closed' in account) {
-      result.closed = account.closed ? 1 : 0;
+      result.closed = account.closed ? true : false;
     }
     return result;
   },

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

@@ -17,6 +17,7 @@ import {
 } from '../shared/transactions';
 import { integerToAmount } from '../shared/util';
 import { Handlers } from '../types/handlers';
+import { AccountEntity } from '../types/models';
 import { ServerHandlers } from '../types/server-handlers';
 
 import { addTransactions } from './accounts/sync';
@@ -33,6 +34,10 @@ import * as cloudStorage from './cloud-storage';
 import { type RemoteFile } from './cloud-storage';
 import * as db from './db';
 import { APIError } from './errors';
+import {
+  categoryGroupModel as categoryGroupServerModel,
+  accountModel as accountServerModel,
+} from './models';
 import { runMutator } from './mutators';
 import * as prefs from './prefs';
 import * as sheet from './sheet';
@@ -53,7 +58,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<db.DbCrdtMessage>(
           'SELECT DISTINCT dataset FROM messages_crdt WHERE timestamp > ?',
           [latestTimestamp],
         );
@@ -94,9 +99,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 +360,8 @@ handlers['api/budget-month'] = async function ({ month }) {
   checkFileOpen();
   await validateMonth(month);
 
-  const groups = await db.getCategoriesGrouped();
+  const grouped = await db.getCategoriesGrouped();
+  const groups = categoryGroupServerModel.fromDbArray(grouped);
   const sheetName = monthUtils.sheetForMonth(month);
 
   function value(name) {
@@ -553,8 +560,10 @@ handlers['api/transaction-delete'] = withMutation(async function ({ id }) {
 
 handlers['api/accounts-get'] = async function () {
   checkFileOpen();
-  const accounts = await db.getAccounts();
-  return accounts.map(account => accountModel.toExternal(account));
+  const accounts = accountServerModel.fromDbArray(await db.getAccounts());
+  return accounts.map(account =>
+    accountModel.toExternal({ ...account } as AccountEntity),
+  );
 };
 
 handlers['api/account-create'] = withMutation(async function ({

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/exec.ts

@@ -38,6 +38,14 @@ export async function execQuery(
   return data;
 }
 
+/**
+ * The run pre-compiled AQL query.
+ * @param query The pre-compiled AQL query.
+ * @param pieces The compiled SQL pieces.
+ * @param state The query state.
+ * @param options The query options.
+ * @returns The queried data.
+ */
 export async function runCompiledQuery(
   query,
   pieces,
@@ -79,6 +87,14 @@ export async function runCompiledQuery(
   return data;
 }
 
+/**
+ * Compile and run the AQL query.
+ * @param schema The schema.
+ * @param schemaConfig The schema configuration.
+ * @param query The AQL query.
+ * @param options The query options.
+ * @returns The queried data and its dependencies.
+ */
 export async function runQuery(schema, schemaConfig, query, options) {
   const { sqlPieces, state } = compileQuery(query, schema, schemaConfig);
   const data = await runCompiledQuery(query, sqlPieces, state, options);

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

@@ -171,7 +171,9 @@ async function execTransactionsGrouped(
     ${sql.orderBy}
   `;
 
-  const allRows = await db.all(finalSql);
+  const allRows = await db.all<db.DbTransaction & { _parent_id: string }>(
+    finalSql,
+  );
 
   // Group the parents and children up
   const { parents, children } = allRows.reduce(

packages/loot-core/src/server/aql/schema/run-query.ts

@@ -9,6 +9,14 @@ import { schemaExecutors } from './executors';
 
 import { schema, schemaConfig } from './index';
 
+/**
+ * Run the pre-compiled AQL query.
+ * @param query The pre-compiled AQL query.
+ * @param sqlPieces The compiled SQL pieces.
+ * @param state The query state.
+ * @param params The query parameters.
+ * @returns The queried data.
+ */
 export function runCompiledQuery(query, sqlPieces, state, params?: unknown) {
   return _runCompiledQuery(query, sqlPieces, state, {
     params,
@@ -16,6 +24,12 @@ export function runCompiledQuery(query, sqlPieces, state, params?: unknown) {
   });
 }
 
+/**
+ * Compile and run the AQL query.
+ * @param query The AQL query to compile and run.
+ * @param params The query parameters.
+ * @returns The queried data and its dependencies.
+ */
 export function runQuery(query: Query | QueryState, params?: unknown) {
   if (query instanceof Query) {
     query = query.serialize();

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

@@ -1,5 +1,4 @@
 // @ts-strict-ignore
-
 import * as monthUtils from '../../shared/months';
 import { integerToCurrency, safeNumber } from '../../shared/util';
 import * as db from '../db';
@@ -35,12 +34,12 @@ function calcBufferedAmount(
   return buffered + amount;
 }
 
-function getBudgetTable(): string {
+function getBudgetTable(): 'reflect_budgets' | 'zero_budgets' {
   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'],
   );
@@ -91,10 +90,12 @@ export function getBudget({
   month: string;
 }): number {
   const table = getBudgetTable();
-  const existing = db.firstSync(
-    `SELECT * FROM ${table} WHERE month = ? AND category = ?`,
-    [dbMonth(month), category],
-  );
+  const existing = db.firstSync<
+    Pick<db.DbReflectBudget, 'amount'> | Pick<db.DbZeroBudget, 'amount'>
+  >(`SELECT amount FROM ${table} WHERE month = ? AND category = ?`, [
+    dbMonth(month),
+    category,
+  ]);
   return existing ? existing.amount || 0 : 0;
 }
 
@@ -110,10 +111,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.DbReflectBudget, 'id'> | Pick<db.DbZeroBudget, 'id'>
+  >(`SELECT id FROM ${table} WHERE month = ? AND category = ?`, [
+    dbMonth(month),
+    category,
+  ]);
   if (existing) {
     return db.update(table, { id: existing.id, amount });
   }
@@ -127,10 +130,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.DbReflectBudget, 'id'> | Pick<db.DbZeroBudget, 'id'>
+  >(`SELECT id FROM ${table} WHERE month = ? AND category = ?`, [
+    dbMonth(month),
+    category,
+  ]);
   if (existing) {
     return db.update(table, {
       id: existing.id,
@@ -148,7 +153,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.DbZeroBudgetMonth, 'id'>>(
     `SELECT id FROM zero_budget_months WHERE id = ?`,
     [month],
   );
@@ -162,15 +167,17 @@ export function setBuffer(month: string, amount: unknown): Promise<void> {
 }
 
 function setCarryover(
-  table: string,
+  table: 'reflect_budgets' | 'zero_budgets',
   category: string,
   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.DbReflectBudget, 'id'> | Pick<db.DbZeroBudget, '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 +232,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 +251,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 +294,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 +313,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 +336,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 +368,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 +554,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, categoryModel } from '../models';
 import * as sheet from '../sheet';
 import { resolveName } from '../spreadsheet/util';
 
@@ -41,7 +42,7 @@ function createCategory(cat, sheetName, prevSheetName, start, end) {
     initialValue: 0,
     run: () => {
       // Making this sync is faster!
-      const rows = db.runQuery(
+      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}
@@ -86,7 +87,7 @@ function createCategoryGroup(group, sheetName) {
 
 function handleAccountChange(months, oldValue, newValue) {
   if (!oldValue || oldValue.offbudget !== newValue.offbudget) {
-    const rows = db.runQuery(
+    const rows = db.runQuery<Pick<db.DbTransaction, 'category'>>(
       `
         SELECT DISTINCT(category) as category FROM transactions
         WHERE acct = ?
@@ -289,7 +290,7 @@ function handleCategoryGroupChange(months, oldValue, newValue) {
         // OK because we're leveraging the sync nature of queries. Ideally we
         // wouldn't be querying here. But I think we have to. At least for now
         // we do
-        const categories = db.runQuery(
+        const categories = db.runQuery<db.DbCategory>(
           'SELECT * FROM categories WHERE tombstone = 0 AND cat_group = ?',
           [group.id],
           true,
@@ -390,8 +391,9 @@ export async function doTransfer(categoryIds, transferId) {
 }
 
 export async function createBudget(months) {
-  const categories = await db.getCategories();
-  const groups = await db.getCategoriesGrouped();
+  const categories = categoryModel.fromDbArray(await db.getCategories());
+  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 =
@@ -450,8 +452,7 @@ export async function createAllBudgets() {
   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
+  // current month. If no transactions currently exist the currentc
   const { start, end, range } = getBudgetRange(
     earliestDate || currentMonth,
     currentMonth,

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

@@ -2,7 +2,6 @@
 
 import * as monthUtils from '../../shared/months';
 import { amountToInteger } from '../../shared/util';
-import { CategoryEntity } from '../../types/models';
 import * as db from '../db';
 
 import { getSheetValue, getSheetBoolean } from './actions';
@@ -30,7 +29,7 @@ export class CategoryTemplate {
   // Class interface
 
   // set up the class and check all templates
-  static async init(templates: Template[], category: CategoryEntity, month) {
+  static async init(templates: Template[], category: db.DbCategory, month) {
     // get all the needed setup values
     const lastMonthSheet = monthUtils.sheetForMonth(
       monthUtils.subMonths(month, 1),
@@ -208,7 +207,7 @@ export class CategoryTemplate {
 
   //-----------------------------------------------------------------------------
   // Implementation
-  readonly category: CategoryEntity; //readonly so we can double check the category this is using
+  readonly category: db.DbCategory; //readonly so we can double check the category this is using
   private month: string;
   private templates = [];
   private remainder = [];
@@ -228,7 +227,7 @@ export class CategoryTemplate {
 
   private constructor(
     templates: Template[],
-    category: CategoryEntity,
+    category: db.DbCategory,
     month: string,
     fromLastMonth: number,
   ) {

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

@@ -5,7 +5,6 @@ import {
   getDateWithSkippedWeekend,
   extractScheduleConds,
 } from '../../shared/schedules';
-import { CategoryEntity } from '../../types/models';
 import * as db from '../db';
 import { getRuleForSchedule } from '../schedules/app';
 
@@ -15,13 +14,13 @@ import { ScheduleTemplate, Template } from './types/templates';
 async function createScheduleList(
   template: ScheduleTemplate[],
   current_month: string,
-  category: CategoryEntity,
+  category: db.DbCategory,
 ) {
   const t = [];
   const errors = [];
 
   for (let ll = 0; ll < template.length; ll++) {
-    const { id: sid, completed: complete } = await db.first(
+    const { id: sid, completed: complete } = await db.first<db.DbSchedule>(
       'SELECT * FROM schedules WHERE TRIM(name) = ? AND tombstone = 0',
       [template[ll].name.trim()],
     );
@@ -200,7 +199,7 @@ export async function goalsSchedule(
   last_month_balance: number,
   to_budget: number,
   errors: string[],
-  category: CategoryEntity,
+  category: db.DbCategory,
 ) {
   if (!scheduleFlag) {
     scheduleFlag = true;

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,7 +47,7 @@ export function runCheckTemplates() {
   return checkTemplates();
 }
 
-async function getCategories(): Promise<CategoryEntity[]> {
+async function getCategories(): Promise<db.DbCategory[]> {
   return await db.all(
     `
     SELECT categories.* FROM categories
@@ -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',
   );
 
@@ -123,7 +123,7 @@ async function processTemplate(
   month,
   force: boolean,
   categoryTemplates,
-  categoriesIn?,
+  categoriesIn?: db.DbViewCategory[],
 ): Promise<Notification> {
   // setup categories
   let categories = [];

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

@@ -1,5 +1,5 @@
+import { ScheduleEntity } from '../../types/models';
 import * as db from '../db';
-import { Schedule } from '../db/types';
 
 import { GOAL_PREFIX, TEMPLATE_PREFIX } from './template-notes';
 
@@ -41,7 +41,7 @@ export async function getCategoriesWithTemplateNotes(): Promise<
   );
 }
 
-export async function getActiveSchedules(): Promise<Schedule[]> {
+export async function getActiveSchedules(): Promise<ScheduleEntity[]> {
   return await db.all(
     'SELECT id, rule, active, completed, posts_transaction, tombstone, name from schedules WHERE name NOT NULL AND tombstone = 0',
   );

packages/loot-core/src/server/budget/template-notes.test.ts

@@ -1,5 +1,5 @@
+import { ScheduleEntity } from '../../types/models';
 import * as db from '../db';
-import { Schedule } from '../db/types';
 
 import {
   CategoryWithTemplateNote,
@@ -20,7 +20,7 @@ function mockGetTemplateNotesForCategories(
   );
 }
 
-function mockGetActiveSchedules(schedules: Schedule[]) {
+function mockGetActiveSchedules(schedules: Partial<ScheduleEntity>[]) {
   (getActiveSchedules as jest.Mock).mockResolvedValue(schedules);
 }
 
@@ -245,24 +245,24 @@ describe('checkTemplates', () => {
   );
 });
 
-function mockSchedules(): Schedule[] {
+function mockSchedules(): Partial<ScheduleEntity>[] {
   return [
     {
       id: 'mock-schedule-1',
       rule: 'mock-rule',
-      active: 1,
-      completed: 0,
-      posts_transaction: 0,
-      tombstone: 0,
+      active: true,
+      completed: false,
+      posts_transaction: false,
+      tombstone: false,
       name: 'Mock Schedule 1',
     },
     {
       id: 'mock-schedule-2',
       rule: 'mock-rule',
-      active: 1,
-      completed: 0,
-      posts_transaction: 0,
-      tombstone: 0,
+      active: true,
+      completed: false,
+      posts_transaction: false,
+      tombstone: false,
       name: 'Mock Schedule 2',
     },
   ];

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

@@ -144,7 +144,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 +178,7 @@ async function importDashboard({ filepath }: { filepath: string }) {
 
     exportModel.validate(parsedContent);
 
-    const customReportIds: CustomReportEntity[] = await db.all(
+    const customReportIds: Pick<CustomReportEntity, 'id'>[] = await db.all(
       'SELECT id from custom_reports',
     );
     const customReportIdSet = new Set(customReportIds.map(({ id }) => id));

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

@@ -15,11 +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,
-  PayeeEntity,
-} from '../../types/models';
 import {
   schema,
   schemaConfig,
@@ -37,6 +32,20 @@ import {
 import { sendMessages, batchMessages } from '../sync';
 
 import { shoveSortOrders, SORT_INCREMENT } from './sort';
+import {
+  DbAccount,
+  DbBank,
+  DbCategory,
+  DbCategoryGroup,
+  DbCategoryMapping,
+  DbClockMessage,
+  DbPayee,
+  DbPayeeMapping,
+  DbTransaction,
+  DbViewTransaction,
+} from './types';
+
+export * from './types';
 
 export { toDateRepr, fromDateRepr } from '../models';
 
@@ -77,7 +86,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);
@@ -100,17 +109,22 @@ export function runQuery(
   sql: string,
   params?: Array<string | number>,
   fetchAll?: false,
-);
-export function runQuery(
+): { changes: unknown };
+export function runQuery<T>(
   sql: string,
   params: Array<string | number> | undefined,
   fetchAll: true,
-);
-export function runQuery(sql, params, fetchAll) {
-  // const unrecord = perf.record('sqlite');
-  const result = sqlite.runQuery(db, sql, params, fetchAll);
-  // unrecord();
-  return result;
+): T[];
+export function runQuery<T>(
+  sql: string,
+  params: (string | number)[],
+  fetchAll: boolean,
+) {
+  if (fetchAll) {
+    return sqlite.runQuery<T>(db, sql, params, true);
+  } else {
+    return sqlite.runQuery(db, sql, params, false);
+  }
 }
 
 export function execQuery(sql: string) {
@@ -146,19 +160,19 @@ 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)[]) {
-  return runQuery(sql, params, true);
+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);
+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);
+export function firstSync<T>(sql, params?: (string | number)[]) {
+  const arr = runQuery<T>(sql, params, true);
   return arr.length === 0 ? null : arr[0];
 }
 
@@ -169,8 +183,8 @@ 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, id: T['id']) {
+  const rows = await runQuery<T>(
     'SELECT * FROM ' + table + ' WHERE id = ?',
     [id],
     true,
@@ -250,15 +264,17 @@ export async function deleteAll(table: string) {
   await Promise.all(rows.map(({ id }) => delete_(table, id)));
 }
 
-export async function selectWithSchema(table, sql, params) {
-  const rows = await runQuery(sql, params, true);
-  return rows
+export async function selectWithSchema<T>(table, sql, params) {
+  const rows = await runQuery<T>(sql, params, true);
+  const convertedRows = rows
     .map(row => convertFromSelect(schema, schemaConfig, table, row))
     .filter(Boolean);
+  // TODO: Make convertFromSelect generic so we don't need this cast
+  return convertedRows as T[];
 }
 
-export async function selectFirstWithSchema(table, sql, params) {
-  const rows = await selectWithSchema(table, sql, params);
+export async function selectFirstWithSchema<T>(table, sql, params) {
+  const rows = await selectWithSchema<T>(table, sql, params);
   return rows.length > 0 ? rows[0] : null;
 }
 
@@ -283,16 +299,16 @@ export function updateWithSchema(table, fields) {
 // different files
 
 export async function getCategories(
-  ids?: Array<CategoryEntity['id']>,
-): Promise<CategoryEntity[]> {
+  ids?: Array<DbCategory['id']>,
+): Promise<DbCategory[]> {
   const whereIn = ids ? `c.id IN (${toSqlQueryParameters(ids)}) AND` : '';
   const query = `SELECT c.* FROM categories c WHERE ${whereIn} c.tombstone = 0 ORDER BY c.sort_order, c.id`;
   return ids ? await all(query, [...ids]) : await all(query);
 }
 
 export async function getCategoriesGrouped(
-  ids?: Array<CategoryGroupEntity['id']>,
-): Promise<Array<CategoryGroupEntity>> {
+  ids?: Array<DbCategoryGroup['id']>,
+): Promise<[DbCategoryGroup, ...DbCategory[]][]> {
   const categoryGroupWhereIn = ids
     ? `cg.id IN (${toSqlQueryParameters(ids)}) AND`
     : '';
@@ -306,24 +322,24 @@ 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 groups.map(group => [
+    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()],
   );
@@ -333,7 +349,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;
@@ -351,7 +367,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`,
   );
 
@@ -363,9 +379,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)));
@@ -381,7 +398,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()],
     );
@@ -392,14 +409,14 @@ export async function insertCategory(
     }
 
     if (atEnd) {
-      const lastCat = await first(`
+      const lastCat = await first<Pick<DbCategory, 'sort_order'>>(`
         SELECT sort_order FROM categories WHERE tombstone = 0 ORDER BY sort_order DESC, id DESC LIMIT 1
       `);
       sort_order = (lastCat ? lastCat.sort_order : 0) + SORT_INCREMENT;
     } else {
       // Unfortunately since we insert at the beginning, we need to shove
       // the sort orders to make sure there's room for it
-      const categories = await all(
+      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],
       );
@@ -427,17 +444,21 @@ export async function insertCategory(
   return id_;
 }
 
-export function updateCategory(category) {
+export function updateCategory(category: Partial<DbCategory>) {
   category = categoryModel.validate(category, { update: true });
   return update('categories', category);
 }
 
-export async function moveCategory(id, groupId, targetId?: string) {
+export async function moveCategory(
+  id: DbCategory['id'],
+  groupId: DbCategoryGroup['id'],
+  targetId?: DbCategory['id'],
+) {
   if (!groupId) {
     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],
   );
@@ -449,12 +470,15 @@ export async function moveCategory(id, groupId, targetId?: string) {
   await update('categories', { id, sort_order, cat_group: groupId });
 }
 
-export async function deleteCategory(category, transferId?: string) {
+export async function deleteCategory(
+  category: Pick<DbCategory, 'id'>,
+  transferId?: DbCategory['id'],
+) {
   if (transferId) {
     // We need to update all the deleted categories that currently
     // point to the one we're about to delete so they all are
     // "forwarded" to the new transferred category.
-    const existingTransfers = await all(
+    const existingTransfers = await all<DbCategoryMapping>(
       'SELECT * FROM category_mapping WHERE transferId = ?',
       [category.id],
     );
@@ -469,15 +493,15 @@ export async function deleteCategory(category, transferId?: string) {
   return delete_('categories', category.id);
 }
 
-export async function getPayee(id) {
-  return first(`SELECT * FROM payees WHERE id = ?`, [id]);
+export async function getPayee(id: DbPayee['id']) {
+  return first<DbPayee>(`SELECT * FROM payees WHERE id = ?`, [id]);
 }
 
-export async function getAccount(id) {
-  return first(`SELECT * FROM accounts WHERE id = ?`, [id]);
+export async function getAccount(id: DbAccount['id']) {
+  return first<DbAccount>(`SELECT * FROM accounts WHERE id = ?`, [id]);
 }
 
-export async function insertPayee(payee) {
+export async function insertPayee(payee: Partial<DbPayee>) {
   payee = payeeModel.validate(payee);
   let id;
   await batchMessages(async () => {
@@ -487,10 +511,11 @@ export async function insertPayee(payee) {
   return id;
 }
 
-export async function deletePayee(payee) {
-  const { transfer_acct } = await first('SELECT * FROM payees WHERE id = ?', [
-    payee.id,
-  ]);
+export async function deletePayee(payee: Pick<DbPayee, 'id'>) {
+  const { transfer_acct } = await first<DbPayee>(
+    'SELECT * FROM payees WHERE id = ?',
+    [payee.id],
+  );
   if (transfer_acct) {
     // You should never be able to delete transfer payees
     return;
@@ -506,19 +531,22 @@ export async function deletePayee(payee) {
   return delete_('payees', payee.id);
 }
 
-export async function deleteTransferPayee(payee) {
+export async function deleteTransferPayee(payee: Pick<DbPayee, 'id'>) {
   // This allows deleting transfer payees
   return delete_('payees', payee.id);
 }
 
-export function updatePayee(payee) {
+export function updatePayee(payee: Partial<DbPayee>) {
   payee = payeeModel.validate(payee, { update: true });
   return update('payees', payee);
 }
 
-export async function mergePayees(target: string, ids: string[]) {
+export async function mergePayees(
+  target: DbPayee['id'],
+  ids: Array<DbPayee['id']>,
+) {
   // Load in payees so we can check some stuff
-  const dbPayees: PayeeEntity[] = await all('SELECT * FROM payees');
+  const dbPayees: DbPayee[] = await all('SELECT * FROM payees');
   const payees = groupById(dbPayees);
 
   // Filter out any transfer payees
@@ -530,7 +558,7 @@ export async function mergePayees(target: string, ids: string[]) {
   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],
         );
@@ -554,7 +582,11 @@ export async function mergePayees(target: string, ids: string[]) {
 }
 
 export function getPayees() {
-  return all(`
+  return all<
+    DbPayee & {
+      name: DbPayee['name'] | DbAccount['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)
@@ -567,7 +599,14 @@ export function getCommonPayees() {
     monthUtils.subWeeks(monthUtils.currentDate(), 12),
   );
   const limit = 10;
-  return all(`
+  return all<
+    DbPayee & {
+      common: boolean;
+      transfer_acct: string | null;
+      c: number;
+      latest: number;
+    }
+  >(`
     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,
@@ -605,23 +644,23 @@ 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) {
-  return first(
+export async function getPayeeByName(name: DbPayee['name']) {
+  return first<DbPayee>(
     `SELECT * FROM payees WHERE UNICODE_LOWER(name) = ? AND tombstone = 0`,
     [name.toLowerCase()],
   );
 }
 
 export function getAccounts() {
-  return all(
+  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
@@ -629,10 +668,10 @@ export function getAccounts() {
   );
 }
 
-export async function insertAccount(account) {
-  const accounts = await all(
+export async function insertAccount(account: Partial<DbAccount>) {
+  const accounts = await all<DbAccount>(
     'SELECT * FROM accounts WHERE offbudget = ? ORDER BY sort_order, name',
-    [account.offbudget != null ? account.offbudget : 0],
+    [account.offbudget ? 1 : 0],
   );
 
   // Don't pass a target in, it will default to appending at the end
@@ -651,17 +690,23 @@ export function deleteAccount(account) {
   return delete_('accounts', account.id);
 }
 
-export async function moveAccount(id, targetId) {
-  const account = await first('SELECT * FROM accounts WHERE id = ?', [id]);
+export async function moveAccount(
+  id: DbAccount['id'],
+  targetId: DbAccount['id'],
+) {
+  const account = await first<DbAccount>(
+    'SELECT * FROM accounts WHERE id = ?',
+    [id],
+  );
   let accounts;
   if (account.closed) {
-    accounts = await all(
+    accounts = await all<Pick<DbAccount, 'id' | 'sort_order'>>(
       `SELECT id, sort_order FROM accounts WHERE closed = 1 ORDER BY sort_order, name`,
     );
   } else {
-    accounts = await all(
+    accounts = await all<Pick<DbAccount, 'id' | 'sort_order'>>(
       `SELECT id, sort_order FROM accounts WHERE tombstone = 0 AND offbudget = ? ORDER BY sort_order, name`,
-      [account.offbudget],
+      [account.offbudget ? 1 : 0],
     );
   }
 
@@ -674,8 +719,8 @@ export async function moveAccount(id, targetId) {
   });
 }
 
-export async function getTransaction(id) {
-  const rows = await selectWithSchema(
+export async function getTransaction(id: DbViewTransaction['id']) {
+  const rows = await selectWithSchema<DbViewTransaction>(
     'transactions',
     'SELECT * FROM v_transactions WHERE id = ?',
     [id],
@@ -683,14 +728,15 @@ export async function getTransaction(id) {
   return rows[0];
 }
 
-export async function getTransactions(accountId) {
+export async function getTransactions(accountId: DbTransaction['acct']) {
   if (arguments.length > 1) {
     throw new Error(
       '`getTransactions` was given a second argument, it now only takes a single argument `accountId`',
     );
   }
 
-  return selectWithSchema(
+  // TODO: Make selectWithSchema generic.
+  return selectWithSchema<DbViewTransaction>(
     'transactions',
     'SELECT * FROM v_transactions WHERE account = ?',
     [accountId],

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

@@ -22,17 +22,15 @@ 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,
-  ]);
-  const payees = (await db.all('SELECT * FROM payee_mapping')).map(r => [
-    r.id,
-    r.targetId,
-  ]);
+  const categories = (
+    await db.all<db.DbCategoryMapping>('SELECT * FROM category_mapping')
+  ).map(r => [r.id, r.transferId]);
+  const payees = (
+    await db.all<db.DbPayeeMapping>('SELECT * FROM payee_mapping')
+  ).map(r => [r.id, r.targetId]);
 
   // All ids are unique, so we can just keep a global table of mappings
-  allMappings = new Map(categories.concat(payees));
+  allMappings = new Map(categories.concat(payees) as [string, string][]);
 
   if (unlistenSync) {
     unlistenSync();

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

@@ -1,9 +0,0 @@
-export type Schedule = {
-  id: string;
-  rule: string;
-  active: number;
-  completed: number;
-  posts_transaction: number;
-  tombstone: number;
-  name: string | null;
-};

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

@@ -0,0 +1,310 @@
+type JsonString = string;
+
+export type DbAccount = {
+  id: string;
+  name: string;
+  offbudget: 1 | 0;
+  closed: 1 | 0;
+  tombstone: 1 | 0;
+  sort_order: number;
+  account_id?: string | null;
+  balance_current?: number | null;
+  balance_available?: number | null;
+  balance_limit?: number | null;
+  mask?: string | null;
+  official_name?: string | null;
+  type?: string | null;
+  subtype?: string | null;
+  bank?: string | null;
+  account_sync_source?: 'simpleFin' | 'goCardless' | null;
+};
+
+export type DbBank = {
+  id: string;
+  bank_id: string;
+  name: string;
+  tombstone: 1 | 0;
+};
+
+export type DbCategory = {
+  id: string;
+  name: string;
+  is_income: 1 | 0;
+  cat_group: DbCategoryGroup['id'];
+  sort_order: number;
+  hidden: 1 | 0;
+  goal_def?: JsonString | null;
+  tombstone: 1 | 0;
+};
+
+export type DbCategoryGroup = {
+  id: string;
+  name: string;
+  is_income: 1 | 0;
+  sort_order: number;
+  hidden: 1 | 0;
+  tombstone: 1 | 0;
+};
+
+export type DbCategoryMapping = {
+  id: DbCategory['id'];
+  transferId: DbCategory['id'];
+};
+
+export type DbKvCache = {
+  key: string;
+  value: string;
+};
+
+export type DbKvCacheKey = {
+  id: number;
+  key: number;
+};
+
+export type DbClockMessage = {
+  id: string;
+  clock: string;
+};
+
+export type DbCrdtMessage = {
+  id: string;
+  timestamp: string;
+  dataset: string;
+  row: string;
+  column: string;
+  value: Uint8Array;
+};
+
+export type DbNote = {
+  id: string;
+  note: string;
+};
+
+export type DbPayeeMapping = {
+  id: DbPayee['id'];
+  targetId: DbPayee['id'];
+};
+
+export type DbPayee = {
+  id: string;
+  name: string;
+  transfer_acct?: DbAccount['id'] | null;
+  favorite: 1 | 0;
+  learn_categories: 1 | 0;
+  tombstone: 1 | 0;
+  // Unused in the codebase
+  category?: string | null;
+};
+
+export type DbRule = {
+  id: string;
+  stage: string;
+  conditions: JsonString;
+  actions: JsonString;
+  tombstone: 1 | 0;
+  conditions_op: string;
+};
+
+export type DbSchedule = {
+  id: string;
+  name: string;
+  rule: DbRule['id'];
+  active: 1 | 0;
+  completed: 1 | 0;
+  posts_transaction: 1 | 0;
+  tombstone: 1 | 0;
+};
+
+// eslint-disable-next-line @typescript-eslint/no-unused-vars
+type DbScheduleJsonPath = {
+  schedule_id: DbSchedule['id'];
+  payee: string;
+  account: string;
+  amount: string;
+  date: string;
+};
+
+export type DbScheduleNextDate = {
+  id: string;
+  schedule_id: DbSchedule['id'];
+  local_next_date: number;
+  local_next_date_ts: number;
+  base_next_date: number;
+  base_next_date_ts: number;
+};
+
+// This is unused in the codebase.
+// eslint-disable-next-line @typescript-eslint/no-unused-vars
+type DbPendingTransaction = {
+  id: string;
+  acct: number;
+  amount: number;
+  description: string;
+  date: string;
+};
+
+export type DbTransaction = {
+  id: string;
+  isParent: 1 | 0;
+  isChild: 1 | 0;
+  date: number;
+  acct: DbAccount['id'];
+  amount: number;
+  sort_order: number;
+  parent_id?: DbTransaction['id'] | null;
+  category?: DbCategory['id'] | null;
+  description?: string | null;
+  notes?: string | null;
+  financial_id?: string | null;
+  error?: string | null;
+  imported_description?: string | null;
+  transferred_id?: DbTransaction['id'] | null;
+  schedule?: DbSchedule['id'] | null;
+  starting_balance_flag: 1 | 0;
+  tombstone: 1 | 0;
+  cleared: 1 | 0;
+  reconciled: 1 | 0;
+  // Unused in the codebase
+  pending?: 1 | 0 | null;
+  location?: string | null;
+  type?: string | null;
+};
+
+export type DbReflectBudget = {
+  id: string;
+  month: number;
+  category: string;
+  amount: number;
+  carryover: number;
+  goal: number;
+  long_goal: number;
+};
+
+export type DbZeroBudgetMonth = {
+  id: string;
+  buffered: number;
+};
+
+export type DbZeroBudget = {
+  id: string;
+  month: number;
+  category: string;
+  amount: number;
+  carryover: number;
+  goal: number;
+  long_goal: number;
+};
+
+export type DbTransactionFilter = {
+  id: string;
+  name: string;
+  conditions: JsonString;
+  conditions_op: string;
+  tombstone: 1 | 0;
+};
+
+export type DbPreference = {
+  id: string;
+  value: string;
+};
+
+export type DbCustomReport = {
+  id: string;
+  name: string;
+  start_date: string;
+  end_date: string;
+  date_static: number;
+  date_range: string;
+  mode: string;
+  group_by: string;
+  balance_type: string;
+  show_empty: 1 | 0;
+  show_offbudget: 1 | 0;
+  show_hidden: 1 | 0;
+  show_uncateogorized: 1 | 0;
+  selected_categories: string;
+  graph_type: string;
+  conditions: JsonString;
+  conditions_op: string;
+  metadata: JsonString;
+  interval: string;
+  color_scheme: string;
+  include_current: 1 | 0;
+  sort_by: string;
+  tombstone: 1 | 0;
+};
+
+export type DbDashboard = {
+  id: string;
+  type: string;
+  width: number;
+  height: number;
+  x: number;
+  y: number;
+  meta: JsonString;
+  tombstone: 1 | 0;
+};
+
+export type DbViewTransactionInternal = {
+  id: DbTransaction['id'];
+  is_parent: DbTransaction['isParent'];
+  is_child: DbTransaction['isChild'];
+  date: DbTransaction['date'];
+  account: DbAccount['id'];
+  amount: DbTransaction['amount'];
+  parent_id: DbTransaction['parent_id'] | null;
+  category: DbCategory['id'] | null;
+  payee: DbPayee['id'] | null;
+  notes: DbTransaction['notes'] | null;
+  imported_id: DbTransaction['financial_id'] | null;
+  error: DbTransaction['error'] | null;
+  imported_payee: DbTransaction['imported_description'] | null;
+  starting_balance_flag: DbTransaction['starting_balance_flag'] | null;
+  transfer_id: DbTransaction['transferred_id'] | null;
+  schedule: DbSchedule['id'] | null;
+  sort_order: DbTransaction['sort_order'];
+  cleared: DbTransaction['cleared'];
+  tombstone: DbTransaction['tombstone'];
+  reconciled: DbTransaction['reconciled'];
+};
+
+export type DbViewTransactionInternalAlive = DbViewTransactionInternal;
+export type DbViewTransaction = DbViewTransactionInternalAlive;
+
+export type DbViewCategory = {
+  id: DbCategory['id'];
+  name: DbCategory['name'];
+  is_income: DbCategory['is_income'];
+  hidden: DbCategory['hidden'];
+  group: DbCategoryGroup['id'];
+  sort_order: DbCategory['sort_order'];
+  tombstone: DbCategory['tombstone'];
+};
+
+export type DbViewPayee = {
+  id: DbPayee['id'];
+  name: DbAccount['name'] | DbPayee['name'];
+  transfer_acct: DbPayee['transfer_acct'];
+  tombstone: DbPayee['tombstone'];
+};
+
+export type DbViewSchedule = {
+  id: DbSchedule['id'];
+  name: DbSchedule['name'];
+  rule: DbSchedule['rule'];
+  next_date:
+    | DbScheduleNextDate['local_next_date_ts']
+    | DbScheduleNextDate['local_next_date']
+    | DbScheduleNextDate['base_next_date'];
+  active: DbSchedule['active'];
+  completed: DbSchedule['completed'];
+  posts_transaction: DbSchedule['posts_transaction'];
+  tombstone: DbSchedule['tombstone'];
+  _payee: DbPayeeMapping['targetId'];
+  _account: DbAccount['id'];
+  _amount: number;
+  _amountOp: string;
+  _date: JsonString;
+  _conditions: JsonString;
+  _actions: JsonString;
+};

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

@@ -10,6 +10,7 @@ import * as budgetActions from './budget/actions';
 import * as budget from './budget/base';
 import * as db from './db';
 import { handlers } from './main';
+import { transactionModel } from './models';
 import {
   runHandler,
   runMutator,
@@ -67,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',
@@ -136,15 +139,15 @@ describe('Accounts', () => {
       await db.all('SELECT * FROM transactions'),
     );
 
-    let transaction = await db.getTransaction(id);
+    let transaction = transactionModel.fromDbView(await db.getTransaction(id));
     await runHandler(handlers['transaction-update'], {
-      ...(await db.getTransaction(id)),
+      ...transactionModel.fromDbView(await db.getTransaction(id)),
       payee: 'transfer-three',
       date: '2017-01-03',
     });
     differ.expectToMatchDiff(await db.all('SELECT * FROM transactions'));
 
-    transaction = await db.getTransaction(id);
+    transaction = transactionModel.fromDbView(await db.getTransaction(id));
     await runHandler(handlers['transaction-delete'], transaction);
     differ.expectToMatchDiff(await db.all('SELECT * FROM transactions'));
   });
@@ -264,7 +267,7 @@ describe('Budget', () => {
     // Test updates
     changed = await captureChangedCells(async () => {
       await runHandler(handlers['transaction-update'], {
-        ...(await db.getTransaction(trans.id)),
+        ...transactionModel.fromDbView(await db.getTransaction(trans.id)),
         amount: 7000,
       });
     });

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

@@ -49,6 +49,12 @@ import { APIError, TransactionError, PostError } from './errors';
 import { app as filtersApp } from './filters/app';
 import { handleBudgetImport } from './importers';
 import { app } from './main-app';
+import {
+  accountModel,
+  categoryGroupModel,
+  categoryModel,
+  payeeModel,
+} from './models';
 import { mutator, runHandler } from './mutators';
 import { app as notesApp } from './notes/app';
 import * as Platform from './platform';
@@ -160,8 +166,8 @@ handlers['transactions-export-query'] = async function ({ query: queryState }) {
 
 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()),
   };
 };
 
@@ -181,7 +187,9 @@ handlers['get-budget-bounds'] = async function () {
 };
 
 handlers['envelope-budget-month'] = async function ({ month }) {
-  const groups = await db.getCategoriesGrouped();
+  const groups = categoryGroupModel.fromDbArray(
+    await db.getCategoriesGrouped(),
+  );
   const sheetName = monthUtils.sheetForMonth(month);
 
   function value(name) {
@@ -233,7 +241,9 @@ handlers['envelope-budget-month'] = async function ({ month }) {
 };
 
 handlers['tracking-budget-month'] = async function ({ month }) {
-  const groups = await db.getCategoriesGrouped();
+  const groups = categoryGroupModel.fromDbArray(
+    await db.getCategoriesGrouped(),
+  );
   const sheetName = monthUtils.sheetForMonth(month);
 
   function value(name) {
@@ -326,7 +336,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],
       );
@@ -337,9 +347,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' };
@@ -365,7 +376,7 @@ handlers['category-delete'] = mutator(async function ({ id, transferId }) {
 });
 
 handlers['get-category-groups'] = async function () {
-  return await db.getCategoriesGrouped();
+  return categoryGroupModel.fromDbArray(await db.getCategoriesGrouped());
 };
 
 handlers['category-group-create'] = mutator(async function ({
@@ -400,7 +411,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],
     );
@@ -418,7 +429,7 @@ handlers['category-group-delete'] = mutator(async function ({
 });
 
 handlers['must-category-transfer'] = async function ({ id }) {
-  const res = await db.runQuery(
+  const res = await db.runQuery<{ count: number }>(
     `SELECT count(t.id) as count FROM transactions t
        LEFT JOIN category_mapping cm ON cm.id = t.category
        WHERE cm.transferId = ? AND t.tombstone = 0`,
@@ -449,11 +460,11 @@ handlers['payee-create'] = mutator(async function ({ name }) {
 });
 
 handlers['common-payees-get'] = async function () {
-  return db.getCommonPayees();
+  return payeeModel.fromDbArray(await db.getCommonPayees());
 };
 
 handlers['payees-get'] = async function () {
-  return db.getPayees();
+  return payeeModel.fromDbArray(await db.getPayees());
 };
 
 handlers['payees-get-orphaned'] = async function () {
@@ -568,11 +579,11 @@ handlers['account-update'] = mutator(async function ({ id, name }) {
 });
 
 handlers['accounts-get'] = async function () {
-  return db.getAccounts();
+  return accountModel.fromDbArray(await db.getAccounts());
 };
 
 handlers['account-balance'] = async function ({ id, cutoff }) {
-  const { balance } = await db.first(
+  const { balance } = 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))],
   );
@@ -580,11 +591,11 @@ handlers['account-balance'] = async function ({ id, cutoff }) {
 };
 
 handlers['account-properties'] = async function ({ id }) {
-  const { balance } = await db.first(
+  const { balance } = await db.first<{ balance: number }>(
     'SELECT sum(amount) as balance FROM transactions WHERE acct = ? AND isParent = 0 AND tombstone = 0',
     [id],
   );
-  const { count } = await db.first(
+  const { count } = await db.first<{ count: number }>(
     'SELECT count(id) as count FROM transactions WHERE acct = ? AND tombstone = 0',
     [id],
   );
@@ -602,9 +613,10 @@ handlers['gocardless-accounts-link'] = async function ({
   const bank = await link.findOrCreateBank(account.institution, requisitionId);
 
   if (upgradingId) {
-    const accRow = await db.first('SELECT * FROM accounts WHERE id = ?', [
-      upgradingId,
-    ]);
+    const accRow = await db.first<db.DbAccount>(
+      'SELECT * FROM accounts WHERE id = ?',
+      [upgradingId],
+    );
     id = accRow.id;
     await db.update('accounts', {
       id,
@@ -663,9 +675,10 @@ handlers['simplefin-accounts-link'] = async function ({
   );
 
   if (upgradingId) {
-    const accRow = await db.first('SELECT * FROM accounts WHERE id = ?', [
-      upgradingId,
-    ]);
+    const accRow = await db.first<db.DbAccount>(
+      'SELECT * FROM accounts WHERE id = ?',
+      [upgradingId],
+    );
     id = accRow.id;
     await db.update('accounts', {
       id,
@@ -754,7 +767,7 @@ handlers['account-close'] = mutator(async function ({
   await handlers['account-unlink']({ id });
 
   return withUndo(async () => {
-    const account = await db.first(
+    const account = await db.first<db.DbAccount>(
       'SELECT * FROM accounts WHERE id = ? AND tombstone = 0',
       [id],
     );
@@ -773,13 +786,15 @@ handlers['account-close'] = mutator(async function ({
     if (numTransactions === 0) {
       await db.deleteAccount({ id });
     } else if (forced) {
-      const rows = await db.runQuery(
+      const rows = await db.runQuery<
+        Pick<db.DbViewTransaction, 'id' | 'transfer_id'>
+      >(
         'SELECT id, transfer_id FROM v_transactions WHERE account = ?',
         [id],
         true,
       );
 
-      const { id: payeeId } = await db.first(
+      const { id: payeeId } = await db.first<Pick<db.DbPayee, 'id'>>(
         'SELECT id FROM payees WHERE transfer_acct = ?',
         [id],
       );
@@ -816,7 +831,7 @@ handlers['account-close'] = mutator(async function ({
       // If there is a balance we need to transfer it to the specified
       // account (and possibly categorize it)
       if (balance !== 0) {
-        const { id: payeeId } = await db.first(
+        const { id: payeeId } = await db.first<Pick<db.DbPayee, 'id'>>(
           'SELECT id FROM payees WHERE transfer_acct = ?',
           [transferAccountId],
         );
@@ -1096,7 +1111,9 @@ handlers['accounts-bank-sync'] = async function ({ ids = [] }) {
     'user-key',
   ]);
 
-  const accounts = await db.runQuery(
+  const accounts = await db.runQuery<
+    db.DbAccount & { bankId: db.DbBank['bank_id'] }
+  >(
     `
     SELECT a.*, b.bank_id as bankId
     FROM accounts a
@@ -1155,7 +1172,9 @@ handlers['accounts-bank-sync'] = async function ({ ids = [] }) {
 };
 
 handlers['simplefin-batch-sync'] = async function ({ ids = [] }) {
-  const accounts = await db.runQuery(
+  const accounts = await db.runQuery<
+    db.DbAccount & { bankId: db.DbBank['bank_id'] }
+  >(
     `SELECT a.*, b.bank_id as bankId FROM accounts a
          LEFT JOIN banks b ON a.bank = b.id
          WHERE
@@ -1214,7 +1233,7 @@ handlers['simplefin-batch-sync'] = async function ({ ids = [] }) {
     const errors = [];
     for (const account of accounts) {
       retVal.push({
-        accountId: account.accountId,
+        accountId: account.id,
         res: {
           errors,
           newTransactions: [],
@@ -1274,7 +1293,7 @@ handlers['transactions-import'] = mutator(function ({
 });
 
 handlers['account-unlink'] = mutator(async function ({ id }) {
-  const { bank: bankId } = await db.first(
+  const { bank: bankId } = await db.first<Pick<db.DbAccount, 'bank'>>(
     'SELECT bank FROM accounts WHERE id = ?',
     [id],
   );
@@ -1283,7 +1302,10 @@ handlers['account-unlink'] = mutator(async function ({ id }) {
     return 'ok';
   }
 
-  const accRow = await db.first('SELECT * FROM accounts WHERE id = ?', [id]);
+  const accRow = await db.first<db.DbAccount>(
+    'SELECT * FROM accounts WHERE id = ?',
+    [id],
+  );
 
   const isGoCardless = accRow.account_sync_source === 'goCardless';
 
@@ -1301,7 +1323,7 @@ handlers['account-unlink'] = mutator(async function ({ id }) {
     return;
   }
 
-  const { count } = await db.first(
+  const { count } = await db.first<{ count: number }>(
     'SELECT COUNT(*) as count FROM accounts WHERE bank = ?',
     [bankId],
   );
@@ -1314,10 +1336,9 @@ handlers['account-unlink'] = mutator(async function ({ id }) {
   }
 
   if (count === 0) {
-    const { bank_id: requisitionId } = await db.first(
-      'SELECT bank_id FROM banks WHERE id = ?',
-      [bankId],
-    );
+    const { bank_id: requisitionId } = await db.first<
+      Pick<db.DbBank, 'bank_id'>
+    >('SELECT bank_id FROM banks WHERE id = ?', [bankId]);
     try {
       await post(
         getServer().GOCARDLESS_SERVER + '/remove-account',
@@ -2339,9 +2360,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,7 +62,7 @@ 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);

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,
+      account_id: account.account_id ?? null,
+      offbudget: account.offbudget === 1,
+      closed: account.closed === 1,
+      sort_order: account.sort_order,
+      tombstone: account.tombstone === 1,
+      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 === 1,
+      learn_categories: payee.learn_categories === 1,
+      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],
       );

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

@@ -337,13 +337,13 @@ 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],
     );
 
     if (latestTrans) {
-      const latestDate = fromDateRepr(latestTrans.date);
+      const latestDate = fromDateRepr(Number(latestTrans.date));
       allSchedules = allSchedules.concat(
         await weekly(latestDate, account.id),
         await every2weeks(latestDate, account.id),

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

@@ -5,6 +5,7 @@ import { captureBreadcrumb } from '../platform/exceptions';
 import * as sqlite from '../platform/server/sqlite';
 import { sheetForMonth } from '../shared/months';
 
+import * as db from './db';
 import * as Platform from './platform';
 import { Spreadsheet } from './spreadsheet/spreadsheet';
 import { resolveName } from './spreadsheet/util';
@@ -92,7 +93,7 @@ function isCacheDirty(mainDb: Database, cacheDb: Database): boolean {
 }
 
 export async function loadSpreadsheet(
-  db,
+  db: typeof import('./db'),
   onSheetChange?,
 ): Promise<Spreadsheet> {
   const cacheEnabled = process.env.NODE_ENV !== 'test';
@@ -182,26 +183,31 @@ export function unloadSpreadsheet(): void {
   }
 }
 
-export async function reloadSpreadsheet(db): Promise<Spreadsheet> {
+export async function reloadSpreadsheet(
+  db: typeof import('./db'),
+): Promise<Spreadsheet> {
   if (globalSheet) {
     unloadSpreadsheet();
     return loadSpreadsheet(db, globalOnChange);
   }
 }
 
-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<db.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<db.DbReflectBudget | db.DbZeroBudget>(`
       SELECT * FROM ${table} b
       LEFT JOIN categories c ON c.id = b.category
       WHERE c.tombstone = 0
@@ -225,7 +231,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<db.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

@@ -198,7 +198,7 @@ async function compareMessages(messages: Message[]): Promise<Message[]> {
     const { dataset, row, column, timestamp } = message;
     const timestampStr = timestamp.toString();
 
-    const res = db.runQuery(
+    const res = db.runQuery<db.DbCrdtMessage>(
       db.cache(
         'SELECT timestamp FROM messages_crdt WHERE dataset = ? AND row = ? AND column = ? AND timestamp >= ?',
       ),
@@ -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

@@ -79,7 +79,11 @@ describe('sync migrations', () => {
     tracer.expectNow('applied', ['trans1/child1']);
     await tracer.expectWait('applied', ['trans1/child1']);
 
-    const transactions = db.runQuery('SELECT * FROM transactions', [], true);
+    const transactions = db.runQuery<db.DbTransaction>(
+      'SELECT * FROM transactions',
+      [],
+      true,
+    );
     expect(transactions.length).toBe(1);
     expect(transactions[0].parent_id).toBe('trans1');
 
@@ -113,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/repair.ts

@@ -7,7 +7,7 @@ export function rebuildMerkleHash(): {
   numMessages: number;
   trie: merkle.TrieNode;
 } {
-  const rows: { timestamp: string }[] = db.runQuery(
+  const rows = db.runQuery<db.DbCrdtMessage>(
     'SELECT timestamp FROM messages_crdt',
     [],
     true,

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,14 +51,14 @@ 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)
   `);
 
   await runMutator(async () => {
-    const updated = deletedRows.map(row => ({ id: row.id, tombstone: 1 }));
+    const updated = deletedRows.map(row => ({ id: row.id, tombstone: true }));
     await batchUpdateTransactions({ updated });
   });
 
@@ -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/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/account.d.ts

@@ -1,10 +1,10 @@
 export type AccountEntity = {
   id: string;
   name: string;
-  offbudget: 0 | 1;
-  closed: 0 | 1;
+  offbudget: boolean;
+  closed: boolean;
   sort_order: number;
-  tombstone: 0 | 1;
+  tombstone: boolean;
 } & (_SyncFields<true> | _SyncFields<false>);
 
 type _SyncFields<T> = {

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

@@ -0,0 +1,6 @@
+export type BankEntity = {
+  id: string;
+  bank_id: string;
+  name: string;
+  tombstone: boolean;
+};

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

@@ -0,0 +1,22 @@
+type BudgetEntity = {
+  id: string;
+  month: number;
+  category: string;
+  amount: number;
+  carryover: boolean | 1 | 0;
+  goal: number;
+  long_goal: number;
+};
+
+export type ReflectBudgetEntity = BudgetEntity & {
+  __brand: 'ReflectBudgetEntity';
+};
+
+export type ZeroBudgetEntity = BudgetEntity & {
+  __brand: 'ZeroBudgetEntity';
+};
+
+export type ZeroBudgetMonthEntity = {
+  id: string;
+  buffered: number;
+};

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

@@ -0,0 +1,4 @@
+export type CategoryMappingEntity = {
+  id: string;
+  transferId: string;
+};

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,10 @@ export type * from './schedule';
 export type * from './transaction';
 export type * from './transaction-filter';
 export type * from './user';
+export type * from './bank';
+export type * from './category-mapping';
+export type * from './payee-mapping';
+export type * from './messages-crdt';
+export type * from './messages-clock';
+export type * from './budget';
+export type * from './preference';

packages/loot-core/src/types/models/messages-clock.d.ts

@@ -0,0 +1,4 @@
+export type ClockMessageEntity = {
+  id: string;
+  clock: string;
+};

packages/loot-core/src/types/models/messages-crdt.d.ts

@@ -0,0 +1,8 @@
+export type CrdtMessageEntity = {
+  id: string;
+  dataset: string;
+  timestamp: string;
+  column: string;
+  row: string;
+  value: Uint8Array;
+};

packages/loot-core/src/types/models/payee-mapping.d.ts

@@ -0,0 +1,4 @@
+export type PayeeMappingEntity = {
+  id: string;
+  targetId: string;
+};

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

@@ -4,7 +4,7 @@ export interface PayeeEntity {
   id: string;
   name: string;
   transfer_acct?: AccountEntity['id'];
-  favorite?: 1 | 0;
-  learn_categories?: 1 | 0;
+  favorite?: boolean;
+  learn_categories?: boolean;
   tombstone?: boolean;
 }

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

@@ -0,0 +1,4 @@
+export type PreferenceEntity = {
+  id: string;
+  value: string;
+};

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

@@ -21,7 +21,7 @@ export interface CustomReportEntity {
   conditions?: RuleConditionEntity[];
   conditionsOp: 'and' | 'or';
   data?: GroupedEntity;
-  tombstone?: boolean;
+  tombstone?: boolean | 1 | 0;
 }
 
 export type balanceTypeOpType =

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

@@ -5,7 +5,7 @@ export interface NewRuleEntity {
   conditionsOp: 'or' | 'and';
   conditions: RuleConditionEntity[];
   actions: RuleActionEntity[];
-  tombstone?: boolean;
+  tombstone?: boolean | 1 | 0;
 }
 
 export interface RuleEntity extends NewRuleEntity {

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

@@ -23,6 +23,7 @@ export interface ScheduleEntity {
   id: string;
   name?: string;
   rule: RuleEntity['id'];
+  active: boolean;
   next_date: string;
   completed: boolean;
   posts_transaction: boolean;
@@ -47,3 +48,12 @@ export type DiscoverScheduleEntity = {
   amount: ScheduleEntity['_amount'];
   _conditions: ScheduleEntity['_conditions'];
 };
+
+export type ScheduleNextDateEntity = {
+  id: string;
+  schedule_id: string;
+  local_next_date: number;
+  local_next_date_ts: number;
+  base_next_date: number;
+  base_next_date_ts: number;
+};

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

@@ -18,6 +18,7 @@ import {
   SimpleFinAccount,
   RuleEntity,
   PayeeEntity,
+  TransactionEntity,
 } from './models';
 import { OpenIdConfig } from './models/openid';
 import { GlobalPrefs, MetadataPrefs } from './prefs';
@@ -26,7 +27,9 @@ import { Query } from './query';
 import { EmptyObject } from './util';
 
 export interface ServerHandlers {
-  'transaction-update': (transaction: { id: string }) => Promise<EmptyObject>;
+  'transaction-update': (
+    transaction: Partial<TransactionEntity>,
+  ) => Promise<EmptyObject>;
 
   undo: () => Promise<void>;
 
@@ -120,7 +123,7 @@ export interface ServerHandlers {
   }) => Promise<unknown>;
 
   'payees-check-orphaned': (arg: { ids }) => Promise<unknown>;
-  'payees-get-orphaned': () => Promise<PayeeEntity[]>;
+  'payees-get-orphaned': () => Promise<Array<Pick<PayeeEntity, 'id'>>>;
 
   'payees-get-rules': (arg: { id: string }) => Promise<RuleEntity[]>;
 

upcoming-release-notes/4238.md

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