Dynamic Lists

Lists are Clarify's pipeline and saved-view abstraction. A dynamic list is a SQL-driven view that auto-evaluates against your data: records enter and leave the list as they start or stop matching its query.

This guide covers authoring the SQL query that powers a dynamic list. For the full list CRUD surface, see the Lists Object endpoints.

Dynamic list queries

Dynamic lists use a SQL query to filter and display records. Pass a query object with the sql, and stamp it with version: 6 (the current query format). version is optional, but an omitted value is treated as the oldest format and run through the query migrations on save, which can rewrite your SQL.

{
  "title": "High-value deals",
  "type": "dynamic",
  "description": "Deals over $10k",
  "query": {
    "sql": "SELECT \"deal\".name AS \"deal:__object__\", \"deal\".stage, \"deal\".amount FROM \"deal\" WHERE \"deal\".amount > 10000 ORDER BY \"deal\"._created_at DESC NULLS LAST",
    "version": 6
  }
}

If you omit query, the API generates a default view with standard columns. Only provide it when you need custom columns or filters.

Column conventions

Columns must be fully qualified with the object type, and use specific alias patterns for the UI to render them correctly.

Relationship columns require a LEFT JOIN:

SELECT "deal".name AS "deal:__object__",
       "deal$company_id".name AS "deal$company_id:__object__",
       "deal$owner_id".name AS "deal$owner_id:__object__",
       "deal".amount, "deal".stage
FROM "deal"
LEFT JOIN "company" AS "deal$company_id"
  ON "deal".company_id = "deal$company_id"._id
LEFT JOIN "user" AS "deal$owner_id"
  ON "deal".owner_id = "deal$owner_id"._id

Without the JOIN, relationship columns render as raw UUIDs.

Filtering on multi-select fields

Multi-select fields (including the built-in labels field) store their values as JSONB shaped { "items": [...] }. Filter them with the array-overlap operator ?| against the items key:

WHERE ("deal".labels->'items') ?| ARRAY['hot-lead', 'enterprise']

This renders as a native filter widget (a dropdown with chips) in the Clarify UI.

Filter patterns by field type

Updating a query

When updating query, the entire query object is replaced. If you need to add columns to a list that has user-applied filters, GET the list first, merge your changes into the existing SQL, and PATCH with the complete query.