Native Edge Graph SQLite: enable PRAGMA foreign_keys=ON for cascade integrity

Context

The Memory Core Native Edge Graph stores nodes + edges in SQLite at .neo-ai-data/sqlite/memory-core-graph.sqlite. The schema declares FOREIGN KEY (source) REFERENCES Nodes(id) ON DELETE CASCADE on the Edges table — but this only fires when SQLite's PRAGMA foreign_keys=ON is set on the connection. Default is OFF.

This ticket is preventive operational substrate / MX hardening — it ensures cascade deletes work as the schema declares.

The Problem

When a Node is deleted, SQLite silently leaves its referencing Edges as orphan rows (source/target referencing non-existent IDs). The schema's ON DELETE CASCADE is non-functional unless PRAGMA foreign_keys=ON is explicitly enabled per-connection. Operators / scripts performing maintenance deletes get a partial cleanup state requiring manual orphan-edge sweeps.

The Architectural Reality

• SQLite default for foreign_keys PRAGMA is OFF (per SQLite docs — backwards-compat default).
• Memory Core's GraphService uses better-sqlite3 (or similar) to open the DB connection.
• The PRAGMA is per-connection; setting it once at connection creation propagates to all queries on that connection.
• The schema files (Nodes, Edges triggers + FK declaration) are correct; only the runtime PRAGMA is missing.

The Fix

In the connection-creation path (likely `ai/mcp/server/memory-core/services/GraphService.mjs` or its underlying Database wrapper):

```javascript const db = new Database(dbPath); db.pragma('foreign_keys = ON'); // ← add this line ```

If the codebase has a shared SQLite connection helper, add to that helper.

Contract Ledger Matrix

Acceptance Criteria

• PRAGMA foreign_keys = ON set on Memory Core SQLite connection at initialization
• Unit test: insert Node + Edge referencing it; delete Node; assert Edge auto-removed (cascade fires)
• Unit test: insert Node + Edge with target referencing it; delete Node; assert target-side cascade fires
• Document the change in learn/agentos/MemoryCore.md (one-line note in Native Edge Graph section)
• No regressions in existing GraphService tests (cascade may surface previously-tolerated orphan edges; sweep + fix any found)

Out of Scope

• Backfill cleanup of existing orphan edges in current SQLite — covered separately if needed; some orphans may be intentional (legacy) and warrant inspection
• Knowledge Base SQLite — separate substrate; can land as follow-up
• Other PRAGMA tuning (journal_mode, synchronous, etc.) — performance-class concerns; orthogonal

Avoided Traps / Gold Standards Rejected

• Manual cleanup of orphan edges in GraphService.deleteNode — duplicates schema-declared cascade; brittle if call sites bypass GraphService.deleteNode.
• Adding triggers that simulate cascade — same problem as above; cascade is already declared in schema; only the PRAGMA is missing.

Related

This is a substrate-correctness issue: the schema's declared cascade is non-functional at runtime due to a missing connection-time PRAGMA. The fix is a one-line addition that enables the schema's intent to work as designed.

Origin Session ID: 8b31fd62-6a53-40b5-aae2-c5288f8ced09 Retrieval Hint: "SQLite PRAGMA foreign_keys ON Memory Core GraphService cascade orphan edge"