Production Data Cleanup Guide

Purpose: Clean up test data when converting from development/testing to production

Last Updated: October 3, 2025

🚨 CRITICAL: Read This First

ALWAYS backup your database before deleting anything in production!

# Create a backup
pg_dump quarry_rentals_production > backup_$(date +%Y%m%d_%H%M%S).sql

# Or if using Kamal on remote server
ssh your-server "pg_dump -U postgres quarry_rentals_production" > backup_$(date +%Y%m%d_%H%M%S).sql

Restore backup if needed:

psql quarry_rentals_production < backup_20251003_120000.sql

Strategy Overview

There are 4 approaches to cleaning test data:

Rake Task (Recommended) - Safe, repeatable, with confirmation prompts
Rails Console - Quick for one-off deletions
SQL Console - Direct database access (advanced)
Full Reset - Nuclear option (development only)

Method 1: Rake Task (Recommended)

Setup

Create the cleanup rake task file:

File: lib/tasks/cleanup.rake

namespace :cleanup do
  desc "Delete all test data (use with caution!)"
  task test_data: :environment do
    puts "🧹 Cleaning up test data..."
    puts ""

    # Preview what will be deleted
    test_vendors = Vendor.where("name LIKE ? OR name LIKE ?", "%Test%", "%Demo%")
    test_companies = Company.where("name LIKE ? OR name LIKE ?", "%Test%", "%Demo%")
    test_orders = Order.where("order_number LIKE ?", "%TEST%")

    puts "This will delete:"
    puts "  - #{test_vendors.count} test vendors"
    puts "  - #{test_companies.count} test companies"
    puts "  - #{test_orders.count} test orders"
    puts "  - All related records (bills, cards, contacts, etc.)"
    puts ""
    print "Type 'DELETE' to confirm: "

    confirmation = STDIN.gets.chomp
    unless confirmation == "DELETE"
      puts "❌ Cleanup cancelled"
      exit
    end

    # Wrap in transaction for safety
    ActiveRecord::Base.transaction do
      puts "\n🗑️  Deleting test data..."

      # Delete test vendors and their related records
      test_vendors.each do |vendor|
        puts "  Deleting vendor: #{vendor.name}"
        vendor.destroy
      end

      # Delete test companies
      test_companies.each do |company|
        puts "  Deleting company: #{company.name}"
        company.destroy
      end

      # Delete test orders
      puts "  Deleting #{test_orders.count} test orders..."
      test_orders.destroy_all

      # Clean up orphaned records
      orphaned_bills = VendorBill.left_joins(:vendor).where(vendors: { id: nil })
      if orphaned_bills.any?
        puts "  Cleaning #{orphaned_bills.count} orphaned vendor bills..."
        orphaned_bills.destroy_all
      end

      orphaned_cards = RampVirtualCard.left_joins(:vendor).where(vendors: { id: nil })
      if orphaned_cards.any?
        puts "  Cleaning #{orphaned_cards.count} orphaned Ramp cards..."
        orphaned_cards.destroy_all
      end

      puts "\n✅ Test data cleanup complete!"
    end
  rescue => e
    puts "\n❌ Error during cleanup: #{e.message}"
    puts "Rolling back all changes..."
    raise ActiveRecord::Rollback
  end

  desc "Delete specific vendor and all related records"
  task :vendor, [:vendor_id] => :environment do |t, args|
    if args[:vendor_id].blank?
      puts "❌ Error: Please provide a vendor ID"
      puts "Usage: bin/rails cleanup:vendor[5]"
      exit
    end

    vendor = Vendor.find_by(id: args[:vendor_id])
    unless vendor
      puts "❌ Error: Vendor with ID #{args[:vendor_id]} not found"
      exit
    end

    puts "⚠️  This will delete vendor '#{vendor.name}' and ALL related records:"
    puts "   - #{vendor.orders.count} orders"
    puts "   - #{vendor.vendor_bills.count} vendor bills"
    puts "   - #{vendor.ramp_virtual_cards.count} Ramp cards"
    puts "   - #{vendor.vendor_products.count} vendor products"
    puts ""
    print "Type 'DELETE' to confirm: "

    confirmation = STDIN.gets.chomp
    if confirmation == "DELETE"
      vendor.destroy
      puts "✅ Vendor '#{vendor.name}' deleted successfully"
    else
      puts "❌ Deletion cancelled"
    end
  end

  desc "Delete specific company/customer and all related records"
  task :company, [:company_id] => :environment do |t, args|
    if args[:company_id].blank?
      puts "❌ Error: Please provide a company ID"
      puts "Usage: bin/rails cleanup:company[10]"
      exit
    end

    company = Company.find_by(id: args[:company_id])
    unless company
      puts "❌ Error: Company with ID #{args[:company_id]} not found"
      exit
    end

    puts "⚠️  This will delete company '#{company.name}' and ALL related records:"
    puts "   - #{company.orders.count} orders"
    puts "   - #{company.contacts.count} contacts"
    puts "   - #{company.invoices.count} invoices"
    puts ""
    print "Type 'DELETE' to confirm: "

    confirmation = STDIN.gets.chomp
    if confirmation == "DELETE"
      company.destroy
      puts "✅ Company '#{company.name}' deleted successfully"
    else
      puts "❌ Deletion cancelled"
    end
  end

  desc "Delete orders within a date range"
  task :orders_by_date, [:start_date, :end_date] => :environment do |t, args|
    start_date = Date.parse(args[:start_date])
    end_date = args[:end_date] ? Date.parse(args[:end_date]) : Date.current

    orders = Order.where(created_at: start_date..end_date)

    puts "⚠️  This will delete #{orders.count} orders created between #{start_date} and #{end_date}"
    puts ""
    print "Type 'DELETE' to confirm: "

    confirmation = STDIN.gets.chomp
    if confirmation == "DELETE"
      orders.destroy_all
      puts "✅ Orders deleted successfully"
    else
      puts "❌ Deletion cancelled"
    end
  end

  desc "List all vendors (for inspection before deletion)"
  task list_vendors: :environment do
    puts "\n📋 All Vendors:"
    puts "=" * 80
    Vendor.order(:name).each do |v|
      puts "ID: #{v.id.to_s.rjust(3)} | #{v.name.ljust(30)} | Orders: #{v.orders.count.to_s.rjust(3)} | Bills: #{v.vendor_bills.count.to_s.rjust(3)}"
    end
    puts "=" * 80
    puts "Total: #{Vendor.count} vendors\n\n"
  end

  desc "List all companies (for inspection before deletion)"
  task list_companies: :environment do
    puts "\n📋 All Companies:"
    puts "=" * 80
    Company.order(:name).each do |c|
      puts "ID: #{c.id.to_s.rjust(3)} | #{c.name.ljust(30)} | Orders: #{c.orders.count.to_s.rjust(3)} | Contacts: #{c.contacts.count.to_s.rjust(3)}"
    end
    puts "=" * 80
    puts "Total: #{Company.count} companies\n\n"
  end

  desc "List all orders grouped by status"
  task list_orders: :environment do
    puts "\n📋 All Orders by Status:"
    puts "=" * 80
    Order.group(:status).count.each do |status, count|
      puts "#{status.ljust(20)}: #{count} orders"
    end
    puts "=" * 80
    puts "Total: #{Order.count} orders\n\n"
  end

  desc "Show database statistics"
  task stats: :environment do
    puts "\n📊 Database Statistics:"
    puts "=" * 60
    puts "Vendors:           #{Vendor.count}"
    puts "Companies:         #{Company.count}"
    puts "Contacts:          #{Contact.count}"
    puts "Orders:            #{Order.count}"
    puts "Vendor Bills:      #{VendorBill.count}"
    puts "Invoices:          #{Invoice.count}"
    puts "Ramp Cards:        #{RampVirtualCard.count}"
    puts "Ramp Transactions: #{RampTransaction.count}"
    puts "Products:          #{Product.count}"
    puts "Users:             #{User.count}"
    puts "=" * 60
    puts ""
  end
end

Usage

Step 1: Inspect data before deleting

# See what you have
bin/rails cleanup:stats
bin/rails cleanup:list_vendors
bin/rails cleanup:list_companies
bin/rails cleanup:list_orders

Step 2: Delete test data

# Delete all test data (anything with "Test" or "Demo" in name)
bin/rails cleanup:test_data

# Delete specific vendor by ID
bin/rails cleanup:vendor[5]

# Delete specific company by ID
bin/rails cleanup:company[10]

# Delete orders from a date range
bin/rails cleanup:orders_by_date[2025-01-01,2025-02-01]

Method 2: Rails Console

For quick, one-off deletions:

bin/rails console

Common Cleanup Commands

# Delete test vendors
Vendor.where("name LIKE ?", "%Test%").destroy_all
Vendor.where("name LIKE ?", "%Demo%").destroy_all

# Delete specific vendor by ID
Vendor.find(5).destroy

# Delete test companies
Company.where("name LIKE ?", "%Test%").destroy_all

# Delete test orders
Order.where("order_number LIKE ?", "%TEST%").destroy_all

# Delete orders from date range
Order.where("created_at >= ?", "2025-01-01").destroy_all

# Delete all orders with no customer
Order.where(company_id: nil, contact_id: nil).destroy_all

# Delete orphaned vendor bills (no vendor)
VendorBill.left_joins(:vendor).where(vendors: { id: nil }).destroy_all

# Delete orphaned Ramp cards
RampVirtualCard.left_joins(:vendor).where(vendors: { id: nil }).destroy_all

# Delete specific records by ID
VendorBill.where(id: [1, 2, 3]).destroy_all

Bulk Deletion with Progress

# Delete many records with progress indicator
vendors_to_delete = Vendor.where("name LIKE ?", "%Test%")
total = vendors_to_delete.count
vendors_to_delete.find_each.with_index do |vendor, index|
  puts "Deleting #{index + 1}/#{total}: #{vendor.name}"
  vendor.destroy
end

Method 3: SQL Console (Advanced)

⚠️ Use with extreme caution - no callbacks are triggered, foreign keys may break!

bin/rails dbconsole

-- Preview what you'll delete
SELECT id, name FROM vendors WHERE name LIKE '%Test%';
SELECT id, name FROM companies WHERE name LIKE '%Test%';
SELECT id, order_number, status FROM orders WHERE created_at < '2025-01-01';

-- Delete in correct order (respecting foreign keys)
-- Ramp transactions first
DELETE FROM ramp_transactions
WHERE ramp_virtual_card_id IN (
  SELECT id FROM ramp_virtual_cards WHERE vendor_id = 5
);

-- Then Ramp cards
DELETE FROM ramp_virtual_cards WHERE vendor_id = 5;

-- Vendor bill line items
DELETE FROM vendor_bill_line_items
WHERE vendor_bill_id IN (
  SELECT id FROM vendor_bills WHERE vendor_id = 5
);

-- Vendor payments
DELETE FROM vendor_payments WHERE vendor_id = 5;

-- Vendor bills
DELETE FROM vendor_bills WHERE vendor_id = 5;

-- Orders
DELETE FROM orders WHERE vendor_id = 5;

-- Finally, the vendor
DELETE FROM vendors WHERE id = 5;

-- Verify deletion
SELECT COUNT(*) FROM vendors;
SELECT COUNT(*) FROM orders;

Method 4: Full Database Reset (Development Only)

⚠️ NEVER run this in production - it deletes EVERYTHING!

# Development/test environment only
RAILS_ENV=development bin/rails db:drop db:create db:migrate db:seed

Pre-Production Checklist

Before going live, follow this checklist:

1. Backup Everything

# Database backup
pg_dump quarry_rentals_production > backup_pre_cleanup_$(date +%Y%m%d).sql

# Code backup (if not already in git)
git add -A
git commit -m "Pre-production cleanup backup"
git push

2. Review Data

bin/rails cleanup:stats
bin/rails cleanup:list_vendors
bin/rails cleanup:list_companies
bin/rails cleanup:list_orders

3. Identify Test Data

Make a list of what needs to be deleted:

[ ] Test vendor IDs: _________________
[ ] Test company IDs: _________________
[ ] Test order date range: _________________
[ ] Test user accounts: _________________

4. Delete Test Data

# Start with specific deletions
bin/rails cleanup:vendor[ID]
bin/rails cleanup:company[ID]

# Then bulk cleanup
bin/rails cleanup:test_data

5. Verify Cleanup

bin/rails cleanup:stats
bin/rails cleanup:list_vendors
bin/rails cleanup:list_companies

6. Test Production

After cleanup:

[ ] Can create new orders?
[ ] Can create new vendors?
[ ] Can create new customers?
[ ] Can process payments?
[ ] Can create vendor bills?
[ ] Ramp cards working?

Common Scenarios

Scenario 1: "I need to delete all data from testing phase"

# 1. Backup
pg_dump quarry_rentals_production > backup.sql

# 2. Review
bin/rails cleanup:stats

# 3. Delete everything created before a certain date
bin/rails console
Order.where("created_at < ?", "2025-10-01").destroy_all
VendorBill.where("created_at < ?", "2025-10-01").destroy_all
Invoice.where("created_at < ?", "2025-10-01").destroy_all

# 4. Delete test vendors/companies
bin/rails cleanup:test_data

# 5. Verify
bin/rails cleanup:stats

Scenario 2: "I have a few specific test vendors to remove"

# 1. List all vendors
bin/rails cleanup:list_vendors

# 2. Delete specific ones
bin/rails cleanup:vendor[5]
bin/rails cleanup:vendor[12]
bin/rails cleanup:vendor[23]

Scenario 3: "I want to keep some test data for demos"

# Delete all except specific vendors
bin/rails console

# Get IDs to keep
keep_vendor_ids = [1, 5, 9]

# Delete others
Vendor.where.not(id: keep_vendor_ids).destroy_all

Troubleshooting

Error: Foreign key constraint violation

Problem: Can't delete because other records reference it

Solution: Delete dependent records first

# Find what's blocking deletion
vendor = Vendor.find(5)
vendor.orders.count        # How many orders?
vendor.vendor_bills.count  # How many bills?

# Delete dependencies first
vendor.orders.destroy_all
vendor.vendor_bills.destroy_all

# Then delete vendor
vendor.destroy

Error: Record locked / Deadlock detected

Problem: Another process is using the record

Solution: Wait and retry, or check for background jobs

# Check background jobs
bin/rails console
Solid::Queue::Job.all

# Kill stuck jobs if needed
Solid::Queue::Job.where(status: 'running').destroy_all

Error: Transaction rolled back

Problem: Something failed during deletion

Solution: Delete records one at a time to find the issue

# Instead of destroy_all, use find_each
Vendor.where("name LIKE ?", "%Test%").find_each do |vendor|
  begin
    vendor.destroy
    puts "✓ Deleted: #{vendor.name}"
  rescue => e
    puts "✗ Failed to delete #{vendor.name}: #{e.message}"
  end
end

Safety Tips

Always backup first - Cannot be stressed enough!
Test in development - Run cleanup commands in development first
```
RAILS_ENV=development bin/rails cleanup:test_data
```

Use transactions - Wrap deletions in transactions so they can be rolled back

ActiveRecord::Base.transaction do
  # Delete stuff
  raise ActiveRecord::Rollback if something_wrong?
end

Soft deletes - Consider adding deleted_at column instead of hard deleting

# In migration
add_column :vendors, :deleted_at, :datetime
add_index :vendors, :deleted_at

# Then use
vendor.update(deleted_at: Time.current)  # Soft delete
Vendor.where(deleted_at: nil)            # Active records

Audit log - Keep track of what was deleted

# Log before deleting
Rails.logger.info "Deleting vendor #{vendor.id}: #{vendor.name}"
vendor.destroy

Quick Reference

# Inspection
bin/rails cleanup:stats              # See all counts
bin/rails cleanup:list_vendors       # List all vendors
bin/rails cleanup:list_companies     # List all companies
bin/rails cleanup:list_orders        # List orders by status

# Deletion
bin/rails cleanup:test_data          # Delete all test data
bin/rails cleanup:vendor[ID]         # Delete specific vendor
bin/rails cleanup:company[ID]        # Delete specific company
bin/rails cleanup:orders_by_date[START,END]  # Delete orders in date range

# Backup/Restore
pg_dump DB_NAME > backup.sql         # Create backup
psql DB_NAME < backup.sql            # Restore backup

Questions?

Check model associations in app/models/ to understand relationships
Review foreign keys in db/schema.rb
Test deletions in Rails console before running rake tasks
When in doubt, backup first!

Remember: It's easier to restore from backup than to recreate deleted data!