Atomic Migrator

Atomic Migrator is a namespaced, PSR-4 compliant WordPress plugin designed to sync delta content updates between a remote WordPress site (source) and a local site (destination). Instead of transferring heavy database dumps, it observes real-time modifications and performs secure, optimized pull operations over the WP REST API.

To protect the main WordPress MySQL database, all plugin tracking tables, UUID mappings, change logs, and synchronization histories live inside an isolated SQLite database, leaving the native core tables completely pristine.

---

📐 Architecture & How It Works

graph TD
    subgraph LocalSite
        SQLDB_L[(Isolated SQLite Database)]
        SyncEng[Sync Engine]
        Remap[Reference Remapper]
        CLI[WP-CLI Commands]
    end

    subgraph RemoteSite
        SQLDB_R[(Isolated SQLite Database)]
        Observer[CRUD Hook Observer]
        REST[REST API Server]
    end

    WP_Hooks --> Observer
    Observer --> SQLDB_R
    CLI --> SyncEng
    SyncEng --> REST
    REST --> SQLDB_R
    REST --> SyncEng
    SyncEng --> Remap
    SyncEng --> SQLDB_L

1. Isolated SQLite Storage Layer & Local Temp Execution

The plugin bypasses $wpdb entirely for internal bookkeeping. It creates an isolated SQLite database under: /wp-content/uploads/atomic-migrator/migrator_cache.sqlite

This folder is locked down automatically upon installation with directory permission blocks (.htaccess and index.php) preventing direct browser access.

Clustered Hosting Execution Lifecycle (WP Engine, Pantheon, Kinsta)

To support clustered, multi-node hosting environments where the uploads/ directory is mounted on a shared network filesystem (NFS/GlusterFS):

• Local Temp Execution: When the database is initialized, the plugin checks for a local fast temp directory (defined by the WP_TEMP_DIR constant or PHP sys_get_temp_dir()). If available, it copies the SQLite file from the network drive to a site-specific local temp path (e.g. /tmp/atomic_migrator_[site_hash].sqlite) for active execution.
• WAL Concurrency: Since queries execute on local storage, Write-Ahead Logging (WAL) and POSIX file locks work correctly, avoiding slow locking latencies and shared memory errors (SQLITE_IOERR_SHMOPEN) typical on network-mounted paths.
• MySQL Session Locking: To prevent race conditions across multiple load-balanced web servers, the lifecycle is coordinated by a lightweight MySQL session lock (GET_LOCK and RELEASE_LOCK). This ensures only one process writes to the SQLite file at a time without polluting the WordPress MySQL database with custom tables or persistent rows.
• Atomic Persist: Upon request termination, the WAL journal is checkpointed and closed, the temp file is copied back to the persistent uploads path, and the MySQL lock is released.

The SQLite database manages three distinct structures:

1. atomic_migrator_registry: Translates WordPress auto-increment IDs to permanent, immutable UUIDv4 strings.
2. atomic_migrator_ledger: Logs local mutations (create, update, delete) and tracks payload hashes to identify drift.
3. atomic_migrator_sync_history: Maintains logs of all synchronizations.

2. Mutation Observing (Ledger Logging)

The plugin registers hooks to capture changes dynamically without performing heavy database lookups:

flowchart TD
    Hook["WP Action Hook (e.g. save_post)"] --> CheckSync{"Is Syncing?"}
    CheckSync -- Yes --> Skip["Skip (Avoid loops)"]
    CheckSync -- No --> Registry{"Get UUID in SQLite Registry"}
    Registry -- Not Found --> Gen["Generate UUIDv4"] --> InsertReg["Insert Registry Row"]
    Registry -- Found --> Hash["Generate MD5 hash of values"]
    InsertReg --> Hash
    Hash --> LogLedger["Write Entry (action: create/update/delete) in SQLite Ledger"]

• Tombstones: When posts or terms are deleted, delete_post and delete_term hooks capture the event and record a delete action tombstone in the SQLite ledger so that deletions propagate correctly during synchronization.

3. Client-Server Connection

The destination site connects to the source site over HTTPS. Authentication is handled via a custom API Token passed in the HTTP request headers.

• Connection details (remote URL and remote API Token) are stored encrypted inside the isolated SQLite settings table using the sodium cryptographic library.
• The key is derived from the ATOMIC_MIGRATOR_KEY constant (configured inside wp-config.php).

sequenceDiagram
    autonumber
    participant Local as Local WordPress (Destination)
    participant Remote as Remote WordPress (Source)

    Local->>Remote: HTTP GET /wp-json/atomic-migrator/v1/changes (X-Atomic-Migrator-Token Header over HTTPS)
    alt Authentication Valid
        Remote-->>Local: HTTP 200 (Returns ledger list)
    else Invalid Credentials or HTTP
        Remote-->>Local: HTTP 401/403 (Forbidden)
    end

---

🔄 The Sync Cycle & Conflict Resolution

When a pull is executed, the Sync Engine requests change details and integrates them:

flowchart TD
    Start["wp atomic-migrator pull"] --> Fetch["GET /changes?since=last_sync"]
    Fetch --> Loop["Loop Changes"]

    Loop --> CheckDel{"Is Action Delete?"}
    CheckDel -- Yes --> Delete["Remove local entity & registry key"]
    CheckDel -- No --> Payload["GET /objects/{uuid}"]

    Payload --> CheckCol{"Remote ID collides with local ID?"}
    CheckCol -- Yes --> Strategy{"Check Strategy"}

    Strategy -- Overwrite --> Over["Delete local colliding object & Recreate"]
    Strategy -- Append --> App["Insert remote object (gets new local ID)"]
    CheckCol -- No --> App

    Over --> Reg["Register local ID <-> UUID mapping in SQLite"]
    App --> Reg

    Reg --> LoopEnd["Next change"]
    LoopEnd --> Remap["ReferenceRemapper: Remap block & serialized ID keys"]
    Remap --> Done["Complete (Save history timestamp)"]

Conflict Resolution Strategies:

• Overwrite (Force Overwrite): Removes the colliding local post/term and replaces it.
• Append & Remap (Safe Merge): Inserts the remote asset under a brand-new auto-increment ID. The reference remapper then scans Gutenberg blocks and metadata relationships (like post parents, term hierarchies, or attachment associations) to translate remote ID pointers to the new local ID pointers.

---

📖 User Guide

This guide explains how to connect two sites and run content pulls, either through the premium Admin GUI or via WP-CLI.

1. Requirements & Setup

• Ensure both sites are running PHP 8.2+.

• Ensure Pretty Permalinks are enabled on both sites (required for custom REST endpoints).

• Add a secure encryption constant to both sites' wp-config.php:

  define( 'ATOMIC_MIGRATOR_KEY', 'your-super-long-secure-random-key-here' );

  Tip: You can generate a secure 64-character hexadecimal key to use here by running one of the following commands in your terminal:

  • OpenSSL: openssl rand -hex 32
  • PHP: php -r "echo bin2hex(random_bytes(32));"

  (Note: The keys do not need to match between the two sites. The key is used strictly for encrypting and decrypting credential options stored in each site's local database, so they should ideally be unique to each site for security).

• Clustered Hosting Support: If your site is hosted on a platform with network-attached file storage (like WP Engine or Pantheon), the plugin automatically detects the environment and uses the local WP_TEMP_DIR / /tmp path with MySQL session locking to execute query transactions. You do not need to manually configure local paths unless you want to override the persistent storage directory completely via:

  define( 'ATOMIC_MIGRATOR_DB_PATH', '/custom/persistent/path/migrator_cache.sqlite' );

---

2. Using the Admin GUI Dashboard

The plugin provides a sleek, modern visual interface for connection management, configuration, and selective sync pulling.

A. Connection Setup

1. Source Site (Remote):
   • Log in to the source/remote WordPress dashboard as an Administrator.
   • Navigate to Atomic Migrator in the sidebar.
   • Under Incoming Connection Helper, copy the auto-generated secure API Token and Connection URL.
2. Destination Site (Local):
   • Log in to the destination/local WordPress dashboard as an Administrator.
   • Navigate to Atomic Migrator in the sidebar.
   • Under Outbound Connection Setup, paste the Remote Connection URL and Remote API Token, then click Connect.

B. Triggering Content Syncs

Under the Sync Dashboard Control Center card on the destination site:

1. Configure Parameters:
   • Conflict Strategy: Select Append & Remap (creates new local IDs and translates block references) or Overwrite (deletes conflicting local IDs and overwrites them).
   • Dry Run Mode: Toggle on to preview actions without committing changes.
2. Selective Filtering:
   • Sync by Post Type: Check or uncheck checkboxes to limit updates to specific post types (e.g. only post, only page, or custom post types).
   • Sync by Specific ID: Provide a comma-separated list of remote Post or Term IDs in the input box to import only those items.
3. Run Sync:
   • Click the prominent Sync Now button.
   • A glassmorphic Sync Progress Panel will slide open, displaying:
     • Real-time progress percentage.
     • A smooth, pulsing gradient loading bar representing imported items.
     • Counters for Created, Updated, Deleted, and Skipped/Failed items.
     • A live streaming log console displaying the title/name of the item currently being imported.

C. Progressive Sync Lifecycle (AJAX Sequence)

The client-side browser orchestrates the progressive sync pull process step-by-step using AJAX requests to prevent gateway timeouts on large changesets:

sequenceDiagram
    autonumber
    actor Admin as WordPress Admin GUI
    participant LocalAPI as Local REST Controller
    participant RemoteAPI as Remote Partner API
    participant Engine as Sync Engine
    participant DB as SQLite / MySQL DB

    Admin->>LocalAPI: POST /admin/sync/changes
    LocalAPI->>RemoteAPI: GET /changes?since=last_sync&exclude_statuses=...
    RemoteAPI-->>LocalAPI: Array of remote changesets
    LocalAPI-->>Admin: Sync Queue items list

    loop For each change in queue
        Admin->>LocalAPI: POST /admin/sync/process (change details)
        LocalAPI->>RemoteAPI: GET /objects/{uuid}
        RemoteAPI-->>LocalAPI: Object payload (post/term + meta)
        LocalAPI->>Engine: process_change(payload)
        Engine->>DB: Write to Local DB (append/overwrite)
        Engine-->>LocalAPI: Identity Mapping (remote ID <-> local ID)
        LocalAPI-->>Admin: Success status & resolved mapping
    end

    Admin->>LocalAPI: POST /admin/sync/finalize (mappings dict)
    LocalAPI->>Engine: finalize_pull(mappings dict)
    Engine->>DB: Remap Gutenberg block blocks & custom meta URLs
    Engine->>DB: Write Sync Session Log
    LocalAPI-->>Admin: Completed summary status

---

3. Using WP-CLI (Command Reference)

You can also run all synchronization and cache routines via WP-CLI on your destination site:

Command	Arguments	Description
wp atomic-migrator connect	<url> --token=<token>	Connects to source site and encrypts the connection API Token.
wp atomic-migrator pull	[--strategy=append\|overwrite] [--dry-run]	Syncs remote changesets into the local database.
wp atomic-migrator cache_status	None	Displays records counts in SQLite cache tables.
wp atomic-migrator cache_vacuum	None	Runs VACUUM database optimizer command.

CLI Pull Examples:

• Preview changes (Dry Run):

  wp atomic-migrator pull --dry-run

• Execute pull using Append strategy (Default):

  wp atomic-migrator pull --strategy=append

• Execute pull using Overwrite strategy:

  wp atomic-migrator pull --strategy=overwrite

---

4. Future Roadmap

Planned enhancements for future releases include:

• Memory-Efficient Chunked Media Transfers: Streamlining the transfer of large binary files (images, PDFs, video attachments) by chunking them into multiple smaller HTTP requests to bypass strict PHP upload sizes and network post-size constraints on budget shared hosts.