Contact Filter

Contact Filter

A reusable, structured filter ({ operator, conditions[] }) applied to contacts. Lives in two packages:

• Frontend feature — apps/builder/src/features/contact-filter/ (Zod schemas, UI config, React components). Barrel: index.ts.

• Backend query builder — packages/database/src/queries/contact-filter/ (@chatbotx.io/database/queries). Shared by the builder app and the worker so both resolve the same contacts. queries/contact-filter.ts is now a one-line re-export barrel (export * from "./contact-filter/index") — the real code is the 14-file directory beside it:

  Concern	File
  Per-field dispatch (buildConditionWhere)	contact-filter/index.ts:412
  Negation/NULL predicates, 24h window	contact-filter/predicates.ts (COLUMN_NEGATION_OPERATORS:21, contactInboxInteractedWithin24hSQL:27)
  Operator inversion map	contact-filter/field-value-predicates.ts (NEGATION_TO_POSITIVE:41)
  Relation EXISTS subqueries	contact-filter/relation-sets.ts (RELATION_SET_FILTERS:76, buildRelationSetWhere:131)
  Custom / bot fields, CTWA, timezone, continent	custom-field-predicates.ts, bot-field-predicates.ts, ctwa-retarget.ts, timezone.ts, continent.ts

Architecture

contactFilterFields enum (partials/contact.ts)     ← the field "universe" (~90)
        │
CONTACT_FILTER_FIELD_DEFINITIONS (schema/definitions.ts)  ← ~49 ACTIVE fields = single source of truth
        │  (each: { field, schemaKind, optionSource })
        ├──► Zod condition schemas  (schema/*.ts, via staticFieldFilter)
        └──► UI FieldConfig[]        (components/contact-filter-config.ts, getFieldConfigs)

Filter object → API `contactFilter` param → buildContactWhere / buildContactInboxContactFilterSQL
             → applyContactFilter → buildConditionWhere (switch per field) → Drizzle where / SQL

Filter shape (schema/index.ts):

contactFilterCriteriaSchema = { operator: "and" | "or", conditions: ContactFilterCondition[] }
// condition (static):  { field, operator, value? }
// condition (boolean): { field, operator: "eq", value: "true"|"false" }  |  { field, operator: "isEmpty" }
// condition (custom):  { field: "customField", customFieldId, valueType, operator, value? }

operator is top-level only — the schema is flat, no nested groups. Operators + form-field types: packages/database/src/partials/custom-field.ts (operatorTypes, FormFieldType).

Adding a new filter field

1. Enum — add the field key to contactFilterFields in packages/database/src/partials/contact.ts.
2. Definition — add one entry to CONTACT_FILTER_FIELD_DEFINITIONS (schema/definitions.ts) with schemaKind (boolean|text|multiSelect|select|datetime|number) and optionSource (none|languages|countries|continents|gender|contactSources|channels|inboxes|tags|flows). This one entry auto-generates both the Zod condition schema and the UI config.
3. Operator rules — TWO places (CRITICAL, must match):
   • Zod validation: STATIC_OPERATOR_RULES in schema/static-field-filter.ts
   • UI enablement: staticFieldRules in components/static-field-filter-config.ts
4. Backend SQL — add a case to buildConditionWhere (packages/database/src/queries/contact-filter/index.ts:412; it takes (condition, context)). Without it the field silently produces no condition (the default: return {} branch).
5. Options / group (if not none) — wire the option source in use-contact-filter-configs.ts / contact-filter-config.ts; group is assigned by getContactFilterFieldGroup.

Where filter application lives

contactRepository.buildListWhere (aliases buildContactListWhere, packages/database/src/repositories/contact/list-where.ts:42) is the one place applyContactFilter is called to build the where clause for a contacts list/count — used by both the builder (private RSC) and the public API, via contactService.list/count (packages/business/src/contact/list.ts). .query.ts files never call applyContactFilter directly — that duplicates the where-builder per caller, which is exactly what the service/repository split exists to prevent (see .agents/rules/data-access.md). The worker's export-contacts.ts still hand-builds its own where clause — a known follow-up, not a pattern to extend.

Backend query builder (packages/database/src/queries/contact-filter/)

• applyContactFilter(criteria) → maps conditions to { AND: [...] } or { OR: [...] }; buildConditionWhere(condition, context) switches on field.
• buildContactWhere({ workspaceId, keyword?, contactFilter? }) → relational where for contactModel.
• buildContactInboxContactFilterSQL({ contactIdColumn, workspaceId, contactFilter }) → contactId IN (SELECT id FROM Contact WHERE …) for ContactInbox-rooted queries.
• ContactFilterCriteriaInput.conditions is unknown[] on purpose — the DB package can't import the builder's Zod schema; each entry is Zod-validated at the request boundary, then narrowed here.

CRITICAL invariants

• Two operator-rule sources must stay in sync — STATIC_OPERATOR_RULES (Zod) and staticFieldRules (UI). Editing one without the other lets the UI offer an operator Zod rejects, or vice-versa.
• NULL / negation three-valued logic — negative and "is empty" operators must also match rows where the value is NULL/absent (SQL NOT (x = y) drops NULLs). Preserve these:
  • COLUMN_NEGATION_OPERATORS (ne, notIn, notContains) ⇒ { OR: [condition, { [col]: { isNull: true } }] }.
  • date ne ⇒ (col < dayStart OR col >= dayEnd OR col IS NULL).
  • relation isEmpty ⇒ NOT EXISTS; custom-field negation ⇒ NEGATION_TO_POSITIVE
    • negated EXISTS. (This is the fix behind commit "correct contact filter results for negative and empty conditions" — do not regress it.)
• Relation fields render as correlated RAW EXISTS — tags, source, currentChannel, inbox go through RELATION_SET_FILTERS + buildRelationSetWhere. relationsFilterToSQL does not understand nested relation filter fields, so relation conditions must be RAW EXISTS subqueries correlated on the contact id.
• No forced/default-condition injection — there is no mechanism to seed a hidden condition into a user's filter. Because the schema is flat (no nested groups), injecting a forced condition would force resolving AND-vs-OR against the user's own operator. Enforce cross-cutting audience constraints in the backend query instead, keyed off context — e.g. the broadcast 24h messaging window keys off broadcast.subaction in the worker (see below), never a filter param.

Hiding fields per context — excludeFields

ContactFilter / ContactFilterDialog / ContactListFilterPanel accept excludeFields?: ContactFilterField[]. It removes the field from the "add condition" list and prunes any existing condition referencing it (lib/prune-conditions.ts → pruneExcludedConditions, run in a useEffect).

Broadcast policy example — apps/builder/src/features/broadcasts/lib/broadcast-filter-fields.ts: getBroadcastExcludedFilterFields({ channel, subaction }) hides currentChannel (+inbox for template sends, +interactedInLast24h for the two non-template Messenger/WhatsApp subactions).

Shared 24h window predicate

contactInboxInteractedWithin24hSQL() (contact-filter/predicates.ts:27) is the single source for lastIncomingMessageAt >= NOW() - INTERVAL '24 hours', used by:

• the interactedInLast24h filter case (wrapped in a contact-level EXISTS), and
• the broadcast audience (apps/worker/src/schedule/handlers/prepare-broadcast.ts)
  • receiver-count preview (countContactInboxes), gated by requiresRecentInteractionWindow(subaction) (partials/broadcast.ts).

Consumers

• Contacts list — apps/builder/src/features/contacts/ (count via countContactInboxes, list via listContactInboxes).
• Conversations — conversations/conversation-filter.tsx (excludes currentChannel).
• Broadcast audience — broadcasts/create-broadcast-form.tsx (UI + count) → persisted broadcast.contactFilter → worker prepare-broadcast.ts.

Common mistakes

• Adding a field to CONTACT_FILTER_FIELD_DEFINITIONS but not implementing its buildConditionWhere case → filter silently no-ops.
• Updating operators in only one of the two rule sources.
• Writing a negative/empty operator that drops NULL rows (breaks three-valued logic).
• Trying to inject a forced/default condition into the flat schema — put the constraint in the query instead.
• Expecting nested (A OR B) AND C — not supported; operator is top-level only.

Checklist for a filter change

• Field key in contactFilterFields enum
• Entry in CONTACT_FILTER_FIELD_DEFINITIONS
• Operator rules updated in both STATIC_OPERATOR_RULES and staticFieldRules
• buildConditionWhere case implemented (with NULL/negation handling)
• Option source + group wired (if optionSource !== "none")
• Tests: apps/builder/__tests__/contact-filter-*.test.ts and packages/database/__tests__/contact-filter.test.ts
• pnpm lint + check-types for builder and @chatbotx.io/database