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 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 | 22x 22x 16x 16x 16x 16x 16x 16x 437x 93x 93x 93x 93x 93x 93x 93x 104x 103x 25x 25x 21x 20x 78x 78x 11x 63x 63x 73x 73x 73x 11x 62x 62x 62x 62x 62x 73x 73x 62x 62x 3x 59x 24x | import { and, asc, desc, eq, gte, lt, sql } from "drizzle-orm";
import type { DrizzleD1Database } from "drizzle-orm/d1";
import * as schema from "../../db/schema";
import type {
REFERRAL_ACTOR_TYPES,
ReferralEventType,
} from "../../db/schema/enums";
import { generateId } from "../../lib/ids";
import { generateReferralCode } from "../../lib/partners/code";
import {
decideReferralState,
type ReferralState,
} from "../../lib/partners/state";
import { isUniqueViolation } from "./partners.dal";
const REFERRALS = schema.referrals;
const EVENTS = schema.referralEvents;
export type Referral = typeof schema.referrals.$inferSelect;
export type ReferralEvent = typeof schema.referralEvents.$inferSelect;
export type ReferralActor = (typeof REFERRAL_ACTOR_TYPES)[number];
export class IllegalTransitionError extends Error {
constructor(
public referralId: string,
public from: ReferralState,
public to: ReferralState,
public reason: string,
) {
super(`Referral ${referralId}: ${from} -> ${to} refused (${reason})`);
this.name = "IllegalTransitionError";
}
}
export type CreateReferralInput = {
partnerId: string;
contactName: string;
contactPhoneNorm: string;
society?: string | null;
societyId?: string | null;
config?: string | null;
possessionBand?: string | null;
mode?: (typeof schema.referrals.$inferInsert)["mode"];
/** Set when this lost a dedupe race — stored, never dropped (FR-F-1). */
status?: ReferralState;
firstReferrerReferralId?: string | null;
/** FR-F-6. Salted digests only — see `lib/partners/ip.ts`. Never an IP. */
ipHash?: string | null;
ipPrefixHash?: string | null;
};
export class ReferralsDal {
constructor(private db: DrizzleD1Database<typeof schema>) {}
/**
* Create a referral and its first event, as one batch.
*
* D1 has no transactions, so `db.batch()` is the only way these two writes
* land together. Without it a crash between them leaves a referral whose
* timeline begins with nothing — and FR-ST-2 makes the event log the ONLY
* source of truth for what happened, so a missing first event is not a
* cosmetic gap, it is a referral that cannot be explained to the partner
* who created it.
*
* The code is generated here and retried on collision: `referrals_code_uniq`
* is what makes a collision a retry rather than two homeowners sharing a
* link.
*/
async create(input: CreateReferralInput): Promise<Referral> {
const now = new Date();
for (let attempt = 0; attempt < 5; attempt++) {
const row: typeof schema.referrals.$inferInsert = {
id: generateId(),
partnerId: input.partnerId,
code: generateReferralCode(),
contactName: input.contactName,
contactPhoneNorm: input.contactPhoneNorm,
society: input.society ?? null,
societyId: input.societyId ?? null,
config: input.config ?? null,
possessionBand: input.possessionBand ?? null,
status: input.status ?? "draft",
mode: input.mode ?? "direct",
firstReferrerReferralId: input.firstReferrerReferralId ?? null,
ipHash: input.ipHash ?? null,
ipPrefixHash: input.ipPrefixHash ?? null,
dateCreated: now,
dateUpdated: now,
};
const event: typeof schema.referralEvents.$inferInsert = {
id: generateId(),
referralId: row.id as string,
type:
row.status === "duplicate"
? "referral.duplicate"
: "referral.created",
actorType: "partner",
actorId: input.partnerId,
payload: JSON.stringify({ mode: row.mode, society: row.society }),
occurredAt: now,
};
try {
await this.db.batch([
this.db.insert(REFERRALS).values(row),
this.db.insert(EVENTS).values(event),
]);
return row as Referral;
} catch (error) {
// Only a code collision is retryable. Anything else — a missing
// column, a failed event insert — must surface rather than be
// retried four more times against the same broken statement.
if (attempt < 4 && isUniqueViolation(error)) continue;
throw error;
}
}
throw new Error(
"Could not allocate a unique referral code after 5 attempts",
);
}
async findById(id: string): Promise<Referral | null> {
const rows = await this.db
.select()
.from(REFERRALS)
.where(eq(REFERRALS.id, id))
.limit(1);
return rows[0] ?? null;
}
async findByCode(code: string): Promise<Referral | null> {
const rows = await this.db
.select()
.from(REFERRALS)
.where(eq(REFERRALS.code, code))
.limit(1);
return rows[0] ?? null;
}
/** The partner's own list, newest first. */
async listForPartner(partnerId: string, limit = 100): Promise<Referral[]> {
return this.db
.select()
.from(REFERRALS)
.where(eq(REFERRALS.partnerId, partnerId))
.orderBy(desc(REFERRALS.dateCreated))
.limit(Math.min(limit, 200));
}
/** The promise-tracker source. Append-only, so ordering is creation order. */
async eventsFor(referralId: string): Promise<ReferralEvent[]> {
return this.db
.select()
.from(EVENTS)
.where(eq(EVENTS.referralId, referralId))
.orderBy(asc(EVENTS.occurredAt));
}
/**
* FR-F-1 dedupe: the EARLIEST prior referral of this number, by anyone.
*
* First referrer wins, so the order matters and the caller must not assume
* "any match" — a number referred three times has one winner and two
* losers, and the second loser must be told about the first winner, not
* about the first loser.
*
* `reversed`-equivalent states are excluded: a referral that was rejected or
* expired never earned anything and must not block a later, better one.
*/
async findFirstReferralOf(
contactPhoneNorm: string,
): Promise<Referral | null> {
const rows = await this.db
.select()
.from(REFERRALS)
.where(
and(
eq(REFERRALS.contactPhoneNorm, contactPhoneNorm),
sql`${REFERRALS.status} NOT IN ('duplicate','rejected','expired')`,
),
)
.orderBy(asc(REFERRALS.dateCreated))
.limit(1);
return rows[0] ?? null;
}
/**
* Drafts older than `cutoff` that nobody forwarded — the FR-P-3.5 expiry
* sweep's input. Oldest first, so a backlog drains in creation order and
* the `limit` never starves the same rows tick after tick.
*/
async listStaleDrafts(cutoff: Date, limit = 200): Promise<Referral[]> {
return this.db
.select()
.from(REFERRALS)
.where(
and(eq(REFERRALS.status, "draft"), lt(REFERRALS.dateCreated, cutoff)),
)
.orderBy(asc(REFERRALS.dateCreated))
.limit(limit);
}
/**
* Referrals this partner created since `since`. The cap count (FR-F-3).
*
* Counts EVERY referral including duplicates and rejections, deliberately.
* The cap exists to bound how much a partner can submit, and a cap that
* only counted successes would let someone spray a thousand numbers as long
* as they were all already known to us.
*/
async countCreatedSince(partnerId: string, since: Date): Promise<number> {
const rows = await this.db
.select({ n: sql<number>`count(*)` })
.from(REFERRALS)
.where(
and(
eq(REFERRALS.partnerId, partnerId),
gte(REFERRALS.dateCreated, since),
),
);
return Number(rows[0]?.n ?? 0);
}
/**
* Move a referral's state, through the state machine, writing the event.
*
* The transition is DECIDED by lib/partners/state.ts and refused here if
* illegal — FR-ST-1's "rejected and logged, never coerced". Both writes go
* in one batch for the same reason `create` does.
*
* Both writes are also predicated on the row STILL being in the state the
* caller read. `args.referral` is a snapshot, and between that read and
* this write someone else may have moved the row — the expiry sweep
* selecting a draft the partner forwards a moment later, or an operator's
* double-click. Without the predicate the second writer silently overwrote
* the first and appended an event that never happened. With it, neither
* statement matches and the caller gets an `IllegalTransitionError`,
* exactly as if the state machine had refused the move — which, against
* the row's real state, it would have.
*/
async transition(args: {
referral: Referral;
to: ReferralState;
actorType: ReferralActor;
actorId?: string | null;
eventType?: ReferralEventType;
payload?: Record<string, unknown>;
viaHomeowner?: boolean;
now?: Date;
}): Promise<Referral> {
const now = args.now ?? new Date();
const decision = decideReferralState(
args.referral.status as ReferralState,
args.to,
{ viaHomeowner: args.viaHomeowner },
);
if (!decision.change) {
throw new IllegalTransitionError(
args.referral.id,
args.referral.status as ReferralState,
args.to,
decision.reason,
);
}
// Stamp the milestone columns the dashboard reads, so the common queries
// do not have to reduce the event log every time.
const patch: Partial<typeof schema.referrals.$inferInsert> = {
status: decision.state,
dateUpdated: now,
};
if (decision.state === "forwarded") patch.forwardedAt = now;
if (decision.state === "engaged") patch.engagedAt = now;
if (decision.state === "verified") patch.verifiedAt = now;
// Annotated so the default type is checked against the enum too. The
// column is `text()`, so without this the one place that BUILDS an
// event type by template is the one place the guardrail cannot see.
const eventType: ReferralEventType =
args.eventType ?? (`referral.${decision.state}` as ReferralEventType);
// "Still where we read it." One batch is one transaction, so the event
// and the update see the same status: both land or neither does. The
// INSERT goes FIRST — it reads the status the UPDATE is about to change.
// Reversed, no transition would ever write its event.
const stillThere = and(
eq(REFERRALS.id, args.referral.id),
eq(REFERRALS.status, args.referral.status),
);
const [, updated] = await this.db.batch([
// INSERT ... SELECT, not VALUES, so the event is written only if the
// row matches. The projection must list the table's columns in
// definition order — drizzle checks the keys, not just the count —
// and every literal needs an alias to type as a subquery field.
this.db.insert(EVENTS).select((qb) =>
qb
.select({
id: sql`${generateId()}`.as("id"),
referralId: REFERRALS.id,
type: sql`${eventType}`.as("type"),
actorType: sql`${args.actorType}`.as("actor_type"),
actorId: sql`${args.actorId ?? null}`.as("actor_id"),
payload:
sql`${args.payload ? JSON.stringify(args.payload) : null}`.as(
"payload",
),
// Through the column's encoder, so the Date lands as the epoch
// seconds the column stores — a bare Date cannot be bound.
occurredAt: sql`${sql.param(now, EVENTS.occurredAt)}`.as(
"occurred_at",
),
})
.from(REFERRALS)
.where(stillThere),
),
this.db
.update(REFERRALS)
.set(patch)
.where(stillThere)
.returning({ id: REFERRALS.id }),
]);
if (updated.length === 0) {
throw new IllegalTransitionError(
args.referral.id,
args.referral.status as ReferralState,
args.to,
"moved on since read",
);
}
return { ...args.referral, ...patch } as Referral;
}
/** Append an event without a state change (a nudge, an operator note). */
async addEvent(args: {
referralId: string;
/**
* The enum, not a free string. The column stays `text()` because this
* table is append-only and an old value must still read back — this is
* the write-side guardrail, and it matters because `timeline.ts` DROPS
* unmapped types, so a typo here is a line that silently never appears
* on a partner's screen.
*/
type: ReferralEventType;
actorType: ReferralActor;
actorId?: string | null;
payload?: Record<string, unknown>;
now?: Date;
}): Promise<void> {
await this.db.insert(EVENTS).values({
id: generateId(),
referralId: args.referralId,
type: args.type,
actorType: args.actorType,
actorId: args.actorId ?? null,
payload: args.payload ? JSON.stringify(args.payload) : null,
occurredAt: args.now ?? new Date(),
});
}
/** FR-P-5.2: one nudge per 48h, max 2 lifetime. Counter lives on the row. */
async recordNudge(referralId: string, now = new Date()): Promise<void> {
await this.db
.update(REFERRALS)
.set({
nudgeCount: sql`${REFERRALS.nudgeCount} + 1`,
lastNudgeAt: now,
dateUpdated: now,
})
.where(eq(REFERRALS.id, referralId));
}
}
|