Skip to content

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_reviews even 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 the PUR- 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:

  • total is unchanged (voided bills are retained); active fell by exactly the number voided; voided rose 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

  1. Run production-backup; note the set name (full-YYYYMMDD-HHMMSS). Off-site mirroring is NOT configured, so the set exists only on the VPS.
  2. 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 and PLAN-DIGEST <32 hex>. Any BLOCKED: line means stop.
  3. The owner approves that digest.
  4. 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_log row with event_type = 'PRODUCT_STOCK_ADJUSTED' and metadata->>'cleanup_run' = 'purchase-test-bills-2026-11', the admin stock adjustment with the opposite delta restores metadata->>'before'.
  • Bills: for each accounting.audit_log row with note test-bill-cleanup:% and after_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.