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).

Option 1: Application-Layer Simulation (Fast, Recommended for Planning)

• 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.

Option 2: Transaction Rollback (Robust, Recommended for Validation)

• 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”. Before append, revalidate every planned aggregate version in the canonical event store. A changed version makes the persisted plan stale and execution fails closed; the operator must run dry-run again. The event-store and outbox writes, item statuses, and promotion status are committed atomically.

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

• Product Version exports include the full supported child sets.
• Product Version planning fetches the complete target child sets and records target-only rows as DELETE candidates.
• Apply the same explicit orphan-boundary contract to each additional selective entity type before enabling its execution.

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 persist the authoritative plan and its items. The canonical DDL is portal-db/postgres/ddl.sql; deployment init.sql files are generated from it. Existing databases must apply patch_20260828_01_product_version_promotion.sql followed by patch_20260828_02_promotion_lifecycle.sql before the services are deployed.

promotion_t stores the immutable source snapshot and digest, plan version and summary, dependency blockers, append transaction, projection lifecycle, timestamps, and failure diagnostics. promotion_item_t stores deterministic plan order, aggregate identity, expected and observed projection versions, source/target snapshots, diff, event identity, and per-item status. promotion_recovery_t is an append-only audit ledger for recheck, reconcile, and replan requests.

The append lifecycle starts with PLANNED or BLOCKED, then reaches APPEND_ACCEPTED. The independent projection lifecycle is NOT_STARTED, PENDING, COMPLETED, FAILED, or TIMED_OUT. A promotion advances from APPEND_ACCEPTED to the terminal promotion status COMPLETED, FAILED, or TIMED_OUT after a durable evidence check. APPEND_ACCEPTED still means only that the event-store/outbox transaction committed.

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: A persisted, server-authoritative diff plan with summary counts and per-item actions. For Platform, Pipeline, and Product Version, executable is true only when dependency and identity checks pass.

{
  "promotionId": "...",
  "planVersion": 1,
  "promotionStatus": "PLANNED",
  "executable": true,
  "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 plans, 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 execution is enabled for platform, pipeline, and product_version. Execution consumes the stored plan by promotionId; the client snapshot is never the execution authority. Pipeline planning requires its referenced Platform to exist on the target, so the supported dependency order is Platform, then Pipeline, then Product Version. Other entity types remain review-only and fail closed.

• Service: user
• Action: importExecute
• Request Data:
  • targetHostId (UUID) – The host to apply changes to.
  • snapshot (Object) – Required only for global snapshots containing tables.
  • promotionId (UUID) – Required for Platform, Pipeline, and Product Version selective execution; returned by importDryRun.
  • orphanAction (String) – "keep" (default), "delete" selected orphanItemIds, or "sync" all target-only items.
  • orphanItemIds (Array<UUID>) – Required selections for delete; rejected if an ID is not a DELETE item in the plan.
• Response: Global snapshots return their import counts. Selective execution returns promotionStatus: "APPEND_ACCEPTED", transactionId, appended, and idempotentReplay. A no-op plan can return COMPLETED immediately.

Promotion Recovery (Command)

• Service: user
• Action: promotionRecovery
• Request: promotionId plus recoveryAction (RECHECK, RECONCILE, or REPLAN).
• RECHECK: Locks the promotion, correlates each event with any open event_failure_* record, reads the target projection row, and compares its active state and monotonic aggregate_version with the stored expected version.
• RECONCILE: Performs the same evidence check and returns actionable replay/corrective-event guidance. It never appends another transaction.
• REPLAN: Creates a new plan from the immutable stored source snapshot and links it through supersedes_promotion_id. It never executes that plan automatically.

All three actions require portal.w, record the actor and outcome in promotion_recovery_t, and are safe under repeated or concurrent requests. Projection timeout defaults to five minutes and can be raised with PROMOTION_PROJECTION_TIMEOUT_SECONDS (minimum 30 seconds).

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: Platform, Pipeline, and Product Version plans can be executed when executable=true. The UI supports keeping all orphans, deleting selected orphans, or strict synchronization. Other selective entity plans remain disabled. Global migration snapshots bypass the selective dry-run plan and call globalSnapshotImport directly.

PromotionHistory.tsx (/app/promotion/history)

A standard MaterialReactTable listing past promotions with append and projection status. Pending rows are rechecked every five seconds through the audited recovery command; an operator can also refresh explicitly.

PromotionDiffView.tsx (/app/promotion/diff)

Displays detailed promotion metadata, projection timestamps and failure diagnostics, expected versus observed item versions, and the recovery controls. Pending detail views follow the promotion to a terminal state every five seconds.

Selective Promotion Delivery Phases

1. P0 – Contract and safety boundary: Platform, Pipeline, and Product Version are executable selective types; execution is plan-ID based, idempotent, and fail-closed. (Implemented.)
2. P1 – Durable plan/history: Add promotion tables and real history/detail persistence. (Implemented.)
3. P2 – Selective normalization: Diff Platform and Pipeline roots plus the Product Version root and five supported child collections, count child actions, validate natural identities and Platform/Pipeline dependencies, and record expected aggregate versions. (Implemented.)
4. P3 – Event materialization and UI: Generate ordered Platform, Pipeline, and Product Version domain events, atomically append event store/outbox records with status updates, and enable guarded UI execution/orphan selection. (Implemented; deployment confirmation pending.)
5. P4 – Projection completion: Track event-linked failure evidence and per-row projection convergence, persist terminal state/timestamps, and let the UI follow pending work. (Implemented.)
6. P5 – Operational recovery: Persist append/stale-plan failures and provide audited recheck, non-appending reconcile guidance, and linked replan controls. Immutable events are corrected through the existing exact-replay or corrective-event workflows. (Implemented.)
7. P6 – Qualification and expansion: PostgreSQL gates cover fresh/upgrade schema parity, completion, consumer failure, timeout, duplicate delivery, and concurrent rechecks. Broader entity types remain disabled until their contracts pass the inventory below. (Implemented for the enabled types.)

P6 Release Gates and Entity Inventory

Release qualification is fail-closed:

• run-product-version-promotion-schema-gate.sh must pass against a disposable PostgreSQL database, including applying both patches twice and proving fresh/upgrade parity.
• PromotionPersistencePostgresTest must pass completion, open consumer failure, timeout, repeated delivery, and concurrent recheck cases with PROMOTION_TEST_JDBC_URL configured.
• The existing P0-P3 tests must prove all three orphan policies, stale target versions, single accepted transaction identity, and Platform -> Pipeline -> Product Version dependency order.
• A 10,000-item Product Version qualification fixture must keep dry-run and recheck below 10 seconds each and a 100-row history page below 2 seconds on the release PostgreSQL profile. Any regression above either limit blocks release.
• Alert when a promotion remains PENDING for two minutes, reaches FAILED, or reaches the five-minute default TIMED_OUT deadline. Roll back the service release when failures exceed 1% of executed promotions in 15 minutes or any append/status atomicity test fails. Database rollback is forward-only: retain immutable events and apply a corrective event or approved exact replay.

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_recovery_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:

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): Platform, Pipeline, and Product Version P0-P3 are implemented; other entity types remain dry-run only pending P6 contracts.
5. Phase 4 – Same-Instance Tracking: Platform, Pipeline, and Product Version plans, items, history, append acceptance, projection completion, and audited recovery are persisted.