---
title: "Building a Generic Entity Relationship System: Database to React | DJ Petersen"
description: "How we built a universal relationship system that automatically connects clusters, workflows, users, and organizations - from PostgreSQL schema to React components."
canonical: "https://www.thedjpetersen.com/llm-thoughts/entity-relationship-system/"
---

# Building a Generic Entity Relationship System: Database to React

## How we built a universal relationship system that automatically connects clusters, workflows, users, and organizations - from PostgreSQL schema to React components.

Jul 8, 2025

Our application had isolated components everywhere. Clusters existed. Workflows existed. Users existed. But they lived in separate silos with no visibility into how they connected.

A user would create a workflow, the workflow would provision clusters, but there was no easy way to see which clusters belonged to which workflows, or which users had access to what. We needed a universal relationship system that could connect any entity to any other entity.

The goal: build a generic relationship system that automatically tracks connections as they're created, then visualize those relationships in the UI without writing entity-specific code.

## The Problem: Entity Islands

Our database had separate tables for each entity type:

```sql
-- Isolated entity tables
CREATE TABLE users (id UUID PRIMARY KEY, name TEXT, email TEXT);
CREATE TABLE organizations (id UUID PRIMARY KEY, name TEXT, domain TEXT);
CREATE TABLE workflows (id UUID PRIMARY KEY, name TEXT, status TEXT);
CREATE TABLE clusters (id UUID PRIMARY KEY, name TEXT, provider TEXT);
```

Relationships existed implicitly in business logic:

```go
// Business logic knew about relationships, but database didn't track them
func CreateWorkflow(userID, orgID string, spec WorkflowSpec) (*Workflow, error) {
    workflow := &Workflow{
        ID:             generateID(),
        UserID:         userID,         // Implicit relationship
        OrganizationID: orgID,          // Implicit relationship
        Name:           spec.Name,
        Status:         "pending",
    }
    // Save workflow...

    // Create clusters for workflow
    for _, clusterSpec := range spec.Clusters {
        cluster := &Cluster{
            WorkflowID: workflow.ID,    // Implicit relationship
            UserID:     userID,         // Implicit relationship
            Name:       clusterSpec.Name,
        }
        // Save cluster...
    }
}
```

Relationships were scattered across foreign keys, embedded in JSON fields, or only existed in application memory. There was no unified way to ask "what's connected to this user?" or "show me everything related to this workflow."

## The Solution: Universal Entity Relationships

We built a generic relationship system that can track connections between any entity types:

```sql
-- Generic relationship table
CREATE TABLE entity_relationships (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    from_entity_type TEXT NOT NULL,
    from_entity_id UUID NOT NULL,
    to_entity_type TEXT NOT NULL,
    to_entity_id UUID NOT NULL,
    relationship_type TEXT NOT NULL,
    metadata JSONB DEFAULT '{}',
    created_at TIMESTAMP WITH TIME ZONE DEFAULT now(),
    created_by UUID REFERENCES users(id),
    organization_id UUID NOT NULL REFERENCES organizations(id),

    -- Ensure unique relationships
    UNIQUE(from_entity_type, from_entity_id, to_entity_type, to_entity_id, relationship_type)
);

-- Indexes for fast lookups
CREATE INDEX idx_entity_relationships_from ON entity_relationships(from_entity_type, from_entity_id);
CREATE INDEX idx_entity_relationships_to ON entity_relationships(to_entity_type, to_entity_id);
CREATE INDEX idx_entity_relationships_org ON entity_relationships(organization_id);
```

This single table can represent any relationship:

- User → Workflow (created\_by)
- Workflow → Cluster (provisions)
- User → Organization (member\_of)
- Cluster → User (accessible\_by)
- Workflow → Workflow (depends\_on)

## Automatic Relationship Creation

Instead of manually creating relationships, we integrate relationship tracking into business operations:

```go
type RelationshipService struct {
    db *sql.DB
}

type Relationship struct {
    FromEntityType   string            `json:"from_entity_type"`
    FromEntityID     string            `json:"from_entity_id"`
    ToEntityType     string            `json:"to_entity_type"`
    ToEntityID       string            `json:"to_entity_id"`
    RelationshipType string            `json:"relationship_type"`
    Metadata         map[string]interface{} `json:"metadata"`
    OrganizationID   string            `json:"organization_id"`
}

func (r *RelationshipService) CreateRelationship(ctx context.Context, rel Relationship) error {
    query := `
        INSERT INTO entity_relationships
        (from_entity_type, from_entity_id, to_entity_type, to_entity_id,
         relationship_type, metadata, organization_id, created_by)
        VALUES ($1, $2, $3, $4, $5, $6, $7, $8)
        ON CONFLICT (from_entity_type, from_entity_id, to_entity_type, to_entity_id, relationship_type)
        DO NOTHING`

    _, err := r.db.ExecContext(ctx, query,
        rel.FromEntityType, rel.FromEntityID, rel.ToEntityType, rel.ToEntityID,
        rel.RelationshipType, rel.Metadata, rel.OrganizationID, getUserID(ctx))
    return err
}

// Integrate into business operations
func (w *WorkflowService) CreateWorkflow(ctx context.Context, userID, orgID string, spec WorkflowSpec) (*Workflow, error) {
    workflow := &Workflow{
        ID:             generateID(),
        Name:           spec.Name,
        Status:         "pending",
    }

    if err := w.relationships.CreateRelationship(ctx, Relationship{
        FromEntityType:   "user",
        FromEntityID:     userID,
        ToEntityType:     "workflow",
        ToEntityID:       workflow.ID,
        RelationshipType: "created",
        OrganizationID:   orgID,
    }); err != nil {
        return nil, err
    }

    for _, clusterSpec := range spec.Clusters {
        cluster := w.clusters.CreateCluster(ctx, workflow.ID, clusterSpec)

        _ = w.relationships.CreateRelationship(ctx, Relationship{
            FromEntityType:   "workflow",
            FromEntityID:     workflow.ID,
            ToEntityType:     "cluster",
            ToEntityID:       cluster.ID,
            RelationshipType: "provisions",
            Metadata: map[string]interface{}{
                "provider": clusterSpec.Provider,
                "region":   clusterSpec.Region,
            },
            OrganizationID: orgID,
        })

        _ = w.relationships.CreateRelationship(ctx, Relationship{
            FromEntityType:   "cluster",
            FromEntityID:     cluster.ID,
            ToEntityType:     "user",
            ToEntityID:       userID,
            RelationshipType: "accessible_by",
            OrganizationID:   orgID,
        })
    }

    return workflow, nil
}
```

## Querying Relationships

We built a query service that can fetch relationships in any direction:

```go
type QueryOptions struct {
    RelationshipTypes []string
    MaxDepth          int
    Direction         Direction // Outgoing, Incoming, Both
}

func (r *RelationshipService) GetRelationships(ctx context.Context, entityType, entityID string, opts QueryOptions) ([]Relationship, error) {
    query := `
        WITH RECURSIVE relationship_tree AS (
            SELECT *, 1 AS depth
            FROM entity_relationships
            WHERE from_entity_type = $1 AND from_entity_id = $2

            UNION ALL

            SELECT r.*, tree.depth + 1
            FROM entity_relationships r
            JOIN relationship_tree tree ON r.from_entity_type = tree.to_entity_type
                AND r.from_entity_id = tree.to_entity_id
            WHERE tree.depth < $3
        )
        SELECT * FROM relationship_tree
    `

    rows, err := r.db.QueryContext(ctx, query, entityType, entityID, opts.MaxDepth)
    if err != nil {
        return nil, err
    }

    var relationships []Relationship
    for rows.Next() {
        var rel Relationship
        if err := rows.Scan(&rel.FromEntityType, &rel.FromEntityID, &rel.ToEntityType, &rel.ToEntityID,
            &rel.RelationshipType, &rel.Metadata, &rel.OrganizationID); err != nil {
            return nil, err
        }
        relationships = append(relationships, rel)
    }

    return relationships, nil
}
```

## Surfacing Relationships in React

With a generic relationship API, we can build dynamic UI components:

```tsx
import { useEntityRelationships } from "@/hooks/use-entity-relationships";

const RelationshipPanel = ({ entityType, entityId }) => {
  const { relationships, loading } = useEntityRelationships(entityType, entityId);

  if (loading) {
    return (
      <div className="relationship-panel">
        <Skeleton variant="rectangular" height={200} />
      </div>
    );
  }

  const groupedRelationships = groupByRelationshipType(relationships);

  return (
    <div className="relationship-panel">
      {Object.entries(groupedRelationships).map(([type, rels]) => (
        <section key={type}>
          <header>
            <h3>{formatRelationshipType(type)}</h3>
            <Badge>{rels.length}</Badge>
          </header>
          <ul>
            {rels.map((rel) => (
              <li key={rel.to_entity_id}>
                <EntityLink type={rel.to_entity_type} id={rel.to_entity_id} />
                {rel.metadata && <MetadataPill metadata={rel.metadata} />}
              </li>
            ))}
          </ul>
        </section>
      ))}
    </div>
  );
};
```

## Relationship Graph Visualization

We can construct relationship graphs using our generic data:

```tsx
interface Relationship {
  from_entity_type: string;
  from_entity_id: string;
  to_entity_type: string;
  to_entity_id: string;
  relationship_type: string;
  metadata: Record<string, unknown>;
}

function buildRelationshipGraph(relationships: Relationship[], maxDepth = 2) {
  const nodes = new Map<string, GraphNode>();
  const edges: GraphEdge[] = [];

  relationships.forEach((rel) => {
    const fromKey = `${rel.from_entity_type}:${rel.from_entity_id}`;
    const toKey = `${rel.to_entity_type}:${rel.to_entity_id}`;

    if (!nodes.has(fromKey)) {
      nodes.set(fromKey, {
        id: fromKey,
        label: `${rel.from_entity_type}\n${rel.from_entity_id}`,
        type: rel.from_entity_type,
      });
    }

    if (!nodes.has(toKey)) {
      nodes.set(toKey, {
        id: toKey,
        label: `${rel.to_entity_type}\n${rel.to_entity_id}`,
        type: rel.to_entity_type,
      });
    }

    edges.push({
      id: `${fromKey}->${toKey}`,
      source: fromKey,
      target: toKey,
      label: formatRelationshipType(rel.relationship_type),
      metadata: rel.metadata,
    });
  });

  return { nodes: Array.from(nodes.values()), edges };
}
```

## Rich Relationship Metadata in UI

We built relationship cards that render metadata intelligently:

```tsx
const RelationshipCard = ({ relationship }: { relationship: Relationship }) => {
  const metadata = relationship.metadata;

  return (
    <div className="relationship-card">
      <header>
        <EntityAvatar type={relationship.to_entity_type} id={relationship.to_entity_id} />
        <div>
          <h4>{relationship.to_entity_type}</h4>
          <p>{formatRelationshipType(relationship.relationship_type)}</p>
        </div>
      </header>
      <dl>
        {metadata.provider && (
          <div>
            <dt>Provider</dt>
            <dd>{metadata.provider}</dd>
          </div>
        )}
        {metadata.region && (
          <div>
            <dt>Region</dt>
            <dd>{metadata.region}</dd>
          </div>
        )}
        {metadata.cost_per_hour && (
          <div>
            <dt>Cost</dt>
            <dd>${metadata.cost_per_hour}/hour</dd>
          </div>
        )}
      </dl>
    </div>
  );
};
```

## Generic Relationship Viewer Component

The relationship viewer adapts to any entity type:

```tsx
interface EntityRelationshipViewerProps {
  entityType: string;
  entityId: string;
  relationshipTypes?: string[];
  maxDepth?: number;
}

const EntityRelationshipViewer: React.FC<EntityRelationshipViewerProps> = ({
  entityType,
  entityId,
  relationshipTypes = ['created', 'owns', 'provisions'],
  maxDepth = 2
}) => {
  const { relationships, loading } = useEntityRelationships(entityType, entityId, relationshipTypes);

  if (loading) return <Loading />;

  const graph = buildRelationshipGraph(relationships, maxDepth);

  return (
    <div className="relationship-viewer">
      <RelationshipGraph graph={graph} />
    </div>
  );
};
```

## The Results

- **Complete Visibility**: Users can see all connections—workflows they've created, clusters provisioned, access granted.
- **Zero Entity-Specific Code**: Adding new entity types requires no relationship-specific logic.
- **Rich Metadata**: Relationships carry context—roles, cost, provider, and more.
- **Automatic Consistency**: Relationships stay accurate because they're created during business operations.
- **Generic UI Components**: One relationship viewer serves any entity.

## Lessons Learned

- **Build generically from the start**. Retrofitting generic relationships is painful.
- **Metadata matters**. Relationships without context are just edges.
- **Automate relationship creation**. Manual systems drift out of sync.
- **Keep a single source of truth**. One relationship table is easier to query and govern.
- **Let UI follow data**. Good data structures make rich interfaces possible.

## When to Use This Pattern

This pattern shines when:

- Multiple entity types need to connect in flexible ways
- Relationships are many-to-many or deeply nested
- You want to visualize entity connections
- Rich metadata about relationships is valuable
- You operate a multi-tenant system

It's overkill for simple parent-child relationships or tiny systems, but for complex platforms, a generic relationship system pays dividends quickly. It turns isolated data islands into an interconnected map your users can actually understand.