Chapter 9: The Transitive FK Join Engine

Generic Multi-Hop Relationship Traversal for Document Merge

Volume 5 — Building Domain Template Packages for DataPublisher

Chapter Overview

In Chapter 7 we covered testing your domain template package and in Chapter 8 we walked through packaging and marketplace submission. This chapter steps inside the engine — specifically the join layer that makes multi-table relationships available to Word templates at merge time.

Understanding this chapter will help you:

• Design domain-config.json FK relationships with confidence
• Know exactly which fields will be available in your templates and why
• Debug a template that isn't receiving expected data
• Understand why the engine requires no application code changes to support new domains

Table Relationships: A Quick Primer

Before explaining how the engine works, it helps to understand the data problem it is solving.

Relational Data 101

Real-world information rarely lives in a single flat table. Consider a homeschool co-op. You have families, and each family has multiple students, and each student is enrolled in multiple classes. If you tried to store all of this in one spreadsheet row per family, you would end up with columns like Student1FirstName, Student2FirstName, Student3FirstName — and the moment a family has a fourth student, your whole structure breaks.

The relational model solves this by splitting information across multiple tables, each focused on one kind of thing:

Tables are connected by foreign keys (FKs). A foreign key is a column in one table that contains the ID of a row in another table. In the school-coop domain:

students.FamilyID  →  families.FamilyID

This means every row in students has a FamilyID column, and that value matches a FamilyID in the families table. The arrow points from the column to the table it references.

One-Hop vs. Multi-Hop Lookups

A one-hop relationship is a direct FK link between two tables. To find all students belonging to family FAM001:

SELECT * FROM students WHERE FamilyID = 'FAM001'

One lookup. Simple.

A multi-hop relationship requires following two or more FK links in sequence. To find all classes a family's students are enrolled in:

students  ──(FamilyID)──▶  families     ← start here (family FAM001)
enrollments ──(StudentID)──▶  students  ← find all students for FAM001
classes ──(ClassID)──▶  enrollments     ← find all classes for those students

You cannot answer this question in one lookup. You must: 1. Find all students where FamilyID = 'FAM001' 2. Collect all their StudentID values 3. Find all enrollments where StudentID is in that collection 4. Look up each class from those enrollments

In database terms this is a multi-table JOIN. The challenge for DataPublisher is performing this join automatically, at runtime, for any domain — based only on the FK declarations in domain-config.json.

Why This Matters for Template Design

A DataPublisher template merges CSV data into a Word document. Each row in the primary table becomes one generated document. Related tables are joined in so the template can access fields and loop through rows.

For a Family Summary document where the primary table is families, you want to write:

{{ForEach:enrollments}}
  <<ClassName>> — <<ClassDay>>
{{EndForEach}}

But enrollments has no direct FK to families. The path is:

families  ──(FamilyID)──▶  students  ──(StudentID)──▶  enrollments

Two hops. The engine must follow the FK graph to find enrollments, connect them to the right family, enrich them with class data, and make them available inside the ForEach loop. That is exactly what the Transitive FK Join Engine does — and it works for any domain, based entirely on the FK relationships you declare in domain-config.json.

Configuration: What You Actually Need to Change

Before diving into the engine internals, here is the good news: enabling multi-hop joins requires only one configuration change.

The Only Required Change: relatedTables in domain-config.json

Each document type in domain-config.json has a relatedTables array. List every table you want accessible in the template — whether it has a direct FK to the primary table or not. The engine resolves the path:

{
  "id": 5,
  "name": "Family Invoice",
  "primaryTable": "families",
  "relatedTables": [
    "family_financials",
    "payments",
    "students",
    "enrollments",
    "classes",
    "academic_terms"
  ]
}

enrollments, payments, and classes have no direct FK to families. Listing them is sufficient — the engine finds the paths automatically.

What You Do NOT Need to Change

Sidecar JSON Files (Template-XX.json)

Each Word template has a companion .json file in word-templates/. These are metadata files, not join configuration. They tell the system which CSVs to offer for e-sign workflows and template previews:

{
  "id": 22,
  "name": "Personalized Family Schedule",
  "csvFiles": [
    "families.csv",
    "students.csv",
    "enrollments.csv",
    "classes.csv",
    "teachers.csv"
  ],
  "filename": "Template-22-Family-Schedule.docx"
}

E-sign–ready templates also include optional fields to identify which merge fields hold the signer's name and email address:

{
  "id": 21,
  "name": "Annual Family Enrollment Agreement",
  "csvFiles": ["esign-enrollment-agreement.csv"],
  "esignReady": true,
  "esignNameField": "ParentName",
  "esignEmailField": "ParentEmail"
}

The sidecar file has no knowledge of FK relationships. The join engine is driven entirely by domain-config.json.

How the Engine Works

The FK Graph

Every domain-config.json is, in graph terms, a directed relationship graph. Each table is a node, and each FK declaration is a directed edge pointing from the table that holds the FK column toward the table it references:

{
  "name": "enrollments",
  "foreignKeys": [
    { "field": "StudentID", "references": "students.StudentID" },
    { "field": "ClassID",   "references": "classes.ClassID" }
  ]
}

Reading all FK declarations, the school-coop domain graph looks like this:

                    ┌─────────────────────┐
                    │         coops        │
                    └──────────┬──────────┘
                               │ ▲
              families.CoopID  │ │ academic_terms.CoopID
                               ▼ │
                    ┌──────────────────────┐
                    │       families        │
                    └──────────────────────┘
                         ▲           ▲
        students.FamilyID│           │family_financials.FamilyID
                         │           │
                    ┌─────────┐  ┌──────────────────┐
                    │ students│  │ family_financials │
                    └────┬────┘  └────────┬─────────┘
                         │                │
  enrollments.StudentID  │                │ payments.FinancialID
                         ▼                ▼
                    ┌──────────┐     ┌─────────┐
                    │enrollments│    │ payments │
                    └────┬─────┘    └──────────┘
                         │
       enrollments.ClassID│
                         ▼
                    ┌─────────┐
                    │ classes  │──▶ academic_terms (classes.TermID)
                    └─────────┘

When generating a "Family Summary" document with families as the primary table, the engine needs to make data from every node in this graph available to the template.

The Three-Step Pipeline

All join processing happens inside the performJoins function, called once per template generation request. It processes data in three ordered steps.

STEP 0 — Dotted Notation

Every field on the primary record is cloned with a tablename.fieldname prefix:

FamilyID   →   also stored as   families.FamilyID
LastName   →   also stored as   families.LastName
CoopID     →   also stored as   families.CoopID

Both names coexist on the record. This makes it unambiguous which table a field came from, and enables field-lookup patterns used in later steps.

STEP 1 — Forward Joins for the Primary Table's Own FKs

Every FK declared on the primary table is followed immediately as a many-to-one join. For families, that means following families.CoopID → coops.CoopID. The engine finds the matching coop record and merges all its fields into the family record:

record.CoopName       = "Riverside Coop"
record["coops.CoopName"] = "Riverside Coop"

This cascade continues two levels deep. If coops had its own FKs, those would be followed too. After STEP 1, the record has fields from every table in the primary table's outward FK chain.

Key insight for template design: After STEP 1, fields from "transit" tables — tables you did not list in relatedTables but which the primary table's FK chain passes through — are already on the record. You do not need to list coops in relatedTables if you just need <<CoopName>> in your template. You only need it if you want {{ForEach:coops}} loops.

Context Joins: Automatic Secondary Lookups

Some tables declare a contextJoin property in domain-config.json. This tells the engine that immediately after forward-joining a table, it should fetch one additional related record and merge its fields into the root record — no application code changes needed.

{
  "name": "coops",
  "primaryKey": "CoopID",
  "contextJoin": {
    "table": "academic_terms",
    "matchField": "CoopID",
    "activeField": "IsActive",
    "sortField": "EndDate"
  }
}

In the school-coop domain, whenever families.CoopID → coops is resolved in STEP 1, the engine also:

1. Loads academic_terms rows where CoopID matches the coop's primary key
2. Prefers the row where IsActive === '1' (the active term)
3. Falls back to the row with the latest EndDate if no active row exists
4. Merges all fields onto the root record as academic_terms.FieldName

The result: every family record automatically carries <<academic_terms.TermName>>, <<academic_terms.AcademicYear>>, and so on — driven entirely by config, with no hardcoded table names in the service.

Template design note: contextJoin adds scalar fields representing one best-match record. If you also want a {{ForEach:academic_terms}} loop to iterate all terms, list academic_terms in relatedTables so Pass 2 Strategy A additionally embeds the full array. The scalar and array forms coexist on the same record.

STEP 2 — Two-Pass Transitive Join Engine

This step processes every table listed in relatedTables. It uses two passes so that the results of the first pass can inform decisions in the second.

Pass 1: Direct FK Joins

For each table in relatedTables, the engine calls findJoinKey to check whether there is a single FK edge between the primary table and the related table. If found, the join proceeds:

• Many-to-one (primary FK → related): merge related fields into each record
• One-to-many (related FK → primary): embed as an array record['tableName'] = [...]

Every table that becomes an array is tracked in embeddedArrayTables. This set is the foundation of Pass 2.

Tables with no direct FK are added to deferredForTransitive. These are handed to Pass 2.

Pass 1 result for families primary:

  students         → embedded as array  ✓  (students.FamilyID → families.FamilyID)
  family_financials → embedded as array  ✓  (family_financials.FamilyID → families.FamilyID)
  parents          → embedded as array  ✓  (parents.FamilyID → families.FamilyID)
  coops            → fields merged in   ✓  (families.CoopID → coops.CoopID)

  Deferred: enrollments, classes, academic_terms, payments

Pass 2: Transitive Joins — Three Strategies

For each deferred table, the engine tries three strategies in order.

Strategy A — Available Field Match

Question: Does the deferred table have a FK whose target field is already on the record?

Example: academic_terms

academic_terms.CoopID → coops.CoopID

After STEP 1, CoopID is on the record (from families.CoopID). Strategy A sees this match and embeds all matching rows as an array:

record['academic_terms'] = [all academic_terms where CoopID matches this family's CoopID]

This works because STEP 1 enriched the record with fields from tables further along the FK chain, making them available as join keys even though there's no direct FK to academic_terms.

Note: In the school-coop domain, coops also declares a contextJoin pointing to academic_terms (see the Context Joins subsection above). That STEP 1 context join adds scalar fields for the single best term. Strategy A then adds the full array when academic_terms is listed in relatedTables. Both coexist — scalar fields for the common single-value template case, the array for {{ForEach:academic_terms}} loops.

Strategy B — Nested Array Embed and Flatten

Question: Does the deferred table have a FK that points to a table already in embeddedArrayTables?

Example: enrollments

enrollments.StudentID → students.StudentID

students is in embeddedArrayTables (placed there in Pass 1). Strategy B:

1. For each family record, iterates record['students']
2. For each student, finds matching enrollments via enrollment.StudentID === student.StudentID
3. Enriches each enrollment item with its own FK joins (2 levels deep): ClassID → classes, then classes.TermID → academic_terms
4. Writes enriched enrollments to student['enrollments'] (nested position)
5. Also writes ALL enrollments across ALL students to record['enrollments'] (flat root position)

The dual-write is intentional. Template authors can use either:

{{ForEach:enrollments}}          ← flat, all enrollments for this family
  <<ClassName>> — <<TermName>>
{{EndForEach}}

or:

{{ForEach:students}}
  <<FirstName>> ({{Count:enrollments}} classes)
  {{ForEach:enrollments}}
    <<ClassName>>
  {{EndForEach}}
{{EndForEach}}

Strategy C — Enriched as FK Target (No Action Needed)

Question: Is the deferred table the target of a FK from a table already embedded as an array?

Example: classes

enrollments.ClassID → classes.ClassID

enrollments is now in embeddedArrayTables (placed there by Strategy B above). During Strategy B's enrichment step, every enrollment item's ClassID was followed and class data was merged in. classes data is already embedded inside each enrollment item.

Strategy C simply logs this and skips — no additional work needed.

Field Promotion

After each array is embedded — both in Pass 1 and Pass 2 — the engine runs a promotion pass on that array. Promotion copies selected fields from the first (or "best") array item up to the root record, so template authors can write <<ParentFirstName>> directly in the document body instead of needing a {{ForEach:parents}} loop just to retrieve one value.

Declaring a promotionRule

Each table definition in domain-config.json may include a promotionRule object. Tables without one are silently skipped. There are no hardcoded promotion rules in the service code — every rule lives in config.

{
  "name": "parents",
  "promotionRule": {
    "activeField": "IsPrimary",
    "fieldsToPromote": ["FirstName", "LastName", "Email", "Phone"],
    "aliases": [
      { "alias": "ParentFirstName", "source": "FirstName" },
      { "alias": "ParentLastName",  "source": "LastName" }
    ]
  }
}

Rule Properties

What Promotion Looks Like

Before promotion, a family record with two parents has:

record['parents'] = [
  { FirstName: 'Jane', LastName: 'Smith', IsPrimary: '1', Email: 'jane@...' },
  { FirstName: 'John', LastName: 'Smith', IsPrimary: '0', Email: 'john@...' }
]

After promotion using the rule above (picking the item where IsPrimary === '1'):

record.FirstName       = 'Jane'       // fieldsToPromote
record.LastName        = 'Smith'      // fieldsToPromote
record.Email           = 'jane@...'   // fieldsToPromote
record.ParentFirstName = 'Jane'       // alias
record.ParentLastName  = 'Smith'      // alias

The Word template can now use <<ParentFirstName>> in the document body — the most common use case — without any loop.

Design note: The aliases property exists for readability. <<FirstName>> is ambiguous when a document has both a student and a parent. <<ParentFirstName>> and <<StudentFirstName>> are self-documenting and avoid accidental field collisions.

The Full Execution Order (Family Summary Template)

Field Promotion

After each array is embedded, the engine calls promoteArrayFieldsToRecord. This function surfaces commonly-needed scalar values from the "best" array item directly onto the root record, so templates can use <<TermName>> without needing a ForEach loop.

Selection Logic

Each table type has a promotion rule defining: - activeField: which field indicates the "preferred" item (IsActive, IsPrimary, etc.) - fieldsToPromote: the list of field names to copy to the root record

If multiple items exist, the item where activeField = '1' is preferred. If none match, the first item is used.

Dotted-Field Fallback

When array items are enriched via nested joins, some fields end up stored as academic_terms.TermName rather than TermName. The promotion loop checks for this:

Trying to promote 'TermName':
  itemToPromote['TermName'] → undefined
  Fallback: look for any key ending in '.TermName'
  Found 'academic_terms.TermName' → promote it

This means family_financials items correctly yield TermName, AcademicYear, etc. at the root record level, even though those fields were joined in from academic_terms.

Template Design Patterns

Pattern 1: Scalar Summary (All Fields at Root)

Use for single-record summaries where you just need values, no loops:

Family: <<LastName>>, <<FirstName>>
Coop: <<CoopName>>
Current Term: <<TermName>> (<<AcademicYear>>)
Balance Due: <<AmountDue>>
Primary Contact: <<ParentFirstName>> <<ParentLastName>> — <<ParentPhone>>

All of these are scalar fields available at the root after promotion. No loops required.

Pattern 2: ForEach with Joined Fields

Use for lists where each item has data from multiple tables:

{{ForEach:enrollments}}
  Class: <<classes.ClassName>> (<<ClassDay>> at <<ClassTime>>)
  Teacher: <<TeacherFirstName>> <<TeacherLastName>>
  Term: <<TermName>>
  Grade Level: <<GradeLevel>>
{{EndForEach}}

Fields inside the loop come from: the enrollment itself, plus classes (joined via Strategy B's L1 FK enrichment), plus academic_terms (joined via Strategy B's L2 enrichment).

Pattern 3: Count Fields

Total Students: {{Count:students}}
Total Enrolled Classes: {{Count:enrollments}}
Payments on File: {{Count:payments}}

Counts work on any array table regardless of how many join hops were needed to produce it.

Pattern 4: Nested Loops (Students → Their Enrollments)

{{ForEach:students}}
  <<FirstName>> <<LastName>> (Grade: <<GradeLevel>>)
  Classes this term:
  {{ForEach:enrollments}}
    — <<ClassName>> (<<ClassDay>>)
  {{EndForEach}}
{{EndForEach}}

The inner {{ForEach:enrollments}} inside a {{ForEach:students}} block uses student.enrollments, the nested array written by Strategy B.

Defining Your Own Domain: FK Design Checklist

When authoring domain-config.json for a new domain, the transitive engine handles multi-hop relationships automatically. Keep these rules in mind:

1. One FK declaration covers both directions.
You declare students.FamilyID → families.FamilyID. The engine reads this and knows that from a families primary, students is a one-to-many table. It also knows that from a students primary, families is a many-to-one table. You do not need a separate "reverse FK" entry.

2. Transitive paths are resolved by the engine, not the config.
You do not need to add a convenience column like enrollments.FamilyID. Declaring enrollments.StudentID → students.StudentID is sufficient. The engine sees that students is reachable from families and uses Strategy B automatically.

3. List every table you want accessible in relatedTables.
The engine will find the path. But if a table is not in relatedTables, it will not be processed even if the path exists. Being explicit is better than relying on automatic discovery.

4. Tables pulled in as FK enrichment targets can be omitted from relatedTables.
If classes data will always be available inside each enrollment item (because enrollments.ClassID → classes.ClassID is enriched during the enrollment join), and your template only accesses class data inside ForEach enrollment loops, you do not need to list classes in relatedTables separately. List it if you want a standalone {{ForEach:classes}} loop at the document root.

5. Field promotion for new table types requires a code addition.
Scalar field promotion (surfacing <<TermName>> at the root without a ForEach loop) uses a lookup table in the engine keyed on table name. Built-in tables are already registered. For a completely new domain table, you would add a promotion rule entry. ForEach loops are unaffected — they work for any array regardless.

Summary

The Transitive FK Join Engine makes multi-hop table relationships available to Word templates without any application code changes. Its design reflects a principle running throughout the DataPublisher architecture: domain knowledge belongs in configuration files, not in application code.

The engine reads the FK graph from domain-config.json, executes a three-step pipeline (STEP 0 dotted notation, STEP 1 primary FK forward joins, STEP 2 two-pass transitive joins), and produces an enriched record with all related data available as either scalar fields or iterable arrays.

Three strategies cover all practical multi-hop patterns: - Strategy A handles indirect lookup via shared transit fields - Strategy B handles child tables nested within already-embedded arrays, with dual-write so templates can use both flat and nested ForEach loops - Strategy C recognizes tables that are automatically enriched as FK targets inside existing array joins

When you design a domain template package, the only configuration required is an accurate domain-config.json with the correct FK declarations and relatedTables lists. The engine handles the rest.

Next: Chapter 10 — Performance Optimization for Large Domain Data Sets

Key Terms