Clearing test purchase orders (owner-approved, void-only)¶
Scope. The portal's Purchase Orders / Purchase Order Entry are accounting.purchase_bills
(PUR-YYYY-NNNNNN). The owner has approved clearing the test entries to start fresh. This
runbook clears only positively identified test bills that have no dependencies. It never touches
genuine purchases, suppliers, products, inventory, payments, invoices, GST records or history.
Why void, not delete¶
- There is no hard-delete path for purchase bills, by design (statutory records;
purchase_bill_reviewseven avoids a foreign key so bills are "voided, never deleted"). - The application's own mechanism is Void (
POST /purchases/:id/void, Accounts Admin only, reason required). It keeps the row, writes a High-severity audit entry (accounting.audit_log, append-only), leaves thePUR-number series untouched, and removes the bill from the default list and from the supplier's totals. - Void does not reverse stock, cost or journals. So a bill that changed inventory or posted a journal is never a candidate here; it needs a designed reversal and a separate owner decision.
No SQL DELETE/TRUNCATE, no sequence reset, no supplier or product change.
1. Back up first¶
Dispatch the existing production-backup workflow (GitHub Actions → production-backup) and note the
run id and the timestamped set it writes under /opt/aim-store/backups. Confirm the off-site mirror step
reported success (or an explicit "unconfigured" warning, which you must accept knowingly).
2. Inspect (read-only)¶
Dispatch production-purchase-bills-report (GitHub Actions). It runs
aim-backend/scripts/ops/purchase_bill_test_data_classify.sql in a read-only database session and prints:
SUMMARY total=… active=… voided=… classA_candidates=… classB_keep=… total_paise_all=… total_paise_classA=…
BILL PUR-2026-000001 | id=… | class=A|B | created=… | by=… | supplier=… | inv=… | po=… | status=…/… | total_paise=… paid_paise=…
| lines=… stock_lines=… journals=… pay_allocs=… notes=… docs=… reviews=… audit=… | reasons=…
- Class A (candidate): no inventory-linked line, journal, payment, credit/debit note, attachment, review, edit, or ITC in a filed GSTR-3B period.
- Class B (keep): at least one dependency (all reasons are listed) or already voided.
A class-A line is not proof that a bill is test data. Decide from the creation time, creator, supplier invoice number, PO number and amount.
3. Owner confirms the list¶
The owner replies with the exact PUR-… numbers that are test records. Only numbers that are both
confirmed by the owner and class A are voided. Anything in class B is reported with its reasons
and left alone until a separate approval.
4. Void (Accounts Admin, in the portal)¶
For each confirmed number: Purchases → open the bill → Void, reason
Owner-approved test-data cleanup <date>. The void is idempotent (a second attempt returns
ALREADY_VOIDED) and the audit entry carries the reason, the actor and the request id.
5. Verify¶
Re-run production-purchase-bills-report and compare with step 2:
totalis unchanged (voided bills are retained);activefell by exactly the number voided;voidedrose by the same number.- Every voided number now shows
reasons=already voided; every class-B bill is unchanged. - The portal list (default, without "include voided") no longer shows the voided bills.
- Suppliers, product stock/cost and journal totals are unchanged because void touches none of them (compare the accounting journal-integrity health check before and after).
Things to know¶
- A voided bill keeps its supplier invoice number reserved (
UNIQUE (supplier_id, supplier_invoice_number)applies to every status), so re-entering the same supplier invoice number for the same supplier is refused. Use a different number, or raise it as a separate decision. - There is no un-void action. Recovery is a restore from the step-1 backup, or a reviewed repair.
- The
PUR-numbering continues from where it was; it is not reset.
One-time cleanup of the seven confirmed test bills (2026-11)¶
Owner decisions (2026-11-09): the recorded payments on the seven bills were bookkeeping only (no real money moved); no input tax credit from them was claimed in GSTR-3B for August or September 2026; the post-reversal physical stock was counted and verified; current product unit costs are KEPT (they are not reset to NULL).
Mechanism: .github/workflows/production-purchase-test-bills-cleanup.yml running
aim-backend/scripts/ops/purchase_test_bills_cleanup.sql. No backend release, no migration. It supersedes the earlier
plan report option (that plan reset costs to NULL).
Procedure¶
- Run production-backup; note the set name (
full-YYYYMMDD-HHMMSS). Off-site mirroring is NOT configured, so the set exists only on the VPS. - Dispatch production-purchase-test-bills-cleanup with
mode = dry-run(the default). It runs in a read-only session and prints every check, the exact rows, the before/after reconciliation andPLAN-DIGEST <32 hex>. AnyBLOCKED:line means stop. - The owner approves that digest.
- Dispatch again with
mode = apply,expected_digest = <digest>,backup_set = <set from step 1, under 6 hours old>,confirm = APPLY-PURCHASE-TEST-BILLS-CLEANUP. One transaction; it applies only if the digest recomputed from the live rows equals the approved one, then re-checks every post-condition. Re-running is a no-op (ALREADY_APPLIED).
What it does¶
- Voids the five still-active bills (027, 034, 035, 036, 037) using the application's void semantics (row kept, reason and time recorded). 038 and 039 are already voided and are not voided again.
- Subtracts, per bill, exactly what the bill added to stock (stock only; unit cost is never written):
| Bill | Stock movement |
|---|---|
| 027 | none (non-stock Watch line) |
| 034 | T-shirt −80 |
| 035 | Earbuds −50, Smartwatch −20 |
| 036 | BT Sports −1 |
| 037 | BT Sports −1 |
| 038, 039 | none (Hoodie is size-managed: its parent stock is derived from the sizes and was re-based to the counted 20 on 2026-11-04) |
Total 152 units: T-shirt 178 → 98, BT Sports 102 → 100, Earbuds 100 → 50, Smartwatch 70 → 50, Hoodie 20 (unchanged).
* Writes one accounting.audit_log row per bill (marker note test-bill-cleanup:) and one public.audit_log
PRODUCT_STOCK_ADJUSTED row per adjusted product (the same shape as the admin stock adjustment), each with before and after.
* Never deletes, never touches suppliers, other products, customer orders, the inline payment fields, journals or any sequence;
supplier invoice numbers stay reserved. Supplier payable (active bills) ends at 0 without altering any payment field.
Product costs that remain TEST-DERIVED (verify before genuine costing)¶
The unit cost of all five linked products was written only by these test bills (no administrator ever set a cost:
cost_updated_at and cost_updated_by are NULL, and no other bill ever received these products). They are kept as the owner
decided, but they are not verified purchase costs:
| Product | Unit cost kept (paise) | Written by |
|---|---|---|
| Men's Top (T-shirt) | 16200 | 034 |
| BT Sports | 10000 | 037 (latest; 036 was 1000) |
| Circle (Ai-ENC) TWS Earbuds | 16864 | 035 |
| Fitpro Smartwatch | 59236 | 035 |
| Men's Top (Hoodie) | 20000 | 039 (038 was 15000) |
Verify or replace each before it feeds COGS, inventory valuation or any financial report. The next genuine purchase receipt
overwrites a product's unit cost with that receipt's cost. Voiding a bill, the stock adjustment and the only trigger on
shop_items (size-stock derivation, size-managed products only) do not write the cost; the script asserts cost and its
attribution are unchanged before it commits.
Recovery (compensating, from the audit rows alone)¶
Nothing is deleted, so recovery needs no restore. Every number needed is in the audit rows this run wrote:
- Stock: for each
public.audit_logrow withevent_type = 'PRODUCT_STOCK_ADJUSTED'andmetadata->>'cleanup_run' = 'purchase-test-bills-2026-11', the admin stock adjustment with the oppositedeltarestoresmetadata->>'before'. - Bills: for each
accounting.audit_logrow with notetest-bill-cleanup:%andafter_state->>'voidedByThisRun' = 'true', the bill was voided by this run; the application has no un-void, so reversing it is a reviewed single-purpose change (voided = FALSE, void_reason = NULL, voided_at = NULL) that needs its own approval. - The tests prove both from the audit rows alone. The pre-run backup set is the last resort.