BOM Update & Cost Rollover Batch Tooling

Two unrelated bulk operations sharing one entry point: repointing every reference to a superseded Bill of Materials, and rolling recomputed cost upward through the entire BOM population one dependency level at a time

1. Requirements

1.1 Functional requirements

• Let an operator supersede one Bill of Materials with another everywhere it is used as a sub-assembly, without hand-editing every parent BOM that referenced the old one.
• Recompute cost across every BOM in the system in dependency order — every leaf BOM first, then only the BOMs whose every sub-assembly has already been recomputed, one level up, repeating until nothing higher remains — since a single BOM’s own save-time recalculation (documented in the costing-engine design) never walks back up to its ancestors on its own.
• Run both operations as trackable, resumable background jobs, not as something an operator waits on synchronously.
• Trigger the cost-rollover sweep automatically on a schedule, if configured, without operator action.

1.2 Non-functional requirements

• Resumable across restarts: a cost-rollover run spanning many levels and many thousands of BOMs must be able to pick up at its current level after an interruption, not restart from the first leaf.
• Bounded batch size: no single background job should process an unbounded number of BOMs; work at one level is sliced into fixed-size batches that can run in parallel.
• No silent duplicate runs while one is actively progressing: a new Update Cost run is refused, regardless of which of the two paths in §3.2 tries to start it, as long as the active run was touched within the last day. This genuinely holds for an active run — it stops holding the moment a run goes quiet for more than a day without failing outright (see §3.2).

1.3 Constraints

• This tool does not implement a third costing algorithm — it drives the exact same per-BOM cost calculation the costing-engine design already defines, just called repeatedly, level by level, across the whole BOM population rather than once per manual save.

2. High-Level Design

2.1 Component diagram

3. Deep Dive

3.1 Data model

BOM Update Tool — not a record with meaningful state of its own; it exists only to validate two inputs (current_bom, new_bom) and hand off to a BOM Update Log. Both of its whitelisted entry points, and the scheduled auto-trigger, do nothing but create and submit a Log.

BOM Update Log — the actual submittable record and audit trail: update_type (Replace BOM or Update Cost), status (Queued → In Progress → Completed/Failed/Cancelled), current_level (which dependency tier an Update Cost run is on), processed_boms (a JSON record of which BOM names have finished at the current level), a table of BOM Update Batch rows, and an error_log link populated on failure. Validation differs by type: a Replace BOM log requires both BOMs set, distinct, and pointed at the same target item; an Update Cost log is refused outright if another Update Cost log is already Queued/In Progress and was modified within the last day. This check runs inside the Log’s own validation, so it applies no matter which of the two paths described in §3.2 created the new log.

BOM Update Batch — one row per (dependency level, slice of up to 7,000 BOMs) during an Update Cost run: boms_updated (the JSON list of BOM names this batch actually processed) and a Pending/Completed status. The whole batch table for a Log is deleted once that Log’s run finishes.

3.2 Algorithm — two unrelated bulk operations

Replace BOM is a targeted substitution, not a system-wide sweep. Every BOM Item row anywhere that currently points at the superseded BOM is repointed to the new one in a single bulk update, with its rate and amount overwritten from the new BOM’s own already-computed unit cost (total_cost / quantity — the same “trust a persisted number on the child” pattern the costing-engine design describes, not a fresh recomputation). The tool then walks upward: every ancestor of the new BOM is found by a genuinely recursive parent-link walk (which also detects and rejects a resulting cycle), and each ancestor has its flattened materials list and cost recalculated and saved, with an explicit version-history entry crediting the tool. This entire operation runs as one background job, bounded by however many ancestors the one affected BOM actually has — it never touches any BOM outside that lineage.

Update Cost (the “Cost Rollover”) is a genuine, system-wide, bottom-up sweep — the missing piece from ordinary BOM saves. It starts from every leaf BOM (active, submitted, with no sub-assembly BOM Item children at all), slices them into batches of up to 7,000, and queues one background job per batch to recompute each BOM’s own cost (committing every 50 BOMs within a batch, to bound transaction size). Once every batch at the current level reports Completed, a separate recurring job — checked every five minutes — computes which BOMs one level up now have every child already processed, and queues the next level’s batches. This repeats until a level produces no further parents, at which point the whole run is marked Completed and its batch rows are deleted. This is exactly the automatic, bounded, cross-level cost propagation that a single BOM’s own save-time recalculation does not do on its own — implemented here as an opt-in or scheduled bulk sweep over the entire BOM population, not as a ripple triggered by any one BOM’s own change.

A stale comment understates the actual batch size. The batching function is commented as slicing “batches of 20k BOMs,” but the code it documents uses a batch size of 7,000 — the comment was not updated when the constant changed.

An Update Cost run can be started two ways, and both eventually pass through the same guard — but one path adds an extra filter first. The whitelisted, operator-triggered entry point creates and submits a new log directly. The scheduled trigger instead pre-checks for an existing Update Cost log that is Queued/In Progress, and skips creating a log at all unless that log’s creation date is more than 10 days in the past — a coarse, scheduled-path-only filter meant to stop the automatic sweep from re-firing on top of itself day after day. Whichever path gets as far as actually creating a log, that log’s own validation then runs the real guard: it is rejected if another Update Cost log is Queued/In Progress and was modified within the last day. The two checks key on different signals and can disagree — a run whose creation is older than 10 days but which is still actively writing progress today clears the scheduled trigger’s own pre-filter, but is still caught by the modified-recency check the moment anything (the scheduled path or an operator) tries to start a second one. The gap is the inverse case: a run that has gone quiet — no progress writes — for more than a day, but has not (yet) transitioned to Failed, no longer blocks anything; an operator can start a second run on top of it immediately, and the scheduled trigger will do the same once that stalled run’s creation also passes 10 days.

3.3 Error handling

• A failure inside either background job rolls back the current transaction, captures a full error log, and marks the Log Failed — a Replace BOM failure stops that one lineage; an Update Cost failure at any level halts the whole multi-level run rather than skipping the failed batch and continuing.
• Recursion in a Replace BOM’s ancestor walk (the new BOM turning out to be its own descendant) is detected and rejected before any BOM is modified.
• Mismatched target items between the current and new BOM in a Replace BOM request are rejected before the log is even queued.
• Concurrent Update Cost runs are guarded, but the guard keys on recent activity, not on whether the run is actually still alive. Either path — operator-triggered or scheduled — is blocked from creating a new Update Cost log while another one is Queued/In Progress and was modified within the last day (see §3.2). The scheduled path additionally declines to even attempt creating a log while an active one’s creation date is within the last 10 days. Neither check catches a run that has simply stopped writing progress without failing outright.

4. Scale and Reliability

• Level-by-level batching is what makes a system-wide recost tractable. Slicing each dependency level into fixed-size batches (rather than one job per level, or one job per BOM) bounds both job size and parallelism, and committing every 50 BOMs within a batch bounds transaction size independently of batch size.
• Progress is resumable by construction, not by a special recovery path. Because current_level and processed_boms are persisted on the Log itself, and the advancing job re-derives “which BOMs are ready for the next level” from batch rows rather than from in-memory state, a worker restart or an interrupted cron cycle loses at most the batches that were mid-flight, not the whole run.
• The recurring five-minute check, not the batch jobs themselves, is what drives multi-level progress — a batch job only ever processes one level’s own slice; nothing about finishing a batch triggers the next level directly.

5. Trade-off Analysis

6. What to Revisit as the System Grows

• Replace the modified-recency check with a genuine liveness signal (e.g., a heartbeat, or explicit stall detection that transitions a quiet run to Failed) — today the guard that actually blocks both entry paths cares only whether the active run was touched within the last day, not whether it is truly still making progress, so a run that has silently stalled stops protecting against a duplicate the moment a day passes without a write.
• Fix the stale batch-size comment so it matches the actual constant, since a future change to the real batch size is likely to leave the comment wrong again otherwise.
• Consider whether a partial failure at one level of a Cost Rollover run should skip that batch and continue, rather than halting the entire multi-level sweep, once the BOM population is large enough that restarting a multi-day run from scratch after one bad BOM becomes costly.