Adding AI Reports to a PayPal Analytics App

The Pattern: Compute First, Then Prompt

In the chat feature, Claude decides at query time what data to fetch using tools. Here, all the database queries run before Claude is involved. Claude never touches the database — it receives a pre-formatted data block and writes the report from that.

The flow:

1. Compute a set of business metrics across two 90-day periods (current and prior)
2. Serialize them into a structured markdown block
3. Send the block to Claude with a prompt that specifies the exact output format
4. Store the response as a report record in the database

This is a deliberate tradeoff. Compared to letting Claude drive the queries: it's less flexible (Claude can only interpret what we computed, not explore the data freely), but it's more predictable (we know exactly what data Claude has, and the output format is enforced at the prompt level, not left to Claude's judgment).

Computing the Metrics

The core function is computeMetrics(credentialId, start, end) in backend/src/lib/analyzeCredential.ts. It runs four parallel database queries for a given time window:

const [grossAgg, feeAgg, totalCount, refundCount] = await Promise.all([
  db.transaction.aggregate({
    where: { credentialId, initiatedAt: { gte: start, lte: end }, status: 'S', amount: { gt: 0 }, currencyCode: currency, AND: [PARENT_ONLY] },
    _sum: { amount: true },
    _count: true,
  }),
  db.transaction.aggregate({
    where: { credentialId, initiatedAt: { gte: start, lte: end }, status: 'S', feeAmount: { not: null }, currencyCode: currency, AND: [PARENT_ONLY] },
    _sum: { feeAmount: true },
  }),
  db.transaction.count({ where: { credentialId, initiatedAt: { gte: start, lte: end }, AND: [PARENT_ONLY] } }),
  db.transaction.count({
    where: {
      credentialId,
      initiatedAt: { gte: start, lte: end },
      OR: [{ eventCode: { in: ['T1107', 'T1109', 'T1106'] } }, { status: 'V' }],
      AND: [PARENT_ONLY],
    },
  }),
]);

This runs twice — once for the current 90 days and once for the prior 90 days — also in parallel. Period-over-period comparison is where the actionable signal lives: a fee rate of 3.2% means little on its own; a fee rate that went from 2.1% to 3.2% is worth flagging.

Beyond computeMetrics(), three more queries run for the current period:

New vs returning customers— a raw SQL CTE that splits the current period's customers into those who had any prior activity and those who didn't. Prisma's ORM can't express this cleanly, so it goes through db.$queryRaw:

WITH current_customers AS (
  SELECT DISTINCT "counterpartyAccountId" FROM "Transaction"
  WHERE "credentialId" = $1 AND "initiatedAt" >= $2 AND "initiatedAt" <= $3
    AND "status" = 'S' AND "amount" > 0 AND ("referenceIdType" != 'TXN' OR "referenceIdType" IS NULL)
),
prior_activity AS (
  SELECT DISTINCT "counterpartyAccountId" FROM "Transaction"
  WHERE "credentialId" = $1 AND "initiatedAt" < $2 AND "status" = 'S' AND "amount" > 0
    AND ("referenceIdType" != 'TXN' OR "referenceIdType" IS NULL)
)
SELECT
  COUNT(*) FILTER (WHERE pa."counterpartyAccountId" IS NULL)    AS new_customers,
  COUNT(*) FILTER (WHERE pa."counterpartyAccountId" IS NOT NULL) AS returning_customers
FROM current_customers cc
LEFT JOIN prior_activity pa ON cc."counterpartyAccountId" = pa."counterpartyAccountId"

Top 5 customers by revenue and top 5 products by revenue — straightforward groupBy aggregations. The PARENT_ONLY filter applies everywhere (same reason as in the chat tools: PayPal stores internal accounting entries alongside real transactions and they inflate all metrics if you include them).

The Data Block

All metrics are serialized into a markdown block before being sent to Claude. It looks like what a human analyst would prepare before asking a question:

## Current Period (Apr 14, 2026 – Jul 13, 2026)
- Gross revenue: 18420.50 USD
- Net revenue (after fees): 17304.27 USD
- Total fees paid: 1116.23 USD (6.1% of revenue)
- Transactions: 142
- Average transaction value: 129.72 USD
- Refunds/reversals: 4 (2.8% rate)
- Unique customers: 89 (23 new, 66 returning)

## Prior Period (Jan 13, 2026 – Apr 13, 2026)
- Gross revenue: 15210.80 USD
...

## Period-over-Period Changes (current vs prior 90 days)
- Gross revenue: +21.1%
- Transaction count: +18.3%
- Unique customers: +12.7%
...

## Top 5 Customers (Current Period)
1. Emma Wilson: 2840.00 USD (8 transactions)
...

One deliberate choice: percentage changes are computed in code (pctChange()) and included in the data block, rather than letting Claude calculate them. Claude is reliable at interpreting trends; it's less reliable at arithmetic over large numbers. Pre-computing also means the numbers Claude references in the report match the numbers we'd compute ourselves if we spot-checked.

If fewer than 5 transactions exist in the current period, a hasLimitedData flag adds a warning line to the block. Claude uses this to hedge its conclusions rather than generating a confident-sounding report from a thin sample.

Structured Prompt → Structured Output

The prompt that goes to Claude specifies not just what to write, but exactly how to structure it:

Write a report with exactly these three sections:

## Summary
2–3 sentences summarising overall business health.

## Highlights
3–5 bullet points of positive developments. Prefix each with ✅.

## Concerns
3–5 bullet points of negative developments or areas to watch. Prefix each with ⚠️.
If there are genuinely no concerns, write one bullet noting the business is healthy.

Use specific numbers and percentages from the data. Keep it concise.

The frontend renders this directly. It knows ## Summary always exists, that ✅ marks positive bullets and ⚠️ marks concerns. No post-processing, no regex, no parsing step. The structure is enforced at the prompt level and it holds consistently across runs.

One more choice: this uses anthropic.messages.create(), not streaming. The report is short — around 400–600 tokens — and gets stored atomically to the database. Streaming a background job would add complexity for no user-visible benefit.

Scheduled + On-Demand

The same runAnalysis(reportId, credentialId) function handles both trigger paths.

Scheduled: a worker pings POST /api/analysis/run-all with a shared WORKER_SECRET. The handler calls runAllAnalysis() in the background and returns immediately. That function iterates over all non-demo credentials, creates a RUNNING report record for each, then calls runAnalysis() which updates the record to COMPLETED or FAILED when done.

On-demand: an authenticated user calls POST /api/analysis/run. The handler creates the report record and fires runAnalysis() as a fire-and-forget:

const report = await db.analysisReport.create({
  data: {
    credentialId,
    trigger: AnalysisTrigger.MANUAL,
    status: AnalysisStatus.RUNNING,
    periodStart: subDays(now, 90),
    periodEnd: now,
  },
});

runAnalysis(report.id, credentialId).catch((err) =>
  console.error('[analysis] on-demand run error:', err)
);

res.status(202).json({ reportId: report.id, status: 'RUNNING' });

The HTTP response returns immediately with 202 Accepted. The frontend polls GET /api/analysis/:id until the status changes from RUNNING. The DB record is the source of truth — if runAnalysis() throws, it catches the error and marks the record FAILED before exiting.

What I Learned

Compute before you prompt. Run your DB queries first. Hand Claude a clean, formatted data block. This is faster, more predictable, and easier to debug than letting Claude drive the queries in real time. You can print the data block to a log, inspect it, and know exactly what Claude received when a report looks wrong.

Structured prompt, structured output. When you need consistent formatting — for a report that will be rendered the same way every time — specify the exact structure in the prompt. Exact headers, exact emoji prefixes, exact section order. Claude will follow it reliably, and your frontend stays simple.