Lookup field — frontend integration

A new field type, lookup, is live. It is a read-only, always-current mirror of one field on the record(s) reached through an existing relation — e.g. showing a Contact's Company city on the Contact.

It is always available: GET /object/field/types reports it with is_available: true, and POST /object/field accepts it.

1. Field-type picker

GET /object/field/types gains one entry:

{ "id": "lookup", "name": "Lookup", "icon": "tabler-zoom-in", "category": "relation", "is_available": true }

Sitting in the relation category, the helper copy that distinguishes the three is worth showing:

Relation links to a record · Lookup shows one of its fields (read-only, always live) · Rollup adds them up.

2. Builder

A lookup needs two choices, both slugs:

{
  "type": "lookup",
  "options": {
    "relation_field_slug": "company",     // required: one of THIS object's relations
    "source_field_slug": "city_ab12",     // required: a field on that relation's target object
    "to_many_reducer": "list",            // optional: list | first | join   (to-many only)
    "join_delimiter": ", "                // optional: with `join`, 1-8 chars
  }
}

• Through relation — a dropdown of the object's existing relations. If it has none, deep-link to the relation builder rather than showing an empty list; a lookup cannot exist without one.
• Field to show — a searchable dropdown of the target object's fields. Only mirrorable types are accepted; showing the type icon helps the author predict what they will get.
• To-many handling — show the reducer control only when the chosen relation is to-many. It is ignored for a to-one relation.

Resolved metadata — no second hop needed

Every endpoint that returns a lookup descriptor stamps the resolved facts onto options. You never have to walk relation → target object → field list to know how to render, filter, or shape a lookup.

{
  "type": "lookup",
  "options": {
    "relation_field_slug": "company",       // stored config
    "source_field_slug": "city_ab12",
    "relation_field_label": "Company",      // ← labels of those two slugs
    "source_field_label": "City",
    "source_field_type": "currency",        // ← render/filter AS this type
    "source_field_options": [ /* … */ ],    // ← option catalog, for filter inputs
    "relation_cardinality": "to_one",       // ← "to_one" | "to_many"
    "lookup_status": "ok"                   // ← "ok" | "broken"
  }
}

• relation_field_label / source_field_label are the display names of the two configured slugs — render "Company → City" straight from the descriptor instead of fetching the relation list and the partner object's fields to translate two opaque slugs. Both fall back to the underlying name, so they are never empty on a resolved lookup. Absent when lookup_status is "broken" (nothing resolved to take a label from).
• source_field_type is the type to render and to build the operator list from. A lookup mirroring a currency formats and filters as currency.
• source_field_options is the source's option catalog when it is option-backed, so a filter value input has choices without another fetch.
• relation_cardinality already accounts for direction — the same relation is to-many read from one side and to-one from the other. Use it to gate the sort affordance, show the reducer control in the builder, and decide which wire shape to expect.
• lookup_status: "broken" means the relation or source field no longer resolves. Because that degrades to a blank cell rather than an error, it is the only signal distinguishing "the config broke" from "the data is empty". Surface a re-point/delete action; the deletes that break a lookup are never blocked.

Present on GET /object/field/active/{objectSlug}, the field list, GET /object/field/show/…, and the fields[] embedded in record reads — one shape everywhere. None of it is stored: it is re-resolved per request, so it cannot go stale.

Rejections to surface

Not mirrorable: another lookup, calculation, relation, file, photo, smart_catalog. Filter these out of the "field to show" dropdown so the rejection is rare.

3. Reading — ⚠️ three wire shapes

This is the main integration point. A lookup's value shape depends on the relation's cardinality and the reducer, so a generic cell renderer will trip over it. All three are derivable from field.options, so branch statically:

Values arrive rendered as the source field's type, so reuse that type's renderer rather than writing a lookup-specific one:

• a mirrored select/status → the usual enriched option array ({ id, value, label, color, icon }[]); a deleted option reads label: "(removed)", never a raw id
• a mirrored datetime → already projected into the viewer's timezone
• a mirrored currency → the usual "222 KWD" string

has_more: true means the list was capped at 20, and remaining is the exact count of values the cap dropped — render "+7 more", not a vague "…". It is exact, not an estimate: the count comes from a window function inside the same statement that pages the rows, so it costs no extra query and reflects everything the viewer is allowed to see.

A join cell carries the same information already rendered into the string. One exception: a cell truncated by the 500-character bound ends in a bare …, because at that point the item count no longer describes what was cut.

4. Rendering on a record

Render as non-editable with a "linked" affordance and a tooltip along the lines of "From <Relation> → <Field> (read-only, always live)". There must be no input to focus: read-only-ness should be visible, not merely enforced.

Any submitted value for a lookup slug is silently dropped by the server — no error, nothing stored.

5. Empty states

null is the single "no value" signal and covers all of: no linked record, the linked record was deleted, and the viewer may not read the source. Render one neutral empty cell. The cases are deliberately indistinguishable — distinguishing them would disclose that a record the viewer cannot see exists, so please do not try to infer which it was.

6. ✅ Lookups ARE in create/update responses

Changed. Unlike calculation and serial_number (recomputed async and delivered over WS), a lookup is resolved synchronously, against your own rights, before the response is built. So:

// PUT /object/data/contact/sara  — re-pointing the company link
{ "data": { "company": "acme-2", "lk_city": "Jeddah" } }   // ← the new mirror, in the response

Delete the local inference. You do not need to work out which lookups depend on the relation you just changed, and you do not need a follow-up read: apply the response like any other field. Re-deriving that dependency client-side duplicates server knowledge and goes stale the moment the rule changes.

staleFields (§12) is still what tells you a different user moved a link on a record you have open — that case has no local answer.

7. Filtering and sorting

Both work, on the records list and the kanban board. The predicate is pushed into SQL against the related record — nothing is filtered in memory, so pagination and totals stay correct.

Offer the operators of the SOURCE field's type, not the lookup's. A lookup mirroring a currency filters like a currency; one mirroring a select filters by option. FIELD_TYPE_OPERATORS["lookup"] is still [], so an operator-driven picker renders nothing for it — read the source field's type from options.source_field_slug to populate the operator list.

Two behaviours worth surfacing in copy:

• On a to-many lookup, IS_EMPTY means "no related record has a value", not "some sibling is blank".
• Sorting a to-many lookup is a 400, not a silent fallback — there is no single value to order by. Disable the sort affordance on those columns.

Still returns 400 LOOKUP_FILTER_UNSUPPORTED when the lookup's config no longer resolves (relation or source field deleted) — see lookup_status in §2.

Group-by is still unsupported on kanban and dashboards.

8. Public forms

A lookup appears on a public read only when the object it traverses into is itself public and the source field's type is not PII. Otherwise both the field descriptor and its value are absent — the same treatment a PII field gets.

9. Export / documents

CSV/XLSX exports and document/PDF templates emit the resolved value (a to-many list renders joined, with … when truncated). An unresolved source is an empty cell — never a raw id.

Import ignores every computed field, not just lookup. The rule is now keyed off the shared computed-field set, so calculation and serial_number columns in an uploaded file are skipped exactly like a lookup's. Previously only lookup was inert here — and only by accident, because it has no physical column to write to; a serial_number column in a file WOULD have been written, forging a serial the engine believes it owns. One rule keyed off COMPUTED_FIELD_TYPES in FieldMappingStep mirrors the backend exactly.

⚠️ Behaviour change worth a word in the import UI: an export/re-import round trip no longer preserves serial numbers — the re-imported records get freshly generated ones. That is the intended trade (the alternative is letting a file dictate serials, which breaks uniqueness for every later record), but it will surprise anyone using export→import as a copy tool. Consider greying these columns in the mapping step with "generated automatically" rather than silently dropping them.

11. Response to the backend asks

On BE-3

Split in two, because the halves have very different costs.

Mutation responses — agreed in principle. One contained call site (data-write.controller), and since the client re-reads anyway the queries are paid either way; doing it server-side just saves the round trip. Not shipped in this pass only because it lands on the record write path and this change set has not had a test run behind it yet. It is a small, isolated follow-up.

WS notification when someone ELSE edits the source — ✅ now shipped, as data.lookup_stale (§12). The original objection was a cross-record fan-out: editing one Company would have to find every record whose lookups reach into it and push to each, which is unbounded at 10k Contacts.

That objection only holds if you push values. Pushing staleness is per-OBJECT, not per-record — one emit regardless of how many records link to the edited one — so the fan-out never happens. What remains deferred is delivering the new value itself, which would need per-reader resolution across every affected record.

On the FRD's open questions

• OQ-3201 (restricted-source visibility) — resolved in the implementation: option (a), always blank. A source outside the viewer's A/G/M/D resolves to null, indistinguishable from "no link" and "deleted", because distinguishing them discloses that a record you cannot see exists. This now also governs filtering and sorting.
• OQ-3203 (to-many default) — resolved: the default stays list, bounded at 20 items / 500 characters, and remaining now makes the bound legible rather than mysterious. No explicit reducer is required.

12. Realtime — the value IS pushed now

Changed. When a relation link moves, each subscriber receives the new mirrored value resolved against their own rights, merged into data exactly like any other field:

{
  "eventType": "data.updated",
  "changedFields": ["company"],
  "staleFields": ["lk_city"],
  "data": { "slug": "sara", "company": "acme-2", "lk_city": "Jeddah" }  // ← your value
}

Apply data as you already do — nothing lookup-specific to write.

The transport is unchanged

Same namespace, same handshake, same subscribe message, same event names. Only the contents of data changed, so there is nothing to migrate:

const socket = io(`${API_BASE}/data/events`, { auth: { token }, transports: ['websocket'] });
socket.on('connected', () => socket.emit('subscribe:object', { objectSlug }));
socket.on('data.updated', applyChange);   // also data.created / data.deleted

The whole integration

function applyChange(e) {
  if (e.eventType === 'data.deleted') return rows.remove(e.dataSlug);
  if (e.data) rows.upsert(e.dataSlug, e.data);

  // A slug named in staleFields but ABSENT from data was not resolved for you.
  const missing = (e.staleFields ?? []).filter((s) => !(s in (e.data ?? {})));
  if (missing.length) scheduleRefetch(e.objectSlug, e.dataSlug);
}

⚠️ Use in, not truthiness. "lk_city": null is a delivered value meaning "blank" — no link, deleted source, or no rights on the mirrored object. Writing !e.data[slug] refetches on every legitimately-empty lookup.

⚠️ Debounce scheduleRefetch per (objectSlug, dataSlug). When resolution fails it fails for every watcher at once (see below), so up to 25 clients hit the refetch path in the same instant.

⚠️ lookupValuesByAdmin must never appear on a payload. It is a server-side routing map holding every reader's values; the gateway strips it before emit. If you ever observe that key, stop and report it — that is a leak, not a feature.

Why staleFields is still there

It is the fallback, and you should still handle it. A subscriber gets no mirrored value in two cases:

• more than 25 people are watching that object (the write resolves for the first 25 and stops — one write cannot pay for unbounded viewers), or
• resolution failed on that write, in which case nobody gets a mirrored value. Viewers who resolve to the same access level share one query, so a failure is a property of the query rather than of a reader; the write itself still succeeded.

Both leave staleFields naming the affected slugs. Rule: if a slug is in staleFields and absent from data, refetch it — do not keep the old value.

What is deliberately per-reader

Two subscribers watching the same record can legitimately receive different values for the same lookup, including null. That is not a bug: a lookup mirrors another object, and their rights there differ. Do not cache a lookup value against the record and reuse it across users.

✅ Editing the SOURCE record now notifies you too

New. Previously only this record's relation moving pushed anything. Editing the mirrored record — someone changes Company.city — told nobody, and your Contact list kept showing the old city until a manual refresh. That was the more common way a mirror went stale, and it is now covered.

You receive a new, separate event on the mirroring object's room:

{ "eventType": "data.lookup_stale", "objectSlug": "contact",
  "staleFields": ["lk_city"], "timestamp": "…" }

Handle it with one line, because it reduces to the rule you already implement:

socket.on('data.lookup_stale', (e) => scheduleRefetch(e.objectSlug, e.staleFields));

No record slugs and no values, by design. A Company may have 10 000 Contacts; naming them would be a fan-out per write, and a mirrored value would have to be resolved against each reader's rights on the source object. So this event says only "these lookup columns on this object may have moved — re-read the rows you are showing." Refetch the current page, not the world.

It only fires when the mirrored source field actually changed: editing a Company's phone will not invalidate a mirrored city. Deleting the source fires it too — every mirror of a deleted record blanks, which is the same staleness — as do bulk edits and bulk deletes. One listener covers all of them; live updating never depends on how the value happened to be edited.

Bulk events carry no row at all

data.bulk_updated / bulk_assigned / bulk_deleted give you affectedSlugs and affectedCount — no data, and no staleFields. (A bulk write on a record that OTHERS mirror still emits data.lookup_stale to their rooms — that is a separate event, covered above.) That is deliberate: there is no row in the payload, so nothing in it can be stale.

A bulk update can move relations, so treat affectedSlugs as "re-read these". Do not look for staleFields here — its absence is not a signal that lookups are current.

13. Still not covered

Pushing the mirrored value on a source edit. data.lookup_stale names the columns; it does not carry the new value, because that would need resolving per reader for every affected record. You re-read. OQ-3205 is closed for notification, open for value delivery.