[Bug]: Index primary key omits app/project scope — same entry_id across projects silently overwrites rows #450

Area: src/everos

Summary

episode / atomic_fact / foresight / agent_case rows use
id = f"{owner_id}_{entry.entry_id}" as their LanceDB primary key, but entry_id
(<prefix>_<YYYYMMDD>_<NNNN>) is only unique per markdown file. The same owner
writing on the same day under two different project_ids therefore produces the same
entry_id → the same primary key → the second upsert overwrites the first row.

The result is silent index loss: entries that exist in the markdown source are absent
from the index, while cascade still reports healthy / pending=0. Worse, queries in
one project can return another project's content for that id.

Impact (measured on a real deployment)

Single owner, 8 projects, 24 markdown files, 331 entries total:

The indexed row count equals the number of unique entry_ids exactly — so nothing
failed to index; the collisions silently overwrote each other.

Concrete worst case: one entry_id (ep_20260911_00000001) existed in 7 different
projects; only one row survived, belonging to whichever project was indexed last. The
other 6 projects' /memory/search returned someone else's content for that entry.

GET /api/v2/memory/get and /memory/search both look correct from the outside —
there is no error anywhere, and cascade self-reports healthy.

Root cause

The per-file sequence is documented at
src/everos/infra/persistence/markdown/writers/base.py:

the in-file identity allocated under the per-path lock

and EntryId (src/everos/core/persistence/markdown/entries.py) is
<prefix>_<YYYYMMDD>_<NNNN> with the sequence scoped to a single daily file.

The composite key, however, is built without the scope in four handlers:

src/everos/memory/cascade/handlers/episode.py:105      id=f"{owner_id}_{entry.entry_id}",
src/everos/memory/cascade/handlers/atomic_fact.py:65   id=f"{owner_id}_{entry.entry_id}",
src/everos/memory/cascade/handlers/foresight.py:89     id=f"{owner_id}_{entry.entry_id}",
src/everos/memory/cascade/handlers/agent_case.py:80    id=f"{owner_id}_{entry.entry_id}",

app_id and project_id are already parameters of _build_row(...) at every one of
these call sites, so the scope is available but not used.

Why this looks unintended

entries.py states the uniqueness contract as:

Cross-user uniqueness is handled at the database layer via a composite
<user_id>_<entry_id> field; it is not encoded into the EntryId string itself.

i.e. the design assumes entry_id is unique per owner, and the composite key only
has to disambiguate across owners. But entry_id is actually unique per file, so the
assumption does not hold as soon as one owner writes under more than one
(app_id, project_id) — which is the normal case for any multi-project deployment.

Every other read/update path in the codebase already works around this by adding the
scope to the predicate, which suggests the collision was known about:

• src/everos/infra/persistence/sqlite/repos/cluster.py:125 — the docstring explains the
  collision and the query filters on app_id / project_id / owner_id:

  member_id (e.g. episode entry_id like ep_20260517_00000001) is only
  per-owner unique … Without the scope filter, two owners writing on the same day would
  share entry_id and either collide on the reverse index (false hit → the second
  owner's row silently drops from a cluster it was never part of) or find a foreign
  cluster.

• src/everos/memory/cascade/handlers/_daily_log_base.py:254 — _mark_deprecated
  explicitly scopes the update "to avoid cross-space collisions" (comment in source).

So the predicates are scope-aware, but the primary key is not — which means
_mark_deprecated targeting a project whose row was overwritten matches zero rows and
is silently ignored (its docstring says "A missing row is silently ignored"), so
deprecation marks are lost for those entries too.

Steps to reproduce

No LLM needed.

1. Create a memory root with the same owner in two projects, same date, so both
   daily files allocate sequence 1:

<root>/<app>/proj-a/users/u1/episodes/episode-2026-01-01.md   → entry_id ep_20260101_00000001
<root>/<app>/proj-b/users/u1/episodes/episode-2026-01-01.md   → entry_id ep_20260101_00000001

2. Index both:

echo y | uv run everos cascade rebuild

3. Both entries are present in markdown (2 entries, 1 unique entry_id):

# 2 markdown entries, but only 1 unique entry_id

4. Query the index directly:

import lancedb
df = lancedb.connect("<root>/.index/lancedb").open_table("episode").to_pandas()
print(df[df["entry_id"] == "ep_20260101_00000001"][["id", "entry_id", "project_id"]])

Observed: exactly 1 row, with project_id equal to whichever project was indexed
last — the other project's episode is gone from the index.
Expected: 2 rows, one per (app_id, project_id).

5. /api/v2/memory/search scoped to the project that lost the row returns either nothing
   or the surviving project's content.

Suggested fix

Include the scope in the key, using the app_id / project_id already in scope at the
call sites:

id=f"{owner_id}_{app_id}_{project_id}_{entry.entry_id}",

Because the id format is part of the /memory/get wire contract, it would help to also
expose entry_id on the episode / agent_case DTOs (everos/memory/get/dto.py) —
the column already exists on the LanceDB tables, so consumers can then stop parsing the
composite key. Re-indexing is required afterwards (cascade rebuild); the index is
derivable from markdown, so there is no data loss in doing so.

Note: agent_skill uses skill_id = f"{owner_id}_{name}" (handlers/agent_skill.py:121),
which is also scope-free, but that table has no entry_id column and its documented
semantics are "same agent + same name is the same row", so we did not change it here.
Flagging it in case it is affected by the same class of problem.

If a patch or PR would be welcome, we have a minimal one (4 handler lines + 2 DTO fields

• updated tests, including a regression test that fails on the current formula) and are
  happy to open it.

Environment

OS:       macOS 15.3.1
Python:   3.13.3
lancedb:  0.34.0
Commit:   5076683

Deployment shape: single-owner, multi-project (8 projects), markdown as the source of
truth, cascade rebuild used to rebuild the index. Owner ids and project names in this
report are placeholders; the counts and the ep_20260911_00000001 example are from the
real measurement.

Workaround

Until this is fixed, keep entry_ids unique per owner by renaming/renumbering colliding
entries before indexing, and/or verify after every rebuild that

number of indexed rows == number of unique entry_ids in markdown

(the two differing means rows were overwritten). Note that cascade reporting
healthy / pending=0 does not catch this.