Design Quora (Q&A Platform)

-- Questions, answers, votes, question_topics, question_follows, answer_requests,
-- and content_revisions are sharded by question_id to co-locate Q&A activity.
-- topic_follows and user_follows are sharded by user_id for subscription reads.
-- IDs are application generated or allocated through Vitess sequences.

CREATE TABLE questions (
    question_id              BIGINT NOT NULL PRIMARY KEY,
    author_id                BIGINT NOT NULL,
    title                    VARCHAR(500) NOT NULL,
    body                     TEXT,
    slug                     VARCHAR(500) NOT NULL,
    view_count               BIGINT DEFAULT 0,
    answer_count             BIGINT DEFAULT 0,
    follow_count             BIGINT DEFAULT 0,
    upvotes                  BIGINT DEFAULT 0,
    downvotes                BIGINT DEFAULT 0,
    status                   ENUM('open','closed','merged') DEFAULT 'open',
    moderation_status        ENUM('visible','quarantined','removed') DEFAULT 'visible',
    canonical_question_id    BIGINT NULL,
    created_at               TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at               TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FULLTEXT INDEX idx_search (title, body),
    INDEX idx_author (author_id),
    INDEX idx_canonical (canonical_question_id)
);

-- Route slug lookups by a hash vindex on slug so each lookup targets one shard.
-- Preserve logical slug uniqueness through the Vitess vindex or an application-level uniqueness workflow.
CREATE TABLE question_slug_lookup (
    slug                     VARCHAR(500) NOT NULL PRIMARY KEY,
    question_id              BIGINT NOT NULL
);

CREATE TABLE answers (
    answer_id                BIGINT NOT NULL PRIMARY KEY,
    question_id              BIGINT NOT NULL,
    author_id                BIGINT NOT NULL,
    body                     TEXT NOT NULL,
    upvotes                  BIGINT DEFAULT 0,
    downvotes                BIGINT DEFAULT 0,
    wilson_score             DOUBLE DEFAULT 0,
    is_accepted              BOOLEAN DEFAULT FALSE,
    moderation_status        ENUM('visible','quarantined','removed') DEFAULT 'visible',
    created_at               TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at               TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_question_score (question_id, wilson_score DESC, answer_id),
    INDEX idx_author (author_id)
);

CREATE TABLE votes (
    question_id              BIGINT NOT NULL,
    user_id                  BIGINT NOT NULL,
    target_type              ENUM('question','answer') NOT NULL,
    target_id                BIGINT NOT NULL,
    vote_type                ENUM('up','down') NOT NULL,
    created_at               TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at               TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (question_id, target_type, target_id, user_id),
    INDEX idx_user_votes (user_id, created_at)
);

-- For target_type='question', target_id equals question_id.
-- For target_type='answer', question_id identifies the parent question.
-- This keeps vote records related to a question within the same question based shard.

CREATE TABLE answer_requests (
    question_id              BIGINT NOT NULL,
    requester_id             BIGINT NOT NULL,
    requested_user_id        BIGINT NOT NULL,
    status                   ENUM('pending','accepted','declined','expired') DEFAULT 'pending',
    created_at               TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (question_id, requester_id, requested_user_id),
    INDEX idx_requested_user (requested_user_id, created_at)
);

CREATE TABLE content_revisions (
    question_id              BIGINT NOT NULL,
    content_type             ENUM('question','answer') NOT NULL,
    content_id               BIGINT NOT NULL,
    revision_id              BIGINT NOT NULL,
    editor_id                BIGINT NOT NULL,
    content_snapshot         JSON NOT NULL,
    created_at               TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (question_id, content_type, content_id, revision_id),
    INDEX idx_content_revision (question_id, content_type, content_id, created_at)
);

-- Topics are low-volume metadata and can live in an unsharded/global keyspace.
CREATE TABLE topics (
    topic_id                BIGINT NOT NULL PRIMARY KEY,
    name                    VARCHAR(255) NOT NULL,
    created_at              TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_topic_name (name)
);

CREATE TABLE question_topics (
    question_id              BIGINT NOT NULL,
    topic_id                 BIGINT NOT NULL,
    created_at               TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (question_id, topic_id),
    INDEX idx_topic_recent (topic_id, created_at DESC, question_id)
);

CREATE TABLE topic_follows (
    user_id                  BIGINT NOT NULL,
    topic_id                 BIGINT NOT NULL,
    created_at               TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (user_id, topic_id),
    INDEX idx_topic_user (topic_id, user_id)
);

CREATE TABLE question_follows (
    user_id                  BIGINT NOT NULL,
    question_id              BIGINT NOT NULL,
    created_at               TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (user_id, question_id),
    INDEX idx_question_user (question_id, user_id)
);

CREATE TABLE user_follows (
    follower_id              BIGINT NOT NULL,
    followed_user_id         BIGINT NOT NULL,
    created_at               TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (follower_id, followed_user_id),
    INDEX idx_followed_follower (followed_user_id, follower_id)
);

-- Durable idempotency records are sharded by user_id and committed with the business mutation.
-- Redis remains the fast path, while MySQL protects correctness if the cache is lost.
CREATE TABLE idempotency_records (
    user_id                  BIGINT NOT NULL,
    idempotency_key          VARCHAR(255) NOT NULL,
    request_hash             BINARY(32) NOT NULL,
    response_payload         JSON NOT NULL,
    created_at               TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    expires_at               TIMESTAMP NOT NULL,
    PRIMARY KEY (user_id, idempotency_key)
);

# Derived vote counters for fast reads. MySQL vote rows and counters are authoritative.
HSET vote_count:{type}:{id} up 153 down 4

# Wilson score sorted set for rank ordering within a question
ZADD answer_order:{question_id} 0.892 a-552190
ZADD answer_order:{question_id} 0.741 a-552191

# Precomputed home feed pointers. The score stores the final feed rank.
ZADD feed:{user_id} 0.992 a-552190
ZADD feed:{user_id} 0.947 q-984210
EXPIRE feed:{user_id} 300

# Derived indexes keep feed candidate generation out of scatter reads across question shards.
ZADD topic_recent_questions:{topic_id} <created_at_score> <question_id>
ZREMRANGEBYSCORE topic_recent_questions:{topic_id} -inf <retention_cutoff_score>
ZADD author_activity:{author_id} <timestamp_score> <content_type>:<content_id>
ZREMRANGEBYSCORE author_activity:{author_id} -inf <retention_cutoff_score>
SADD followers:{author_id} <user_id>
SADD question_followers:{question_id} <user_id>
SADD topic_followers:{topic_id} <user_id>

# Redis idempotency cache for safe retries. Durable MySQL idempotency records remain authoritative.
SET idem:{user_id}:{idempotency_key} <request_hash_and_result> NX EX 86400

# High volume view counts are aggregated outside the primary write path and flushed asynchronously.
HINCRBY view_count:{type}:{id} views 1

# Materialized question counters such as answer_count and follow_count should use batched or sharded
# counter paths for very hot questions instead of an independent primary-row write for every event.