ColoredCow / ColoredCow/performance-adapter-wp

Add guided archival workflow — dry-run, batched execution, and post-archival verification

Open
#39 0 comments 0 reactions 1 assignee Claimed by @Ajay-Singh-Adhikari View on GitHub
Dominant language
PHP
Stars
0
Forks
0
PR merge metrics
No merged PRs in 30d

Description

## Problem

The WooCommerce order archival process today is fully manual:

- SQL is written by hand each time
- Testing on staging before production takes significant time
- Deletes are run in manual batches to avoid timeouts and DB locks
- Post-archival verification (DB size, row counts) is done by querying the DB manually

The full cycle — from decision to verified archival — takes approximately **2 weeks** including staging validation and production execution.

The plugin already detects when archival is needed (table size threshold + QET drift). The missing piece is helping the engineer safely execute the archival once that signal fires.

---

## Goal

Compress the 2-week manual archival cycle into a **supervised, safe, plugin-guided process** — without removing the human from the loop. The engineer makes the call; the plugin removes the guesswork and manual SQL work.

---

## Proposed Workflow

### Step 1 — Dry Run
Before deleting anything, the plugin shows exactly what will be affected:

- Date range that will be archived (based on configured retention window)
- Number of orders that will be deleted
- Estimated row reduction in `wp_woocommerce_order_items` and `wp_woocommerce_order_itemmeta`
- Estimated DB size reduction (MB)

Engineer reviews and confirms before proceeding.

### Step 2 — Batched Execution
Run the archival in configurable batch sizes (e.g. 500 orders per batch) to avoid:
- Query timeouts
- DB lock contention on a live store
- All-or-nothing risk on a single large query

Progress visible in WP Admin — batches completed, rows deleted, estimated remaining.

Pause/resume support so the engineer can stop mid-run if something looks wrong.

### Step 3 — Automatic Post-Archival Verification
After execution completes, the plugin automatically:
- Compares DB size before vs after
- Compares order count and row count before vs after
- Confirms the delta matches the dry-run estimate
- Sets `Last Archival Date` to today
- Resets QET baseline tracking from the new clean state

Engineer sees a summary: *"Deleted 28,400 orders. order_itemmeta reduced from 12.25 GB → 8.1 GB. Baseline QET reset."*

---

## Context

- Current archival trigger: `woo_order_itemmeta_size_mb` crossing the configured threshold (already in dashboard)
- Retention window: configurable in ProPerf Settings (`properf_archival_threshold_years`, default 2 years)
- Last Archival Date: already stored in `properf_last_archival_date` — this workflow should set it automatically on completion
- QET baseline reset: already happens when Last Archival Date changes — this workflow triggers that automatically

### From Kuldeep (MFM engineer, Jul 2026)
> "We write SQL ourselves... it's around a 2-week process including testing and all... Running and deleting [is the hardest part]... Testing is mostly run on staging first and verifying nothing breaks... batches manually... verify by DB size and counting numbers of orders or rows deleted."

---

## Acceptance Criteria

- [ ] Dry-run mode shows: date range, order count, estimated row/size reduction — before any delete runs
- [ ] Batched execution runs in configurable chunk sizes with progress visible in WP Admin
- [ ] Pause/resume supported mid-run
- [ ] Post-archival summary shows before/after DB size and row counts
- [ ] `Last Archival Date` set automatically on successful completion
- [ ] QET baseline reset triggered automatically on completion
- [ ] All operations respect HPOS and legacy order table structures (same dual-path as existing collectors)
- [ ] Dry-run and execution both run on staging safely (no staging-detection required — engineer controls when to run)

## Peer Dev QA Checklist

- [ ] Acceptance criteria of the User Story
- [ ] Admin and non-Admin flows
- [ ] Test with HPOS enabled and disabled
- [ ] Test with large order volumes (verify batching works, no timeout)
- [ ] Verify QET baseline resets correctly after archival completes
- [ ] Verify Last Archival Date is set correctly
- [ ] Text wrapping in cards/containers
- [ ] Test at 1920x1080 and 1366x768

## Related

- Archival signal: #24 (dashboard caching), #20 (QET baseline)
- Retention window setting: #22
- Context: Kuldeep's Jul 2026 archival history notes (MFM: 411K orders, itemmeta at 12.25 GB, last archival Apr 2024)

Contributor guide

No contributing guide indexed for this repository

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.