🐛 - Bugbot reports invalid composite foreign key despite an existing imported UNIQUE index #859

Is there an existing issue for this?

• I searched the existing issues for foreign-key Bugbot reports. No matching report was returned. 🐛🧑‍💻 - Bugbot false positive treats JsonDocument.decode object as text and suggests a breaking cast #855 is a different dict-versus-text false positive.

Which actions are affected?

Pull Request

Which platforms are affected?

macOS, Linux

Description

Bugbot publishes a 99%-confidence medium correctness finding that Domus profile foreign keys reference a non-unique parent key and that fresh member creation/schema9 migration are therefore blocked. The exact reviewed program already creates a UNIQUE index on the referenced pair in an existing unchanged task-schema module, before the profile statements. Nine exact-source positive controls and all 4,105 backend tests pass. The proposed single-column workaround would weaken household scope.

Expected: reconcile the complete composed schema/index prerequisites at the exact reviewed source before asserting an invalid SQLite foreign key. An unmodified imported DDL module can establish a valid parent key. Report a real mismatch when the index is absent, nonunique or has different columns, or when a reachable migration skips it. Do not add redundant production constraints or weaken consumer composite FKs just to silence an unsupported finding.

Reproducing the issue

1. Review Persist independent member profiles and migrate SQLite to schema9 domus#24 at e129e76e25deff7314efe3b72996d295cf73f7f3. Finding: Persist independent member profiles and migrate SQLite to schema9 domus#24 (comment) ; thread PRRT_kwDOVa5AKs6ys_tx. Semantic sf-dcd316c2 / fingerprint fp-dab25aac.
2. Native producer https://github.com/vypdev/domus/actions/runs/41459181117/job/125323533768 is completed/success and publishes this finding. The human-visible finding explicitly reasons from IDENTITY_STATEMENTS alone.
3. Read immutable source https://github.com/vypdev/domus/blob/e129e76e25deff7314efe3b72996d295cf73f7f3/backend/src/domus/infrastructure/sqlite_task_schema.py#L5 : TASK_STATEMENTS begins with CREATE UNIQUE INDEX task_member_scope ON members(household_id,member_id).
4. sqlite.py composes TASK_STATEMENTS before PROFILE_STATEMENTS for fresh creation. Upgrades from versions below5 include TASK_STATEMENTS first; genuine5..8 inputs already have that unique index. The profile FK correctly references the exact unique pair.
5. Run tests/backend/test_sqlite_member_profiles.py:74 focused executions pass, including genuine1..8 upgrades, persisted defaults, actual concurrent writers, SQL rollback, independent inspection and generation restoration. Full backend:4,105, all140 modules/9,919 statements/2,822 branches100% with independent per-module audit. Hosted backend on the same head is green: https://github.com/vypdev/domus/actions/runs/41459181271/job/125323533010 .
6. Independent private reproduction uses unchanged production SQLiteDatabase/SQLiteMembers for fresh0 and every genuine1..8 input, migrates to9, inserts a new member and reads both profiles. All9 succeed. PRAGMA index_list confirms task_member_scope unique=1; index_info returns household_id,member_id; foreign_key_check is empty for all9.
7. Isolated in-memory counterfactuals only: omitting TASK_STATEMENTS[0] reproduces the claimed mismatch during profile backfill; replacing only the profile FK with member_id-only allows an invalid nonexistent household while foreign_key_check remains empty. Neither counterfactual changes any repository file or Copilot source. The initial negative driver expected the failure later at member insertion; SQLite already rejected the deliberately missing-index backfill. That driver expectation was corrected, not reported as a production failure.

copilot Version

Native runtime3e7ceea08e6131360e026937d1c0cc1b120472e0; exact Domus heade129e76e25deff7314efe3b72996d295cf73f7f3. No claim about a newer upstream fix.

Relevant Log Output

contextCoverageStatus=complete;37 changed files. Native telemetry and finding are retained separately from local reproductions. Complete source harvesting in the reported dimensions does not establish whether the internal model saw the unchanged task DDL. Hidden prompt/partition contents and root cause are unknown.

Additional context and comments

No production workaround, redundant index, Copilot edit/config/limit change, or merge with an open finding is made. The separate valid low-severity owner-blob documentation finding in this same review is being corrected. Please retain independent dispositions; a valid documentation fix does not validate this foreign-key claim. Suggested regression cases: existing imported unique index, absent index, nonunique index, wrong columns, fresh composition order, historical migrations that already have it, an actually skipped migration prerequisite, and a scope-weakening suggested workaround. Preserve true findings and truthful context completeness.