CQRS for B2B event streams

Split the write model (incoming EDI commands, sagas, ACK journals) from the read model (partner KPIs, tracking, fiscal archives) so each view is shaped to its real usage — not a schema compromise.

Problem

A B2B hub ingests dozens of EDI flows (ORDERS, DESADV, INVOIC) and must serve multiple audiences with conflicting demands: the ERP expects a strict canonical format, the partner portal wants enriched tracking via joins, BI requires hourly aggregates, fiscal audit needs raw timestamped messages kept for 10 years. A single normalised relational schema becomes a permanent compromise where every query slows down, every new KPI breaks an index, every schema migration terrifies ops. Flat CRUD on a shared table does not scale to B2B flows in 2026.

Forces

• Independent evolution: tracking portal needs change faster than EDI validator needs.
• Read-heavy performance: 10x to 1000x more reads (BI, dashboards, polling) than writes (ingested messages).
• Fiscal compliance: the 10-year archive requires immutable append-only, incompatible with a continuously mutating normalised schema.
• Eventual consistency acceptable: tracking KPIs do not need to be strictly consistent at ingestion time t; a few seconds lag is acceptable.
• Team decoupling: the portal team iterates on its PostgreSQL view without touching the Kafka ingestion pipeline maintained by the platform team.

Solution

Split the model in two. The command side receives incoming EDI messages, applies validation, persists an append-only journal of business events (OrderReceived, OrderValidated, InvoiceAcked), and publishes them on Kafka. The query side consumes this stream to materialise specialised views: a PostgreSQL table for the portal, a ClickHouse view for BI, an Elasticsearch index for full-text search, an S3 bucket partitioned by month for fiscal archive. Each projection owns its own consistency and can be rebuilt by replaying the event journal from t=0.

Structure

                  ┌─────── COMMAND side ────────┐
   AS4 / AS2  ──► │  Validate EDI message       │
   PEPPOL     ──► │  Persist event in journal   │ ──┐
                  │  Publish to Kafka           │   │
                  └─────────────────────────────┘   │
                                                     │
                              Kafka topic            │
                       ┌───── edi.events ───────────┘
                       │
                       ▼
       ┌───────────────┼─────────────────┐
       │               │                 │
       ▼               ▼                 ▼
  Projection A    Projection B     Projection C
  PostgreSQL      ClickHouse       S3 Parquet
  (portal)        (BI/KPI)         (10y archive)

EDI implementation

On a modern EDI hub, the command journal can be a PostgreSQL append-only events table with columns id (uuid), aggregate_id (varchar), type (varchar), payload (jsonb), created_at (timestamptz), partition_key. Debezium captures inserts via the WAL and publishes them to a Kafka topic compacted by partner. Each consumer materialises its own projection:

-- COMMAND side: append-only journal
CREATE TABLE edi_events (
  id           UUID PRIMARY KEY,
  aggregate_id VARCHAR(64) NOT NULL,  -- e.g. "ORDER-2026-12345"
  partner_id   VARCHAR(32) NOT NULL,
  type         VARCHAR(64) NOT NULL,  -- e.g. "OrderReceived"
  payload      JSONB NOT NULL,
  occurred_at  TIMESTAMPTZ NOT NULL,
  recorded_at  TIMESTAMPTZ DEFAULT now(),
  schema_version SMALLINT NOT NULL
);
CREATE INDEX ON edi_events (aggregate_id, occurred_at);

-- QUERY side: portal projection (idempotent)
INSERT INTO portal_orders (order_id, partner, status, last_updated)
VALUES ($1, $2, $3, $4)
ON CONFLICT (order_id) DO UPDATE
  SET status = EXCLUDED.status,
      last_updated = EXCLUDED.last_updated
  WHERE portal_orders.last_updated < EXCLUDED.last_updated;

Projection idempotency is critical: Kafka delivers at-least-once, so each consumer must handle duplicates via a WHERE guard or via a consumed events table (see Transactional Inbox). Full replay must be possible: truncate the projection, reset the Kafka offset to 0, let the consumer replay.

Anti-patterns

• Enforcing strong consistency between command and query — kills the whole point of the pattern and reintroduces the 2PC you were trying to avoid.
• Sharing the database between command and query — each projection should have its own storage with its own indexing strategy.
• Doing CQRS without Event Sourcing then regretting it — without an append-only journal, you cannot replay to rebuild a broken projection.
• Reusing the Kafka payload as a "source of truth" to answer audits — Kafka is not designed for 10-year retention, archive to S3/Glacier.
• Modelling commands as HTTP REST resources (PUT/POST on /orders/123) — loses traceability of business intent (who did what, when, why).

Related patterns

• CQRS (architectural view) — the generic version of the pattern in DDD literature.
• Event Sourcing with append-only EDI log — often paired with CQRS to replay projections.
• Transactional Outbox — to publish events to Kafka atomically with the DB commit.
• Data Mesh — each projection becomes a data product exposed to its consumer.

Sources

• Young G. — CQRS Documents, 2010. The founding PDF where Greg Young formalised the pattern. cqrs.files.wordpress.com
• Fowler M. — CQRS, martinfowler.com, July 2011. The reference page that spread the term in the DDD community. martinfowler.com/bliki/CQRS.html
• Microsoft Patterns & Practices — CQRS Journey, 2012. A full book documenting a production implementation based on Azure Service Bus and Event Store. learn.microsoft.com
• Kleppmann M. — Designing Data-Intensive Applications, O'Reilly 2017, ch. 11 ("Stream Processing").
• Confluent — Building a Microservices Ecosystem with Kafka Streams and KSQL, Ben Stopford, O'Reilly 2018. The reference manual for event-driven CQRS implementations with Kafka.