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.
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:
-- 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:
// 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:
-- 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:
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:
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:
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:
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:
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:
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.