>>> [!note] Migrated issue <!-- Drupal.org comment --> <!-- Migrated from issue #3574907. --> Reported by: [kristen pol](https://www.drupal.org/user/8389) Related to !119 >>> <p>[Tracker]<br> <strong>Update Summary: </strong>[One-line status update for stakeholders]<br> <strong>Check-in Date: </strong>MM/DD/YYYY<br> <strong>Blocked by: </strong>[#XXXXXX] (New issues on new lines)<br> <strong>Additional Collaborators: </strong> @username1, @username2<br> <em>Metadata is used by the <a href="https://www.drupalstarforge.ai/" title="AI Tracker">AI Tracker.</a> Docs and additional fields <a href="https://www.drupalstarforge.ai/ai-dashboard/docs" title="AI Issue Tracker Documentation">here</a>.</em><br> [/Tracker]</p> <p>Follow-up to:</p> <p><span class="drupalorg-gitlab-issue-link project-issue-status-info project-issue-status-7"><a href="https://www.drupal.org/project/ai_context/issues/3573713" title="Status: Closed (fixed)">#3573713: Full architecture review of CCC in prep for 1.0</a></span></p> ## Problem/Motivation `ai_context_usage` records can grow quickly on active sites. Current pruning and clearing operations can load large numbers of usage entities into memory before deleting them, which risks slow cron runs, high memory use, and large delete operations. Common usage lookups also need targeted database indexes so the existing usage tracker remains responsive while the module continues toward 1.0. A broader normalization of `tools_used` and `routes` into separate indexed tables was considered, but that work is intentionally deferred. The usage/observability architecture is expected to move toward `ai_observability`, so this issue should focus on small, safe improvements to the current `ai_context_usage` implementation rather than building a replacement storage model. ## Summary / Goal Make existing `ai_context_usage` pruning and clearing safer at scale by: - adding targeted database indexes for common lookups and pruning; - converting cron pruning to bounded delete batches; - converting the usage clear form to Drupal Batch API; - preserving the current usage data model and behavior. ## Why This Matters Usage records are written frequently and may accumulate over time. Without batching, cron pruning and manual clearing can load too many entities into PHP memory and create large delete operations. These changes reduce operational risk for current sites without investing in a larger normalization architecture that may be replaced by `ai_observability`. ## Proposed Resolution Add targeted indexes to the `ai_context_usage` table for common lookup and pruning paths: - `runner_id` - `changed` - `(target_entity_type, target_entity_id)` - rely on Drupal’s generated entity-reference field index for `context_item_id` Add the indexes for both: - existing installs via update hook; - new installs during module install. Refactor `ai_context_cron()` so age-based and max-record pruning delete usage records in bounded batches instead of loading all matching entities at once. Refactor the usage clear confirmation form to use Drupal’s Batch API so clearing large usage tables is chunked across batch iterations. Add kernel test coverage for pruning behavior, batch clearing, and index creation. ## Out Of Scope Normalization and broader observability improvements should be handled as part of the planned `ai_observability` integration. - Do not normalize `tools_used` or `routes` into separate indexed tables in this issue. - Do not introduce new usage storage tables, backfill jobs, or Views integrations for normalized usage metadata. - Do not change the public structure or meaning of existing recorded usage data. ## Files To Change - `ai_context.install` - `ai_context.module` - `src/Form/AiContextUsageClearForm.php` - `tests/src/Kernel/` ## Acceptance Criteria - Existing installs receive the new `ai_context_usage` indexes through an update hook. - New installs create the same usage indexes during module install. - Common lookups by `runner_id`, target entity, and `changed` have indexes available. - Cron age pruning deletes records in bounded batches. - Cron max-record pruning deletes oldest excess records in bounded batches. - Cron behavior remains unchanged from a user/data perspective: - records older than `usage_max_age` are removed; - oldest records beyond `usage_max_records` are removed; - pruning is skipped when both limits are disabled. - The usage clear form deletes records through Drupal Batch API instead of one large entity load/delete operation. - Kernel tests cover: - age-based pruning; - max-record pruning; - combined age and max-record pruning; - disabled pruning; - clear-form batch deletion; - index existence. - Existing usage data structure and public usage APIs remain unchanged. ## Subtasks - Confirm which existing `ai_context_usage` fields need explicit indexes and which indexes Drupal already generates. - Add install/update hook logic for missing usage indexes. - Refactor cron pruning into bounded delete loops. - Refactor usage clear form to use Batch API. - Add kernel tests for pruning and clear-form batch behavior. - Add kernel test coverage that confirms expected usage indexes exist. - Run lint/static analysis and focused kernel tests. ## Testing Strategy - Run module lint/static analysis. - Run focused kernel tests for usage pruning and clear-form batch deletion. - Verify update hook creates the expected indexes on an existing install. - Verify a fresh install has the same indexes. - Smoke test usage tracking read/write flows to confirm recorded data is unchanged. - Optionally verify representative queries on a staging dataset with `EXPLAIN` if a large dataset is available. ## Risks & Mitigations - **Risk:** Adding indexes can be expensive on large production tables. - **Mitigation:** Keep indexes targeted and limited to high-value lookup/pruning fields. Sites with very large tables can run updates during a maintenance window. - **Risk:** Batch size may be too small or too large for some environments. - **Mitigation:** Use a conservative fixed batch size for safety. Tune later if real-world data shows a need. - **Risk:** Max-record pruning still requires counting total usage records. - **Mitigation:** This issue focuses on eliminating large entity loads and large delete operations. More advanced retention architecture can be handled by `ai_observability`. - **Risk:** Normalizing `tools_used` and `routes` now could duplicate or conflict with upcoming observability work. - **Mitigation:** Defer normalization and broader reporting changes to the planned `ai_observability` integration. <h3 id="summary-ai-usage">AI usage (if applicable)</h3> <p>[x] AI Assisted Issue<br> This issue was generated with AI assistance, but was reviewed and refined by the creator.</p> <p>[ ] AI Assisted Code<br> This code was mainly generated by a human, with AI autocompleting or parts AI generated, but under full human supervision.</p> <p>[x] AI Generated Code<br> This code was mainly generated by an AI with human guidance, and reviewed, tested, and refined by a human.</p> <p>[ ] Vibe Coded<br> This code was generated by an AI and has only been functionally tested.</p> > Related issue: [Issue #3573713](https://www.drupal.org/node/3573713)