Evidence schema cannot represent multi-parent provenance #108

New issue

Open

opened 2026-07-28 06:06:02 +00:00 by claude-bot · 0 comments

claude-bot commented

2026-07-28 06:06:02 +00:00

Severity: MEDIUM - blocking schema work, cheapest before Phase 2 data volume

The problem

Two schema gaps will require table-rebuild migrations on SQLite, and are far cheaper to fix before
the evidence table holds tens of thousands of rows.

1. derived_from is a single FK, but constraints have two parents.
backend/app/models/models.py:303-305 models provenance as one nullable FK. But the constraint
propagation engine (#19) derives a range from two anchors — "between photo 12 (June 1987) and
photo 20 (September 1987)". That is unrepresentable, and the constraint explanation UI (#20)
depends on exactly that chain being navigable. docs/circa-spec.md:401 specifies multi-parent
provenance as JSON "source evidence ids".

2. EvidenceSource lacks event_anchor. The spec lists it (circa-spec.md:392) and #28 needs
it.

Also absent versus the spec: source_subtype (e.g. polaroid_sx70, birthday_anchor),
is_hard_constraint, cleared_by_decision_id, and any CHECK enforcing date_low <= date_high.

Recommendation

Add an evidence_derivation(evidence_id, source_evidence_id) link table rather than the spec's
JSON column — cleanly queryable, and the explanation UI needs to traverse it.
Add event_anchor to EvidenceSource in the same migration.
Add source_subtype; it feeds the format-rule work.
Add the date_low <= date_high CHECK.
is_hard_constraint can stay folded into reliability=hard if that is a deliberate
simplification — but document that, since the spec disagrees.

Note the unused enum members that already exist (DuplicateType, the extra job types, audit types)
are reasonable forward provisioning. The gaps are the problem, not the surplus.

Done when

Evidence can record multiple parents, and the chain is queryable in both directions
event_anchor exists as a source
Date ranges are constrained at the database level
The migration runs cleanly on an existing database

References

backend/app/models/models.py:77-84,303-305
docs/circa-spec.md:392,401

Blocks: #19, #20, #28.

## Severity: MEDIUM - blocking schema work, cheapest before Phase 2 data volume ## The problem Two schema gaps will require table-rebuild migrations on SQLite, and are far cheaper to fix before the evidence table holds tens of thousands of rows. **1. `derived_from` is a single FK, but constraints have two parents.** `backend/app/models/models.py:303-305` models provenance as one nullable FK. But the constraint propagation engine (#19) derives a range from **two** anchors — "between photo 12 (June 1987) and photo 20 (September 1987)". That is unrepresentable, and the constraint explanation UI (#20) depends on exactly that chain being navigable. `docs/circa-spec.md:401` specifies multi-parent provenance as JSON "source evidence ids". **2. `EvidenceSource` lacks `event_anchor`.** The spec lists it (`circa-spec.md:392`) and #28 needs it. Also absent versus the spec: `source_subtype` (e.g. `polaroid_sx70`, `birthday_anchor`), `is_hard_constraint`, `cleared_by_decision_id`, and any CHECK enforcing `date_low <= date_high`. ## Recommendation - Add an `evidence_derivation(evidence_id, source_evidence_id)` link table rather than the spec's JSON column — cleanly queryable, and the explanation UI needs to traverse it. - Add `event_anchor` to `EvidenceSource` in the same migration. - Add `source_subtype`; it feeds the format-rule work. - Add the `date_low <= date_high` CHECK. - `is_hard_constraint` can stay folded into `reliability=hard` if that is a deliberate simplification — but document that, since the spec disagrees. Note the *unused* enum members that already exist (DuplicateType, the extra job types, audit types) are reasonable forward provisioning. The gaps are the problem, not the surplus. ## Done when - [ ] Evidence can record multiple parents, and the chain is queryable in both directions - [ ] `event_anchor` exists as a source - [ ] Date ranges are constrained at the database level - [ ] The migration runs cleanly on an existing database ## References - `backend/app/models/models.py:77-84,303-305` - `docs/circa-spec.md:392,401` Blocks: #19, #20, #28.

claude-bot added this to the v0.4.0 milestone

2026-07-28 06:06:02 +00:00

claude-bot added the

backend

enhancement

labels

2026-07-28 06:06:02 +00:00

claude-bot referenced this issue

2026-07-28 06:07:52 +00:00

Implement the group constraint propagation engine #19

claude-bot referenced this issue

2026-07-28 06:07:52 +00:00