For Townstage, I wanted to allow people to create or update events without having an account. I didn't want to immediately publish that data, so I needed a way to record changes and review them before they go live.
I chose to implement a versioning system using Postgres views. There are a few gems that do content versioning, but they seem more oriented towards tracking historical changes than capturing changes for review.
If you are unfamiliar with database views, you can think of them as a form of cached queries. If you're frequently using complicated joins, subqueries, etc. in a single query, a view can wrap that complexity and offer a pared down interface that simplifies your queries.
Let's get started!
The domain: live music events
My app has events, artists, and performances. One event can have zero or more performances (one for each artist who is performing).
First, in order to allow multiple versions of my events, I created an
event_versions table. Each row has an
event_id column which auto increments and a
I then created an
CREATE OR REPLACE VIEW events AS SELECT DISTINCT ON (ev.event_id) ev.event_id AS id, ev.id AS version_id, ev.headline FROM event_versions ev ORDER BY ev.event_id, ev.published_at DESC NULLS LAST
In effect this creates a read-only snapshot of my events which contains only the latest published version of each event.
I can now retrieve events like usual:
@event = Event.find(1) puts @event.headline #=> "Take a chance Tuesday with Dragon Wagon"
In addition to events, I also needed versioning for performances. I could write a view similar to the
events view above, but there isn't really a benefit to managing performance versions separately from their associated event. Instead I decided to let each event version have its own set of performances. Thus, the view joins
performance_versions to the
CREATE OR REPLACE VIEW performances AS SELECT pv.id, pv.artist_id, pv.headliner, e.id AS event_id FROM performance_versions pv JOIN events e ON pv.event_version_id = e.version_id
I can now retrieve event performances like usual:
@event = Event.find(1) @event.performances.each do |performance| puts performance.artist_name end
I'm really happy with how this turned out. New event versions are created with
published_at set to
NULL, which is easily filtered out in queries by a
WHERE condition. Publishing that version is as simple as setting
published_at to the current time. If I ever need to roll back a change, I can update
published_at on the old version or delete the newer version.
In part 2 of this series, I will discuss how my Rails models changed as a result of the new database schema.