Skip to content

Design study

CQRS Service with an Optimized Read Side

A conceptual design for the same lender's review queue: CQRS commands, a read model shaped for filtered browsing, and a frontend that mirrors both.

An independent conceptual design study. The business, scenarios and architecture are illustrative and do not describe a production system.

  • .NET
  • MediatR
  • Dapper
  • SQL Server
  • React Query

Context

The same invented lender. Underwriters work through a shared queue of applications: they filter by status, product and risk band, sort by submission time, open an application, add notes and change its status. Reads far outnumber writes.

Experience behind it

Professional experience: CQRS with MediatR, Dapper and SQL Server stored procedures, and React Query. The queue read model, its same-transaction update and keyset pagination are design choices of this study.

Challenge

Keep deep, filtered browsing of a growing queue fast while many reviewers work at once. Writes must stay simple, validated and consistent, and a reviewer who changes a status must see that change straight away, never an older one.

Constraints

  • Many reviewers work on the queue at the same time.
  • Reads far outnumber writes.
  • A reviewer must see their own change immediately after saving.
  • The queue keeps growing.

Architecture

A status change travels from the portal as a command through the MediatR pipeline into one SQL Server transaction that updates the application and its queue row. The portal then refetches its queue pages, which Dapper reads from the queue table on the primary database with keyset pagination.
  1. 01 Change

    A reviewer changes an application's status in the portal, and a React Query mutation sends the matching command.

  2. 02 Pipeline

    The MediatR pipeline validates and logs the command before its handler runs.

  3. 03 Commit

    The handler updates the application and its queue row in one SQL Server transaction. Both commit, or neither does.

  4. 04 Refresh

    The portal applies the returned state and invalidates every cached queue page with refetchType: 'all'. The change shows as saved only after they refetch.

  5. 05 Query

    Each queue page is a MediatR query that Dapper runs against the queue table on the primary database, never a lagging replica.

  6. 06 Page

    Keyset pagination returns, newest first, the rows after the last SubmittedAt and Id seen, through a composite index that matches the filters and the sort.

Key decisions

  1. CQRS through MediatR, with a read model updated in the same transaction

    Commands and queries are separate MediatR requests; pipeline behaviours validate and log before every handler. A command writes the normalized tables and the application's queue row in one SQL Server transaction, so the read model is never behind.

    Trade-off
    Every write touches two tables, and a schema change affects both.
    Alternative
    An asynchronous projection. The queue would be eventually consistent, and reviewers could see a status older than their own change.
  2. Keyset pagination with Dapper

    Dapper reads pages newest first by SubmittedAt, then Id, a unique and immutable key. The next page is SubmittedAt < @a OR (SubmittedAt = @a AND Id < @b), served by a composite index that leads with the filter columns, then SubmittedAt and Id.

    Trade-off
    No jumping to page N, and total counts stay expensive.
    Alternative
    OFFSET paging. Pages shift as rows arrive or change status, so reviewers skip or repeat applications.
  3. A React Query frontend that mirrors CQRS

    Each queue page is a query keyed by its filters and cursor, with placeholderData set to keepPreviousData, so paging never blanks the list. Mutations mirror commands. On success, the portal applies the returned state, invalidates every cached queue page with refetchType: 'all', active or not, and shows the change as saved only after they refetch.

    Trade-off
    Cache-key discipline rests on conventions and tests.

Guarantees

  • The application and its queue row commit or roll back together, in one transaction.
  • Rows present when browsing starts are never skipped or repeated, unless their filter membership changes.
  • After saving, the reviewer's own tab shows the change: reads always use the primary.

Limits of the design

Limits: every new filter combination needs a matching index, and full-text search or many independent sort orders would outgrow a single queue table; a search index would replace it first. Each save refetches every cached queue page, which costs more as reviewers page deeper.