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 | 9x 26x 26x 26x 26x 26x 26x 26x 3x 3x 23x 26x 26x 13x 13x 13x 13x 13x 26x 28x 25x 25x 34x 34x 43x 43x 43x 43x 25x 25x 11x 11x 8x 8x 8x 11x 1x 11x 11x 11x 11x 11x 36x 36x 1x 26x 26x 26x 3x 3x 2x 2x 4x 4x | // The lead store (#1085) — see db/schema/landing-leads.ts. Every entry point
// writes through `upsert`; the admin Web leads page reads through `list`.
import {
and,
asc,
desc,
eq,
gte,
like,
lte,
notInArray,
or,
type SQL,
sql,
} from "drizzle-orm";
import type { DrizzleD1Database } from "drizzle-orm/d1";
import * as schema from "../db/schema";
import type { LEAD_ENTRY_TYPES, LEAD_STATUSES } from "../db/schema/enums";
import type {
LandingLead,
LeadTouch,
NewLandingLead,
} from "../db/schema/landing-leads";
import {
consentApplies,
LEAD_NAME_PLACEHOLDER,
type LeadInput,
parseLeadInput,
} from "../lib/lead-input";
export type LeadStatus = (typeof LEAD_STATUSES)[number];
export interface LeadListFilters {
status?: LeadStatus;
entryType?: (typeof LEAD_ENTRY_TYPES)[number];
utmSource?: string;
from?: Date;
to?: Date;
/** Name or phone, substring match. */
q?: string;
qualified?: "yes" | "no" | "call_to_qualify";
/** Hide `closed` and `junk`. */
open?: boolean;
}
const t = schema.landingLeads;
function filterWhere(f: LeadListFilters): SQL | undefined {
const conditions: SQL[] = [];
if (f.status) conditions.push(eq(t.status, f.status));
if (f.entryType) conditions.push(eq(t.entryType, f.entryType));
if (f.utmSource) conditions.push(eq(t.utmSource, f.utmSource.toLowerCase()));
if (f.from) conditions.push(gte(t.dateCreated, f.from));
if (f.to) conditions.push(lte(t.dateCreated, f.to));
if (f.qualified === "yes") {
// Qualified by the WhatsApp assistant, or marked qualified by an admin
// on Web leads: either way it belongs on the call list.
const match = or(eq(t.qualified, "yes"), eq(t.status, "qualified"));
Eif (match) conditions.push(match);
I} else if (f.qualified) {
conditions.push(eq(t.qualified, f.qualified));
}
if (f.open) conditions.push(notInArray(t.status, ["closed", "junk"]));
if (f.q) {
// Escape LIKE's own wildcards so a search for "_" or "%" is literal.
const escapeLike = (v: string) => v.replace(/[\\%_]/g, (c) => `\\${c}`);
const byName = sql`${t.name} LIKE ${`%${escapeLike(f.q)}%`} ESCAPE '\\'`;
// Phones are stored E.164 (`+919876543210`); a pasted "98765 43210",
// "+91 98765-43210" or "098765 43210" should still find it, so match on
// the digits, minus the leading 0 trunk prefix Indian numbers are often
// written with.
const digits = f.q.replace(/\D/g, "").replace(/^0+/, "");
const match =
digits.length >= 3 ? or(byName, like(t.phone, `%${digits}%`)) : byName;
Eif (match) conditions.push(match);
}
return conditions.length ? and(...conditions) : undefined;
}
export class LandingLeadsDal {
constructor(private db: DrizzleD1Database<typeof schema>) {}
async create(data: NewLandingLead): Promise<LandingLead> {
const result = await this.db
.insert(schema.landingLeads)
.values(data)
.returning();
return result[0];
}
/** The oldest lead with this phone: the table predates dedup (see schema). */
async findByPhone(phone: string): Promise<LandingLead | undefined> {
const rows = await this.db
.select()
.from(t)
.where(eq(t.phone, phone))
.orderBy(asc(t.id))
.limit(1);
return rows[0];
}
/**
* The one writer. First contact from a phone creates the lead; every
* contact, the first included, adds a touch. First-touch attribution is
* never overwritten. Email-only contacts can't be deduped and always create.
*
* `created` is what gates the support email: one per person, not per form.
*/
// ponytail: find-then-insert can race on two simultaneous first contacts
// from one phone; add a unique phone key once prod duplicates are merged.
async upsert(
raw: LeadInput,
): Promise<{ lead: LandingLead; created: boolean }> {
const input = parseLeadInput(raw);
const now = new Date();
const existing = input.phone
? await this.findByPhone(input.phone)
: undefined;
let lead: LandingLead;
if (!existing) {
const consent = input.consentState
? {
consentState: input.consentState,
consentVersion: input.consentVersion,
consentAt: now,
}
: {};
lead = await this.create({
name: input.name,
phone: input.phone,
email: input.email,
locality: input.locality,
notes: input.notes,
source: input.source,
pagePath: input.pagePath,
entryType: input.entryType,
offer: input.offer,
utmSource: input.utmSource,
utmMedium: input.utmMedium,
utmCampaign: input.utmCampaign,
utmContent: input.utmContent,
utmTerm: input.utmTerm,
gclid: input.gclid,
fbclid: input.fbclid,
intLink: input.intLink,
sourceId: input.sourceId,
sourceType: input.sourceType,
ctwaClid: input.ctwaClid,
adHeadline: input.adHeadline,
priceTestArm: input.priceTestArm,
city: input.city,
bhk: input.bhk,
possessionMonth: input.possessionMonth,
...consent,
firstTouchAt: now,
lastUpdated: now,
});
} else {
const patch: Partial<NewLandingLead> = { lastUpdated: now };
if (consentApplies(existing.consentState, input.consentState)) {
patch.consentState = input.consentState;
patch.consentVersion = input.consentVersion ?? null;
patch.consentAt = now;
}
if (
existing.name === LEAD_NAME_PLACEHOLDER &&
input.name !== LEAD_NAME_PLACEHOLDER
) {
patch.name = input.name;
}
// Fill blanks only — what we already know is never overwritten. Not
// contact details: a form can't prove whose phone it is, so a stranger
// must not be able to attach their email to someone else's lead.
Iif (!existing.locality && input.locality) patch.locality = input.locality;
if (!existing.bhk && input.bhk) patch.bhk = input.bhk;
Iif (!existing.possessionMonth && input.possessionMonth)
patch.possessionMonth = input.possessionMonth;
const rows = await this.db
.update(t)
.set(patch)
.where(eq(t.id, existing.id))
.returning();
lead = rows[0];
}
await this.db.insert(schema.leadTouches).values({
leadId: lead.id,
at: now,
entryType: input.entryType,
channel: input.source,
pagePath: input.pagePath,
utmSource: input.utmSource,
utmMedium: input.utmMedium,
utmCampaign: input.utmCampaign,
utmContent: input.utmContent,
utmTerm: input.utmTerm,
sourceId: input.sourceId,
ctwaClid: input.ctwaClid,
ref: input.ref,
notes: input.notes,
consentState: input.consentState,
consentVersion: input.consentVersion,
});
return { lead, created: !existing };
}
/** Support has now been emailed about this person (see the column). */
async markSupportNotified(id: number): Promise<void> {
await this.db
.update(t)
.set({ supportNotifiedAt: new Date() })
.where(eq(t.id, id));
}
// Admin list, newest first.
async list(
filters: LeadListFilters & { limit: number; offset: number },
): Promise<{ items: LandingLead[]; total: number }> {
const where = filterWhere(filters);
const [items, countResult] = await Promise.all([
this.db
.select()
.from(t)
.where(where)
.orderBy(desc(t.dateCreated), desc(t.id))
.limit(filters.limit)
.offset(filters.offset),
this.db.select({ count: sql<number>`count(*)` }).from(t).where(where),
]);
return { items, total: Number(countResult[0]?.count ?? 0) };
}
async getWithTouches(
id: number,
): Promise<(LandingLead & { touches: LeadTouch[] }) | null> {
const [lead] = await this.db.select().from(t).where(eq(t.id, id));
if (!lead) return null;
const touches = await this.db
.select()
.from(schema.leadTouches)
.where(eq(schema.leadTouches.leadId, id))
.orderBy(desc(schema.leadTouches.at), desc(schema.leadTouches.id));
return { ...lead, touches };
}
// Admin edit. Returns null when the id doesn't exist so the route can 404.
async update(
id: number,
patch: { status?: LeadStatus; adminNotes?: string | null },
): Promise<LandingLead | null> {
const rows = await this.db
.update(t)
.set({ ...patch, lastUpdated: new Date() })
.where(eq(t.id, id))
.returning();
return rows[0] ?? null;
}
}
|