MemberJunction Metadata Sync

A library for synchronizing MemberJunction database metadata with local file system representations. This library is integrated into the MemberJunction CLI (mj) and is accessed through mj sync commands. It enables developers and non-technical users to manage MJ metadata using their preferred editors and version control systems while maintaining the database as the source of truth.

Installation

MetadataSync is included with the MemberJunction CLI. Install the CLI globally:

npm install -g @memberjunction/cli

Then use the sync commands:

mj sync --help

Purpose

MemberJunction is a powerful metadata-driven system where configuration, business logic, AI prompts, templates, and more are stored as metadata in the database. This approach provides tremendous flexibility and runtime configurability, but it can create friction in modern development workflows.

Why This Tool Matters

For Developers:

• Full IDE Support: Edit complex prompts and templates with syntax highlighting, IntelliSense, and all your favorite editor features
• Version Control: Track every change with Git - see diffs, blame, history, and collaborate through pull requests
• Branch-based Development: Work on features in isolation, test changes, and merge when ready
• CI/CD Integration: Automatically deploy metadata changes as code moves through environments
• Bulk Operations: Use familiar command-line tools (grep, sed, find) to make sweeping changes
• Offline Development: Work on metadata without database connectivity

For Non-Technical Users:

• Familiar Tools: Edit prompts in Word, Notepad++, or any text editor
• No Database Access Needed: IT can set up sync, users just edit files
• Folder Organization: Intuitive file/folder structure instead of database IDs
• Easy Sharing: Send prompt files via email or shared drives
• Simple Backups: Copy/paste folders for personal backups

For Organizations:

• Migration Path: Metadata flows naturally from dev → staging → production with code
• Compliance: Full audit trail through version control
• Collaboration: Multiple team members can work on different metadata simultaneously
• Disaster Recovery: File-based backups complement database backups
• Cross-System Sync: Export from one MJ instance, import to another

The Best of Both Worlds

This tool preserves the power of MJ's metadata-driven architecture while adding the convenience of file-based workflows. The database remains the source of truth for runtime operations, while files become the medium for creation, editing, and deployment.

Overview

The Metadata Sync tool bridges the gap between database-stored metadata and file-based workflows by:

• Pulling metadata entities from database to JSON files with external file support
• Pushing local file changes back to the database
• Supporting embedded collections for related entities
• Enabling version control for all MJ metadata through Git
• Supporting CI/CD workflows for metadata deployment
• Providing a familiar file-based editing experience

Key Features

Hybrid File Storage

• JSON files: Store structured metadata for entities
• External files: Store large text fields (prompts, templates, etc.) in appropriate formats (.md, .html, .sql)
• File references: Use @file:filename.ext to link external files from JSON

Embedded Collections

• Related Entities: Store related records as arrays within parent JSON files
• Hierarchical References: Use @parent: and @root: to reference parent/root entity fields
• Automatic Metadata: Related entities maintain their own primaryKey and sync metadata
• Nested Support: Support for multiple levels of nested relationships

Synchronization Operations

• Pull: Download metadata from database to local files
  • Optionally pull related entities based on configuration
  • Filter support for selective pulling
• Push: Upload local file changes to database
  • Process embedded collections automatically
  • Verbose mode (-v) for detailed output
  • Directory filtering with --include and --exclude options
• Status: Show what would change without making modifications
  • Directory filtering with --include and --exclude options

Directory Filtering

• Selective Processing: Use --include or --exclude to filter which entity directories are processed
• Pattern Support: Supports glob patterns like ai-*, *-test, etc.
• Mutually Exclusive: Cannot use both --include and --exclude together
• Use Cases:
  • Speed up development by excluding large/slow directories
  • Focus on specific entities during debugging
  • Create specialized sync workflows for different teams

Development Workflow Integration

• Watch mode for automatic syncing during development
• Dry-run mode to preview changes
• CI/CD mode for automated deployments
• Integration with existing mj.config.cjs configuration

Supported Entities

The tool works with any MemberJunction entity - both core system entities and user-created entities. Each entity type can have its own directory structure, file naming conventions, and related entity configurations.

Important Limitation: Database-Reflected Metadata

This tool should NOT be used to modify metadata that is reflected from the underlying database catalog. Examples include:

• Entity field data types
• Column lengths/precision
• Primary key definitions
• Foreign key relationships
• Table/column existence

These properties are designed to flow from the database catalog up into MJ metadata, not the other way around. Attempting to modify these via file sync could create inconsistencies between the metadata and actual database schema.

The tool is intended for managing business-level metadata such as:

• Descriptions and documentation
• Display names and user-facing text
• Categories and groupings
• Custom properties and settings
• AI prompts, templates, and other content
• Permissions and security settings
• Any other data that is not reflected up from the underlying system database catalogs

For more information about how CodeGen reflects system-level data from the database into the MJ metadata layer, see the CodeGen documentation.

Creating Error-Free Entity Files

Quick Start Checklist

Before creating entity JSON files, follow this checklist to avoid common mistakes:

✅ 1. Find the Entity Definition

• Open packages/MJCoreEntities/src/generated/entity_subclasses.ts or packages/GeneratedEntities/src/generated/entity_subclasses.ts
• Search for class [EntityName]Entity (e.g., class TemplateEntity)
• Review JSDoc comments and property definitions to identify required vs optional fields

✅ 2. Check Required Fields

• Look for JSDoc comments with @required annotations
• Fields without ? in TypeScript definitions are typically required
• Always include Name (almost always required)
• Always include UserID (use System User ID: ECAFCCEC-6A37-EF11-86D4-000D3A4E707E)

✅ 3. Validate Field Names

• Use exact field names from the BaseEntity class definition
• Field names are case-sensitive
• Don't assume fields exist (e.g., not all entities have Status)

✅ 4. Use Correct File Naming

• Configuration files (.mj-sync.json, .mj-folder.json) must start with dot
• Metadata files follow the filePattern in your .mj-sync.json
• Most common: "filePattern": "*.json" (matches any .json file)
• Alternative: "filePattern": ".*.json" (matches dot-prefixed .json files)

✅ 5. Set Up Directory Structure

• Create .mj-sync.json in the entity directory
• Use glob patterns: "filePattern": "*.json" (not regex: ".*.json")

Discovering Entity Structure

CRITICAL: Before creating entity files, you must understand the entity's field structure. Most errors occur because users are unfamiliar with the required fields, data types, and constraints.

Finding Entity Definitions

The approach depends on whether you're working inside or outside the MemberJunction monorepo:

Working Inside MJ Monorepo

Entity classes are located in:

• Core MJ Entities: packages/MJCoreEntities/src/generated/entity_subclasses.ts
  • System entities like Users, Roles, EntityFields, etc.
  • AI-related entities like AI Prompts, AI Models, etc.
• Custom Entities: packages/GeneratedEntities/src/generated/entity_subclasses.ts
  • Your application-specific entities
  • Business domain entities

Working Outside MJ Monorepo (In Your Own Project)

Entity classes are located in:

• Core MJ Entities: node_modules/@memberjunction/core-entities/dist/generated/entity_subclasses.js
  • Note: This is compiled JavaScript, but your IDE should provide IntelliSense
  • For TypeScript definitions: node_modules/@memberjunction/core-entities/dist/generated/entity_subclasses.d.ts
• Custom Entities: Your project's generated entities location (varies by project structure)
  • Common locations: src/generated/, packages/entities/, or similar
  • Look for files containing your custom entity classes

Best Practice: Use Your IDE's IntelliSense

Recommended approach for all scenarios:

1. Import the entity class in your IDE:

   import { TemplateEntity } from '@memberjunction/core-entities';

2. Create an instance and explore with IntelliSense:

   const template = new TemplateEntity();
   // Type "template." and let your IDE show available properties

3. Check the class definition (F12 or "Go to Definition") to see:

   • JSDoc comments with field descriptions
   • Required vs optional fields
   • Field types and validation rules
   • Relationships and constraints

How to Find Required Fields

1. Use IDE IntelliSense (Recommended):

   • Import the entity class
   • Create an instance: const entity = new TemplateEntity();
   • Use "Go to Definition" (F12) to see the BaseEntity class
   • Look for JSDoc comments and field definitions

2. Examine the BaseEntity Class:

   • Find the entity class (e.g., class TemplateEntity)
   • Look at property declarations with JSDoc comments
   • Check for required vs optional field annotations
   • Review any validation methods or constraints

3. Runtime Metadata Discovery:

   import { Metadata } from '@memberjunction/core';
      
   const md = new Metadata();
   const entityInfo = md.EntityByName('Templates');
   console.log('Required fields:', entityInfo.Fields.filter(f => !f.AllowsNull));

Example: Templates Entity Structure

// BaseEntity class (accessible via IDE IntelliSense)
export class TemplateEntity extends BaseEntity {
  /**
   * Primary key - auto-generated GUID
   */
  ID: string;
  
  /**
   * Template name - REQUIRED
   * @required
   */
  Name: string;
  
  /**
   * Template description - optional
   */
  Description?: string;
  
  /**
   * User who created this template - REQUIRED
   * Must be a valid User ID
   * @required
   * @foreignKey Users.ID
   */
  UserID: string;
  
  /**
   * Category for organizing templates - optional
   * @foreignKey TemplateCategories.ID
   */
  CategoryID?: string;
  
  // Note: Status field may not exist on all entities!
}

Common Required Fields Pattern

Most MJ entities follow these patterns:

Always Required:

• ID - Primary key (GUID) - auto-generated if not provided
• Name - Human-readable name
• UserID - Creator/owner (use System User: ECAFCCEC-6A37-EF11-86D4-000D3A4E707E)

Often Required:

• Description - Usually optional but recommended
• Foreign key fields ending in ID - Check if they have .optional()

Be Careful With:

• Status fields - Some entities have them, others don't
• Enum fields - Must match exact values from database
• DateTime fields - Use ISO format: 2024-01-15T10:30:00Z

Common Mistakes and Solutions

❌ Mistake 1: Using Non-Existent Fields

{
  "fields": {
    "Name": "My Template",
    "Status": "Active"  // ❌ Templates entity may not have Status field
  }
}

✅ Solution: Check the BaseEntity class first

// In entity_subclasses.ts - if you don't see Status here, don't use it
export class TemplateEntity extends BaseEntity {
  Name: string;  // Required
  Description?: string;  // Optional (note the ?)
  // No Status field defined
}

❌ Mistake 2: Missing Required Fields

{
  "fields": {
    "Name": "My Template"
    // ❌ Missing required UserID
  }
}

✅ Solution: Include all required fields

{
  "fields": {
    "Name": "My Template",
    "UserID": "ECAFCCEC-6A37-EF11-86D4-000D3A4E707E"
  }
}

❌ Mistake 3: Wrong File Pattern in .mj-sync.json

{
  "entity": "Templates",
  "filePattern": ".*.json"  // ❌ This is regex, not glob
}

✅ Solution: Use glob patterns

{
  "entity": "Templates", 
  "filePattern": "*.json"  // ✅ Correct glob pattern
}

❌ Mistake 4: Incorrect Data Types

{
  "fields": {
    "Name": "My Template",
    "CreatedAt": "2024-01-15",  // ❌ Wrong datetime format
    "Priority": "1"  // ❌ Should be number, not string
  }
}

✅ Solution: Use correct data types

{
  "fields": {
    "Name": "My Template",
    "CreatedAt": "2024-01-15T10:30:00Z",  // ✅ ISO format
    "Priority": 1  // ✅ Number type
  }
}

❌ Mistake 5: Files Not Being Detected

mydir/
├── .mj-sync.json (with "filePattern": "*.json")
├── template1.txt  // ❌ Wrong extension
└── .template2.json  // ❌ Dot prefix when pattern is "*.json"

✅ Solution: Match your filePattern

mydir/
├── .mj-sync.json (with "filePattern": "*.json")
├── template1.json  // ✅ Matches *.json pattern
└── template2.json  // ✅ Matches *.json pattern

Step-by-Step Entity File Creation

Step 1: Research the Entity

# Open in your IDE:
packages/MJCoreEntities/src/generated/entity_subclasses.ts

# Search for your entity class (Ctrl+F):
class TemplateEntity

# Note the required vs optional fields:
Name: string;           // Required (no ?)
UserID: string;         // Required (no ?)  
Description?: string;   // Optional (note the ?)

Step 2: Create Directory Structure

mkdir templates
cd templates

# Create entity config (dot-prefixed configuration file)
echo '{
  "entity": "Templates",
  "filePattern": "*.json"
}' > .mj-sync.json

Step 3: Create Your First Entity File

# Create metadata file (follows filePattern from .mj-sync.json)
echo '{
  "fields": {
    "Name": "My First Template",
    "Description": "A test template",
    "UserID": "ECAFCCEC-6A37-EF11-86D4-000D3A4E707E"
  }
}' > my-first-template.json

Step 4: Test and Validate

# Dry run to check for errors
mj sync push --dir="templates" --dry-run

# If successful, do actual push
mj sync push --dir="templates"

AI/LLM Guidelines

When using AI tools (like Claude, ChatGPT, etc.) to generate entity files:

🤖 For AI Assistants:

1. Always check entity definitions first - Never assume field names or requirements
2. Look up the exact BaseEntity class in the generated entity files
3. Use the System User ID (ECAFCCEC-6A37-EF11-86D4-000D3A4E707E) for UserID fields
4. Include only fields that exist in the entity definition
5. Use proper data types as defined in the BaseEntity class
6. Remember file naming rules:
   • Configuration files (.mj-sync.json) must have dot prefix
   • Metadata files follow the filePattern in .mj-sync.json
7. Use glob patterns in .mj-sync.json, not regex patterns

📝 Prompt Template for AI:

I need to create entity files for the [EntityName] entity in MemberJunction.

Please:
1. First, check the entity definition in packages/MJCoreEntities/src/generated/entity_subclasses.ts
2. Find the class [EntityName]Entity (e.g., class TemplateEntity)
3. Review JSDoc comments and property definitions to identify required vs optional fields
4. Create a .mj-sync.json file with correct glob pattern
5. Create sample metadata JSON files following the filePattern
6. Use UserID: "ECAFCCEC-6A37-EF11-86D4-000D3A4E707E" for required UserID fields
7. Follow the exact field names and data types from the BaseEntity class definition

CRITICAL: Configuration files (.mj-sync.json) must start with dot, but metadata files follow the filePattern specified in the configuration.

Understanding File Naming Rules

Configuration Files (Always Dot-Prefixed):

• ✅ .mj-sync.json - Entity configuration
• ✅ .mj-folder.json - Folder defaults
• ❌ mj-sync.json - Won't be recognized

Metadata Files (Follow filePattern): With "filePattern": "*.json":

• ✅ my-template.json - Will be processed
• ✅ greeting.json - Will be processed
• ❌ .my-template.json - Won't match pattern
• ❌ package.json - Will be ignored (add to ignore list if needed)

With "filePattern": ".*.json":

• ✅ .my-template.json - Will be processed
• ✅ .greeting.json - Will be processed
• ❌ my-template.json - Won't match pattern
• ❌ package.json - Won't match pattern

Troubleshooting Quick Reference

| Error Message | Cause | Solution | |---------------|-------|----------| | No entity directories found | Missing .mj-sync.json or wrong filePattern | Check .mj-sync.json exists and uses "*.json" | | Field 'X' does not exist on entity 'Y' | Using non-existent field | Check BaseEntity class in entity_subclasses.ts | | User ID cannot be null | Missing required UserID | Add "UserID": "ECAFCCEC-6A37-EF11-86D4-000D3A4E707E" | | Processing 0 records | Files don't match filePattern | Check files match pattern in .mj-sync.json | | Failed validation | Wrong data type or format | Check BaseEntity class for field types |

System User ID Reference

Always use this GUID for UserID fields:

ECAFCCEC-6A37-EF11-86D4-000D3A4E707E

This is the System User ID that should be used when creating entity records through the MetadataSync tool. Using any other ID or leaving it null will cause validation errors.

File Structure

The tool uses a hierarchical directory structure with cascading defaults:

• Each top-level directory represents an entity type
• .mj-sync.json files define entities and base defaults
• .mj-folder.json files define folder-specific defaults (optional)
• Only dot-prefixed JSON files (e.g., .prompt-template.json, .category.json) are treated as metadata records
• Regular JSON files without the dot prefix are ignored, allowing package.json and other config files to coexist
• External files (.md, .html, etc.) are referenced from the JSON files
• Defaults cascade down through the folder hierarchy

File Naming Convention

Metadata files must be prefixed with a dot (.) to be recognized by the sync tool. This convention:

• Clearly distinguishes metadata files from regular configuration files
• Allows package.json, tsconfig.json and other standard files to coexist without being processed
• Follows established patterns like .gitignore and .eslintrc.json

Examples:

• ✅ .greeting.json - Will be processed as metadata
• ✅ .customer-prompt.json - Will be processed as metadata
• ❌ greeting.json - Will be ignored
• ❌ package.json - Will be ignored

File Format Options

Single Record per File (Default)

Each JSON file contains one record:

{
  "fields": { ... },
  "relatedEntities": { ... }
}

Multiple Records per File

JSON files can contain arrays of records:

[
  {
    "fields": { ... },
    "relatedEntities": { ... }
  },
  {
    "fields": { ... },
    "relatedEntities": { ... }
  }
]

This is useful for:

• Grouping related records in a single file
• Reducing file clutter for entities with many small records
• Maintaining logical groupings while using @file: references for large content

Example Structure

metadata/
├── .mj-sync.json                    # Global sync configuration
├── ai-prompts/
│   ├── .mj-sync.json               # Defines entity: "AI Prompts"
│   ├── customer-service/
│   │   ├── .mj-folder.json         # Folder metadata (CategoryID, etc.)
│   │   ├── .greeting.json          # AI Prompt record with embedded models
│   │   ├── greeting.prompt.md      # Prompt content (referenced)
│   │   └── greeting.notes.md       # Notes field (referenced)
│   └── analytics/
│       ├── .mj-folder.json         # Folder metadata (CategoryID, etc.)
│       ├── .daily-report.json      # AI Prompt record
│       └── daily-report.prompt.md  # Prompt content (referenced)
├── templates/                       # Reusable JSON templates
│   ├── standard-prompt-settings.json # Common prompt configurations
│   ├── standard-ai-models.json     # Standard model configurations
│   ├── high-performance-models.json # High-power model configurations
│   └── customer-service-defaults.json # CS-specific defaults
└── template-entities/
    ├── .mj-sync.json               # Defines entity: "Templates"
    ├── email/
    │   ├── .mj-folder.json         # Folder metadata
    │   ├── .welcome.json           # Template record (dot-prefixed)
    │   └── welcome.template.html   # Template content (referenced)
    └── reports/
        ├── .mj-folder.json         # Folder metadata
        ├── .invoice.json           # Template record (dot-prefixed)
        └── invoice.template.html   # Template content (referenced)

JSON Metadata Format

Individual Record (e.g., ai-prompts/customer-service/.greeting.json)

{
  "fields": {
    "Name": "Customer Greeting",
    "Description": "Friendly customer service greeting",
    "TypeID": "@lookup:AI Prompt Types.Name=Chat",
    "CategoryID": "@lookup:AI Prompt Categories.Name=Customer Service",
    "Temperature": 0.7,
    "MaxTokens": 1000,
    "Prompt": "@file:greeting.prompt.md",
    "Notes": "@file:../shared/notes/greeting-notes.md",
    "SystemPrompt": "@url:https://raw.githubusercontent.com/company/prompts/main/system/customer-service.md"
  },
  "primaryKey": {
    "ID": "550e8400-e29b-41d4-a716-446655440000"
  },
  "sync": {
    "lastModified": "2024-01-15T10:30:00Z",
    "checksum": "sha256:abcd1234..."
  }
}

Record with Embedded Collections

{
  "fields": {
    "Name": "Customer Service Chat",
    "Description": "Main customer service prompt",
    "TypeID": "@lookup:AI Prompt Types.Name=Chat",
    "TemplateText": "@file:customer-service.md",
    "Status": "Active"
  },
  "relatedEntities": {
    "MJ: AI Prompt Models": [
      {
        "fields": {
          "PromptID": "@parent:ID",
          "ModelID": "@lookup:AI Models.Name=GPT 4.1",
          "VendorID": "@lookup:MJ: AI Vendors.Name=OpenAI",
          "Priority": 1,
          "Status": "Active"
        },
        "primaryKey": {
          "ID": "BFA2433E-F36B-1410-8DB0-00021F8B792E"
        },
        "sync": {
          "lastModified": "2025-06-07T17:18:31.687Z",
          "checksum": "a642ebea748cb1f99467af2a7e6f4ffd3649761be27453b988af973bed57f070"
        }
      },
      {
        "fields": {
          "PromptID": "@parent:ID",
          "ModelID": "@lookup:AI Models.Name=Claude 4 Sonnet",
          "Priority": 2,
          "Status": "Active"
        }
      }
    ]
  },
  "primaryKey": {
    "ID": "C2A1433E-F36B-1410-8DB0-00021F8B792E"
  },
  "sync": {
    "lastModified": "2025-06-07T17:18:31.698Z",
    "checksum": "7cbd241cbf0d67c068c1434e572a78c87bb31751cbfe7734bfd32f8cea17a2c9"
  }
}

Composite Primary Key Example

{
  "primaryKey": {
    "UserID": "550e8400-e29b-41d4-a716-446655440000",
    "RoleID": "660f9400-f39c-51e5-b827-557766551111"
  },
  "fields": {
    "GrantedAt": "2024-01-15T10:30:00Z",
    "GrantedBy": "@lookup:[email protected]",
    "ExpiresAt": "2025-01-15T10:30:00Z",
    "Notes": "@file:user-role-notes.md"
  },
  "sync": {
    "lastModified": "2024-01-15T10:30:00Z",
    "checksum": "sha256:abcd1234..."
  }
}

Default Value Inheritance

The tool implements a cascading inheritance system for field defaults, similar to CSS or OOP inheritance:

1. Entity-level defaults (in .mj-sync.json) - Base defaults for all records
2. Folder-level defaults (in .mj-folder.json) - Override/extend entity defaults
3. Nested folder defaults - Override/extend parent folder defaults
4. Record-level values - Override all inherited defaults

Inheritance Example

ai-prompts/.mj-sync.json         → Temperature: 0.7, MaxTokens: 1500
├── customer-service/.mj-folder.json → Temperature: 0.8 (overrides)
│   ├── greeting.json            → Uses Temperature: 0.8, MaxTokens: 1500
│   └── escalation/.mj-folder.json → Temperature: 0.6 (overrides again)
│       └── urgent.json          → Temperature: 0.9 (record override)

Final values for urgent.json:

• Temperature: 0.9 (from record)
• MaxTokens: 1500 (from entity defaults)
• All other fields from folder hierarchy

Special Conventions

The tool supports special reference types that can be used in ANY field that accepts text content. These references are processed during push/pull operations to handle external content, lookups, and environment-specific values.

Primary Key Handling

The tool automatically detects primary key fields from entity metadata:

• Single primary keys: Most common, stored as {"ID": "value"} or {"CustomKeyName": "value"}
• Composite primary keys: Multiple fields that together form the primary key
• Auto-detection: Tool reads entity metadata to determine primary key structure
• No hardcoding: Works with any primary key field name(s)

deleteRecord Directive

The tool now supports deleting records from the database using a special deleteRecord directive in JSON files. This allows you to remove obsolete records as part of your metadata sync workflow:

How to Delete a Record

1. Add a deleteRecord section to any record JSON file
2. Set delete: true to mark the record for deletion
3. Run mj sync push to execute the deletion
4. The tool will update the JSON with a deletion timestamp

Syntax

{
  "fields": {
    "Name": "Obsolete Prompt",
    "Description": "This prompt is no longer needed"
  },
  "primaryKey": {
    "ID": "550e8400-e29b-41d4-a716-446655440000"
  },
  "deleteRecord": {
    "delete": true
  }
}

After Deletion

After successfully deleting the record, the tool updates the JSON file:

{
  "fields": {
    "Name": "Obsolete Prompt",
    "Description": "This prompt is no longer needed"
  },
  "primaryKey": {
    "ID": "550e8400-e29b-41d4-a716-446655440000"
  },
  "deleteRecord": {
    "delete": true,
    "deletedAt": "2024-01-15T14:30:00.000Z"
  }
}

Important Notes

• Primary key required: You must specify the primaryKey to identify which record to delete
• One-time operation: Once deletedAt is set, the deletion won't be attempted again
• SQL logging: Delete operations are included in SQL logs when enabled
• Foreign key constraints: Deletions may fail if other records reference this record
• Dry-run support: Use --dry-run to preview what would be deleted
• Takes precedence: If deleteRecord is present, normal create/update operations are skipped

Use Cases

• Removing deprecated prompts, templates, or configurations
• Cleaning up test data
• Synchronizing deletions across environments
• Maintaining clean metadata through version control

@file: References

When a field value starts with @file:, the tool will:

1. Read content from the specified file for push operations
2. Write content to the specified file for pull operations
3. Track both files for change detection
4. For JSON files: Automatically process any @include directives within them

Examples:

• @file:greeting.prompt.md - File in same directory as JSON
• @file:./shared/common-prompt.md - Relative path
• @file:../templates/standard-header.md - Parent directory reference
• @file:spec.json - JSON file with @include directives (processed automatically)

@url: References

When a field value starts with @url:, the tool will:

1. Fetch content from the URL during push operations
2. Cache the content with appropriate headers
3. Support both HTTP(S) and file:// protocols

Examples:

• @url:https://example.com/prompts/greeting.md - Remote content
• @url:https://raw.githubusercontent.com/company/prompts/main/customer.md - GitHub raw content
• @url:file:///shared/network/drive/prompts/standard.md - Local file URL

@lookup: References (ENHANCED)

Enable entity relationships using human-readable values:

• Basic syntax: @lookup:EntityName.FieldName=Value
• Multi-field syntax: @lookup:EntityName.Field1=Value1&Field2=Value2
• Auto-create syntax: @lookup:EntityName.FieldName=Value?create
• With additional fields: @lookup:EntityName.FieldName=Value?create&Field2=Value2
• Deferred lookup syntax: @lookup:EntityName.FieldName=Value?allowDefer
• Combined flags: @lookup:EntityName.FieldName=Value?create&allowDefer

Examples:

• @lookup:AI Prompt Types.Name=Chat - Single field lookup, fails if not found
• @lookup:[email protected]&Department=Sales - Multi-field lookup for precise matching
• @lookup:AI Prompt Categories.Name=Examples?create - Creates if missing
• @lookup:AI Prompt Categories.Name=Examples?create&Description=Example prompts - Creates with description
• @lookup:Dashboards.Name=Data Explorer?allowDefer - Defers lookup if not found, retries at end of push

Multi-Field Lookups

When you need to match records based on multiple criteria, use the multi-field syntax:

{
  "CategoryID": "@lookup:AI Prompt Categories.Name=Actions&Status=Active",
  "ManagerID": "@lookup:[email protected]&Department=Engineering&Status=Active"
}

This ensures you get the exact record you want when multiple records might have the same value in a single field.

Deferred Lookups (?allowDefer)

The ?allowDefer flag enables handling of circular dependencies between entities during push operations. Use this when Entity A references Entity B and Entity B references Entity A - or any situation where a lookup target might not exist yet during initial processing.

How it works:

The flag is permission-based, not imperative. The lookup is always attempted first, and only deferred if it fails:

flowchart TD
    A["@lookup:Entity.Field=Value?allowDefer"] --> B{Try lookup now}
    B -->|Found| C[Return ID immediately]
    B -->|Not found| D{Has ?allowDefer?}
    D -->|Yes| E[Skip this field, continue processing]
    D -->|No| F[Fatal error - rollback transaction]
    E --> G[Save record without deferred field]
    G --> H[Queue record for re-processing]
    H --> I[Phase 2.5: Re-process entire record]
    I -->|Success| J[Update record with resolved field]
    I -->|Failure| F

When to use ?allowDefer:

• When Entity A references Entity B, and Entity B references Entity A
• When you're creating related records that need to reference each other
• When the lookup target might not exist yet during initial processing

Processing phases:

1. During the initial push phase, if a lookup with ?allowDefer fails (record not found), the field is skipped but the record still saves
2. The record IS saved during the initial pass (without the deferred field value), allowing other records to reference it
3. The record is queued for re-processing in Phase 2.5
4. After all other records are processed, deferred records are re-processed using the exact same logic
5. If retry succeeds, the record is updated with the resolved field; if it fails, an error is reported and the transaction rolls back

Example: Application ↔ Dashboard circular reference

The Applications entity can have DefaultNavItems (a JSON field) that contains nested references to Dashboards, while Dashboards have an ApplicationID that references Applications.

Since Applications are processed before Dashboards (alphabetical order), the Dashboard lookup in DefaultNavItems needs ?allowDefer:

// .data-explorer-application.json
{
  "fields": {
    "Name": "Data Explorer",
    "DefaultNavItems": [
      {
        "Label": "Explorer",
        "ResourceType": "Dashboard",
        "RecordID": "@lookup:Dashboards.Name=Data Explorer?allowDefer"
      }
    ]
  }
}

// .data-explorer-dashboard.json
// Note: No ?allowDefer needed - Applications are processed first
{
  "fields": {
    "Name": "Data Explorer",
    "ApplicationID": "@lookup:Applications.Name=Data Explorer"
  }
}

Processing order:

1. Applications are processed first (per directoryOrder in .mj-sync.json):
   • The Dashboard lookup fails (Dashboard doesn't exist yet)
   • Because ?allowDefer is set, the DefaultNavItems field is skipped
   • Application IS saved (without the DefaultNavItems value)
   • Application record is queued for re-processing
2. Dashboards are processed:
   • Dashboard references Application via ApplicationID - this lookup succeeds because Application was saved in step 1
   • Dashboard is created normally
3. Deferred records are re-processed (Phase 2.5):
   • The Application record is processed again using the exact same logic
   • The Dashboard lookup now succeeds since Dashboard exists in the database
   • Application is updated with the resolved DefaultNavItems field

Console output:

Processing Applications...
   ⏳ Deferring lookup for Applications.DefaultNavItems -> Dashboards
   📋 Queued Applications for deferred processing (record saved, some fields pending)
   ✓ Created: 1

Processing Dashboards...
   ✓ Created: 1

⏳ Processing 1 deferred record...
   ✓ Applications (ID=867CB743-...) - updated
   ✓ Resolved 1 deferred record (0 created, 1 updated)

Important: The ?allowDefer flag queues the entire record for re-processing, not just the failed field. This ensures the exact same processing logic is used on retry, including proper handling of nested lookups within JSON structures, @parent references, and all other field processing.

Combining flags: You can combine ?allowDefer with ?create:

"CategoryID": "@lookup:Categories.Name=New Category?create&allowDefer"

This means: "Look up the category, create if missing, and if the lookup still fails for some reason, defer it."

Important notes:

• Deferred records are processed before the final commit (Phase 2.5)
• If any deferred record fails on retry, the entire push transaction is rolled back
• Use sparingly - only for genuine circular dependencies
• The record must have a primaryKey defined in the metadata file

@parent: References

Reference fields from the immediate parent entity in embedded collections:

• @parent:ID - Get the parent's ID field
• @parent:Name - Get the parent's Name field
• Works with any field from the parent entity

@root: References

Reference fields from the root entity in nested structures:

• @root:ID - Get the root entity's ID
• @root:CategoryID - Get the root's CategoryID
• Useful for deeply nested relationships

@env: References

Support environment-specific values:

• @env:VARIABLE_NAME
• Useful for different environments (dev/staging/prod)

Automatic JSON Stringification with Reference Processing

When a field value is an array or object, the tool automatically:

1. Recursively processes all @lookup:, @file:, @parent:, @root: references inside the object
2. Converts to JSON string with pretty formatting (2-space indentation) for database storage
3. Maintains clean structure in source files while storing as strings in database

This is extremely powerful for JSON-typed fields like Configuration, Settings, Metadata, etc.

Basic Example

{
  "fields": {
    "Name": "My Entity",
    "Configuration": {
      "setting1": "value1",
      "setting2": {
        "nested": true,
        "items": [1, 2, 3]
      }
    },
    "Tags": ["tag1", "tag2", "tag3"]
  }
}

The Configuration and Tags fields will automatically be converted to JSON strings when pushed to the database.

Advanced Example: References Inside JSON Fields

This is the powerful part - you can use @lookup: and other references INSIDE object-typed fields:

{
  "fields": {
    "Name": "Agent Memory Manager Job",
    "CronExpression": "0 */15 * * * *",
    "Configuration": {
      "AgentID": "@lookup:AI Agents.Name=Memory Manager",
      "InitialMessage": "Analyze recent conversations",
      "Settings": {
        "MaxNotes": 5,
        "Strategy": "Relevant",
        "TargetAgentID": "@lookup:AI Agents.Name=Sage"
      }
    }
  }
}

When pushed to the database, this becomes:

{
  "Configuration": "{\"AgentID\":\"actual-uuid-here\",\"InitialMessage\":\"Analyze recent conversations\",\"Settings\":{\"MaxNotes\":5,\"Strategy\":\"Relevant\",\"TargetAgentID\":\"another-uuid-here\"}}"
}

Benefits:

• ✅ Human-readable: Use agent names, not UUIDs in your metadata
• ✅ Maintainable: Changes to entity names don't break references
• ✅ Type-safe: Structured objects in source, properly stringified for DB
• ✅ Nested support: References work at any depth in the object tree

Common Use Cases

Scheduled Job Configuration:

{
  "Configuration": {
    "AgentID": "@lookup:AI Agents.Name=Report Generator",
    "Schedule": "daily",
    "Recipients": "@lookup:[email protected]"
  }
}

Action Parameters:

{
  "DefaultParameters": {
    "TargetEntityID": "@lookup:Entities.Name=Customers",
    "TemplateID": "@lookup:Templates.Name=Welcome Email",
    "Settings": {
      "SendImmediate": true,
      "Priority": "High"
    }
  }
}

AI Configuration:

{
  "AIConfig": {
    "PreferredModelID": "@lookup:AI Models.Name=GPT 4.1",
    "FallbackModels": [
      "@lookup:AI Models.Name=Claude Sonnet 3.7",
      "@lookup:AI Models.Name=Gemini Pro"
    ],
    "Temperature": 0.7
  }
}

{@include} References in Files

Enable content composition within non-JSON files (like .md, .html, .txt) using JSDoc-style include syntax:

• Pattern: {@include path/to/file.ext}
• Supports relative paths from the containing file
• Recursive includes (includes within includes)
• Circular reference detection prevents infinite loops
• Works seamlessly with @file: references

How It Works

When a JSON metadata file uses @file: to reference an external file, the MetadataSync tool:

1. Loads the referenced file
2. Scans for {@include} patterns
3. Recursively resolves all includes
4. Returns the fully composed content

Example Usage

# My Prompt Template

## System Instructions
{@include ./shared/system-instructions.md}

## Context
{@include ../common/context-header.md}

## Task
Please analyze the following...

Complex Example with Nested Includes

Directory structure:

prompts/
├── customer-service/
│   ├── greeting.json          # Uses @file:greeting.md
│   ├── greeting.md            # Contains {@include} references
│   └── shared/
│       ├── tone.md
│       └── guidelines.md
└── common/
    ├── company-info.md
    └── legal-disclaimer.md

greeting.json:

{
  "fields": {
    "Name": "Customer Greeting",
    "Prompt": "@file:greeting.md"
  }
}

greeting.md:

# Customer Service Greeting

{@include ./shared/tone.md}

## Guidelines
{@include ./shared/guidelines.md}

## Company Information
{@include ../common/company-info.md}

## Legal
{@include ../common/legal-disclaimer.md}

The final content pushed to the database will have all includes fully resolved.

Benefits:

• DRY Principle: Share common content across multiple files
• Maintainability: Update shared content in one place
• Flexibility: Build complex documents from modular parts
• Validation: Automatic checking of included file existence and circular references

@include References in JSON Files

Enable modular JSON composition by including external JSON files directly into your metadata files. This feature allows you to break large JSON configurations into smaller, reusable components.

Syntax Options

Object Context - Property Spreading (Default)

{
  "name": "Parent Record",
  "@include": "child.json",
  "description": "Additional fields"
}

The included file's properties are spread into the parent object.

Multiple Includes with Dot Notation (Eliminates VS Code Warnings)

{
  "name": "Parent Record",
  "@include.data": "shared/data-fields.json",
  "description": "Middle field",
  "@include.config": "shared/config-fields.json",
  "status": "Active"
}

Use dot notation (@include.anything) to include multiple files at different positions in your object. The part after the dot is ignored by the processor but makes each key unique, eliminating VS Code's duplicate key warnings. The includes are processed in the order they appear, allowing precise control over property ordering.

Array Context - Element Insertion

[
  {"name": "First item"},
  "@include:child.json",
  {"name": "Last item"}
]

The included file's content is inserted as array element(s).

Explicit Mode Control

{
  "@include": {
    "file": "child.json",
    "mode": "spread"  // or "element"
  }
}

Modes Explained

"spread" mode:

• Merges all properties from the included file into the parent object
• Only works when including an object into an object
• Parent properties override child properties if there are conflicts
• Default mode for objects

"element" mode:

• Directly inserts the JSON content at that position
• Works with any JSON type (object, array, string, number, etc.)
• Replaces the @include directive with the actual content
• Default mode for arrays when using string syntax

Path Resolution

• All paths are relative to the file containing the @include
• Supports: "child.json", "./child.json", "../shared/base.json", "subfolder/config.json"
• Circular references are detected and prevented

Complex Example

Directory structure:

metadata/
├── components/
│   ├── dashboard.json
│   ├── base-props.json
│   └── items/
│       └── dashboard-items.json
└── shared/
    └── common-settings.json

dashboard.json:

{
  "fields": {
    "Name": "Analytics Dashboard",
    "@include.common": "../shared/common-settings.json",
    "Type": "Dashboard",
    "@include.defaults": "../shared/default-values.json",
    "Configuration": {
      "@include": {"file": "./base-props.json", "mode": "element"}
    }
  },
  "relatedEntities": {
    "Dashboard Items": [
      "@include:./items/dashboard-items.json"
    ]
  }
}

common-settings.json:

{
  "CategoryID": "@lookup:Categories.Name=Analytics",
  "Status": "Active",
  "Priority": 1
}

base-props.json:

{
  "refreshInterval": 60,
  "theme": "dark",
  "layout": "grid"
}

dashboard-items.json:

[
  {"name": "Revenue Chart", "type": "chart"},
  {"name": "User Stats", "type": "stats"},
  {"name": "Activity Feed", "type": "feed"}
]

Result after processing:

{
  "fields": {
    "Name": "Analytics Dashboard",
    "CategoryID": "@lookup:Categories.Name=Analytics",
    "Status": "Active", 
    "Priority": 1,
    "Type": "Dashboard",
    "Configuration": {
      "refreshInterval": 60,
      "theme": "dark",
      "layout": "grid"
    }
  },
  "relatedEntities": {
    "Dashboard Items": [
      {"name": "Revenue Chart", "type": "chart"},
      {"name": "User Stats", "type": "stats"},
      {"name": "Activity Feed", "type": "feed"}
    ]
  }
}

Use Cases

• Shared Configurations: Reuse common settings across multiple entities
• Modular Records: Build complex records from smaller components
• Template Libraries: Create libraries of reusable JSON fragments
• Environment Configs: Include environment-specific settings
• Large Data Sets: Break up large JSON files for better maintainability
• VS Code Compatibility: Use dot notation to avoid duplicate key warnings when including multiple files

Practical Example: Component with Multiple Includes

{
  "name": "DashboardComponent",
  "type": "dashboard",
  "@include.dataRequirements": "../shared/data-requirements.json",
  "functionalRequirements": "Dashboard displays real-time metrics...",
  "@include.libraries": "../shared/chart-libraries.json",
  "technicalDesign": "Component uses React hooks for state...",
  "@include.eventHandlers": "../shared/event-handlers.json",
  "code": "const Dashboard = () => { ... }"
}

In this example, data requirements, libraries, and event handlers are spread into the component definition at their specific positions, maintaining a logical property order while avoiding VS Code warnings about duplicate @include keys.

@include in Referenced JSON Files

When using @file: to reference a JSON file, any @include directives within that JSON file are automatically processed:

@file References in Included JSON Files

The system now automatically resolves @file references found within JSON files that are pulled in via @include. This allows for complete nesting of references:

// main-entity.json
{
  "fields": {
    "Name": "MyComponent",
    "Specification": "@file:files/component-spec.json"
  }
}

// files/component-spec.json
{
  "name": "ComponentSpec",
  "@include.base": "../shared/base-spec.json",
  "customFields": {
    "feature": "advanced"
  },
  "@include.libs": "../shared/libraries.json"
}

The component-spec.json file's @include directives are processed before the content is returned to the Specification field, ensuring all includes are resolved.

Nested @file References in JSON Files

The system now recursively processes @file references within JSON files loaded via @file. This enables powerful composition patterns:

// components.json
{
  "fields": {
    "Name": "RecentDealsList",
    "Specification": "@file:spec/recent-deals-list.spec.json"
  }
}

// spec/recent-deals-list.spec.json
{
  "name": "RecentDealsList",
  "description": "List of recent deals",
  "code": "@file:../code/recent-deals-list.js",  // This nested @file is now resolved!
  "style": "@file:../styles/deals.css",
  "config": {
    "template": "@file:../templates/deal-row.html"  // Even deeply nested @file references work
  }
}

All @file references are recursively resolved, regardless of nesting depth. The final result will have all file contents properly loaded and embedded.

Processing Order

1. @include directives are processed first (recursively)
2. @file references are recursively resolved (including nested ones in JSON)
3. Then @template references
4. Finally, other @ references (@lookup, etc.)

This ensures that included content can contain other special references that will be properly resolved.

New Feature: @file references within @included JSON files are now automatically resolved. This means you can have:

• A main JSON file with @include directives
• The included JSON files can have @file references to load code, templates, etc.
• Those @file references are resolved to their actual content
• If the @file points to a JSON file with @include directives, those are also processed

@template: References

Enable JSON template composition for reusable configurations:

String Template Reference

Use @template: to replace any value with template content:

{
  "relatedEntities": {
    "MJ: AI Prompt Models": "@template:templates/standard-ai-models.json"
  }
}

Object Template Merging

Use @template field within objects to merge template content:

{
  "fields": {
    "Name": "My Prompt",
    "@template": "templates/standard-prompt-settings.json",
    "Temperature": 0.9  // Overrides template value
  }
}

Multiple Template Merging

Merge multiple templates in order (later templates override earlier ones):

{
  "fields": {
    "@template": [
      "templates/base-settings.json",
      "templates/customer-service-defaults.json"
    ],
    "Name": "Customer Bot"  // Local fields override all templates
  }
}

Nested Templates

Templates can reference other templates:

// templates/high-performance-models.json
[
  {
    "fields": {
      "@template": "../templates/model-defaults.json",
      "ModelID": "@lookup:AI Models.Name=GPT 4o"
    }
  }
]

Template Benefits

• DRY Principle: Define configurations once, use everywhere
• Maintainability: Update template to affect all uses
• Flexibility: Use at any JSON level
• Composability: Build complex configurations from simple parts
• Override Support: Local values always override template values

CLI Commands

All MetadataSync functionality is accessed through the MemberJunction CLI (mj) under the sync namespace. The commands previously available through mj-sync are now integrated as mj sync commands:

# Validate all metadata files
mj sync validate

# Validate a specific directory
mj sync validate --dir="./metadata"

# Validate with detailed output
mj sync validate --verbose

# Validate with JSON output for CI/CD
mj sync validate --format=json

# Save validation report to markdown file
mj sync validate --save-report

# Initialize a directory for metadata sync
mj sync init

# Pull all AI Prompts from database to ai-prompts directory
mj sync pull --entity="AI Prompts"

# Pull specific records by filter
mj sync pull --entity="AI Prompts" --filter="CategoryID='customer-service-id'"

# Pull multiple records into a single file
mj sync pull --entity="AI Prompts" --multi-file="all-prompts"
mj sync pull --entity="AI Prompts" --filter="Status='Active'" --multi-file="active-prompts.json"

# Push all changes from current directory and subdirectories
mj sync push

# Push only specific entity directory
mj sync push --dir="ai-prompts"

# Push with verbose output
mj sync push -v
mj sync push --verbose

# Dry run to see what would change
mj sync push --dry-run

# Push with parallel processing
mj sync push --parallel-batch-size=20  # Process 20 records in parallel (default: 10, max: 50)

# Directory filtering - exclude specific directories
mj sync push --exclude="actions"                    # Exclude single directory
mj sync push --exclude="actions,templates"          # Exclude multiple directories (comma-separated)
mj sync push --exclude="*-test,*-old"              # Exclude using glob patterns

# Directory filtering - include only specific directories
mj sync push --include="prompts,agent-types"        # Include only these directories
mj sync push --include="ai-*"                       # Include using glob patterns

# Filtering works with other options
mj sync push --dir="metadata" --exclude="actions" --dry-run
mj sync status --exclude="actions,templates"

# Show status of local vs database
mj sync status

# Watch for changes and auto-push
mj sync watch

# CI/CD mode (push with no prompts, fails on validation errors)
mj sync push --ci

# Push/Pull without validation
mj sync push --no-validate
mj sync pull --entity="AI Prompts" --no-validate

# Reset file checksums after manual edits
mj sync file-reset

Configuration

The tool uses the existing mj.config.cjs for database configuration, eliminating the need for separate connection settings.

Configuration follows a hierarchical structure:

• Root config: Global settings for all operations
• Entity configs: Each entity directory has its own config defining the entity type
• Inheritance: All files within an entity directory are treated as records of that entity type

Push Configuration Options

The push command supports several configuration options to control how records are synchronized to the database:

autoCreateMissingRecords

When set to true, the push command will automatically create new records when a primaryKey is specified but the record doesn't exist in the database. This is useful when:

• Migrating data between environments
• Restoring records from backups
• Initializing a new database with known IDs

{
  "push": {
    "autoCreateMissingRecords": true
  }
}

Warning: When enabled, you'll see: 🔧 WARNING: autoCreateMissingRecords is enabled - Missing records with primaryKey will be created

alwaysPush

When set to true, forces ALL records to be saved to the database regardless of their dirty state. This bypasses the normal dirty checking mechanism and ensures every record is written to the database.

Use cases:

• Ensuring complete synchronization - When you need absolute certainty that all metadata is in sync
• Bypassing dirty detection issues - If file content changes aren't being detected properly
• Force refresh - When you want to refresh all database records with file content
• After database restoration - To ensure metadata matches file system after database operations

{
  "push": {
    "alwaysPush": true
  }
}

Warning: When enabled, you'll see: ⚡ WARNING: alwaysPush is enabled - ALL records will be saved to database regardless of changes

Note: This flag should be used judiciously as it will cause database writes for all records, even those that haven't changed. It's recommended to enable this temporarily when needed, then disable it for normal operations.

Parallel Processing

MetadataSync now supports parallel processing of records during push operations, significantly improving performance for large datasets.

How It Works

Records are automatically grouped into dependency levels:

• Level 0: Records with no dependencies
• Level 1: Records that depend only on Level 0 records
• Level 2: Records that depend on Level 0 or Level 1 records
• And so on...

Records within the same dependency level can be safely processed in parallel since they have no dependencies on each other.

Configuration

Use the --parallel-batch-size flag to control parallelism:

# Default: 10 records in parallel
mj sync push

# Process 20 records in parallel
mj sync push --parallel-batch-size=20

# Maximum parallelism (50 records)
mj sync push --parallel-batch-size=50

# Conservative approach for debugging
mj sync push --parallel-batch-size=1

Performance Benefits

• 2-3x faster for typical metadata pushes
• 5-10x faster for records with many file references (@file) or lookups (@lookup)
• Most beneficial when processing large numbers of independent records

When to Use

Recommended for:

• Large initial data imports
• Bulk metadata updates
• CI/CD pipelines with time constraints

Use conservative settings for:

• Debugging sync issues
• Working with complex dependencies
• Limited database connection pools

Directory Processing Order

The MetadataSync tool now supports custom directory processing order to handle dependencies between entity types. This feature ensures that dependent entities are processed in the correct order.

Directory Order Configuration

Directory order is configured in the root-level .mj-sync.json file only (not inherited by subdirectories):

{
  "version": "1.0.0",
  "directoryOrder": [
    "prompts",
    "agent-types"
  ]
}

How It Works

• Ordered Processing: Directories listed in directoryOrder are processed first, in the specified order
• Remaining Directories: Any directories not listed are processed after the ordered ones, in alphabetical order
• Dependency Management: Ensures prompts are created before agent types that reference them
• Flexible: Only specify the directories that have order requirements

Example Use Cases

1. AI Prompts → Agent Types: Create prompts before agent types that reference them
2. Categories → Items: Create category records before items that reference them
3. Parent → Child: Process parent entities before child entities with foreign key dependencies

Ignore Directories

The MetadataSync tool supports ignoring specific directories during push/pull operations. This is useful for:

• Excluding output or example directories from processing
• Skipping temporary or build directories
• Organizing support files without them being processed as metadata

Configuration

Ignore directories are configured in .mj-sync.json files and are cumulative through the directory hierarchy:

{
  "version": "1.0.0",
  "ignoreDirectories": [
    "output",
    "examples",
    "templates"
  ]
}

How It Works

• Cumulative Inheritance: Each directory inherits ignore patterns from its parent directories
• Relative Paths: Directory names are relative to the location of the .mj-sync.json file
• Simple Patterns: Supports exact directory names (e.g., "output", "temp")
• Additive: Child directories can add their own ignore patterns to parent patterns

Example

metadata/.mj-sync.json            → ignoreDirectories: ["output", "temp"]
├── prompts/.mj-sync.json         → ignoreDirectories: ["examples"]
│   ├── output/                   → IGNORED (from root)
│   ├── examples/                 → IGNORED (from prompts)
│   └── production/.mj-sync.json  → ignoreDirectories: ["drafts"]
│       ├── drafts/               → IGNORED (from production)
│       └── output/               → IGNORED (inherited from root)

In this example:

• Root level ignores "output" and "temp" everywhere
• Prompts directory adds "examples" to the ignore list
• Production subdirectory further adds "drafts"
• All patterns are cumulative, so production inherits all parent ignores

SQL Logging

The MetadataSync tool now supports SQL logging for capturing all database operations during push commands. This feature is useful for:

• Creating migration files from MetadataSync operations
• Debugging database changes
• Understanding what SQL operations occur during push
• Creating migration scripts for deployment to other environments

SQL Logging Configuration

SQL logging is configured in the root-level .mj-sync.json file only (not inherited by subdirectories):

{
  "version": "1.0.0",
  "sqlLogging": {
    "enabled": true,
    "outputDirectory": "./sql_logging",
    "formatAsMigration": true
  }
}

SQL Logging Options

| Option | Type | Default | Description | |--------|------|---------|-------------| | enabled | boolean | false | Whether to enable SQL logging during push operations | | outputDirectory | string | "./sql_logging" | Directory to output SQL log files (relative to command execution directory) | | formatAsMigration | boolean | false | Whether to format SQL as migration-ready files with Flyway schema placeholders | | filterPatterns | string[] | undefined | Array of patterns to filter SQL statements (see below) | | filterType | "exclude" | "include" | "exclude" | How to apply filter patterns |

SQL Log File Format

When formatAsMigration is false, log files are named:

metadatasync-push-YYYY-MM-DDTHH-MM-SS.sql

When formatAsMigration is true, log files are named as Flyway migrations:

VYYYYMMDDHHMMSS__MetadataSync_Push.sql

Migration files include:

• Header comments with timestamp and description
• Schema placeholders that can be replaced during deployment
• Properly formatted SQL statements with parameters

Example Usage

1. Enable SQL logging in your root .mj-sync.json:

   {
     "version": "1.0.0",
     "sqlLogging": {
       "enabled": true,
       "outputDirectory": "./migrations",
       "formatAsMigration": true
     }
   }

2. Run push command as normal:

   mj sync push

3. Review generated SQL in the output directory:

   migrations/
   └── V20241215103045__MetadataSync_Push.sql

The SQL logging runs in parallel with the actual database operations, ensuring minimal performance impact while capturing all SQL statements for review and potential migration use.

SQL Filtering Patterns

The filterPatterns option allows you to include or exclude specific SQL statements from logging. It supports both regex patterns and simple wildcard patterns:

Pattern Types:

• Regex patterns: Start with / and optionally end with flags (e.g., /spCreate.*Run/i)
• Simple wildcards: Use * as a wildcard (e.g., *AIPrompt*)

Examples:

{
  "sqlLogging": {
    "enabled": true,
    "filterPatterns": [
      "*AIPrompt*",           // Exclude anything containing "AIPrompt"
      "/^EXEC sp_/i",         // Exclude stored procedures starting with "sp_"
      "*EntityFieldValue*",   // Exclude EntityFieldValue operations
      "/INSERT INTO (__mj|mj)/i"  // Exclude inserts to system tables
    ],
    "filterType": "exclude"   // Default - exclude matching patterns
  }
}

Include Mode Example:

{
  "sqlLogging": {
    "enabled": true,
    "filterPatterns": [
      "*User*",               // Only log User-related SQL
      "*Role*",               // Only log Role-related SQL
      "/sp_ChangePassword/i"  // Include password change procedures
    ],
    "filterType": "include"   // Only log statements matching patterns
  }
}

Simple Wildcard Syntax:

• *pattern* - Contains pattern (case-insensitive)
• pattern* - Starts with pattern
• *pattern - Ends with pattern
• pattern - Exact match

User Role Validation

MetadataSync now supports validating UserID fields against specific roles in the MemberJunction system. This ensures that only users with appropriate roles can be referenced in metadata files.

Configuration

Add the userRoleValidation configuration to your root .mj-sync.json file:

{
  "version": "1.0.0",
  "userRoleValidation": {
    "enabled": true,
    "allowedRoles": [
      "Administrator",
      "Developer",
      "Content Manager"
    ],
    "allowUsersWithoutRoles": false
  }
}

Configuration Options

| Option | Type | Default | Description | |--------|------|---------|-------------| | enabled | boolean | false | Enable user role validation for UserID fields | | allowedRoles | string[] | [] | List of role names that are allowed | | allowUsersWithoutRoles | boolean | false | Allow users without any assigned roles |

How It Works

1. During validation, all user roles are loaded from the database and cached
2. For each UserID field in metadata files, the validator checks:
   • If the user exists and has roles assigned
   • If the user has at least one of the allowed roles
3. Validation fails if:
   • A UserID references a user without any roles (unless allowUsersWithoutRoles is true)
   • A UserID references a user whose roles are not in the allowedRoles list

Example

Given a metadata file with a UserID field:

{
  "fields": {
    "Name": "Admin Action",
    "UserID": "user-123"
  }
}

The validation will:

1. Check if user-123 exists in the system
2. Verify that user-123 has one of the allowed roles
3. Report an error if the user doesn't have appropriate roles

Error Messages

✗ UserID 'user-123' does not have any assigned roles
  Suggestion: User must have one of these roles: Administrator, Developer

✗ UserID 'user-456' has roles [Viewer] but none are in allowed list
  Suggestion: Allowed roles: Administrator, Developer, Content Manager

Root Configuration (metadata/.mj-sync.json)

{
  "version": "1.0.0",
  "directoryOrder": [
    "prompts",
    "agent-types"
  ],
  "push": {
    "validateBeforePush": true,
    "requireConfirmation": true,
    "autoCreateMissingRecords": false,
    "alwaysPush": false
  },
  "sqlLogging": {
    "enabled": true,
    "outputDirectory": "./sql_logging",
    "formatAsMigration": false
  },
  "userRoleValidation": {
    "enabled": true,
    "allowedRoles": ["Administrator", "Developer"],
    "allowUsersWithoutRoles": false
  },
  "watch": {
    "debounceMs": 1000,
    "ignorePatterns": ["*.tmp", "*.bak"]
  }
}

Entity Configuration (metadata/ai-prompts/.mj-sync.json)

{
  "entity": "AI Prompts",
  "filePattern": ".*.json",
  "defaults": {
    "TypeID": "@lookup:AI Prompt Types.Name=Chat",
    "Temperature": 0.7,
    "MaxTokens": 1500,
    "Status": "Active"
  },
  "pull": {
    "filePattern": ".*.json",
    "updateExistingRecords": true,
    "createNewFileIfNotFound": true,
    "mergeStrategy": "merge",
    "filter": "Status = 'Active'",
    "externalizeFields": [
      {
        "field": "Prompt",
        "pattern": "@file:{Name}.prompt.md"
      }
    ],
    "relatedEntities": {
      "MJ: AI Prompt Models": {
        "entity": "MJ: AI Prompt Models",
        "foreignKey": "PromptID",
        "filter": "Status = 'Active'"
      }
    }
  }
}

Folder Defaults (metadata/ai-prompts/customer-service/.mj-folder.json)

{
  "defaults": {
    "CategoryID": "@lookup:AI Prompt Categories.Name=Customer Service",
    "Temperature": 0.8,
    "Tags": ["customer-service", "support"]
  }
}

Nested Folder Defaults (metadata/ai-prompts/customer-service/escalation/.mj-folder.json)

{
  "defaults": {
    "Tags": ["customer-service", "support", "escalation", "priority"],
    "MaxTokens": 2000,
    "Temperature": 0.6
  }
}

Embedded Collections

The tool now supports managing related entities as embedded collections within parent JSON files. This is ideal for entities that have a strong parent-child relationship.

Benefit