Press n or j to go to the next uncovered block, b, p or k for the previous block.
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 | 1x 1x 1x 5x 5x 5x 5x 5x 4x 4x 1x 20x 20x 20x 16x 16x | // Public leadership-metrics endpoint. Mounted in index.ts BEFORE the guarded
// `/api/internal` sub-app so the X-Internal-API-Key check does not apply.
// Anyone with the URL can read; the leadership team shares the link manually.
//
// No caching on purpose. Three readers, polling once per minute, four queries
// per call — D1 swallows that load with no problem. Caching here introduced a
// divergence between cached and ?fresh=1 paths that broke E2E (E2E sees fresh
// numbers, dashboard sees stale cache that doesn't get warmed by the fresh
// path) and bought nothing measurable in return.
import { Hono } from "hono";
import { sql } from "drizzle-orm";
import { getDb } from "../../db";
import { hoUsers } from "../../db/schema/homeowner";
import { inquiries } from "../../db/schema/inquiries";
import { landingLeads } from "../../db/schema/landing-leads";
import { pros } from "../../db/schema/pros";
import { handleError, success } from "../../lib/response";
type Env = { Bindings: CloudflareBindings };
const SECONDS_PER_DAY = 86_400;
export type MetricsWindow = {
d1: number;
d2: number;
d7: number;
d30: number;
};
export type MetricsResponse = {
leads: MetricsWindow;
landingLeads: MetricsWindow;
pros: MetricsWindow;
homeowners: MetricsWindow;
generatedAt: string;
};
const metrics = new Hono<Env>();
// GET /api/leadership-metrics — point-in-time signup/lead counts.
// Always computed fresh — see file header for the rationale.
metrics.get("/", async (c) => {
try {
const db = getDb(c.env.DB);
const now = Math.floor(Date.now() / 1000);
const cutoffs = {
d1: now - SECONDS_PER_DAY,
d2: now - 2 * SECONDS_PER_DAY,
d7: now - 7 * SECONDS_PER_DAY,
d30: now - 30 * SECONDS_PER_DAY,
} as const;
const [leads, landingLeadCounts, prosCounts, homeowners] =
await Promise.all([
countWindow(db, "inquiries", cutoffs),
countWindow(db, "landing_leads", cutoffs),
countWindow(db, "pros", cutoffs),
countWindow(db, "ho_users", cutoffs),
]);
const data: MetricsResponse = {
leads,
landingLeads: landingLeadCounts,
pros: prosCounts,
homeowners,
generatedAt: new Date().toISOString(),
};
return success(c, data);
} catch (err) {
return handleError(c, err);
}
});
type TableName = "inquiries" | "landing_leads" | "pros" | "ho_users";
async function countWindow(
db: ReturnType<typeof getDb>,
tableName: TableName,
cutoffs: Record<keyof MetricsWindow, number>,
): Promise<MetricsWindow> {
// ho_users uses created_at; inquiries + pros use date_created.
// Reference the Drizzle table objects so the column names resolve through
// the schema (snake_case in SQL, camelCase in TS).
const ts =
tableName === "ho_users"
? hoUsers.createdAt
: tableName === "inquiries"
? inquiries.dateCreated
: tableName === "landing_leads"
? landingLeads.dateCreated
: pros.dateCreated;
const table =
tableName === "ho_users"
? hoUsers
: tableName === "inquiries"
? inquiries
: tableName === "landing_leads"
? landingLeads
: pros;
const result = await db
.select({
d1: sql<number>`coalesce(sum(case when ${ts} >= ${cutoffs.d1} then 1 else 0 end), 0)`,
d2: sql<number>`coalesce(sum(case when ${ts} >= ${cutoffs.d2} then 1 else 0 end), 0)`,
d7: sql<number>`coalesce(sum(case when ${ts} >= ${cutoffs.d7} then 1 else 0 end), 0)`,
d30: sql<number>`coalesce(sum(case when ${ts} >= ${cutoffs.d30} then 1 else 0 end), 0)`,
})
.from(table);
const row = result[0];
return {
d1: Number(row?.d1 ?? 0),
d2: Number(row?.d2 ?? 0),
d7: Number(row?.d7 ?? 0),
d30: Number(row?.d30 ?? 0),
};
}
export default metrics;
|