TrustCommerce payment history & invoicing

Lately I’ve been hearing more and more from developers and companies who are using the my TrustCommerce subscription gem or plugin.

One of the questions I recently received was “How do you handle syncing transaction history, send monthly invoices, etc.?”

The way I’ve done this in apps is to create two tables to handle subscription and subscription transaction data.

Example tables

create_table :subscriptions do |t|
  t.column :account_id,                :integer,   :null => false
  t.column :created_on,                :datetime,  :null => false
  t.column :billing_id,                :string
  t.column :length,                    :integer,   :null => false
  t.column :cents,                     :integer,   :null => false
  t.column :billing_full_name,         :string
  t.column :billing_address,           :string
  t.column :billing_zip_code,          :string
  t.column :billing_country,           :string
  t.column :billing_card_type,         :string
  t.column :billing_credit_card,       :string
  t.column :billing_expiration_date,   :datetime   

add_index :subscriptions, :account_id

create_table :subscription_transactions do |t|
  t.column :subscription_id,          :integer,   :null => false
  t.column :account_id,               :integer,   :null => false
  t.column :transaction_date,         :datetime,  :null => false
  t.column :transaction_id,           :string,    :null => false
  t.column :transaction_type,         :string,    :null => false
  t.column :amount,                   :decimal,   :null => false
  t.column :card_number,              :string,    :null => false
  t.column :card_type,                :string,    :null => false
  t.column :cardholder_name,          :string,    :null => false     

add_index :subscription_transactions, :subscription_id
add_index :subscription_transactions, :account_id

Then we can create an agent to grab recent transactions from the vault:

class TrustcommerceSyncAgent
  # time_ago examples: 
  #   30m => 30 minutes
  #   1h  => 1 hour
  #   3d  => 3 days
  def self.sync(time_ago = nil)
    time = case time_ago
      when /^(\d+)m$/ then - $1.to_i.minute
      when /^(\d+)h$/ then - $1.to_i.hour
      when /^(\d+)d$/ then - $
      else - 1.hour
    # --- [ find recently created paying subscriptions ] ---
    subscriptions = Subscription.find(:all, :conditions => ['cents > 0 AND created_on > ?', time])
    subscriptions.each do |subscription|


    def self.sync_subscription(subscription)
      # --- [ get TC data ] ---
      tc = TrustCommerce::Subscription.query(
        :querytype  => 'transaction',
        :billingid  => subscription.billing_id
      return if !tc.kind_of? Net::HTTPOK

      # --- [ create index by CSV header ]
      field_names = tc.body.split("\n")[0].split(',')
      indexes = field_names.inject({}) {|h, field| h[field.to_sym] = field_names.index(field); h }

      # --- [ build transaction array ] ---
      transactions = tc.body.split("\n")
      transactions.shift # get rid of header

      transactions.each do |line|
        transaction = line.split(',')

        #log_transaction(indexes, transaction)
        subscription_transaction = subscription.transactions.find_by_transaction_id(transaction[indexes[:transid]])
        if subscription_transaction.nil?
            :subscription_id    =>,
            :account_id         => subscription.account_id,
            :transaction_date   => convert_date(transaction[indexes[:trans_date]]),
            :transaction_id     => transaction[indexes[:transid]],
            :transaction_type   => transaction[indexes[:action_name]],
            :amount             => transaction[indexes[:bank_amount]],
            :card_number        => transaction[indexes[:cc]],
            :card_type          => convert_card_type(transaction[indexes[:media_name]]),
            :cardholder_name    => transaction[indexes[:name]]

    # TC returns mm-dd-yyyy HH:mm:ss
    def self.convert_date(str)
      date = str.split(' ')[0].split('-')
      Time.local(date[2], date[0], date[1])
    # TC returns VISA-D, MC-D, AMEX-D
    def self.convert_card_type(str)
      case str
        when /VISA/i  then 'Visa'
        when /MC/i    then 'MasterCard'
        when /AMEX/i  then 'American Express'
        else str
    # --- [ helpful for debugging ] ---
    def self.log_transaction(indexes, transaction)
      puts '---------- transaction --------------------'
      indexes.each{|k,v| puts "#{k} => #{transaction[indexes[k]]}" if !transaction[indexes[k]].blank? }
      puts '-------------------------------------------'

You can play around with this on the console:

$ ./script/runner -e production "TrustcommerceSyncAgent.sync('3h')"

and then install into cron in production.

In terms of monthly invoices, a simple after_create hook should work nice:

class SubscriptionTransaction < ActiveRecord::Base
  after_create :send_invoice

By the way, I’ve added TrustcommerceSyncAgent to the gem and plugin.

Hope this helps getting your monthly billing groove on…

Comment or question via
FYI: This post was migrated over from another blogging engine. If you encounter any issues please let me know on . Thanks.