CQRS in a Modular Monolith: Read Models in One Shared Database

What CQRS means in a modular monolith

Command Query Responsibility Segregation (CQRS) means that writes and reads use separate models and separate code paths. A command such as "place order" or "cancel shipment" goes through a domain model that enforces invariants and changes state. A query such as "show this customer's open orders" goes through a read model built only to answer that question quickly. The two sides can share a database. They should not share types or assumptions about shape.

CQRS is often confused with two other ideas. It does not require event sourcing: the command side can store current state in ordinary tables, and read models can be derived from those tables or from domain events. It also does not require multiple databases. In a modular monolith, both sides normally live in one database, often in separate schemas or table sets.

On the command side, each module owns its domain model: aggregates, validation rules and transactional boundaries. On the read side, models are shaped for consumers. A read model might be a flattened table for a listing screen, a denormalized row for an API response, or a materialized view that joins data from several modules into one queryable shape.

The pattern helps teams with read-heavy screens, complex filterable listings, or a growing number of queries that reach across module boundaries. Its value is mainly organizational. Query separation is a discipline in the codebase, not an infrastructure change. It stops reporting needs from bending the domain model and makes cross-module reads explicit rather than incidental.

Module boundaries inside one database

The rule that makes this work is ownership. Each module owns its write tables and any read-model tables it maintains for its own screens. No other module reads those tables directly. A module that needs another module's data has two options. It can call that module's published query interface, or it can consume a read model built specifically for it.

Copy+-------------------+        +-------------------+
| orders module     |        | billing module    |
|  write: orders,   |        |  write: invoices, |
|         lines     |        |         payments  |
|  read:  order_    |        |  read:  invoice_  |
|         list      |        |         status    |
|  outbox ----------+--+  +--+---------- outbox |
+-------------------+  |  |  +-------------------+
                       v  v
              +---------------------+
              | query module        |
              |  read: order_       |
              |        summary      |
              |  (cross-module,     |
              |   built from events)|
              +---------------------+

A shared database makes violations cheap. A join across orders and invoices is one line of SQL. It works on the first try and passes review because it looks like ordinary data access. Each such join quietly turns a private schema into a public contract, and the coupling is invisible until someone renames a column.

You can enforce the boundary at three levels:

• Schema-per-module. Give each module its own schema, such as orders.* or billing.*. This makes ownership visible in every query, and cross-schema references stand out.
• Database permissions. Each module connects with a role that can read and write only its own schema. The query module gets read access to the published read-model tables only. A violation then fails at runtime instead of slipping through.
• Architecture tests. These run in CI and fail the build when one module's code references another module's internal repositories, entities, or table names. They catch the problem before deployment, which permissions cannot do.

Permissions and architecture tests complement each other. Use both where you can.

Copy+------------------------ one shared database -------------------------+
|                                                                      |
|  +-- schema: orders ------+            +-- schema: billing -----+    |
|  | write: orders, lines   |            | write: invoices,       |    |
|  | read:  order_list      |            |        payments        |    |
|  | outbox: orders_outbox  |            | read:  invoice_status  |    |
|  +-----------+------------+            | outbox: billing_outbox |    |
|              |                         +-----------+------------+    |
|              | events                              | events          |
|              v                                     v                 |
|  +-- schema: query -----------------------------------------------+  |
|  | read: order_summary (cross-module, built from events)          |  |
|  | role: SELECT on published read models only                     |  |
|  +----------------------------------------------------------------+  |
|                                                                      |
|  orders.* --X--> billing.invoices  (cross-schema join: denied)       |
+----------------------------------------------------------------------+

Each module owns a schema with its write tables, read models and outbox; the query module builds cross-module read models from published events, and direct reads of another module's private tables are denied.

How read models can be implemented, from coupled to decoupled

Each option below moves the read side one step further from the write tables. Each step buys independence and costs more machinery. They are listed from most coupled to most decoupled, and every one short of the last is a legitimate alternative to full CQRS.

Query services and plain views keep a single source of truth. The joins still run at read time, though, so they stop helping once queries span many modules' tables.

Materialized views look like free precomputation. In practice, refresh usually recomputes the whole result. On large tables that costs a lot, and depending on the database, refreshing can block readers or compete for locks with writers. The projection logic also sits in SQL, outside any module, so module boundaries and tests don't cover it.

Same-transaction projection tables keep reads consistent. In exchange, every command pays for the extra writes, and the write path is tied to the read schema.

Async projections decouple the two sides. The costs move to delivery guarantees, idempotent handlers and making lag visible.

Synchronous vs asynchronous projections

Same-transaction projections

Updating the read model inside the command's transaction gives read-your-writes consistency for free. If the projection fails, the whole write rolls back, so there is nothing to reconcile. The price is that every write pays for every read model. Hot rows, such as a per-customer counter, serialize concurrent commands and increase lock contention. A bug in one projection also blocks business writes, so a broken reporting view can stop checkout.

Async projections with an outbox

The command writes its business rows and an outbox row in the same transaction. A consumer reads the outbox afterwards, either in-process or as a background worker against the same database. This gives at-least-once delivery, so handlers must be idempotent. Each projection stores its own checkpoint, the last outbox ID it applied, and advances it in the same transaction as the read-model update. A replayed event then becomes a no-op.

Ordering matters per aggregate, not globally. Process events for one order in sequence and parallelize across orders. Retry transient failures with backoff. After a fixed number of attempts, move the event to a dead-letter table so one poison message cannot stall the projection.

Monitor lag as the age of the oldest unprocessed event for each projection, and alert on it. In the UI, show an "as of" timestamp or a pending state on stale data. Alternatively, return the version from the command and let the client poll until the read model catches up to it.

Copy-- Write side (PostgreSQL): business row and outbox row commit or roll back together.
BEGIN;
UPDATE orders
   SET status = 'placed', version = version + 1
 WHERE id = 'ord_7f3a' AND version = 4;           -- optimistic concurrency check
INSERT INTO outbox (aggregate_id, aggregate_version, event_type, payload)
VALUES ('ord_7f3a', 5, 'OrderPlaced',
        '{"customer_id": "cus_19c2", "total_cents": 4250}'::jsonb);
COMMIT;

-- Read side: the handler for outbox event id 1042 in the 'order_summary' projection.
-- The checkpoint advance and the read-model update run as one transaction,
-- so a redelivered event changes nothing.
BEGIN;
WITH advanced AS (
  UPDATE projection_checkpoints
     SET last_outbox_id = 1042, updated_at = now()
   WHERE projection = 'order_summary'
     AND last_outbox_id < 1042                    -- no row if already applied
  RETURNING 1
)
INSERT INTO order_summary (order_id, customer_id, status, total_cents, version)
SELECT 'ord_7f3a', 'cus_19c2', 'placed', 4250, 5
 WHERE EXISTS (SELECT 1 FROM advanced)            -- skip on replay
ON CONFLICT (order_id) DO UPDATE
   SET status      = EXCLUDED.status,
       total_cents = EXCLUDED.total_cents,
       version     = EXCLUDED.version
 WHERE order_summary.version < EXCLUDED.version;  -- per-aggregate ordering guard
COMMIT;

-- Caveat: sequence-generated IDs can commit out of order across concurrent writers.
-- A max-ID checkpoint can skip a late-committing event. Either have the consumer
-- read only IDs older than all in-flight writers, or track applied IDs per projection.

A PostgreSQL sketch that inserts an outbox row in the same transaction as the business write, then applies the event to a read model and advances that projection's checkpoint atomically, so a replayed event becomes a no-op.

Worked example: an order summary read model

Consider an "Orders" screen that lists order number, customer name, total and shipping status. Three modules own the source data. Ordering writes ordering.orders and ordering.order_lines. Customers writes customers.customers. Shipping writes shipping.shipments. A query that joins across those schemas would couple the listing to three internal models. Any change to one of them could break the screen.

Instead, the query module owns reporting.order_summary_v1. It has one row per order, with the customer name and shipping status copied in, and it is indexed for the screen's filters. A projection handler consumes OrderPlaced, CustomerRenamed and ShipmentStatusChanged. Each upsert is keyed by order ID and guarded by the event's sequence number. That guard makes replays and out-of-order delivery harmless.

Now product wants an "estimated delivery" column. Don't alter the live table in place. Create order_summary_v2 and point a new projection version at it. Backfill it in one of two ways:

• From the write tables, if the current state is enough.
• From outbox history, if the column depends on past events.

Run the backfill in small batches with pauses between them so it doesn't compete with checkout traffic for locks and I/O. Once v2 has caught up with the live event stream, switch the query module's reads to it and drop v1.

This approach works because the read model is disposable, derived data. You can always rebuild it, so it never needs a hand-written migration that could silently corrupt it. Rebuildable doesn't mean always correct, though. Run a scheduled reconciliation job that compares row counts and sampled fields against the write tables. When the job finds drift, raise an alert and trigger a rebuild before users notice wrong data.

Copy-- PostgreSQL 11+. v2 is built next to the live v1 table; v1 keeps serving reads.
CREATE TABLE reporting.order_summary_v2 (
  order_id           bigint PRIMARY KEY,
  order_number       text   NOT NULL,
  customer_name      text   NOT NULL,
  total_cents        bigint NOT NULL,
  shipping_status    text   NOT NULL,
  estimated_delivery date,             -- the new column
  last_event_seq     bigint NOT NULL   -- replay / out-of-order guard
);

-- Projection v2, ShipmentStatusChanged: older or duplicate events match no rows.
UPDATE reporting.order_summary_v2
   SET shipping_status = $2, estimated_delivery = $3, last_event_seq = $4
 WHERE order_id = $1 AND last_event_seq < $4;

-- Throttled backfill from the write tables, run outside an explicit transaction.
-- Each batch commits on its own and then sleeps, so locks stay short and I/O is spread out.
-- seq 0 plus DO NOTHING means rows the live projection already wrote take precedence.
DO $$
DECLARE last_id bigint := 0; n int;
BEGIN
  LOOP
    WITH batch AS (
      SELECT o.id, o.order_number, c.name, o.total_cents,
             coalesce(sh.status, 'pending') AS status, sh.estimated_delivery
        FROM ordering.orders o
        JOIN customers.customers c ON c.id = o.customer_id
        LEFT JOIN shipping.shipments sh ON sh.order_id = o.id
       WHERE o.id > last_id ORDER BY o.id LIMIT 1000
    ), ins AS (
      INSERT INTO reporting.order_summary_v2
      SELECT id, order_number, name, total_cents, status, estimated_delivery, 0 FROM batch
      ON CONFLICT (order_id) DO NOTHING
    )
    SELECT count(*), max(id) INTO n, last_id FROM batch;
    EXIT WHEN n = 0;
    COMMIT;                  -- release locks after each batch
    PERFORM pg_sleep(0.2);   -- leave room for checkout traffic
  END LOOP;
END $$;

This example creates a versioned read-model table, applies a projection update guarded by sequence number, and backfills the table from the module write tables in small committed batches with a pause between each, before reads are switched from v1 to v2.

When to use it, when to avoid it, and when to split the read store

Reconciliation adds an ongoing cost, and that cost is one reason not to build read models everywhere. A module that stores customer addresses and serves them back through a form does not need a projection. Its reads and writes use the same shape, so one model with direct queries is easier to understand. Applying CQRS uniformly across every module is a common way to over-engineer a modular monolith. Each extra read model adds a projection to keep correct and a rebuild path to test.

Use a read model when a query joins across several modules, aggregates heavily, or is requested much more often than its source data changes. The decision to move reads into a separate store or replica should come from measured signals, not from the pattern itself:

If none of these signals is present, keep reads in the shared database.

Your existing projections also show how far a module is from extraction. Async projections fed by events already define an integration contract, because the event schema is the only thing the consumer relies on. Same-transaction projections and shared database views mark coupling that still remains. Each of them has to be replaced with an event before the module can move to its own database.

Impact on cost and engineering time

The costs fall into three areas.

Concepts and code. Every read model adds an event contract, a projection handler, a schema migration, and tests that cover replay as well as the normal path.

Storage. Denormalized rows duplicate data that already exists elsewhere. The outbox table also keeps growing until a job prunes it.

Operations. The outbox relay and the projection runner are processes that need deployment, restarts, and alerting. Rebuild tooling (truncate, replay from a checkpoint, swap) often gets written in a hurry during an incident. Lag monitoring is the piece most often underbuilt. Without a metric for the age of the oldest unprocessed event, a stalled projector is only discovered when users report stale screens.

Illustrative estimate (assumed numbers, not a benchmark):

extra storage ≈ (read rows × bytes per row) + (events per day × bytes per event × retention days)

Assume 2,000,000 order summaries at 600 bytes each, which is about 1.2 GB. Add 50,000 events per day at 1 KB each, kept for 14 days, which is about 0.7 GB. The total is roughly 1.9 GB before indexes. That storage is cheap compared with the engineering hours described above.

The savings come from four places:

• Simpler queries. Query code returns rows already shaped for a screen instead of assembling joins across modules. A table change in one module no longer breaks another module's queries.
• Local eventual consistency. Eventual consistency stays inside one database, one backup, and one deployment, so lag and replay bugs can be debugged locally rather than across a network.
• Cheaper extraction. When a module is extracted, its consumers already depend only on events. The move becomes a relocation rather than a rewrite.

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 modular monolith, 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.
• The dependency graph answers "what does this touch?" in seconds instead of a manual audit.

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.
• You can size a change's blast radius before merging, avoiding surprise cross-team firefighting.
• 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.