Mark/osint-hub
1
0
Fork 0
Code Issues Pull requests Projects Releases Packages Wiki Activity Actions

Add alembic migration for ondelete=CASCADE on organization child FKs #96

New issue
Closed
opened 2026-08-11 05:09:14 +00:00 by Mark · 3 comments
Mark commented 2026-08-11 05:09:14 +00:00
Owner
Copy link

d0723c9 added ondelete=CASCADE to OrganizationEmail/Phone/Domain org_id foreign keys in app/models.py, but only the SQLModel metadata changed — no alembic migration was generated, so an existing Postgres database still has the old FK without ON DELETE CASCADE. Deleting an organization in production will raise a foreign-key violation on child rows instead of cascading. Generate and commit a migration (e.g. drop/recreate the three FKs with ondelete=CASCADE).

d0723c9 added ondelete=CASCADE to OrganizationEmail/Phone/Domain org_id foreign keys in app/models.py, but only the SQLModel metadata changed — no alembic migration was generated, so an existing Postgres database still has the old FK without ON DELETE CASCADE. Deleting an organization in production will raise a foreign-key violation on child rows instead of cascading. Generate and commit a migration (e.g. drop/recreate the three FKs with ondelete=CASCADE).
Mark commented 2026-08-11 06:38:56 +00:00
Author
Owner
Copy link

Related fix landed in 9a07880: alembic had TWO heads (d1e2f3a4b5c6 and a2b3c4d5e8f0 both branched from b5f6a7c8d9e0 with no merge), so alembic upgrade head failed outright with "Multiple head revisions are present" on any fresh database. Added merge migration a25b7f06b9f1 so migrations can actually run. Note: the broader migration story is still inconsistent — alembic migrations assume the base schema exists via SQLModel.metadata.create_all (entrypoint.sh runs create_all first), so migrations are additive patches rather than a complete chain. Generating the ondelete=CASCADE migration for organization_email/phone/domain from this issue remains valid work on top of the merge fix.

Related fix landed in 9a07880: alembic had TWO heads (d1e2f3a4b5c6 and a2b3c4d5e8f0 both branched from b5f6a7c8d9e0 with no merge), so `alembic upgrade head` failed outright with "Multiple head revisions are present" on any fresh database. Added merge migration a25b7f06b9f1 so migrations can actually run. Note: the broader migration story is still inconsistent — alembic migrations assume the base schema exists via SQLModel.metadata.create_all (entrypoint.sh runs create_all first), so migrations are additive patches rather than a complete chain. Generating the ondelete=CASCADE migration for organization_email/phone/domain from this issue remains valid work on top of the merge fix.
Mark commented 2026-08-16 21:50:49 +00:00
Author
Owner
Copy link

Migration implemented in PR #103 (#103).

c9e3721d770e drops and recreates the three org_id foreign keys (organization_email_org_id_fkey, organization_phone_org_id_fkey, organization_domain_org_id_fkey) with ON DELETE CASCADE. Verified on scratch Postgres 16: upgrade on fresh + pre-seeded DBs applies cleanly, DELETE FROM organization cascades to child rows, downgrade -1 restores NO ACTION FKs. Full psql evidence pasted in the PR body.

Migration implemented in PR #103 (https://forgejo.tail018ac4.ts.net/Mark/osint-hub/pulls/103). `c9e3721d770e` drops and recreates the three org_id foreign keys (`organization_email_org_id_fkey`, `organization_phone_org_id_fkey`, `organization_domain_org_id_fkey`) with `ON DELETE CASCADE`. Verified on scratch Postgres 16: upgrade on fresh + pre-seeded DBs applies cleanly, `DELETE FROM organization` cascades to child rows, `downgrade -1` restores NO ACTION FKs. Full psql evidence pasted in the PR body.
Mark commented 2026-09-10 04:03:22 +00:00
Author
Owner
Copy link

Closed via watcher recovery: kilo-gsd runner reached Phase 8/9 commit but the synthesized salvage commit on fix/101-fk-cascade included a stray off-scope migration (3f4a5b6c7d8e_add_investigation_completeness_metrics.py — leftover from a prior run, not part of #96). The runner's --ff-only merge of that branch into main then failed because origin/main had moved (lint-cleanup PRs landed between dispatch and commit), and the Forgejo API close attempt on top of the failed push errored.

Watcher recovery applied:

  1. Reset local main to origin/main (93791d4).
  2. Cherry-picked only the real #96 fix commit 7f747ef onto main (now 8a891e7) — drops the bad synthesized commit.
  3. Verified all 5 gate commands pass on the clean state:
    • ruff check backend/alembic/versions/b2c3d4e5f6a8_add_ondelete_cascade_org_child_fks.py → PASS
    • ruff format --check ... → PASS
    • pytest backend/tests/unit/test_organization_crud.py::TestOrganizationRelatedEntities::test_delete_organization_cascade_to_emails → PASS (1/1)
    • pytest backend/tests/unit/test_organization_crud.py → PASS (42/42)
    • pytest backend/tests/integration/test_api_entities.py::TestOrganizationAPI::test_delete_organization → PASS (1/1)
  4. git push origin main → 93791d4..8a891e7 main -> main (clean fast-forward).

The landed migration (b2c3d4e5f6a8_add_ondelete_cascade_org_child_fks.py, 173 lines) mirrors the proven f101c4scd0a1_add_ondelete_cascade_case_membership_fk pattern from #101 with an _fk_has_cascade guard making it a safe no-op on rerun or fresh schemas. Down_revision = f101c4scd0a1.

The existing PRs #103 (fix/96-org-fk-cascade, migration c9e3721d770e) and #108 (fix/96-ondelete-cascade-migration, migration b53942abd63a) are now superseded by the landed commit on main. Closing them as superseded to avoid drift.

Closed via watcher recovery: kilo-gsd runner reached Phase 8/9 commit but the synthesized salvage commit on `fix/101-fk-cascade` included a stray off-scope migration (`3f4a5b6c7d8e_add_investigation_completeness_metrics.py` — leftover from a prior run, not part of #96). The runner's `--ff-only` merge of that branch into `main` then failed because `origin/main` had moved (lint-cleanup PRs landed between dispatch and commit), and the Forgejo API close attempt on top of the failed push errored. Watcher recovery applied: 1. Reset local `main` to `origin/main` (93791d4). 2. Cherry-picked only the real #96 fix commit `7f747ef` onto `main` (now `8a891e7`) — drops the bad synthesized commit. 3. Verified all 5 gate commands pass on the clean state: - `ruff check backend/alembic/versions/b2c3d4e5f6a8_add_ondelete_cascade_org_child_fks.py` → PASS - `ruff format --check ...` → PASS - `pytest backend/tests/unit/test_organization_crud.py::TestOrganizationRelatedEntities::test_delete_organization_cascade_to_emails` → PASS (1/1) - `pytest backend/tests/unit/test_organization_crud.py` → PASS (42/42) - `pytest backend/tests/integration/test_api_entities.py::TestOrganizationAPI::test_delete_organization` → PASS (1/1) 4. `git push origin main` → 93791d4..8a891e7 main -> main (clean fast-forward). The landed migration (`b2c3d4e5f6a8_add_ondelete_cascade_org_child_fks.py`, 173 lines) mirrors the proven `f101c4scd0a1_add_ondelete_cascade_case_membership_fk` pattern from #101 with an `_fk_has_cascade` guard making it a safe no-op on rerun or fresh schemas. Down_revision = `f101c4scd0a1`. The existing PRs #103 (`fix/96-org-fk-cascade`, migration `c9e3721d770e`) and #108 (`fix/96-ondelete-cascade-migration`, migration `b53942abd63a`) are now superseded by the landed commit on `main`. Closing them as superseded to avoid drift.
Mark closed this issue 2026-09-10 04:03:22 +00:00
Sign in to join this conversation.
No Branch/Tag specified
Branches Tags
main
No results found.
Labels
Clear labels   
agentic-osint

Agentic OSINT features

  
audit

   
blocked:commit-ready-failed

   
blocked:gate-fetch

   
blocked:gate-fetch

   
blocked:gate-lint

   
blocked:gate-plan

   
blocked:gate-research

   
blocked:needs-decision

   
blocked:needs-human

   
blocked:no-changes

   
blocked:preflight

Repo has dirty lint; kilo-gsd preflight refuses to dispatch until lint-cleanup lands

  
blocked:verify-failed

   
bug

Something is not working

  
chore

Config/cleanup item

  
deferred

Deferred — will revisit later

  
enhancement

Enhancement request

  
feature-request

   
in-progress

   
lint-baseline

   
lint-cleanup

kilo-gsd preflight lint gate: repo has dirty ruff state, cleanup required before fixer dispatch

  
needs-decompose

Issue needs decompose processing

  
needs-triage

   
performance

   
quickfix

   
security/medium

Medium severity security issue

  
triage-approved

Approved by triage

No labels
agentic-osint
audit
blocked:commit-ready-failed
blocked:gate-fetch
blocked:gate-fetch
blocked:gate-lint
blocked:gate-plan
blocked:gate-research
blocked:needs-decision
blocked:needs-human
blocked:no-changes
blocked:preflight
blocked:verify-failed
bug
chore
deferred
enhancement
feature-request
in-progress
lint-baseline
lint-cleanup
needs-decompose
needs-triage
performance
quickfix
security/medium
triage-approved
Milestone
Clear milestone
No items
No milestone
Projects
Clear projects
No items
No project
Assignees
Clear assignees
No assignees
1 participant
Notifications
Due date
The due date is invalid or out of range. Please use the format "yyyy-mm-dd".

No due date set.

Dependencies

No dependencies set.

Reference
Mark/osint-hub#96
No description provided.