A WhatsApp campaign goes to a segment: "customers in Pune interested in the sale, or anyone in Mumbai". Behind that sentence is a small query compiler. It turns conditions a marketer picked from dropdowns into SQL that must never reach another tenant's contacts, never match everyone by accident, and treat 50%_off as text. Then there's keeping the membership current as contacts change. This post builds both in TypeScript against SQLite, with the cases that went wrong for us along the way.

The snippets are small, self-contained versions of the patterns, and each was run against the cases shown.

The shape: OR of ANDs

Segment builders almost always produce the same structure: boxes of conditions that must all match, and a contact in any box is in the segment. That's disjunctive normal form, and it stores well. Each condition row carries a group number; rows with the same number are ANDed, and the groups are ORed:

group field op value
0 city equals Pune
0 interest equals sale
1 city equals Mumbai

That's (city = Pune AND interest = sale) OR city = Mumbai, with no nested trees to store or edit. In WAPGROWAI it's a group_index column on the filter rows, added after segments already existed. Old segments had one group and a match-all/match-any switch, so a single group still respects that switch, and two or more groups ignore it.

The compiler 

// A segment is OR-of-ANDs: conditions sharing a `group` must all match, // and a contact matching any one group is in the segment. export type Op = "equals" | "contains" | "starts_with" | "ends_with" | "is_empty" | "is_not_empty"; export type Field = "name" | "phone" | "allow_campaign" | { attribute: string }; export type Condition = { field: Field; op: Op; value?: string; group?: number }; export type Compiled = { sql: string; params: (string | number)[] }; export class SegmentError extends Error {} const NEEDS_VALUE: Op[] = ["equals", "contains", "starts_with", "ends_with"]; // `%` and `_` in a customer's search are text, not wildcards. const escapeLike = (v: string) => v.replace(/[\\%_]/g, (c) => `\\${c}`); const pattern = (op: Op, v: string) => op === "contains" ? `%${escapeLike(v)}%` : op === "starts_with" ? `${escapeLike(v)}%` : `%${escapeLike(v)}`; const textClause = (column: string, op: Op, value: string): Compiled => { if (op === "equals") return { sql: `${column} = ?`, params: [value] }; if (op === "is_empty") return { sql: `(${column} IS NULL OR ${column} = '')`, params: [] }; if (op === "is_not_empty") return { sql: `(${column} IS NOT NULL AND ${column} <> '')`, params: [] }; return { sql: `${column} LIKE ? ESCAPE '\\'`, params: [pattern(op, value)] }; }; // Compare digits only, against both the full and the national number, // so "+91 98200 11223", "919820011223" and "9820011223" all match. const digits = (expr: string) => ["+", " ", "-", "(", ")"].reduce((e, ch) => `REPLACE(${e}, '${ch}', '')`, expr); const conditionClause = (c: Condition): Compiled => { const value = (c.value ?? "").trim(); if (NEEDS_VALUE.includes(c.op) && value === "") throw new SegmentError(`"${c.op}" needs a value`); if (typeof c.field === "object") { // Uncorrelated subquery: a join would repeat a contact once per matching attribute. const inner = textClause("value", c.op, value); return { sql: `id IN (SELECT contact_id FROM contact_attributes WHERE key = ? AND ${inner.sql})`, params: [c.field.attribute, ...inner.params], }; } if (c.field === "allow_campaign") { if (c.op !== "equals" || !["yes", "no"].includes(value.toLowerCase())) { throw new SegmentError('allow_campaign only supports equals "yes" or "no"'); } return { sql: "allow_campaign = ?", params: [value.toLowerCase() === "yes" ? 1 : 0] }; } if (c.field === "phone") { if (c.op === "is_empty" || c.op === "is_not_empty") return textClause("national_number", c.op, ""); const d = value.replace(/\D/g, ""); const full = textClause(digits("country_code || national_number"), c.op, d); const national = textClause(digits("national_number"), c.op, d); return { sql: `(${full.sql} OR ${national.sql})`, params: [...full.params, ...national.params] }; } if (c.field === "name") return textClause("name", c.op, value); throw new SegmentError(`Unknown field ${JSON.stringify(c.field)}`); }; export const compileSegment = (accountId: string, conditions: Condition[]): Compiled => { const groups = new Map<number, Compiled[]>(); for (const c of conditions) { const clause = conditionClause(c); const g = c.group ?? 0; groups.set(g, [...(groups.get(g) ?? []), clause]); } // No conditions must mean nobody: "everyone" is the expensive mistake. if (groups.size === 0) return { sql: "SELECT id FROM contacts WHERE 1 = 0", params: [] }; const ors = [...groups.values()].map((and) => ({ sql: `(${and.map((c) => c.sql).join(" AND ")})`, params: and.flatMap((c) => c.params), })); return { // The tenant check sits outside the parentheses, so no OR can widen it. sql: `SELECT id FROM contacts WHERE account_id = ? AND (${ors.map((o) => o.sql).join(" OR ")})`, params: [accountId, ...ors.flatMap((o) => o.params)], }; };

Each rule in there came from a real failure mode.

The tenant check goes outside the parentheses

The query every multi-tenant app should be afraid of looks like this:

SELECT id FROM contacts WHERE account_id = ? AND city = 'Pune' OR city = 'Mumbai'

AND binds tighter than OR, so the second group matches every Mumbai contact in every account. Wrap all the groups in one pair of parentheses and put the tenant check outside it. The test seeds a contact in another account who matches the first group, and checks they never appear.

No conditions means nobody

A segment whose conditions are all missing or half-filled has no filter left. If the compiler returns "all contacts in this account" for that, the segment silently becomes "message everyone", and a campaign to it costs real money. Ours did exactly this until we changed it: an empty segment now compiles to a query that matches nothing.

Refuse what you can't compile; don't drop it

A row like "name contains (blank)" or "opted in contains y" can't be turned into SQL. The tempting fix is to skip it. But dropping a condition always widens the result: skip one AND and the segment grows. For a campaign audience that's the dangerous direction, so the compiler throws, and the API returns a 400 the user can act on.

Values are parameters, and LIKE patterns are escaped

Every value goes in as a ? parameter. That stops SQL injection, but not LIKE wildcards: a search for 50%_off would match "50 people off" too. Escape \, % and _ before building the pattern, and say which escape character you used.

Phone numbers: compare digits

People type +91 98200 11223, 919820011223 and 9820011223, and the number is often stored with its formatting, split into a country code and a national number. Strip punctuation in SQL and compare against both the full and the national digits, so all three match. "Starts with 971" then finds UAE numbers.

Attributes: an uncorrelated subquery

Custom attributes live in their own table. A join repeats the contact once per matching attribute row, which breaks LIMIT/OFFSET on any paginated list that reuses the same filter. A correlated EXISTS works but has to name the outer table, and ORMs alias that table differently depending on what else the query includes. id IN (SELECT contact_id FROM contact_attributes WHERE …) avoids both, and the outer account_id check still scopes it.

One condition builder for every screen

The contacts list, the contacts export and the segment engine all filter the same table. Ours had three copies of this logic, and they drifted: a phone filter matched in a segment but not in the list, and % was a wildcard in one and not the other. Now there's one function that turns a condition row into a clause, and every caller uses it.

What we'd do differently: attributes need a key

The compiler above matches an attribute by key and value: city equals Mumbai. Ours doesn't. WAPGROWAI's attribute condition matches a value in any attribute, or an attribute's name. So "attribute equals Mumbai" also finds a contact whose notes say "asked about Mumbai store". The test covers that case: with the key, Meera, whose city is Pune, isn't in the Mumbai segment.

Matching any attribute felt simpler in the UI, with one dropdown fewer. It also makes segments wider than people expect, and it can't express "city is empty". Pick the key in the condition from the start.

Membership: computed once, read many times

You can evaluate a segment at send time, or store its members in a segment_contacts table and keep that up to date. Storing wins once segments show counts in lists, campaigns read them, and the same segment is used many times. But it means every change to a contact has to update the table.

import type { DatabaseSync } from "node:sqlite"; import { compileSegment, type Condition } from "./compile.ts"; export type Segment = { id: string; accountId: string; conditions: Condition[]; isStatic: boolean }; // Full recompute for one segment: add who now matches, remove who no longer does. export const syncSegment = (db: DatabaseSync, segment: Segment) => { if (segment.isStatic) return { added: 0, removed: 0 }; const { sql, params } = compileSegment(segment.accountId, segment.conditions); const desired = new Set((db.prepare(sql).all(...params) as { id: string }[]).map((r) => r.id)); const current = new Set( (db.prepare("SELECT contact_id FROM segment_contacts WHERE segment_id = ?").all(segment.id) as { contact_id: string }[]).map( (r) => r.contact_id, ), ); const remove = db.prepare("DELETE FROM segment_contacts WHERE segment_id = ? AND contact_id = ?"); // OR IGNORE: a per-contact sync running at the same time may have inserted it first. const add = db.prepare("INSERT OR IGNORE INTO segment_contacts (segment_id, contact_id) VALUES (?, ?)"); let added = 0; let removed = 0; for (const id of current) if (!desired.has(id)) removed += Number(remove.run(segment.id, id).changes); for (const id of desired) if (!current.has(id)) added += Number(add.run(segment.id, id).changes); return { added, removed }; }; // Cheap path for "this one contact changed": check it against each segment. export const syncContact = (db: DatabaseSync, segments: Segment[], contactId: string) => { for (const segment of segments) { if (segment.isStatic || segment.conditions.length === 0) continue; const { sql, params } = compileSegment(segment.accountId, segment.conditions); const matches = db.prepare(`${sql} AND id = ?`).get(...params, contactId) !== undefined; if (matches) db.prepare("INSERT OR IGNORE INTO segment_contacts (segment_id, contact_id) VALUES (?, ?)").run(segment.id, contactId); else db.prepare("DELETE FROM segment_contacts WHERE segment_id = ? AND contact_id = ?").run(segment.id, contactId); } };
  • A full sync diffs the stored members against the current matches. It's the right tool when the segment's conditions change.
  • A per-contact sync checks one contact against each segment. It's the right tool for hot paths: a new contact from an inbound WhatsApp message shouldn't recompute every segment for every contact.
  • Static segments are never synced. "Everyone at Saturday's webinar" is a snapshot, and re-running its conditions would change it.

Every write path must sync

This is the part that's easy to get wrong. Membership is only as fresh as the code paths that call the sync. In WAPGROWAI, editing or importing contacts, the public API and new inbound contacts all sync. A chatbot node that saves an answer to a contact attribute doesn't, so a contact whose city a chatbot saved won't join "Pune" until something else triggers a sync. Campaigns read the stored membership, so that contact misses the campaign. List your write paths and test each one, or evaluate the conditions again when a campaign builds its recipient list.

Full re-syncs on every contact edit also get expensive: segments × contacts per edit. Use the per-contact path wherever you know which contact changed.

Don't soft-delete membership rows

We once made segment_contacts soft-deleting, with a unique index on (segment_id, contact_id). A soft-deleted row is hidden from the ORM but not from the index, so a contact who left a segment could never rejoin: the "is it already there?" check saw nothing, and the insert failed with a duplicate key. Membership is derived data; delete it for real. The test moves a contact out of "Pune" and back in.

Inserts must tolerate races

A full sync and a per-contact sync can run at the same time: an inbound message arrives while someone edits the segment. Both decide the contact should be added, and one insert hits the unique index. INSERT OR IGNORE (MySQL: INSERT IGNORE or bulkCreate with ignoreDuplicates) makes that harmless.

What the tests check

  • DNF: (Pune AND sale) OR Mumbai returns Asha and Ravi.
  • Tenancy: a contact in another account who matches the first group never appears.
  • Attribute keys: Meera's notes mention Mumbai, but she isn't in the Mumbai segment.
  • Empty segment: no conditions returns nobody.
  • Refusal: a blank value, or contains on the opt-in flag, throws instead of being dropped.
  • LIKE: 50%_ matches only the contact literally named "50%_off club".
  • Phone: three spellings of one number all match, and "starts with 971" finds the UAE contact.
  • Injection: a value like x' OR 1=1 -- matches nothing and never appears in the SQL.
  • Sync: a full sync adds two members and is a no-op when repeated; a contact can leave and rejoin; a static segment never changes.

Checklist

  • Store segments as OR-of-AND groups with a group number per condition.
  • Put the tenant check outside the parentheses.
  • Compile an empty segment to "nobody".
  • Refuse conditions you can't compile; never drop them.
  • Parameterize values and escape LIKE wildcards.
  • Normalize phone numbers to digits on both sides.
  • Match attributes by key and value, with an uncorrelated subquery.
  • Use one condition builder for lists, exports and segments.
  • Sync membership from every write path, and use the per-contact path for hot ones.
  • Hard-delete membership rows, and make inserts ignore duplicates.

WAPGROWAI is built by DI Solutions, a custom software and AI development company. This series covers what we've learned building WAPGROWAI on the WhatsApp Cloud API. Related: phone numbers will break your WhatsApp integration and designing a public API for a WhatsApp platform.