Table Relationship Registry (TRR)

1. Purpose

A single JSON registry describing:

• Primary keys
• Foreign keys
• Relationship cardinalities
• Join direction
• Optional semantics (lookup tables, bridge tables, event streams, etc.)

This becomes the source of truth for:

• Automatic diagrams in Docusaurus
• Automated validation
• Automated lineage graphs
• Automated engine/module dependency checks

⸻

2. Recommended JSON Structure

A single file:

config/system/table_relationships.json

Structure:

[
  {
    "table": "fact_product_footprint",
    "pk": ["footprint_id"],

    "relations": [
      {
        "type": "foreign_key",
        "fk_columns": ["country_iso"],
        "ref_table": "dim_countries",
        "ref_columns": ["iso_cc_id"],
        "cardinality": "many-to-one",
        "role": "country_lookup",
        "status": "stable",
        "description": "Maps product footprint to its production country"
      },
      {
        "type": "foreign_key",
        "fk_columns": ["nace_code"],
        "ref_table": "dim_nace",
        "ref_columns": ["nace_id"],
        "cardinality": "many-to-one",
        "role": "industry_lookup",
        "status": "stable",
        "description": "Industry classification for the product"
      }
    ]
  },

  {
    "table": "altd_event",
    "pk": ["audit_id"],

    "relations": [
      {
        "type": "foreign_key",
        "fk_columns": ["actor_id"],
        "ref_table": "dim_actor",
        "ref_columns": ["actor_uid"],
        "cardinality": "many-to-one",
        "role": "actor_lookup",
        "status": "stable",
        "description": "Links audit events to actor registry"
      }
    ]
  }
]

⸻

3. Explanation of Fields

Required:

Field	Meaning
table	Table name
pk	Primary key(s)
relations	List of relationships

In each relationship:

Field	Meaning
type	"foreign_key" or "dependency"
fk_columns	Columns in this table
ref_table	Target table
ref_columns	Columns in reference table
cardinality	"one-to-one", "one-to-many", "many-to-one", "many-to-many"
role	Human-friendly name (“country_lookup”, “industry_lookup”)
status	"stable", "deprecated", etc.
description	Human explanation

⸻

4. How It Integrates Into Docusaurus

We add a new MDX component:

docusaurus/src/components/TableRelations.js

It will auto-render

• 🗺 ER-style relationship table per data table
• 🔗 List of referenced tables
• ↩️ Back-references (a table showing all tables linking to it)
• 📈 Future optional: Auto-generate diagrams using Mermaid.js

⸻

5. How MCP/Engines Use It

This gives us:

✔ Lineage Graphs “What upstream tables does SEM depend on?”

✔ Dependency Validation If a schema changes, we know which engines break.

✔ Automated Join Builders Perfect for micro-engines like PEF-ME, ZHIF, SEM:

[!TIP] “To extract the full record for dim_nace, join using nace_code → nace_id.”

✔ Future: Zero-config ETL Graphs ETLs can self-build from the registry.