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
01 Change
A reviewer changes an application's status in the portal, and a React Query mutation sends the matching command.
02 Pipeline
The MediatR pipeline validates and logs the command before its handler runs.
03 Commit
The handler updates the application and its queue row in one SQL Server transaction. Both commit, or neither does.
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.05 Query
Each queue page is a MediatR query that Dapper runs against the queue table on the primary database, never a lagging replica.
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
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.
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.
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.