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.