Design a content versioning system with history and revert

WHAT THIS TESTS: This question probes your ability to balance immutability with query performance. Interviewers want to see if you understand that version history must be append-only, that reverting is just adding a new version, and that editors need fast access to the current article without reconstructing it from a chain of diffs. They also care whether you consider concurrency, authorship, and soft-deletion.

A GOOD ANSWER COVERS: A strong design starts with two core tables: an articles table holding article_id, current_version_id, and metadata like title or slug, and an article_versions table with version_id, article_id, version_number, content_snapshot, author_id, created_at, and an optional change_summary. The API surface should include createVersion to append a new snapshot and atomically update the articles pointer, getHistory to return paginated version metadata, getVersion to fetch a specific snapshot, and restoreVersion to copy an old snapshot into a new row with the next version_number rather than rewinding state. You should mention indexing article_versions by article_id and created_at for fast history lookups, and note that content_snapshot can be stored in S3 with a reference URL if articles are large. Mentioning optimistic locking or a version_number check prevents lost updates when two editors publish simultaneously.

COMMON WRONG ANSWERS: A red flag is suggesting an in-place update of the current article without retaining the prior state. Another mistake is storing only diffs to save space but lacking a concrete plan to rebuild any arbitrary version in under 100 milliseconds. Proposing a full Git object graph is usually overkill for a CMS and signals failure to scope the problem. Omitting author_id and created_at is also weak because editorial audit trails are non-negotiable.

LIKELY FOLLOW-UPS: The interviewer may ask how you would handle branching drafts versus published versions, how to diff two versions efficiently, or how to scale when articles reach thousands of versions. They might also ask about handling binary assets like images within the versioning scheme, or how to implement permissions so only senior editors can restore old versions.

ONE CONCRETE EXAMPLE: Imagine an article with article_id 42. The articles table points to version_id 107. An editor updates the headline, so your createVersion API inserts a new row in article_versions with version_id 108, version_number 5, the new content_snapshot, author_id 7, and created_at now. The articles table current_version_id flips to 108 atomically. When the editor clicks revert to version 3, restoreVersion copies version 3's content_snapshot into a new row with version_id 109 and version_number 6, then updates the articles pointer. History remains intact and every change is auditable.