Product Import Rollback on Failure

Implementation of Rollback for Failed Product Imports

An import with column mapping errors can rewrite prices with incorrect data for thousands of products. Without a rollback mechanism, the only option is to restore from a database backup, which takes hours and brings down the entire site. Import rollback returns the catalog to the "before" state in minutes.

Rollback Strategies

1. Snapshot Before Import (Reliable, Expensive in Space)

Before import, save a snapshot of affected rows:

CREATE TABLE import_product_snapshots (
    id          bigserial PRIMARY KEY,
    import_id   int REFERENCES import_runs(id) ON DELETE CASCADE,
    product_id  int,
    operation   varchar(10),   -- create | update (delete separately)
    data_before jsonb,         -- state BEFORE import (NULL for create)
    created_at  timestamptz DEFAULT now()
);

2. Soft Delete for New Products (Create)

Products created during import are marked deleted_at on rollback — not physically deleted.

3. Changelog / Event Sourcing (For Complex Catalogs)

Each change is written as an event. Rollback = applying reverse events. More complex to implement, but allows rollback to any point in time.

Saving Snapshot Before Processing

class ImportSnapshotService
{
    public function captureBeforeImport(int $importId, array $skus, int $sourceId): void
    {
        // Get existing product data that we'll change
        $products = Product::whereIn('sku', $skus)
            ->where('source_id', $sourceId)
            ->get(['id', 'sku', 'name', 'price', 'qty', 'description',
                   'category_id', 'deleted_at', 'updated_at']);

        $snapshots = $products->map(fn($p) => [
            'import_id'   => $importId,
            'product_id'  => $p->id,
            'operation'   => 'update',
            'data_before' => json_encode($p->toArray()),
            'created_at'  => now()->toDateTimeString(),
        ])->all();

        // Batch insert
        foreach (array_chunk($snapshots, 1000) as $chunk) {
            ImportProductSnapshot::insert($chunk);
        }
    }

    public function captureNewProduct(int $importId, int $productId): void
    {
        ImportProductSnapshot::create([
            'import_id'   => $importId,
            'product_id'  => $productId,
            'operation'   => 'create',
            'data_before' => null,
        ]);
    }
}

Rollback Mechanism

class ImportRollbackService
{
    public function rollback(ImportRun $import): RollbackResult
    {
        if (!in_array($import->status, ['success', 'partial', 'failed'])) {
            throw new \RuntimeException('Import is not in a rollbackable state');
        }

        if ($import->rolled_back_at) {
            throw new \RuntimeException('Import already rolled back');
        }

        $restored = $deleted = 0;

        DB::transaction(function () use ($import, &$restored, &$deleted) {
            $snapshots = ImportProductSnapshot::where('import_id', $import->id)
                ->orderByDesc('id') // reverse order for dependencies
                ->get();

            foreach ($snapshots as $snapshot) {
                if ($snapshot->operation === 'create') {
                    // Created products — delete (soft)
                    Product::find($snapshot->product_id)?->delete();
                    $deleted++;
                } else {
                    // Updated products — restore previous state
                    $before = json_decode($snapshot->data_before, true);
                    Product::where('id', $snapshot->product_id)->update($before);
                    $restored++;
                }
            }

            $import->update([
                'rolled_back_at'  => now(),
                'rolled_back_by'  => auth()->id(),
                'rollback_result' => compact('restored', 'deleted'),
            ]);
        });

        return new RollbackResult($restored, $deleted);
    }
}

Everything executes in a single transaction — either rollback completes fully, or nothing changes.

Incremental Rollback for Large Imports

Rolling back 100,000 rows in one transaction is risky (long lock). Use batching:

public function rollbackInBatches(ImportRun $import, int $batchSize = 1000): void
{
    $totalSnapshots = ImportProductSnapshot::where('import_id', $import->id)->count();
    $offset         = 0;

    while ($offset < $totalSnapshots) {
        DB::transaction(function () use ($import, $batchSize, $offset) {
            $snapshots = ImportProductSnapshot::where('import_id', $import->id)
                ->orderByDesc('id')
                ->skip($offset)
                ->take($batchSize)
                ->get();

            foreach ($snapshots as $snapshot) {
                $this->applySnapshot($snapshot);
            }
        });

        $offset += $batchSize;
        ImportRun::find($import->id)->increment('rollback_progress', $batchSize);
    }
}

Rollback Applicability Conditions

Not every import can be rolled back:

public function canRollback(ImportRun $import): bool
{
    return !$import->rolled_back_at
        && $import->created_at->isAfter(now()->subDays(7))
        && ImportProductSnapshot::where('import_id', $import->id)->exists();
}

Cascading Rollback of Related Data

Import affects not just the products table. On rollback, account for:

private function applySnapshot(ImportProductSnapshot $snapshot): void
{
    DB::transaction(function () use ($snapshot) {
        if ($snapshot->operation === 'create') {
            // Delete all related data
            ProductImage::where('product_id', $snapshot->product_id)->delete();
            ProductSpec::where('product_id', $snapshot->product_id)->delete();
            ProductFilterValue::where('product_id', $snapshot->product_id)->delete();
            Product::find($snapshot->product_id)?->forceDelete();
        } else {
            $before = json_decode($snapshot->data_before, true);
            Product::where('id', $snapshot->product_id)->update(
                array_intersect_key($before, array_flip(['name', 'price', 'qty', 'description', 'category_id']))
            );
        }
    });
}

Snapshot Storage and TTL

Snapshots take up space. Retention policy:

// Artifacts stored 7 days after successful import
$schedule->command('import:cleanup-snapshots --days=7')->daily();

ImportProductSnapshot::whereHas('run', fn($q) =>
    $q->where('status', 'success')
      ->where('completed_at', '<', now()->subDays(7))
)->delete();

Notification After Rollback

ImportRolledBackNotification::send($import->triggeredBy, $import);
// Email: "Import #1847 rolled back. Restored: 4,312 products, deleted: 88."

Timeline

• Snapshot before import (captureBeforeImport), basic rollback in transaction — 2 days
• Rollback batching, cascade through related tables, applicability check — +1 day
• Admin UI (rollback button, progress status, snapshot TTL) — +1 day