Skip to content

A local-first personal finance app

9.4 KB
Raw Blame
import { createApp } from '#server/app';
import * as db from '#server/db';
import { payeeModel } from '#server/models';
import { mutator } from '#server/mutators';
import { getCompletedScheduleRuleIds } from '#server/schedules/app';
import { batchMessages } from '#server/sync';
import * as rules from '#server/transactions/transaction-rules';
import { undoable } from '#server/undo';
import { DEFAULT_MAX_DISTANCE_METERS } from '#shared/constants';
import type { Diff } from '#shared/util';
import type {
  NearbyPayeeEntity,
  PayeeEntity,
  PayeeLocationEntity,
  RuleEntity,
} from '#types/models';

export type PayeesHandlers = {
  'payee-create': typeof createPayee;
  'common-payees-get': typeof getCommonPayees;
  'payees-get': typeof getPayees;
  'payees-get-orphaned': typeof getOrphanedPayees;
  'payees-get-rule-counts': typeof getPayeeRuleCounts;
  'payees-merge': typeof mergePayees;
  'payees-batch-change': typeof batchChangePayees;
  'payees-check-orphaned': typeof checkOrphanedPayees;
  'payees-get-rules': typeof getPayeeRules;
  'payee-location-create': typeof createPayeeLocation;
  'payee-locations-get': typeof getPayeeLocations;
  'payee-location-delete': typeof deletePayeeLocation;
  'payees-get-nearby': typeof getNearbyPayees;
};

export const app = createApp<PayeesHandlers>();
app.method('payee-create', mutator(undoable(createPayee)));
app.method('common-payees-get', getCommonPayees);
app.method('payees-get', getPayees);
app.method('payees-get-orphaned', getOrphanedPayees);
app.method('payees-get-rule-counts', getPayeeRuleCounts);
app.method(
  'payees-merge',
  mutator(
    undoable(mergePayees, args => ({
      mergeIds: args.mergeIds,
      targetId: args.targetId,
    })),
  ),
);
app.method('payees-batch-change', mutator(undoable(batchChangePayees)));
app.method('payees-check-orphaned', checkOrphanedPayees);
app.method('payees-get-rules', getPayeeRules);
app.method('payee-location-create', mutator(createPayeeLocation));
app.method('payee-locations-get', getPayeeLocations);
app.method('payee-location-delete', mutator(deletePayeeLocation));
app.method('payees-get-nearby', getNearbyPayees);

async function createPayee({ name }: { name: PayeeEntity['name'] }) {
  return db.insertPayee({ name });
}

async function getCommonPayees(): Promise<PayeeEntity[]> {
  // TODO: Update to an AQL query. Server must return AQL entities not the raw DB data.
  return (await db.getCommonPayees()).map(p => payeeModel.fromDb(p));
}

async function getPayees(): Promise<PayeeEntity[]> {
  // TODO: Update to an AQL query. Server must return AQL entities not the raw DB data.
  return (await db.getPayees()).map(p => payeeModel.fromDb(p));
}

async function getOrphanedPayees(): Promise<Array<Pick<PayeeEntity, 'id'>>> {
  return await db.syncGetOrphanedPayees();
}

async function getPayeeRuleCounts() {
  const payeeCounts: Record<PayeeEntity['id'], number> = {};
  const completedScheduleRules = new Set(await getCompletedScheduleRuleIds());

  rules.iterateIds(rules.getRules(), 'payee', (rule, id) => {
    if (rule.id && completedScheduleRules.has(rule.id)) {
      return;
    }

    if (payeeCounts[id] == null) {
      payeeCounts[id] = 0;
    }
    payeeCounts[id]++;
  });

  return payeeCounts;
}

async function mergePayees({
  targetId,
  mergeIds,
}: {
  targetId: PayeeEntity['id'];
  mergeIds: Array<PayeeEntity['id']>;
}) {
  await db.mergePayees(targetId, mergeIds);
}

async function batchChangePayees({
  added,
  deleted,
  updated,
}: Partial<Diff<PayeeEntity>>): Promise<void> {
  await batchMessages(async () => {
    if (deleted) {
      await Promise.all(
        deleted.map(p => ({ id: p.id })).map(p => db.deletePayee(p)),
      );
    }

    if (added) {
      await Promise.all(
        added.map(p => payeeModel.toDb(p)).map(p => db.insertPayee(p)),
      );
    }

    if (updated) {
      await Promise.all(
        updated
          .map(p => payeeModel.toDb(p as PayeeEntity, { update: true }))
          .map(p => db.updatePayee(p)),
      );
    }
  });
}

async function checkOrphanedPayees({
  ids,
}: {
  ids: Array<PayeeEntity['id']>;
}): Promise<Array<PayeeEntity['id']>> {
  const orphaned = new Set(await db.getOrphanedPayees());
  return ids.filter(id => orphaned.has(id));
}

async function getPayeeRules({
  id,
}: {
  id: PayeeEntity['id'];
}): Promise<RuleEntity[]> {
  return rules.getRulesForPayee(id).map(rule => rule.serialize());
}

async function createPayeeLocation({
  payeeId,
  latitude,
  longitude,
}: {
  payeeId: PayeeEntity['id'];
  latitude: number;
  longitude: number;
}): Promise<PayeeLocationEntity['id']> {
  const created_at = Date.now();

  if (
    !Number.isFinite(latitude) ||
    !Number.isFinite(longitude) ||
    latitude < -90 ||
    latitude > 90 ||
    longitude < -180 ||
    longitude > 180
  ) {
    throw new Error(
      'Invalid coordinates: latitude must be between -90 and 90, longitude must be between -180 and 180',
    );
  }

  return await db.insertWithUUID('payee_locations', {
    payee_id: payeeId,
    latitude,
    longitude,
    created_at,
  });
}

async function getPayeeLocations({
  payeeId,
}: {
  payeeId?: PayeeEntity['id'];
} = {}): Promise<PayeeLocationEntity[]> {
  let query = 'SELECT * FROM payee_locations WHERE tombstone IS NOT 1';
  let params: string[] = [];

  if (payeeId) {
    query += ' AND payee_id = ?';
    params = [payeeId];
  }

  query += ' ORDER BY created_at DESC';

  return db.runQuery<PayeeLocationEntity>(query, params, true);
}

async function deletePayeeLocation({
  id,
}: {
  id: PayeeLocationEntity['id'];
}): Promise<void> {
  await db.delete_('payee_locations', id);
}

// Type for the raw query result that combines PayeeEntity and PayeeLocationEntity fields
type NearbyPayeeQueryResult = Pick<
  db.DbPayee,
  | 'id'
  | 'name'
  | 'transfer_acct'
  | 'favorite'
  | 'learn_categories'
  | 'tombstone'
> &
  Omit<PayeeLocationEntity, 'id'> & {
    // PayeeLocationEntity's id renamed to location_id
    location_id: PayeeLocationEntity['id'];
    // Calculated distance from SQL
    distance: number;
  };
async function getNearbyPayees({
  latitude,
  longitude,
  maxDistance = DEFAULT_MAX_DISTANCE_METERS,
}: {
  latitude: number;
  longitude: number;
  maxDistance?: number;
}): Promise<NearbyPayeeEntity[]> {
  if (
    !Number.isFinite(latitude) ||
    !Number.isFinite(longitude) ||
    latitude < -90 ||
    latitude > 90 ||
    longitude < -180 ||
    longitude > 180
  ) {
    throw new Error(
      'Invalid coordinates: latitude must be between -90 and 90, longitude must be between -180 and 180',
    );
  }

  if (!Number.isFinite(maxDistance) || maxDistance <= 0) {
    throw new Error(
      'Invalid maxDistance: must be a finite positive number greater than 0',
    );
  }

  // Get the closest location for each payee within maxDistance using window functions
  const query = `
    WITH payee_distances AS (
      SELECT
        pl.id as location_id,
        pl.payee_id,
        pl.latitude,
        pl.longitude,
        pl.created_at,
        p.id,
        p.name,
        p.transfer_acct,
        p.favorite,
        p.learn_categories,
        p.tombstone,
        -- Haversine formula to calculate distance
        ((6371 * acos(
          MIN(1, MAX(-1,
            cos(radians(?)) * cos(radians(pl.latitude)) *
            cos(radians(pl.longitude) - radians(?)) +
            sin(radians(?)) * sin(radians(pl.latitude))
          ))
        ))) * 1000 as distance,
        -- Rank locations by distance for each payee
        ROW_NUMBER() OVER (PARTITION BY pl.payee_id ORDER BY (
          (6371 * acos(
            MIN(1, MAX(-1,
              cos(radians(?)) * cos(radians(pl.latitude)) *
              cos(radians(pl.longitude) - radians(?)) +
              sin(radians(?)) * sin(radians(pl.latitude))
            ))
          )) * 1000
        )) as distance_rank
      FROM payee_locations pl
      JOIN payees p ON pl.payee_id = p.id
      WHERE p.tombstone IS NOT 1
        AND pl.tombstone IS NOT 1
        -- Filter by distance using Haversine formula
        AND (6371 * acos(
          MIN(1, MAX(-1,
            cos(radians(?)) * cos(radians(pl.latitude)) *
            cos(radians(pl.longitude) - radians(?)) +
            sin(radians(?)) * sin(radians(pl.latitude))
          ))
        )) * 1000 <= ?
    )
    SELECT
      location_id,
      payee_id,
      latitude,
      longitude,
      created_at,
      id,
      name,
      transfer_acct,
      favorite,
      learn_categories,
      tombstone,
      distance
    FROM payee_distances
    WHERE distance_rank = 1
    ORDER BY distance ASC
    LIMIT 10
  `;

  const results = db.runQuery<NearbyPayeeQueryResult>(
    query,
    [
      latitude,
      longitude,
      latitude, // For first distance calculation in SELECT
      latitude,
      longitude,
      latitude, // For ROW_NUMBER() ordering
      latitude,
      longitude,
      latitude, // For WHERE distance filter
      maxDistance,
    ],
    true,
  );

  // Transform results to expected format
  const nearbyPayees: NearbyPayeeEntity[] = results.map(row => {
    const payee = payeeModel.fromDb({
      id: row.id,
      name: row.name,
      transfer_acct: row.transfer_acct,
      favorite: row.favorite,
      learn_categories: row.learn_categories,
      tombstone: row.tombstone,
    });

    return {
      payee,
      location: {
        id: row.location_id,
        payee_id: row.payee_id,
        latitude: row.latitude,
        longitude: row.longitude,
        created_at: row.created_at,
        distance: row.distance,
      },
    };
  });

  return nearbyPayees;
}