---
title: "Event Sourcing with CQRS: Build an Audit-Complete Order System for Mobile Backends"
published: true
description: "A hands-on guide to implementing event sourcing with CQRS using PostgreSQL and Kafka — with concrete schemas, snapshotting, and the failure modes that actually bite you in production."
tags: architecture, kotlin, postgresql, api
canonical_url: https://blog.mvpfactory.co/event-sourcing-cqrs-mobile-backends
---
## What We Will Build
By the end of this tutorial, you will have a working event-sourced order system for a mobile backend. We will design a PostgreSQL event store, set up CQRS with purpose-built projections for mobile reads, wire in Kafka for async processing, and add snapshotting so aggregate replay stays fast at scale.
Let me show you a pattern I use in every project that handles orders, payments, or anything with audit requirements.
## Prerequisites
- PostgreSQL 16+
- Apache Kafka (or a managed equivalent like Confluent Cloud)
- Familiarity with SQL and backend API design
- A backend runtime — the examples use Kotlin/JVM, but the schemas and patterns are language-agnostic
## Step 1: Design the Event Store
Forget specialized event store databases. PostgreSQL handles this cleanly up to tens of billions of events with proper indexing.
sql
CREATE TABLE event_store (
event_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
aggregate_id UUID NOT NULL,
aggregate_type VARCHAR(128) NOT NULL,
event_type VARCHAR(256) NOT NULL,
event_data JSONB NOT NULL,
metadata JSONB DEFAULT '{}',
version INTEGER NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
UNIQUE (aggregate_id, version)
);
CREATE INDEX idx_event_store_aggregate ON event_store (aggregate_id, version);
CREATE INDEX idx_event_store_type_time ON event_store (event_type, created_at);
That `UNIQUE (aggregate_id, version)` constraint is doing the heavy lifting — it gives you optimistic concurrency control. Two concurrent commands on the same order will conflict at the database level. No distributed locks needed.
## Step 2: Separate Reads from Writes with CQRS
Here is the minimal setup to get this working. Your command side appends events to `event_store`. Your query side reads from a denormalized projection table built for your mobile API's exact needs:
sql
CREATE TABLE order_projections (
order_id UUID PRIMARY KEY,
user_id UUID NOT NULL,
status VARCHAR(32) NOT NULL,
total_cents BIGINT NOT NULL,
item_count INTEGER NOT NULL,
last_event_version INTEGER NOT NULL,
projected_at TIMESTAMPTZ NOT NULL,
snapshot JSONB NOT NULL
);
Design each projection for a specific screen or endpoint. The whole point of CQRS is that reads and writes have different shapes — lean into it.
## Step 3: Wire Kafka with the Outbox Pattern
Here is the gotcha that will save you hours: **never dual-write** to PostgreSQL and Kafka separately. Write events and an outbox row in a single PostgreSQL transaction, then use a poller or Debezium CDC to push to Kafka.
plaintext
Mobile App → API Gateway → Command Handler → event_store + outbox (single PG transaction)
↓ (CDC / poller)
Kafka Topic: order.events
↓
Projector → order_projections
Notifier → Push Notifications
This eliminates the number one production failure mode in event-sourced systems. The docs do not mention this, but skipping the outbox pattern is how you get ghost events or missing projections that are nearly impossible to debug.
## Step 4: Add Snapshotting
Without snapshots, replaying a 10,000-event aggregate takes ~810ms. With snapshots every 50 events, it drops to ~6ms.
sql
CREATE TABLE aggregate_snapshots (
aggregate_id UUID NOT NULL,
aggregate_type VARCHAR(128) NOT NULL,
version INTEGER NOT NULL,
state JSONB NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
PRIMARY KEY (aggregate_id, version)
);
On aggregate load: fetch the latest snapshot, then replay only events after that version. Generate snapshots asynchronously after command processing — 50 events is a solid threshold.
## Gotchas
1. **"Eventually consistent" means nothing without a number.** Pin down your projection lag target. For mobile clients, under 200ms is a good baseline. Measure it. Alert on it.
2. **Duplicate Kafka publishes will happen.** Make your projectors idempotent by keying on `(aggregate_id, version)`. If you have already projected version 7, skip it.
3. **Never mutate stored events.** When your schema evolves, write upcasters that transform old events at read time. The event log is your source of truth — treat it as sacred.
4. **Projection bugs are not catastrophic — that is the point.** Ship a fix, rebuild the projection from the event log, and move on. This is the superpower you paid for.
5. **Snapshot corruption is recoverable.** Always fall back to full replay if a snapshot fails validation. The full event log is your safety net.
## Conclusion
Event sourcing adds real complexity, and I would not use it for a simple CRUD app. But for domains with audit requirements, complex state transitions, and mobile clients that need fast denormalized reads, the investment compounds over time. Start with the outbox pattern, snapshot aggressively, and build projections for your screens — not your domain model. The cost is upfront; the value is non-linear.
**Resources:**
- [PostgreSQL JSONB documentation](https://www.postgresql.org/docs/current/datatype-json.html)
- [Debezium CDC for the outbox pattern](https://debezium.io/documentation/reference/stable/transformations/outbox-event-router.html)
- [Martin Fowler on Event Sourcing](https://martinfowler.com/eaaDev/EventSourcing.html)
Top comments (0)