get_conversions

Instructions

Implementation Reference

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

  Backend service function getConversions() that queries the database for conversion stats: total count, total value, breakdown by event, daily trends, and recent events.

  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.schemas.ts:43-69 (schema)

  Fastify JSON schema for the conversion stats API endpoint (GET /:shortId) with shortId param, period enum, and optional event filter.

  export const conversionStatsSchema = {
    params: {
      type: "object" as const,
      required: ["shortId"],
      properties: {
        shortId: {
          type: "string",
          description: "The short_id of the QR code to get conversion stats for.",
        },
      },
    },
    querystring: {
      type: "object" as const,
      properties: {
        period: {
          type: "string",
          enum: ["7d", "30d", "90d", "all"],
          default: "30d",
          description: "Time period for aggregations. Default: 30d.",
        },
        event: {
          type: "string",
          description: "Filter by event name (e.g., 'purchase').",
        },
      },
    },
  };