task-0230 Investigation — Nightly Summary Cache Does Not Complete

Finding

The nightly standard-POS task is dominated by combinatorial traversal and N+1 metadata queries. A single long Bilbo reporting query is not required to reproduce the failure.

Production Evidence

• Standard POS contains 59 summaries and 26 ccodes. The current two loops start 1,593 top-level summary builds before nested context expansion.
• Familystores contains six summaries and two ccodes. Its equivalent 18 builds completed in approximately 67–75 seconds on recent nights.
• Standard started a run on August 10 and never printed its completion marker. August 11–13 correctly skipped because the August 10 session still held the advisory lock.
• The July single-flight and Bilbo statement-timeout change prevented new overlap but did not bound the work inside the first run.

Controlled Reproduction

A production runner replaced KPI execution with a process-local function returning NA. It performed no KPI/API work and created no cache rows.

Even with KPI execution removed, one blank-context Branch Effectiveness Summary build exceeded a 45-second timeout. Before cancellation it had:

• attempted 2,218 KPI calls;
• issued 5,695 primary-database metadata statements;
• issued 863 SBM statements; and
• issued 6,558 metadata statements in total.

The largest repeated query groups were:

• 1,080 individual SummaryItem loads;
• 692 child-section loads;
• 692 summary-item-link loads;
• 692 nested-summary-link loads;
• 314 individual employee loads; and
• 183 employee-by-position/model lookups.

CEO, COO, CEO of Food Service, Branch Effectiveness, Floor Manager, and Warehouse Manager roots all exceeded five seconds in the same KPI-disabled probe. The summary graph contains nested summaries but no direct cycle in current production data.

Why Existing Safeguards Are Insufficient

• The advisory lock limits concurrency; it does not limit one run's size or duration.
• The 120-second statement timeout is set only on the Bilbo reporting connection. Primary and SBM metadata queries are outside that protection.
• API-backed KPI requests have no explicit open/read timeout in the KPI service.
• ignore_cache: true deliberately bypasses cache reads, so repeated equivalent paths execute the KPI and cache update again.
• The method is named cache_all_active_summaries, but the Summary table has no active field and the method uses Summary.all.

Conclusion

The observed “hang” is an effectively unbounded batch. Long individual KPI queries may add cost, but optimizing one query cannot make the current traversal reliable. The fix must first bound and deduplicate the worklist, remove presentation-only rendering from cache warming, and extend timeout/observability coverage.

No production data, code, cron configuration, or task setting was changed during this investigation.