Casework's review audit tables are append-only by convention only, with no database-enforced tamper evidence. As of PR #1189, casework_review_history, casework_review_accountability, and casework_review_terminal_events are defined in crates/registry-casework/migrations/0015_unified_reviews.sql (around lines 234, 278, and 292 respectively) with no REVOKE, no row-level security policy, and no BEFORE UPDATE/BEFORE DELETE trigger refusing mutation. Separately, casework_review_accountability has a foreign key only to casework_review_requests(request_id); despite carrying a task_id column, it has no foreign key to casework_review_tasks or to any decisions table.
Failure scenario: any code path with a database connection at sufficient privilege (an operational script, a future migration, a bug in a bulk-repair tool) can silently UPDATE or DELETE a row in these tables, and nothing at the schema level would refuse it or record that it happened, undermining the tables' purpose as an accountability trail for who decided what and when.
Suggested fix:
- Add a role-level
REVOKE UPDATE, DELETE (or row-level security) on these tables for the application role, with an explicit escape hatch for the retention sweep's own erasure path.
- Add the missing
(task_id, request_id) foreign key from casework_review_accountability to casework_review_tasks.
Filed from the review of #1189; not merge-blocking.
Casework's review audit tables are append-only by convention only, with no database-enforced tamper evidence. As of PR #1189,
casework_review_history,casework_review_accountability, andcasework_review_terminal_eventsare defined incrates/registry-casework/migrations/0015_unified_reviews.sql(around lines 234, 278, and 292 respectively) with noREVOKE, no row-level security policy, and noBEFORE UPDATE/BEFORE DELETEtrigger refusing mutation. Separately,casework_review_accountabilityhas a foreign key only tocasework_review_requests(request_id); despite carrying atask_idcolumn, it has no foreign key tocasework_review_tasksor to any decisions table.Failure scenario: any code path with a database connection at sufficient privilege (an operational script, a future migration, a bug in a bulk-repair tool) can silently
UPDATEorDELETEa row in these tables, and nothing at the schema level would refuse it or record that it happened, undermining the tables' purpose as an accountability trail for who decided what and when.Suggested fix:
REVOKE UPDATE, DELETE(or row-level security) on these tables for the application role, with an explicit escape hatch for the retention sweep's own erasure path.(task_id, request_id)foreign key fromcasework_review_accountabilitytocasework_review_tasks.Filed from the review of #1189; not merge-blocking.