How would you implement a simple editorial status workflow?

WHAT THIS TESTS: This question evaluates whether you think in terms of state machines and data integrity rather than just adding a string column. The interviewer wants to see that you distinguish between storing a value and enforcing business rules, and that you know where to place constraints (database versus application layer). They also care about auditability and concurrency.

A GOOD ANSWER COVERS: First, the data type. Prefer a database-native ENUM or a small lookup table referenced by a foreign key over a plain VARCHAR. ENUM gives you storage efficiency and implicit constraint, while a lookup table is more portable and lets you attach metadata like labels or permissions. Second, transition enforcement. Never rely only on frontend code. The safest approach is an application-layer state machine that checks allowed transitions before writing. A more database-centric approach is a transitions table with allowed_from and allowed_to rows, optionally enforced by a trigger or by app code reading that matrix. Third, auditability. Add created_at and changed_by columns on the content table, or better, a separate status_history table recording every transition with old_value, new_value, actor_id, and changed_at. Fourth, concurrency. Mention optimistic locking with a version or updated_at timestamp so two editors cannot simultaneously move the same piece to different states.

COMMON WRONG ANSWERS: Storing status as a VARCHAR with no CHECK constraint or foreign key. Relying on frontend dropdowns to enforce transitions. Proposing a single boolean is_published flag for a multi-step workflow. Suggesting you update the status in place with no history table and no actor tracking. Saying you would use a document store because relational databases cannot handle workflows.

LIKELY FOLLOW-UPS: How would you handle permissions so only editors can move content to In Review and only admins can Publish? How would you query for all content that has been in review longer than three days? What happens if two users update the status at the same time? Would you ever push transition logic into the database with triggers, and what are the tradeoffs?

ONE CONCRETE EXAMPLE: A content_pieces table has status_id as a foreign key to a content_statuses table containing Draft, In Review, and Published. A content_transitions table defines allowed moves, such as Draft to In Review and In Review to Published, but not Draft to Published. When a user submits an update, the application checks that the requested new status_id exists in content_transitions for the current status_id before committing. A content_status_history table records every change with actor and timestamp. This pattern mirrors how Drupal's Content Moderation module expands on basic published and unpublished states by introducing custom workflows and transitions between them.

Source: Drupal.org Content moderation module