Database per Service Reporting: Cross-Service Queries with CDC

Why cross-service reporting breaks with a database per service

In a database-per-service architecture, each service owns its data store outright. Orders, payments, inventory and customers each keep their tables private, and other services reach that data only through the owning service's API or the events it publishes. That boundary is the reason the pattern exists. A team can change its schema, switch storage engines or split a table without coordinating with anyone else, because nobody else depends on the internals.

Reporting cuts across those boundaries. A question like "revenue by customer segment for orders shipped late last quarter" needs rows from four services, joined and aggregated. No single database can answer it.

The obvious shortcut is to point a BI tool at every service database, or to join across read replicas in a federated query engine. It works on day one. After that, each dashboard becomes an undeclared consumer of a private schema. When the orders team renames status to fulfillment_state, or moves line items into a JSON column, the migration passes their tests and then breaks the finance report that nobody told them about. Teams learn that every schema change carries hidden risk, so they slow down or stop refactoring. You have rebuilt the shared-database coupling the architecture was meant to remove, and the dependency is now invisible.

There are also operational costs. Analytical scans compete with production traffic on replicas. Cross-database joins have no consistent snapshot, so the totals can disagree depending on replica lag.

When API composition is acceptable, and when it is not

API composition answers a query by calling each owning service's API and joining the responses in memory, usually in a gateway or a dedicated query service. It works when the result set is small and bounded before the fan-out begins. "Show order 8812 with its customer and shipment status" is a good fit: three lookups by key, each served by an index, and the result fits on one screen.

It fails when the join key set is unbounded. Aggregations need every row, not one page. Historical questions need data the services may have already archived or overwritten. Pagination across services breaks because sorting by a field owned by service B means fetching all of service A's candidates first.

Illustrative estimate (assumed inputs): a full-year revenue report covers 50,000 orders. The orders API returns 100 per page, so 500 pages. Each page requires one batched call each to the orders, customers and products services. That is 1 call × 3 services × 500 pages = 1,500 calls. At an assumed 50 ms per call, run sequentially, that is about 75 seconds, and no single snapshot covers the whole run. A warehouse answers the same question with one query.

Use fan-out for operational lookups. For everything else, build a read model, as the next section describes.

How the publish-and-project pattern works

The pattern splits reporting into three stages, each with one owner.

Publish. Each service remains the only writer to its own database. When its state changes, it emits a change event, either by writing to an outbox table in the same transaction or by letting a log-based CDC connector read the database's write-ahead or replication log. Section 4 compares the two. Either way, events land in a durable broker, partitioned by entity key so that changes to one order or customer arrive in order. The service never learns who consumes them, which is the point: reporting needs should not shape its schema or its release schedule.

Project. Consumers read those topics and build a denormalized, cross-service view in a separate store. Joins that used to cross network boundaries at query time happen once, at write time, inside the projection.

Query. Reports read only from that store. They never call service APIs or connect to service databases, so a heavy quarter-end query cannot degrade checkout latency.

Two kinds of read model

The same event stream usually feeds two different targets, and they should not share a store:

• Operational read models are CQRS projections or materialized views kept seconds behind the source. They serve product features such as order-history screens or support dashboards, and they are shaped around specific access patterns.
• Analytical reporting goes to a warehouse or lakehouse, loaded in micro-batches or on a schedule. It keeps full history, favors columnar scans, and tolerates minutes or hours of lag in exchange for cheap aggregation.

Keeping the two apart lets each one choose its own freshness, retention and schema-evolution rules.

Copy+-------------+   +-------------+   +-------------+
| Orders svc  |   | Billing svc |   | Users svc   |  1. PUBLISH
| (orders DB) |   |(billing DB) |   | (users DB)  |
+------+------+   +------+------+   +------+------+
       | outbox          | log-based CDC   | outbox or CDC
       v                 v                 v
+-------------------------------------------------+
|    Durable broker, partitioned by entity key    |
+---------+----------------------------+----------+
          |                            |
          v                            v
+---------+----------+       +---------+----------+
| Stream projector   |       | Batch loader       |  2. PROJECT
| (CQRS projection)  |       | (on a schedule)    |
+---------+----------+       +---------+----------+
          |                            |
          v                            v
+---------+----------+       +---------+----------+
| Operational read   |       | Warehouse or       |
| model / mat. view  |       | lakehouse, full    |
| lag: seconds       |       | history, columnar  |
|                    |       | lag: minutes-hours |
+---------+----------+       +---------+----------+
          ^ reads                      ^ reads
          |                            |
+---------+----------+       +---------+----------+
| Product features:  |       | Reports and BI     |  3. QUERY
| order history,     |       | (no service DB or  |
| support dashboards |       | API access)        |
+--------------------+       +--------------------+

Services publish changes through an outbox or log-based CDC into a key-partitioned broker, where separate pipelines project them into an operational read model and an analytical warehouse or lakehouse. Product features and reports each read only from their own store.

Log-based CDC vs the transactional outbox

Two extraction methods feed the reporting side, and they differ in what they emit. Log-based CDC reads the database's replication log and publishes every committed row change. The transactional outbox has the service write an explicit event alongside its business data, so the published record states what happened in business terms.

A minimal outbox table holds event_id, aggregate_id, event_type, payload, and created_at. The service inserts the order row and the outbox row in the same database transaction, so both commit or neither does. A relay process, often CDC pointed only at the outbox table, then publishes the outbox rows.

Raw CDC turns every internal table into a public interface. If the owning team renames cust_ref to customer_id, reporting breaks, and the team that made the change may not know anyone depended on the column. Put curated topics or a translation layer between internal tables and consumers. The service team owns that published shape.

Treat published data as a versioned contract. Enforce it with a schema registry or compatibility checks in CI that reject breaking changes. For the outbox, include identifiers and timestamps generously, because backfilling a field you never emitted is far harder than ignoring one you did.

Copy-- PostgreSQL: minimal outbox table, separate from internal tables.
-- The payload is the published contract; schema_version lets consumers
-- detect which contract version they are reading.
CREATE TABLE outbox (
  event_id       uuid        PRIMARY KEY,
  aggregate_id   text        NOT NULL,          -- e.g. the order id
  event_type     text        NOT NULL,          -- e.g. 'OrderPlaced'
  schema_version int         NOT NULL DEFAULT 1,
  payload        jsonb       NOT NULL,
  created_at     timestamptz NOT NULL DEFAULT now()
);

-- Business row and event commit together or not at all.
BEGIN;

INSERT INTO orders (order_id, cust_ref, total_cents, status)
VALUES ('ord-1042', 'c-7781', 4599, 'PLACED');

-- The payload uses contract names (customer_id), not the internal
-- column name (cust_ref), so a later internal rename stays private.
-- Identifiers and timestamps are included generously for analysts.
INSERT INTO outbox (event_id, aggregate_id, event_type, payload)
VALUES (
  gen_random_uuid(),
  'ord-1042',
  'OrderPlaced',
  jsonb_build_object(
    'order_id',    'ord-1042',
    'customer_id', 'c-7781',
    'total_cents', 4599,
    'currency',    'USD',
    'status',      'PLACED',
    'placed_at',   now()
  )
);

COMMIT;
-- A relay (often CDC scoped to the outbox table only) publishes these rows.

A PostgreSQL outbox table and a single transaction that writes the order row and its OrderPlaced event together. The event payload uses contract field names that stay stable when internal columns are renamed.

Building an idempotent reporting model

Treat the reporting store as two layers. The first is a raw landing table that appends every change event exactly as received: source ID, source version (an LSN or a per-row version counter), operation type, payload, and ingestion time. Nothing is updated or discarded here. This layer is your recovery tool. If a transformation bug corrupts a fact table, you truncate it and replay from raw instead of asking every service to re-emit history. Keep raw data at least as long as your longest plausible bug-detection window.

Upserts keyed on ID and version

The second layer holds fact and dimension tables built by a merge keyed on source ID. It writes a row only when the incoming version is greater than the stored one. That single condition handles duplicates, replays and out-of-order delivery together: a stale or repeated event matches the key, fails the version check, and changes nothing. Totals stay correct no matter how many times a batch reruns. Late events still land, but they never overwrite newer state.

Deletes

Model deletes as tombstones. Set a deleted_at flag and have reports filter on it, so a replayed delete stays idempotent. A hard delete that a service performs without emitting an event will never reach you. Detect these with the reconciliation process described in the next section.

Snapshot handover

Record the source's log position before the initial snapshot starts. Load the snapshot with each row's version, then stream from the recorded position. Overlapping events are absorbed by the version check, so the handover leaves no gap and counts nothing twice.

Copy-- Idempotent merge from the raw landing table into fact_orders (PostgreSQL).
-- Rerunning any window is safe: duplicates, replays and stale events change nothing.
-- Snapshot rows (op = 'r') and streamed rows share one version scheme, so the
-- overlap at handover is resolved by the same version check.
INSERT INTO fact_orders AS f
  (order_id, source_version, customer_id, amount, status, deleted_at, loaded_at)
SELECT
  source_id,
  source_version,
  (payload->>'customer_id')::bigint,
  (payload->>'amount')::numeric(12,2),
  payload->>'status',
  -- Deletes become tombstones. For op = 'd' the payload holds the before-image.
  CASE WHEN op = 'd' THEN ingested_at END,
  now()
FROM (
  -- Keep only the newest event per ID in this batch.
  -- ON CONFLICT cannot update the same target row twice in one statement.
  SELECT DISTINCT ON (source_id)
         source_id, source_version, op, payload, ingested_at
  FROM raw_order_events
  WHERE ingested_at >= TIMESTAMPTZ '2026-10-24 00:00:00+00'  -- batch or replay window
  ORDER BY source_id, source_version DESC
) AS latest
ON CONFLICT (order_id) DO UPDATE SET
  source_version = EXCLUDED.source_version,
  customer_id    = EXCLUDED.customer_id,
  amount         = EXCLUDED.amount,
  status         = EXCLUDED.status,
  deleted_at     = EXCLUDED.deleted_at,  -- a newer re-insert clears the tombstone
  loaded_at      = EXCLUDED.loaded_at
-- Write only when the event is newer than the stored state. Late or repeated
-- events fail this check and leave the row untouched.
WHERE EXCLUDED.source_version > f.source_version;

-- Reports read only live rows.
-- SELECT status, sum(amount) FROM fact_orders WHERE deleted_at IS NULL GROUP BY status;

A PostgreSQL upsert that collapses each batch of raw change events to the newest version per source ID, records deletes as tombstones, and updates a row only when the incoming version is higher, so replays, duplicates and the snapshot-to-stream overlap do not inflate totals.

Freshness, reconciliation and failure modes to plan for

Treat freshness as a stated requirement, not an assumption. Label each report with its expected lag, such as "orders: under five minutes; refunds: hourly", and show a last-updated timestamp for every source feeding it. Readers then know whether a missing order is late or actually absent.

Eventual consistency has a subtler cost. Because streams advance independently, a report can briefly join a shipment to an order whose payment has not arrived yet. That combination never existed at any single moment. Dashboards can tolerate it. Financial close usually cannot. For close, define a cutoff, for example events committed before midnight UTC, and report from a snapshot taken only after every stream has passed that point.

Reconciliation and monitoring

Run scheduled jobs that compare row counts and key totals, such as order value per day, between each source and the warehouse. Small, stable differences usually point to a filtering bug. Growing differences mean events are being lost.

Monitor consumer lag per stream. Also alert when a stream stops producing events entirely, because an idle stream and a stopped one show the same low lag.

Source-side and governance risks

An abandoned replication slot, or retention set longer than disk capacity, makes the source keep its log until the disk fills. Alert on retained log size. Schema changes such as a renamed column or a changed type can break consumers silently, so validate payloads against a contract in CI.

Finally, the warehouse copies personal data from every service. To govern it:

• Mask or tokenize personal fields during projection.
• Apply access controls per report.
• Propagate deletions as events so erasure requests reach every copy.

Who should adopt it, cost, and engineering time

Adopt the pipeline when reports routinely join data from three or more services, when schema migrations have already broken reports, or when analysts wait on backend engineers for every new query. Avoid it if you run a handful of services with modest data volumes. A read replica per service, or API composition for the few cross-service views you need, costs far less and does not require anyone to be on call for a broker.

The ongoing cost is mostly ownership, not hardware. Brokers, connectors, the warehouse and transformation jobs each need upgrades and monitoring, and someone has to be paged when lag grows.

Illustrative estimate (assumed numbers, not a benchmark). The pipeline pays off when monthly hours saved are greater than or equal to monthly upkeep hours.

Hours saved = (reporting outages × hours per outage) + (new reports × hours saved per report) + fan-out maintenance hours

Assume 2 outages × 6 h, plus 4 reports × 8 h, plus 10 h of fan-out maintenance. That gives 54 h/month saved, against an assumed 40 h/month of platform upkeep. At one new report a month and no outages, the result reverses.

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 database per service reporting, 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.