CQRS Read Model Microservices: Building Cross-Service Query Views

The cross-service query problem: an order-history view

A customer opens "My orders" and expects one list: each order with its line items, the name and address on file, payment status, and the current shipment location. In a microservice system those facts live in four places. Orders owns line items, Customers owns profiles, Payments owns authorizations and refunds, and Shipping owns tracking. No single service can answer the query, so something has to join across boundaries.

There are three common ways to do that join.

The availability cost of composition is easy to underestimate. As an illustrative estimate, if four independent services each run at 99.9%, the composed call succeeds about 99.6% of the time (0.999⁴). That is roughly four times the downtime before you count retries and timeouts.

Composition is still often the cheaper choice. It works well when the view reads a handful of entities by ID, when each upstream service is fast and stable, and when short-TTL caching of slow-changing data such as customer profiles removes most of the fan-out. A read model earns its cost when you need filtering, sorting, or pagination across fields owned by different services, because composition cannot do that without fetching far too much data.

What a service-owned read model is and how it works

A read model is a denormalized table, document or index shaped for one query. For order history, it might be one row per order that carries the customer name, line items, payment status and shipment state, with indexes on the fields users filter and sort by. The services that own those facts publish domain events. A projector consumes the events and writes them into the view. No request ever crosses a service boundary at query time, because the join has already been done.

Copy Orders svc     Payments svc    Shipping svc
 [DB+outbox]    [DB+outbox]     [DB / CDC]
      \              |              /
       \             |             /
        v            v            v
       +---------------------------+
       |      message broker       |
       +---------------------------+
                     |
                     v
          +---------------------+
          |  projector (owned   |
          |  by consumer team)  |
          +---------------------+
                     |
                     v
          +---------------------+
          |  read store         |<--- order-history API
          +---------------------+

The producers own their events. The team that serves the query owns everything below the broker. That team decides the schema, how long data is kept, which indexes exist, and what freshness and availability targets the view must meet. Because the view has one owner, it can change shape whenever the query changes. Adding a column means updating the projector and running a replay, and no other team needs to agree to it.

A shared reporting database works the other way. Many teams write into it or query it directly, so its schema becomes a contract that nobody can change safely. Retention ends up set by whichever consumer needs the most history. When the view is stale or broken, no single team is responsible for fixing it.

Copy PRODUCER-OWNED: each team owns its tables and the events it publishes
 +--------------+   +--------------+   +--------------+
 | Orders svc   |   | Payments svc |   | Shipping svc |
 | DB + outbox  |   | DB + outbox  |   | DB + CDC     |
 +------+-------+   +------+-------+   +------+-------+
        |                  |                  |
        | OrderPlaced      | PaymentCaptured  | ShipmentSent
        v                  v                  v
 +------------------------------------------------------+
 |                    message broker                    |
 +--------------------------+---------------------------+
                            |
============================|============================
 CONSUMER-OWNED: order-history team owns schema, retention, SLOs
                            v
               +-------------------------+
               | projector               |
               | upsert by order_id      |
               | replayable from start   |
               +------------+------------+
                            |
                            v
               +-------------------------+      +-----------------+
               | read store              |<-----| order-history   |
               | 1 row per order:        |      | API (query only)|
               | customer, lines,        |      +-----------------+
               | payment, shipment       |
               +-------------------------+

Producer services publish domain events through an outbox or CDC to a broker, and everything below the broker (projector, read store and query API) belongs to the single team that serves the order-history query. Event names are illustrative.

Designing events for projection and getting them out reliably

A thin notification event ("order 4471 changed") forces the projector to call back into the owning service for current state. That reintroduces the synchronous coupling the read model was meant to remove, and under load the callbacks arrive after later changes, so the projector cannot tell which state it fetched. Event-carried state transfer puts the relevant state in the event, so the projector can build the view from the stream alone.

Every event a projector consumes needs five fields:

• Event ID, so duplicates can be discarded.
• Entity key, which identifies the row being updated.
• Entity version, a monotonic per-entity counter that detects reordering and gaps.
• Event time, used for staleness reporting.
• State payload, the fields the view needs.

Treat each published schema as a versioned public contract. Additive changes such as new optional fields are safe. Renames, removals and changes in a field's meaning require a new event version, and the old version must run alongside it while consumers migrate.

Getting events out reliably

When a service writes its database and publishes to a broker as two separate steps, a crash between them loses or invents an event. This is the dual-write problem. Two designs avoid it:

With raw CDC, the projector ends up parsing another team's column changes. Add a translation step that maps table rows to contract events before anything downstream consumes them.

Copy-- PostgreSQL 13+ (gen_random_uuid is built in). Columns mirror the event contract, not the orders table.
CREATE TABLE outbox (
  seq            bigint GENERATED ALWAYS AS IDENTITY, -- relay scan order only
  event_id       uuid PRIMARY KEY DEFAULT gen_random_uuid(), -- projector dedupe key
  event_type     text NOT NULL,        -- versioned contract name
  entity_key     text NOT NULL,        -- view row to update
  entity_version bigint NOT NULL,      -- per-entity counter, detects reorder/gaps
  event_time     timestamptz NOT NULL DEFAULT now(), -- for staleness reporting
  payload        jsonb NOT NULL,       -- state the view needs
  published_at   timestamptz           -- set by the relay after the broker acks
);

-- State change and event commit together, or neither does: no dual write.
BEGIN;
WITH changed AS (
  UPDATE orders
     SET status = 'shipped', version = version + 1
   WHERE order_id = 4471
  RETURNING order_id, version, status, customer_id, total_cents
)
INSERT INTO outbox (event_type, entity_key, entity_version, payload)
SELECT 'OrderStatusChanged.v2', order_id::text, version,
       -- Payload field names are the contract, so internal columns can be renamed freely
       jsonb_build_object('orderId', order_id, 'status', status,
                          'customerId', customer_id, 'totalCents', total_cents)
  FROM changed;
COMMIT;

-- Relay: claim unpublished rows without blocking other relay instances.
-- Delivery is at-least-once. Projectors dedupe on event_id and order by entity_version.
SELECT event_id, event_type, entity_key, entity_version, event_time, payload
  FROM outbox
 WHERE published_at IS NULL
 ORDER BY seq
 LIMIT 100
   FOR UPDATE SKIP LOCKED;

A PostgreSQL transactional outbox writes the order update and its versioned event in one transaction. A relay then claims unpublished rows to send to the broker.

How to implement an idempotent, order-tolerant projector

Brokers that guarantee at-least-once delivery will redeliver after a consumer crash, a rebalance, or a timeout. A projector that blindly applies every message will double-count line items or reset a status that has already moved forward. Idempotency is a correctness requirement for the projector.

Write every change as an upsert. In PostgreSQL that is INSERT ... ON CONFLICT (order_id) DO UPDATE, and it lets the first event for an order create the row no matter which service sent it. Guard each column group with a version check. The update for shipping fields should apply only when the incoming shipping_version is greater than the stored one. A replayed or older event then matches zero rows and does nothing. If an event carries no usable version, record its event ID in a processed-events table instead and skip any ID already present.

Where the read store supports transactions, write the consumer offset in the same transaction as the view update. Committing the offset separately leaves a window in which a crash replays work that has already been applied, or skips work that never landed.

Brokers usually guarantee order only within a partition or key. Order, payment and shipping events come from different services, so they can arrive in any order relative to each other. Keep columns owned by other services nullable until their events arrive, and expose a completeness flag. The API can then hide incomplete rows or label them as pending, rather than showing half-built data as final.

Copy-- Read model: columns owned by other services stay NULL until their events arrive.
CREATE TABLE order_view (
  order_id          uuid PRIMARY KEY,
  order_status      text,    -- owned by the order service
  order_version     bigint,
  payment_status    text,    -- owned by the payment service
  payment_version   bigint,
  shipping_status   text,    -- owned by the shipping service
  shipping_version  bigint,
  -- Lets the API hide or label rows that are not fully built yet.
  completeness boolean GENERATED ALWAYS AS (
    order_status IS NOT NULL AND payment_status IS NOT NULL AND shipping_status IS NOT NULL
  ) STORED
);

CREATE TABLE consumer_offsets (
  consumer_group text, topic text, partition int, next_offset bigint,
  PRIMARY KEY (consumer_group, topic, partition)
);

-- Handling one ShippingUpdated event. View change and offset commit happen atomically.
BEGIN;

-- The first event for an order creates the row, whichever service sent it.
-- A replayed or older event fails the WHERE guard, so it updates zero rows.
INSERT INTO order_view (order_id, shipping_status, shipping_version)
VALUES ($1, $2, $3)
ON CONFLICT (order_id) DO UPDATE
  SET shipping_status  = EXCLUDED.shipping_status,
      shipping_version = EXCLUDED.shipping_version
  WHERE order_view.shipping_version IS NULL
     OR order_view.shipping_version < EXCLUDED.shipping_version;

-- Commit the checkpoint in the same transaction; GREATEST keeps it from moving backwards.
INSERT INTO consumer_offsets VALUES ('order-view-projector', 'shipping-events', $4, $5)
ON CONFLICT (consumer_group, topic, partition) DO UPDATE
  SET next_offset = GREATEST(consumer_offsets.next_offset, EXCLUDED.next_offset);

COMMIT;

A PostgreSQL projector step that upserts one service's column group under a per-entity version guard, keeps other services' columns nullable behind a generated completeness flag, and commits the consumer offset in the same transaction as the view update.

Bootstrapping, rebuilding and choosing the read store

When a new view needs history the log no longer holds, for example because the broker's retention window has already expired, use snapshot-plus-tail. Ask the owning service for a consistent export tagged with the log position it reflects. Load the export, then consume events from that position onward. Some events will overlap with the snapshot, and the idempotent projector absorbs them. The export endpoint is the hard part. Agree on it with the owning team before you need it.

Rebuilds follow a blue/green pattern. Project into a fresh table such as order_history_v2, replay until lag is near zero, and then switch reads by repointing a view, alias or config value. Keep the old table until the new one has been verified, so rollback is a single pointer change.

Make rebuildability a day-one requirement. Projector bugs and schema changes are routine. If you can replay, a fix is a redeploy. If you cannot, every fix becomes a hand-written data repair against production.

Choose the store by query shape:

A single service can project the same events into several of these stores. A customer's order list and the support team's free-text search have different access patterns. If you force both onto one schema, both get worse. Separate projections can each be tuned and rebuilt on their own schedule.

Copy-- Blue/green rebuild in PostgreSQL: project into a fresh table, then swap a view pointer.

-- 1. Fresh target table. last_position records the log offset of the last applied event.
CREATE TABLE order_history_v2 (
  order_id      text PRIMARY KEY,
  customer_id   text NOT NULL,
  status        text NOT NULL,
  total_cents   bigint NOT NULL,
  updated_at    timestamptz NOT NULL,
  last_position bigint NOT NULL
);
CREATE INDEX ON order_history_v2 (customer_id, updated_at DESC);

-- 2. Idempotent apply, used for both snapshot rows and tailed events.
--    Events at or below the stored position are ignored, so a replay
--    that overlaps the snapshot cannot overwrite newer state.
INSERT INTO order_history_v2
  (order_id, customer_id, status, total_cents, updated_at, last_position)
VALUES ('ord-1042', 'cus-77', 'shipped', 4599, now(), 81234)
ON CONFLICT (order_id) DO UPDATE
  SET status        = EXCLUDED.status,
      total_cents   = EXCLUDED.total_cents,
      updated_at    = EXCLUDED.updated_at,
      last_position = EXCLUDED.last_position
  WHERE order_history_v2.last_position < EXCLUDED.last_position;

-- 3. When lag is near zero and the data has been verified, repoint readers.
--    DDL is transactional in PostgreSQL, so readers see the old or the new table, never neither.
BEGIN;
DROP VIEW IF EXISTS order_history;
CREATE VIEW order_history AS SELECT * FROM order_history_v1;
COMMIT;

-- Rollback is the same swap pointed back at order_history_v1.
-- Drop v1 only after v2 has served traffic cleanly.

A PostgreSQL blue/green rebuild. Rows from the snapshot and the tailed events go into a fresh versioned table through an upsert guarded by log position, which absorbs duplicates. Reads then move to the new table by redefining a view inside one transaction.

Making staleness visible and operating projections in production

Separate projections also lag independently, so each one needs its own staleness signal.

Measuring and exposing lag

Record two timestamps per event: when the source service committed it (event time) and when the projector applied it (apply time). Publish the difference as a per-projection metric. Consumer offset lag alone can mislead: a projector can be "caught up" on its queue while the outbox relay upstream is stalled. Alert when lag stays above a threshold for several minutes, not on single spikes.

Read-your-own-writes

When a write succeeds, return the resulting version or position to the client. The read endpoint accepts it and waits briefly for the projection to reach that version. If it doesn't, the endpoint either reads that one record from the owning service or returns the stale view with a "last updated" time. A visible timestamp turns "my order vanished" into "this list is a few seconds behind."

Poison messages and schema changes

One malformed event must not block a partition indefinitely. After bounded retries, move it to a dead-letter queue and record which projection and which event caused it. Before replaying, decide whether skipping it breaks ordering for that entity. Evolve schemas additively: new optional fields first, and remove fields only after every consumer has stopped reading them.

Personal data and reconciliation

Copying customer data into many projections multiplies your erasure work. Keep a register of which views hold which fields, and handle erasure events in every projector. Finally, run periodic reconciliation jobs. They sample entities, compare each view against its source of truth, and report drift as a metric.

When to use it, when to avoid it, and the impact on cost and engineering time

Default to API composition and adopt event-fed read models only when a specific query justifies the extra machinery. Avoid them in three cases:

• Flows that need strong consistency. A balance check before payment, or an inventory reservation, must read the owning service's write model synchronously. A lagging view there causes double-spends and oversells.
• A few low-traffic queries. Calling two services and caching the result is simpler, and the cache is the only state you need to invalidate.
• Teams without broker and monitoring capacity. A projection nobody watches will silently serve stale data.

Illustrative estimate (assumed numbers, not a benchmark)

• Read model, first year: B + N × M × 12. Assume a 120 h build (B), 3 projections (N) and 8 h of operations per projection per month (M): 120 + 288 = 408 h.
• Composition, first year: Q × H + I. Assume 40 query changes per year (Q), 6 h of cross-team coordination per change (H) and 60 h of dependency incidents (I): 240 + 60 = 300 h.

With these inputs, composition is cheaper. Read models start to pay off when query churn (Q) or coordination cost (H) grows, or when projections are reused across several queries, which spreads N × M over more value.

How OneEnv can help

Disclosure: OneEnv is the product behind this blog.

OneEnv (oneenv.io) is a workspace-based governance platform for the things services share — environment variables, database structure, API routes and validation rules. A change to a shared value is staged on a branch, reviewed once, and then every affected service explicitly approves or rejects it before it goes live, so you can see who a change touches before it ships instead of finding out in production.

Applied to cqrs read model microservices, this is where it helps:

Saves time

• Every service that inherits a shared value is listed in one place, so nobody searches repos and chats to find out who is affected.
• One review per branch replaces coordinating each team separately; each service still keeps its own approve/reject.
• Reviewers see the exact diff and the list of affected services up front, so approval is a decision rather than an investigation.

Saves cost

• Fewer production incidents and rollbacks caused by a shared value, route or column changing under a consumer unannounced.
• Less duplicated configuration to maintain: one shared definition inherited by many services.
• Per-service approve/reject means a bad shared value can be refused where it would break something, before it is live.
• OneEnv has a free plan (1 workspace, unlimited services and members, full governance), so trying it costs nothing.

These are qualitative benefits, not measured results — how much you save depends on how many services share the value.

Want to try it? Start at oneenv.io.