Oracle Content Feedback FK Cleanup

Oracle ADB — content_feedback FK cleanup

This runbook describes a one-shot, operator-run DDL change for production Oracle Autonomous Database instances. It is optional and non-urgent: the application is functionally correct without it. The task only matters for cleanliness and to avoid surprising future code that deletes rows from threat_models outside the standard cleanup path.

If you only care about the short version: drop the ON DELETE CASCADE foreign key on content_feedback.threat_model_id and re-add it without cascade. PostgreSQL operators don't need to do anything — schema is GORM-managed and freshly migrated databases already lack the cascade. This page is Oracle-specific.

Why this exists

In TMI 1.4.x, #378 aligned ContentFeedback with the project's "explicit cleanup" convention: every threat-model child row is now deleted by the application's deleteThreatModelChildren routine before the parent row is removed. Previously, ContentFeedback was the only TM-child relying on a DB-level ON DELETE CASCADE foreign key.

The GORM model tag was updated to drop the cascade. Newly-created databases never get the cascade FK. GORM AutoMigrate, however, does not drop existing FK constraints, so production Oracle ADB still has the old ON DELETE CASCADE constraint on content_feedback.threat_model_id. The cascade is now dead weight: the application's explicit cleanup deletes the rows first, so by the time the FK fires there's nothing to cascade.

The minor reason to clean it up anyway: the lingering cascade FK still imposes Oracle's child-row locking semantics on any future code path that deletes a threat_models row outside deleteThreatModelChildren. There are no such paths today, but a future contributor could add one and be surprised by the locking behaviour.

When to run this

• You are operating a production TMI deployment on Oracle ADB.
• You have already deployed TMI at or after version 1.4.x (the version that ships #378).
• You have a maintenance window or low-traffic period; the DDL takes milliseconds but obtains a brief table-level lock.

If your deployment is on PostgreSQL only, stop here — this page does not apply.

Prerequisites

• Oracle SQL*Plus, SQLcl, or the Oracle Database Actions UI.
• A database user with ALTER privileges on the content_feedback table (typically the TMI application user that owns the schema).
• A current backup or a recent automated snapshot. The DDL is reversible (the inverse is provided below) but a backup is cheap insurance.

Step 1 — Find the constraint name

GORM names FK constraints from a template; the actual name on your database is generated, not hardcoded. Confirm it before you DROP.

SELECT constraint_name, delete_rule
  FROM user_constraints
 WHERE table_name = 'CONTENT_FEEDBACK'
   AND constraint_type = 'R';

Example output:

CONSTRAINT_NAME                          DELETE_RULE
---------------------------------------- -----------
FK_CONTENT_FEEDBACK_THREAT_MODEL         CASCADE

If DELETE_RULE is already NO ACTION, your database has already been cleaned up — nothing to do.

If the query returns multiple rows, identify the FK that targets threat_models by inspecting the column it covers:

SELECT c.constraint_name, c.delete_rule, cc.column_name, c.r_constraint_name
  FROM user_constraints c
  JOIN user_cons_columns cc
    ON cc.constraint_name = c.constraint_name
 WHERE c.table_name = 'CONTENT_FEEDBACK'
   AND c.constraint_type = 'R';

The row with column_name = 'THREAT_MODEL_ID' and delete_rule = 'CASCADE' is the one to replace.

Step 2 — Drop and recreate without cascade

Substitute the constraint name from Step 1 for <name> below. Run both statements in the same session (a COMMIT after Oracle DDL is implicit, so they don't strictly need to be in a transaction, but you want them adjacent in case anything goes wrong).

ALTER TABLE content_feedback DROP CONSTRAINT <name>;

ALTER TABLE content_feedback
  ADD CONSTRAINT fk_content_feedback_threat_model
  FOREIGN KEY (threat_model_id) REFERENCES threat_models(id);

The new constraint name (fk_content_feedback_threat_model) is consistent with GORM's naming convention for fresh databases, but Oracle is case-insensitive for unquoted identifiers — pick whatever name your operations team prefers.

Step 3 — Verify

Re-run the Step 1 query:

SELECT constraint_name, delete_rule
  FROM user_constraints
 WHERE table_name = 'CONTENT_FEEDBACK'
   AND constraint_type = 'R';

Expected:

CONSTRAINT_NAME                          DELETE_RULE
---------------------------------------- -----------
FK_CONTENT_FEEDBACK_THREAT_MODEL         NO ACTION

NO ACTION is Oracle's representation of "no cascade." You're done.

Reverting

If you need to put the cascade back (you shouldn't, but just in case):

ALTER TABLE content_feedback DROP CONSTRAINT fk_content_feedback_threat_model;

ALTER TABLE content_feedback
  ADD CONSTRAINT fk_content_feedback_threat_model
  FOREIGN KEY (threat_model_id) REFERENCES threat_models(id) ON DELETE CASCADE;

The application is correct in either configuration; the explicit cleanup runs first regardless.

Why this isn't automated

TMI's schema is managed by GORM AutoMigrate. AutoMigrate adds new columns, indexes, and constraints, but it deliberately does not drop or alter existing FK constraints — to avoid silent destructive changes in production.

Adding a SQL migration tool just to handle this single DDL would be more disruptive than the manual one-shot. If TMI ever adopts a dedicated migration framework (golang-migrate, Atlas, etc.), this DDL is a candidate for the first migration set; until then, this page is the canonical place to look.

References

• Originating issue: #390.
• Application change that made this cleanup possible: #378, commit b5c14add.
• Source of truth for the application-side cascade behaviour: auth/repository/deletion_repository.go::deleteThreatModelChildren.
• Linked reference: Feedback APIs (Usability + Content) → "Retention" section.