CCC: Refactor to denormalize scope values and replace SQL LIKE on serialized scope (#3574905) · Issues · project / ai_context

CCC: Refactor to denormalize scope values and replace SQL LIKE on serialized scope

>>> [!note] Migrated issue   Reported by: [kristen pol](https://www.drupal.org/user/8389) Related to !117 >>> [Tracker] Update Summary: [One-line status update for stakeholders] Check-in Date: MM/DD/YYYY Blocked by: [#XXXXXX] (New issues on new lines) Additional Collaborators: @username1, @username2 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>. [/Tracker] <h3 id="summary-problem-motivation">Problem/Motivation</h3> Follow-up to: <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> Summary / goal Introduce indexed, queryable storage for frequently-filtered scope data (e.g., global flag, target entity type, common scope values) and update Views/filters to use joins/columns instead of SQL LIKE against the serialized scope map. This removes full-table scans and makes admin filters/selectors indexable and performant. Why this matters Current views/filter (TargetFilter::query()) uses LIKE on ai_context_item.scope (serialized map) which is unindexable and causes slow scans at scale. Replacing it with denormalized/indexed storage yields predictable query plans and much faster admin queries and selector prefiltering. Acceptance criteria (measurable) <ul> <li>TargetFilter::query() no longer uses LIKE on ai_context_item.scope; it joins or queries the new index table/fields.</li> <li>EXPLAIN ANALYZE of representative Views queries shows index usage and significantly reduced execution time vs baseline.</li> <li>No functional regression: filtering semantics (global / non-global / target entity type) remain equivalent.</li> <li>Migration/update hook populates index for existing rows and future writes update index automatically.</li> <li>Kernel test asserts query path uses the new table and unit test validates behavior parity.</li> </ul> Files to inspect/change <ul> <li>src/Plugin/views/filter/TargetFilter.php — replace LIKE logic with indexed join/condition</li> <li>src/Entity/AiContextItem.php — consider lightweight hooks to update index on save/delete</li> <li>ai_context.install / src/updates/ — add update hook(s) to create index table/columns and backfill</li> <li>ai_context.services.yml or a new service — index maintenance (if using a dedicated service)</li> <li>config/install/views.view.ai_context_items.yml — update views if they reference scope field/table</li> <li>tests/ — new kernel tests for filter correctness and performance assertions (query plan verification)</li> </ul> <h3 id="summary-proposed-resolution">Proposed resolution</h3> Recommended implementation approaches (options) Option A — Dedicated index table (recommended) <ul> <li>Add new table ai_context_item_scope_index with columns: item_id INT, scope_id VARCHAR, value VARCHAR, plus indexes: <ul> <li>PRIMARY (item_id, scope_id, value) or separate index on (scope_id, value) for lookups</li> <li>INDEX on item_id for joins</li> </ul> </li> <li>On entity save/delete (via entity hook/subscriber or dedicated service), upsert index rows for the subset of scopes we need to query (global, target_entity, key scopes).</li> <li>Update TargetFilter and any selector prefilters to JOIN/WHERE against ai_context_item_scope_index instead of LIKE.</li> <li>Add update hook to create table and backfill from existing ai_context_item.scope map.</li> </ul> Pros: very fast, DB-agnostic, clear indexes. Cons: small write overhead to maintain index. Option B — Add dedicated base fields (boolean/string) for high-value keys <ul> <li>Add explicit fields to ai_context_item entity for is_global (boolean) and target_entity_type (string or DER-based index), with DB indexes.</li> <li>Update forms/services to set these fields when scope changes.</li> </ul> Pros: simpler queries, works well for a few keys. Cons: limited flexibility for many scope types. Option C — JSONB + GIN index (Postgres-specific) <ul> <li>Store scope in JSONB and add GIN indexes for existence/containment queries.</li> </ul> Pros: compact and flexible. Cons: Postgres-only and Views/SQL syntax changes may still be awkward. Subtasks (suggested) <ol> <li>Design & decision (choose Option A/B/C)</li> <li>Add DB schema (update hook) to create index table/fields + indexes</li> <li>Backfill migration script (update hook) to populate index from existing scope maps</li> <li>Implement index maintenance service or entity hooks to keep index updated on save/delete</li> <li>Update TargetFilter::query() to use new index (and update Views definitions if needed)</li> <li>Add kernel tests verifying functional parity and asserting index-based query path; add EXPLAIN check or perf smoke test</li> <li>Deploy: run update hook, verify backfill, run queries and monitor; rollback plan documented</li> </ol> Testing strategy <ul> <li>Kernel tests that create items with various scope maps and verify TargetFilter returns same results when using index vs baseline.</li> <li>Performance smoke test comparing EXPLAIN ANALYZE before/after on a >10k item fixture (can be local load test).</li> <li>Integration test ensuring index maintenance on entity save/delete.</li> </ul> Risks & mitigations <ul> <li>Risk: index not kept in sync if some code paths mutate scope directly. Mitigate by centralizing scope updates in service or enforcing index update in entity presave/subscriber.</li> <li>Risk: Backfill large table could be long-running. Mitigate with batched backfill and monitor; provide maintenance window instructions.</li> <li>Risk: Views or other code referencing raw scope may need updates. Mitigate by scanning codebase for scope LIKE usage and updating callers.</li> </ul> <h3 id="summary-remaining-tasks">Target date or deadline</h3> April 2026 <h3 id="summary-remaining-tasks">Remaining tasks</h3> <ul> <li>Review AI findings</li> <li>Decide if they are correct</li> <li>Decide if the proposed approach is best or propose alternative</li> <li>Update code</li> <li>Review and test</li> </ul> <h3 id="summary-ai-usage">AI usage (if applicable)</h3> [x] AI Assisted Issue This issue was generated with AI assistance, but was reviewed and refined by the creator. [ ] AI Assisted Code This code was mainly generated by a human, with AI autocompleting or parts AI generated, but under full human supervision. [ ] AI Generated Code This code was mainly generated by an AI with human guidance, and reviewed, tested, and refined by a human. [ ] Vibe Coded This code was generated by an AI and has only been functionally tested. > Related issue: [Issue #3573713](https://www.drupal.org/node/3573713)

issue