Building Prebuilt Reports on Top of PayPal Transaction Data

The ReportResult Contract

Before looking at any specific report, the most important thing is the type that every report returns:

type Col = { key: string; label: string; align: 'left' | 'right' };
type ReportResult = {
  title: string;
  columns: Col[];
  rows: Record<string, unknown>[];
  summary?: string;
};

columns drives both the table header and the chart axis labels. rows is the data — values keyed by column key. summary is an optional single-line takeaway (used by a handful of reports like ytd_summary).

This contract is the load-bearing piece of the feature. The frontend table renderer doesn't know anything about the specific report — it just maps columns to headers and rows to cells. Chart configs are stored separately on the frontend, keyed by report ID. And when the user opens the AI chat alongside a report, this same object gets serialized to a markdown table and handed to Claude.

Adding a new report means writing a function that returns a ReportResult. Everything else — the table, the chart, the AI context — already works.

Walking Through monthly_cashflow

monthly_cashflow is a good one to understand the pattern. Most users want to know: what came in, what went out, and what was left each month?

The query is minimal — grab every transaction for this account, take only the date and amount:

const rows = await db.transaction.findMany({
  where: { credentialId, AND: [PARENT_ONLY] },
  select: { initiatedAt: true, amount: true },
  orderBy: { initiatedAt: 'asc' },
});

PARENT_ONLY appears in every report query:

const PARENT_ONLY = {
  OR: [
    { referenceIdType: { not: 'TXN' } },
    { referenceIdType: null },
  ],
};

PayPal stores internal accounting entries alongside real transactions — records with referenceIdType = 'TXN' that represent steps within a single payment, not separate payments. Without this filter, counts and sums are inflated 2–3×.

The aggregation happens in JavaScript rather than the database:

const byMonth: Record<string, { inflow: number; outflow: number }> = {};
for (const r of rows) {
  const key = r.initiatedAt.toISOString().slice(0, 7); // YYYY-MM
  if (!byMonth[key]) byMonth[key] = { inflow: 0, outflow: 0 };
  const n = Number(r.amount);
  if (n >= 0) byMonth[key].inflow += n;
  else byMonth[key].outflow += n;
}

Positive amounts go into inflow, negative amounts into outflow. Net is computed at return time. For most PayPal accounts this is fine — a few thousand transactions is nothing to reduce in memory. For very high-volume accounts you'd want a DB-level GROUP BY instead.

The output is a standard ReportResult:

return {
  title: 'Monthly Cashflow (In vs Out)',
  columns: [
    { key: 'month', label: 'Month', align: 'left' },
    { key: 'in',    label: 'Money In',  align: 'right' },
    { key: 'out',   label: 'Money Out', align: 'right' },
    { key: 'net',   label: 'Net',       align: 'right' },
  ],
  rows: Object.entries(byMonth)
    .sort(([a], [b]) => b.localeCompare(a))
    .map(([month, v]) => ({
      month,
      in:  v.inflow.toFixed(2),
      out: v.outflow.toFixed(2),
      net: (v.inflow + v.outflow).toFixed(2),
    })),
};

The frontend receives this and renders a table. It doesn't know it's looking at a cashflow report — it just maps columns to headers and rows to cells.

Charts

Charts are configured entirely on the frontend, in a map from report ID to chart spec. monthly_cashflow gets a grouped-bar chart with green bars for inflows and red bars for outflows:

monthly_cashflow: {
  type: 'grouped-bar',
  xKey: 'month',
  yKeys: ['in', 'out'],
  colors: ['#22c55e', '#ef4444'],
},

If a report ID has no entry in this map, only the table renders. 16 of the 27 reports have chart configs. The backend doesn't know or care — it returns the same ReportResult either way.

This separation paid off in practice. I added charts to several reports after the fact without touching the backend at all. And adding new reports doesn't require touching the chart config unless you want one.

From Table to AI Chat

Every prebuilt report has a floating chat widget. When the user opens it, the current ReportResult gets serialized to a markdown table and injected into Claude's system prompt:

function formatReportContext(report: ReportResult): string {
  const header  = report.columns.map((c) => c.label).join(' | ');
  const divider = report.columns.map(() => '---').join(' | ');
  const rows    = report.rows.slice(0, 50).map((row) =>
    report.columns.map((c) => String(row[c.key] ?? '')).join(' | ')
  );
  return `Report: ${report.title}\n\n${[header, divider, ...rows].join('\n')}`;
}

For monthly_cashflowthis produces a plain markdown table of all the month-by-month data. Claude receives it and can answer questions directly — "which month had the highest net?" or "is the trend improving?" — without calling any tools.

If the user asks something that requires more granularity ("show me the individual transactions from March"), Claude switches to the transaction search tools and queries the database for the specifics.

The bridge between the report and the AI is just a string. ReportResult → markdown → system prompt. No special API, no new endpoints. The formatReportContext() function is 8 lines.

What I Learned

A shared output contract unlocks everything. ReportResult is a small type, but because every report returns it, the table renderer, the chart config lookup, and the AI serializer all work automatically for any report you add. Designing the interface first made the whole system composable.

Separate data from presentation. The backend only knows about data. The frontend only knows about rendering. ReportResult is the boundary. This let me add charts, tweak column labels, and iterate on the frontend without touching a single backend query — and add new backend reports without risking any frontend regressions.