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 | 11x 11x 11x 11x 809x 809x 767x 1864x 466x 466x 516x 516x 24x 492x 492x 7x 485x 485x 19x 466x 466x 466x 466x 19x 447x 447x 4x 443x 443x 3x 440x | import {
isValidIndianMobile,
normalizeToE164,
} from "@interioring/utils/validation/phone";
import type { SQL } from "drizzle-orm";
import { inArray, or, sql } from "drizzle-orm";
import type { DrizzleD1Database } from "drizzle-orm/d1";
import type { AnySQLiteColumn } from "drizzle-orm/sqlite-core";
import { RrmProspectsDal } from "../../dal/rrm/prospects.dal";
import { RrmSuppressionDal } from "../../dal/rrm/suppression.dal";
import * as schema from "../../db/schema";
import type { RrmProspect } from "../../db/schema/rrm";
const PROS = schema.pros;
const RECIPIENTS = schema.proWhatsAppRecipients;
const INQUIRIES = schema.inquiries;
/**
* Why a phone number may not become a prospect. Machine-readable on purpose:
* the import writes one of these strings into `rrm_import_batches.rejects` and
* the admin UI maps it to copy, so a reworded human sentence must never be the
* thing a caller branches on.
*/
export const PROSPECT_REJECT_REASONS = [
"invalid_phone",
"suppressed",
"is_registered_pro",
"is_pro_whatsapp_recipient",
"is_homeowner_contact",
] as const;
export type ProspectRejectReason = (typeof PROSPECT_REJECT_REASONS)[number];
export type ResolveProspectContext = {
db: DrizzleD1Database<typeof schema>;
};
export type ResolveProspectResult =
/**
* The number is already a prospect. NOTE: this says nothing about whether we
* may message them — the row can carry `doNotContact = 1`. This resolver
* answers identity, not send-eligibility; the send gate is suppression plus
* the prospect's own DNC flag, checked by whoever is about to send.
*/
| { outcome: "existing"; prospect: RrmProspect }
/** Nothing owns this number. `phoneNorm` is digits-only, '91XXXXXXXXXX'. */
| { outcome: "create"; phoneNorm: string }
/**
* `detail` is a short operator-facing reference for the reject report: the
* stored suppression reason, or the id of the record that already owns the
* number. Never the number itself — it is already on the rejected row.
*/
| { outcome: "reject"; reason: ProspectRejectReason; detail?: string };
/**
* The RRM phone identity: E.164 minus the leading '+'.
*
* `rrm_prospects.phone_norm` and `rrm_suppression.phone_norm` are digits only
* ('91XXXXXXXXXX') while every other table in the codebase stores E.164 with
* the '+'. Exported so the import and submit paths share one definition of that
* difference instead of each writing its own `.slice(1)`.
*
* Returns null for anything that is not a valid Indian MOBILE. A landline
* normalises fine and cannot receive WhatsApp, so it is not a prospect.
*/
export function toProspectPhoneNorm(
rawPhone: string | null | undefined,
): string | null {
const e164 = normalizeToE164(rawPhone);
if (!e164 || !isValidIndianMobile(e164)) return null;
return e164.slice(1);
}
/**
* Strips the separators humans type into a phone field, SQL-side.
*
* `pros.whatsapp` and `inquiries.customer_phone` are free-text profile/form
* fields that were never normalised on write, so "+91 98765 43210" and
* "9876543210" are both really in there. Comparing the raw column against one
* canonical string would miss most of the rows this resolver exists to catch.
*/
function digitsOnly(column: AnySQLiteColumn): SQL<string | null> {
return sql`replace(replace(replace(replace(replace(replace(${column}, ' ', ''), '-', ''), '(', ''), ')', ''), '.', ''), '+', '')`;
}
/**
* Every digit-string a stripped column could hold for this one number.
*
* An exact set rather than a `LIKE '%9876543210'` suffix match: the suffix form
* also matches a foreign number that happens to end in the same ten digits,
* which would reject a real prospect with no way for the operator to see why.
*/
function phoneMatchCandidates(phoneNorm: string): string[] {
// `parseIndianPhone` rejects every country but IN, so phoneNorm is always
// '91' + a ten-digit national number.
const national = phoneNorm.slice(2);
return [phoneNorm, national, `0${national}`, `00${phoneNorm}`];
}
/**
* The single front door for "may this phone number become an RRM prospect?".
*
* Spec §5.3 gives the CSV import four dedupe checks; §10.5's organic path — the
* landing-page form — creates a prospect and applies none of them, so the exact
* person the import refuses could walk in through the form. Both paths call
* this instead, which is the only way the two stay in agreement.
*
* **The two callers differ only in what they do with the outcome:**
* - The **import** records `reason` against the CSV row and reports it in the
* batch's `rejects[]` — §5.3's "report the reason per row; never silently
* drop".
* - The **form** records the submission for operator review rather than
* dropping it. A rejected form submission is a person who typed their number
* and pressed send; they may be a pro's colleague, or someone who opted out
* and changed their mind, and a human should see it. Discarding it silently
* would lose a real inbound signal to a dedupe rule.
*
* Check order is deliberate. Normalisation has to come first (a number we
* cannot parse cannot be looked up), then suppression, before anything else:
* a number that told us to stop must never resurface as a "new" prospect
* through any door, and it is also the cheapest check — one primary-key read.
*/
export async function resolveOrRejectProspectByPhone(
rawPhone: string | null | undefined,
ctx: ResolveProspectContext,
): Promise<ResolveProspectResult> {
const phoneNorm = toProspectPhoneNorm(rawPhone);
if (!phoneNorm) {
return { outcome: "reject", reason: "invalid_phone" };
}
const suppression = await new RrmSuppressionDal(ctx.db).find(phoneNorm);
if (suppression) {
// `find` rather than `isSuppressed` for the same one-row read: the stored
// reason is what makes the reject line actionable at triage.
return {
outcome: "reject",
reason: "suppressed",
detail: suppression.reason,
};
}
const existing = await new RrmProspectsDal(ctx.db).findByPhone(phoneNorm);
if (existing) {
return { outcome: "existing", prospect: existing };
}
// Only reached when the row is genuinely new, which is the common case for a
// whole import file — so the three "is this already one of ours?" lookups run
// concurrently rather than paying three round trips in sequence.
const candidates = phoneMatchCandidates(phoneNorm);
const [proRows, recipientRows, inquiryRows] = await Promise.all([
ctx.db
.select({ id: PROS.id })
.from(PROS)
.where(
or(
inArray(digitsOnly(PROS.whatsapp), candidates),
inArray(digitsOnly(PROS.whatsappBusinessNumber), candidates),
),
)
.limit(1),
ctx.db
.select({ proId: RECIPIENTS.proId })
.from(RECIPIENTS)
.where(inArray(digitsOnly(RECIPIENTS.number), candidates))
.limit(1),
ctx.db
.select({ id: INQUIRIES.id })
.from(INQUIRIES)
.where(inArray(digitsOnly(INQUIRIES.customerPhone), candidates))
.limit(1),
]);
// Fixed priority, not "whichever query answered first" — a number in two
// tables has to reject with the same reason on every run or the reject report
// is not reproducible.
const pro = proRows[0];
if (pro) {
return { outcome: "reject", reason: "is_registered_pro", detail: pro.id };
}
const recipient = recipientRows[0];
if (recipient) {
return {
outcome: "reject",
reason: "is_pro_whatsapp_recipient",
detail: recipient.proId,
};
}
const inquiry = inquiryRows[0];
if (inquiry) {
return {
outcome: "reject",
reason: "is_homeowner_contact",
detail: String(inquiry.id),
};
}
return { outcome: "create", phoneNorm };
}
|