ChatGPT invoice numbers concern

chat.md

write a concern to add a garantueed unique and sequential invoice number to an active record model

Here is an example of a concern that adds a guaranteed unique and sequential invoice number to an ActiveRecord model:

module InvoiceNumberable
  extend ActiveSupport::Concern

  included do
    before_create :generate_invoice_number
    validates :invoice_number, presence: true, uniqueness: true
  end

  private

  def generate_invoice_number
    current_date = Date.today.strftime('%y%m')
    last_invoice = self.class.where('invoice_number LIKE ?', "#{current_date}%").order(invoice_number: :desc).first
    if last_invoice.present?
      last_number = last_invoice.invoice_number.slice(4,6).to_i + 1
    else
      last_number = 1
    end
    self.invoice_number = "#{current_date}-#{format('%06d', last_number)}"
  end
end

In this concern, we define a before_create callback that generates the invoice number before the record is saved, and a validation that ensures that the invoice number is present and unique.

The generate_invoice_number method first gets the current date in the format yymm. It then queries the table for the last invoice number that starts with the current date. If there is a previous invoice, it increments the last 6 digits and formats it with leading zeroes. If there are no previous invoices, it sets the last 6 digits to 1. Finally, it concatenates the current date and the formatted invoice number to create the final invoice number.

To use this concern in a model, simply include it and the invoice_number attribute will be handled automatically:

class Invoice < ApplicationRecord
  include InvoiceNumberable
end

invoices belong to a park, can you make the sequence unique per park

Sure! Here is an updated version of the concern that generates a unique sequence per park:

module InvoiceNumberable
  extend ActiveSupport::Concern

  included do
    before_create :generate_invoice_number
    validates :invoice_number, presence: true, uniqueness: true
  end

  private

  def generate_invoice_number
    current_date = Date.today.strftime('%y%m')
    last_invoice = self.class.where(park_id: park_id).where('invoice_number LIKE ?', "#{current_date}%").order(invoice_number: :desc).first
    if last_invoice.present?
      last_number = last_invoice.invoice_number.slice(4,6).to_i + 1
    else
      last_number = 1
    end
    self.invoice_number = "#{current_date}-#{format('%06d', last_number)}"
  end
end


With this updated concern, we first include a park_id attribute in the query to find the last invoice number. This ensures that we are only looking for invoice numbers that belong to the same park as the current invoice.

To use this updated concern in a model, simply include it and specify that the model belongs to a park:

class Invoice < ApplicationRecord
  include InvoiceNumberable
  belongs_to :park
end

Make sure that the invoices table has a park_id column and that the parks table exists.

great, but i think you are missing a scope on the validates :invoice_number

You are right! In order to make the validates :invoice_number statement scope to the park_id, we can modify it like this:

validates :invoice_number, presence: true, uniqueness: { scope: :park_id }

This will ensure that the uniqueness validation checks for an already existing invoice number only within the same park. The complete updated InvoiceNumberable concern would look like this:

module InvoiceNumberable
  extend ActiveSupport::Concern

  included do
    before_create :generate_invoice_number
    validates :invoice_number, presence: true, uniqueness: { scope: :park_id }
  end

  private

  def generate_invoice_number
    current_date = Date.today.strftime('%y%m')
    last_invoice = self.class.where(park_id: park_id).where('invoice_number LIKE ?', "#{current_date}%").order(invoice_number: :desc).first
    if last_invoice.present?
      last_number = last_invoice.invoice_number.slice(4,6).to_i + 1
    else
      last_number = 1
    end
    self.invoice_number = "#{current_date}-#{format('%06d', last_number)}"
  end
end

With this updated concern, we ensure that the uniqueness validation is scoped to the park_id, so it will only check for uniqueness within the same park.

the invoice numbers should have a prefix, configurable per park, so that the invoice numbers look like for instance BEX0000001, BEX0000002 if that prefix in BEX. You can remove the year and month bit.

Sure, we can modify the generate_invoice_number method to include a prefix that is configurable per park. Here's the updated concern:

module InvoiceNumberable
  extend ActiveSupport::Concern

  included do
    before_create :generate_invoice_number
    validates :invoice_number, presence: true, uniqueness: { scope: :park_id }
  end

  private

  def generate_invoice_number
    prefix = park.invoice_number_prefix
    last_invoice = self.class.where(park_id: park_id).where('invoice_number LIKE ?', "#{prefix}%").order(invoice_number: :desc).first
    if last_invoice.present?
      last_number = last_invoice.invoice_number.slice(prefix.size,6).to_i + 1
    else
      last_number = 1
    end
    self.invoice_number = "#{prefix}#{format('%06d', last_number)}"
  end
end

In this updated InvoiceNumberable concern, we first retrieve the prefix value for the park to which the invoice belongs. We then use the prefix value to look up the last invoice number for that park. We set the last number to 1 if there are no previous invoices. Finally, we concatenate the prefix and the formatted last number to create the new invoice number.

To use this concern in a model, simply include it and specify that the model belongs to a park:

class Invoice < ApplicationRecord
  include InvoiceNumberable
  belongs_to :park
end

Make sure that the parks table has a invoice_number_prefix column that contains the prefix value for each park you want to associate the invoice numbers with.

could this lead to race conditions where two invoice get the same number?

Yes, it is possible that race conditions occur when generating invoice numbers using this concern. If two invoices are created at the same time, they can both retrieve the same last invoice number and generate the same invoice number.

To prevent this, we can make use of database-level locking to ensure that no two invoices are assigned the same invoice number at the same time. One way to do this is to wrap the generate_invoice_number method in a database transaction with a row-level advisory lock. This ensures that the current thread holds a lock on the invoice number row while generating the new invoice number, and prevents other threads from reading or modifying the same row at the same time.

Here's the updated concern that uses database-level locking:

module InvoiceNumberable
  extend ActiveSupport::Concern

  included do
    before_create :generate_invoice_number_with_lock
    validates :invoice_number, presence: true, uniqueness: { scope: :park_id }
  end

  private

  def generate_invoice_number_with_lock
    prefix = park.invoice_number_prefix
    self.invoice_number = nil
    self.class.transaction do
      self.class.connection.execute('SELECT pg_advisory_xact_lock(:id)', id: InvoiceNumberable::LOCK_ID)
      last_invoice = self.class.where(park_id: park_id).where('invoice_number LIKE ?', "#{prefix}%").order(invoice_number: :desc).first
      last_number = last_invoice.present? ? last_invoice.invoice_number.slice(prefix.size,6).to_i + 1 : 1
      self.invoice_number = "#{prefix}#{format('%06d', last_number)}"
    end
  end

  LOCK_ID = 45679_0126_1147_1222
end

In this updated concern, we've added a generate_invoice_number_with_lock method which retrieves a row-level advisory lock on a constant integer value LOCK_ID. This ensures that only one thread at a time can retrieve or modify the invoice numbers associated with any given park.

Note that we set self.invoice_number to nil at the start of the method to avoid any existing invoice numbers that may have been assigned by other callback, that do not follow this rule, to complicate the logic.

Also, we've wrapped the DB query to retrieve the last invoice by park in a transaction, so that the advisory lock is held for the entire time it takes to generate the invoice number, eliminating the possibility of race conditions.

With this updated concern, we can ensure that the generated invoice numbers are always unique for every park, even under heavy load where many invoices are being generated concurrently.

would it be a bad idea to do park.lock instead of the advisory lock?

Using record-level locks (park.lock) to prevent race conditions during invoice number generation may not be the best approach. It can be less efficient than using advisory locks because it locks the entire parks table in the database whenever an invoice is generated for a park, potentially causing contention and slowing down other parts of the application that need to access the parks table.

Advisory locks, on the other hand, are much more lightweight and are designed specifically for this kind of scenario. When advisory locks are used, only the invoice number rows for the relevant park are locked, minimizing the potential for contention with other parts of the application.

In general, it's best to use advisory locks for situations like this where you need to synchronize access to a shared resource across multiple threads. Record-level locks should be reserved for situations where you need to lock specific records for a longer duration, such as when updating a large number of records.

Therefore, I would recommend using advisory locks instead of record-level locks in this scenario.