feat(convex/customers): add normalizedEmail field + index for broadcast dedup by koala73 · Pull Request #3424 · koala73/worldmonitor

koala73 · 2026-04-26T07:24:28Z

Why

Pre-work for the PRO-launch broadcast to ~tens-of-thousands of waitlist users.

The audience-build dedup query is registrations − emailSuppressions − paying customers (from the customers join, since subscriptions is keyed on userId, not email). On main today:

registrations.normalizedEmail is lowercased + trimmed, indexed
emailSuppressions.normalizedEmail is lowercased + trimmed, indexed
customers.email is raw (whatever Dodo sent us — case-preserved), not indexed

Result: the dedup join either does a full customers scan on every audience build, or — if we naively join on email == normalizedEmail — silently drops paid users whose Dodo email had any case/whitespace divergence from their waitlist signup. Failure mode: paid users receive a "buy PRO!" launch email. That's the worst-case outcome of the whole broadcast effort.

What

Mirrors the convention registrations and emailSuppressions already use.

convex/schema.ts — adds customers.normalizedEmail: v.optional(v.string()) + by_normalized_email index. Optional so existing rows pass schema validation; comment explains the broadcast-dedup motivation and points at the backfill command.
convex/payments/subscriptionHelpers.ts — handleSubscriptionActive now derives normalizedEmail once and writes it at both upsert paths (existing-customer patch + new-customer insert).
convex/payments/backfillCustomerNormalizedEmail.ts (new) — idempotent paginated internalMutation (default batchSize: 500) + countPending diagnostic query. Skips rows that already have a non-empty value; handles empty-email rows by writing empty string. Pattern mirrors seedProductPlans.ts.

Verification

npx tsc --noEmit -p convex/tsconfig.json → clean
Field is v.optional → no validation break for existing rows on deploy
Both write sites compute normalizedEmail from the same email.trim().toLowerCase() derivation as the existing convention sites (registerInterest.ts:60, emailSuppressions.ts:11)

Operational sequence after merge

Convex deploys schema automatically via the Vercel hook. Then:

npx convex run payments/backfillCustomerNormalizedEmail:countPending
# expect: { total: N, pending: ~N (most existing rows lack the field), withEmail: M }

npx convex run payments/backfillCustomerNormalizedEmail:backfill
# rerun until response shows done:true (paginated at 500/call)

npx convex run payments/backfillCustomerNormalizedEmail:countPending
# expect: { pending: 0 }

Out of scope

The actual broadcast pipeline (Convex action that builds the audience, upserts to Resend Contacts via API, and triggers the Broadcast). That's the next PR — this one just unblocks the dedup join.
Migrating normalizedEmail from v.optional to required. Separate cleanup PR after backfill confirms 100% coverage.

…st dedup Required pre-work for the PRO-launch broadcast. Without this, the audience-build dedup query (registrations − emailSuppressions − paying customers) is a full `customers` table scan, AND the join would silently fail across case/whitespace differences between `registrations.normalizedEmail` (lowercased+trimmed) and `customers.email` (raw) — leaking paid users into the "buy PRO!" send. Mirrors the existing convention from `registrations` and `emailSuppressions`, both of which already store + index `normalizedEmail`. - schema.ts: customers.normalizedEmail (v.optional, backward-compatible) + by_normalized_email index - subscriptionHelpers.ts: populate normalizedEmail at both write sites in handleSubscriptionActive (insert + patch); single derivation reused - backfillCustomerNormalizedEmail.ts (new): idempotent paginated internalMutation + countPending diagnostic query for existing rows Operational sequence after deploy: npx convex run payments/backfillCustomerNormalizedEmail:countPending npx convex run payments/backfillCustomerNormalizedEmail:backfill # rerun backfill until done:true; verify with countPending → pending:0

vercel · 2026-04-26T07:24:33Z

The latest updates on your projects. Learn more about Vercel for GitHub.

Project	Deployment	Actions	Updated (UTC)
worldmonitor	Ready	Preview, Comment	Apr 26, 2026 7:44am

greptile-apps · 2026-04-26T07:26:27Z

Greptile Summary

This PR adds a normalizedEmail field and index to the customers table, mirroring the convention already used by registrations and emailSuppressions, so the broadcast dedup join can be done in O(1) instead of a full scan. The schema and subscriptionHelpers changes are correct, but the backfill mutation has two defects that need fixing before it's run against production data:

Full table scan on every invocation (backfill calls .collect() before applying batchSize). Convex's 16,384-document read limit will cause the mutation to throw once the customers table grows past that threshold, making the backfill unrunnable.
Empty-email rows bypass batchSize — they are patched but not counted against patched, so a batch with many blank-email customers can write far beyond the intended limit in a single call.

Confidence Score: 3/5

The schema and live write-path changes are safe, but the backfill script has two P1 defects that would make it unreliable or fail outright on production data — fix before running.

Two P1 findings in the backfill mutation (full-scan ignoring Convex's document-read limit, and empty-email rows bypassing batchSize) pull the score below the P1 ceiling of 4. The production write path (subscriptionHelpers) and schema are clean, which keeps it above 2.

convex/payments/backfillCustomerNormalizedEmail.ts — both the pagination strategy and the empty-email branch need fixes before the backfill is run.

Important Files Changed

Filename	Overview
convex/payments/backfillCustomerNormalizedEmail.ts	New one-time backfill mutation — has two P1 issues: full `.collect()` scan ignores Convex's 16k document-read limit (defeating batchSize pagination), and empty-email rows bypass the batch limit entirely. Also exposes `countPending` as a public query instead of `internalQuery`.
convex/payments/subscriptionHelpers.ts	Derives `normalizedEmail` via `email.trim().toLowerCase()` and writes it at both upsert paths (patch + insert) — clean, consistent with existing convention.
convex/schema.ts	Adds `normalizedEmail: v.optional(v.string())` and `by_normalized_email` index to the `customers` table — correctly mirrors the pattern used by `registrations` and `emailSuppressions`.

Flowchart

%%{init: {'theme': 'neutral'}}%%
flowchart TD
    A[backfill called] --> B["ctx.db.query('customers').collect()\n⚠️ loads ALL rows"]
    B --> C{row.normalizedEmail\nnon-empty?}
    C -- yes --> D[alreadySet++ / continue]
    C -- no --> E["computed = email.trim().toLowerCase()"]
    E --> F{computed.length == 0?}
    F -- yes --> G["patch normalizedEmail = ''\nemptyEmail++\n⚠️ patched NOT incremented"]
    G --> C
    F -- no --> H["patch normalizedEmail = computed\npatched++"]
    H --> I{patched >= limit?}
    I -- yes --> J[break]
    I -- no --> C
    J --> K["remaining = all.length - scanned\ndone = remaining == 0"]
    K --> L[return stats]

    style B fill:#ffcccc
    style G fill:#ffcccc

_{Reviews (1): Last reviewed commit: "feat(convex/customers): add normalizedEm..." | Re-trigger Greptile}

greptile-apps · 2026-04-26T07:26:31Z

+  },
+  handler: async (ctx, { batchSize }) => {
+    const limit = batchSize ?? 500;
+    const all = await ctx.db.query("customers").collect();


Full table scan defeats batchSize pagination

.collect() fetches every customer row into memory before applying the batch limit. Convex enforces a 16,384-document read limit per mutation — once the customers table exceeds that, every call to backfill will throw, making the backfill unrunnable. The batchSize argument currently only caps writes, not reads.

Use a cursor-aware approach — e.g., filter to rows missing the field and call .take(limit) — so each invocation only reads what it needs to process:

const rows = await ctx.db .query("customers") .filter((q) => q.eq(q.field("normalizedEmail"), undefined)) .take(limit);

This also eliminates the need for the alreadySet counter and keeps reads proportional to batchSize.

greptile-apps · 2026-04-26T07:26:32Z

+      if (computed.length === 0) {
+        emptyEmail++;
+        await ctx.db.patch(row._id, { normalizedEmail: "" });
+        continue;
+      }


Empty-email rows bypass the batch limit

When computed.length === 0 the code patches the row and increments emptyEmail but does not increment patched, so the patched >= limit guard never fires for these rows. If there are many customers with blank emails, a single invocation will write far beyond the intended batchSize, potentially hitting Convex's per-mutation write budget or causing unexpectedly long mutation times.

await ctx.db.patch(row._id, { normalizedEmail: "" }); emptyEmail++; patched++; // count against the limit like any other row if (patched >= limit) break; continue;

greptile-apps · 2026-04-26T07:26:33Z

+ * Diagnostic: how many customer rows still need backfilling?
+ * Returns an exact count (full scan — only run from CLI, not hot paths).
+ */
+export const countPending = query({


countPending should be internalQuery

The comment explicitly says "only run from CLI, not hot paths", but exporting it as a public query (not internalQuery) means any authenticated client can call it and trigger a full table scan. Using internalQuery matches the intent and prevents accidental exposure.

import { internalMutation, internalQuery } from "../generated/server"; // ... export const countPending = internalQuery({

… P2) Three findings on the backfill mutation, all valid: 1. (P1) `.collect()` defeats batchSize pagination — read scope was the entire table, only writes were capped. Once `customers` exceeds Convex's 16,384-doc read limit the mutation throws and the backfill becomes unrunnable. Switched to `.filter(q => q.eq(q.field("normalizedEmail"), undefined)).take(limit)` so reads scale with `batchSize` and patched rows drop out of subsequent calls automatically. 2. (P1) Empty-email rows bypassed the batch limit — they hit the patch path without incrementing `patched`, so a customer set with many blank emails could drive a single invocation past the intended write budget. Now counted against `patched` like any other patched row (and tracked separately via `emptyEmail` for diagnostics). 3. (P2) `countPending` was exported as public `query` despite the JSDoc stating CLI-only. Switched to `internalQuery` so authenticated clients can't trigger a full table scan. Intent now matches export. Cleanup that fell out of (1): - Removed `alreadySet` counter (filter excludes already-patched rows by construction, so the counter is no longer meaningful). - Simplified return shape: `{ read, patched, emptyEmail, done }`. - `done = rows.length < limit` (we got fewer than we asked for ⇒ no more).

koala73 · 2026-04-26T07:41:22Z

Addressed all three Greptile findings in 4dfa2ae:

P1 #1 — .collect() defeats batchSize: Switched to a filter-then-take that only reads rows missing normalizedEmail. Reads now scale with batchSize and patched rows drop out of subsequent calls automatically — backfill drains in O(N/limit) calls and self-terminates regardless of table size.

P1 #2 — Empty-email rows bypassed the limit: Empty-email patches now count against patched like any other row. emptyEmail retained as a separate diagnostic counter.

P2 #3 — countPending exposed as public query: Switched to internalQuery. Comment intent now matches the export.

Cleanup that fell out of #1: removed the now-meaningless alreadySet counter and simplified the return shape to { read, patched, emptyEmail, done } where done = rows.length < limit.

…1 PR #3429 round 4) Greptile flagged that removing the confidence>=0.9 skip unconditionally opens genuine current keyword=critical events to silent demotion: the LLM cache hit feeds capLlmUpgrade which is Math.min, so a stale/wrong cache entry saying 'info' demotes critical -> info with no safeguard. The retrospective case PR #3424 wanted to handle is already handled UPSTREAM in classifyByKeyword via classSource='keyword-historical- downgrade' (confidence 0.85, level=info), which still flows through this function and is caught by the L3 marker check. Items reaching enrichWithAiCache at confidence 0.9 are by construction keyword=critical matches where the keyword classifier saw NO marker - trust the keyword verdict for those. The L3 marker check still runs BEFORE this skip, so the keyword=info (no-match, confidence 0.3) + marker case - the brief 2026-04-26-1302 'Science history: melts down...' shape - still gets forced to info.

…3) (#3429) * fix(classifier): two-layer historical-retrospective downgrade (L1 + L3) Brief 2026-04-26-1302 surfaced a 40-year Chernobyl anniversary article from Live Science: "Science history: Chernobyl nuclear power plant melts down, bringing the world to the brink of disaster — April 26, 1986 - Live Science" Investigation showed two distinct failure modes that cooperate to ship retrospective/anniversary content as if it were a current crisis: L1 — enrichWithAiCache skipped the LLM cache result for keyword=critical items (`if (0.9 <= item.confidence) continue;`). Original intent was "trust keyword when confident", but for retrospective titles that trip a CRITICAL keyword (e.g. "meltdown"), the LLM is the only thing that could disambiguate context — and the gate locked it out. Removed. The U4 +2-tier cap already protects against UPWARD over-promotion; symmetric DOWNWARD demotion is unconditionally safer than keeping a suspect keyword classification (capLlmUpgrade is a Math.min so downgrades pass freely). Cache application also now covers the new 'keyword-historical-downgrade' classSource so LLM can confirm/override the heuristic. L3 — Two-layer historical-marker downgrade: L3a (classifier-side): classifyByKeyword now downgrades CRITICAL/HIGH keyword matches to info when the title contains a retrospective marker. Markers (cheap regex): - Prefix: ^(Science history|On this day|Today in|This day in| Throwback|Flashback) - Phrase: \b(N years/decades/months ago|after|later|anniversary| in memoriam|remembering|commemorat|retrospective)\b - Full date: "April 26, 1986" or ISO 1986-04-26 Standalone 4-digit year is intentionally NOT a marker (current-event headlines like "Russia warns of 2026 strike" would falsely trigger). Downgrade applies only to CRITICAL/HIGH; LOW/MEDIUM are left alone (don't clear brief thresholds anyway, over-aggression cost outweighs signal). Distinct source tag 'keyword-historical-downgrade' for telemetry. L3b (LLM-cache-side defense-in-depth): The Chernobyl headline ABOVE doesn't match any CRITICAL keyword in the keyword classifier ("meltdown" substring isn't present in "melts down" — there's a space). So L3a wouldn't catch it. But the LLM cache had promoted this title (or one with the same hash) to CRITICAL at some prior point, and enrichWithAiCache applied that cache hit. The new L3b guard re-runs hasHistoricalMarker AFTER capLlmUpgrade — if the LLM-promoted level is CRITICAL/HIGH AND the title has a marker, force info. Logs on every fire so operators can audit. Tests: - tests/news-classifier-historical-downgrade.test.mts — 23 cases for hasHistoricalMarker predicate matrix + classifyByKeyword integration. Includes regression guards: current-year mentions don't trigger, "history" alone doesn't trigger (only the prefix forms), LOW/MEDIUM not downgraded, current-event critical headlines unchanged. - tests/news-classifier-llm-historical-guard.test.mts — 9 cases covering the LLM-cache guard predicate behavior + behavioral semantics doc (CRITICAL+marker → info; HIGH+marker → info; MEDIUM+marker → unchanged; CRITICAL-no-marker → unchanged). Includes the EXACT brief 2026-04-26-1302 title as a test fixture. Type tightening: - ClassificationResult.source: 'keyword' | 'keyword-historical-downgrade' - ParsedItem.classSource: 'keyword' | 'keyword-historical-downgrade' | 'llm' 106/106 tests pass across the touched + parity-coupled surface; importance-score-parity preserved. * fix(classifier): narrow historical markers (P2 PR #3429 round 2) Reviewer found two false-positive vectors that suppressed real critical alerts: - "Today in Ukraine: Russian missile strikes Kyiv" → was downgraded to info because of bare "Today in" prefix. This is a current-event headline pattern, not a historical one. - "Missile launch reported on April 26, 2026" → was downgraded because any full-date pattern matched, regardless of year. Both vectors would have suppressed real missile/attack/invasion alerts before they reached the brief. Fixes: 1. Prefix regex narrowed: REMOVED: bare "Today in" / "This day in" (too broad — legitimate current-event uses). KEPT: "Science history:", "Throwback", "Flashback" (always retrospective). ADDED: "On this day in YYYY" (year required — prevents bare "On this day, Iran fires missile" from triggering). ADDED: "This day in history" (specific phrasing — distinct from bare "This day in"). 2. Full-date matching now extracts the year and only treats dates as retrospective when year < currentYear - 1 (i.e. 2+ years old). In 2026: - 2024 and earlier dates → retrospective marker fires. - 2025 (last year) → NOT a marker (could be current context, e.g. "court ruling on April 15, 2025 takes effect"). - 2026 (current) → NOT a marker. - 2027+ (future) → NOT a marker (clock skew or scheduled events). Same logic applies to ISO date format. 3. hasHistoricalMarker(title, nowMs?) now takes optional nowMs for unit testability. Defaults to Date.now(); production callers omit. Tests: - 4 explicit reviewer-fix safety cases: * "Today in Ukraine: Russian missile strikes Kyiv" → high (preserved) * "Missile launch reported on April 26, 2026" → high (preserved) * "Today in tech: Apple unveils iPhone" → no marker * "On this day, Iran invasion begins" (no year) → no marker - 4 full-date boundary cases (1986, 2024, 2025, 2026, 2027) verify the year-based gating. - All test calls pass NOW = 2026-04-15 UTC to pin "current year" deterministically. 120/120 tests pass; importance-score-parity preserved. * fix(classifier): run historical-marker guard before capLlmUpgrade (P1 PR #3429 round 3) Previously the L3 defense-in-depth marker check ran on cappedLevel and only fired for critical/high. For keyword=info + hit=critical, capLlm- Upgrade demotes to medium (info+2=medium) — the post-cap check missed it and the brief 2026-04-26-1302 Chernobyl case shipped at MEDIUM, which still ships in 'all'-sensitivity briefs. Move the check to run on the RAW hit before the cap and force info unconditionally (not just critical/high). Retrospective markers now suppress the LLM verdict at every non-info level. * fix(classifier): restore confidence>=0.9 skip for keyword=critical (P1 PR #3429 round 4) Greptile flagged that removing the confidence>=0.9 skip unconditionally opens genuine current keyword=critical events to silent demotion: the LLM cache hit feeds capLlmUpgrade which is Math.min, so a stale/wrong cache entry saying 'info' demotes critical -> info with no safeguard. The retrospective case PR #3424 wanted to handle is already handled UPSTREAM in classifyByKeyword via classSource='keyword-historical- downgrade' (confidence 0.85, level=info), which still flows through this function and is caught by the L3 marker check. Items reaching enrichWithAiCache at confidence 0.9 are by construction keyword=critical matches where the keyword classifier saw NO marker - trust the keyword verdict for those. The L3 marker check still runs BEFORE this skip, so the keyword=info (no-match, confidence 0.3) + marker case - the brief 2026-04-26-1302 'Science history: melts down...' shape - still gets forced to info.

greptile-apps Bot reviewed Apr 26, 2026

View reviewed changes

vercel Bot deployed to Preview April 26, 2026 07:27 View deployment

vercel Bot deployed to Preview April 26, 2026 07:44 View deployment

koala73 merged commit e43fd0b into main Apr 26, 2026
10 checks passed

koala73 deleted the feat/customers-normalized-email-index branch April 26, 2026 08:44

koala73 mentioned this pull request Apr 26, 2026

feat(convex/broadcast): audience-export pipeline for PRO launch #3431

Merged

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

feat(convex/customers): add normalizedEmail field + index for broadcast dedup#3424

feat(convex/customers): add normalizedEmail field + index for broadcast dedup#3424
koala73 merged 2 commits into
mainfrom
feat/customers-normalized-email-index

koala73 commented Apr 26, 2026

Uh oh!

vercel Bot commented Apr 26, 2026 •

edited

Loading

Uh oh!

greptile-apps Bot commented Apr 26, 2026

Uh oh!

greptile-apps Bot Apr 26, 2026

Uh oh!

greptile-apps Bot Apr 26, 2026

Uh oh!

greptile-apps Bot Apr 26, 2026

Uh oh!

koala73 commented Apr 26, 2026

Uh oh!

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

1 participant

Conversation

koala73 commented Apr 26, 2026

Why

What

Verification

Operational sequence after merge

Out of scope

Uh oh!

vercel Bot commented Apr 26, 2026 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

greptile-apps Bot commented Apr 26, 2026

Greptile Summary

Confidence Score: 3/5

Important Files Changed

Flowchart

Uh oh!

greptile-apps Bot Apr 26, 2026

Choose a reason for hiding this comment

Uh oh!

greptile-apps Bot Apr 26, 2026

Choose a reason for hiding this comment

Uh oh!

greptile-apps Bot Apr 26, 2026

Choose a reason for hiding this comment

Uh oh!

koala73 commented Apr 26, 2026

Uh oh!

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

1 participant

vercel Bot commented Apr 26, 2026 •

edited

Loading