@memberjunction/external-change-detection

Detects and reconciles changes made to MemberJunction entities by external systems, third-party integrations, or direct database modifications that bypass the MemberJunction application layer.

Overview

The @memberjunction/external-change-detection package detects when records have been modified outside of MemberJunction, generates detailed change reports with field-level differences, and can replay those changes through the MemberJunction entity system to trigger all business logic, validations, and audit tracking.

graph TD
    A["ExternalChangeDetectorEngine<br/>(Singleton)"] --> B["Create Detection"]
    A --> C["Update Detection"]
    A --> D["Delete Detection"]

    B --> E["Records without<br/>Create in RecordChanges"]
    C --> F["__mj_UpdatedAt newer than<br/>latest RecordChange"]
    D --> G["RecordChanges entries<br/>with no matching record"]

    A --> H["ReplayChanges"]
    H --> I["Load Entity via MJ"]
    I --> J["Save/Delete with ReplayOnly"]
    J --> K["Business Logic<br/>Audit Trail<br/>Triggers"]

    style A fill:#2d6a9f,stroke:#1a4971,color:#fff
    style B fill:#2d8659,stroke:#1a5c3a,color:#fff
    style C fill:#2d8659,stroke:#1a5c3a,color:#fff
    style D fill:#2d8659,stroke:#1a5c3a,color:#fff
    style H fill:#7c5295,stroke:#563a6b,color:#fff
    style K fill:#b8762f,stroke:#8a5722,color:#fff

Key Features

• Detect external changes to entity records (creates, updates, and deletes)
• Compare current state with previous snapshots stored in RecordChanges
• Generate detailed change reports with field-level differences
• Support for composite primary keys
• Configurable change detection with parallel processing
• Ability to replay/apply detected changes through MemberJunction
• Built-in optimization for batch loading records
• Track change replay runs for audit purposes

Installation

npm install @memberjunction/external-change-detection

Dependencies

This package relies on the following MemberJunction packages:

• @memberjunction/core - Core MemberJunction functionality
• @memberjunction/core-entities - Entity definitions
• @memberjunction/global - Global utilities
• @memberjunction/sqlserver-dataprovider - SQL Server data provider

Basic Usage

import { ExternalChangeDetectorEngine } from '@memberjunction/external-change-detection';
import { Metadata } from '@memberjunction/core';

async function detectAndReplayChanges() {
  // Get the engine instance
  const detector = ExternalChangeDetectorEngine.Instance;
  
  // Configure the engine (loads eligible entities)
  await detector.Config();
  
  // Get a specific entity
  const md = new Metadata();
  const entityInfo = md.Entities.find(e => e.Name === 'Customer');
  
  // Detect changes for the entity
  const result = await detector.DetectChangesForEntity(entityInfo);
  
  if (result.Success) {
    console.log(`Detected ${result.Changes.length} changes`);
    
    // Replay the changes if any were found
    if (result.Changes.length > 0) {
      const replaySuccess = await detector.ReplayChanges(result.Changes);
      console.log(`Replay ${replaySuccess ? 'succeeded' : 'failed'}`);
    }
  }
}

API Documentation

ExternalChangeDetectorEngine

The main class for detecting and replaying external changes. This is a singleton that extends BaseEngine.

Configuration

// Configure the engine - this loads eligible entities
await ExternalChangeDetectorEngine.Instance.Config();

Properties

• EligibleEntities: EntityInfo[] - List of entities eligible for change detection
• IneligibleEntities: string[] - List of entity names to exclude from detection

Methods

DetectChangesForEntity

Detects changes for a single entity.

const result = await detector.DetectChangesForEntity(entityInfo);

Returns a ChangeDetectionResult with:

• Success: boolean
• ErrorMessage: string (if failed)
• Changes: ChangeDetectionItem[]

DetectChangesForEntities

Detects changes for multiple entities in parallel.

const entities = [entity1, entity2, entity3];
const result = await detector.DetectChangesForEntities(entities);

DetectChangesForAllEligibleEntities

Detects changes for all eligible entities.

const result = await detector.DetectChangesForAllEligibleEntities();

ReplayChanges

Replays detected changes through MemberJunction to trigger all business logic.

const success = await detector.ReplayChanges(changes, batchSize);

Parameters:

• changes: ChangeDetectionItem[] - Changes to replay
• batchSize: number (optional, default: 20) - Number of concurrent replays

Data Types

ChangeDetectionItem

Represents a single detected change:

class ChangeDetectionItem {
  Entity: EntityInfo;              // The entity that changed
  PrimaryKey: CompositeKey;        // Primary key of the record
  Type: 'Create' | 'Update' | 'Delete';  // Type of change
  ChangedAt: Date;                 // When the change occurred
  Changes: FieldChange[];          // Field-level changes (for updates)
  LatestRecord?: BaseEntity;       // Current record data (for creates/updates)
  LegacyKey?: boolean;             // For backward compatibility
  LegacyKeyValue?: string;         // Legacy single-value key
}

FieldChange

Represents a change to a single field:

class FieldChange {
  FieldName: string;
  OldValue: any;
  NewValue: any;
}

ChangeDetectionResult

Result of a change detection operation:

class ChangeDetectionResult {
  Success: boolean;
  ErrorMessage?: string;
  Changes: ChangeDetectionItem[];
}

Eligible Entities

For an entity to be eligible for external change detection, all of the following must be true:

1. TrackRecordChanges is set to 1 (entity has audit logging enabled)
2. DetectExternalChanges is set to 1 (entity has opted into external change scanning)
3. The entity has __mj_UpdatedAt and __mj_CreatedAt fields (automatically added by CodeGen)
4. The entity is not in the IneligibleEntities list

The eligible entities are determined by the database view vwEntitiesWithExternalChangeTracking.

Opt-In Model

External change detection is opt-in by default (DetectExternalChanges = 0). Most entities — especially __mj schema metadata tables — are managed exclusively by SQL migrations and CodeGen. Scanning them produces false positives because their records are intentionally created outside the MJ Save() flow.

To enable external change detection for specific entities, create a metadata JSON file in metadata/entities/ and push it with mj sync. For example, create metadata/entities/.external-change-detection.json:

[
  {
    "fields": { "Name": "Contacts", "DetectExternalChanges": true },
    "primaryKey": { "ID": "@lookup:MJ: Entities.Name=Contacts" }
  },
  {
    "fields": { "Name": "Companies", "DetectExternalChanges": true },
    "primaryKey": { "ID": "@lookup:MJ: Entities.Name=Companies" }
  },
  {
    "fields": { "Name": "Invoices", "DetectExternalChanges": true },
    "primaryKey": { "ID": "@lookup:MJ: Entities.Name=Invoices" }
  }
]

Then push from the repository root:

npx mj sync push --dir=metadata --include="entities"

Alternatively, you can toggle DetectExternalChanges directly in the Entity form within MJ Explorer.

Good Candidates

• User-facing data that may be modified by third-party integrations (CRM sync, ETL tools)
• Entities loaded via bulk import that bypass the MJ application layer
• Tables with direct SQL edits from administrative tools or scripts

Bad Candidates

• __mj schema tables — managed by migrations/CodeGen, no external changes by design
• System configuration entities — changes should go through the MJ API
• High-volume logging tables — detection scans would be expensive with no benefit

How It Works

Change Detection Process

1. Create Detection: Finds records in the entity table that don't have a corresponding 'Create' entry in RecordChanges
2. Update Detection: Compares __mj_UpdatedAt timestamps between entity records and their latest RecordChanges entry
3. Delete Detection: Finds RecordChanges entries where the corresponding entity record no longer exists

Change Replay Process

1. Creates a new RecordChangeReplayRun to track the replay session
2. For each change:
   • Creates a new RecordChange record with status 'Pending'
   • Loads the entity using MemberJunction's entity system
   • Calls Save() or Delete() with the ReplayOnly option
   • Updates the RecordChange status to 'Complete' or 'Error'
3. Updates the RecordChangeReplayRun status when finished

Examples

Detect Changes for Specific Entities

const detector = ExternalChangeDetectorEngine.Instance;
await detector.Config();

// Get specific entities
const md = new Metadata();
const customerEntity = md.Entities.find(e => e.Name === 'Customer');
const orderEntity = md.Entities.find(e => e.Name === 'Order');

// Detect changes for both entities
const result = await detector.DetectChangesForEntities([customerEntity, orderEntity]);

console.log(`Found ${result.Changes.length} total changes`);

Process Changes with Error Handling

const detector = ExternalChangeDetectorEngine.Instance;
await detector.Config();

const result = await detector.DetectChangesForAllEligibleEntities();

if (result.Success && result.Changes.length > 0) {
  console.log(`Processing ${result.Changes.length} changes...`);
  
  // Group changes by entity for reporting
  const changesByEntity = result.Changes.reduce((acc, change) => {
    const entityName = change.Entity.Name;
    if (!acc[entityName]) acc[entityName] = [];
    acc[entityName].push(change);
    return acc;
  }, {});
  
  // Log summary
  Object.entries(changesByEntity).forEach(([entityName, changes]) => {
    console.log(`${entityName}: ${changes.length} changes`);
  });
  
  // Replay with smaller batch size for critical entities
  const success = await detector.ReplayChanges(result.Changes, 10);
  
  if (!success) {
    console.error('Some changes failed to replay');
  }
}

Scheduled Change Detection Job

import { ExternalChangeDetectorEngine } from '@memberjunction/external-change-detection';
import { UserInfo } from '@memberjunction/core';

async function runScheduledChangeDetection(contextUser: UserInfo) {
  const detector = ExternalChangeDetectorEngine.Instance;
  
  try {
    // Configure with specific user context
    await detector.Config(false, contextUser);
    
    // Detect all changes
    const detectResult = await detector.DetectChangesForAllEligibleEntities();
    
    if (!detectResult.Success) {
      throw new Error(`Detection failed: ${detectResult.ErrorMessage}`);
    }
    
    console.log(`Detection complete: ${detectResult.Changes.length} changes found`);
    
    // Replay changes if any were found
    if (detectResult.Changes.length > 0) {
      const replaySuccess = await detector.ReplayChanges(detectResult.Changes);
      
      if (!replaySuccess) {
        console.error('Some changes failed during replay');
        // Could implement retry logic or notifications here
      }
    }
  } catch (error) {
    console.error('Change detection job failed:', error);
    // Implement alerting/logging as needed
  }
}

Performance Considerations

1. Batch Processing: The engine processes multiple entities in parallel and loads records in batches
2. Efficient Queries: Uses optimized SQL queries with proper joins and filters
3. Composite Key Support: Handles both simple and composite primary keys efficiently
4. Configurable Batch Size: Adjust the replay batch size based on your system's capacity

Best Practices

• Run change detection during off-peak hours
• Monitor the RecordChangeReplayRuns table for failed runs
• Set appropriate batch sizes for replay based on your data volume
• Consider entity-specific scheduling for high-volume entities
• Implement proper error handling and alerting

Database Requirements

This package requires the following database objects:

• __mj.vwEntitiesWithExternalChangeTracking - View listing eligible entities
• __mj.vwRecordChanges - View of record change history
• __mj.RecordChange - Table storing change records
• __mj.RecordChangeReplayRun - Table tracking replay runs

License

ISC