Relationships

Purpose

Relationships model associations between entities that are not structural containment.

They answer:

How is this entity associated with another entity?

Examples:

Asset uses Credential
Asset depends_on Asset
Asset connects_to Asset
User manages Site
Connection Method applies_to Building
Vendor supplies Asset

Relationship vs Hierarchy

Hierarchy:

Customer -> Site -> Building -> Asset

Relationship:

Asset -> uses -> Credential
Asset -> connects_to -> Asset

Containment belongs to entities.parent_entity_id and entities.path.

Relationships belong to entity_relationships.

Direction

Relationships are directional by default.

Example:

Asset A depends_on Asset B

This is not necessarily the same as:

Asset B depends_on Asset A

If the relationship is conceptually symmetric, the service can either:

• treat it as symmetric in queries,
• or create two directed rows.

Prefer one directed row plus explicit query behavior.

Relationship Types

Initial relationship types:

uses
depends_on
connects_to
managed_by
owned_by
supplied_by
applies_to
replaces
related_to

Start with relationship_type text for flexibility.

Add a relationship_type_definitions table later if you need:

• display metadata,
• allowed source/target type rules,
• directionality configuration,
• inverse names,
• UI icons,
• permission rules.

Table

Recommended fields:

id
source_entity_id
target_entity_id
relationship_type
metadata
created_at
updated_at
deleted_at

Unique active edge:

CREATE UNIQUE INDEX uq_entity_relationships_active
ON entity_relationships(source_entity_id, target_entity_id, relationship_type)
WHERE deleted_at IS NULL;

Query Patterns

Outgoing Relationships

SELECT r.*, target.*
FROM entity_relationships r
JOIN entities target ON target.id = r.target_entity_id
WHERE r.source_entity_id = $1
  AND r.deleted_at IS NULL
  AND target.deleted_at IS NULL;

Incoming Relationships

SELECT r.*, source.*
FROM entity_relationships r
JOIN entities source ON source.id = r.source_entity_id
WHERE r.target_entity_id = $1
  AND r.deleted_at IS NULL
  AND source.deleted_at IS NULL;

Relationship By Type

SELECT r.*
FROM entity_relationships r
WHERE r.source_entity_id = $1
  AND r.relationship_type = $2
  AND r.deleted_at IS NULL;

Graph Traversal

Use recursive CTEs for graph traversal where needed.

Example: dependency chain.

WITH RECURSIVE dependency_tree AS (
  SELECT
    r.source_entity_id,
    r.target_entity_id,
    r.relationship_type,
    1 AS depth
  FROM entity_relationships r
  WHERE r.source_entity_id = $1
    AND r.relationship_type = 'depends_on'
    AND r.deleted_at IS NULL

  UNION ALL

  SELECT
    r.source_entity_id,
    r.target_entity_id,
    r.relationship_type,
    dt.depth + 1
  FROM entity_relationships r
  JOIN dependency_tree dt ON dt.target_entity_id = r.source_entity_id
  WHERE r.relationship_type = 'depends_on'
    AND r.deleted_at IS NULL
    AND dt.depth < 10
)
SELECT * FROM dependency_tree;

Use graph traversal sparingly. Most UI operations should use direct relationships, not unbounded graph walks.

Relationship Validation

Service layer should validate:

• source exists and is active,
• target exists and is active,
• user has scope to source,
• user has scope to target where required,
• relationship type is allowed,
• source type and target type are allowed,
• duplicate active edge does not exist,
• self-relationship is allowed only where meaningful.

Relationships and Authorization

A relationship may connect entities in different root scopes.

Examples:

Internal engineer user -> manages -> Customer asset
Vendor -> supplies -> Asset

Default rule:

• user must be authorized for the source entity,
• user must be authorized for the target entity unless the relationship type explicitly allows cross-scope links.

Cross-scope relationships require careful audit.

Relationships and Soft Delete

When an entity is soft-deleted, related active relationships should normally be soft-deleted too.

For subtree soft delete:

• soft-delete relationships where source is in subtree,
• soft-delete relationships where target is in subtree,
• consider whether external incoming relationships should be preserved as historical records.

Recommended Alpha behavior:

Soft-delete active relationships touching deleted entities.

Examples

Asset Uses Credential

source: Asset entity
relationship_type: uses
target: Credential entity

Connection Method Applies To Site

source: Connection Method entity
relationship_type: applies_to
target: Site entity

Asset Depends On Asset

source: Downstream Asset
relationship_type: depends_on
target: Upstream Asset