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 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 | 2x 2x 192x 26x 3x 3x 3x 192x 2x 13x 13x 13x 13x 13x 1x 12x 13x 13x 12x 96x 2x 2x 16x 1x | /**
* The referral funnel (FR-I-3).
*
* Mounted under `/api/admin/rrm`, where the parent router has already run
* `contextMiddleware` and `requirePlatformAdmin`.
*
* Counted from `referral_events`, not from `referrals.status`, for the same
* reason `metrics.routes.ts` counts stage changes rather than current stage: a
* funnel step means "how many ever got this far", and a row's current status
* has forgotten everywhere it has been. A referral that reached `verified` and
* was later `lost` belongs in the verified count.
*
* COUNT(DISTINCT referral_id), always. One verify writes TWO rows —
* `referral.verified` carrying the money and `referral.verified.transition` for
* the audit — and so, now, does one conversion. `count(*)` would report double
* for exactly the two steps the programme is paid on.
*
* PER TIER IS NOT POSSIBLE FOR MOST PARTNERS, and the response says so rather
* than quietly under-reporting. Tier lives on `rrm_prospects`, and
* `partners.prospect_id` is null for every self-registered partner — which is
* all of them outside the recruitment campaign. The tier breakdown therefore
* covers recruited partners only, and carries the number it left out.
*/
import { and, eq, gte, lte, sql } from "drizzle-orm";
import { Hono } from "hono";
import type { Dal } from "../../../dal";
import {
referralEvents as EVENTS,
partners as PARTNERS,
rrmProspects as PROSPECTS,
} from "../../../db/schema";
import type { ReferralEventType } from "../../../db/schema/enums";
import { ValidationError } from "../../../lib/errors";
import { handleError, success } from "../../../lib/response";
type Env = {
Bindings: CloudflareBindings;
Variables: {
user: { id: string } | null;
session: unknown;
dal: Dal;
};
};
const funnel = new Hono<Env>();
/**
* The steps, in order, each with the event types that mean "got this far".
*
* `verified` lists both spellings on purpose: the money-carrying event and the
* audit transition are written by different code paths, and a funnel that knew
* about only one would silently miss every referral verified by the other. The
* DISTINCT is what makes listing both safe.
*/
const STEPS: ReadonlyArray<{
key: string;
types: readonly ReferralEventType[];
}> = [
{ key: "created", types: ["referral.created"] },
{ key: "forwarded", types: ["referral.forwarded"] },
{ key: "opened", types: ["referral.link_opened"] },
{ key: "engaged", types: ["referral.engaged"] },
{
key: "verified",
types: ["referral.verified", "referral.verified.transition"],
},
{ key: "contacted", types: ["referral.contacted"] },
{ key: "quoted", types: ["referral.quoted"] },
{
key: "converted",
types: ["referral.converted", "referral.converted.transition"],
},
];
function step(types: readonly ReferralEventType[]) {
return sql<number>`count(distinct case when ${EVENTS.type} in ${types} then ${EVENTS.referralId} end)`;
}
function parseBoundary(raw: string | undefined, label: string): Date | null {
if (raw === undefined || raw === "") return null;
const parsed = new Date(raw);
Iif (Number.isNaN(parsed.getTime())) {
throw new ValidationError(`${label} must be an ISO date`);
}
return parsed;
}
/** Every step in one statement — D1 charges per statement. */
function selection() {
return Object.fromEntries(STEPS.map((s) => [s.key, step(s.types)])) as Record<
string,
ReturnType<typeof step>
>;
}
// GET /api/admin/rrm/referral-funnel?from=&to=
funnel.get("/referral-funnel", async (c) => {
try {
const dal = c.get("dal");
const from = parseBoundary(c.req.query("from"), "from");
const to = parseBoundary(c.req.query("to"), "to") ?? new Date();
if (from && from.getTime() > to.getTime()) {
throw new ValidationError("from must be before to");
}
/**
* FR-I-3's "per partner". Scoped by id rather than grouped over
* everyone: an operator asks this question about ONE partner they have
* open, and a row per partner would be the whole table on one response
* for a chart nobody reads that way.
*/
const partnerId = c.req.query("partnerId")?.trim() || null;
const window = and(
from ? gte(EVENTS.occurredAt, from) : undefined,
lte(EVENTS.occurredAt, to),
partnerId
? sql`${EVENTS.referralId} in (select id from referrals where partner_id = ${partnerId})`
: undefined,
);
const [[overall], byTier, [{ recruited, total }]] = await Promise.all([
dal.db.select(selection()).from(EVENTS).where(window),
// Recruited partners only — the join is the filter. A LEFT join
// would put every self-registered partner in a `null` bucket, which
// reads as a tier rather than as an absence.
dal.db
.select({ tier: PROSPECTS.tier, ...selection() })
.from(EVENTS)
.innerJoin(
PARTNERS,
eq(
PARTNERS.id,
sql`(
select ${sql.identifier("partner_id")} from referrals
where referrals.id = ${EVENTS.referralId}
)`,
),
)
.innerJoin(PROSPECTS, eq(PROSPECTS.id, PARTNERS.prospectId))
.where(window)
.groupBy(PROSPECTS.tier),
// How much of the programme the tier cut can see at all.
dal.db
.select({
recruited: sql<number>`sum(case when ${PARTNERS.prospectId} is not null then 1 else 0 end)`,
total: sql<number>`count(*)`,
})
.from(PARTNERS),
]);
return success(c, {
from: from?.toISOString() ?? null,
to: to.toISOString(),
partnerId,
steps: STEPS.map((s) => ({
key: s.key,
referrals: Number(
(overall as unknown as Record<string, unknown>)?.[s.key] ?? 0,
),
})),
// Empty when scoped to one partner: a tier breakdown of one person
// is a chart with one bar and a label they might not even have.
byTier: (partnerId ? [] : byTier).map((row) => {
const counts = row as unknown as Record<string, unknown>;
return {
tier: row.tier,
steps: STEPS.map((s) => ({
key: s.key,
referrals: Number(counts[s.key] ?? 0),
})),
};
}),
/**
* The tier cut's own footnote. Without it a chart covering 12 of 400
* partners looks like a chart of the programme, and the reader has
* no way to tell.
*/
tierCoverage: {
recruitedPartners: Number(recruited ?? 0),
totalPartners: Number(total ?? 0),
},
});
} catch (err) {
return handleError(c, err);
}
});
export default funnel;
|