search_index permalink collision between observation and relation rows

[phernandez]

Summary

A relation permalink is from/type/to with the type authored by the user, so a relation can be made to occupy any path — including one an observation already owns. The two rows then collide on search_index's permalink uniqueness.

This is pre-existing on main, not introduced by any branch in flight. Raised during review of #1426 and reproduced there against origin/main with no temporal qualifier, no duplicate observation, and no ordinal involved.

Reproduction

A note containing:

- [decision] redis
- observations [[decision/redis]]

produces a colliding pair.

• Postgres: IntegrityError — Key (2, observation, 1) is not present in table "search_index"
• SQLite: both rows silently coexist under one permalink

The SQLite behavior is the more dangerous of the two, since nothing surfaces.

Why a better segment does not fix it

The obvious mitigation — reserving a path segment that relations cannot occupy — does not work. The relation type is authored, so any reserved segment can be spelled by a user and collide again. There is no naming scheme that closes it.

Direction

Put the row kind into the address or into the uniqueness scheme, so an observation and a relation cannot occupy the same key regardless of what either is named.

That is a migration on search_index's unique index, and it affects every row kind on both backends — entity, observation and relation. It wants to be scoped and tested on its own rather than carried along by a feature branch.

Note

Raised on #1426 (3918032660) and declined there on scope, with the reproduction above run on origin/main as the evidence that it is not that branch's defect.

[claude]

Claude finished @phernandez's task in 39s —— View job

---

Triage

Type: Bug — search_index's permalink uniqueness assumes each permalink maps to one row kind, but a relation's permalink (from/type/to) uses a user-authored type, so a relation can occupy a path an observation already owns. Confirmed against origin/main with a minimal repro (no temporal qualifier, no dedup, no ordinal involved), so this is independent of #1426.

Component: Database/sync (core search_index schema) — not cloud-specific; affects both SQLite and Postgres backends identically.

Priority: High — on Postgres this raises an IntegrityError on write; on SQLite it silently coexists under one permalink with no surfaced error, which is the more dangerous failure mode since data can be silently shadowed/overwritten in the index.

Complexity: Complex — as the issue notes, no permalink-naming mitigation closes this because the colliding segment is user-authored. The real fix is putting row kind into the uniqueness scheme (e.g., composite key including entity/observation/relation kind) on search_index, which requires an Alembic migration touching every row kind on both backends, plus tests reproducing the Postgres/SQLite divergence described here. Reasonable to scope as its own PR rather than folding into #1426, as requested.

Duplicate check: No exact duplicate found. #909 (closed) covered a related but distinct failure — observation-vs-observation permalink collisions from truncated-hash content dedup — not the observation-vs-relation kind collision described here. This issue is a new, non-duplicate report.

Reproduction is clear and self-contained; no further repro steps needed from the reporter.