Learn how to avoid 5 common database traps when building a coupon system

We shipped a coupon system last week. Took one day. Most of the time was spent fixing bugs that only appear when things happen at the same time—race conditions.

Coupon systems look trivial. Store a code, check if it's valid, apply a discount. But underneath that simplicity is a minefield of race conditions and edge cases. The kind that pass every test, work fine in staging, then blow up when two customers click "Apply" at the same moment.

Here's what we learned.

The Setup

MilkStraw AI helps companies reduce their AWS bills. We analyze their cloud usage, find savings opportunities, and take a percentage of what we save them. Think of it as a success-fee model: if we save you $10,000/month, we take 20% ($2,000) and you keep the rest.

We wanted promotional pricing. Give early adopters a discount. Let partners offer special rates. The usual SaaS promo code stuff.

The domain model looks like this:

• Coupon: The promo code itself. Has a code (SUMMER25), a validity window (start/end dates), a duration (how many months the discount lasts), and a discounted fee percentage (e.g., 10% instead of our standard 20%).

• Organization: A company using MilkStraw. Has AWS accounts, gets monthly bills.

• User: A person who belongs to one or more organizations.

• Redemption: The record that ties it together. When a user enters a promo code for their organization, we create a redemption with a months_remaining counter.

• Bill: Monthly invoice for an organization. References the active redemption (if any) to apply the discounted rate.

Each billing cycle, we create a bill for each organization. If they have an active redemption, we use the coupon's discounted fee instead of the standard rate. Then we decrement months_remaining. When it hits zero, the discount stops.

Simple enough. Here's where it breaks.

1. The Double Redemption Race (TOCTOU)

A Time-of-Check to Time-of-Use (TOCTOU) bug occurs when two users from the same organization click "Apply" simultaneously. Both requests see no existing redemption and create one, resulting in duplicate discounts.

Each organization should only redeem a given coupon once. We added a validation:

def already_redeemed_for_organization?(organization)
  redemptions.exists?(organization:)
end

def already_redeemed_for_organization?(organization)
  redemptions.exists?(organization:)
end

def already_redeemed_for_organization?(organization)
  redemptions.exists?(organization:)
end

Simple check. If a redemption exists for this org, reject. Except two users from the same organization can hit "Apply" at the same moment.

Both requests check already_redeemed_for_organization?. Both see no existing redemption. Both create one. Now the organization has two redemptions for the same coupon.

The state changes between when you check it and when you act on it. It's a classic "check-then-act" race condition.

Here's how it plays out:

This isn't theoretical. It happens when companies have multiple admins. Two people see a promo code in Slack, both rush to apply it. Milliseconds apart.

The fix is a database-level unique constraint:

add_index :coupon_redemptions, [:coupon_id, :organization_id],
          unique: true,
          name: 'idx_coupon_redemptions_coupon_org_unique'

add_index :coupon_redemptions, [:coupon_id, :organization_id],
          unique: true,
          name: 'idx_coupon_redemptions_coupon_org_unique'

add_index :coupon_redemptions, [:coupon_id, :organization_id],
          unique: true,
          name: 'idx_coupon_redemptions_coupon_org_unique'

Then catch the constraint violation:

def call
  # ... validation and redemption creation
rescue ActiveRecord::RecordNotUnique
  error_result(:already_redeemed)
end

def call
  # ... validation and redemption creation
rescue ActiveRecord::RecordNotUnique
  error_result(:already_redeemed)
end

def call
  # ... validation and redemption creation
rescue ActiveRecord::RecordNotUnique
  error_result(:already_redeemed)
end

Application code validates first (fast feedback for users). Database constraints catch the race condition (correctness guarantee). Both layers matter.

2. The Coupon Stacking Problem and Write Skew

An organization should only have one active coupon at a time. If you're getting 50% off for 3 months, you shouldn't be able to stack another 25% off on top.

We added a check:

def validate_redemption_limits(coupon)
  return :organization_has_active_redemption if @organization.active_coupon_redemption.present?
  # ...
end

def validate_redemption_limits(coupon)
  return :organization_has_active_redemption if @organization.active_coupon_redemption.present?
  # ...
end

def validate_redemption_limits(coupon)
  return :organization_has_active_redemption if @organization.active_coupon_redemption.present?
  # ...
end

Same race condition. Two different promo codes, submitted simultaneously. Both pass validation. Both create redemptions. Organization now has two active discounts.

In database terms, this is called write skew. Two transactions read overlapping data, make decisions based on what they read, and write back results that violate an invariant. Neither transaction saw the other's write.

The solution is a partial unique index (also called a filtered index):

add_index :coupon_redemptions, :organization_id,
          unique: true,
          where: 'months_remaining > 0',
          name: 'idx_coupon_redemptions_one_active_per_org'

add_index :coupon_redemptions, :organization_id,
          unique: true,
          where: 'months_remaining > 0',
          name: 'idx_coupon_redemptions_one_active_per_org'

add_index :coupon_redemptions, :organization_id,
          unique: true,
          where: 'months_remaining > 0',
          name: 'idx_coupon_redemptions_one_active_per_org'

This says: among all redemptions where months_remaining > 0, each organization can appear only once. When the redemption expires (months hit zero), it falls out of the index, and the organization can redeem a new coupon.

Partial indexes are underused. They let you enforce constraints that only apply to a subset of rows. "Unique among active records" is a pattern that shows up constantly: one active subscription per user, one pending order per cart, one draft post per author.

We also need to distinguish between the two race condition errors:

def call
  # ... validation and redemption creation
rescue ActiveRecord::RecordNotUnique => e
  if e.message.include?('idx_coupon_redemptions_one_active_per_org')
    error_result(:organization_has_active_redemption)
  else
    error_result(:already_redeemed)
  end
end

def call
  # ... validation and redemption creation
rescue ActiveRecord::RecordNotUnique => e
  if e.message.include?('idx_coupon_redemptions_one_active_per_org')
    error_result(:organization_has_active_redemption)
  else
    error_result(:already_redeemed)
  end
end

def call
  # ... validation and redemption creation
rescue ActiveRecord::RecordNotUnique => e
  if e.message.include?('idx_coupon_redemptions_one_active_per_org')
    error_result(:organization_has_active_redemption)
  else
    error_result(:already_redeemed)
  end
end

Different constraints, different user messages. Parsing the error message feels hacky, but it works and it's explicit about what went wrong.

3. The Cascading Delete Trap

A user redeems a coupon for their company. A month later, that user leaves the company and gets deleted from the system. What happens to the discount?

With dependent: :destroy on the association:

class User < ApplicationRecord
  has_many :coupon_redemptions, dependent: :destroy
end

class User < ApplicationRecord
  has_many :coupon_redemptions, dependent: :destroy
end

class User < ApplicationRecord
  has_many :coupon_redemptions, dependent: :destroy
end

The redemption gets deleted. The company loses its promotional pricing mid-way through. Worse: the coupon's redemptions_count decrements via counter cache, potentially reopening a sold-out promotion.

This is a cascading delete gone wrong. The database faithfully maintains referential integrity by removing child records when the parent disappears. But referential integrity isn't the same as business logic integrity.

The discount doesn't belong to the user. It belongs to the organization. The user just happened to type in the code.

Fix:

class User < ApplicationRecord
  has_many :coupon_redemptions, dependent: :nullify
end

class Coupon::Redemption < ApplicationRecord
  belongs_to :user, optional: true
end

class User < ApplicationRecord
  has_many :coupon_redemptions, dependent: :nullify
end

class Coupon::Redemption < ApplicationRecord
  belongs_to :user, optional: true
end

class User < ApplicationRecord
  has_many :coupon_redemptions, dependent: :nullify
end

class Coupon::Redemption < ApplicationRecord
  belongs_to :user, optional: true
end

Now when a user is deleted, their redemptions survive with user_id set to null. The organization keeps its discount. The counter cache stays accurate. We lose the audit trail of who entered the code, but that's a reasonable tradeoff.

This pattern applies anywhere you have "who did it" vs "who benefits from it" ownership. The click-to-buy user vs the account that owns the purchase. The admin who invited a teammate vs the team that gains the member.

4. The Case Sensitivity Trap

Users type coupon codes wrong. summer25 instead of SUMMER25. If your code lookup is case-sensitive, they get "invalid code" errors and file support tickets.

So you add a normalizer:

class Coupon < ApplicationRecord
  normalizes :code, with: -> { it.strip.upcase }
end

class Coupon < ApplicationRecord
  normalizes :code, with: -> { it.strip.upcase }
end

class Coupon < ApplicationRecord
  normalizes :code, with: -> { it.strip.upcase }
end

The normalizes callback (Rails 7.1+) handles the application layer. All codes entered through your app get uppercased before storage.

But there's a gap. What if someone creates SUMMER25 and summer25 as separate coupons through a direct database insert, a Rails console session, or a bug in an admin API?

The unique index needs to match:

add_index :coupons, 'lower(code)', unique: true,
          name: 'index_coupons_on_lower_code'

add_index :coupons, 'lower(code)', unique: true,
          name: 'index_coupons_on_lower_code'

add_index :coupons, 'lower(code)', unique: true,
          name: 'index_coupons_on_lower_code'

This is called an expression index (or functional index). Instead of indexing the column value directly, you index a function of it. lower(code) ensures SUMMER25 and Summer25 collide at the database level.

PostgreSQL, MySQL 8+, and SQLite all support this. If you have case-insensitive uniqueness requirements, expression indexes are the reliable way to enforce them.

5. The Half-Created Bill

Creating a bill and decrementing the coupon's remaining months are two operations. What if one fails?

Original code:

def create_organization_bill(organization)
  coupon_redemption = organization.active_coupon_redemption

  bill = organization.bills.create!(
    fee_percentage: fee_percentage(organization, coupon_redemption),
    coupon_redemption:
  )

  coupon_redemption&.decrement_month!
end

def create_organization_bill(organization)
  coupon_redemption = organization.active_coupon_redemption

  bill = organization.bills.create!(
    fee_percentage: fee_percentage(organization, coupon_redemption),
    coupon_redemption:
  )

  coupon_redemption&.decrement_month!
end

def create_organization_bill(organization)
  coupon_redemption = organization.active_coupon_redemption

  bill = organization.bills.create!(
    fee_percentage: fee_percentage(organization, coupon_redemption),
    coupon_redemption:
  )

  coupon_redemption&.decrement_month!
end

If decrement_month! raises an exception, the bill exists but the month wasn't decremented. Next billing cycle, they get the discount again. Free month.

This is a partial failure leading to inconsistent state. The fix is atomicity (the A in ACID). Wrap both operations in a transaction:

def create_organization_bill(organization)
  coupon_redemption = organization.active_coupon_redemption

  ActiveRecord::Base.transaction do
    bill = organization.bills.create!(
      fee_percentage: fee_percentage(organization, coupon_redemption),
      coupon_redemption:
    )

    coupon_redemption&.decrement_month!
  end
end

def create_organization_bill(organization)
  coupon_redemption = organization.active_coupon_redemption

  ActiveRecord::Base.transaction do
    bill = organization.bills.create!(
      fee_percentage: fee_percentage(organization, coupon_redemption),
      coupon_redemption:
    )

    coupon_redemption&.decrement_month!
  end
end

def create_organization_bill(organization)
  coupon_redemption = organization.active_coupon_redemption

  ActiveRecord::Base.transaction do
    bill = organization.bills.create!(
      fee_percentage: fee_percentage(organization, coupon_redemption),
      coupon_redemption:
    )

    coupon_redemption&.decrement_month!
  end
end

If either operation fails, both roll back. Atomicity.

One thing transactions don't solve: idempotency. If your job succeeds, commits the transaction, then crashes before marking itself complete, it might retry and create a duplicate bill. We handle this by checking if a bill already exists for the organization in the current billing period before creating a new one. Transactions guarantee consistency within a single attempt. Idempotency guards against repeated attempts.

The test for rollback behavior is important too:

test 'rolls back bill creation when decrement_month! fails' do
  Coupon::Redemption.any_instance.stubs(:decrement_month!).raises(ActiveRecord::RecordInvalid)

  assert_no_difference 'Organization::Bill.count' do
    assert_raises(ActiveRecord::RecordInvalid) do
      CreateOrganizationBillJob.perform_now(organization)
    end
  end
end

test 'rolls back bill creation when decrement_month! fails' do
  Coupon::Redemption.any_instance.stubs(:decrement_month!).raises(ActiveRecord::RecordInvalid)

  assert_no_difference 'Organization::Bill.count' do
    assert_raises(ActiveRecord::RecordInvalid) do
      CreateOrganizationBillJob.perform_now(organization)
    end
  end
end

test 'rolls back bill creation when decrement_month! fails' do
  Coupon::Redemption.any_instance.stubs(:decrement_month!).raises(ActiveRecord::RecordInvalid)

  assert_no_difference 'Organization::Bill.count' do
    assert_raises(ActiveRecord::RecordInvalid) do
      CreateOrganizationBillJob.perform_now(organization)
    end
  end
end

If you don't test the rollback behavior explicitly, you'll only discover it's broken when customers complain about missing charges or duplicate discounts.

The Pattern

Every one of these bugs shares a root cause: assuming atomicity where none exists.

• Two requests can run the same check simultaneously

• A user can be deleted while their data is being processed

• Two operations that "always happen together" can fail independently

In a single-user, single-threaded world, none of this happens. Everything works in sequence. But production has concurrent requests, background jobs, admin panels, and cascading deletions all happening simultaneously.

The database is your friend here. Unique indexes, partial indexes, expression indexes, foreign keys, and transactions all exist to enforce invariants that application code can't reliably maintain.

Application validations are for user feedback. Database constraints are for correctness. Use both.