Affinity Query Language

This skill covers the structured query language for querying Affinity CRM data via the query MCP tool.

⚠️ Before running queries: Complete the pre-flight checklist from xaffinity://workflows-guide (read data-model, run discover-commands, state what you learned). This ensures you use current syntax and don't miss useful flags.

When to Use This Tool

Use the query tool instead of individual CLI commands when you need:

• •Complex filtering with multiple conditions (AND, OR, NOT)
• •Include relationships (e.g., get persons with their companies)
• •Aggregations (count, sum, avg, min, max, percentile)
• •Grouping (count opportunities by status)
• •Multi-field sorting
• •Batch analysis across large datasets

For simple lookups, prefer execute-read-command with individual commands.

Quick Start

// Simplest query - get 10 persons
{"from": "persons", "limit": 10}

// Add a filter
{"from": "persons", "where": {"path": "email", "op": "contains", "value": "@acme.com"}, "limit": 10}

// Include related companies
{"from": "persons", "include": ["companies"], "limit": 10}

// Query list entries
{"from": "listEntries", "where": {"path": "listName", "op": "eq", "value": "Dealflow"}, "limit": 10}

Query Structure

{
  "$version": "1.0",
  "from": "persons",
  "where": { "path": "email", "op": "contains", "value": "@acme.com" },
  "include": ["companies", "opportunities"],
  "select": ["id", "firstName", "lastName", "email"],
  "orderBy": [{ "field": "lastName", "direction": "asc" }],
  "limit": 100
}

Required Fields

Optional Fields

Filter Operators

Comparison Operators

String Operators

Collection Operators

Multi-Select Field Filtering

Multi-select dropdown fields (like "Team Member") return arrays from the API. The eq and neq operators handle these automatically:

Examples:

// Find entries where Team Member includes "LB"
{ "path": "fields.Team Member", "op": "eq", "value": "LB" }

// Find entries where Team Member includes any of ["LB", "DW"]
{ "path": "fields.Team Member", "op": "has_any", "value": ["LB", "DW"] }

// Find entries where Team Member includes both "LB" and "MA"
{ "path": "fields.Team Member", "op": "has_all", "value": ["LB", "MA"] }

Null/Empty Checks

Compound Conditions

AND

{
  "where": {
    "and": [
      { "path": "status", "op": "eq", "value": "Active" },
      { "path": "amount", "op": "gt", "value": 10000 }
    ]
  }
}

OR

{
  "where": {
    "or": [
      { "path": "email", "op": "contains", "value": "@acme.com" },
      { "path": "email", "op": "contains", "value": "@acme.io" }
    ]
  }
}

NOT

{
  "where": {
    "not": { "path": "status", "op": "eq", "value": "Closed" }
  }
}

Advanced Filtering (Quantifiers, Exists, Count)

Filter based on related entities using quantifiers and existence checks.

Include vs Quantifiers: Use include to get related data in the response (e.g., "include": ["companies"]). Use quantifiers to filter by related data (e.g., {"path": "companies._count", "op": "gte", "value": 2}). You can use both together.

ALL Quantifier

All related items must match the condition:

{
  "from": "persons",
  "where": {
    "all": {
      "path": "companies",
      "where": { "path": "domain", "op": "contains", "value": ".com" }
    }
  }
}

Note: Returns true for records with no related items (vacuous truth). To require at least one, combine with _count:

{
  "where": {
    "and": [
      { "path": "companies._count", "op": "gte", "value": 1 },
      { "all": { "path": "companies", "where": { "path": "domain", "op": "contains", "value": ".com" }}}
    ]
  }
}

NONE Quantifier

No related items may match the condition:

{
  "from": "persons",
  "where": {
    "none": {
      "path": "interactions",
      "where": { "path": "type", "op": "eq", "value": "spam" }
    }
  }
}

EXISTS Clause

At least one related item exists (optionally matching a filter):

// Simple existence check
{
  "from": "persons",
  "where": { "exists": { "from": "interactions" }}
}

// With filter
{
  "from": "persons",
  "where": {
    "exists": {
      "from": "interactions",
      "where": { "path": "type", "op": "eq", "value": "meeting" }
    }
  }
}

Count Pseudo-Field

Count related items and compare:

// Persons with 2 or more companies
{
  "from": "persons",
  "where": { "path": "companies._count", "op": "gte", "value": 2 }
}

// Persons with no interactions
{
  "from": "persons",
  "where": { "path": "interactions._count", "op": "eq", "value": 0 }
}

Available Relationships for Quantifiers

Limitations

• •Nested quantifiers not supported: Cannot use all/none/exists inside another quantifier
• •N+1 API calls: Quantifiers fetch relationship data for each record (use dry-run to preview)

Include Relationships

Fetch related entities in a single query:

{
  "from": "persons",
  "include": ["companies", "opportunities"],
  "limit": 50
}

Available Relationships

Note: For listEntries:

• •entity dynamically resolves to person/company/opportunity based on entityType
• •persons, companies, opportunities, interactions fetch related entities for each list entry

Include Output Format

Included data appears in a separate included section keyed by relationship name:

{
  "data": [{"id": 123, "firstName": "John", "organizationIds": [456]}],
  "included": {
    "companies": [{"id": 456, "name": "Acme Inc", "domain": "acme.com"}]
  }
}

In markdown format, included data appears as separate tables with headers like "Included: companies".

Note: Parent records reference included entities via ID fields (e.g., organizationIds for companies). The included section contains deduplicated records.

Expand Computed Data

Unlike include (which fetches related entities), expand adds computed data directly to each record.

⚠️ ALWAYS use dryRun: true first before running expand queries to see estimated API calls:

// STEP 1: Preview with dryRun
{"query": {"from": "companies", "expand": ["interactionDates"], "limit": 50}, "dryRun": true}

// STEP 2: If API calls look reasonable (<200 calls), run without dryRun
{"from": "companies", "expand": ["interactionDates"], "limit": 50}

Available Expansions

Interaction Dates Output

When using expand: ["interactionDates"], each record includes:

{
  "id": 123,
  "name": "Acme Corp",
  "interactionDates": {
    "lastMeeting": {
      "date": "2026-01-08T10:00:00Z",
      "daysSince": 5,
      "teamMembers": ["Bob Smith", "Carol Jones"]
    },
    "nextMeeting": {
      "date": "2026-01-20T14:00:00Z",
      "daysUntil": 7,
      "teamMembers": ["Alice Wong"]
    },
    "lastEmail": {
      "date": "2026-01-10T09:30:00Z",
      "daysSince": 3
    },
    "lastInteraction": {
      "date": "2026-01-10T09:30:00Z",
      "daysSince": 3
    }
  }
}

Include vs Expand

Parameterized Includes for listEntries

When including interactions for listEntries, you can customize the fetch with parameters:

{
  "from": "listEntries",
  "where": {"path": "listName", "op": "eq", "value": "Dealflow"},
  "include": [
    {"interactions": {"limit": 50, "days": 180}},
    {"opportunities": {"list": "Pipeline"}}
  ]
}

Example: Pipeline with Interaction Dates

{
  "from": "listEntries",
  "where": {"path": "listName", "op": "eq", "value": "Dealflow"},
  "expand": ["interactionDates"],
  "select": ["entityId", "entityName", "fields.Status"],
  "limit": 100
}

Note: expand causes N+1 API calls (one per record). Use dryRun: true to preview the cost.

Aggregations

Basic Aggregates

{
  "from": "opportunities",
  "aggregate": {
    "total": { "count": true },
    "totalValue": { "sum": "amount" },
    "avgValue": { "avg": "amount" },
    "minValue": { "min": "amount" },
    "maxValue": { "max": "amount" }
  }
}

Group By

{
  "from": "opportunities",
  "groupBy": "status",
  "aggregate": {
    "count": { "count": true },
    "totalValue": { "sum": "amount" }
  }
}

Having (Filter on Aggregates)

{
  "from": "opportunities",
  "groupBy": "status",
  "aggregate": {
    "count": { "count": true }
  },
  "having": { "path": "count", "op": "gte", "value": 5 }
}

Querying List Entries

listEntries requires either listId or listName filter:

// By ID
{"from": "listEntries", "where": {"path": "listId", "op": "eq", "value": 12345}}

// By name (executor resolves name → ID at runtime)
{"from": "listEntries", "where": {"path": "listName", "op": "eq", "value": "Dealflow"}}

Invalid paths: list.name, list.id - use listName or listId directly.

Note: When using listName, the query executor looks up the list by name and resolves it to a listId before fetching entries. This adds one API call but allows using human-readable names.

Custom Field Values

When querying listEntries with groupBy, aggregate, or where on fields.* paths, the query engine automatically detects which fields are referenced and requests their values from the API.

{
  "from": "listEntries",
  "where": {"path": "listName", "op": "eq", "value": "Dealflow"},
  "groupBy": "fields.Status",
  "aggregate": {"count": {"count": true}}
}

Best practice: Select only the fields you need:

{
  "from": "listEntries",
  "where": {"path": "listName", "op": "eq", "value": "Dealflow"},
  "select": ["entityName", "fields.Status", "fields.Owner"],
  "limit": 100
}

⚠️ Performance warning: The fields.* wildcard fetches ALL custom field values. For lists with many fields (50+), this can take 60+ seconds per API page. Only use fields.* when you genuinely need every field - otherwise select specific fields like fields.Status, fields.Owner.

Available Select Fields

Field Value Normalization

Reference field values are normalized to display strings for readability:

Note: Use expand or include to get full entity objects when you need IDs or other properties.

Field Paths

Access nested fields using dot notation:

{
  "from": "listEntries",
  "where": { "path": "fields.Status", "op": "eq", "value": "Active" }
}

Common paths:

• •fields.<FieldName> - Custom list fields on listEntries (preferred - select specific fields)
• •fields.* - All custom fields (⚠️ avoid for lists with 50+ fields - very slow)
• •emails[0] - First email in array
• •company.name - Nested object field (on included relationships)

Date Filtering

Relative Dates

{
  "from": "interactions",
  "where": { "path": "created_at", "op": "gte", "value": "-30d" }
}

Supported formats:

• •-30d - 30 days ago
• •+7d - 7 days from now
• •today - Start of today
• •now - Current time
• •yesterday - Start of yesterday
• •tomorrow - Start of tomorrow

Dry-Run Mode

⚠️ MANDATORY for expand/include queries: Always use dryRun: true first to preview API cost.

When to use dryRun:

• •✅ ALWAYS before any query with expand or include
• •✅ ALWAYS before quantifier queries (all, none, exists, _count)
• •✅ Before large result sets (100+ records)
• •Optional for simple filters without relationships

{
  "query": {
    "from": "persons",
    "include": ["companies", "opportunities"]
  },
  "dryRun": true
}

Returns execution plan with:

• •Estimated API calls (key metric - if >200, consider reducing limit)
• •Estimated records
• •Step breakdown
• •Warnings about expensive operations

Decision guide based on dryRun results:

Examples

Find VIP Contacts

{
  "from": "persons",
  "where": {
    "and": [
      { "path": "email", "op": "is_not_null" },
      { "path": "fields.VIP", "op": "eq", "value": true }
    ]
  },
  "include": ["companies"],
  "orderBy": [{ "field": "lastName", "direction": "asc" }],
  "limit": 100
}

Pipeline Summary by Status

{
  "from": "listEntries",
  "where": { "path": "listId", "op": "eq", "value": 12345 },
  "groupBy": "fields.Status",
  "aggregate": {
    "count": { "count": true },
    "totalValue": { "sum": "fields.Deal Value" }
  }
}

Recent Interactions

{
  "from": "interactions",
  "where": {
    "and": [
      { "path": "created_at", "op": "gte", "value": "-7d" },
      { "path": "type", "op": "in", "value": ["call", "meeting"] }
    ]
  },
  "include": ["persons"],
  "orderBy": [{ "field": "created_at", "direction": "desc" }],
  "limit": 50
}

Companies Without Recent Activity

{
  "from": "companies",
  "where": {
    "or": [
      { "path": "lastInteraction.date", "op": "lt", "value": "-90d" },
      { "path": "lastInteraction.date", "op": "is_null" }
    ]
  },
  "limit": 100
}

Tool Parameters

Truncated Responses

When output exceeds maxOutputBytes, the response includes truncated: true and a nextCursor. Pass this cursor to continue from the truncation point:

// Response with truncation
{
  "data": [...],
  "truncated": true,
  "nextCursor": "eyJ2IjoxLC...",
  "_cursorMode": "streaming"
}

// Resume with cursor (keep query and format identical)
{
  "query": {"from": "persons", "limit": 1000},
  "format": "toon",
  "cursor": "eyJ2IjoxLC..."
}

Important: The nextCursor is for output size truncation, not record limits. A query returning all requested records won't have a nextCursor unless the output was too large. See "Handling Truncated Responses" in xaffinity://data-model for details.

Timeout Auto-Calculation

When timeout is not specified, it's automatically calculated based on the query's estimated API calls:

• •Formula: ~2 seconds per API call, minimum 30 seconds
• •Example: Query with 100 API calls → 200 second timeout

The auto-calculation runs a quick dry-run internally to estimate API calls, then sets an appropriate timeout. Specify timeout explicitly to override.

Output Formats

The format parameter controls how results are returned. Choose based on your use case:

Format Recommendations

• •For LLM analysis tasks: Use markdown - LLMs are trained on documentation and tables
• •For large result sets: Use toon to minimize tokens (30-60% smaller than JSON)
• •For programmatic processing: Use json for full structure
• •For streaming workflows: Use jsonl for line-by-line processing

Format Examples

JSON:

{"data": [{"id": 1, "name": "Acme"}], "included": {...}, "pagination": {...}}

JSONL:

{"id": 1, "name": "Acme"}
{"id": 2, "name": "Beta"}

Markdown:

| id | name |
| --- | --- |
| 1 | Acme |
| 2 | Beta |

TOON (default):

data[2]{id,name}:
  1,Acme
  2,Beta
pagination:
  hasMore: false
  total: 2

Note: jsonl and csv are data-only export formats (no envelope). toon, json, and markdown preserve pagination and included entity information.

Truncated Response Example

When output exceeds maxOutputBytes, the response includes truncation metadata (shown for JSON format):

{
  "data": [{"id": 1, "name": "Acme"}, {"id": 2, "name": "Beta"}, ...],
  "executed": ["xaffinity", "query", "--output", "json", ...],
  "truncated": true,
  "nextCursor": "eyJ2IjoxLCJxaCI6IjZmYzJhZDJkYTI5...",
  "_cursorMode": "streaming"
}

For TOON format, truncation appears as:

data[56]{id,name}:
  ...
truncated: true
nextCursor: eyJ2IjoxLCJxaCI6IjZmYzJhZDJkYTI5...
_cursorMode: streaming

Performance

Expand InteractionDates

The interactionDates expansion fetches meeting/email dates and team member names for each record. Performance optimizations:

• •Parallel fetching: Entity fetches and person name resolution run in parallel
• •Shared concurrency limits: Person API calls are bounded to prevent rate limiting
• •Graceful degradation: If person name lookup fails, falls back to "Person {id}" instead of failing the query
• •Progress reporting: Shows per-record progress for large expansions

Recommendations:

• •For large datasets (500+ records), expect ~2 seconds per record
• •Use limit to scope the expansion appropriately
• •The timeout auto-calculates based on estimated API calls

Environment Variables

For advanced tuning (power users only):

Best Practices

1. •Start with dry-run for complex queries to see API call estimates
2. •Use limit to avoid fetching too much data
3. •Be specific with where to reduce client-side filtering
4. •Avoid deep includes which cause N+1 API calls
5. •Use groupBy + aggregate for reports instead of fetching all records
6. •For quantifier queries on large databases, always add maxRecords

Quantifier Query Performance

Quick decision:

• •listEntries → Safe (bounded by list size)
• •persons/companies/opportunities with quantifiers → Requires maxRecords

Important: Queries using all, none, exists, or _count on unbounded entities (persons, companies, opportunities) require explicit maxRecords.

Why? These operations make N+1 API calls (one per record). On a database with 50,000 persons, this could take 26+ minutes.

Recommended approach:

1. •Start from listEntries (bounded by list size) instead of unbounded entities
2. •Add cheap pre-filters before quantifier conditions to reduce N+1 calls
3. •Use maxRecords to explicitly limit scope: maxRecords: 100
4. •Use dryRun: true to preview estimated API calls before running

Example - safe quantifier query:

{
  "query": {
    "from": "listEntries",
    "where": {
      "and": [
        {"path": "listName", "op": "eq", "value": "Target Companies"},
        {"path": "persons._count", "op": "gte", "value": 3}
      ]
    }
  },
  "maxRecords": 1000
}

Limitations

• •All filtering except listEntries field filters happens client-side
• •Includes cause N+1 API calls (1 per parent record)
• •No cross-entity joins (use includes instead)
• •Maximum 10,000 records per query for safety
• •Nested quantifiers (all/none/exists inside each other) not supported
• •OR clauses containing quantifiers cannot benefit from lazy loading optimization