# Artifact 5: __r Traversal Inventory

Generated from 19 migrated flows in the `nexuscrm` test database, 2026-04-17.

## Summary

- **7 of 19 flows** contain `__r` relationship references
- **16 unique `__r` paths** across all flows
- **Max depth**: 3 (multiple `__r` hops in a single expression)
- **Paths inside loops**: 0 (WARN: none detected)
- **12 flows** have zero `__r` references

## __r Traversal Inventory

| # | Flow | Trigger Object | __r Path | Depth | In Loop? | Est. Queries |
|---|------|---------------|----------|-------|----------|-------------|
| 1 | Order_Start_Date_End_Date | Order_Product__c | `$Record.Order__r.Overall_Start_Date__c` | 1 | No | 1 |
| 2 | Order_Start_Date_End_Date | Order_Product__c | `$Record.Order__r.Overall_End_Date__c` | 1 | No | 0 (cached) |
| 3 | Collections_Region_PingCAP_Contracting_Entity_Marketplace | Invoice_Collection__c | `$Record.Account__r.Region__c` | 1 | No | 1 |
| 4 | Collections_Region_PingCAP_Contracting_Entity_Marketplace | Invoice_Collection__c | `$Record.Order__r.PingCAP_Contracting_Entity_EMEA_only__c` (in formula) | 1 | No | 1 |
| 5 | Collections_Region_PingCAP_Contracting_Entity_Marketplace | Invoice_Collection__c | `$Record.Account__r.PingCAP_Contracting_Entity__c` (in formula) | 1 | No | 0 (cached) |
| 6 | Collections | Invoice_Collection__c | `$Record.Order__r.ContractId` | 1 | No | 1 |
| 7 | Collections | Invoice_Collection__c | `$Record.Order__r.Owned_DM__c` | 1 | No | 0 (cached) |
| 8 | Collections | Invoice_Collection__c | `$Record.Order__r.BillToContactId` | 1 | No | 0 (cached) |
| 9 | Collections | Invoice_Collection__c | `$Record.Order__r.Email__c` | 1 | No | 0 (cached) |
| 10 | Collections | Invoice_Collection__c | `$Record.Order__r.AccountId` | 1 | No | 0 (cached) |
| 11 | Order_Expiration_Notice_90 | Invoice_Collection__c | `$Record.Order__r` (bare relationship check) | 1 | No | 1 |
| 12 | Order_Expiration_Notice_90 | Invoice_Collection__c | `$Record.Order__r.Order_Expiration_Alert__c` | 1 | No | 0 (cached) |
| 13 | Order_Expiration_Notice_90 | Invoice_Collection__c | `$Record.Order__r.X90_Alert_Arranged_Date__c` | 1 | No | 0 (cached) |
| 14 | Order_Expiration_Notice_90 | Invoice_Collection__c | `$Record.Order__r.X60_Alert_Arranged_Date__c` | 1 | No | 0 (cached) |
| 15 | Order_Expiration_Notice_90 | Invoice_Collection__c | `$Record.Order__r.X30_Alert_Arranged_Date__c` | 1 | No | 0 (cached) |
| 16 | Collections_Account_ID | Invoice_Collection__c | `$Record.Order__r.AccountId` | 1 | No | 1 |
| 17 | Collection_Partner_Name | Invoice_Collection__c | `$Record.Order__r.Partner_Name__c` | 1 | No | 1 |

### Multi-hop Expressions (Depth > 1)

These appear **only inside formula expressions** (not as direct field references). The formula normalizer must resolve them:

| # | Flow | Full Expression | __r Hops | Context |
|---|------|----------------|----------|---------|
| 1 | Collections_Region_PingCAP_Contracting_Entity_Marketplace | `IF({!$Record.Order__r.PingCAP_Contracting_Entity_EMEA_only__c}="",TEXT({!$Record.Account__r.PingCAP_Contracting_Entity__c}),{!$Record.Order__r.PingCAP_Contracting_Entity_EMEA_only__c})` | 3 total (Order__r x2, Account__r x1) | Formula: conditional entity lookup |

Note: The depth-3 count reflects 3 separate `__r` traversals within a single expression, not 3-hop chains. Each individual traversal is depth 1 (one hop from the trigger record).

## Flows with No __r References (12 of 19)

| Flow | Trigger Object | Reason |
|------|---------------|--------|
| Order_Account_Name | Order | Reads from `$Record.Account.Name` (dot traversal, not `__r`) |
| Order_Partner_Name | Order | Direct field assignment |
| Order_PingCAP_RevRec_Entity | Order | Formula uses `$Record.Account.PingCAP_Contracting_Entity__c` (dot, not `__r`) |
| Contract_PingCAP_Contracting_Entity | Contract | Direct `$Record` fields only |
| Account_PingCAP_Contracting_Entity | Account | Direct `$Record` fields only |
| Collections_Cash_Collected | Invoice_Collection__c | Direct `$Record` fields only |
| Collections_Invoice_Status_marketplace | Invoice_Collection__c | Formula is string literal `"Not Applicable"` |
| Invoice_Notice | Invoice_Collection__c | Direct `$Record` fields only |
| Order_Expiration_Alert_7 | (Scheduled) | No record trigger; screen/scheduled flow |
| Order_Expiration_Alert_15 | (Scheduled) | Same |
| Order_Expiration_Alert_30 | (Scheduled) | Same |
| Order_Expiration_Alert_60 | (Scheduled) | Same |
| Order_Expiration_Alert_90 | (Scheduled) | Same |

## Relationship Traversal Patterns

### By Relationship Name

| Relationship | Flows Using It | Fields Accessed |
|-------------|---------------|----------------|
| `Order__r` | 5 flows | ContractId, Owned_DM__c, BillToContactId, Email__c, AccountId, Partner_Name__c, PingCAP_Contracting_Entity_EMEA_only__c, Order_Expiration_Alert__c, X90/X60/X30_Alert_Arranged_Date__c |
| `Account__r` | 1 flow | Region__c, PingCAP_Contracting_Entity__c |
| `Account` (dot) | 2 flows | Name, PingCAP_Contracting_Entity__c |

### By Trigger Object

| Trigger Object | Flows with __r | Unique __r Paths |
|---------------|---------------|-----------------|
| Order_Product__c | 1 (Order_Start_Date_End_Date) | 2 (`Order__r.*`) |
| Invoice_Collection__c | 5 (Collections*, Order_Expiration_Notice_90, Collection_Partner_Name, Collections_Account_ID) | 13 (`Order__r.*`, `Account__r.*`) |
| Order | 0 | 0 (uses dot traversal `Account.Name` instead) |
| Contract | 0 | 0 |
| Account | 0 | 0 |

## Query Cost Analysis

- **Total estimated queries per flow execution**: max 2 (one per unique relationship, cached within interview)
- **No `__r` paths inside loops**: query amplification risk is zero for these 19 flows
- **Caching strategy**: All `__r` traversals from the same base object (e.g., `$Record.Order__r.*`) resolve to a single query; subsequent field accesses hit the cache

### Estimated Query Budget per Flow

| Flow | Unique Relationships | Est. Queries |
|------|---------------------|-------------|
| Order_Start_Date_End_Date | 1 (Order__r) | 1 |
| Collections_Region_PingCAP_Contracting_Entity_Marketplace | 2 (Order__r, Account__r) | 2 |
| Collections | 1 (Order__r) | 1 |
| Order_Expiration_Notice_90 | 1 (Order__r) | 1 |
| Collections_Account_ID | 1 (Order__r) | 1 |
| Collection_Partner_Name | 1 (Order__r) | 1 |

## Engine Support Status

The flow executor's `__r` traversal relies on a `Fetcher` callback in `formula.Context` (see `backend/pkg/formula/engine.go:61`). The fetcher is invoked when the expression engine encounters a member access on a relationship field.

**Current gap**: The `{!$Record.Order__r.Field}` merge-field syntax in formula expressions (rows 4-5 in the inventory) will fail for the same reason as Artifact 3 -- the `{!...}` wrapper is not stripped. Once the `{!}` stripping fix from Artifact 3 is applied, these traversals should resolve correctly via the existing fetcher mechanism.