Timeline-Based Query Scoping for Data Corruption Fixes

When a bug corrupts data, you need to fix only the affected records—not the entire table. Here’s how to use timestamp-based scoping to target exactly the right rows.

The Problem

A code change introduced a bug that incorrectly populated a field. The bug ran in production for 5 days before being caught. You need to fix the corrupted data without touching records created before or after that window.

The Pattern

Use created_at or audit timestamps to scope your data migration to the exact corruption window:

use Illuminate\Database\Migrations\Migration;
use Illuminate\Support\Facades\DB;

class FixCorruptedCategoryAssignments extends Migration
{
    public function up()
    {
        // Bug deployed: Feb 20, 2026 at 14:30 UTC
        // Bug fixed: Feb 25, 2026 at 09:15 UTC
        
        $bugStartTime = '2026-02-20 14:30:00';
        $bugEndTime = '2026-02-25 09:15:00';
        
        // Only fix records created during the corruption window
        DB::table('products')
            ->whereBetween('created_at', [$bugStartTime, $bugEndTime])
            ->where('category_id', 0)  // The corrupted value
            ->update([
                'category_id' => DB::raw('(
                    SELECT categories.id 
                    FROM categories 
                    WHERE categories.slug = products.category_slug
                    LIMIT 1
                )')
            ]);
    }
}

How to Find Your Timeline

Reconstruct the bug timeline from these sources:

1. Deployment logs — When was the bad code deployed?
2. Git history — When was the buggy commit merged?
3. Error monitoring — When did related errors start appearing?
4. User reports — When did people first complain?
5. Fix deployment — When was the patch deployed?

Your scope window is: bug deployed → bug fixed.

Why This Matters

Scoping by timestamp prevents two disasters:

• Over-fixing: Applying “fixes” to records that were never broken
• Under-fixing: Missing corrupted records because your scope was too narrow

For example, if you only fix records with category_id = 0 without the timestamp scope, you might accidentally “fix” legitimate records that intentionally have no category (like drafts or templates).

Testing Your Scope

Before running the migration:

// Count affected records
$count = DB::table('products')
    ->whereBetween('created_at', [$bugStartTime, $bugEndTime])
    ->where('category_id', 0)
    ->count();

echo "Will fix {$count} records\n";

Compare this count against your expectations. If the bug affected ~100 orders per day for 5 days, you’d expect ~500 records. If you see 10,000, your scope is wrong.

Edge Cases

Bug Affected Updates, Not Inserts

If the bug corrupted existing records (not new ones), use updated_at instead:

DB::table('products')
    ->whereBetween('updated_at', [$bugStartTime, $bugEndTime])
    ->where('category_id', 0)
    ->update(['category_id' => /* fix logic */]);

Multiple Deployment Windows

If the bug was deployed, rolled back, then re-deployed, use multiple scopes:

$windows = [
    ['2026-02-20 14:30:00', '2026-02-20 18:00:00'],  // First deployment
    ['2026-02-24 10:00:00', '2026-02-25 09:15:00'],  // Second deployment
];

foreach ($windows as [$start, $end]) {
    DB::table('products')
        ->whereBetween('created_at', [$start, $end])
        ->where('category_id', 0)
        ->update(['category_id' => /* fix logic */]);
}

Verification

After running the migration, verify the fix:

// Should return 0
$remaining = DB::table('products')
    ->whereBetween('created_at', [$bugStartTime, $bugEndTime])
    ->where('category_id', 0)
    ->count();

if ($remaining > 0) {
    echo "WARNING: {$remaining} records still corrupted\n";
}

Timeline-based scoping turns a risky bulk update into a surgical fix. Analyze first, scope precisely, verify after.