Tenant-Scoped Cross-Child / Cross-Universe Queries

A child entity inherits its tenant scope from its parent: the child table carries parent_eid but no tenant_id column. The parent-scoped Parent-Child Persistence universe answers “all children of this parent” cleanly, but some queries need to span many parents — for example, “all supplies that reference a given VENDOR role, across every Item in my tenant.” This page describes how to do that safely.

Problem

A cross-parent query over a child table has two failure modes:

1. N+1 per-row parent reads. Listing the child rows and then reading each row’s parent (to check tenant membership) is an N+1 that does not scale and is wrong under a bitemporal structure — each parent read must itself resolve the latest non-retired version as-of a time coordinate.
2. Cross-tenant leakage. A child eId is not globally unique across tenants, and the child row has no tenant_id. An unscoped query over the child table returns rows belonging to other tenants — a data-exposure hole.

Solution

Compose the parent universe’s UniversalCondition (its tenant scope) into the child query as a correlated sub-query at the query level. The child query constrains parent_eid to the set of parent eIds that are visible in the caller’s tenant scope:

child.parent_eid IN (
  SELECT parent.eid FROM parent
  WHERE <tenant scope from parent UniversalCondition>
    AND <bitemporal-latest as-of>   -- latest non-retired version of each parent eId as-of (effective, recorded)
    AND parent.retired = false
)

This is the only valid way to scope a cross-child query under a bitemporal structure: resolve the parent’s UniversalCondition into a predicate evaluated in the database, rather than reading parents per row. The bitemporal-latest sub-query must mirror the framework’s own selection condition (latest effective_as_of / recorded_as_of per eId, ordered DESC, limit 1) so it sees exactly the parents the framework would surface.

Worked Example: CrossItemSupplyUniverse

item_supply is a child table with no tenant_id column; tenant scope is inherited from the parent Item. CrossItemSupplyUniverse.findSuppliesBySupplierRole composes ItemUniversalCondition (the parent’s tenant scope) into the supply query as a correlated sub-query over ITEM_TABLE:

override fun findSuppliesBySupplierRole(
  vendorRoleEId: UUID,
  asOf: TimeCoordinates
): DBIO<List<BitemporalEntity<ItemSupply, ItemSupplyMetadata>>> = suspend {
  flatInApplicationContext { ctx ->
    // Compose the parent Item universe's universal condition (tenant scope) into the query.
    ItemUniversalCondition.filter(ctx).flatMap { itemScope ->
      val parentInTenantScope: WhereCondition<ITEM_SUPPLY_TABLE> = {
        this[ITEM_SUPPLY_TABLE.parentEId] inSubQuery currentTenantItemEIds(asOf, itemScope)
      }
      ItemSupplyRecord.recordCompanion.list(
        condition = itemSupplyQCompiler.filter(
          Filter.Eq(ITEM_SUPPLY_TABLE.supplierRef.eId.name, vendorRoleEId)
        ),
        asOf = asOf,
        orderedBy = { emptyList() },
        constraint = parentInTenantScope
      )()
    }
  }
}

The currentTenantItemEIds sub-query mirrors the framework’s bitemporal-latest selection — latest effective_as_of / recorded_as_of per eId, ordered DESC and limited to one — restricted by the composed tenant scope and retired = false. It also fails closed: a Filter.TRUE (global) scope drops the tenant predicate, Filter.FALSE (unauthenticated) matches nothing, and any unexpected scope shape matches nothing rather than leaking.

Companion Rule: Composable, Transaction-Agnostic

A cross-child universe follows the same convention as any other universe operation (see Universe Design): every method returns a composable DBIO<*> and runs no transaction of its own. The calling service owns the transaction boundary — it composes the cross-child action into a single DB action and invokes it inside its own inTransaction(db) { ... }. This keeps the cross-child query reusable inside larger transaction scripts and avoids nested-transaction surprises.

When to Use / When Not

Use a cross-child / cross-universe query when:

• You must find child rows across many parents (the parent eId is not known up front), and
• The child inherits tenant scope from its parent (no tenant_id of its own).

Do not use it when:

• You already know the parent eId — use the parent-scoped child universe (lineAsOf, linesAsOf); it is simpler and the scope is already correct.
• You are tempted to list children and read parents per row — that is the N+1 / leakage anti-pattern this pattern exists to replace.

Until a second cross-child query emerges, this lives as a per-module class (CrossItemSupplyUniverse); it is a candidate for extraction into common-module once the shape repeats.

See Also

• Parent-Child Persistence — parent-scoped child universes and the security callout for single-child reads.
• Universe Design — the composable, transaction-agnostic DBIO contract for universe operations.
• Data Authority Module Pattern § Cross-Service Isolation — why cross-service resolution goes through the owning service interface.