Memory sync: UNIQUE constraint failure on memory_docs.document_id breaks GitHub sync on every tick

[Al629176]

Summary

The GitHub Composio memory sync pipeline fails on every periodic tick with a SQLite UNIQUE constraint violation on memory_docs.document_id. The pipeline retries next tick but encounters the same error, so GitHub memory never syncs after the first successful ingest.

Log evidence

08:53:50:WRN [sync:orchestrator] sync failed toolkit="github" connection_id="ca_HLk7bTXBixH6" error=upsert memory_docs: UNIQUE constraint failed: memory_docs.document_id
08:53:50:WRN [memory_sync:dispatcher] pipeline tick failed pipeline_id="composio:github" error=upsert memory_docs: UNIQUE constraint failed: memory_docs.document_id
08:53:50:WRN [composio:periodic] sync failed (will retry next tick) toolkit=github connection_id=ca_HLk7bTXBixH6 error=upsert memory_docs: UNIQUE constraint failed: memory_docs.document_id

09:32:05:WRN (same pattern, connection_id="ca_BRvLIFLFLj4l")

Recurs every sync interval. Two different connection IDs both hit the same failure.

Root cause

The upsert path for memory_docs is not using INSERT OR REPLACE / ON CONFLICT DO UPDATE. When a document with the same document_id already exists (e.g. a GitHub issue that was previously ingested), the insert fails instead of updating.

Impact

• GitHub memory sync is permanently broken after first ingest — new PRs, issues, and commits are never added to memory.
• The error is non-fatal and silent to the user; no UI notification is shown.
• Affects at minimum two separate GitHub connections.

Expected behaviour

Re-syncing an already-ingested document should update it in place (upsert), not fail with a constraint error.

Fix direction

Change the SQL in the memory_docs upsert to:

INSERT INTO memory_docs (...) VALUES (...)
ON CONFLICT(document_id) DO UPDATE SET ...

Environment

• OpenHuman 0.63.17 (aarch64-apple-darwin)
• Log date: 2026-09-09
• Composio GitHub integration active

[YellowSnnowmann]

Root cause is different from the fix direction in the description, so recording what the code actually does.

The upsert already has ON CONFLICT(namespace, key) DO UPDATE. The failing constraint is the table's PRIMARY KEY, document_id, not the (namespace, key) unique index. SQLite checks the upsert's conflict target first, so this error only fires for a write whose (namespace, key) is new while its document_id is already taken by another row.

Where that happens. The Composio GitHub pipeline in the engine (tinycortex orchestrator → HostSyncAdapter::store → MemoryClient::store_skill_sync) writes namespace skill-github, key github:{id}, document id github:{id}. It has keyed by the stable id since #4953 (2026-07-17); before that the key was the item's title, with the same github:{id} document id. A pre-#4953 row whose issue is updated later is re-fetched (updated:>cursor), written under the new key → primary-key collision with its own old row. The GitHub source does not tolerate scope errors, so the whole run aborts, the cursor never advances, and the next tick hits the same item. Both connections share the skill-github namespace, so both fail. Verified on a local profile that had both generations of rows (43 title-keyed from 2026-07-15, 186 id-keyed from 2026-07-23).

Versions. 0.63.17 (2026-08-21) pins tinymemory v1.1.0-7, which still routes Composio through that engine pipeline. Since tinymemory v1.13.4 (2026-08-30; openhuman ≥ v0.63.20 pins v1.13.7) Composio sources are read by the connector module and stored through accept_source_items — namespace source:github:{connection}, key = item id, no caller-supplied document id — so the reported failure cannot occur on current releases. Upgrading stops it. The legacy skill-github rows stay searchable.

Fix. The store hazard is real for every writer that supplies a document_id, so it is fixed in the store rather than in one caller: tinyhumansai/tinymemory#146 resolves the row a write addresses through its requested id too (same-namespace row under a stale key → re-keyed and updated in place; id owned by another namespace → derived id, never a failure). Regression tests reproduce the exact error. Follow-up: tinymemory patch release, then an openhuman re-pin PR that closes this issue.

[M3gA-Mind]

#6147 — "UNIQUE constraint failure on memory_docs.document_id"

Verdict: Confirmed as a real failure on 0.63.17, misdiagnosed as to cause, and
already unreachable on main. The investigation surfaced a different, still-live
bug that matters more than the reported one.

PR tinyhumansai/tinymemory#146 is already open (YellowSnnowmann). Its analysis
is correct; I independently confirmed each load-bearing claim below.

Line numbers: vendor/tinymemory at 9143fe1 (v1.15.2), the SHA upstream/main pins.

1. The stated cause is wrong

The issue says the upsert "is not using INSERT OR REPLACE / ON CONFLICT DO UPDATE".
It already is:

• crates/tinymemory-core/src/store/namespace_store/documents.rs:257 —
  ON CONFLICT(namespace, key) DO UPDATE SET ...
• same clause on the metadata-only path at documents.rs:420

The table has two constraints (namespace_store/init.rs:158-175):

document_id TEXT PRIMARY KEY,
...
UNIQUE(namespace, key)

The upsert's conflict target is (namespace, key). A collision on the primary
key is not covered by that target, so SQLite raises
UNIQUE constraint failed: memory_docs.document_id. The error therefore fires
only for a write whose (namespace, key) is new while its requested
document_id is already taken by another row.

The issue's proposed fix — ON CONFLICT(document_id) DO UPDATE — would swap
which constraint is unhandled, not fix it. The two constraints can name
different rows, so no single conflict target is correct; the identity has to be
resolved before the write.

2. Trigger, and why it is gone on main

The GitHub skill-sync key scheme changed (item title → stable github:{id})
while document_id stayed github:{id}. A row written under the old scheme,
re-synced under the new key, collides with itself on the primary key. Both
connections share the skill-github namespace, so both fail; the GitHub source
aborts the run, the cursor never advances, and every tick repeats — matching the
reported "fails on every tick".

Could not determine: I did not verify the specific legacy corpus (the "43
title-keyed rows" in PR #146's replay) — I have no profile DB to hand. The
mechanism follows from the schema and the code; the corpus figure is YS's
observation, not something I re-checked.

Already fixed on main, by routing rather than by any upsert change:
crates/tinymemory-core/src/engine/sync.rs:606-612 states plainly that Composio
sources never reach the HostSyncAdapter seam — run_source_pipeline routes
them to crate::sync::pipelines first. openhuman upstream/main pins
tinymemory at 9143fe1 = v1.15.2 (2026-09-07), past the v1.13.4 that moved
the routing.

Alan reported on 0.63.17, a shipped release pinning the old tinymemory. Real
for users on that build; the user-facing remedy is upgrading, not a code change.

3. The bug that actually matters — still live on main

documents.rs:198-200:

let document_id = input
    .document_id
    .or(existing_document_id)
    .unwrap_or_else(|| Self::derive_document_id(&namespace, &key));

The caller's id wins over the row's own id. When a row already exists for
(namespace, key) under a different document_id:

• the INSERT takes the ON CONFLICT(namespace, key) DO UPDATE branch, whose
  SET list (documents.rs:257-269) does not include document_id — the
  row keeps its original id;
• the chunk write immediately after uses the requested id:
  DELETE FROM vector_chunks WHERE namespace = ?1 AND document_id = ?2
  (documents.rs:288-291) and the inserts that follow.

Chunks land under an id no row carries. forget deletes chunks by the row's
id, so it never reaches them: recall keeps returning content the caller
deleted. Silent, no error, no log.

The code's own comment at documents.rs:204-212 describes exactly this hazard
for the concurrent-mint case and explains why a derived id avoids it — the
input.document_id precedence reintroduces it for any caller that supplies an
id. HostSyncAdapter::store still supplies one (engine/sync.rs:635) for the
tree-coupled source kinds that do still reach that seam.

4. Blast radius

Writers routing through the upsert:
store_skill_sync (store/client.rs:329, forwards the caller id),
upsert_document / upsert_documents / upsert_document_metadata_only
(store/client.rs:192, 222, 256; re-declared at store/write_gate.rs:141),
HostSyncAdapter::store (engine/sync.rs:617-640).

Consumers of the returned id — these are what break when it is a phantom:
ingestion/mod.rs:43 → ingestion/queue.rs:46-48 (the graph job is keyed by it),
engine/sync.rs:653 (ingest_connector_item_tolerated).

Second write path with the same shape: the metadata-only upsert at
documents.rs:416-425.

Test pinning current behaviour:
store/client_tests.rs:114 —
store_skill_sync_with_secret_like_title_uses_stable_document_id_as_key.

5. Why no test caught it

Every existing test writes into a clean store, where (namespace, key) and
document_id always agree, so neither branch is reachable. The failure needs a
corpus written under an older key scheme — a two-generation fixture.

No lane builds one. tinymemory's CI tests a fresh DB; openhuman's CI never
exercises the vendored store's write path against a migrated corpus at all. This
is an upgrade/migration-shaped gap — not a feature gate, not jsdom, not a
coverage lane that skips PRs. Worth noting because it will keep swallowing this
class of bug: nothing in either repo tests "old data, new code".

6. Fix shape

Two separable pieces, and I would not treat them as one job.

(a) The corruption at documents.rs:198 — do this. One guard where every
writer already routes through: when a row exists for (namespace, key), its own
id wins over the requested one, and chunks/sidecar/graph all use the resolved id.
Small, high value, fixes a silent data-loss class rather than one caller.

(b) The re-key half — question whether it needs to exist. PR #146 also
teaches the store to re-key a row when the requested id names a same-namespace
row under a stale key (~171 lines in documents.rs plus an in-transaction
re-check on every write). Its only known trigger is a one-time key-scheme
migration. A one-shot migration of the legacy skill-github rows would be
smaller and would not put an extra primary-key lookup on every document write
forever.

Decision for you: permanent store-level re-keying (PR #146 as written) vs.
(a) plus a one-shot migration. I would take the latter; PR #146 takes both. Not
my call — it depends on whether other key schemes are expected to change.

7. Severity

Alan filed it P1; the label is P1. I disagree with the priority as attached to
the reported symptom, and agree with it as attached to what is underneath.

• The reported sync failure is not P1. It is already unreachable on main and
  fixed by upgrading. It is also loud — it logs on every tick, which is the good
  kind of failure.
• The corruption at documents.rs:198 is the P1. Silent, still live on main,
  and it makes deletes not delete: recall returns content the user removed. No
  error, no log, no way for anyone to notice from the outside.

Silent data corruption outranks a noisy error. Recommend keeping P1 but
repointing the issue — or splitting the corruption into its own issue, since it
has nothing to do with GitHub sync and will outlive this one.