sqlite3.IntegrityError

[cka29]

cce init works fine. However, when running cce serve in a separate terminal I get this errors

CCE v0.4.18 · Starting context engine MCP server...
CCE ready · project · 138 chunks indexed · live watcher active · memory hooks :60381
UserPromptSubmit insert failed
Traceback (most recent call last):
  File "/Users/cka/.local/share/uv/tools/code-context-engine/lib/python3.12/site-packages/context_engine/memory/hooks.py", line 226, in handle_user_prompt_submit
    conn.execute(
sqlite3.IntegrityError: FOREIGN KEY constraint failed
PostToolUse insert failed
Traceback (most recent call last):
  File "/Users/cka/.local/share/uv/tools/code-context-engine/lib/python3.12/site-packages/context_engine/memory/hooks.py", line 283, in handle_post_tool_use
    conn.execute(
sqlite3.IntegrityError: FOREIGN KEY constraint failed
PostToolUse insert failed
Traceback (most recent call last):
  File "/Users/cka/.local/share/uv/tools/code-context-engine/lib/python3.12/site-packages/context_engine/memory/hooks.py", line 283, in handle_post_tool_use
    conn.execute(
sqlite3.IntegrityError: FOREIGN KEY constraint failed
UserPromptSubmit insert failed
Traceback (most recent call last):
  File "/Users/cka/.local/share/uv/tools/code-context-engine/lib/python3.12/site-packages/context_engine/memory/hooks.py", line 226, in handle_user_prompt_submit
    conn.execute(
sqlite3.IntegrityError: FOREIGN KEY constraint failed
auto-prune iteration failed; backing off
Traceback (most recent call last):
  File "/Users/cka/.local/share/uv/tools/code-context-engine/lib/python3.12/site-packages/context_engine/memory/db.py", line 889, in auto_prune_loop
    out = await asyncio.to_thread(_do_prune)
          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/homebrew/Cellar/python@3.12/3.12.13/Frameworks/Python.framework/Versions/3.12/lib/python3.12/asyncio/threads.py", line 25, in to_thread
    return await loop.run_in_executor(None, func_call)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/homebrew/Cellar/python@3.12/3.12.13/Frameworks/Python.framework/Versions/3.12/lib/python3.12/concurrent/futures/thread.py", line 59, in run
    result = self.fn(*self.args, **self.kwargs)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/cka/.local/share/uv/tools/code-context-engine/lib/python3.12/site-packages/context_engine/memory/db.py", line 884, in _do_prune
    payload = prune_old_payloads(conn, days=days)
              ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/cka/.local/share/uv/tools/code-context-engine/lib/python3.12/site-packages/context_engine/memory/db.py", line 725, in prune_old_payloads
    conn.execute(
sqlite3.OperationalError: database is locked
PostToolUse insert failed
Traceback (most recent call last):
  File "/Users/cka/.local/share/uv/tools/code-context-engine/lib/python3.12/site-packages/context_engine/memory/hooks.py", line 283, in handle_post_tool_use
    conn.execute(
sqlite3.IntegrityError: FOREIGN KEY constraint failed
PostToolUse insert failed
Traceback (most recent call last):
  File "/Users/cka/.local/share/uv/tools/code-context-engine/lib/python3.12/site-packages/context_engine/memory/hooks.py", line 283, in handle_post_tool_use
    conn.execute(
sqlite3.IntegrityError: FOREIGN KEY constraint failed
PostToolUse insert failed
Traceback (most recent call last):
  File "/Users/cka/.local/share/uv/tools/code-context-engine/lib/python3.12/site-packages/context_engine/memory/hooks.py", line 283, in handle_post_tool_use
    conn.execute(
sqlite3.IntegrityError: FOREIGN KEY constraint failed
PostToolUse insert failed
Traceback (most recent call last):
  File "/Users/cka/.local/share/uv/tools/code-context-engine/lib/python3.12/site-packages/context_engine/memory/hooks.py", line 283, in handle_post_tool_use
    conn.execute(
sqlite3.IntegrityError: FOREIGN KEY constraint failed
PostToolUse insert failed
Traceback (most recent call last):
  File "/Users/cka/.local/share/uv/tools/code-context-engine/lib/python3.12/site-packages/context_engine/memory/hooks.py", line 283, in handle_post_tool_use
    conn.execute(

Any advise?

Thank you!

[fazleelahhee]

Reproduced and diagnosed. Two related issues:

1. FOREIGN KEY constraint failed (the loud one)

memory/hooks.py lines 226 and 283 — the prompts and tool_events tables both have a foreign key on sessions(id). The SessionStart handler is the only thing that creates the row in sessions. Subsequent UserPromptSubmit / PostToolUse events for that session_id then crash because the FK target doesn't exist.

The lifecycle invariant — "SessionStart must come first" — is fragile. It breaks in at least two real scenarios:

• Starting cce serve after a Claude Code session is already running, so the SessionStart for that session was sent before the hook server was listening.
• Claude Code resuming or re-using a session_id from a prior run where SessionStart doesn't re-fire.

The CI tests (tests/memory/test_hooks.py:221) always POST SessionStart before other hooks, which is why this never tripped before merge.

Fix: make each handler self-healing. Before inserting into prompts / tool_events, do an INSERT OR IGNORE INTO sessions (id, project, started_at_epoch, started_at) VALUES (...) to ensure the FK target exists. Crashing the hook on a missing parent row is the wrong call — we should backfill and continue.

2. database is locked in auto_prune_loop (the quiet one)

memory/db.py:725 — the background prune task contends with the hot insert path on the writer lock. Whatever is set for busy_timeout and journal mode isn't sufficient under load. Needs a verified WAL + busy_timeout configuration plus a retry/backoff in the prune loop so a single contended iteration doesn't trigger the (currently exponential) backoff.

Working on a PR now.