[jira] [Created] (FINERACT-2672) Standing Instruction batch loads every active instruction into memory and filters due-ness row-by-row in Java — add a persisted next-run date, an SQL "due today" filter, and chunked processing

["Farooq Ayoade (Jira)" <ji...@apache.org>]

Farooq Ayoade created FINERACT-2672:
---------------------------------------

             Summary: Standing Instruction batch loads every active instruction into memory and filters due-ness row-by-row in Java — add a persisted next-run date, an SQL "due today" filter, and chunked processing
                 Key: FINERACT-2672
                 URL: https://issues.apache.org/jira/browse/FINERACT-2672
             Project: Apache Fineract
          Issue Type: Improvement
          Components: System
            Reporter: Farooq Ayoade


h3. Current behaviour

The scheduled job selects work with {{{}standingInstructionReadPlatformService.retrieveAll(ACTIVE){}}}, whose query filters only on {{{}status = ACTIVE{}}}, the {{{}valid_from{}}}/{{{}valid_till{}}} window, and {{{}last_run_date <> businessDate{}}}. Recurrence *due-ness is not expressed in SQL* — the entire active set is materialised into a single in-memory {{{}Collection{}}}, and {{ExecuteStandingInstructionsTasklet}} then calls {{isDateFallsInSchedule(...)}} *per row in Java* to decide whether each one is due. There is {*}no pagination, no batch limit, no chunk cursor{*}. Per due instruction there is also an N+1 shape (an extra {{retriveLoanDuesData(...)}} for loan targets, plus a per-SI history insert and a {{last_run_date}} update).

So cost is *O(all active SIs in the validity window)* loaded in memory, regardless of how few are actually due today. This does not scale cleanly as the active-SI count grows.
h3. Proposed behaviour
 # *Persist a next-run date.* Add a {{next_run_date}} column, computed on create/update and after each successful run from the recurrence rule. The job then selects {{WHERE status = ACTIVE AND next_run_date <= businessDate AND valid_from <= businessDate AND (valid_till IS NULL OR businessDate < valid_till)}} — turning O(all active) into O(due today). This also lays the groundwork for catch-up (a due date in the past is naturally selected).
 # *Chunked / paged processing.* Process due instructions in bounded chunks (Spring Batch chunk or explicit paging) so the working set is bounded regardless of tenant size.
 # *Reduce N+1.* Batch-load loan dues for the due set rather than one query per instruction where practical.

Keep {{priority DESC}} ordering (already honored) within each chunk.


h3. Acceptance
 * With 100k active SIs of which 200 are due today, the job selects ~200 rows (verified via query plan/row count), not 100k, and never materialises the full set in memory.
 * Due-ness results are unchanged vs. the current Java filter for a representative recurrence matrix (regression set).
 * {{next_run_date}} is correctly advanced after a successful run and left in the past after a missed/failed run (enabling catch-up work later).



--
This message was sent by Atlassian Jira
(v8.20.10#820010)