Build CQRS projections and read models

CQRS separates the model that accepts changes from the models that answer queries. In a Flowcore application, the write side validates a command and appends a fact. Consumers turn those facts into PostgreSQL tables, search indexes, aggregates, or other read models.

The event history remains durable. A projection is an optimized interpretation of that history and should be replaceable when requirements or handler logic change.

Build read models for specific questions

An order-detail page, a support timeline, and a monthly revenue report need different shapes of data. Trying to serve them all from one generic table usually produces complicated queries and fragile rebuilds.

Create focused projections. An entity projection represents current state. A timeline projection preserves ordered activity. A search projection denormalizes filterable text. An aggregate projection maintains counts or totals.

Several projections can consume the same event without coupling their schemas. Each should have a clear owner, query use case, and freshness expectation.

Make every handler idempotent

Flowcore delivers at least once, so the same event can reach a handler again after a timeout or restart. The second delivery must not duplicate rows, increment a total twice, or repeat an external action.

A common pattern stores a processed-event marker and the projection update in one database transaction:

await db.transaction(async (tx) => {
  const claimed = await tx.insert(processedEvents)
    .values({
      projection: "orders-v1",
      eventId: event.eventId,
    })
    .onConflictDoNothing()
    .returning({ eventId: processedEvents.eventId })
 
  if (claimed.length === 0) return
 
  await tx.insert(orders)
    .values({
      id: event.payload.orderId,
      customerId: event.payload.customerId,
      status: "placed",
      totalMinor: event.payload.totalMinor,
      sourceEventId: event.eventId,
    })
    .onConflictDoUpdate({
      target: orders.id,
      set: {
        status: "placed",
        totalMinor: event.payload.totalMinor,
        sourceEventId: event.eventId,
      },
    })
})

The transaction matters. If the marker commits but the projection update fails, a retry may incorrectly skip required work. An in-memory deduplication set is also insufficient because it disappears on restart and is not shared by replicas.

Preserve lineage

Store the source event ID on important projection rows. Depending on the use case, also store the event type, valid time, projection version, and projected timestamp.

Lineage helps support teams answer why a row has its current value. It also helps a rebuild determine which handler version produced the state and whether an affected event range was processed.

Do not copy the entire event envelope into every row. Keep the fields needed for traceability and query behavior, and retrieve the authorized event from Flowcore when deeper investigation is required.

Handle ordering deliberately

Do not assume global ordering across every event in a data core. If a projection needs ordering for one entity, use the ordering guarantees and identifiers available for that event path and model stale updates explicitly.

Valid time can help when an older business fact arrives after a newer one. A current-state projection may ignore an update whose valid time is older than the state already applied, while a timeline projection may retain both facts.

Avoid rejecting every event that references an entity not yet projected. In eventually consistent systems, the related event may arrive shortly afterward. Decide whether to upsert partial state, defer processing, or fail and retry based on the domain requirement.

Keep queries on the read side

User-facing requests should query the model built for them. Replaying raw events during every page load is slow, expensive, and makes availability depend on historical scans.

Use indexed projection tables for operational APIs, a search engine for text retrieval, and a warehouse-oriented model for analytics. These models may have different freshness targets while sharing the same source facts.

The write API can return the accepted event ID before every projection is current. If the product needs read-your-write behavior, update a local projection in the command boundary or wait for a narrowly defined confirmation with a clear timeout. Do not claim system-wide consistency after ingestion alone.

Rebuild without rewriting history

Projection code changes over time. When a handler bug or schema redesign makes current state unreliable, fix the handler and rebuild the read model from retained events.

The safest pattern is a shadow rebuild. Create a new projection version, replay the required event range into it, compare counts and representative records, and then switch the query path. Keep the old model available briefly for rollback.

A smaller issue may only require truncating one affected partition or replaying a bounded event window. Define the event types, start position, expected record count, and verification method before starting.

Do not delete or mutate Flowcore history to repair a projection. The durable event is evidence of what happened; the projection is the part that can be replaced.

Prevent side effects during a rebuild

A projection handler should ideally change only the projection it owns. If it also sends email or calls a partner, replay can repeat those actions.

Separate external side effects into another consumer where possible. If separation is not practical, provide an explicit rebuild mode that disables outbound work while preserving database updates, and verify that the mode cannot be enabled accidentally during normal delivery.

Test behavior that replay depends on

Projection tests should deliver the same event twice, deliver related events out of the expected order, and retry after a simulated transaction failure. A small historical fixture can prove that a fresh projection reaches the expected final state.

Contract tests confirm that payloads are valid. Projection tests confirm that valid history produces correct query state. Both are necessary.

Observe projection freshness

Track the latest successfully processed event time and handler duration for each critical projection. Compare projection counts with source or business expectations after imports and replays.

A low error rate does not prove correctness if no events are being processed. Alert when a projection stops advancing while matching events continue to enter Flowcore.

CQRS adds operational responsibility, so use it where independent read models, audit history, or replay justify that responsibility. Start with one projection and establish reliable delivery and rebuild procedures before expanding the pattern.

Continue with Migrate systems onto Flowcore v2 when moving existing services and data into this model.