add_flows

Instructions

Implementation Reference

• apps/mcp/src/tools/mutate.ts:413-470 (handler)

  The `add_flows` tool definition and handler. Registers the tool with the MCP server, defines the Zod schema for input validation (period, date, type, sub_type, category, amount, memo), and implements the bulk-upsert logic by iterating entries and doing INSERT ... ON CONFLICT DO UPDATE using the flowEntries table.

  server.tool(
    'add_flows',
    'Bulk-upsert cash flow entries (income + expenses) across multiple months and categories in one call. Use when importing an income/expense spreadsheet with rows = months and columns = categories. Each entry overwrites if (period, type, sub_type, category) already exists.',
    {
      entries: z
        .array(
          z.object({
            period: z.string().describe('YYYY-MM'),
            date: z.string().describe('YYYY-MM-DD'),
            type: z.enum(['income', 'expense']),
            sub_type: z
              .string()
              .describe(
                'income: salary|business|dividends|interest|income_other; expense: personal|insurance|phone|utilities|rent|maintenance|loan_repayment|expense_other',
              ),
            category: z.string(),
            amount: z.number().int().describe('Amount in USD (whole dollars)'),
            memo: z.string().optional(),
          }),
        )
        .min(1),
    },
    async ({ entries }) => {
      const db = getDb();
      const byPeriod = new Map<string, number>();
  
      for (const e of entries) {
        db.insert(flowEntries)
          .values({
            period: e.period,
            date: e.date,
            type: e.type,
            sub_type: e.sub_type,
            category: e.category,
            amount: e.amount,
            currency: 'USD',
            memo: e.memo ?? null,
          })
          .onConflictDoUpdate({
            target: [
              flowEntries.period,
              flowEntries.type,
              flowEntries.sub_type,
              flowEntries.category,
            ],
            set: { amount: e.amount, 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),
      });
    },
  );

• packages/db/src/schema.ts:30-43 (schema)

  Drizzle ORM schema for the `flow_entries` SQLite table, including columns (id, period, date, type, sub_type, category, amount, currency, memo) and a unique index on (period, type, sub_type, category).

  export const flowEntries = sqliteTable(
    'flow_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('flow_uq').on(t.period, t.type, t.sub_type, t.category)],

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

  Registration function `registerMutateTools` that receives the McpServer instance and registers all mutate tools including `add_flows`.

  export function registerMutateTools(server: McpServer): void {

• apps/mcp/src/prompts.ts:72-73 (helper)

  Prompt instructing the AI to call add_flows ONCE with the full array after user confirms the category mapping.

  4. Once I confirm, flatten into one array of entries and call add_flows ONCE with the full array. Don't loop add_flow.
  5. Report the upserted count and number of months.