memory+llm: three failures the prod logs gave up ...

All three came out of a survey of the last days of journalctl on prod,
and each one silently destroyed something the user had already paid for.

memory_facts: the no-embedding INSERT bound $13 while its column list
had 12 entries, so every fact written while the embedding backend was
down died on PostgresSyntaxError and the extraction was lost — 47
embed failures in three days, most of them landing in this branch.

Embedding input now gets clipped instead of 400'd away. A 400 takes the
whole embedding with it and recall drops to ILIKE over everything; a
reaction-session prompt (a full event envelope inlined) did that 41
times. settings.embedding_max_chars (6000, 0 disables) caps the input
at the model's window, so recall still works on the head of the text.

Message strips NUL bytes at the model boundary. PostgreSQL text cannot
hold one, and a NUL arriving in a tool result (reading a binary file)
made the whole session_messages INSERT fail with
CharacterNotInRepertoireError — the turn died and the user lost it.
Three times on 2026-10-07. As a Message validator it covers every
writer downstream: session store, kv store, memory extraction.

Each new test was checked to fail with its fix reverted.

Extract single shared Database pool, eliminate 4 duplicated pool creations ...

- Create navi/db.py::Database managing one asyncpg pool
- KvStore, PgSessionStore, MemoryStore, RecallScheduler now accept pool in constructor
- AppContainer holds Database, shutdown closes one pool instead of 4
- create_container creates one pool and passes it to all stores
- All tests updated to set _initialized=True on fakes to skip DDL

392 passed, 1 skipped

Co-Authored-By: Claude Opus 4.7 <noreply@anthropic.com>

Enhance native toolset and add persistent KV store ...

- Add PostgreSQL-backed KvStore (navi/store/) for session-scoped data.
- Migrate todo and scratchpad from in-memory dicts to KvStore.
- Filesystem: add copy, grep, diff actions; compress description.
- CodeExec: remove language param, expose working_dir in schema.
- ImageView: resize to 1024px JPEG + Content-Type guard for URLs.
- Memory list: return distinct categories instead of all facts.
- SSH: add scp action with upload/download support.
- Update CLAUDE.md (Postgres-only), docs/tools.md, add docs/store.md.
- Fix agent/planning/context_builder async signatures for todo helpers.

Co-Authored-By: Claude Opus 4.7 <noreply@anthropic.com>

Clarify knowledge persistence prompts

Fix admin memory showing 0 facts; add pagination/search/sort ...

Bug fix:
- get_all_facts(user_id=None) was filtering for user_id IS NULL only,
  so admin panel showed 0 facts when facts had user_ids set.
- Added all_users parameter to get_all_facts and fact_count.
- Admin endpoint now passes all_users=True to return all facts.

Memory pagination:
- Extended get_all_facts with offset, search, sort_by, sort_order params.
- Extended fact_count with search and all_users params.
- /admin/memory endpoint now accepts limit, offset, search, sort_by,
  sort_order and returns {total, limit, offset, items}.

Admin panel frontend:
- Added server-side search with debounce for memory facts.
- Added sortable column headers (category, key, source, confidence, updated).
- Added pagination controls (prev/next, page size selector).
- Switched memory display from grouped tables to flat sortable table.

Tests:
- Added unit tests for get_all_facts(all_users=True) and
  fact_count(all_users=True).
- All 219 tests pass.

Co-Authored-By: Claude Opus 4.7 <noreply@anthropic.com>

Fix memory system bugs: deterministic summary id, skip legacy extraction ...

- _summary.py: replace non-deterministic hash() with zlib.crc32 so
  summary id stays stable across server restarts, preventing duplicate
  summary rows.
- extractor.py: skip memory extraction for legacy sessions (user_id=None)
  — ON CONFLICT(user_id, category, key) does not catch NULL duplicates
  in PostgreSQL (NULL != NULL).
- sessions.py: _process_stale_sessions skips legacy sessions.
- _facts.py: remove dead code (user_clause/user_param variables).
- test_extractor.py: add user_id to test sessions + new test for
  legacy session skip.

Co-Authored-By: Claude Opus 4.7 <noreply@anthropic.com>

Fix memory system multi-user bugs: summary PK conflict and vector search params ...

- _summary.py: generate deterministic per-user id (hash(user_id)+2) instead of
  hardcoded id=1, preventing UniqueViolationError on memory_summary_pkey
  for multi-user sessions.
- _facts.py: split vector search into user_id=None / user_id!=None branches
  with correct parameter counts, fixing InterfaceError "expects 3 arguments".

Co-Authored-By: Claude Opus 4.7 <noreply@anthropic.com>

Fix memory_summary ON CONFLICT and WebSocket 403 from auth deps ...

- memory/_ddl.py: add UNIQUE(id, user_id) constraint migration for
  memory_summary so ON CONFLICT in set_summary works on existing tables
- auth/deps.py: wrap _resolve_user body in broad try/except so any
  auth resolution failure returns None instead of raising, preventing
  FastAPI dependency system from returning HTTP 403 on WebSocket upgrades

Co-Authored-By: Claude Opus 4.7 <noreply@anthropic.com>

Fix DDL race condition and PostgreSQL syntax error on startup ...

- _ensure_auth_tables now uses raw asyncpg.connect instead of get_session_store,
  avoiding PgSessionStore._MIGRATE running before navi_users exists
- Move _ensure_auth_tables before ensure_tables in startup so navi_users is
  created before any foreign-key references
- Replace invalid "ADD CONSTRAINT IF NOT EXISTS" with DO $$ block for
  memory_facts_user_cat_key

Co-Authored-By: Claude Opus 4.7 <noreply@anthropic.com>

Multi-user auth via gnexus-auth OAuth + hybrid role/permission model ...

- Integrate gnexus-auth-client-py (GAuthClient) for OAuth flow, token refresh,
  and webhook parsing
- Add navi/auth/ package: User model, Fernet encryptor, client singleton,
  deps (get_current_user, require_admin, require_permission)
- New tables: navi_users, user_auth_sessions (auto-created on startup)
- Session/memory isolation by user_id with legacy NULL support
- Cookie-based auth proxy: /auth/login, /callback, /logout, /me
- Webhook receiver /webhooks/gnexus-auth handling user events, global logout,
  session revocation, role/permission changes
- Admin endpoints (/admin/*) gated by role + permissions
- Webclient auth store with isAdmin/hasPermission guards
- Admin-only profile filtering in /agents/profiles
- 200/200 tests passing

Co-Authored-By: Claude Opus 4.7 <noreply@anthropic.com>

Split memory/store.py into focused mixins ...

- _ddl.py: table creation (conditional on pgvector/pg_trgm)
- _embeddings.py: EmbeddingMixin — pgvector checks, embed generation, backfill
- _facts.py: FactMixin — upsert, search (vector + ILIKE), delete, list, count
- _summary.py: SummaryMixin — get/set conversation summary
- _session_state.py: SessionStateMixin — extraction tracking per session
- store.py: reduced to ~60-line facade composing all mixins

No external API changes — MemoryStore remains the single interface.

Co-Authored-By: Claude Opus 4.7 <noreply@anthropic.com>

Architecture extensibility — event bus, middleware, auto-discovery, Pydantic profiles ...

- EventBus: async pub/sub for AgentEvents, WebSocket subscribes instead of direct yield
- Declarative serialization: AgentEvent.to_wire() on all event types
- Auto-discovery for LLM backends (_discover_backends) and workers (scan navi/workers/*.py)
- AgentProfile: Pydantic BaseModel with extra='allow', @field_validator for model coercion
- Tool middleware chain: pre/post execute hooks via ToolRegistry.add_middleware()
- LoggingMiddleware: built-in, logs every tool call
- Fix pg_trgm DDL: conditional GIN indexes via DO $$ block, no CREATE EXTENSION
- New files: event_bus.py, middleware.py, logging_middleware.py

Co-Authored-By: Claude Opus 4.7 <noreply@anthropic.com>

Architecture fixes batch — NaN validation, ILIKE indexes, prompt cache, N+1 batching ...

- _vector_to_str: reject NaN/Inf via math.isfinite() to avoid invalid pgvector syntax
- memory DDL: add pg_trgm + GIN trigram indexes on category/key/value for fast ILIKE fallback
- _build_system_prompt: cache per-profile to avoid rebuilding every iteration
- backfill_embeddings: batch UPDATEs via executemany instead of N+1 loop

No new Python deps; pg_trgm is a PostgreSQL extension auto-created on startup.

Co-Authored-By: Claude Opus 4.7 <noreply@anthropic.com>

Remove SQLite legacy support ...

SQLite is no longer supported; PostgreSQL is now required.

- Delete navi/core/sqlite_session_store.py
- Delete navi/memory/sqlite_store.py
- Remove SqliteSessionStore from navi/core/__init__.py exports
- deps.py: drop SQLite fallback, raise RuntimeError if DATABASE_URL missing
- config.py: remove db_path setting
- pyproject.toml & requirements.txt: drop aiosqlite dependency
- .gitignore: remove navi.db entry
- tech_debt_review_2026-04-29.md: mark #8 as REMOVED

Co-Authored-By: Claude Opus 4.7 <noreply@anthropic.com>

Stability fixes batch — tech debt review 2026-04-29 ...

Critical:
- Concurrent WS run race guard (#1)
- Tool task cancellation on generator teardown (#2)
- StopAsyncIteration kills fallback chain (#3)
- Session loading race with _lastLoadId guard (#4)
- ContentCard .match() crash on non-string result (#5)
- Image data type guard in buildMessageList (#6)

High:
- Cap WS replay buffer at 500 events (#7)
- Deduplicate memory extraction task with asyncio.Lock (#9)
- TTL-based fallback blacklisting (5 min) (#10)
- Subagent tool exception isolation (#11)
- Inline image size/count validation on WS (#12)
- Clean up orphaned file on DB insert failure (#13)
- Deep watch streamingMsg for auto-scroll (#14)
- WS_SCHEME wss:// support for HTTPS (#15)
- Sending guard against duplicate message sends (#16)
- Global unhandledrejection listener in API layer (#17)

Medium:
- Cap planning_logs at 20 entries (#22)
- Store cleanup_loop task reference (#23)
- BaseException → Exception in _run_with_sentinel (#24)
- Propagate SystemExit in agent loop (#25)
- Configurable output_reserve_tokens (#26)
- Always reloadSession on session_sync (#30)
- FIFO queue for confirm dialogs (#31)
- Reset body.overflow on ImageLightbox unmount (#32)
- try/finally in fallback copy (#33)
- _isConnecting guard in WS send() (#34)

Low:
- Lazy-init deps.py singletons (#36)
- Replace __import__ with direct imports (#38)
- Preserve token count 0 in ollama.py (#39)
- Clear orphaned streamingMsg on reconnect reload (#43)
- Escape single quote in UserMessage (#44)
- Polyfill-free findLast replacement (#48)
- Match <table> tags with attributes in markdown (#49)
- Attach copy buttons only when msg.done (#50)
- Fix hasMeta falsy-0 bug (#53)

Co-Authored-By: Claude Opus 4.7 <noreply@anthropic.com>

Add dedicated CPU embedding server for memory backfill ...

- Install Ollama CPU-only on 192.168.1.168 server
- Pull nomic-embed-text:latest on server
- Create systemd service ollama-embed.service (0.0.0.0:11434)
- Add embedding_ollama_host / embedding_ollama_api_key to config.py
- Update deps.py to build separate embedding backend when host configured
- Update backfill_embeddings.py to use dedicated embedding backend
- Add _generate_embeddings batch helper and backfill_embeddings to store.py
- Backfilled 119 existing facts with embeddings

Co-Authored-By: Claude Opus 4.7 <noreply@anthropic.com>

Enrich memory extractor with tool calls/results in transcript ...

- _EXTRACT_SYSTEM now explains 4 transcript entry types and instructs
  LLM to trust tool results over chat, return source/source_context
- _extract_facts builds tool_call_map, appends [Tool call] and
  [Tool result] lines with truncation (500/200 chars)
- Transcript capped at 12k chars (head+tail, drop middle)
- Parse source/source_context from LLM response; map confidence:
  tool_call/auto_discovery=95, user_explicit=90, default=70
- Add TODO comment about deferred semantic deduplication

Co-Authored-By: Claude Opus 4.7 <noreply@anthropic.com>

Add pgvector migration script for memory_facts ...

- ALTER TABLE memory_facts: embedding, source, confidence, expires_at, source_context

- CREATE INDEX: hnsw(embedding), expires, source+category

- Safe to run multiple times (IF NOT EXISTS)

- Reads DATABASE_URL from settings

Wire pgvector semantic search into memory system ...

- Add vector(768) column + HNSW index to memory_facts

- Add LLMBackend.embed() with Ollama + fallback implementation

- MemoryStore: cosine-distance search with ILIKE fallback

- New memory tool params: source, confidence, expires_days, source_context

- Update extractor, sqlite_store, deps wiring

- Add pgvector to requirements

Co-Authored-By: Claude Opus 4.7 <noreply@anthropic.com>

Improve compression and memory prompts

Improve memory search: normalize query, AND-first, relevance scoring ...

- _normalize_query(): hyphens/underscores/slashes/dots → word boundaries,
  strip all other punctuation, lowercase — fixes comma-separated keyword bug
- Auto-dump: if ≤ 60 facts in DB, skip search and return all (no false negatives
  in a small personal memory store)
- AND-first: try matching all terms; fall back to OR only when AND returns nothing
- OR-fallback with scoring: facts matching more terms rank higher (score DESC),
  ties broken by recency

Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>

Migrate storage to PostgreSQL with SQLite fallback; misc fixes ...

- Add PgSessionStore (asyncpg pool) and PgMemoryStore replacing aiosqlite
- Keep SqliteSessionStore + SqliteMemoryStore for zero-dependency quick start
- Selection logic in deps.py: DATABASE_URL set → PG, else → SQLite
- Add asyncpg>=0.29 to dependencies; add DATABASE_URL / DB_PATH to config
- Add RESPONSE HYGIENE rule to persona: never echo tool output or plan state
- Add developer profile user tools: weather, internal_monitor
- Update README: developer profile, DB section, current tool/profile state

Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>

Add long-term user memory system ...

Architecture:
- navi/memory/store.py: MemoryStore backed by SQLite (memory_facts,
  memory_summary, session_memory_state tables in navi.db)
- navi/memory/extractor.py: LLM-based fact extraction from sessions +
  summary regeneration (triggered after session goes idle >30 min)
- Fact upsert uses UNIQUE(category, key) — same key always overwrites,
  no duplicates or stale contradictions
- Keyword search across category + key + value (LIKE-based, no extra deps)

Context injection:
- Memory summary injected as an ephemeral system message on every LLM call
  via Agent._with_memory() — never persisted to session.context

Tools (all profiles):
- memory_search(query): keyword search against fact DB; persona instructs
  model to call it at session start and before personal-context questions
- memory_forget(key, category?): delete a specific fact on user request

Extraction trigger:
- On new session creation, fire-and-forget background task checks all
  sessions idle >30 min with unprocessed messages → runs extraction

Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>