Voting System APIs and Data Model

0) Problem Restatement

Apple asked: design the APIs and core data model for a voting system. The hint: start by clarifying the type of vote, because it changes the design:

• Identified vs anonymous: do we store who voted for what?
• Single-choice vs multiple-choice (or ranked)?
• Mutable vs final: can a voter change their vote before the poll closes?
• Partial results: visible while voting is open, or only after it closes?

Let's design for a common case: identified voters, single or multiple choice (configurable), changeable until close, results hidden until close (configurable), and explain the variants.

1) Data Model

CREATE TABLE polls (
  poll_id BIGINT PRIMARY KEY, title TEXT, created_by BIGINT,
  max_choices INT DEFAULT 1,          -- 1 = single choice
  allow_change BOOLEAN DEFAULT TRUE,
  results_visibility TEXT,            -- 'live' | 'after_close'
  opens_at TIMESTAMP, closes_at TIMESTAMP, status TEXT
);
CREATE TABLE options (option_id BIGINT PRIMARY KEY, poll_id BIGINT REFERENCES polls, label TEXT, position INT);
CREATE TABLE ballots (                 -- one ballot per voter per poll
  poll_id BIGINT, voter_id BIGINT, choices BIGINT[], version INT,
  cast_at TIMESTAMP, updated_at TIMESTAMP,
  PRIMARY KEY (poll_id, voter_id)       -- enforces one vote per user
);
CREATE TABLE option_counts (poll_id BIGINT, option_id BIGINT, shard INT, votes BIGINT,
  PRIMARY KEY (poll_id, option_id, shard));

2) APIs

POST /v1/polls                         { title, options: [...], max_choices, allow_change, results_visibility, closes_at }  → 201
GET  /v1/polls/{id}                    → poll + options (+ results if allowed) + my_ballot
PUT  /v1/polls/{id}/ballot             { choices: [optionId, ...] }   Idempotency-Key  → 200 { ballot }
                                        400 too many choices / invalid option; 409 changes not allowed; 410 poll closed
DELETE /v1/polls/{id}/ballot           → 204 (withdraw, if allowed)
GET  /v1/polls/{id}/results            → { option_id: count, ... } | 403 until closed

• PUT on "my ballot" is naturally idempotent: sending the same choices twice gives the same state. It also handles changing a vote cleanly.
• The server validates: the poll is open (server time), the options belong to this poll, the number of choices ≤ max_choices, and no duplicates.

3) Counting Votes

flowchart LR
    V["Voter"] --> API["Ballot API"]
    API --> TX["Transaction: upsert ballot + adjust counts"]
    TX --> DB[("ballots + option_counts")]
    API --> RC["Results cache"]
    DB --> RC
    R["Results viewers"] --> RC

• On cast or change, in one transaction: upsert the ballot, decrement the counts for the old choices and increment the new ones. Counts always match ballots.
• Hot polls (millions of votes at once): split each option's counter into N shards (increment a random shard, and sum them for results), or aggregate from a queue of ballot events in batches.
• Results are read from a cache refreshed every few seconds (for live polls) and computed exactly at close (optionally recounted from ballots for audit).

4) Variants and Security

• Anonymous voting: separate "who has voted" (voter_id → voted flag, to prevent double voting) from "what was voted" (ballots without voter IDs). Changing votes then becomes impossible or needs special cryptographic receipts. Say this trade-off.
• Fraud: require authentication, rate limits, bot detection, and for public polls maybe verified accounts or CAPTCHAs.
• Audit: ballot history (versions) and a recount from the ballots table.

5) Wrap-Up

Clarify the vote semantics first (identity, choice count, mutability, result visibility), then model polls, options, one ballot per (poll, voter) enforced by the primary key, and per-option counters. Cast and change votes with an idempotent PUT that validates choices and poll timing, and update the ballot and counters in one transaction (sharding counters for hot polls). Serve results from a cache according to the visibility setting, and explain how anonymous voting changes the model.