Examples

A lookup is a read-only, not-stored, live mirror of one field on the record(s) reached through an existing relation. A Contact shows its Company's city; a Deal shows its Company's payment_terms.

The value is never entered and never stored — it is resolved from the linked record on every read, so it is always current. The alternative teams reach for otherwise is copying the value onto the child record, which goes stale the moment the parent changes.

Choosing between relation, lookup, and rollup

A lookup cannot exist without a relation — it names one and traverses it.

Configuration

options on the field:

Both are slugs, so renaming the relation, the source field, or their underlying database columns never breaks the mirror.

POST /object/field/contact
{
  "name": "Company City",
  "label": "Company City",
  "type": "lookup",
  "options": {
    "relation_field_slug": "company",
    "source_field_slug": "city_ab12"
  }
}

Creating one is metadata only — no ALTER TABLE, no index, nothing to migrate or revert. There is no column because there is no stored value.

What can be mirrored

Ordinary values: text, long text, number, boolean, date/datetime, currency, select-family, rating, email, phone, link, location, serial number, and the tracking types.

Rejected at create time (422 INVALID_SOURCE_FIELD): another lookup, a calculation, a relation, a file, a photo, or a smart_catalog. Refusing a derived source is what makes a mirror cycle (L1 → L2 → L1) structurally impossible, so the resolver needs no depth limit and no "chain too deep" state.

Reading a lookup

A lookup takes one of three wire shapes, decided by the relation's cardinality and the reducer:

Values render as the source field's type: a mirrored select comes back as its resolved option label (a deleted option reads (removed), never a raw id), a mirrored datetime in the viewer's timezone, a mirrored currency with its precision intact.

Resolved on: record list, search, single record, public read, CSV/XLSX export, and document/PDF rendering.

The resolved descriptor

A lookup stores two slugs, which is enough to resolve the value and nowhere near enough to render it. So every read that returns a lookup descriptor — GET /object/field/active/{objectSlug}, the field list, GET /object/field/show/…, and the fields[] embedded in record reads — stamps the resolved facts onto options:

None of it is stored. Every key is re-derived from the relation and the source field on each request, so it cannot go stale the way a copied value would; a broken lookup carries its stored slugs and the status, and nothing else.

Authorization

A relation leaks at most a target's name; a lookup leaks the actual field data. So the mirrored value is read through the viewer's A/G/M/D view level on the partner object — the same gate a direct read of that record would pass. A source outside the viewer's scope simply does not resolve.

On the public surface a lookup is denied by default and re-admitted only when the partner object is itself public and the source field's type is not PII — so a mirrored email is stripped exactly as a direct email would be.

Everything missing resolves the same way: blank. A null link, a deleted source record, a source the viewer may not read, and a lookup whose config no longer resolves are deliberately indistinguishable — distinguishing them would disclose that a record you cannot see exists.

Lifecycle

• Re-pointing either slug re-resolves live; there is no data to migrate.
• Deleting the relation or the source field leaves dependent lookups resolving to blank — never stale or wrong data. The delete logs which lookups it broke so they can be re-pointed; it is never blocked.
• Finding a broken one: every read of a lookup descriptor carries options.lookup_status ("ok" | "broken") — the field list, the field detail, and the fields[] of a record read. Because a broken lookup degrades to blank instead of erroring, this is the only signal that a column emptied because its config stopped resolving rather than because the data is empty.
• Deactivating the lookup (is_active=false) stops it resolving. Nothing was stored, so nothing is lost and re-activating restores it.

Performance

Resolution is batched: one query per traversed relation, not per record and not per lookup field. Several lookups through the same relation become extra columns of the same statement, and two lookups on the same source field share one column.

A page of N records with K lookups across R relations to P partner objects costs 2 + 4P + R queries — independent of N. A 500-row list with 5 lookup columns is 7 queries; resolving per row would be 2,500.

The schema half of that (1 + 2P — the relation shapes and each partner's field list) is memoized for the whole request, keyed on the request's own context object so it is collected with the request and can never be read by the next one. That matters because a single list read asks for the same schema three times over: the resolver fetching values, the descriptor enricher below stamping source types onto the embedded field list, and a filter push-down resolving columns. Without the memo each paid its own way. Outside a request — a Bull export — there is no context to key on, so the memo is private to the call and behaviour is unchanged.

To-many results are bounded (20 items, 500 characters for join) so a lookup over a relation with thousands of children can never expand a list read. The remaining count is exact and free — a COUNT(*) OVER (PARTITION BY …) in the same statement that pages the rows, so the cell can say "+7 more" without a second query.

Reading a lookup field's descriptor also resolves its source type, the source's option catalog, the relation's cardinality, and its health onto options. That costs one relation read plus one field read per partner object for a whole field list, and saves each client two round trips it would otherwise spend re-deriving the same facts.

Filtering and sorting

Both are pushed down into SQL — never resolved in memory, which would break pagination — and both are scoped to the viewer's right on the partner object, exactly as reading the value is. Filtering on a value you cannot read would otherwise leak it by inference: which rows come back tells you what it is.

A to-one lookup becomes a correlated subquery, so it behaves like an ordinary column: IS_EMPTY matches records with no link, a mirrored currency compares and orders by its parsed amount, a mirrored select compares by option.

On a to-many lookup IS_EMPTY means "no related record has a value" — not "some sibling is blank".

A lookup whose configuration no longer resolves returns 400 LOOKUP_FILTER_UNSUPPORTED rather than being silently dropped from the WHERE clause, which would return an unfiltered list that looks filtered.

Measured cost

Against PostgreSQL 17, 200k contacts → 20k companies, indexed FK, page size 25:

Filtering is not the expensive part — that was the intuition, and the numbers say otherwise. The cost is in the two operations that cannot terminate early.

The reason both are slow is not the index (the partner lookup is a primary-key hit): a correlated subquery in WHERE/ORDER BY forces a serial plan. The same queries rewritten so the planner can parallelise run 6–8× faster — LEFT JOIN LATERAL for the sort (160 ms → 24 ms) and EXISTS for an equality filter's count (120 ms → 14 ms).

Neither rewrite is applied today, for different reasons. The EXISTS form is not a drop-in: it flips the null semantics of negation, where (SELECT …) != 'x' excludes a linkless row but NOT EXISTS(… = 'x') includes it. The LATERAL form is semantically safe but needs the shared paginated executor to expose its FROM clause, which today it does not. Both are worth doing when a tenant's list actually feels slow; neither is worth destabilising the shared read path before then.

Known limitations

• Mutation responses carry it; the broadcast does not. A create/update resolves lookups for the acting admin before responding, so a link change returns the new mirror immediately. The realtime broadcast cannot: it is one payload per object room and a lookup's value is per-reader, so it carries staleFields (the slugs that moved) instead of values.
• Field-level rights do not exist in the platform, so the enforced intersection is (host record access) ∩ (source record access) ∩ (PII rules) — not per-field rights on the source object.
• Snapshots freeze. A lookup captured into a record _snapshot is a point-in-time copy, not a live mirror.
• Realtime is per-reader. Re-pointing a relation pushes each subscriber their OWN resolved value, merged into the event's data; the gateway emits per socket rather than once per room, because two watchers of the same record can legitimately see different values. Almost none of that work is really per-viewer, and none of it is any longer per-viewer round-trips: the schema loads once, every watcher's A/G/M/D level comes back in a single batched matrix read, and viewers whose scope emits the same predicate — everyone at A, everyone at D, every super-admin — share one row fetch. Twenty A-level watchers cost roughly three queries rather than sixty. G and M bind the reader's own id into the predicate and are never folded together. Bounded at 25 viewers per write — beyond that, and if the resolution fails, staleFields names the slugs so the client re-reads.
• Editing or DELETING the SOURCE record — singly or in bulk — notifies the objects that mirror it. A write resolves which other objects hold a lookup whose source field just changed — one indexed query — and each of those object rooms receives a data.lookup_stale carrying the affected field slugs. Per OBJECT, never per record: a Company with 10 000 Contacts costs the same emit as one with ten, which is what makes the notification affordable at all. The new VALUE is not pushed, because that would need resolving per reader for every affected record; the client re-reads what it is showing.
• Not a calculation operand. The formula engine reads stored columns, so a formula naming a lookup slug evaluates to null. Resolving one during recompute would put a cross-object read on the write path.
• Grouping. A kanban board or dashboard widget can filter by a lookup but cannot group by one — grouping needs a column list the board can enumerate.
• A materialized cache is not implemented (the not-stored baseline is always fresh, which is the safer default).