Cross-collection relations

AppEngine has no foreign keys. Related records are linked by storing one record's pk inside another record's data. There's no enforced referential integrity at write time — but the conventions are stable and the lookups are cheap.

How references look on the wire

A lead record that points to a contact:

{
  "pk": "lead-9f3a...",
  "datatype": "lead",
  "version": 2,
  "state": "new",
  "data": {
    "title": "Q3 inbound",
    "contactId": "contact-abc123",
    "ownerId": "user-77",
    "stage": "qualified"
  }
}

The references are plain string fields inside data — contactId, ownerId. They name the target's pk. There's no _ref magic; clients fetch the related record with another GET /repository/get/contact/contact-abc123.

This convention is everywhere:

Pattern	Example field
Singular reference	contactId, ownerId, productId, categoryId
Multi reference (array)	tagIds, categoryIds, participantIds
Polymorphic	target: { datatype: 'order', id: 'order-...' }
Owner pointer	owner: { datatype, id, name?, email? } (on BaseModel)

The owner field on BaseModel is a built-in polymorphic reference — used when a record is created on behalf of another (a comment owned by a contact, an order owned by a customer).

Fetching the relation

Three options, in order of complexity:

1. 1

   Sequential fetches

   Simplest; one extra round-trip per relation. Fine for detail pages.

   const lead = await get(`/repository/get/lead/${leadId}`);
   const contact = await get(`/repository/get/contact/${lead.data.contactId}`);
   

2. 2

   Find by attribute

   For "all leads belonging to this contact", query by the foreign attribute.

   GET/repository/find-by-attribute/{datatype}/{attribute?}/{attrValue?}JWT

   curl 'https://appengine.appmint.io/repository/find-by-attribute/lead/contactId/contact-abc123' \
     -H 'orgid: my-org' -H "Authorization: Bearer $JWT"
   

3. 3

   Find related (heuristic)

   The find-related endpoint walks any reference field and returns matching records across collections.

   GET/repository/find-related/{datatype}/{anyId}JWT

   It scans for any record whose data mentions the given id. Useful for a "related to this contact" panel where you don't know in advance which collections might mention it.

Querying with filters

For non-trivial joins, the find-by-attribute endpoint won't cut it — use the POST query.

{
  "filter": {
    "data.contactId": "contact-abc123",
    "data.stage": { "$in": ["qualified", "proposal"] },
    "state": "published"
  },
  "sort": { "modifydate": -1 },
  "page": 1,
  "pageSize": 25
}

The filter is Mongo's query syntax — $in, $gte, $regex, $or, all available. The RepositoryService scopes it to the tenant before passing to the provider, so you can't accidentally leak across orgs.

Enrichment

Some collections opt into automatic enrichment. Pass enrich: true on a query and the repository fans out to fetch the named relations and inlines them under a sibling _enriched field. Use sparingly — it's an N+1 in disguise.

{ "filter": { "data.stage": "qualified" }, "enrich": true }

What gets enriched is collection-specific. Built-ins like lead enrich the contact and owner; custom collections won't enrich anything unless you wire the hook.

When you need real joins

For analytics and reporting, neither approach scales — fan-out is too chatty, enrichment is too coarse. Two production-grade options:

• Aggregation pipeline — POST /repository/aggregate/{datatype} accepts a Mongo aggregation pipeline. $lookup works exactly like native Mongo.
• Dynamic Query / Rule Engine — for rule eval and audience segmentation, the dynamic-query layer builds aggregation pipelines from a higher-level filter spec.

curl -X POST https://appengine.appmint.io/repository/aggregate/order \
  -H "orgid: my-org" -H "Authorization: Bearer $JWT" \
  -H "Content-Type: application/json" \
  -d '[
    { "$match": { "state": "published" } },
    { "$lookup": {
        "from": "data",
        "localField": "data.contactId",
        "foreignField": "pk",
        "as": "contact"
    } }
  ]'

Naming conventions

Stick to these and the heuristic endpoints (find-related, enrichment) work without configuration:

• Single ref: <targetCollection>Id — contactId, productId, userId.
• Array of refs: <targetCollection>Ids — tagIds, categoryIds.
• Polymorphic ref: { datatype, id } shape under a meaningful key — target, owner, linkedTo.

Built-in collections follow this consistently; custom collections should too.