Outbox/CDC Integration with Relational Sources, Identity Mapping, and Eventual Consistency

AtlasMart Orders remains authoritative in a relational database while Neo4j powers connected customer/product/order traversals. A service updates SQL and then separately publishes “OrderPaid.” If the process crashes between those operations, SQL says PAID but the graph never receives an event. Reversing the calls merely changes which side can become wrong. The transactional outbox solves the atomicity boundary by storing the business update and an event row in the same relational transaction.

Learning outcomes

Lab contract and assumptions

1. The unsafe dual write

2. Transactional outbox mechanism

The order row and outbox row commit atomically. A separate publisher/CDC connector reads the outbox and emits it to Kafka. If publication repeats, the immutable event_id lets downstream consumers deduplicate. After successful publication, retention/compaction of outbox rows is an operational policy—not part of the business transaction.

BEGIN;
UPDATE orders
SET status = 'PAID', updated_at = CURRENT_TIMESTAMP
WHERE order_id = 'O-2001';

INSERT INTO outbox_events(event_id, aggregate_type, aggregate_id, event_type, schema_version, payload, created_at)
VALUES (
  'evt-o-2001-paid-v1', 'Order', 'O-2001', 'OrderPaid', 1,
  '{"orderId":"O-2001","customerId":"C-2001","status":"PAID"}',
  CURRENT_TIMESTAMP
);
COMMIT;
-- Business row and outbox row are atomic in the relational source transaction.

3. Identity mapping must survive every storage engine

AtlasMart maps SQL order_id → graph OrderReplica.orderId, not SQL row location or Neo4j elementId. The same rule applies to customers/products. Stable constrained keys are the bridge between a relational aggregate and graph projection.

4. Apply semantic events, not raw SQL mutations

OrderPaid describes a business fact. The graph projection can choose its own labels/relationships while retaining source identity and event version. This reduces coupling to source table layouts and makes model refactors explicit.

CYPHER 25
// Parameterized event application on Neo4j target.
MERGE (r:SyncReceipt {eventId:$eventId})
ON CREATE SET r.firstSeenAt = datetime(), r.labTag='ch20'
WITH r
MATCH (o:OrderReplica {orderId:$orderId})
SET o.status = $status,
    o.customerId = $customerId,
    o.schemaVersion = $schemaVersion,
    o.labTag='ch20';
// In production use a transaction function and a receipt check so replay is a no-op,
// rather than mutating again unconditionally.

5. Ordering and concurrency belong to the aggregate contract

If two Order events race—OrderPaid then OrderCancelled—the target needs a source version/sequence or a Kafka partitioning key that preserves per-order order. Global serialization of all orders is unnecessary. Include a monotonically increasing aggregateVersion from the authoritative source when transitions are non-commutative.

{
  "eventId":"evt-o-2001-cancelled-v2",
  "aggregateType":"Order",
  "aggregateId":"O-2001",
  "aggregateVersion":2,
  "eventType":"OrderCancelled",
  "schemaVersion":1,
  "occurredAt":"2026-09-09T18:00:00Z",
  "payload":{"reason":"CUSTOMER_REQUEST"}
}

6. Eventual consistency requires an SLO and reconciliation

Define an SLO such as “99% of Order status changes are queryable in the graph within the agreed product-specific window,” then derive alerts from measured distributions. Do not invent a universal “under one second” target.

7. Backfill without racing the live stream

A safe bootstrap uses a watermark: record source boundary W, export/backfill a consistent snapshot corresponding to W, apply it idempotently, then consume events after W. Exact implementation depends on the relational CDC/outbox technology. If the source cannot give a consistent snapshot + position pair, design overlap and dedupe rather than assuming no gap.

8. Feedback loops and ownership

If graph-derived enrichment must return to the relational system, use a different event type/topic and explicit ownership. Never echo the same Order status field in both directions. Include origin/flowId metadata and reject a flow’s own emissions where bidirectional integration is unavoidable.

9. Production judgment

Summary and next step

The outbox makes event intent durable with the source transaction, while the graph consumer remains idempotent and measurable. Lesson 5 combines Neo4j CDC concepts, simulator failure injection, Kafka/outbox reasoning and backfill into one recovery acceptance test.

Authoritative references

• Current Neo4j versions — Current database release 2026.07.1 and current 5.26 LTS patch 5.26.30.
• CDC introduction — Current CDC availability and purpose; CDC is a change feed, not an exact database-copy mechanism.
• CDC on self-managed Neo4j — Enterprise-only enablement, OFF/DIFF/FULL txLogEnrichment modes, security, retention, disk and unrecorded-change boundaries.
• CDC on Aura — Current AuraDB Business Critical / Virtual Dedicated Cloud enablement, pause/resume and snapshot-reset behavior.
• CDC procedures — db.cdc.earliest/current/query semantics, exclusive cursors, txId/seq, metadata and change identifiers.
• CDC event schema — Current node/relationship event envelope including operation, keys, before/after state and metadata.
• CDC selectors — Server-side entity/operation/label/type/key/property and metadata filtering.
• CDC examples — Official cursor-management examples and empty-result/current-cursor retention guidance.
• CDC backup/restore behavior — Why restores invalidate old change identifiers and require consumer state/backfill/reinitialization planning.
• CDC troubleshooting — Disabled, scan-failure and invalid-identifier error mechanisms including retention and wrong-database cursors.
• CDC known issues — ElementId and change-identifier instability across restore/import/copy/pause-resume operations.
• CDC changelog — Current db.cdc.* namespace and deprecation history of cdc.* procedures.
• Neo4j Connector for Kafka — Current source/sink connector architecture and CDC/query source strategies.
• Kafka source CDC quickstart — Current CDC source configuration, txLogEnrichment prerequisite, event fields, offsets, topics and failure modes.
• Kafka connector installation — Current connector release 5.5.3 and Kafka Connect plugin deployment model.
• Kafka source query quickstart — Community-compatible polling source alternative and its delete/soft-delete limitation.
• Kafka sink CDC quickstart — Neo4j-to-Neo4j CDC sink pattern, source header requirement, key constraints and loop warning.
• Python driver 6.3 API — Official driver 6.3 and Bolt compatibility used for optional application examples.