Annotated relationships: allow metadata on reference property values

[mitselek]

Problem

When a relationship between entities carries per-relationship context — roles, dates, permissions, weights — there is no way to store that context on the relationship itself. Users must create junction entities: a separate entity type whose sole purpose is to hold the reference plus its context.

This is a common pattern in relational databases (junction/bridge tables), but Entu's entity-property model already has a more expressive primitive: multi-valued properties. A _parent property can hold multiple references. The missing piece is that each reference value cannot carry metadata alongside it.

The result:

• Doubled entity count. Every relationship requires a dedicated entity, not just a property value.
• Sync overhead. The junction entity must stay in sync with the structural relationship it annotates. If a _parent reference is removed but the junction entity isn't deleted, orphans accumulate.
• Lost structural benefits. Junction entities don't participate in _parent-driven features like _inheritrights cascade, _child.Type.prop formula aggregation, or webapp parent-child browsing. The structural relationship and the contextual data live in different places.

Use Case: Polyphony (Choral Music Platform)

We're rebuilding Polyphony on Entu as a proof of concept. A core domain concept: a choir member belongs to one or more organizations, with a role (conductor, section_leader, librarian, admin, owner) and a join date per organization.

Today, we model this as:

Organization (entity type)
  └── Membership (junction entity — child of Organization)
        ├── member: reference → Member entity
        ├── role: string (e.g., "section_leader")
        └── joined_at: date
Member (entity type — standalone, under Database entity)

Adding someone to an organization = creating a Membership entity. The Membership carries the per-org context, and its _parent to the Organization gives it inherited rights.

This works, but it means:

• Every member-org pair creates an entity (a choir of 60 members in 3 voice sections = 60 Membership entities per org)
• "List members of this org" queries Memberships, not Members
• The Member entity itself has no structural relationship to the Organization — we lose _child.Member formula aggregation and webapp browsing of Members under their Org

With annotated relationships, the model would be:

Organization (entity type)
  └── Member (multi-parent — _parent to each Organization)
        └── _parent values carry role + joined_at as metadata

No junction entities. One operation to add or remove someone. Native _child.Member counts. Rights cascade. Webapp browsing. The relationship IS the data.

Current Behavior

Property values for system properties (_parent, _type, _owner, etc.) are restricted to a single typed field. The validation in server/utils/entity.js (lines 125-179):

const systemPropertyValueTypes = {
  _type: 'reference',
  _parent: 'reference',
  _noaccess: 'reference',
  _viewer: 'reference',
  _expander: 'reference',
  _editor: 'reference',
  _owner: 'reference',
  _sharing: 'string',
  _inheritrights: 'boolean'
}
const allValueFields = ['string', 'number', 'boolean', 'reference', 'date', 'datetime']

// ...

const expectedValueField = systemPropertyValueTypes[property.type]

if (expectedValueField) {
  // Reject if expected field is missing
  if (property[expectedValueField] === undefined || property[expectedValueField] === null) {
    throw createError({
      statusCode: 400,
      statusMessage: `Property ${property.type} must have ${expectedValueField} value`
    })
  }

  // Reject ALL other typed fields
  const disallowedFields = allValueFields.filter((f) => f !== expectedValueField)
  for (const field of disallowedFields) {
    if (property[field] !== undefined) {
      throw createError({
        statusCode: 400,
        statusMessage: `Property ${property.type} must only have ${expectedValueField} value`
      })
    }
  }
}

Posting {type: "_parent", reference: "orgId", string: "section_leader"} returns:

400: Property _parent must only have reference value

Note: Non-system properties (lines 188-209) already allow multiple typed fields — the restriction is only on system properties. And the aggregation layer (propertiesToEntity in aggregate.js, line 343) already adds a string field to reference property values (the referenced entity's display name). So the infrastructure partially supports multi-field values already.

Proposed Behavior

Allow property values to carry additional typed fields alongside their primary value. The primary value field remains required for system properties; additional fields are optional metadata.

Example — annotated _parent:

[
  {
    "type": "_parent",
    "reference": "orgId1",
    "string": "section_leader",
    "date": "2024-01-15"
  },
  {
    "type": "_parent",
    "reference": "orgId2",
    "string": "conductor",
    "date": "2023-09-01"
  }
]

A Member with this _parent property:

• Is a child of both orgId1 and orgId2 (existing multi-parent behavior)
• Carries per-relationship context: role as string, join date as date
• Inherits rights from both parents (existing behavior)
• Counts in both parents' _child.Member formulas (existing behavior)

Implementation sketch:

1. Validation change (entity.js, lines 160-179): Keep requiring the primary value field. Remove the loop that rejects other typed fields. Optionally, define which additional fields are allowed per system property type.

2. Storage: No change needed — MongoDB documents already support arbitrary fields. The property collection schema doesn't constrain field presence.

3. Aggregation (aggregate.js): propertiesToEntity already copies all fields from property documents (line 328: let cleanProp = { ...prop }). Additional fields would be preserved in the aggregated entity automatically.

4. Query filters: Extend the existing dot-notation query syntax. _parent.reference=orgId already works (used internally). Add _parent.string=section_leader to filter by metadata. This would allow queries like "find all Members who are conductors in org X."

5. Webapp: The entity detail view and property editors would need to display/edit the additional fields. This could be progressive — show metadata fields only when present.

Benefits

• Eliminates junction entities for annotated relationships, reducing entity count and complexity
• Single source of truth — the relationship and its context are one property value
• Preserves native multi-parent behavior — rights cascade, formula aggregation, webapp parent-child browsing all work unchanged
• Backward compatible — existing single-field property values continue to work. Multi-field is purely additive.
• Aligns with graph database best practices — annotated edges (relationships with properties) are a standard pattern in Neo4j, Amazon Neptune, and other graph databases. Entu's property model is well-positioned to support this natively.

Alternative (Current Workaround)

Junction entities. This is what we're implementing for Polyphony. It works but doesn't leverage Entu's structural relationship model and introduces sync overhead between the junction entity and the actual _parent relationship.

Scope

This could be implemented incrementally:

1. Phase 1: Allow additional fields on system properties (just remove the validation restriction). No query support yet — metadata is stored and returned but not filterable.
2. Phase 2: Add query filter support (_parent.string=value).
3. Phase 3: Webapp UI for viewing/editing metadata fields on property values.

Phase 1 alone would unblock the Polyphony use case — we'd handle querying in application code.

---

Filed by the entu-research team (Polyphony PoC) — @mitselek

(ER:Codd + Saavedra)