best-practices-arangodb

> STOP. READ THIS ENTIRE SKILL.MD BEFORE CALLING ANY ENDPOINT. # ArangoDB Best Practices (Project Skill) This skill codifies rules for using ArangoDB correctly in this repo. The core principle: **ArangoDB's built-in features MUST be used instead of reimplementing them in Python.** ## The Anti-Pattern This Skill Prevents Python code that duplicates what ArangoDB already does: - Stop word lists in Python (ArangoDB `text_en` already strips them) - Hand-rolled stemming in Python (ArangoDB `text_en` already stems via Snowball) - Regex patterns that classify entity types (ArangoDB collections already know what things are) - Hardcoded domain term lists (ArangoDB collections are the source of truth) - Python-side BM25/TF-IDF scoring (ArangoDB `BM25()` does this server-side) - Python-side cosine similarity loops (ArangoDB `COSINE_SIMILARITY()` does this server-side) See [references/anti-patterns-duplicated-functionality.md](references/anti-patterns-duplicated-functionality.md) for 7 real removed-from-codebase examples. ## When to Apply Use this skill whenever you: - Write AQL queries or ArangoSearch code - Add new collections or ArangoSearch views - Work with entity extraction, intent mapping, or text search - Add domain knowledge (terms, keywords, frameworks) to the system - Implement fuzzy matching, spellcheck, or text classification ## Rules (priority order) ### 1. CRITICAL: No Stop Words in Python — `arango-no-python-stopwords` ArangoDB's `text_en` analyzer already removes English stop words. NEVER maintain a stop word list in Python. ```python # BAD — duplicates text_en analyzer _STOPWORDS = frozenset({"the", "a", "an", "of", "for", "in", ...}) # GOOD — let ArangoDB handle it cursor = db.aql.execute("RETURN TOKENS(@query, 'text_en')", bind_vars={"query": text}) ``` ### 2. CRITICAL: No Hand-Rolled Stemming — `arango-no-python-stemming` ArangoDB's `text_en` uses Snowball stemming. NEVER strip suffixes in Python. ```python # BAD for suffix in ("s", "es", "ing", "ed", "ly"): if word.endswith(suffix): stem = word[:-len(suffix)] # GOOD — ArangoDB stems automatically: "recommendations" matches "recommendation" ``` ### 3. CRITICAL: Domain Knowledge in ArangoDB — `arango-no-hardcoded-domain-lists` Domain terms, keywords, frameworks belong in ArangoDB collections, not Python dicts/frozensets. Collections: `domain_terms`, `taxonomy_vocabulary`, `sparta_controls`. ### 4. CRITICAL: No Regex for Entity Classification — `arango-no-regex-classification` Regex is for TOKENIZATION (finding candidates). Classification (what is it?) comes from ArangoDB. **Exception**: `_extract_control_ids()` in trace.py uses regex to FIND ID-like patterns — that's tokenization, not classification. ### 5. HIGH: Use AQL Functions — `arango-use-aql-functions` | Need | AQL Function | Python Anti-Pattern | |------|-------------|-------------------| | Fuzzy match | `LEVENSHTEIN_DISTANCE(a, b)` | `rapidfuzz` loop over all docs | | Tokens | `TOKENS(@q, 'text_en')` | Python stop words + stemming | | N-gram | `NGRAM_SIMILARITY(a, b, n)` | Hand-rolled comparison | | BM25 | `BM25(doc)` | Python TF-IDF | | Cosine | `COSINE_SIMILARITY(a, b)` | `numpy` dot product on all docs | ### 6. HIGH: Two-Stage BM25 + Cosine Rerank — `arango-two-stage-search` NEVER brute-force scan all embeddings. BM25 top-100 → cosine rerank to top-k. ### 7. HIGH: One AQL Round-Trip — `arango-single-roundtrip` NEVER split one server-side operation into two network round-trips. ### 8. MEDIUM: Batch Operations — `arango-batch-operations` Use bulk AQL for large-scale operations (10K+ documents): ```aql -- BAD: 222K individual UPDATE calls (3.3/s = 18 hours) UPDATE {_key: @key} WITH {field: @val} IN collection -- GOOD: Bulk update (50-100 docs per query) FOR item IN @updates UPDATE {_key: item.key} WITH {field: item.val} IN collection RETURN 1 ``` For batch exact matches, use one AQL with `FOR cid IN @cids` instead of N individual queries. For HTTP endpoints with internal ThreadPoolExecutor, use batch endpoints (e.g., `/create-evidence-case-batch` with `max_workers: 32`). ### 9. MEDIUM: Identity Analyzer for Exact Matches — `arango-identity-for-exact` Use `identity` (not `text_en`) for control IDs, framework names, categories. ### 10. MEDIUM: Database Name is Always "memory" — `arango-db-name-memory` The only database is `memory`. `ARANGO_DB=lessons` anywhere = bug. ### 11. LOW: Cache Vocabulary Lookups — `arango-cache-vocab` Module-level `_cache: T | None = None` + `_get_*()` function. ### 12. CRITICAL: BM25 Score > 0 Is NOT Grounding — `arango-bm25-not-grounding` BM25 ALWAYS returns results for any security-adjacent query. A score > 0 does NOT prove a term exists. ID-like terms must be grounded via exact `control_id` match or fuzzy edit distance. ```python # BAD — BM25 returns results for "X23-MUSTARD" because it matches "spoofing" # GOOD — exact match: FOR c IN sparta_controls FILTER UPPER(c.control_id) == @cid ``` ### 13. HIGH: Pre-Filter Before LEVENSHTEIN_DISTANCE — `arango-prefilter-fuzzy` Full table scan = 268ms. Length ±3 + prefix overlap pre-filter = 4ms. ```aql -- GOOD — pre-filter reduces 9,337 → 61 candidates FOR c IN sparta_controls LET cid = UPPER(c.control_id) FILTER LENGTH(cid) >= LENGTH(@candidate) - 3 AND LENGTH(cid) <= LENGTH(@candidate) + 3 FILTER LEFT(cid, 1) == LEFT(@candidate, 1) OR CONTAINS(cid, LEFT(@candidate, 2)) LET dist = LEVENSHTEIN_DISTANCE(cid, @candidate) FILTER dist <= 2 SORT dist LIMIT 3 RETURN {control_id: c.control_id, distance: dist} ``` ### 14. HIGH: Concurrent Queries — `arango-concurrent-queries` ArangoDB is multi-threaded. Use `ThreadPoolExecutor` for independent queries. ```python with ThreadPoolExecutor(max_workers=3) as pool: f1 = pool.submit(lambda: list(db.aql.execute("..."))) f2 = pool.submit(lambda: list(db.aql.execute("..."))) results1, results2 = f1.result(), f2.result() ``` ### 15. HIGH: Performance Budgets — `arango-performance-budgets` | Operation | Budget | |-----------|--------| | BM25 text search | <100ms | | Hybrid search (BM25 + cosine) | <300ms | | Entity extraction (full pipeline) | <1s | | Exact lookup by indexed field | <10ms | Use `tests/test_memory_performance.py` to validate. ### 16. MEDIUM: No DOCUMENT() in AQL Loops — `arango-no-document-in-loops` ```aql -- BAD: DOCUMENT('lessons', key) in a loop -- GOOD: FOR l IN lessons FILTER l._key == key LIMIT 1 ``` ### 17. CRITICAL: Always Hybrid Search — `arango-always-hybrid-search` Every retrieval query MUST use BM25 + semantic embedding + multi-hop graph traversal. NEVER use a single lane alone. ```python # GOOD — via RecallSources from graph_memory.hybrid_search import hybrid_search_sparta_qra results = hybrid_search_sparta_qra(query, db, embedding_service, k=12) ``` Direct single-lane queries only in unit tests and diagnostics. ### 18. CRITICAL: No Silent Fallback — `arango-no-silent-fallback` When a search lane fails, the code MUST: 1. **Log at `logger.error`** (NOT `logger.debug`) with exception context 2. **Signal degradation** in the result so the caller knows 3. **NEVER** silently return partial results as if complete 4. **NEVER** use bare `except Exception:` — always capture as `except Exception as exc:` `logger.debug` in exception handlers is a silent fallback — invisible in production. This rule applies to ALL AQL queries, view searches, and collection operations. ### 18b. CRITICAL: Every Collection Searchable via /recall — `arango-recall-all-collections` Every document collection with an ArangoSearch view MUST be searchable via `/recall`. Use scope routing to filter (e.g., `scope=sparta` → only `sparta_*` collections). New collections MUST be added to both the ArangoSearch view AND `bm25_rank()`. `/recall` MUST return BM25 + semantic (cosine) + multi-hop graph traversal for ALL collections. ### 18c. CRITICAL: Use @@coll bind variables — `arango-no-collection-interpolation` Collection names in AQL MUST use `@@coll` bind variables, NOT f-string interpolation. ```python # WRONG — AQL injection risk aql = f"FOR d IN {collection} FILTER d._key == @key RETURN d" # RIGHT — bind variable for collection aql = "FOR d IN @@coll FILTER d._key == @key RETURN d" db.aql.execute(aql, bind_vars={"@coll": collection, "key": key}) ``` ### 19. MEDIUM: Cache Errors Must Log — `arango-cache-must-log` DB-backed cache `except` blocks MUST log at `logger.error`. Cross-ref: `/best-practices-python` rule `correctness-no-silent-fallback`. ### 20. HIGH: No Null Filters for Backfills — `arango-no-null-backfill` NEVER use `FILTER doc.field == null` for batch backfill operations on large collections. **Why it fails:** As documents get updated, fewer match `field == null`. Without an index on the field, ArangoDB scans progressively more documents to find fewer matches. Rate degrades from 20/s → 7/s → worse. ```aql -- BAD — progressively slower as nulls decrease (no index helps) FOR doc IN sparta_qra FILTER doc.lineage == null FILTER doc._key > @last_key LIMIT 200 RETURN doc -- GOOD — version-based, indexable, constant performance FOR doc IN sparta_qra FILTER doc.lineage.graph_version < @target_version OR doc.lineage == null FILTER doc._key > @last_key LIMIT 200 RETURN doc ``` **Pattern:** 1. Add a version field (e.g., `lineage.graph_version`) 2. Create persistent index: `db.sparta_qra.ensureIndex({type: "persistent", fields: ["lineage.graph_version"]})` 3. Query by version, not null 4. Bump version constant when schema changes **Real incident (2026-04-13):** 171K QRA lineage backfill started at 20/s, degraded to 7/s by 50% completion. Root cause: unindexed `lineage == null` filter. ### 21. HIGH: Sparse Vector Index UPDATE Bug — `arango-vector-update-bug` ArangoDB versions < 3.12.9 have a bug where sparse vector indexes block UPDATE operations on documents that don't have the vector field, even though `sparse: true` should allow this. **Error:** `[HTTP 400][ERR 10] vector field not present in document <key>` **GitHub Issue:** [arangodb/arangodb#22568](https://github.com/arangodb/arangodb/issues/22568) ```python # BAD — fails on docs without embedding (versions < 3.12.9) db.aql.execute("UPDATE {_key: @key} WITH {lineage: @lin} IN sparta_qra", ...) # WORKAROUND — drop index, update, recreate coll = db.collection('sparta_qra') idx_config = next(i for i in coll.indexes() if i['type'] == 'vector') coll.delete_index(idx_config['id']) # ... do all updates ... coll.add_index({ 'type': 'vector', 'name': idx_config['name'], 'fields': idx_config['fields'], 'params': idx_config['params'], 'sparse': True, 'inBackground': True }) ``` **When to apply:** Any batch update to a collection with a sparse vector index where some docs lack the vector field. **Real incident (2026-04-14):** Lineage backfill failed on 269 docs without embeddings. Error message was misleading ("vector field not present") even when providing the embedding in the UPDATE — the index validates against the OLD document state. ### 22. CRITICAL: Arango NEVER Stores Embeddings — `arango-no-embedding-arrays` **Operator ruling (2026-07-31), supersedes the old `arango-require-embeddings` rule.** ArangoDB must never hold embedding/vector arrays. Qdrant is the only vector store. Rationale: embedding arrays inflate the dataset past the ArangoDB community-edition size cap, at which point the server shuts down and demands a paid license. Arango documents carry pointer metadata only: `qdrant_collection`, `qdrant_point_id`, `embedding_model`, `embedding_version`, `text_hash`, `semantic_sync_state`. The dense lane of hybrid search queries Qdrant via the memory daemon, never `COSINE_SIMILARITY` over Arango-resident arrays. ```python # BAD — writing a vector into Arango (community-edition size bomb) db.aql.execute("INSERT {question: @q, answer: @a, embedding: @emb} INTO sparta_qra", ...) # GOOD — canonical doc through the memory daemon; semantic sync owns Qdrant client.post("/upsert", json={ "collection": "sparta_qra", "documents": [{"_key": "...", "question": q, "answer": a}], }) ``` **Monitoring (count should be 0 — a hit is a violation, not a gap):** ```aql FOR d IN sparta_qra FILTER HAS(d, "embedding") AND d.embedding != null COLLECT WITH COUNT INTO cnt RETURN cnt ``` **Detection:** `/ops-arango embeddings` reports violations. `--fix` is refused by design; migration is owned by the memory repo (`scripts/migrate_arango_embeddings_to_qdrant.py`). Rules 21 (sparse vector index UPDATE bug) and the vector-index server flags in rule 24 are legacy context for pre-migration collections; do not add new vector indexes to Arango. **Real incident (2026-04-16):** 2,937 QRAs missing embeddings discovered during batch update. These were created by a script that skipped the embedding step. ### 22b. CRITICAL: Qdrant Is Single-Owner — `qdrant-single-owner-access` Qdrant is the counterpart of rule 22: since it is the **only** vector store, it is also a **single-owner** store. The memory repo (`graph_memory`) owns all Qdrant collection config, upserts, and semantic sync. A skill MUST NOT embed raw Qdrant client authoring code. - **NEVER** `from qdrant_client import QdrantClient` (the raw PyPI library) in a