Acumatica CQRS Pattern — A Complete Guide

Full CQRS — separate write and read stores kept in sync by events — is the wrong tool for an Acumatica instance, and you will never build it. But the idea underneath it is sound and worth borrowing: the shape of data that is efficient to write is rarely the shape that is efficient to read. In Acumatica you already have the read side built for you. Graphs and BQL are your command model; Generic Inquiries, OData, and analytical DACs are your query model. The skill is knowing which side a given problem belongs on, so you stop running heavy reporting reads through transactional graphs.

The two models you already have

On the command side, a graph like SOOrderEntry loads a document into its per-graph cache, runs validation and defaulting events, and persists. That machinery exists to protect invariants on write — it is deliberately expensive, and it is scoped to one document at a time. Using it to answer "show me every open order over 5,000 across all customers" is a category error: you would be instantiating graphs and firing RowSelected on rows nobody is editing.

The query side is Generic Inquiries and the OData/REST feeds built on them. A GI is a saved, joined, filterable projection over DACs — a read model in everything but name. It never fires business logic, it can be indexed and cached at the SQL level, and it is exactly where read-heavy questions belong.

Don't answer read questions through the write model

The most common performance mistake I see is a customization that loops a PXSelect inside a processing graph to build a summary. It works on ten rows and dies on ten thousand, because every iteration drags a row through the cache and its events. When the question is "read and aggregate," go straight to the data with a projection DAC or a GI, not through the entry graph.

// A read model: a projection that joins and aggregates for querying,
// never used to insert or update.
[PXProjection(typeof(Select5<SOOrder,
    InnerJoin<Customer, On<Customer.bAccountID, Equal<SOOrder.customerID>>>,
    Where<SOOrder.status, Equal<SOOrderStatus.open>>,
    Aggregate<GroupBy<Customer.customerClassID,
        Sum<SOOrder.orderTotal>>>>))]
public class OpenOrderByClass : PX.Data.IBqlTable
{
    [PXString(IsKey = true)]
    [PXDBString(BqlField = typeof(Customer.customerClassID))]
    public string CustomerClassID { get; set; }
    public abstract class customerClassID
        : PX.Data.BQL.BqlString.Field<customerClassID> { }

    [PXDBDecimal(BqlField = typeof(SOOrder.orderTotal))]
    public decimal? OrderTotal { get; set; }
    public abstract class orderTotal
        : PX.Data.BQL.BqlDecimal.Field<orderTotal> { }
}

A PXProjection DAC is the read model in the platform's own vocabulary: it compiles to a single SQL query, aggregates in the database, and returns shaped rows with no per-document cache overhead. That is the "Q" of CQRS expressed in idiomatic Acumatica.

Keep the command side narrow and correct

Because you are no longer overloading the graph with read duties, the write side can stay focused on the one thing it is good at: enforcing invariants on a single document as it is created or changed. Keep validation in the events, keep it about this document, and let cross-document questions live on the read side.

Staleness is the trade-off you accept

Real CQRS accepts eventual consistency between the write store and the read store. In Acumatica the read model is usually the same database, so a GI is consistent on the next query — but the moment you push a read model further out (a nightly OData pull into a warehouse, a cached materialized summary, a search index fed by a Business Event) you inherit the same staleness question. Decide explicitly how fresh each read model must be, and do not pretend a warehouse extract is real-time.

When to actually materialize a read model

Most of the time a GI or projection querying live tables is enough. You only reach for a genuinely separate, materialized read model when the read is both expensive and hot — a dashboard hit hundreds of times an hour over a query that scans millions of rows. Then a scheduled process that writes a summary DAC, or a Business Event feeding an external store, earns its complexity. Below that bar, materialization is just a cache you now have to invalidate, and the platform's live projections are the lazier, correct default.

Wrapping up

You are not going to run textbook CQRS inside Acumatica, and you should not try. What transfers is the discipline of separating write shapes from read shapes: protect invariants document-by-document on the command side, answer set-based questions with projections and Generic Inquiries on the query side, and only materialize a dedicated read model when a hot, heavy query proves it needs one.