Single Table Inheritance with Rails

Recently my team was tasked with building an in house Kanban Ticketing system, similar to JIRA. At it’s core the system would create and manage tickets, which are assigned to people to work on.

We needed to support several different types of tickets, using the JIRA example you could have: story, defect, request or epic tickets. It became apparent to the team early on that all these tickets would have the exact same database structure, but would need to behave differently.

When to use Single Table Inheritance

Before diving into how to implement STI, I want to point out that this design choice only makes sense in certain circumstances. You should ask yourself the following questions:

  • Does your application have several sub-models that inherit from a parent model class?
  • Do the sub-models all have the same attributes and database columns as each other?
  • Do the sub-models need to behave differently from the parent model class?

If you answered yes to all of the above, then you’ve come to the right place. If not you may need to consider another design like polymorphism, or even separate tables for each model. Having attributes that are not used by all sub-models can lead to a tonne of nulls in your database.

Remember that STI is a great way of DRYing up models that share the same attributes, OO characteristics and database structure.

A real world example

I’m going to stick with the JIRA example given above, so the first thing we’ll do is generate a migration to create our ticket table.

Here’s an example migration for creating the ticket table.

class CreateTickets < ActiveRecord::Migration
  def change
    create_table :tickets do |t|
      t.integer :priority, unsigned: true, null: false, default: 4
      t.string :type, limit: 20, null: false
      t.string :abstract, limit: 255, null: false
      t.text :details, limit: 65535, null: false

The type column we’ve defined is the default column used by STI, to store the sub-model name. If you want to use a different column you just need to declare it your base model.

self.inheritance_column = :my_cool_column

Next we’ll need to define our Ticket model and it’s sub-models. A simple Ticket model will look like this.

# app/models/ticket.rb
class Ticket < ActiveRecord::Base
  validates :abstract, presence: true, length: { minimum: 10, maximum: MAX_ABSTRACT_LENGTH }
  validates :details, presence: true, length: { minimum: 10, maximum: MAX_DETAILS_LENGTH }
  validates :type, presence: true, length: { minimum: 3, maximum: MAX_TYPE_LENGTH }
  validates :priority, numericality: { only_integer: true, greater_than_or_equal_to: 0 }
  after_create :set_default_priority
  def description
    "This is a #{type.downcase} ticket."

So we’ve defined our base Ticket model with a few validations and an after_create action. Notice that we have not defined the method set_default_priority yet, this will be done in the sub-models.

# app/models/defect.rb
class Defect < Ticket

  def set_default_priority
    update(priority: 1)
  def due_at
    created_at + + 1.hour
# app/models/story.rb
class Story < Ticket

  def set_default_priority
    update(priority: 2)
  def due_at
    created_at + + 3.hours
# app/models/issue.rb
class Issue < Ticket
  def set_default_priority
    update(priority: 3)
  def due_at
    created_at + + 6.hours
# app/models/request.rb
class Request < Ticket

  def set_default_priority
    update(priority: 4)
  def due_at
    created_at + + 12.hours

All of the sub-models we’ve defined have a unique implementation of the set_default_priority function. This is to illustrate that by using STI we can have sub-models share the same database table, yet have behave differently.

Using the rails console we can test that STI is working as expected.

# Open the console by typing 'rails console' in your terminal
ticket = Ticket.create(abstract: 'Testing abstract', 
                       details: 'Testing details', 
                       type: 'Defect')    
=> #<Defect id: 1, priority: 1, type: "Defect", abstract: "Testing abstract", details: "Testing details", created_at: "2017-08-24 11:02:12", updated_at: "2017-08-24 11:02:12">
=> "This is a defect ticket"

=> 1

=> Fri, 25 Aug 2017 12:04:26 UTC +00:00

Now we know STI is working as expected, we can begin to build our ticketing system on our ticket model. We can add our routes and some controller actions.

# app/config/routes.rb
Rails.application.routes.draw do
  resources :tickets, only: [:index, :show, :edit, :update, :create, :new]
  root 'tickets#index'
# app/controllers/tickets_controller.rb
class TicketsController < ApplicationController
  def index
    @tickets = Ticket.all
  def show
    @ticket = Ticket.find(params[:id])



Wrapping up

Through the use of Single Table Inheritance we’ve been able to design our sub-models to be flexible on a code level, while sharing the same database table. I hope this post has given you a good understanding of how and when to implement Single Table Inheritance. I’ve created a simple toy app with all the code shown above that you can find here on Github. Feel free to clone it and experiment yourself!

Optimizing Performance of Ordered Active Record Queries

Active Record enables Rails developers to model, store and query their application’s data, often without ever having to write a line of SQL.

While this is amazing for getting an app up and running quickly, once it’s up and running, you will undoubtedly experience performance issues and find yourself examining SQL queries. In this post I’ll share with you an example of a performance issue that can creep in when using the ActiveRecord::QueryMethods order function and how to fix it.

A Typical Rails Scenario

Lets take a blog app as an example and assume we have a Posts table and a Users table. The relationship between these tables is a User has many Posts. The Posts table is linked by the foreign key user_id to the Users table.


Posts table


Users table

You’re bound to see an ActiveRecord statement that’s something like this:

Post.where(user_id: 7092).order(updated_at: :desc)

With Active Record we can easily write a query to retrieve all the posts for a user and order them by when they were last updated. Active Record will go and generate the following SQL query:

SELECT `posts`.* FROM `posts` WHERE `posts`.`user_id` = 7092  ORDER BY `posts`.`updated_at` DESC;

However Active Record won’t check this query’s performance for you and when you go live with your blog you’ll see a big difference ordering 1 post vs ordering 1000 posts.

Copy this generated SQL query into your preferred SQL editor and prepend the EXPLAIN keyword to the start of the query.


EXPLAIN is a must know for any serious Rails developer, it helps you understand your queries and identify possible performance issues.

There are already many good blog posts on EXPLAIN and it’s usage, such as this one here. Below is the output of running EXPLAIN on our query.

Explain Query 1

As you can see this is a terrible query, it is using no indexes and doing a full table scan, luckily there’s only 4 rows!

So the first thing most people will do in this case is add a foreign key constraint for user_id on the Posts table. Lets do that first and re-run the explained query.

add_foreign_key :posts, :users # in a Rails migration
/* in a SQL statement */
ALTER TABLE posts ADD FOREIGN KEY (user_id) REFERENCES users (id);

Explain Query 2

Things are looking a lot better now with the addition of the foreign key constraint. If you look in the Extra column you’ll notice that there are two statements “Using index” (good) and “Using filesort” (bad).

What is Filesort then?

Using Filesort means you are performing a sort that can’t use an index. You might be thinking to yourself “I already added my foreign key so I do have an index”. While it’s true that you have an index, you haven’t created an index on the required columns for the sort.

This leads to:

  • A full scan of the result set
  • Using the filesystem to store chunks when the sort buffer in memory gets full
  • A decrease in query performance as the result set grows

How to fix it

Fortunately the hardest part of dealing with these types of performance issues is identifying them. To solve the Filesort issue we simply need to add an index to the appropriate columns. Going back to our Active Record generated SQL query, we can see that the columns we need to index are user_id and updated_at.

Lets add the index and EXPLAIN our query one last time.

add_index :posts, [:user_id, :updated_at] # in a Rails migration
/* in a SQL statement */
ALTER TABLE posts ADD INDEX user_id_updated_at (user_id, updated_at);

Explain Query 3

Finally we have a query using the new index user_id_updated_at and “Using Filesort” is a thing of the past.

Extract and parse digital certificates using Ruby

Recently I came across a challenging piece of work. I was required to write a pure Ruby solution to extract and parse digital certificates from PE files.

Reading in .der and .pem files

Before I cover how to extract digital certificates from a PE file let’s take a look at a standard PKCS7 certificate in .der format. First I read in my_certificate.der and create an OpenSSL::PKCS7 object from the raw certificate string. I then instantiate the DigitalCertificateParser class with the PKCS7 object as a parameter.

raw_pkcs7_cert = + 'my_certificate.der')
certificate_object =
@digital_certificate_parser = 

Extracting X509 certificates from a PKCS7 certificate

Digital signatures are usually a collection of X509 certificates within a PKCS7 certificate, the X509 certificates are the ones we’re interested in. The parser pulls these OpenSSL::X509 certificates out from the PKCS7 certificate on line 5. I can now call @digital_certificate_parser.chains to build an array containing one or more ordered arrays of digital certificates, from the root certificate to the end user certificate, with intermediate certificates in between.

The logic I’ve implemented here is based on ‘name match’ validation which is explained very well in this article. In a nutshell certificates in a chain are linked by their subject and issuer values.

The issuer value of a end user certificate should be the subject of the next certificate in the chain, this continues up the chain to the root certificate. The root certificate is the certificate that does not have it’s issuer referenced by any other certificate as it is a loop back to itself.

class DigitalCertificateParser
  attr_reader :all_certificates

  def initialize(digital_signature)
    if !digital_signature.instance_of?(OpenSSL::PKCS7)
      raise InvalidSignatureError, 'Signature is not a PKCS7'
    @all_certificates = do |cert| 

  def chains
    @chains ||= build_chains

  def end_user_certificate
    first_chain = chains[0]
    first_chain.last if first_chain

  def root_certificate
    first_chain = chains[0]
    first_chain.first if first_chain


  def build_chains
    chains = []
    root_certs = find_root_certs
    root_certs.each_with_index do |cert, index|
      chains[index] = [cert]
      while (next_certificate = find_next_in_chain(chains[index].last))
        chains[index] << next_certificate

  def find_next_in_chain(parent_cert)
    all_certificates.find do |cert| 
      cert.issuer == parent_cert.subject && !cert.time_stamping_certificate?

  def find_root_certs
    subjects = do |certificate| 

Decorating the certificate’s data

You’ll notice the parser is creating an object called @all_certificates when it’s initialized. This is basically mapping all the X509 certificates to become DigitalCertificate objects. The DigitalCertificate object is one I’ve written, it uses the Decorator design pattern by inheriting from the SimpleDelegator class.

This allows me to add functions like time_stamping_certificate? and to_db to the X509 certificates. I can also overwrite functions that the OpenSSL::X509 object would usually respond to like subject with the subject value decorated, in this case as a string.

The Decorator pattern also allows me to call functions like to_der, to_pem and verify, so essentially what we have is an X509 certificate object with some extra functionality I’ve added.

require 'delegate'
require 'date'

class DigitalCertificate < SimpleDelegator
  def subject

  def issuer

  def serial
    __getobj__.serial.to_s(16).scan(/.{1,2}/).join(' ')

  def extensions

  def valid_from

  def valid_to

  def thumbprint

  def root_certificate?(subjects)
    !subjects.include?(issuer) && !time_stamping_certificate?

  def time_stamping_certificate?
    extension_match = extensions.any? { |ext| ext =~ /time\s*stamp/i }
    extension_match || subject =~ /time\s*stamp/i

  def to_db
    { issuer: issuer, subject: subject, serial_no: serial, 
      thumbprint: thumbprint, algorithm: signature_algorithm, 
      valid_from: DateTime.parse(valid_from), 
      valid_to: DateTime.parse(valid_to) 

Digital signatures will usually include a timestamp countersignature, when working with certificates I wasn’t concerned with these so I chose to identify them and ignore them. If you are interested in them you can remove the check in this class and the check in the DigitalCertificateParser class.

Extracting certificates from a PE file

To extract one or more certificates from a PE file I’m going to use the gem pedump. If you’re using Bundler then just add this gem to your Gemfile, otherwise you can install it with gem install pedump. The gem has a command-line implementation, but this didn’t suit my requirements so I used pedump’s code instead.

I have observed one caveat when using the pedemp gem, it is unable to extract SHA256 digital certificates if all certificates in the chain are signed with the sha256 algorithm.

require 'pedump'

class InvalidFilePathError < StandardError; end

class DigitalCertificateExtractor

  def new(path)
    @file_path = Pathname(path)
    raise InvalidFilePathError unless @file_path.exist?
    @signature = nil
  def extract_end_user_certificate
    begin, 'rb') do |f| 
        @signature =, :log_level => Logger::UNKNOWN ).signature 
      return nil unless @signature && @signature.any?
      parser =
    rescue InvalidSignatureError => e
      puts "File with invalid signature: #{@file_path}\n\n" + e.backtrace.join("\n")

This class is just an example class, to show you how you can pull digital certificates from PE files. You might want to extract all the certificates instead of just the end user certificate, all you have to do just write a function in this class that calls parser.chains. The certificate objects that are returned can be decorated any way you like by modifying the DigitalCertificate class to suit your needs.

Putting it all together

So far the code covers parsing certificates from .der and .pem files, extracting certificates from PE files and decorating certificates to display information in a way you want.

The last snippet I’ll share is some examples of how to use this code. Feel free to modify the code to suit your needs.

# Extracting a certificate chain from a PKCS7 file
raw_pkcs7_cert ='/path/to/my_pkcs7.der')
certificate_object =
parser =
certificate_chain = parser.build_chains.first
certificate_chain.each |certificate|
  puts "Subject: #{certificate.subject}\nIssuer: #{certificate.issuer}\n\n"

# Extracting an end user certificate from a PE file
extractor ='path/to/your/file.exe')
certificate = extractor.extract_end_user_certificate

# Store a certificate in a Database using Rails
MyModel.create!(certificate.to_db) unless certificate.nil?

# Write to a file using OpenSSL::X509 functionality'path/to/my_cert.cer', 'wb') { |f| f.write certificate.to_der }'path/to/my_cert.pem', 'wb') { |f| f.write certificate.to_pem }