# CQRS in Action: Designing a Real-Time Online Voting System “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: ```json { "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. ```plaintext 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)`. ```pgsql 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: ```pgsql 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: ```pgsql 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: ```pgsql 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: ```pgsql 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) ```json { "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) ```json { "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. ```json 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:** 1. Filter votes from `vote_records` by region + time 2. Emit new `RECOUNT` events 3. Process through the sync layer 4. Build a shadow view (don’t touch live counters) 5. Validate checksum 6. 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.