🔗 Modeling state transitions in PostgreSQL

On most projects, a status column often suffices until stakeholders begin asking for historical data, like "Who was denied last Tuesday?" In the article, Thiago Araújo Silva proposes a more effective method by modeling each status change as its own row in a separate table. This way, the current status is derived from the most recently recorded status, maintaining both a full history and strong read performance.

He outlines an architecture involving a user_statuses table, illustrating that instead of updating a status column, new records are inserted for each status change. Various SQL techniques to retrieve the latest status efficiently are discussed, including correlated subqueries, window functions, and lateral joins, all optimized with appropriate indexing for speed. The article emphasizes the significance of this design in avoiding the pitfalls of outdated schemas and incomplete data history.