product_purchase_datamodel.rb

# Here is a sketch of some ActiveRecord models which could be used to support the features
# requested. I didn't code the implementation of the state machine as that isn't crucial
# to the question, but I think it would suit the system well and help in designing further
# features.
#
# The instance methods on the Seller model allow for flexibility of the rolling settlement
# window dates.
# 
# As for indexing the tables, the most important one for the models below is:
# [:created_at, :seller_id] on the Transactions table. This makes the rolling settlement window
# queries feasible for very large datasets by optimizing the #unsettled_transactions method
# on the Seller model.
# 

class Product < ActiveRecord::Base
  has_many :transactions
  has_many :customers, through: :transactions
  belongs_to :seller
end

class Seller < ActiveRecord::Base
  has_many :products
  has_many :transactions

  def settle_balance up_until=1.week.ago.beginning_of_week
    transaction do
      make_payment unsettled_balance(up_until)
      settle_complete_transactions(up_until)
    end
  end

  def unsettled_balance up_until=Time.now
    unsettled_transactions(up_until).sum('amount')
  end

  def unsettled_transactions up_until=Time.now
    transactions.where 'created_at < ? AND status = ?', up_until, 'complete'
  end

  def settle_complete_transactions up_until=Time.now
    unsettled_transactions(up_until).update_all status: 'closed'
  end
end

class Customer < ActiveRecord::Base
  has_many :transactions
end

class Transaction < ActiveRecord::Base
  # attributes:
  #   - settled_on: datetime when purchase was credited/debited to seller
  #   - type: for rails STI
  #   - amount: $ amount (negative for refunds)
  #   - refund_for: the id of another transaction
  belongs_to :product
  belongs_to :seller # maintain the seller_id here in case the product assoc is damaged (product deleted?)
  belongs_to :customer
  # I would recommend using a state machine here
  # possible states:
  #   - incomplete (unfinished purchase)
  #   - complete (customer has paid, seller has not yet collected)
  #   - closed (customer has paid, seller collected payment)
  #     - on transition, set setted_on date
end

class Purchase < Transaction
  has_many :refunds, class_name: 'Refund', foreign_key: 'refund_for'
end
class Refund < Transaction
  belongs_to :purchase, class_name: 'Purchase', foreign_key: 'refund_for'
end