lotus-analyze-reference-dependencies

What this skill does

Works with reference field mapping, dependency graphs, circular reference detection, and orphan handling.
# Analyze Lotus Notes Reference Database Dependencies

## Table of Contents

**Quick Start** → [What Is This](#purpose) | [When to Use](#when-to-use) | [Simple Example](#examples)

**How to Implement** → [Step-by-Step](#instructions) | [Expected Outcomes](#quick-start)

**Reference** → [Requirements](#requirements) | [Related Skills](#see-also)

## Purpose

Lotus Notes applications frequently depend on shared reference databases—master lists like Suppliers, Catalogs, Employees, Locations, etc. These reference databases are typically read-only lookups that multiple applications depend on. Understanding these dependencies is critical for migration planning: you must migrate reference databases first and handle orphaned references. This skill guides you through mapping these complex dependency relationships.

## When to Use

Use this skill when you need to:

- **Analyze shared reference databases** - Identify master lists (suppliers, catalogs, employees) used by multiple applications
- **Track bidirectional relationships** - Map how applications reference each other and detect circular dependencies
- **Handle orphaned references** - Find and remediate broken references before or during migration
- **Plan database synchronization** - Design proper migration sequencing to avoid breaking dependencies
- **Map cross-database relationships** - Document lookup patterns, reference fields, and data dependencies
- **Validate reference integrity** - Detect missing references and assess data quality

This skill is critical for determining migration order and preventing broken references during Lotus Notes migrations.

## Quick Start

To analyze reference database dependencies:

1. Create inventory of all reference databases in your ecosystem
2. Identify all applications that reference them
3. Map specific reference fields in each application
4. Document reference integrity constraints and orphan handling
5. Create dependency graph showing which applications depend on what references
6. Plan migration order based on dependency chains
7. Design orphan handling and fallback strategies

## Instructions

### Step 1: Identify Reference Databases

Locate all shared reference databases in your Lotus Notes environment:

**Search for databases with patterns:**
- Names like "Lookup", "Master", "Catalog", "Configuration"
- Read-only databases accessed by multiple applications
- Databases replicated across multiple servers
- Domino Directory (Notes.nsf)—the system reference database
- Custom reference databases specific to your organization

**For each reference database, document:**
- Database name and title
- Physical file location(s) and replicas
- Primary documents forms (Supplier, Product, Employee, etc.)
- Which applications reference it
- Update frequency (static vs. regularly updated)
- Access restrictions and who can modify it

Example inventory:

```
Reference Database: Supplier Master
  File: suppliers.nsf
  Location: Domino Server, also replicated to local workstations
  Forms:
    - Supplier (main form)
    - SupplierCategory
    - SupplierContact
  Applications that use it:
    - Order Management (B&O system)
    - Purchasing
    - Finance/Billing
  Update frequency: Weekly (Monday 2 PM)
  Access: Everyone can read, only procurement team can edit

Reference Database: Employee Directory
  File: employees.nsf
  Location: Domino Server (HR maintains)
  Forms:
    - Employee (main form)
    - Department
    - Manager
  Applications that use it:
    - Order Management
    - Project Tracking
    - Expense Reports
  Update frequency: Daily (automatic sync from HR system)
  Access: Everyone can read, HR team can edit
```

### Step 2: Map Reference Fields in Applications

Identify all fields that reference external databases:

**For each application, document:**

1. **Which forms use references:**
   ```
   Form: Order
     Field: Supplier
       Type: Reference
       Points to: Supplier Master.nsf, Supplier form
       Is multi-value: No
       Key field: SupplierID
       Display field: SupplierName

     Field: Approvers
       Type: Reference
       Points to: Employee Directory.nsf, Employee form
       Is multi-value: Yes (can select multiple)
       Key field: EmployeeID
       Display field: EmployeeName
   ```

2. **How are references used:**
   - In views (filtering by supplier name)
   - In computed fields (pulling supplier information)
   - In validation (ensuring supplier exists)
   - In lookups (formula looking up supplier data)

3. **What happens when reference is missing:**
   - Does the document fail to save?
   - Is there a fallback value?
   - Is there a validation formula that enforces it?

Example complete mapping:

```
Application: Order Management (B&O System)

Form: Order
  Reference Fields:
    1. Supplier
       Points to: Supplier Master.nsf → Supplier form
       Key: SupplierID
       Display: SupplierName
       Required: Yes
       Multi-value: No
       Used in: View filtering, computed field for discount lookup

    2. Items (embedded table)
       Contains: Product (reference)
       Points to: Catalog.nsf → Product form
       Key: ProductCode
       Display: ProductName
       Required: Yes (per row)
       Multi-value: No
       Used in: Price lookup, inventory check

    3. Approvers
       Points to: Employee Directory.nsf → Employee form
       Key: EmployeeID
       Display: EmployeeName
       Required: No
       Multi-value: Yes
       Used in: Approval workflow routing

View: "Orders by Supplier"
  Uses field: Supplier (grouping)
  Filter: Shows only documents with valid Supplier reference

Computed Fields:
  SupplierDiscount
    Formula: @DbLookup("NotesSQL"; ""; "suppliers.nsf"; Supplier; "DiscountRate")
    Depends on: Supplier field, Supplier Master database
    Fallback: 0 if lookup fails
```

### Step 3: Create Dependency Graph

Visualize how databases and applications relate:

**Text representation of dependency graph:**

```
Reference Databases (bottom tier - depended upon):
  ├─ Employee Directory (employees.nsf)
  ├─ Supplier Master (suppliers.nsf)
  └─ Catalog (products.nsf)

Applications (top tier - depend on references):
  ├─ Order Management
  │   └─ Depends on: Suppliers, Products, Employees
  ├─ Purchasing
  │   └─ Depends on: Suppliers, Employees
  ├─ Finance/Billing
  │   └─ Depends on: Suppliers
  └─ Project Tracking
      └─ Depends on: Employees
```

**Example with cardinality:**

```
Order Management
    ├─ Supplier reference (many-to-one)
    │   Points to: Supplier Master.nsf
    │   ~100K orders reference ~500 suppliers
    │   Risk: HighHigh (missing supplier breaks entire order)
    │
    ├─ Product reference (many-to-many via embedded table)
    │   Points to: Catalog.nsf
    │   ~100K orders × ~15 items avg = ~1.5M item references
    │   Risk: High (missing product breaks line item)
    │
    └─ Approver reference (many-to-one or one-to-many)
        Points to: Employee Directory.nsf
        ~100K orders with ~1-3 approvers each
        Risk: Medium (missing approver affects approval workflow, not order)
```

### Step 4: Analyze Reference Integrity

Assess how well references are maintained:

**For each reference field, investigate:**

1. **Is reference validation enforced?**
   ```
   Supplier field in Order form
     Validation formula: @NotEmpty(Supplier)
     Valid values: @DbCommand("NotesSQL"; ... list from Supplier Master)
     Result: ENFORCED - can't save order without valid supplier
   ```

2. **What happens with obsolete references?**
   - If a Supplier is marked inactive, can orders still reference it?
   - Can you change a Supplier's key field (SupplierID)?
   - Are there any historical orders that reference deleted suppliers?

3. **Orphan references:**
   - Count documents with broken/missing references
   - Document why references might be orphaned:
     ```
     Order with orp