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).
Versioning events
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 published_at
column.
I then created an events
view:
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"
Versioning performances
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 events
view:
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
Final thoughts
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.