task-0230 Plan — Bounded Nightly Summary Caching

Outcome

Make summaries:run_nightly_summaries complete predictably without changing any KPI formula or displayed summary value. Nightly work will be an explicit, database-backed, deduplicated list rather than “render every summary for every company code.”

Review the old/new workflow diagram

The supporting measurements are in the investigation artifact.

What Is Broken Today

The current task builds every Summary once with no ccode and again for every ccode. In standard POS, that starts 1,593 complete summary builds. Nested summaries and entity/location/position/employee sections multiply the work further, and ignore_cache: true makes every repeated KPI execute again.

This means the process behaves like a hang even when no individual reporting query is stuck. The existing advisory lock prevents overlap, but it leaves one effectively permanent run holding the lock while every later night skips.

Implementation Plan

1. Let administrators choose what is warmed nightly

• Add a nightly_cache_enabled boolean to summaries using a Rails-generated migration.
• Default it to false and do not silently enable existing records.
• Add an Include in nightly cache switch to the existing Summary Settings dialog.
• Permit and return the field through the existing Summaries controller/API.
• Explain that this affects background warming only; users can still open any summary and calculate it on demand.

This makes the selection authoritative and database-backed. The code will not contain a hard-coded summary list.

2. Plan cache work without rendering whole summaries

Introduce a dedicated planner responsible for turning enabled summary definitions into cache requests.

• Traverse enabled root summaries and nested summaries with cycle detection.
• Expand only contexts declared by the summary/section definition. Remove the outer Ccode.all × Summary.all loop.
• Produce a normalized work key containing summary item, effective arguments, weight, year/cache period, and any other field that changes the cache row.
• Deduplicate identical keys before executing KPI work.
• Do not calculate section/overall scores, build presentation JSON, or load historical trend rows during cache warming.
• Provide a dry-run mode that reports enabled roots, context counts, unique work count, duplicates removed, and definition errors without executing KPI queries or writing cache rows.

3. Execute the bounded worklist safely

• Keep execution sequential so the task cannot create a database traffic spike.
• Preserve the existing PostgreSQL advisory lock and always release it through ensure.
• Apply configured statement timeouts to every database connection used during the run, including primary and SBM metadata lookups as well as Bilbo reporting SQL.
• Add explicit HTTP open/read timeouts for API-backed KPI items.
• Add a configurable whole-run deadline. On expiry, stop cleanly, release the lock, and exit nonzero instead of remaining alive indefinitely.
• Log one concise start/progress/completion summary with counts and durations. Do not log SQL text, API keys, customer data, or employee data.
• Record individual failures and finish the bounded worklist where safe, but report a failed task result when any planned item did not refresh.

4. Update operating guidance

• Document the nightly-cache switch in the Summaries guide.
• Explain enabled versus on-demand summaries, dry-run review, timeout/failure behavior, and how to recognize a skipped run caused by the advisory lock.
• Keep the cron schedule unchanged unless rollout evidence shows a schedule change is actually needed.

Verification Plan

Add focused automated coverage for:

• only enabled summaries enter the planner;
• disabled summaries remain usable on demand;
• nested summaries are traversed without duplicate KPI work;
• repeated ccode/entity/position/employee paths normalize to one work item when their effective arguments are equal;
• genuinely different arguments remain separate;
• a summary cycle is rejected with a useful error;
• dry-run mode executes no KPI SQL and creates/updates no cache rows;
• the executor retains the advisory single-flight behavior;
• database, HTTP, and whole-run timeouts release the lock and produce a failed result;
• partial item failures are visible and do not falsely print “Finished”;
• Summary Settings reads and writes nightly_cache_enabled; and
• one nested employee-context fixture has a bounded SQL-query count, preventing the current N+1 pattern from returning.

Per repository policy, reset the test database and run Rails tests only inside Docker. Run focused Rails and React tests after the implementation batch, followed by the dependency-closure and required parity gates before review.

Controlled Production Rollout

1. Deploy the code with no summaries enabled and keep the nightly cache cron paused.
2. Run the production dry-run mode. Confirm it performs no cache writes and review its root/context/unique-work counts.
3. Use Summary Settings to enable a deliberately small initial set selected by the operator—not by a source-code default.
4. Run one supervised cache execution while observing duration, database activity, failures, memory, and the advisory lock.
5. Compare refreshed cache rows and visible summary values with on-demand calculations for representative contexts.
6. Expand the enabled set only after each supervised run completes comfortably before the next scheduled window.
7. Re-enable cron after the approved set completes predictably and the failure path has been observed to release its lock.

Rollback is simple: disable the nightly flags or pause cron. On-demand summary behavior and existing cache rows remain available.

Review Setup

This adds a setting to the React Summary Builder. Before moving the build to review:

• start the task-owned POS review server on the task branch/worktree for the Summary Builder and build_summaries.tsx using the canonical review-server tool;
• verify Rails, Vite, React refresh, and the authenticated Summary Settings workflow;
• use Playwright to confirm the switch loads, saves, and remains scoped to users who can edit summaries;
• inspect the dry-run output with production read-only data; and
• record URLs, branch, commit, worktree, commands, results, query-count evidence, and changed files in the build artifact.

Approval Boundary

Approving this plan authorizes a local task branch/worktree and implementation through the building -> review gate. It does not authorize a remote push, CI transition, PR, production deployment, cron change, or enabling any production summary.