Data Purge and Safe Transaction Deletion Tool

A design reference for bulk-deleting one legal entity's transactional history without hand-auditing every referencing record type

1. Requirements

1.1 Functional requirements

• Given a single Legal Entity, produce a reviewable list of every record type that could hold data scoped to it, before anything is deleted — discovered mechanically from field metadata (a link to the Legal Entity record type), not a hand-maintained registry, so record types added by any module are picked up automatically.
• Distinguish record types genuinely scoped to one entity (they carry a link field) from record types with no such link, where “delete” clears the whole table, not just this entity’s rows.
• Let the operator edit the discovered list before committing: remove candidates, add one manually with a chosen link field, or round-trip the list through an export/import file.
• Refuse a small, non-negotiable list of core platform record types regardless of choice (permission/workflow infrastructure, file/version/error history, the entity record itself, the tool’s own record). Separately, maintain an editable default exclusion list — general ledger, cost center, warehouse, employee, tax template, point-of-sale profile, bill-of-materials, bank account, customer, supplier, department — extendable by other modules with no import back into this tool.
• Run the deletion unattended as a chain of gated stages, deleting each in-scope record type in fixed-size batches, persisting enough progress that a failed run can resume instead of restarting.
• Beyond the primary record, remove its comments, communications, version history, and child-table rows, and detach (not delete) shared file attachments.
• Repair side effects a pure reference scan would miss: cached per-item stock quantities, the entity’s cached sales totals, and numbering-series counters left ahead of the highest surviving record.
• Guarantee at most one run is active system-wide, and block other users from saving records of a record type mid-purge.

1.2 Non-functional requirements

• Resumability: a failed run must be retriable without redoing record types already finished.
• Auditability: per record type, the operator sees how many records existed, how many were processed, and whether the stage completed, failed, or was skipped.
• Safe-by-default discovery: an unrecognized record type lands in the reviewable list or the exclusion list, never straight to “delete.”
• Privileged-only: restricted to an administrative role given the irreversible blast radius.

1.3 Constraints

• Scoped to exactly one Legal Entity per run; no separate purge service — it runs inside the same system as the data it deletes.
• A record type with no link to Legal Entity cannot be partially purged — selecting it clears the entire table.

2. High-Level Design

2.1 Component diagram

2.2 Discovery and review walkthrough

1. Enumerate candidates — every record type with at least one link field to Legal Entity, regardless of which module defines it.
2. Drop non-candidates — the protected core list, the effective ignore list (defaults plus module-registered plus operator-excluded), child tables (deleted automatically with their parent), and record types with no backing table.
3. Expand multi-link record types — one candidate row per link field, since a record type can be scoped by more than one relationship to the entity.
4. Annotate each candidate with a live count matching this run’s entity, and any nested child tables.
5. Review — remove, add manually, or export/edit/re-import, recalculating counts on return.
6. Freeze — on submission, the reviewed list is copied into the run’s progress table, each row starting at zero processed and not done.

3. Deep Dive

3.1 Run lifecycle

One overall status sits alongside a separate Pending/Completed/Skipped flag per stage (cache cleanup, address cleanup, entity-total reset, notification clearing, progress-table initialization, batched deletion). Re-entering the chain is a no-op for any stage already off “Pending,” so a retry resumes at whichever stage actually failed. Before a run starts, the system checks — system-wide, not per entity — that no other run is already queued or running.

3.2 Batching, ordering, and resumability

For each not-yet-done record type in the run’s progress table, in the order the candidates were frozen:

count = records matching this run's entity through the record type's link field
if count == 0:
    repair the record type's numbering series counter
    mark this record type done
else:
    fetch up to BATCH_SIZE records (fixed at 5,000)
    delete, in order: version history, communications, comments,
        detach file attachments, child-table rows, the records themselves
    add the batch size to this record type's processed count
resubmit this stage if any record type is still not done

The remaining-count is re-queried from the database on every pass rather than trusted from an in-memory queue, so a crash between batches loses no bookkeeping — the next attempt just asks again and continues from where the data now stands. A record type reaching zero also has its numbering-series counter rolled back to the highest surviving record matching that series’ pattern, or to zero if none survive.

3.3 Non-reference fallout, and what is not repaired

Three side effects outside plain reference-following are handled, gated on scope: cached stock balances for the entity’s warehouses, the entity’s own cached sales totals, and the numbering-series repair above.

Not handled: hierarchical/tree-shaped master data. No stage touches parent/child hierarchy fields; instead, the default ignore list keeps every tree-shaped record type observed here (ledger accounts, cost centers, warehouses, departments) out of the candidate list by convention — a default, not an enforced rule. An operator can still move one into the candidate list, and no repair step exists afterward.

3.4 Concurrency and failure handling

A per-record-type marker is set for the run’s duration; saving a document of a record type mid-purge is rejected with a pointer to the run — except ledger-entry record types and the protected core list, which are exempt. If a stage raises, its transaction rolls back, the traceback lands in the run’s error-log field, status flips to Failed, and the markers clear so nothing stays locked. Retry re-enters the chain per §3.1.

4. Scale and Reliability

• Fixed, not adaptive, batch size. Five thousand records per batch bounds runtime and lock duration; huge record counts still finish, across many resubmissions of the same stage.
• One run at a time, globally, not per entity — the simplest guarantee the concurrency guard and numbering-series repair never race, at the cost of throughput for multi-entity deployments.
• Progress is the recovery mechanism. No separate checkpoint log exists; the run’s own progress table is both audit trail and resume point.
• Discovery cost is paid once, at candidate-generation time, not inside the batching loop.

5. Trade-off Analysis

6. What to Revisit as the System Grows

• Per-entity concurrency, once purging becomes routine across many entities — the single global slot is the first bottleneck.
• A real repair step, or a hard block, for hierarchical master data — today’s only safeguard is that nobody removed those record types from the default ignore list.
• Finer-grained retry — the chain re-enters at the first stage and relies on every earlier flag to short-circuit correctly; recording which stage failed would simplify this as stages grow.
• A discovery-side extension point to match the exclusion-side one — a module can say “exclude me by default” but not “purge me, only after my own cleanup.”