artifacts/api-server/src/routes/usage.ts

import { Router, type IRouter } from "express";
import { and, eq, gte, sql } from "drizzle-orm";
import { db, usageEvents, users } from "@workspace/db";
import { requireAuth } from "../middlewares/auth";

const router: IRouter = Router();
router.use(requireAuth);

router.get("/usage", async (req, res, next) => {
  try {
    const period = (String(req.query.period || "current") as
      | "current"
      | "last_30d"
      | "all");
    const now = new Date();
    let start: Date;
    if (period === "last_30d") {
      start = new Date(now.getTime() - 30 * 24 * 60 * 60 * 1000);
    } else if (period === "all") {
      start = new Date(0);
    } else {
      start = req.user!.periodStart;
    }
    const conds = and(
      eq(usageEvents.userId, req.user!.id),
      gte(usageEvents.createdAt, start),
    );
    const byModel = await db
      .select({
        model_id: usageEvents.modelId,
        input_tokens: sql<number>`coalesce(sum(${usageEvents.inputTokens}),0)::int`,
        output_tokens: sql<number>`coalesce(sum(${usageEvents.outputTokens}),0)::int`,
      })
      .from(usageEvents)
      .where(conds)
      .groupBy(usageEvents.modelId);
    const byDay = await db
      .select({
        date: sql<string>`to_char(${usageEvents.createdAt}, 'YYYY-MM-DD')`,
        input_tokens: sql<number>`coalesce(sum(${usageEvents.inputTokens}),0)::int`,
        output_tokens: sql<number>`coalesce(sum(${usageEvents.outputTokens}),0)::int`,
      })
      .from(usageEvents)
      .where(conds)
      .groupBy(sql`to_char(${usageEvents.createdAt}, 'YYYY-MM-DD')`)
      .orderBy(sql`to_char(${usageEvents.createdAt}, 'YYYY-MM-DD')`);

    const fresh = (await db
      .select({ tokens_used: users.tokensUsed, tokens_limit: users.tokensLimit })
      .from(users)
      .where(eq(users.id, req.user!.id))
      .limit(1))[0]!;

    res.json({
      period_start: start.toISOString(),
      period_end:
        period === "current" ? req.user!.periodEnd.toISOString() : now.toISOString(),
      tokens_used: Number(fresh.tokens_used),
      tokens_limit: Number(fresh.tokens_limit),
      by_model: byModel,
      by_day: byDay,
    });
  } catch (err) {
    next(err);
  }
});

export default router;