---
title: 'Add FK cascade delete for domain_term_dictionary and data_hierarchical_nodes'
type: 'chore'
created: '2026-04-09'
status: 'done'
baseline_commit: '1d824d4'
context: []
---

<frozen-after-approval reason="human-owned intent -- do not modify unless human renegotiates">

## Intent

**Problem:** Two tables reference documents without FK constraints: (1) `domain_term_dictionary.source_document_id` — plain UUID, no FK. (2) `data_hierarchical_nodes` — document_id only in JSONB metadata, no column or FK. When a document is deleted, orphaned records must be cleaned up manually.

**Approach:** Add Alembic migration(s) that: (1) Add FK on `domain_term_dictionary.source_document_id` → `documents.id` with `ON DELETE CASCADE`. (2) Add a `document_id` UUID column to `data_hierarchical_nodes`, backfill from JSONB metadata, and add FK → `documents.id` with `ON DELETE CASCADE`. Update SQLAlchemy models to match.

## Boundaries & Constraints

**Always:**
- Migrations must handle orphaned records (NULL out invalid references before adding FK)
- Include both upgrade and downgrade paths
- Update SQLAlchemy models in `src/db/models.py`
- Backfill `data_hierarchical_nodes.document_id` from `value->'__data__'->'metadata'->>'document_id'`

**Ask First:**
- N/A

**Never:**
- Change existing column types or nullability
- Break LlamaIndex's usage of `data_hierarchical_nodes` (it manages id, key, namespace, value)

## I/O & Edge-Case Matrix

| Scenario | Input / State | Expected Output / Behavior | Error Handling |
|----------|--------------|---------------------------|----------------|
| Clean DB | No orphaned records | FKs added successfully | N/A |
| Orphaned domain terms | source_document_id → deleted doc | NULL out orphans before FK | Migration handles |
| Orphaned chunks | JSONB metadata → deleted doc | NULL the new column before FK | Migration handles |
| Chunks without metadata doc ID | JSONB has no document_id key | document_id column stays NULL | FK allows NULL |
| Document deleted after FK | Document row removed | Both domain terms and chunks auto-deleted | PostgreSQL CASCADE |

</frozen-after-approval>

## Design Notes

**Backfill from 3 namespace paths:** `data_hierarchical_nodes` stores document references differently per namespace:
- `hierarchical/data`: `value->'__data__'->'metadata'->>'document_id'`
- `hierarchical/metadata`: `value->>'ref_doc_id'` (confirmed identical to document_id)
- `hierarchical/ref_doc_info`: `key` column IS the document UUID

**Trigger extension:** The existing `set_hierarchical_node_tenant()` trigger (from migration `20260330_100000`) already auto-populates `tenant_id`/`node_id` from JSONB on INSERT/UPDATE. The new migration replaces this function to also extract `document_id` using the same 3-path logic. This means no DAG or LlamaIndex code changes are needed — new inserts auto-populate `document_id` via the trigger.

**Downgrade restores original trigger** without `document_id` extraction.

## Code Map

- `src/db/migrations/versions/20260409_100000_add_domain_term_document_fk.py` -- NEW: Migration for domain_term_dictionary FK
- `src/db/migrations/versions/20260409_100001_add_hierarchical_nodes_document_id.py` -- NEW: Add document_id column, backfill (3 paths), NULL orphans, extend trigger, add FK + index
- `src/db/models.py` -- Add ForeignKey to domain_term_dictionary.source_document_id
- `airflow/plugins/api/upload.py` -- Remove explicit DELETE of chunks and domain terms from delete_document (CASCADE handles it now)
- `src/db/migrations/versions/20260330_100000_rls_hierarchical_nodes.py` -- Reference: original trigger that is extended

## Tasks & Acceptance

**Execution:**
- [x] `src/db/migrations/versions/20260409_100000_add_domain_term_document_fk.py` -- NEW: NULL orphans, add FK on source_document_id → documents.id with ON DELETE CASCADE
- [x] `src/db/migrations/versions/20260409_100001_add_hierarchical_nodes_document_id.py` -- NEW: Add document_id UUID column, backfill from JSONB metadata (3 namespace paths), NULL orphans, extend trigger, add FK → documents.id with ON DELETE CASCADE, add index
- [x] `src/db/models.py` -- Add ForeignKey to domain_term_dictionary.source_document_id
- [x] `airflow/plugins/api/upload.py` -- Remove explicit DELETE of data_hierarchical_nodes and domain_term_dictionary from delete_document(); CASCADE handles both

**Acceptance Criteria:**
- Given the migrations are applied, when a document is deleted, then all domain terms and chunks linked to it are automatically deleted by PostgreSQL
- Given orphaned records exist, when migrations run, then orphans are NULLed and FKs are added without error
- Given a chunk with no document_id in JSONB metadata, when migration runs, then document_id column is NULL and FK allows it

## Verification

**Commands:**
- `uv run alembic upgrade head` -- expected: both migrations apply without error
- `SELECT conname FROM pg_constraint WHERE conrelid = 'domain_term_dictionary'::regclass AND contype = 'f';` -- expected: `fk_domain_term_source_document`
- `SELECT conname FROM pg_constraint WHERE conrelid = 'data_hierarchical_nodes'::regclass AND contype = 'f';` -- expected: `fk_hierarchical_nodes_document`