⚡ Optimistic CRUD Pipeline: The Factorio Pattern

Deleting or renaming a scan currently blocks the UI for 4-5 seconds while we synchronously:

• Remove the image from Supabase Storage
• Delete job_queue rows
• Delete the scan_uploads row

That delay is network bound and won’t disappear in production. We need a design that provides perceived instant feedback while doing heavy work in the background – just like erasing a tweet on Twitter.

• Optimistic UI – mutate local state immediately, rollback on error.
• Soft Deletes – never hard-delete in the request path; set deleted_at.
• CQRS – write operations are commands; reads come from pre-filtered views.
• Event-Driven – commands raise domain events handled by background workers.
• Idempotency – every handler safe to run twice.
• Observability – structured logs, metrics, DLQ for failed jobs.

1. User clicks Delete Scan.
2. Frontend dispatches optimisticDelete(scanId) → item disappears.
3. API POST /api/commands/delete-scan writes a row to command_queue.
4. API returns 202 Accepted with commandId in <100 ms.
5. Worker (Node or Python) picks up DeleteScanCommand, executes:
• Remove file from Storage
• Delete related job_queue rows
• Hard-delete scan_uploads row
6. On success an ScanDeletedEvent is emitted (for metrics, cache purge).

• Add deleted_at TIMESTAMPTZ & version INT DEFAULT 0 to scan_uploads.
• Create command_queue table:

  id UUID PK, type TEXT, payload JSONB, created_at TIMESTAMPTZ DEFAULT now(), processed_at TIMESTAMPTZ

We expose /api/commands/* endpoints that only insert into command_queue. They run on Vercel Edge or Next.js API, complete in under 100 ms, and never block on Supabase Storage.

• Preferred: Supabase Edge Function (TypeScript) subscribed to command_queue.
• Fallback: Existing Python worker polls every few seconds.
• Each command handled in isolation and retried on failure.

• React Query mutation with onMutate → optimistic removal.
• Use onError to rollback if command rejected.
• Poll or subscribe to scans view (excluding soft-deleted rows) for eventual consistency.

1. D1: Add deleted_at column & create command_queue.
2. D1: Refactor delete/rename API to enqueue commands.
3. D2: Build minimal Edge Function handler for DeleteScanCommand.
4. D2: Update React Query mutations to optimistic UI.
5. D3: Extend pattern to Rename, Retry, and other heavy ops.

// deleteScan.ts (client)
await deleteScanMutation.mutateAsync(scanId);

// /api/commands/delete-scan (server)
export async function POST(req: NextRequest) {
  const { scanId } = await req.json();
  await supabase.from('command_queue').insert({
    type: 'DELETE_SCAN',
    payload: { scanId }
  });
  return NextResponse.json({ accepted: true }, { status: 202 });
}

// edge_function/delete_scan.ts
if (command.type === 'DELETE_SCAN') {
  const { scanId } = command.payload;
  // 1) Delete storage
  // 2) Purge job_queue rows
  // 3) Hard-delete scan_uploads
  // 4) Mark command processed
}