“Let users vote online. Show live results.”

That’s it. That’s the requirement.

No billion-row datasets, no distributed consensus, no data science — just one button to vote and one dashboard to see who’s winning.

Looks trivial on paper. Until you build it.

Someone votes twice — from two devices, two networks.
The analytics team wants per-region heatmaps every second.
Mods ask for audit logs of vote retractions.
A candidate wants to trace all votes from a specific mobile network.
A recount triggers a replay storm and wipes your Redis cache mid-event.

And just like that, your “simple app” has turned into a coordination nightmare.

This isn’t a scale problem. It’s a conflict-of-purpose problem.

One side of the system needs to capture truth — vote casting, integrity, traceability.
The other side needs to serve insights fast — live tallies, filters, leaderboards.

Different SLAs. Different access patterns. Different guarantees.

One button says “Vote.” One screen shows “Results.” But behind them? A cold war between consistency and speed.

You didn’t adopt CQRS because you love patterns.
You got there the moment your write and read paths stopped wanting the same thing.

The Write Path – Protecting the Vote

Let’s get one thing straight: writing a vote is not just an insert.

You’re not adding a product to a cart. You’re recording an irreversible, auditable, and potentially contested action that directly impacts public trust.

That means:

One user = one vote (idempotency isn’t optional).
You must know who voted, where they voted from, and when.
Retractions or edits need to be traceable.
Fraud attempts shouldn’t just be blocked — they should leave a trail.

This isn’t about speed. It’s about truth.

✅ Sample Vote Event

This is what gets posted from the UI or app:

{
  "voteId": "VOTE-UUID-1234",
  "voterId": "USR-98213",
  "candidateId": "CAND-45",
  "region": "WestZone",
  "timestamp": "2025-07-08T18:45:00Z"
}

✅ Write-Side DB Schema

The write model has to capture the full story — not just who won.

Table: vote_records
- vote_id        (PK)
- voter_id
- candidate_id
- region
- timestamp
- ip_address
- user_agent
- is_retracted   (boolean)

Each field earns its place:

vote_id: Uniquely identifies the vote — used for idempotency.
voter_id + region: Also enforced as a composite unique key, to prevent double-voting.
ip_address, user_agent: Inputs for fraud detection — not prevention.
is_retracted: Soft delete flag. Never remove data from the source of truth.

No derived fields. No counters. That’s for the read model.

Why This Can’t Be Fast

Sure, you can bulk insert votes. But what happens when:

A user submits the same vote twice due to flaky Wi-Fi?
Two microservices race to log the same event?
You need to roll back a fraudulent batch?

You need deduplication, locking (optimistic or otherwise), and trace-level logs.
Speed takes a back seat — because if you lose integrity here, the read side doesn’t matter.

DB Choices for the Write Model

You’re looking for something that:

Supports strong consistency
Has good indexing for dedupe and querying by voter
Is easy to audit and backfill

Options:

PostgreSQL → Strong schemas, easy audit trails
DynamoDB → If you're okay trading joins for speed + scale
MongoDB → Works if your model is evolving fast, but requires more care with consistency

The Write Side Winner: PostgreSQL

We’re using Postgres — not to be safe, but to be exact.

Why?

Strong Consistency → ACID guarantees with no compromises.
Declarative Constraints → Unique indexes, foreign keys, partial indexes — all out of the box.
Audit-Friendly → Can version rows, backfill, query point-in-time state.
Replay-Resilient → Handles inserts, upserts, and deduping with clean transaction semantics.

It’s boring. Which is exactly what you want when people are voting.

How PostgreSQL Enforces Integrity Under Pressure

This is where it earns its keep.

✅ Idempotency

We define both a primary key on vote_id and a unique constraint on (voter_id, region).

CREATE UNIQUE INDEX unique_vote ON vote_records(voter_id, region)
WHERE is_retracted = false;

That WHERE clause ensures retracted votes don’t block a re-vote — but duplicates still fail fast.

Result:

Same user tries to vote twice → blocked
User retracts and votes again → allowed
Replay of same vote event → ignored

✅ Retractions

We don’t delete. We just flip the flag:

UPDATE vote_records
SET is_retracted = true
WHERE vote_id = 'VOTE-UUID-1234';

This keeps the audit trail intact and supports recounts or retroactive fraud reviews.

✅ Fraud Detection

We index suspicious metadata:

CREATE INDEX vote_ip_idx ON vote_records(ip_address);
CREATE INDEX vote_ts_idx ON vote_records(timestamp);

Now we can run retrospective scans like:

SELECT voter_id, COUNT(*)
FROM vote_records
WHERE ip_address = '192.168.0.7'
AND timestamp BETWEEN NOW() - INTERVAL '1 minute' AND NOW()
GROUP BY voter_id
HAVING COUNT(*) > 1;

Perfect for catching bot spikes, shared-device voting, or ballot stuffing.

✅ Audit-Ready

If needed, we can add a shadow audit table:

CREATE TABLE votes_audit AS
SELECT *, clock_timestamp() AS audited_at
FROM vote_records;

And insert into it via a trigger, log ship, or external listener. But even without it, our base table is already self-explanatory.

The Read Path – Fast, Fresh, and Deceptively Complex

What Reading a Vote Actually Means

We’re not fetching records. We’re answering questions — in real time.

Who’s leading right now?
How are votes distributed across zones?
What changed in the last 5 minutes?
Which candidate just pulled ahead in EastZone?

These are aggregate, filtered, and high-volume reads — across tens of thousands of users.
And unlike the write path, these queries care about speed, not absolute precision.

In other words:

It’s okay if a vote cast 5 seconds ago hasn’t shown up yet.
It’s not okay if the numbers look broken or change wildly with every refresh.

The Read Model

We don’t fetch from vote_records. We read from a materialized view — pre-joined, pre-aggregated, optimized for direct access.

✅ Sample Read Model (Redis or in-memory shape)

{
  "candidateId": "CAND-45",
  "totalVotes": 125490,
  "regionBreakdown": {
    "WestZone": 40050,
    "EastZone": 30870,
    "NorthZone": 54570
  }
}

totalVotes is precomputed.
regionBreakdown is a running tally.
No voter info. No time-series history.
It’s denormalized, fast, and disposable — designed for dashboards, not audits.

This model gets updated by the sync layer, not queried directly from the write store.

DB Choices for the Read Model

We’re solving for:

Low-latency fetches (under 100ms)
Real-time counters, filters, region-wise breakdowns
Tolerance for eventual consistency
The ability to rebuild or replay if needed

Let’s break the candidates down:

DB	Strengths	Weak Spots
Redis	Sub-millisecond reads, native counter ops, good for hot-path dashboards	Volatile memory, no joins, eventual loss under replay storm
ClickHouse	Columnar, great for fast group-bys and filtering, analytics-grade	Not real-time; ideal for second-order queries, not hot path
Elastic	Flexible filters, good for semi-structured data (tags, IP, location)	Not optimized for counters or TTL-safe rollups

Our DB of Choice: Redis + ClickHouse (Hybrid Read Path)

We split the read path into two tiers — fast-path and cold-path:

✅ Redis for Real-Time Stats

We use Redis for:

GET /results → candidate-level counters
GET /heatmap → per-region aggregates
GET /delta?since=5m → change tracking via TTL keys

Why Redis:

Native atomic counters (INCR, HINCRBY)
Hashes for storing breakdowns per candidate
Expiry + sliding window tracking via EXPIRE and ZREVRANGE

We treat it as a hot cache layer, not a source of truth.
It’s fast, cheap to read, and easy to flush if things go wrong.

✅ ClickHouse for Backup, Analytics, and Rebuilds

Redis can’t hold the long tail.

ClickHouse stores the full denormalized event stream and is used for:

Daily summaries
Recount verification
Rebuilding Redis in case of cache wipe or desync
Fraud pattern analysis over time

Why ClickHouse:

Blazing fast aggregation over billions of rows
Time-based partitioning and compression
Ideal for replays: “Rebuild all stats from 8:00 to 9:00”

It’s cold, but durable.

How the Read Path Holds Up Under Load

Scenario: 100k users hit the dashboard at once.

Redis handles the load with in-memory counters.
ClickHouse stays untouched — unless there's a recount or admin dashboard request.
If Redis desyncs? Rehydrate from ClickHouse using the last known snapshot + deltas.

We never query vote_records here. That’s the entire point of CQRS.

The Sync Layer – Where Consistency Lives (and Dies)

The moment a vote is written, someone has to tell the read model.

That someone is this layer — the sync layer. It’s not a feature. It’s not a library. It’s the bloodstream of your CQRS system.

Every vote cast → gets serialized → dispatched → consumed → and applied to Redis/ClickHouse.

And this is exactly where most systems fail — not because the logic is wrong, but because the assumptions break under real-world timing.

What the Sync Layer Actually Does

Listens to inserts (via CDC, outbox, or domain events)
Transforms the write-side event to a read-side command
Publishes it (via Kafka, RabbitMQ, etc.)
Read model consumes it and updates its view

One job. Thousands of ways to go wrong.

Sample VoteCast Event (Pushed to Event Bus)

{
  "eventType": "VOTE_CAST",
  "sequenceNumber": 982145,
  "payload": {
    "voteId": "VOTE-UUID-1234",
    "voterId": "USR-98213",
    "candidateId": "CAND-45",
    "region": "WestZone",
    "timestamp": "2025-07-08T18:45:00Z"
  },
  "emittedAt": "2025-07-08T18:45:01Z"
}

This is what flows through your event bus — not just data, but intent.
And your system has to apply it exactly once — no more, no less.

What Breaks in the Real World

❌ Delay → Stale dashboards

A vote is cast at 8:59:58
Dashboard refreshes at 9:00:00
The sync event hits Redis at 9:00:02
Users scream: “My vote didn’t count!”

It did. But it didn’t sync fast enough to prove it.

❌ Out-of-Order Events → Broken Aggregates

Votes arrive out of sequence:

Candidate A loses 100 votes
Then gains 50
Then loses 50

If processed out-of-order, your tallies are now… fiction.

You need sequence numbers or idempotent update logic.

❌ Replay Storms → Cache Eviction

An admin requests a recount.
You replay 1 million events into Redis in 10 seconds.
Redis evicts half your TTL keys.
Your heatmap breaks.

Replays are necessary — but they’re also violent.
They must be rate-limited and buffered.

How We Survive It

✅ Use Sequence Numbers

Every event gets a monotonic sequenceNumber.
Consumers ignore any event older than what they've already applied.

✅ Idempotent Upserts

Every read-side update uses a deduplication key (usually voteId).
If it's already been counted, skip.

luaCopyEdit-- In Redis: Lua script that increments only if not already seen
if not redis.call("SISMEMBER", "seen_votes", voteId) then
  redis.call("HINCRBY", "candidate_votes", candidateId, 1)
  redis.call("SADD", "seen_votes", voteId)
end

✅ Lag Monitoring

Track time delta between vote.timestamp and processed_at.
If the lag exceeds your SLA, raise alerts or backpressure producers.

✅ Replay Isolation

During a replay:

Don’t update live counters.
Write to a shadow view.
Swap views only after a successful replay and checksum match.

Edge Cases & Pain Points

You built a write model. You built a read model. You built the sync bridge.

Now comes the part nobody plans for — when humans, audits, policies, and time collide.

These aren’t bugs. These are expected outcomes in real systems that operate under conflicting truths.

Vote Cast at 8:59, Missing from 9:00 Results

What happened:

A user votes at 08:59:58
Dashboard polls at 09:00:00
Event hits Redis at 09:00:03

Result:
The vote is counted — just not yet visible. The dashboard “missed it.”

Why it’s not a bug:
This is eventual consistency in action.
The write model is correct.
The read model is temporarily stale — by design.

If you force strong consistency here, you’ll kill your read path’s speed.

Recount Triggered — What Actually Happens?

Scenario:
Candidate B requests a recount for EastZone between 7:00–8:00 AM.

System Behavior:

Filter votes from vote_records by region + time
Emit new RECOUNT events
Process through the sync layer
Build a shadow view (don’t touch live counters)
Validate checksum
Swap views only if checksum passes

Why this matters:
You don’t replay blindly into Redis. That’s how dashboards glitch and users panic.

Fraud Detected — Now What?

Scenario:
1000 votes from 1 IP in 10 minutes.

You need to:

Trace the votes (via ip_address in write model)
Mark them as retracted (is_retracted = true)
Replay the affected time window
Rebuild the read view with those votes excluded

Important:
The read model never deletes votes. It just replays a new version with different inputs.

Desync Between Write and Read

How it shows up:

Vote counts fluctuate across refreshes
Heatmaps are empty in some zones
Candidates gain and lose votes erratically

Root causes:

Event loss
Duplicate application
Partial replay
Redis eviction mid-update

Fix:

Trigger a checksum comparison between Redis and ClickHouse
If mismatch → schedule a full rebuild
Alert if drift exceeds threshold

This is why you have two read models — one fast, one durable.

Closing Thoughts — CQRS Wasn’t a Choice

We didn’t start with CQRS.
We started with a simple requirement: “Let users vote. Show the results live.”

What we got instead was:

A write path that demands accuracy, traceability, and finality
A read path that demands speed, freshness, and scalability
A sync layer that operates in the gray zone between trust and lag

And suddenly, one model wasn’t enough.

We split the models not because we liked the pattern — but because the system refused to stay consistent and fast under the same roof.

This is CQRS in the real world:

Your write DB holds the truth — even if it’s slow.
Your read DB holds the illusion — fast, imperfect, constantly updated.
The sync bridge holds your nerves together — or doesn’t.

The moment your write path demands safety and your read path demands speed — CQRS has already begun.

This wasn’t architecture.
This was survival.

Epilogue — End of the Series

This post closes the “Why CQRS Was Conceived” series.

We didn’t try to sell the pattern. We walked through the pressure that forced it to exist — system by system, failure by failure. From OLAPs choking on writes, to OLTPs dying under read load, to the sync hell in between.

If there’s one takeaway, it’s this:

CQRS isn’t a technique. It’s a fracture line.
It shows up the moment your system tries to serve two masters with one model.

Thanks for following the trail.

Command Palette