QR for Agent

Instructions

Implementation Reference

• src/modules/conversions/conversions.service.ts:48-128 (handler)

  The getConversions service function that performs the database queries to aggregate conversion data.

  export function getConversions(
    qrCodeId: number,
    shortId: string,
    period: string = "30d",
    eventFilter?: string
  ) {
    const periodStart = getPeriodStart(period);
  
    // Build conditions
    const conditions = [eq(conversionEvents.qrCodeId, qrCodeId)];
    if (periodStart) conditions.push(gt(conversionEvents.createdAt, periodStart));
    if (eventFilter) conditions.push(eq(conversionEvents.eventName, eventFilter));
  
    const whereClause = conditions.length === 1 ? conditions[0] : and(...conditions);
  
    // Total conversions
    const [{ total }] = db
      .select({ total: count() })
      .from(conversionEvents)
      .where(whereClause)
      .all();
  
    // Total value
    const [{ totalValue }] = db
      .select({ totalValue: sql<string | null>`SUM(CAST(${conversionEvents.value} AS REAL))` })
      .from(conversionEvents)
      .where(whereClause)
      .all();
  
    // By event
    const byEvent = db.all<{ event_name: string; count: number; total_value: string | null }>(
      sql`SELECT ${conversionEvents.eventName} as event_name, COUNT(*) as count,
          SUM(CAST(${conversionEvents.value} AS REAL)) as total_value
          FROM conversion_events
          WHERE ${whereClause}
          GROUP BY ${conversionEvents.eventName}
          ORDER BY count DESC`
    );
  
    // By day
    const byDay = db.all<{ date: string; count: number; total_value: string | null }>(
      sql`SELECT date(${conversionEvents.createdAt}) as date, COUNT(*) as count,
          SUM(CAST(${conversionEvents.value} AS REAL)) as total_value
          FROM conversion_events
          WHERE ${whereClause}
          GROUP BY date(${conversionEvents.createdAt})
          ORDER BY date ASC`
    );
  
    // Recent events (last 20)
    const recent = db
      .select()
      .from(conversionEvents)
      .where(whereClause)
      .orderBy(sql`${conversionEvents.createdAt} DESC`)
      .limit(20)
      .all();
  
    return {
      short_id: shortId,
      total_conversions: total,
      total_value: totalValue ? parseFloat(totalValue) : 0,
      period,
      by_event: byEvent.map((e) => ({
        event_name: e.event_name,
        count: e.count,
        total_value: e.total_value ? parseFloat(e.total_value) : 0,
      })),
      by_day: byDay.map((d) => ({
        date: d.date,
        count: d.count,
        total_value: d.total_value ? parseFloat(d.total_value) : 0,
      })),
      recent_events: recent.map((r) => ({
        event_name: r.eventName,
        value: r.value ? parseFloat(r.value) : null,
        metadata: r.metadata ? JSON.parse(r.metadata) : null,
        created_at: r.createdAt,
      })),
    };
  }

• src/modules/conversions/conversions.routes.ts:174-209 (handler)

  The API route handler that receives the request and calls the getConversions service function.

  // GET /api/conversions/:shortId — conversion stats
  app.get(
    "/:shortId",
    {
      schema: {
        ...conversionStatsSchema,
        tags: ["Conversions"],
        summary: "Get conversion statistics for a QR code",
        description:
          "Returns conversion totals, breakdowns by event name, daily trends, and recent events. Filter by period and event name.",
      },
    },
    async (request, reply) => {
      const { shortId } = request.params as { shortId: string };
      const { period = "30d", event } = request.query as {
        period?: string;
        event?: string;
      };
  
      const qr = db
        .select()
        .from(qrCodes)
        .where(
          and(
            eq(qrCodes.shortId, shortId),
            eq(qrCodes.apiKeyId, request.apiKeyId)
          )
        )
        .get();
  
      if (!qr) {
        return sendError(reply, 404, Errors.notFound("QR code", shortId));
      }
  
      return getConversions(qr.id, shortId, period, event);
    }

• packages/mcp/src/tools.ts:869-888 (registration)

  The MCP tool registration for 'get_conversions', which proxies the request to the HTTP API.

  get_conversions: {
    description:
      "Get conversion statistics for a QR code. Returns total conversions, total value, breakdowns by event name, daily trends, and recent events. Use this to measure QR code ROI and understand which codes drive the most value.",
    inputSchema: z.object({
      short_id: z.string().describe("The short_id of the QR code to get conversion stats for."),
      period: z
        .enum(["7d", "30d", "90d", "all"])
        .default("30d")
        .describe("Time period for aggregations. Default: 30d."),
      event: z
        .string()
        .optional()
        .describe("Filter by event name (e.g., 'purchase')."),
    }),
    handler: async (input: { short_id: string; period: string; event?: string }) => {
      const query: Record<string, string> = { period: input.period };
      if (input.event) query.event = input.event;
      return apiRequest(`/api/conversions/${input.short_id}`, { query });
    },
  },