How Foreign Keys Are Enforced in ai-memory's SQLite Database

ai-memory enforces foreign key constraints by toggling SQLite's PRAGMA foreign_keys pragma—disabling it only during schema migrations in crates/ai-memory-store/src/lib.rs and migrations.rs, then re-enabling it for all runtime operations to ensure referential integrity across the single-writer thread and read-only connection pools.

ai-memory is a Rust-based knowledge management system that persists all data to a single SQLite file (data/db/memory.sqlite). To maintain strict referential integrity while allowing flexible schema evolution, the codebase implements a precise foreign key management strategy. This approach ensures that foreign key constraints are validated for every production transaction while safely bypassing them only during controlled migration procedures.

The Foreign Key Toggle Strategy

The core enforcement mechanism relies on SQLite's foreign_keys pragma. Rather than leaving this setting to chance, ai-memory explicitly manages the pragma lifecycle at every database initialization, ensuring constraints are temporarily lifted for schema modifications and then restored for normal operations.

Disabling Constraints During Store Initialization

In crates/ai-memory-store/src/lib.rs, the Store::open method orchestrates the foreign key state during database startup. The implementation first disables foreign keys to allow the migration runner to rebuild tables without interference, executes all pending migrations, then re-enables constraints before handing connections to the application.

// In crates/ai-memory-store/src/lib.rs
conn.pragma_update(None, "foreign_keys", "OFF")?;   // disable FK while migrations run
migrations::run(&mut conn)?;
conn.pragma_update(None, "foreign_keys", "ON")?;    // enable FK for runtime operations

This pattern guarantees that schema changes—such as creating intermediate tables or dropping deprecated columns—can proceed without being blocked by existing referential constraints.

Migration Script Handling

The migration runner itself, located in crates/ai-memory-store/src/migrations.rs, applies the same toggle pattern around individual migration steps that require temporary constraint relaxation. When a migration must recreate a table or perform data transformations that would violate foreign keys transiently, the code explicitly manages the pragma state.

conn.pragma_update(None, "foreign_keys", "OFF")?;
// …run statements that would violate FK temporarily…
conn.pragma_update(None, "foreign_keys", "ON")?;

This granular control ensures that migrations remain atomic and safe, with constraints restored immediately after each risky operation.

Runtime Enforcement and Connection Validation

Once the store initialization completes, every connection used by the application guarantees foreign key validation. The single-writer thread and the read-only connection pool both inherit connections where foreign_keys = ON has been explicitly set, ensuring that every INSERT, UPDATE, and DELETE undergoes SQLite's native referential integrity checks.

The test suite further validates this behavior by explicitly checking for violations after migration failures:

let foreign_key_errors: i64 = conn
    .query_row("SELECT COUNT(*) FROM pragma_foreign_key_check", [], |r| r.get(0))
    .unwrap();
assert_eq!(foreign_key_errors, 0);

This query against pragma_foreign_key_check ensures that no orphaned records or constraint violations persist after schema modifications.

Scope-Guarding Triggers for Complex Constraints

Beyond SQLite's built-in foreign key enforcement, ai-memory implements additional data integrity rules through database triggers. These triggers enforce cross-entity invariants—such as requiring matching workspace and project IDs across tables—that go beyond standard referential constraints.

Key triggers defined in the migration scripts include:

• sessions_ws_proj_pairing_ai – Validates that session records reference consistent workspace and project pairs.
• entity_page_links_scope_pairing_ai – Ensures that entity links maintain proper scope alignment with their parent pages.

These triggers execute whenever foreign keys are enabled, providing an additional layer of validation for the application's complex relational model.

Practical Examples

Verifying Foreign Key Status

To confirm that foreign keys are enabled on any given connection, query the pragma directly:

use ai_memory_store::Store;
use std::path::PathBuf;

let data_dir = PathBuf::from("/path/to/ai-memory/data");
let store = Store::open(&data_dir).expect("failed to open store");

// Verify FK is on (should return 1):
let conn = store.writer.handle().connection();
let fk_status: i32 = conn.query_row(
    "PRAGMA foreign_keys",
    [],
    |row| row.get(0)
).unwrap();

assert_eq!(fk_status, 1);

Handling Constraint Violations

When foreign keys are enabled, attempting to insert orphaned records results in immediate errors:

let err = conn.execute(
    "INSERT INTO pages (id, workspace_id, project_id, path) VALUES (?, ?, ?, ?)",
    params![uuid, b"nonexistent_ws", b"nonexistent_proj", "orphan.md"]
).unwrap_err();

assert!(err.to_string().contains("foreign key constraint failed"));

Temporarily Disabling Foreign Keys in Custom Migrations

When writing custom migration logic that requires temporarily relaxing constraints, follow the established pattern from crates/ai-memory-store/src/ops.rs:

conn.pragma_update(None, "foreign_keys", "OFF")?;
conn.execute(
    "CREATE TABLE workstreams_tmp AS SELECT * FROM workstreams WHERE created_at > ?",
    [timestamp]
)?;
// Perform data migration...
conn.pragma_update(None, "foreign_keys", "ON")?;

Summary

• Foreign keys are disabled via conn.pragma_update(None, "foreign_keys", "OFF") only during schema migration execution in Store::open and the migration runner.
• Constraints are re-enabled immediately after migrations complete using conn.pragma_update(None, "foreign_keys", "ON"), ensuring all runtime connections validate referential integrity.
• Connection pools inherit the enabled state, applying foreign key checks to every operation performed by the single-writer thread and read-only workers.
• Validation queries against pragma_foreign_key_check verify zero violations exist after migration failures.
• Custom triggers such as sessions_ws_proj_pairing_ai enforce higher-level scope constraints beyond standard foreign key relationships.

Frequently Asked Questions

Why does ai-memory disable foreign keys during migrations?

Schema migrations often require intermediate steps—such as creating temporary tables or dropping columns—that would violate referential integrity constraints. By disabling PRAGMA foreign_keys only during these controlled procedures, ai-memory allows safe schema evolution while ensuring constraints are active for all production data operations.

How can I verify that foreign keys are enabled in a connection?

Execute the query PRAGMA foreign_keys against the connection. According to the implementation in crates/ai-memory-store/src/lib.rs, this returns 1 when enforcement is active and 0 when disabled. The Store::open method guarantees this pragma is set to ON before returning the store handle.

What happens if a migration introduces a foreign key violation?

The migration runner explicitly checks for violations after executing migration steps. If SELECT COUNT(*) FROM pragma_foreign_key_check returns a non-zero value, the test suite will fail, indicating that the migration left the database in an inconsistent state with orphaned records or invalid references.

How does ai-memory enforce constraints beyond standard foreign keys?

The system defines SQLite triggers such as entity_page_links_scope_pairing_ai and sessions_ws_proj_pairing_ai directly in the migration scripts. These triggers fire during data modification operations to enforce cross-entity business rules—such as matching workspace/project scope—that standard foreign key constraints cannot express alone.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}