Keyboard shortcuts

Press ← or → to navigate between chapters

Press S or / to search in the book

Press ? to show this help

Press Esc to hide this help

Event Promotion Design: State-Based Reconciliation with Composite Keys

Overview

Traditional event sourcing replication involves copying raw events from one environment to another. However, this fails when the target environment has diverged (e.g., hotfixes), causing aggregateVersion conflicts. Additionally, strict global UUID constraints can prevent reusing the same ID across environments (Tenants). Finally, partial promotions can fail if parent dependencies (referential integrity) are missing in the target.

To resolve this, we adopt a State-Based Reconciliation approach (Semantic Replay) combined with Composite Keys for identity and Recursive Dependency Resolution for integrity.

Core Strategy: State-Based Reconciliation

Workflow

  1. Export (Lower Environment):
    • Query the current state (Snapshot) of the entity from the Lower Environment (LE).
    • Produce a “Canonical State Snapshot” (JSON).
  2. Import & Diff (Higher Environment):
    • Read the LE Snapshot.
    • Query the current state of the representative entity in the Higher Environment (HE).
    • Compare:
      • New? -> Generate XxxCreatedEvent.
      • Changed? -> Calculate Delta -> Generate XxxUpdatedEvent.
      • Same? -> No-op.

Advantages

  • Conflict Immunity: No aggregateVersion conflicts; we always append new events.
  • Self-Healing: Automatically synchronizes diverged states.

Identity Strategy: Composite Keys

The Problem: Global UUID Uniqueness

In a multi-tenant system shareing a single database, a standard Primary Key UUID (e.g., user_id) is globally unique. This prevents us from having “User Steve” with UUID 123 in both the “Dev Tenant” and “Prod Tenant” if the DB enforces strict uniqueness on that column.

The Solution: Composite Keys (host_id + aggregate_id)

We scope all identity by the Tenant ID (host_id).

  1. Schema Change:

    • Primary Keys: Change from PK(id) to PK(host_id, id).
    • Uniqueness: Change unique constraints (e.g., email) from UK(email) to UK(host_id, email).
    • Event Store: Change unique constraint from UK(aggregate_id, version) to UK(host_id, aggregate_id, version).

  2. Promotion Benefit:

    • Dev Tenant: host_id=DEV, user_id=123
    • Prod Tenant: host_id=PROD, user_id=123
    • Matching entities is trivial (compare id directly).

Data Integrity: Recursive Dependency Resolution

The Problem: Missing Dependencies

Promoting a child entity (e.g., API Configuration) fails if its parent (e.g., API Instance) does not exist in the target environment (Higher Env).

The Solution: Deep Promotion (Recursive Bundling)

The exporter must be “Topology Aware”.

  1. Dependency Metadata: Every Entity Type must declare its dependencies.

    • ApiConfig depends on ApiInstance.
    • ApiInstance depends on GatewayInstance.
    • GatewayInstance depends on Host.

  2. Export Workflow (Recursive): When a user selects ApiConfig-123 for promotion:

    • System checks ApiConfig-123 -> Parent ApiInstance-456.
    • System checks ApiInstance-456 -> Parent GatewayInstance-789.
    • Export Package: Includes [GatewayInstance-789, ApiInstance-456, ApiConfig-123] (Ordered by dependency).

  3. Import Workflow (Ordered): The Importer processes the list in order:

    1. GatewayInstance: Exists in Prod? Yes. (Skip).
    2. ApiInstance: Exists in Prod? No. Action: Create ApiInstance.
    3. ApiConfig: Exists in Prod? No. Action: Create ApiConfig.

Dry Run Technical Implementation

Purpose

To guarantee the promotion will succeed without actually modifying the Higher Environment (Production).

  • Logic: The Importer queries the DB (read-only) to fetch the current state of all entities in the package.
  • Result: It calculates the “Diff Plan” purely in memory.
  • Output: “Plan: Create API Instance (New), Update API Config (Diff)”.
  • Pros: Very fast, zero DB locks.
  • Cons: Does not verify deep database constraints (e.g., complex triggers or check constraints) that only trigger on write.
  • Logic:
    1. Start a Database Transaction: connection.setAutoCommit(false);
    2. Simulate Execution: Perform the actual SQL Inserts and Updates generated by the Plan.
      • Insert ApiInstance…
      • Insert ApiConfig…
    3. Check for Errors: If any SQL Exception occurs (e.g., FK violation, unique constraint violation), catch it.
    4. Rollback: Regardless of success or failure, always call connection.rollback().
  • Output: “Validation Successful: The detailed plan is valid and safe to execute.” OR “Validation Failed: FK Violation on Table X”.
  • Pros: 100% certainty that the data is valid according to the database schema.
  • Cons: Slightly heavier key locks, but acceptable for admin operations.

Recommendation

Use Option 1 (App Simulation) for the UI preview to show the user “what will happen”. Use Option 2 (Transaction Rollback) immediately when the user clicks “Promote” (as a pre-flight check) or as an explicit “Verify” button to ensure deep integrity.

Sibling Deletion: Handling Orphaned Items

The User Case

When promoting a collection of items (e.g., “10 Config Properties” in HE vs “8 in LE”), simply creating or updating the 8 matching items from LE is insufficient. We must identify the 2 extra items in HE that likely need to be deleted to match the LE state.

Design Pattern: Scoped Reconciliation

To handle this, the import logic must be aware of the “Parent Scope” of the entities being promoted.

  1. Export (Snapshot with Siblings):

    • When promoting ApiConfig-123, we fetch ALL associated properties for that config in LE.
    • LE Snapshot: Properties = {P1, P2, ... P8} (Total 8).

  2. Import (Set Difference Logic):

    • Query ALL associated properties for ApiConfig-123 in HE.
    • HE State: Properties = {P1, P2, ... P8, P9, P10} (Total 10).
    • Logic: HE_Only = HE_Set - LE_Set => {P9, P10}.

  3. User Decision (Interactive Mode):

    • The Dry Run Plan reports:
      • Updates: 8 items synced (P1..P8).
      • Deuntions (Potential): 2 items exist in Prod but not Dev (P9, P10).
    • Default Action: Do nothing (Safe Mode).
    • Option: “Sync Deletes” -> Checkbox to delete extras?
    • Strict Mode: Mirror exact state (Automatically schedule ConfigPropertyDeletedEvent for P9, P10).

Implementation Checklist

  • Exporter must include the full list of children IDs when exporting a parent container.
  • Importer must realize that for “One-to-Many” relationships, it has to fetch the full target set to detect orphans.

UI and Service Design

Entity Dependency Graph

The exporter must be “Topology Aware”. When exporting an entity, all parent and child dependencies are included. Starting with instance_t as the primary promotable entity:

host_t
└── instance_t
    ├── instance_property_t
    ├── instance_file_t
    ├── instance_api_t
    │   ├── instance_api_property_t
    │   └── instance_api_path_prefix_t
    ├── instance_app_t
    │   ├── instance_app_property_t
    │   └── instance_app_api_t
    │       └── instance_app_api_property_t
    └── deployment_instance_t
        └── deployment_instance_property_t

Promotion Modes

Two promotion modes are supported:

  1. Cross-Instance (JSON): Export entity snapshots as JSON files, then import them into a different environment/database instance. Used when source and target are in separate databases.
  2. Same-Instance (Data Table): Use promotion_t and promotion_item_t tables for tracking promotions between hosts within the same database. Source and target hosts share the same database.

Database: Promotion Tracking Tables

These tables are for same-instance promotions to track promotion jobs and their items.

CREATE TABLE promotion_t (
    promotion_id         UUID NOT NULL,
    source_host_id       UUID NOT NULL,
    target_host_id       UUID NOT NULL,
    entity_type          VARCHAR(64) NOT NULL,   -- 'instance', 'rule', 'api', etc.
    promotion_status     VARCHAR(16) NOT NULL,   -- 'Planned', 'DryRun', 'Executed', 'Failed', 'RolledBack'
    plan_summary         JSONB,                  -- The diff plan generated by dry run
    created_by           UUID NOT NULL,
    aggregate_version    BIGINT DEFAULT 1 NOT NULL,
    active               BOOLEAN NOT NULL DEFAULT TRUE,
    delete_user          VARCHAR(255),
    delete_ts            TIMESTAMP WITH TIME ZONE,
    update_user          VARCHAR(255) DEFAULT SESSION_USER NOT NULL,
    update_ts            TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP NOT NULL,
    PRIMARY KEY(promotion_id)
);

CREATE TABLE promotion_item_t (
    promotion_id         UUID NOT NULL,
    item_id              UUID NOT NULL,
    entity_type          VARCHAR(64) NOT NULL,    -- 'instance', 'instance_property', etc.
    entity_id            VARCHAR(255) NOT NULL,   -- The ID of the entity being promoted
    action               VARCHAR(16) NOT NULL,    -- 'CREATE', 'UPDATE', 'DELETE', 'NOOP'
    source_snapshot      JSONB,                   -- State in source (LE)
    target_snapshot      JSONB,                   -- State in target (HE) for diff
    diff_summary         JSONB,                   -- Field-level diff
    execution_status     VARCHAR(16) DEFAULT 'Pending', -- 'Pending', 'Success', 'Failed'
    error_message        TEXT,
    update_user          VARCHAR(255) DEFAULT SESSION_USER NOT NULL,
    update_ts            TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP NOT NULL,
    PRIMARY KEY(promotion_id, item_id),
    FOREIGN KEY(promotion_id) REFERENCES promotion_t(promotion_id) ON DELETE CASCADE
);

Service API Contracts

All promotion services are implemented in the user-command module (net.lightapi.portal.user.command.handler) alongside the existing ExportPortalEvent and ImportPortalEvent handlers.

Export Snapshot (Query)

Exports the current state of selected entities and all their children as a canonical JSON snapshot.

  • Service: user
  • Action: exportSnapshot
  • Request Data:
    • sourceHostId (UUID) – The host to export from.
    • entityType (String) – e.g., "instance".
    • entityIds (Array<String>) – IDs of entities to export.
    • includeChildren (Boolean) – Recursively include child entities.
    • includeSiblings (Boolean) – Include full sibling sets for orphan detection.
  • Response: Canonical State Snapshot JSON containing all entities ordered by dependency depth, with nested children. The nested format is preferred over flat-with-references because the tree depth is bounded (max 4 levels for instance_t), making it self-contained and easy to process depth-first during import.
{
  "exportVersion": "1.0.0",
  "sourceHostId": "...",
  "exportTs": "2026-03-09T20:00:00Z",
  "entities": [
    {
      "entityType": "instance",
      "entityId": "...",
      "data": { },
      "children": {
        "instance_property": [ ],
        "instance_file": [ ],
        "instance_api": [
          {
            "data": { },
            "children": {
              "instance_api_property": [ ],
              "instance_api_path_prefix": [ ]
            }
          }
        ],
        "instance_app": [
          {
            "data": { },
            "children": {
              "instance_app_property": [ ],
              "instance_app_api": [
                {
                  "data": { },
                  "children": {
                    "instance_app_api_property": [ ]
                  }
                }
              ]
            }
          }
        ],
        "deployment_instance": [
          {
            "data": { },
            "children": {
              "deployment_instance_property": [ ]
            }
          }
        ]
      }
    }
  ]
}

Import Dry Run (Command)

Performs an application-layer simulation (Option 1) to calculate the diff plan without modifying the database.

  • Service: user
  • Action: importDryRun
  • Request Data:
    • targetHostId (UUID) – The host to import into.
    • snapshot (Object) – The exported canonical snapshot JSON.
  • Response: Diff plan with summary counts and per-item actions.
{
  "promotionId": "...",
  "summary": { "create": 5, "update": 3, "noop": 2, "orphan": 1 },
  "items": [
    {
      "entityType": "instance",
      "entityId": "...",
      "action": "UPDATE",
      "diff": { "instance_name": { "from": "old-name", "to": "new-name" } }
    },
    {
      "entityType": "instance_property",
      "entityId": "...",
      "action": "CREATE",
      "diff": null
    }
  ]
}

Import Execute (Command)

Executes the promotion plan, applying all changes to the target host.

  • Service: user
  • Action: importExecute
  • Request Data:
    • targetHostId (UUID) – The host to apply changes to.
    • promotionId (UUID, optional) – From the dry run (for same-instance tracking).
    • snapshot (Object) – The canonical snapshot.
    • orphanAction (String) – "keep" | "delete" | "sync".
  • Response: Execution result with per-item status (Success/Failed) and error messages.

UI Pages

All pages are located under portal-view/src/pages/promotion/ and accessible via a top-level “Promotion” sidebar menu with children: Export, Import, History.

PromotionExport.tsx (/app/promotion/export)

A 3-step wizard guiding the user through the export process:

  1. Select Source & Type: User picks a source host from a dropdown and selects the entity type (starting with “Instance”).
  2. Select Entities: A MaterialReactTable loads entities for the selected host with checkbox selection. Supports filtering, sorting, and pagination.
  3. Preview & Export: Two options:
    • Download JSON – Downloads the canonical snapshot as a .json file for cross-instance promotion.
    • Promote to Host – Select a target host and navigate to the Import page with the snapshot pre-loaded for dry run.

PromotionImport.tsx (/app/promotion/import)

Handles the import and execution workflow:

  1. Select Import Source: Upload a JSON file, or receive a snapshot from the Export page via navigation state.
  2. Dry Run Preview: After selecting a target host and clicking “Run Dry Run,” displays the diff plan:
    • New items (green) – Will be created.
    • Changed items (yellow) – Will be updated, with expandable field-level diffs.
    • Same items (gray) – No action needed.
    • Orphaned items (red) – Exist in target but not in source.
  3. Execute: For orphaned items, user selects Keep/Delete/Sync via radio buttons. Clicking “Execute Promotion” applies all changes.

PromotionHistory.tsx (/app/promotion/history)

A standard MaterialReactTable listing past promotions with columns: Source Host, Target Host, Entity Type, Status (color-coded chip), Created By, Timestamp, Promotion ID. Row action: View Details (navigates to diff view).

PromotionDiffView.tsx (/app/promotion/diff)

Displays detailed promotion metadata (source/target hosts, status, timestamps) and a table of all promotion items with expandable field-level diffs showing source vs. target values and per-item execution status.

Implementation Phases

  1. Phase 1 – UI Foundation: Create promotion pages, sidebar menu entry, route registration. (Completed)
  2. Phase 2 – Backend Services: Implement exportSnapshot, importDryRun, importExecute services, and promotion_t/promotion_item_t DDL.
  3. Phase 3 – Same-Instance Promotion: Integrate promotion tracking tables, add “Promote to Host” flow, orphan detection.
  4. Phase 4 – Additional Entity Types: Add support for rule_t, schema_t, api_t, config_t and extend the dependency resolver.