Why do database joins and API fan-out fail for cross-service reporting?
The usual first attempt at a report like "revenue by customer segment last quarter" reaches for data where it already lives. The order service owns orders, the customer service owns segments, and the payment service owns settlement status. There are two obvious shortcuts, and both break as the system grows.
Joining across service databases treats another team's schema as a public contract, which it never was. A column rename, a table split, or a move to a different database engine is a routine internal change for the owning team. For your report it is a breaking change, and often a silent one: the query still runs but returns wrong numbers. Heavy analytical queries also compete with transactional traffic on the same database.
Fanning out to APIs at query time respects ownership, but the cost multiplies. Report latency is bounded by the slowest call, and every dependency adds a failure point. As an illustrative estimate, three services each available 99.9% of the time give a combined availability of roughly 99.7%. Pagination is worse. If you filter on one service's field and sort on another's, you must pull complete result sets from both and merge them in memory.
| Approach | Coupling | Failure modes | Scales with result size |
|---|---|---|---|
| Direct DB join | Private schemas | Silent breakage, load on transactional databases | Poorly across engines |
| API fan-out | Public APIs | Latency stacking, partial failures | Poorly with pagination |
Both shortcuts fail for the same reason: the report needs a dataset that no single service owns.
What is a CQRS read model for reporting and how does it work?
A CQRS read model takes that observation literally: it gives reporting its own dataset. A dedicated reporting component owns a separate store with a denormalized schema shaped around the queries reports actually run. If the revenue dashboard groups orders by region and product category, the read model holds one row per order with region and category already attached, so no join is needed at query time. Writes to that store come from only one place: the projection code.
The flow
- Source services commit their own transactions and publish events such as
OrderPlaced,CustomerRelocatedorProductRecategorized. They know nothing about reports. - The broker keeps those events durably and lets consumers read them at their own pace. A slow report rebuild therefore never slows down checkout.
- Projection consumers read each event, look up or update the affected rows, and write the denormalized result.
- The read store serves the queries. Its indexes and partitioning are chosen for reporting, not for transactional writes.
- The report API is the only way consumers reach the data. This lets you change the schema without breaking dashboards.
Ownership
One team should own the component end to end: the projections, the store, the API and the on-call rotation. This is usually a data platform or reporting team. Service teams own their event contracts and nothing else.
That split is where you save engineering time. A new report no longer means asking three teams for database credentials or writing one-off data aggregation code. It means adding a projection, or a query against tables that already exist.
Service teams own their databases and event contracts only
+--------------+ +-----------------+ +-------------------+
| Order svc | | Customer svc | | Catalog svc |
+------+-------+ +--------+--------+ +---------+---------+
| OrderPlaced | CustomerRelocated | ProductRecategorized
v v v
+-------------------------------------------------------------+
| Broker: durable event log; consumers read at own pace |
+------------------------------+------------------------------+
| reporting team owns below
+- - - - - - - - - - - - - - - v - - - - - - - - - - - - - - -+
: +--------------------------+---------------------------+ :
: | Projection consumers | :
: | read event -> look up/update rows -> write result | :
: +--------------------------+---------------------------+ :
: | only writer :
: v :
: +--------------------------+---------------------------+ :
: | Read store (denormalized; schema shaped by queries) | :
: | e.g. one row per order, region + category attached | :
: +--------------------------+---------------------------+ :
: ^ reads :
: +--------------------------+---------------------------+ :
: | Report API: only entry point; decouples schema | :
: +--------------------------+---------------------------+ :
+- - - - - - - - - - - - - - - | - - - - - - - - - - - - - - -+
v
+-------------------------------+
| Dashboards / report consumers |
+-------------------------------+
Source services publish domain events to a durable broker, and a single reporting team owns the projection consumers, the denormalized read store and the report API through which all dashboards query the data.
Should you use domain events or change data capture as the source?
A projection is only as stable as its input, so the source choice matters more than the store.
Domain events are messages a service publishes on purpose, such as OrderPlaced or RefundIssued. The owning team decides what they mean and keeps them stable. Change data capture (CDC) reads the database's replication log and emits every row change. CDC is faster to adopt because no service code changes. The cost is that your projections now depend on table and column names, which are private implementation details. If a team renames a column or splits a table, the projection can keep running while it writes nulls or drops rows. Nothing alerts you. You can contain this by pointing CDC at an outbox table that the service writes in the same transaction, or by mapping raw changes to a published schema before any consumer sees them.
| Domain events | Raw CDC | CDC via outbox | |
|---|---|---|---|
| Adoption effort | High | Low | Medium |
| Coupling to tables | None | Full | None |
| Migration risk | Low | Silent breakage | Low |
Whichever source you use, each event contract needs:
- Stable entity IDs that never get reused.
- A per-entity version or sequence number, so consumers can detect gaps and reordering.
- An event timestamp taken from when the change happened, not when it was published.
- Enough fields to avoid callbacks to the source API.
Evolve contracts by adding optional fields. For breaking changes, introduce a versioned type such as OrderPlaced.v2 and publish both types until consumers migrate. Publishing too little forces callbacks and requests to other teams. Publishing too much freezes internal fields into a public contract.
// Envelope shared by every event the order service publishes.
// Consumers depend on this contract, never on the service's tables.
interface EventEnvelope<TType extends string, TData> {
eventId: string; // unique per event; lets consumers dedupe redeliveries
type: TType; // versioned type name, e.g. "OrderPlaced.v2"
entityId: string; // stable ID, never reused (e.g. "ord_7f3c9a")
entityVersion: number; // increments by 1 per change to this entity;
// a jump from 4 to 6 means a gap, 5 after 6 means reordering
occurredAt: string; // ISO-8601 time the change happened, not publish time
data: TData;
}
// v1: kept unchanged and still published until consumers migrate.
interface OrderPlacedV1Data {
customerId: string;
totalCents: number;
currency: string;
region?: string; // added later as optional: an additive, non-breaking change
}
// v2: line items changed shape, which is breaking, so it gets a new type.
// Line items are included so reporting needs no callback to the orders API.
interface OrderPlacedV2Data {
customerId: string;
currency: string;
totalCents: number;
lines: { sku: string; quantity: number; unitPriceCents: number }[];
region?: string;
// Deliberately absent: internal fields such as fraudScore or warehouseShard.
// Publishing them would freeze implementation details into the contract.
}
type OrderPlacedV1 = EventEnvelope<"OrderPlaced.v1", OrderPlacedV1Data>;
type OrderPlacedV2 = EventEnvelope<"OrderPlaced.v2", OrderPlacedV2Data>;
export type OrderEvent = OrderPlacedV1 | OrderPlacedV2;
A TypeScript event contract with a stable entity ID, a per-entity version, the time the change occurred, and enough data to avoid callbacks, showing an additive optional field and a versioned OrderPlaced.v2 type published alongside v1.
How do you implement idempotent, order-tolerant projections?
Whatever the source, assume every event can arrive twice and some will arrive out of order. Brokers redeliver after consumer crashes, and replays during rebuilds deliberately reprocess history. The projection has to make these cases harmless.
Upsert with a version guard
Key each read-model row by entity ID and store the version of the last event applied to it. Write with an upsert whose update branch only fires when the incoming version is newer. In PostgreSQL this is INSERT ... ON CONFLICT (order_id) DO UPDATE ... WHERE report_orders.version < EXCLUDED.version. A stale or duplicate event then matches zero rows and does nothing. This only works if the producer stamps a monotonic per-entity version, such as an aggregate sequence number or a CDC log position. Wall-clock timestamps from different hosts are not a safe substitute.
Track progress in the same transaction
Commit the consumer offset, or insert the event ID into a processed_events table, inside the same database transaction as the projection write. If the two are committed separately, a crash between them either loses an update or applies it twice. The sequence is: begin, check whether the event is already processed, apply the upsert, record the offset, commit.
Missing parents and partial ordering
An OrderLineAdded can arrive before its OrderPlaced. To handle this, insert a placeholder row flagged incomplete and fill it in when the parent arrives. Alternatively, park the event in a retry table and resolve it later.
Ordering is usually guaranteed only per partition key. A view that joins customers and orders can briefly show an order whose customer row is still out of date. Design reports so they tolerate this lag rather than treating it as corruption.
-- Read model: one row per order, plus the version of the last event applied.
CREATE TABLE report_orders (
order_id uuid PRIMARY KEY,
customer_id uuid NOT NULL,
status text NOT NULL,
total_cents bigint NOT NULL,
version bigint NOT NULL, -- producer's monotonic per-entity sequence
updated_at timestamptz NOT NULL DEFAULT now()
);
-- Dedup ledger, written in the same transaction as the projection.
CREATE TABLE processed_events (
event_id uuid PRIMARY KEY,
processed_at timestamptz NOT NULL DEFAULT now()
);
-- Per event: $1 event_id, $2 order_id, $3 customer_id, $4 status,
-- $5 total_cents, $6 version.
BEGIN;
WITH claimed AS (
-- Returns a row only the first time this event is seen.
INSERT INTO processed_events (event_id) VALUES ($1::uuid)
ON CONFLICT (event_id) DO NOTHING
RETURNING event_id
)
INSERT INTO report_orders (order_id, customer_id, status, total_cents, version)
SELECT $2::uuid, $3::uuid, $4::text, $5::bigint, $6::bigint
FROM claimed -- duplicate event: claimed is empty, nothing is written
ON CONFLICT (order_id) DO UPDATE
SET customer_id = EXCLUDED.customer_id,
status = EXCLUDED.status,
total_cents = EXCLUDED.total_cents,
version = EXCLUDED.version,
updated_at = now()
-- Stale or out-of-order event: zero rows updated, row keeps newer state.
WHERE report_orders.version < EXCLUDED.version;
COMMIT; -- dedup record and projection write become visible together
A PostgreSQL projection write that claims the event ID and applies a version-guarded upsert in one transaction, so redelivered events are skipped and older events cannot overwrite newer state.
How do you backfill, rebuild and choose the store for a read model?
A new read model starts empty, but the services it reads from already hold years of state. Seed it by taking a consistent snapshot of each source and recording the log position the snapshot corresponds to. Load the snapshot, then replay events from that position forward. Because the projections are idempotent, events that overlap the snapshot are harmless.
Event retention decides how much this matters. If the broker keeps only a short window of history, you cannot rebuild from the first event ever published. You need a separate snapshot mechanism, such as a periodic export or a compacted topic, that captures full state together with its position.
Treat every projection as disposable and rebuildable from scratch. Bug fixes, new columns and changed business rules all require reprocessing history, and patching rows in place makes the result impossible to verify. To rebuild without downtime:
- Create a new versioned table, such as
order_summary_v2. - Run the projection into it from the snapshot plus the replay.
- Wait until it catches up to the live position.
- Repoint a view or alias at the new table, then drop the old one.
Readers never see a half-built table.
Choose the store based on the shape of the queries:
| Query shape | Store | Why |
|---|---|---|
| Operational reports, filtered lookups, moderate volume | Relational database with summary or materialized tables | Indexes, joins and transactional upserts suit per-entity updates |
| Large historical scans and aggregations | Columnar analytics store | Compression and column pruning make wide scans cheap |
Keep the projection logic separate from the store. Event handling should produce plain records that a thin writer persists, so you can move to a different store later without rewriting the business rules.
-- PostgreSQL: rebuild a projection into a versioned table, then swap it in.
-- 1. New table built alongside the live one; readers keep using v1.
CREATE TABLE order_summary_v2 (
order_id uuid PRIMARY KEY,
customer_id uuid NOT NULL,
status text NOT NULL,
total_cents bigint NOT NULL,
currency text NOT NULL, -- new column that forced the rebuild
last_position bigint NOT NULL -- log position of the last applied event
);
-- 2. Projector writes are idempotent: replaying events that overlap the
-- snapshot (or are redelivered) cannot move a row backwards.
INSERT INTO order_summary_v2
(order_id, customer_id, status, total_cents, currency, last_position)
VALUES ($1, $2, $3, $4, $5, $6)
ON CONFLICT (order_id) DO UPDATE
SET status = EXCLUDED.status,
total_cents = EXCLUDED.total_cents,
currency = EXCLUDED.currency,
last_position = EXCLUDED.last_position
WHERE order_summary_v2.last_position < EXCLUDED.last_position;
-- 3. Once the v2 projector has caught up to the live position, swap the view.
-- DDL is transactional in PostgreSQL, so readers see either v1 or v2,
-- never a missing or half-built table.
BEGIN;
DROP VIEW order_summary;
CREATE VIEW order_summary AS
SELECT order_id, customer_id, status, total_cents, currency
FROM order_summary_v2;
COMMIT;
-- 4. After stopping the v1 projector and confirming reports look right:
DROP TABLE order_summary_v1;
A PostgreSQL rebuild of a projection into a versioned table, using position-guarded idempotent upserts, followed by an atomic view swap so reporting queries never see a partially built table.
How do you expose staleness and catch silent data drift?
A read model is always behind its sources, so make the lag visible. For each source stream, store the timestamp and offset of the last event the projection applied, in the same transaction as the projected rows so the two cannot disagree. Return that watermark with every report and API response, for example "orders data as of 14:02:11 UTC". A user who sees that the figure is 40 minutes old can decide whether to trust it, and doesn't have to guess.
Alert on lag in two forms: wall-clock lag (now minus last applied event time) and offset lag (latest source offset minus applied offset). Wall-clock lag alone misleads during quiet periods, when no new events arrive even though the projection is healthy.
Lag tells you the projection is running. It does not tell you the projection is correct. A handler that drops an unknown event variant, or misreads a field, keeps advancing its offset. Nothing errors, dashboards stay green, and the first person to notice is a finance analyst whose totals disagree with the billing system.
Run reconciliation as a scheduled job. For each entity type and closed time window, compare row counts, and where feasible checksums or sums of key amounts, between each source and the read model. Report mismatches with the window and entity type so you can replay that slice.
Illustrative estimate: assume 2,000,000 events per day and a bug that misapplies 0.01% of them. That is 200 wrong records per day. Corrupted rows persist, so after 30 days 6,000 records are wrong, and any running totals built on them are off as well.
When should you avoid an event-fed read model, and what are the alternatives?
Avoid the pattern when a query needs real-time correctness. A balance check before a withdrawal, or a stock check before confirming an order, must ask the owning service. A projection that lags by a few seconds can approve an overdraft, and no reconciliation job undoes that after the fact.
Avoid it when only a few low-volume services feed the report. If three services each hold tens of thousands of rows and the business reads the report once a day, a scheduled batch export into a shared reporting database is simpler and easier to debug.
The ongoing cost is easy to underestimate. You run a broker, consumers, a separate store and rebuild tooling. Someone must be on call when a consumer stalls. Every producing team must coordinate before changing an event contract, which slows releases across teams.
Duplicated data also adds a compliance burden. If events carry personal or financial fields, the read model needs its own access controls and retention policy. Deletion requests must reach the read model and its rebuild source, including retained events and snapshots. Otherwise a replay restores data you were required to erase.
| Approach | Freshness | Coupling | Cost | Best fit |
|---|---|---|---|---|
| Direct DB reads | Live | High: schemas become shared contracts | Low at first, then grows with schema breakage | Prototypes, or a single team that owns all the data |
| API composition | Live | Runtime: depends on every service being available | Low infrastructure, high latency | Small result sets, few services |
| Batch export/ETL | Hours | Low | Low to moderate | Daily or weekly reports, few low-volume sources |
| Event-fed read model | Seconds to minutes | Contract-level | High: build and ongoing operation | High-volume, near-real-time analytics across many services |
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 cross-service reporting 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.