# Contact Filter

> Work with the ChatbotX contact filter system — the shared filter model behind the contacts list, conversations, and broadcast audiences. Use when adding a filter field or operator, changing the filter UI, editing the SQL query builder, or enforcing an audience constraint. Covers the definitions single-source, the two operator-rule sources that must stay in sync, NULL / negation three-valued logic, relation EXISTS subqueries, and the excludeFields mechanism.

- Skill: `chatbotxio/contact-filter` (Agent Skill)
- Install (CLI): `npx skillmds@latest add chatbotxio/contact-filter`
- Raw SKILL.md: https://api.skillmd.com/api/skills/chatbotxio/contact-filter/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Data & Analytics
- Author: chatbotxio (https://skillmd.com/u/chatbotxio)
- Updated: 2026-09-17
- Page: https://skillmd.com/skills/chatbotxio/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`):

```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`