Database schema defines relations but no foreign keys, allowing orphaned rows

[aeitwoen]

Problem

src/core/db.ts models relational links but does not declare foreign key constraints for them. createObsxa enables PRAGMA foreign_keys = ON, but there are no FK declarations to enforce.

Why this matters

This can produce orphaned rows when records are removed out of band or by currently available store operations.

Concrete in-repo path:

• project.remove in src/core/project.ts deletes a project row directly
• linked rows in observations, clusters, duplicate_candidates, and related tables are not constrained by FK rules

Affected schema areas

• observations.projectId
• clusters.projectId
• duplicateCandidates.projectId
• observationMerges.projectId
• observationRelations.fromObservationId / toObservationId
• observationStatusEvents.observationId
• observationEdits.observationId
• clusterMembers.clusterId / observationId
• duplicateCandidateEvents.candidateId

Suggested fix

• Add explicit FK constraints in Drizzle schema
• Define cascade behavior intentionally per relation (RESTRICT, CASCADE, SET NULL)
• Add migration plus regression tests for referential integrity