add_balances

Instructions

Implementation Reference

• apps/mcp/src/tools/mutate.ts:374-411 (handler)

  The async handler function that executes 'add_balances'. It iterates over entries, converts amounts to USD via nativeToUsd, upserts into balance_entries table using onConflictDoUpdate on (period, type, sub_type, category), and returns counts grouped by period.

    async ({ entries }) => {
      const db = getDb();
      const byPeriod = new Map<string, number>();
  
      for (const e of entries) {
        const conv = nativeToUsd(e.amount, e.currency, e.date);
        if (!conv.ok) return err(`Entry ${e.period}/${e.category}: ${conv.error}`);
        db.insert(balanceEntries)
          .values({
            period: e.period,
            date: e.date,
            type: e.type,
            sub_type: e.sub_type,
            category: e.category,
            amount: conv.usd,
            currency: 'USD',
            memo: e.memo ?? null,
          })
          .onConflictDoUpdate({
            target: [
              balanceEntries.period,
              balanceEntries.type,
              balanceEntries.sub_type,
              balanceEntries.category,
            ],
            set: { amount: conv.usd, currency: 'USD', date: e.date, memo: e.memo ?? null },
          })
          .run();
        byPeriod.set(e.period, (byPeriod.get(e.period) ?? 0) + 1);
      }
  
      return ok({
        upserted: entries.length,
        periods: byPeriod.size,
        by_period: Object.fromEntries(byPeriod),
      });
    },
  );

• apps/mcp/src/tools/mutate.ts:350-411 (registration)

  Registration of the 'add_balances' tool on the MCP server with its description and Zod schema.

  server.tool(
    'add_balances',
    'Bulk-upsert balance sheet entries (assets + liabilities) across multiple months and categories in one call. Use when importing a net-worth spreadsheet with rows = months and columns = categories. Each entry overwrites if (period, type, sub_type, category) already exists. Returns counts grouped by period. Each entry can specify its own native `currency` — defaults to USD; non-USD values are converted via historical FX at `date`.',
    {
      entries: z
        .array(
          z.object({
            period: z.string().describe('YYYY-MM'),
            date: z.string().describe('YYYY-MM-DD; typically the last day of the period'),
            type: z.enum(['asset', 'liability']),
            sub_type: z.string().describe('cash | investment | other | short_term | long_term'),
            category: z
              .string()
              .describe('Specific category (cash, savings, real_estate, usd_cash, loan, …)'),
            amount: z.number().int().describe('Amount in `currency` units (whole units)'),
            currency: z
              .string()
              .default('USD')
              .describe('Currency of `amount` (USD/KRW/JPY/EUR/CNY/GBP)'),
            memo: z.string().optional(),
          }),
        )
        .min(1),
    },
    async ({ entries }) => {
      const db = getDb();
      const byPeriod = new Map<string, number>();
  
      for (const e of entries) {
        const conv = nativeToUsd(e.amount, e.currency, e.date);
        if (!conv.ok) return err(`Entry ${e.period}/${e.category}: ${conv.error}`);
        db.insert(balanceEntries)
          .values({
            period: e.period,
            date: e.date,
            type: e.type,
            sub_type: e.sub_type,
            category: e.category,
            amount: conv.usd,
            currency: 'USD',
            memo: e.memo ?? null,
          })
          .onConflictDoUpdate({
            target: [
              balanceEntries.period,
              balanceEntries.type,
              balanceEntries.sub_type,
              balanceEntries.category,
            ],
            set: { amount: conv.usd, currency: 'USD', date: e.date, memo: e.memo ?? null },
          })
          .run();
        byPeriod.set(e.period, (byPeriod.get(e.period) ?? 0) + 1);
      }
  
      return ok({
        upserted: entries.length,
        periods: byPeriod.size,
        by_period: Object.fromEntries(byPeriod),
      });
    },
  );

• apps/mcp/src/tools/mutate.ts:353-372 (schema)

  Zod input schema for 'add_balances' — expects an array of entries with period, date, type (asset|liability), sub_type, category, amount, currency (default USD), and optional memo.

  {
    entries: z
      .array(
        z.object({
          period: z.string().describe('YYYY-MM'),
          date: z.string().describe('YYYY-MM-DD; typically the last day of the period'),
          type: z.enum(['asset', 'liability']),
          sub_type: z.string().describe('cash | investment | other | short_term | long_term'),
          category: z
            .string()
            .describe('Specific category (cash, savings, real_estate, usd_cash, loan, …)'),
          amount: z.number().int().describe('Amount in `currency` units (whole units)'),
          currency: z
            .string()
            .default('USD')
            .describe('Currency of `amount` (USD/KRW/JPY/EUR/CNY/GBP)'),
          memo: z.string().optional(),
        }),
      )
      .min(1),

• packages/db/src/schema.ts:14-28 (schema)

  Database schema definition for balance_entries table (columns: id, period, date, type, sub_type, category, amount, currency, memo) with a unique index on (period, type, sub_type, category).

  export const balanceEntries = sqliteTable(
    'balance_entries',
    {
      id: integer('id').primaryKey({ autoIncrement: true }),
      period: text('period').notNull(),
      date: text('date').notNull(),
      type: text('type').notNull(),
      sub_type: text('sub_type').notNull(),
      category: text('category').notNull(),
      amount: integer('amount').notNull().default(0),
      currency: text('currency').notNull().default('KRW'),
      memo: text('memo'),
    },
    (t) => [uniqueIndex('balance_uq').on(t.period, t.type, t.sub_type, t.category)],
  );

• apps/mcp/src/helpers.ts:79-90 (helper)

  Helper function nativeToUsd converts amounts from a given currency to USD using cached FX rates. Used by the add_balances handler to normalize all amounts to USD.

  export const nativeToUsd = (amount: number, currency: string, date: string): ConvertResult => {
    const cur = currency.toUpperCase();
    if (cur === 'USD') return { ok: true, usd: amount };
    const row = getRepository().fx.getRateOnOrBefore(date, cur);
    if (!row || row.rate_to_usd == null) {
      return {
        ok: false,
        error: `No FX rate cached for ${cur} on or before ${date}. Run sync_fx_rates first, or pass currency="USD" with the converted amount.`,
      };
    }
    return { ok: true, usd: Math.round(amount / row.rate_to_usd) };
  };