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 supported promotion snapshots, applying changes to the target host through event sourcing.

The current implementation supports global migration snapshots whose payload contains a top-level tables object. These snapshots are imported through the existing event-based global import pipeline, which converts table rows into ordered events and writes them to event_store_t and outbox_message_t.

Selective entity promotion snapshots whose payload contains a top-level entities array are not executable yet. The dry-run planner can still calculate the diff plan, but importExecute must reject this snapshot shape until the selective entity event materializer is implemented. Returning PLANNED items from importExecute is not a valid execution result and the UI must not treat that response as a successful promotion.

  • Service: user
  • Action: importExecute
  • Request Data:
    • targetHostId (UUID) – The host to apply changes to.
    • snapshot (Object) – The canonical snapshot. Executable today only when it contains tables.
    • promotionId (UUID, optional) – Reserved for selective entity execution.
    • orphanAction (String) – Reserved for selective entity execution: "keep" | "delete" | "sync".
  • Response: For global snapshots, the global import result such as { "imported": 42, "total": 42 }. For selective entity snapshots, a validation error until the selective execution path is implemented.

Selective entity execution requires a new materialization layer:

  1. Translate each dry-run CREATE, UPDATE, and optional orphan DELETE item into the matching domain event.
  2. Preserve dependency order from the exported snapshot.
  3. Write generated events through the same event-store/outbox transaction pattern used by the global import pipeline.
  4. Return per-item execution status only after the event write succeeds or fails.

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 selective entity snapshots, execution is disabled by the backend until event materialization is implemented. For global migration snapshots, the page bypasses the selective dry-run plan and calls globalSnapshotImport directly.

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, and validation for importExecute. (Partially completed: selective execution is blocked until event materialization is implemented.)
  3. Phase 3 – Same-Instance Promotion: Integrate promotion tracking tables, add “Promote to Host” flow, orphan detection, and selective event materialization.
  4. Phase 4 – Additional Entity Types: Add selective export and dry-run support for additional entity types. (Partially completed: config, rule, schema, api, and other entity snapshots are supported for export/dry-run; selective execution still waits on Phase 3 event materialization and dependency ordering remains entity-specific.)
  5. Phase 5 – Global Migration Export: Implement dynamic table discovery for full-database migration. (Completed; see below.)

Global Migration Export

Motivation

The entity-level promotion (ExportSnapshot) is designed for selective promotion — the user picks specific entities (e.g., 3 instances) and promotes them from a lower environment to a higher one. For that use case, the export produces a rich nested JSON with children and dependencies, which requires hand-crafted exportXxxSnapshot() methods per entity type.

However, a full database migration has fundamentally different requirements:

  • Scope: ALL entities across ALL entity types — not a user-selected subset.
  • Maintainability: When new tables are added to the system, the migration should work automatically without code changes.
  • Simplicity: A flat per-table export is sufficient since all data is exported together (no missing dependency risk).

Design: Dynamic Table Discovery

Instead of maintaining a manual list of entity types and per-type export methods, the Global Migration Export uses PostgreSQL DatabaseMetaData to automatically discover and export all projection tables.

How It Works

  1. Discover all tables ending in _t in the public schema via DatabaseMetaData.getTables().
  2. Skip infrastructure tables that should never be exported:
    • event_store_t — immutable event log (events will be regenerated on import)
    • outbox_message_t — transient consumer outbox
    • consumer_offsets — operational state
    • consumer_lock — operational lock
    • promotion_t, promotion_item_t — promotion tracking (environment-specific)
  3. For each discovered table:
    • Inspect column metadata to detect if the table has host_id and active columns.
    • If active column exists: SELECT * FROM table_t WHERE active = TRUE [AND host_id = ?].
    • If no active column: SELECT * FROM table_t [WHERE host_id = ?].
    • Convert each row to Map<String, Object> with camelCase key names.
  4. Record a consistency marker: SELECT MAX(id) FROM event_store_t at the start of the export transaction to stamp the snapshot with the lastEventId.
  5. Use REPEATABLE READ transaction isolation for consistency across all tables (PostgreSQL MVCC ensures a frozen-in-time view even if events are being processed concurrently).

Data Consistency Strategy

Querying projection tables directly is safe because:

  • PostgreSQL MVCC: REPEATABLE READ provides a consistent snapshot at transaction start time. Concurrent event processing does not affect the exported data.
  • Atomic event application: Each event is applied via handleEvent() within its own transaction, so partial aggregate states are never visible.
  • lastEventId marker: The export records the maximum event ID at transaction start, providing an auditable consistency boundary without the cost of event replay.

Why not replay events from event_store_t?

  • The projection tables are the replayed event result — re-replaying is redundant.
  • handleEvent() has 120+ event type cases — duplicating that logic in an in-memory replayer is impractical.
  • Event replay would not unlock any consistency benefit beyond what MVCC already provides.

Output Format

{
  "exportVersion": "1.0",
  "sourceHostId": "N2CMw0HGQXeLvC1wBfln2A",
  "lastEventId": "abc123...",
  "exportTs": "2026-04-09T20:00:00Z",
  "tables": {
    "config_t": {
      "count": 5,
      "rows": [
        { "configId": "...", "configName": "...", "configPhase": "...", ... },
        ...
      ]
    },
    "user_t": {
      "count": 12,
      "rows": [
        { "userId": "...", "email": "...", "firstName": "...", ... },
        ...
      ]
    },
    "role_t": { ... },
    "instance_t": { ... },
    ...
  }
}

Key differences from the per-entity promotion export:

AspectPer-Entity Promotion (ExportSnapshot)Global Migration (ExportGlobalSnapshot)
ScopeUser-selected entitiesAll active entities
StructureNested (parent/children/dependencies)Flat per-table
New table supportRequires code changesAutomatic via DatabaseMetaData
Use caseLower env → Higher envFull database migration
OutputEntity-centric JSONTable-centric JSON
Import mechanismSame-instance via promotion_t or Cross-instance via JSONCross-instance via JSON only

Import: Event-Based Migration (Refined in Phase 2.5)

To ensure maximum compatibility and maintain the integrity of the event-sourced system, the global import process follows a 3-step pipeline:

Source DB → Export (Flat JSON) → Convert to Events (Ordered JSON) → Import (Target DB)

1. Snapshot-to-Events Conversion

An intermediate step (ConvertSnapshotToEvents) transforms the flat table-centric snapshot into an ordered JSON array of CloudEvents. This format is 100% compatible with the existing event-importer CLI tool (matching the 00-bootstrap.json structure).

2. Topological Sequencing (Dependency Awareness)

Since a full migration often involves complex relationships, the converter is “Relationship Aware.” It uses DatabaseMetaData.getImportedKeys() to dynamically discover parent→child dependencies.

  • Topological Sort: It implements Kahn’s algorithm to order events such that parent entities (e.g., Org, Host, User, Role) are processed before their children (e.g., UserHost, RoleUser, AuthProviderClient).
  • Dynamic: This approach handles new tables and FK constraints automatically without requiring code changes to a “hard-coded” dependency list.
3. Batch Replay & Reconciliation

The import handler performs a batch insertion of these generated events into event_store_t and outbox_message_t within a single transaction.

  • Nonce Re-calculation: Nonces are re-calculated on the target system during import to ensure uniqueness.
  • Automatic Projections: Inserting into the outbox triggers the DbEventConsumerStartupHook to rebuild all materialized projection tables on the target system.

Service API Contract

  • Export:

    • Handler: GlobalSnapshotExport (user-query)
    • Service ID: lightapi.net/user/exportGlobalSnapshot/0.1.0
    • Request: { "sourceHostId": "...", "entityTypes": [...] }
    • Response: Canonical snapshot JSON (flat tables)

  • Convert (New):

    • Handler: ConvertSnapshotToEvents (user-query)
    • Service ID: lightapi.net/user/convertSnapshotToEvents/0.1.0
    • Request: { "snapshot": "...", "targetHostId": "...", "adminUserId": "..." }
    • Response: JSON array of ordered CloudEvents (event-importer compatible)

  • Import:

    • Handler: GlobalSnapshotImport (user-command)
    • Service ID: lightapi.net/user/importGlobalSnapshot/0.1.0
    • Request: { "targetHostId": "...", "snapshot": "...", "entityTypes": [...] }
    • Response: { "imported": 42, "total": 42 }

Implementation Phases (Updated)

  1. Phase 1 – UI Foundation: Create promotion pages, sidebar menu entry. (Completed)
  2. Phase 2 – Global Export: Implement dynamic table discovery via JDBC metadata. (Completed)
  3. Phase 2.5 – Global Migration Step: Implement Topological Sorting and Snapshot-to-Events conversion for CLI compatibility. (Completed)
  4. Phase 3 – Entity Promotion (Selective): Implement recursive bundling for user-selected entities (e.g., Instance export).
  5. Phase 4 – Same-Instance Tracking: Integrate promotion_t tracking for in-DB moves. (Not completed; history/detail handlers currently return placeholder data until the promotion tables and persistence are added.)