Transitive Relationships

Problem/Motivation

CRM relationships can represent direct connections between two contacts, such as parent/child, supervisor/subordinate, mentor/mentee, or organization/department. Some relationship types are transitive, while others are only meaningful between the two directly related contacts.

For example, if Alice is the child of Bob, and Bob is the child of Carol, then Carol should be discoverable as Alice's grandparent. Likewise, if Sam reports to Dana, and Dana reports to Morgan, then Morgan should be discoverable as someone Sam reports up to.

The current direct relationship model is not sufficient for efficient transitive lookups. Drupal Entity Query and Views do not provide portable recursive querying, and recursive common table expressions should not be assumed available. Computing these relationships on demand in PHP would be inefficient and difficult to expose consistently to Drupal APIs.

The system needs a Drupal-friendly way to query transitive relationships across multiple levels without making all relationship types transitive. For example, mentor/mentee may be directional but should not automatically imply that a mentor's mentor is also the mentee's mentor.

Steps to reproduce

1. Create a CRM relationship type such as parent/child or supervisor/subordinate.
2. Create a relationship where contact A is related to contact B.
3. Create another relationship where contact B is related to contact C.
4. Attempt to query all indirectly related upstream contacts for contact A.
5. Only the direct relationship to contact B is readily available; contact C must be discovered by manual recursive traversal.

Proposed resolution

Add support for explicitly transitive relationship types. A transitive relationship type is one where indirect relationships should be indexed and queryable.

Add configuration to CRM relationship types to indicate whether the type is transitive and, if so, which direction should be treated as lower-to-higher:

• transitive: boolean.
• transitive_direction: for example, a_to_b or b_to_a.

For example, a_to_b means contact A is the descendant/lower contact and contact B is the ancestor/higher contact.

Store direct relationships in the existing crm_relationship entity. Add derived closure tables that store the transitive closure of enabled relationship types. These tables are not the source of truth and should be rebuildable from crm_relationship.

Use a closure table for fast indexed lookups. The closure table stores each reachable descendant/ancestor pair and the distance between them. For example:

relationship_type | descendant_id | ancestor_id | depth
------------------|---------------|-------------|------
parent            | 10            | 20          | 1
parent            | 20            | 30          | 1
parent            | 10            | 30          | 2

Use a second path-step table when exact route reconstruction is needed. This avoids storing relationship or contact ID paths in varchar/text columns, which would impose an arbitrary maximum depth.

closure_id | step | relationship_id | from_contact_id | to_contact_id
-----------|------|-----------------|-----------------|--------------
1          | 0    | 101             | 10              | 20
1          | 1    | 102             | 20              | 30

Add a service responsible for maintaining and rebuilding the derived closure tables. The service should support synchronous updates for small changes and deferred updates for larger changes.

Add configuration for deciding when closure indexing should happen synchronously or asynchronously:

• A configurable affected-row threshold.
• An async mode, such as auto, batch, or queue.

When a relationship form submission exceeds the configured threshold and async mode is auto, show a pre-save confirmation screen. The primary relationship entity should not be saved until the user confirms. The confirmation should allow the user to choose between processing now with Batch API or deferring to Queue API.

For programmatic saves or non-interactive changes, large closure updates should use Queue API. For full rebuilds from the UI, Batch API should be preferred. A Drush command or administrative operation should be available to rebuild the closure tables from source relationship entities.

Remaining tasks

• Define relationship type configuration for transitive behavior and transitive direction.
• Add schema for the closure table and path-step table.
• Create a closure manager service to calculate affected rows and maintain derived tables.
• Add queue worker support for deferred closure updates.
• Add Batch API support for interactive large updates and full rebuilds.
• Add pre-save confirmation handling to relationship forms when a large transitive change is detected.
• Add configuration for synchronous threshold and async processing mode.
• Add a full rebuild operation for administrators and/or Drush.
• Add validation to prevent cycles in transitive relationship types.
• Add tests for direct related contacts, transitive related contacts, multiple parents, multiple paths, queue updates, batch updates, and rebuild behavior.
• Document the difference between directional relationships and transitive relationships for site builders.

User interface changes

• Relationship type forms should include settings for whether the relationship type is transitive.
• Relationship type forms should include a transitive direction setting when transitive support is enabled.
• Site builders should be warned that only truly transitive relationships should enable transitive lookup.
• Configuration UI should allow setting the synchronous update threshold.
• Configuration UI should allow choosing async behavior: automatic, Batch API, or Queue API.
• When an interactive relationship form change exceeds the threshold and automatic mode is enabled, show a confirmation screen before saving.
• The confirmation screen should offer options to process now with progress or defer processing to the background.
• If the user cancels at confirmation, the relationship entity should not be saved.

API changes

• Add APIs for querying upstream contacts for a transitive relationship type.
• Add APIs for querying downstream contacts for a transitive relationship type.
• Add APIs for retrieving depth/distance between related contacts.
• Add APIs for reconstructing the relationship path between two contacts when path data is available.
• Add a closure manager service for rebuilding or updating derived closure indexes.
• Add queue worker integration for deferred closure updates.
• Add batch operations for interactive rebuilds.
• Add configuration APIs for closure threshold and async processing mode.

Data model changes

The existing crm_relationship entity remains the source of truth for direct relationships.

Add relationship type configuration fields:

• transitive: whether this relationship type participates in transitive closure indexing.
• transitive_direction: which contact position points from lower/descendant to higher/ancestor.

Add a derived closure table, for example crm_relationship_closure:

id
relationship_type
descendant_id
ancestor_id
depth
path_hash
created
changed

Recommended indexes:

relationship_type, descendant_id, depth
relationship_type, ancestor_id, depth
relationship_type, descendant_id, ancestor_id
relationship_type, descendant_id, ancestor_id, path_hash

Add a derived path-step table, for example crm_relationship_closure_path:

closure_id
step
relationship_id
from_contact_id
to_contact_id

Recommended indexes:

closure_id, step
relationship_id
from_contact_id
to_contact_id

The closure and path tables are derived indexes. They may become temporarily stale when asynchronous processing is selected, but they must be rebuildable from canonical crm_relationship data.