feat(schema): add the missing foreign keys with orphan quarantine #47

2026-10-14T21:23:49Z

KofTwentyTwo commented

2026-10-14 21:23:49 +00:00

(Migrated from github.com)

Stacked on #46 (branched from feature/schema-indexes; the FK block follows its index audit block). Merge #46 first; this diff then shows only the FK commit.

What

Adds a guarded, re-runnable "Foreign keys" block to docs/schema-postgres.sql, plus an UPGRADE/CHANGELOG note.

Relation	ON DELETE	Why
message / turn / tool_call / feedback / pending_action `.conversation_id`	CASCADE	conversation detail; matches `AssistantStore.deleteConversation` and retention, and makes an admin delete clean up the same way
turn_event.turn_id, tool_call.turn_id, feedback.message_id	CASCADE	owned detail rows
turn.user_message_id / assistant_message_id, message.turn_id, message.parent_message_id	SET NULL	message/turn cycle; deleteConversation deletes messages before turns. Inserts are parent-first, so nothing is deferred
tool_call.pending_action_id, pending_action.turn_id	SET NULL	optional pointers; turns go before pending actions in deleteConversation
usage.conversation_id / turn_id	SET NULL	usage is never deleted with a conversation and keeps the cost rows
data_audit.conversation_id / turn_id	SET NULL	audit trail: never cascaded into (deleteConversation still deletes its rows explicitly)
usage / data_audit / budget_usage `.provider_id`	NO ACTION	like turn.provider_id: disable a provider that has history; don't delete it
profile_skill.profile_id / skill_id	CASCADE	link rows
model.provider_id	NO ACTION to CASCADE (`fk_assistant_model_provider`, as on kof22)	a provider's own model rows

assistant_budget_usage.usage_id gets no FK, on purpose: the projection has to outlive its usage row so budgets keep counting the spend.

Rows that already point at a missing parent go into qqq_fk_quarantine first. Nullable columns are then nulled. NOT NULL link rows are deleted, with the full row recorded. Every FK column now leads an index; the names match the kof22 index changeset.

Tests

SchemaForeignKeyPostgresTest (Testcontainers, same image as the pgvector test) applies the DDL and re-runs the FK block. It checks:

every PVS/join relation, and every code-only one, has an FK or appears in EXEMPT with a reason
the delete rules
every FK column leads an index
deleteConversation's delete order, and a plain conversation delete, both work, and usage and the audit rows are kept

Red without the block (4/4 fail), green with it. ./mvnw verify passes.

🤖 Generated with Claude Code

Stacked on #46 (branched from `feature/schema-indexes`; the FK block follows its index audit block). Merge #46 first; this diff then shows only the FK commit. ## What Adds a guarded, re-runnable "Foreign keys" block to `docs/schema-postgres.sql`, plus an UPGRADE/CHANGELOG note. | Relation | ON DELETE | Why | |---|---|---| | message / turn / tool_call / feedback / pending_action `.conversation_id` | CASCADE | conversation detail; matches `AssistantStore.deleteConversation` and retention, and makes an admin delete clean up the same way | | turn_event.turn_id, tool_call.turn_id, feedback.message_id | CASCADE | owned detail rows | | turn.user_message_id / assistant_message_id, message.turn_id, message.parent_message_id | SET NULL | message/turn cycle; deleteConversation deletes messages before turns. Inserts are parent-first, so nothing is deferred | | tool_call.pending_action_id, pending_action.turn_id | SET NULL | optional pointers; turns go before pending actions in deleteConversation | | usage.conversation_id / turn_id | SET NULL | usage is never deleted with a conversation and keeps the cost rows | | data_audit.conversation_id / turn_id | SET NULL | audit trail: never cascaded into (deleteConversation still deletes its rows explicitly) | | usage / data_audit / budget_usage `.provider_id` | NO ACTION | like turn.provider_id: disable a provider that has history; don't delete it | | profile_skill.profile_id / skill_id | CASCADE | link rows | | model.provider_id | NO ACTION to CASCADE (`fk_assistant_model_provider`, as on kof22) | a provider's own model rows | `assistant_budget_usage.usage_id` gets no FK, on purpose: the projection has to outlive its usage row so budgets keep counting the spend. Rows that already point at a missing parent go into `qqq_fk_quarantine` first. Nullable columns are then nulled. NOT NULL link rows are deleted, with the full row recorded. Every FK column now leads an index; the names match the kof22 index changeset. ## Tests `SchemaForeignKeyPostgresTest` (Testcontainers, same image as the pgvector test) applies the DDL and re-runs the FK block. It checks: - every PVS/join relation, and every code-only one, has an FK or appears in `EXEMPT` with a reason - the delete rules - every FK column leads an index - deleteConversation's delete order, and a plain conversation delete, both work, and usage and the audit rows are kept Red without the block (4/4 fail), green with it. `./mvnw verify` passes. 🤖 Generated with [Claude Code](https://claude.com/claude-code)

Sign in to join this conversation.

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: QRun-IO/qbit-assistant#47