MemberJunction / MemberJunction/MJ
Replace SQL JSON_VALUE with in-memory filtering for agent notes scoping
- Dominant language
- TSQL
- Stars
- 29
- Forks
- 6
- Avg merge
- 2d 1h
- Merged PRs (30d)
- 323
Description
## Context
PR #1881 reviewer flagged that `JSON_VALUE(SecondaryScopes, '$.dimension')` in SQL WHERE clauses is a performance concern at scale and requested we investigate in-memory filtering instead. All agent notes are already cached in `AIEngine.Instance.AgentNotes`, and examples already use the in-memory pattern successfully. This change aligns the notes query path with the examples pattern and eliminates ~170 lines of SQL builder code.
**Current state:** Two code paths exist for note retrieval:
- **Semantic search** (strategy='Relevant') — already uses in-memory filtering via callback. No change needed.
- **Direct query** (strategy='Recent'/'All') — uses `queryNotesWithScoping()` → SQL `JSON_VALUE` via RunView. **This is what needs fixing.**
## Changes
### 1. Rewrite `queryNotesWithScoping()` — in-memory pattern
**File:** `packages/AI/Agents/src/agent-context-injector.ts` (lines 236-257)
Replace the RunView call with the same pattern used by `queryExamplesWithScoping()`:
```
AIEngine.Instance.AgentNotes → filterNotesByScoping() → sortNotes() → slice(maxNotes)
```
### 2. Add `filterNotesByScoping()` method
**File:** same, insert near `filterExamplesByScoping()` (~line 458)
Filters the cached notes array by:
- `Status === 'Active'`
- 8-level MJ-internal scoping (AgentID/UserID/CompanyID combinations)
- Secondary scope matching via existing `matchesSecondaryScope()` (no duplication)
### 3. Add `matchesNoteScoping()` helper
**File:** same
Translates the 8-level SQL OR conditions into TypeScript boolean logic. Notes have 8 priority levels (vs 4 for examples) because notes allow `AgentID` to be null:
1. AgentID + UserID + CompanyID
2. AgentID + UserID
3. AgentID + CompanyID
4. AgentID only
5. UserID + CompanyID (no agent)
6. UserID only
7. CompanyID only
8. Global (all null)
### 4. Delete SQL builder methods
**File:** same
Remove these three methods entirely (only called from old `queryNotesWithScoping`):
- `buildNotesScopingFilter()` (lines 281-336)
- `buildSecondaryScopeFilter()` (lines 346-400)
- `buildPerDimensionFilter()` (lines 409-451)
### 5. Clean up imports
**File:** same, line 1
Remove `RunView` from the `@memberjunction/core` import (no longer used anywhere in file).
## Existing code to reuse (no changes needed)
- `matchesSecondaryScope()` (lines 501-554) — core secondary scope matching
- `matchSecondaryScopes()` (lines 563-617) — per-dimension inheritance
- `userDimensionMatches()` — dimension value comparison helper
- `buildScopePreFilter()` (lines 223-230) — still used by semantic search path
- `sortNotes()` — sorting logic
Contributor guide
Assessment
This issue has not been assessed yet.