CRM Query Language (CQL)

CQL is a SQL-like query language for filtering, searching, sorting, and paginating CRM contacts and companies. It compiles to optimized PostgreSQL queries with parameterized values, type validation, access control, and cost estimation.

CQL queries can be expressed in three forms:

SQL-like string (recommended) — human-readable, ergonomic for dashboards and CLI
JSON structure — machine-friendly, ideal for programmatic use and API calls
Legacy DSL (field:op:value) — deprecated, kept for backward compatibility

All three compile to the same underlying execution plan.

Quick Start

status = ACTIVE

status = ACTIVE AND email contains "@example.com"

lifecycleStage in (LEAD, CUSTOMER) AND createdAt > 2024-01-01

Run any of these from the CLI:

transactional crm query -w "status = ACTIVE"
transactional crm query -w 'email contains "@example.com"'
transactional crm parse "status = ACTIVE AND lifecycleStage = LEAD"

Or via the API:

curl -X POST http://localhost:3001/crm/query \
  -H "Authorization: Bearer $CLI_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "entity": "contact",
    "filter": {
      "logic": "AND",
      "conditions": [
        { "type": "field", "field": "status", "operator": "eq", "value": "ACTIVE" }
      ]
    }
  }'

Query Structure

A CQL query has five parts, all optional except entity:

{
  "entity": "contact",
  "filter": { ... },
  "search": { ... },
  "sort": [ ... ],
  "pagination": { ... },
  "select": [ ... ]
}

Field	Description
`entity`	`"contact"`, `"company"`, or `["contact", "company"]` for cross-entity queries
`filter`	A filter group with AND/OR conditions
`search`	Full-text search across searchable fields
`sort`	Array of `{ field, direction }` — up to 3 levels
`pagination`	Offset or cursor pagination
`select`	Field names to return (omit for all fields)

SQL-like Syntax

The SQL-like form is the recommended way to write CQL filters. It looks and reads like a WHERE clause.

Comparison Operators

Operator	Meaning	Example
`=`	Equal	`status = ACTIVE`
`!=`, `<>`	Not equal	`status != ARCHIVED`
`>`	Greater than	`monthlySpend > 1000`
`<`	Less than	`createdAt < 2024-06-01`
`>=`	Greater or equal	`custom.mrr >= 500`
`<=`	Less or equal	`lastActivityAt <= 2024-12-31`

Text Operators

Operator	Example
`contains`	`email contains "@example.com"`
`not contains`	`name not contains "spam"`
`starts with`	`email starts with "admin"`
`ends with`	`domain ends with ".io"`
`like`	`name like "Acme"` (alias for `contains`)

Set Operators

Operator	Example
`in`	`lifecycleStage in (LEAD, CUSTOMER, OPPORTUNITY)`
`not in`	`country not in (US, CA)`

Existence Operators

Operator	Meaning
`is set`	Field has a non-null value
`is not set`	Field is null
`is null`	Alias for `is not set`
`is not null`	Alias for `is set`

email is set AND phone is not set

Range Operator

createdAt between 2024-01-01 and 2024-12-31
monthlySpend between 500 and 5000

Array Operators

For array fields (like activeModules):

Operator	Example
`has`	`activeModules has AUTH`
`has any`	`activeModules has any (AUTH, SUPPORT)`

Logical Operators

status = ACTIVE AND email contains "@example.com"
lifecycleStage = LEAD OR lifecycleStage = CUSTOMER
status = ACTIVE AND (lifecycleStage = LEAD OR lifecycleStage = CUSTOMER)

AND has higher precedence than OR. Use parentheses to override.

Values

Strings: double-quoted "value" or single-quoted 'value', or a bareword if it contains only letters, digits, and underscores
Numbers: 123, 45.67, -10
Booleans: true, false
Null: null
Dates: ISO format, e.g. 2024-01-15 or 2024-01-15T10:30:00Z

name = "Acme Corp"            -- quoted string
lifecycleStage = CUSTOMER     -- bareword (enum value)
isIdentified = true           -- boolean
mergedIntoId is null          -- null check
createdAt > 2024-01-01        -- date

Identifiers

Field names are plain identifiers. Custom properties use the custom. prefix:

email contains "@acme.io"
custom.plan_tier = "enterprise"
custom.mrr > 1000

JSON Syntax

For programmatic use, queries can be expressed as structured JSON. The filter object uses a recursive FilterGroup structure:

{
  "entity": "contact",
  "filter": {
    "logic": "AND",
    "conditions": [
      {
        "type": "field",
        "field": "status",
        "operator": "eq",
        "value": "ACTIVE"
      },
      {
        "type": "group",
        "group": {
          "logic": "OR",
          "conditions": [
            {
              "type": "field",
              "field": "lifecycleStage",
              "operator": "eq",
              "value": "LEAD"
            },
            {
              "type": "field",
              "field": "lifecycleStage",
              "operator": "eq",
              "value": "CUSTOMER"
            }
          ]
        }
      }
    ]
  },
  "sort": [
    { "field": "lastActivityAt", "direction": "desc" }
  ],
  "pagination": {
    "type": "offset",
    "page": 1,
    "limit": 50
  }
}

Field Condition

{
  "type": "field",
  "field": "email",
  "operator": "contains",
  "value": "@example.com"
}

Group Condition (nested)

{
  "type": "group",
  "group": {
    "logic": "OR",
    "conditions": [...]
  }
}

Operator Reference

Operator	JSON key	Compatible types
`=`	`eq`	text, number, boolean, date, enum
`!=`, `<>`	`neq`	text, number, boolean, date, enum
`contains`	`contains`	text
`not contains`	`not_contains`	text
`starts with`	`starts_with`	text
`ends with`	`ends_with`	text
`>`	`gt`	number, date
`<`	`lt`	number, date
`>=`	`gte`	number, date
`<=`	`lte`	number, date
`in`	`in`	text, number, enum
`not in`	`not_in`	text, number, enum
`is set`	`is_set`	all
`is not set`	`is_not_set`	all
`between`	`between`	number, date
`has`	`array_contains`	array
`has any`	`array_contains_any`	array

Entities and Fields

Contact Fields

Field	Type	Description
`email`	text	Contact email (indexed)
`phone`	text	Phone number
`name`	text	Full display name
`givenName`	text	First name
`familyName`	text	Last name
`status`	enum	`ACTIVE`, `ARCHIVED`, `MERGED`, `DELETED`
`lifecycleStage`	enum	`SUBSCRIBER`, `LEAD`, `MARKETING_QUALIFIED`, `SALES_QUALIFIED`, `OPPORTUNITY`, `CUSTOMER`, `EVANGELIST`, `OTHER`
`isIdentified`	boolean	Whether the contact has provided identifying info
`firstSeenModule`	text	Module that first created the contact
`activeModules`	array	Modules currently engaging the contact (`AUTH`, `SUPPORT`, `EMAIL`, etc.)
`createdAt`	date	When the contact was created
`updatedAt`	date	When the contact was last updated
`lastSeenAt`	date	Last activity timestamp
`lastActivityAt`	date	Last meaningful interaction (indexed)

Company Fields

Field	Type	Description
`name`	text	Company name (indexed)
`domain`	text	Primary domain (indexed)
`industry`	text	Industry classification
`size`	text	Employee count band
`plan`	text	Current plan/tier
`monthlySpend`	number	Monthly recurring revenue (admin only)
`website`	text	Company website URL
`phone`	text	Contact phone
`city`	text	City
`state`	text	State/region
`country`	text	Country code
`postalCode`	text	Postal/ZIP code
`status`	enum	`ACTIVE`, `ARCHIVED`
`createdAt`	date	When the company was created
`updatedAt`	date	When last updated

Custom Properties

Any property defined in your organization's CRM schema is queryable with the custom. prefix:

custom.plan_tier = "enterprise"
custom.contract_value > 10000
custom.renewal_date between 2024-10-01 and 2024-12-31
custom.tags has "priority"

Discover available custom properties:

transactional crm fields --entity contact

Full-Text Search

CQL supports PostgreSQL full-text search across text fields:

{
  "entity": "contact",
  "search": {
    "query": "acme corporation",
    "mode": "fulltext"
  }
}

Search Modes

Mode	Use case	Available on
`fulltext`	Natural language matching with stemming	PRO, ENTERPRISE
`prefix`	Typeahead / autocomplete (matches word prefixes)	FREE, PRO, ENTERPRISE
`fuzzy`	Similarity-based matching (typo tolerant)	ENTERPRISE

Searchable Fields

Contacts: name, email, phone, givenName, familyName Companies: name, domain, industry, website, notes

You can restrict search to specific fields:

{
  "search": {
    "query": "acme",
    "fields": ["name", "domain"],
    "mode": "prefix"
  }
}

Sorting

Sort by one or more fields:

{
  "sort": [
    { "field": "lastActivityAt", "direction": "desc" },
    { "field": "createdAt", "direction": "asc" }
  ]
}

Sorting on non-indexed fields emits a warning but still works. For best performance, sort on indexed fields: createdAt, updatedAt, lastActivityAt (contacts), name, domain, status.

Pagination

Offset Pagination (default)

{
  "pagination": {
    "type": "offset",
    "page": 1,
    "limit": 20
  }
}

page: 1-indexed
limit: max 100
Response includes total and totalPages

Cursor Pagination (PRO and ENTERPRISE)

For large datasets (> 10,000 rows), cursor pagination is more efficient:

{
  "pagination": {
    "type": "cursor",
    "cursor": "eyJzb3J0VmFsdWVzIjpbIjIwMjQtMDEtMDEiXSwiaWQiOjEyM30",
    "limit": 50,
    "direction": "forward"
  }
}

The response includes a cursor field for the next page and a hasMore boolean.

Cross-Entity Queries

Search contacts and companies in one query (PRO and ENTERPRISE only):

{
  "entity": ["contact", "company"],
  "search": {
    "query": "acme",
    "mode": "fulltext"
  }
}

Results include a _entity_type discriminator on each row. Cross-entity queries only work on fields common to both entities.

Validation

CQL queries are validated in multiple stages before execution:

Structural validation (Zod) — checks JSON shape, enforces depth and size limits
Semantic validation — verifies field existence, type-operator compatibility, and enum values
Access control — enforces field-level ACL and tier-based complexity limits
Cost estimation — rejects queries that exceed the tier's complexity budget

Validation errors include a JSON path, an error code, a human message, and often a suggestion:

{
  "errorCode": "INVALID_QUERY",
  "errors": [
    {
      "path": "filter.conditions[0].field",
      "code": "UNKNOWN_FIELD",
      "message": "Field 'emial' does not exist",
      "suggestion": "Did you mean 'email'?"
    }
  ]
}

Common Error Codes

Code	Meaning
`UNKNOWN_FIELD`	The field does not exist in the registry or custom properties
`OPERATOR_TYPE_MISMATCH`	The operator is not valid for the field's type
`VALUE_TYPE_MISMATCH`	The value does not match the field's expected type
`INVALID_ENUM_VALUE`	The value is not a valid enum option
`MISSING_VALUE`	An operator that requires a value is missing one
`UNEXPECTED_VALUE`	A unary operator (like `is_set`) has a value
`MAX_DEPTH_EXCEEDED`	Filter nesting exceeds the maximum depth (5)
`MAX_CONDITIONS_EXCEEDED`	Too many conditions for the current tier
`QUERY_TOO_COMPLEX`	Estimated cost exceeds the tier's budget
`ACCESS_DENIED`	Insufficient role to query a restricted field
`CROSS_ENTITY_NOT_ALLOWED`	Cross-entity queries require PRO or ENTERPRISE
`SEARCH_MODE_NOT_ALLOWED`	The search mode is not available on the current tier
`CURSOR_NOT_ALLOWED`	Cursor pagination requires PRO or ENTERPRISE

Access Control

Every query runs inside an organization context — data is always scoped to the caller's organization.

Field-Level ACL

Each field has a minimum access level:

Access Level	Roles allowed
`public`	All roles (Owner, Admin, Member, Viewer)
`member`	Owner, Admin, Member
`admin`	Owner, Admin only

For example, company.monthlySpend is admin-only — Viewer and Member roles cannot query or filter by it.

Tier Limits

Limit	FREE	PRO	ENTERPRISE
Max conditions per query	20	50	100
Max nesting depth	3	5	5
Max OR groups	2	10	25
Max cost budget	50	200	500
Cross-entity queries	❌	✅	✅
Full-text search	prefix only	fulltext, prefix	all modes
Fuzzy search	❌	❌	✅
Cursor pagination	❌	✅	✅

Query Cost

Each filter condition contributes to the query cost. The cost estimator prevents expensive queries from hitting the database.

Element	Cost
Indexed field, `=` or `!=`	1
Non-indexed field, `=` or `!=`	2
Range operators (`>`, `<`, `between`)	3
Text operators (`contains`, `starts_with`)	5
Custom property condition	8
Full-text search	10
Fuzzy search	20
OR group (multiplier)	1.5× children
Cross-entity (multiplier)	2× total

Use --cost in the CLI to see the estimated cost without executing:

transactional crm query -w "status = ACTIVE" --cost

Performance

Custom Property Batching

CQL batches custom property lookups into a single Common Table Expression (CTE) instead of generating N individual EXISTS subqueries. A query with 5 custom property conditions runs as one JOIN, not five subqueries.

Indexes

Fields marked as indexed in the field registry benefit from B-tree or GIN indexes. Queries on indexed fields are dramatically faster.

Indexed contact fields: organizationId, email, status, lifecycleStage, lastActivityAt, mergedIntoId, activeModules (GIN) Indexed company fields: organizationId, name, domain, status, externalId

Soft-deleted contacts

By default, contacts with status = DELETED are excluded from all queries. To include them, set an explicit status filter:

status = DELETED

CLI Reference

Query

transactional crm query -w "status = ACTIVE"
transactional crm query -w 'email contains "@example.com"' --limit 50
transactional crm query -w "lifecycleStage in (LEAD, CUSTOMER)" --sort createdAt:desc
transactional crm query --query @query.json

Flags:

Flag	Description
`-e, --entity <type>`	`contact` (default) or `company`
`-w, --where <expr>`	SQL-like filter expression
`-f, --filter <json>`	JSON filter group (or `@file.json`)
`--query <json>`	Full CQL query as JSON
`-s, --search <text>`	Full-text search
`--search-mode <mode>`	`fulltext`, `prefix`, or `fuzzy`
`--sort <field:direction>`	e.g. `lastActivityAt:desc`
`-p, --page <n>`	Page number
`-l, --limit <n>`	Results per page (max 100)
`--select <fields>`	Comma-separated fields to return
`--cost`	Show cost estimate without executing
`--explain`	Show the compiled SQL without executing
`--json`	Output raw JSON

Validate

Validate a query without executing it:

transactional crm validate -w "status = ACTIVE AND email contains \"@example.com\""

Parse

Parse a SQL-like expression locally (no API call) — useful for debugging:

transactional crm parse "status = ACTIVE AND lifecycleStage in (LEAD, CUSTOMER)"

Explain

Show the compiled SQL and parameters without executing:

transactional crm query -w "status = ACTIVE AND custom.mrr > 100" --explain

Output includes:

Target entity
Estimated cost
Whether custom property CTE is used
Full SQL for the data and count queries
Parameter list

Fields

Discover available fields and custom properties for an entity:

transactional crm fields --entity contact
transactional crm fields --entity company

Operators

Show all CQL operators and their compatible field types:

transactional crm operators

Examples

Show a curated list of example queries:

transactional crm examples

Complete Examples

Find recent active contacts in a specific lifecycle stage

status = ACTIVE
  AND lifecycleStage in (LEAD, OPPORTUNITY)
  AND lastActivityAt > 2024-10-01

Find contacts without phone numbers who are customers

lifecycleStage = CUSTOMER AND phone is not set

Find high-value companies in specific regions

monthlySpend > 5000 AND country in (US, CA, GB) AND status = ACTIVE

Find contacts active in multiple modules

activeModules has AUTH AND activeModules has SUPPORT

Find contacts added in a date range with a custom property

createdAt between 2024-01-01 and 2024-03-31
  AND custom.source = "partner-referral"
  AND custom.deal_value >= 1000

Complex nested logic

status = ACTIVE
  AND (
    lifecycleStage in (OPPORTUNITY, CUSTOMER)
    OR (
      lifecycleStage = LEAD
      AND custom.score > 80
    )
  )
  AND email is set

Full-text search for companies

{
  "entity": "company",
  "search": {
    "query": "enterprise software",
    "mode": "fulltext"
  },
  "sort": [{ "field": "monthlySpend", "direction": "desc" }],
  "pagination": { "type": "offset", "page": 1, "limit": 20 }
}

Dynamic Lists

Dynamic lists use CQL filters as their membership rule. When the underlying data changes, the list's members are automatically recomputed.

Create a dynamic list with a filter:

# Via the dashboard: navigate to CRM → Lists → Create List → Dynamic
# Or via the API:
curl -X POST http://localhost:3001/crm/lists \
  -H "Authorization: Bearer $TOKEN" \
  -d '{
    "name": "High-value customers",
    "entityType": "CONTACT",
    "isDynamic": true,
    "filterCriteria": {
      "logic": "AND",
      "conditions": [
        { "type": "field", "field": "lifecycleStage", "operator": "eq", "value": "CUSTOMER" },
        { "type": "field", "field": "custom.mrr", "operator": "gt", "value": 1000 }
      ]
    }
  }'

The same filterCriteria JSON that works here can be sent to POST /crm/query — the shape is identical.

Dynamic lists are re-evaluated every 15 minutes by a background worker, or on-demand via POST /crm/lists/:id/evaluate.

API Reference

`POST /crm/query`

Execute a CQL query.

Request body: a CQL query JSON object (see Query Structure).

Response:

{
  "data": [...],
  "meta": {
    "total": 1234,
    "page": 1,
    "limit": 20,
    "totalPages": 62,
    "queryTimeMs": 45,
    "estimatedCost": 12
  }
}

`POST /crm/query?explain=true`

Compile a CQL query and return the generated SQL without executing it.

Response:

{
  "explain": {
    "entity": "contact",
    "dataSql": { "sql": "select ...", "params": [...] },
    "countSql": { "sql": "select count ...", "params": [...] },
    "usesPropertyCte": false,
    "customPropertyKeys": [],
    "page": 1,
    "limit": 20,
    "offset": 0
  },
  "validation": { "valid": true, "warnings": [] },
  "estimatedCost": 3
}

Error Responses

Invalid query (400):

{
  "errorCode": "INVALID_QUERY",
  "errors": [
    {
      "path": "filter.conditions[0].field",
      "code": "UNKNOWN_FIELD",
      "message": "Field 'foo' does not exist",
      "suggestion": "Did you mean 'phone'?"
    }
  ],
  "warnings": []
}

Access denied (403):

{
  "errorCode": "ACCESS_DENIED",
  "errors": [...]
}

Query too complex (400):

{
  "errorCode": "QUERY_TOO_COMPLEX",
  "errors": [
    {
      "path": "",
      "code": "QUERY_TOO_COMPLEX",
      "message": "Query cost (120) exceeds FREE tier limit (50). Simplify the query or upgrade your plan."
    }
  ],
  "estimatedCost": 120
}

Version

This document describes CQL v1. The query language is versioned independently of the API. Future versions will maintain backward compatibility for the JSON structure; the SQL-like parser may gain new syntactic sugar over time.

CQL — CRM Query Language