fix: complete user-delete FK lockstep across PG and SQLite schemas #1

2026-06-30T15:31:11-04:00

overseer commented

2026-06-30 15:31:11 -04:00

The prior user-deletion work updated the PG schema and a live-PG migration
but left the canonical schema definitions inconsistent, breaking user
deletion on fresh PG installs and on all SQLite dev installs.

schema.sql: add ON DELETE SET NULL to context_files.updated_by (was the
only user FK missing it; fresh PG installs could not delete an authoring
user).
schema_sqlite.sql: bring five user_id FK columns into lockstep with PG
(drop NOT NULL, add ON DELETE SET NULL): project_context.updated_by,
context_files.updated_by, change_requests.submitted_by,
reviews.reviewer_id, audit_log.user_id.
schema_sqlite.sql: remove the audit_log append-only UPDATE/DELETE triggers.
ON DELETE SET NULL on audit_log.user_id is an UPDATE the trigger aborted,
so deleting any user who had ever logged in failed. This mirrors schema.sql,
which dropped the equivalent PG triggers in fc1a2f5; append-only is enforced
at the application layer (db.py only INSERTs into audit_log).
db.py: user_delete no longer swallows non-FK exceptions on the SQLite path
(Exception masked sqlite3.IntegrityError); only FK violations map to the
soft "user_has_references" response, everything else propagates. PG
rollback-on-any-error (shared-connection cascade fix) is preserved.
db.py: document that SQLite cannot ALTER FK constraints in place; existing
dev DBs must be recreated to pick up these changes.
server.py: the global 409 handler no longer leaks raw psycopg text (index
names, column expressions) to API callers; it is logged instead.
migrate_user_fk_set_null.py: use the column from FKS_TO_FIX directly instead
of re-deriving it from the constraint name.

Co-Authored-By: Claude Opus 4.8 noreply@anthropic.com

The prior user-deletion work updated the PG schema and a live-PG migration but left the canonical schema definitions inconsistent, breaking user deletion on fresh PG installs and on all SQLite dev installs. - schema.sql: add ON DELETE SET NULL to context_files.updated_by (was the only user FK missing it; fresh PG installs could not delete an authoring user). - schema_sqlite.sql: bring five user_id FK columns into lockstep with PG (drop NOT NULL, add ON DELETE SET NULL): project_context.updated_by, context_files.updated_by, change_requests.submitted_by, reviews.reviewer_id, audit_log.user_id. - schema_sqlite.sql: remove the audit_log append-only UPDATE/DELETE triggers. ON DELETE SET NULL on audit_log.user_id is an UPDATE the trigger aborted, so deleting any user who had ever logged in failed. This mirrors schema.sql, which dropped the equivalent PG triggers in fc1a2f5; append-only is enforced at the application layer (db.py only INSERTs into audit_log). - db.py: user_delete no longer swallows non-FK exceptions on the SQLite path (Exception masked sqlite3.IntegrityError); only FK violations map to the soft "user_has_references" response, everything else propagates. PG rollback-on-any-error (shared-connection cascade fix) is preserved. - db.py: document that SQLite cannot ALTER FK constraints in place; existing dev DBs must be recreated to pick up these changes. - server.py: the global 409 handler no longer leaks raw psycopg text (index names, column expressions) to API callers; it is logged instead. - migrate_user_fk_set_null.py: use the column from FKS_TO_FIX directly instead of re-deriving it from the constraint name. Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>

overseer added 1 commit 2026-06-30 15:31:12 -04:00

fix: complete user-delete FK lockstep across PG and SQLite schemas 8395df0f80

The prior user-deletion work updated the PG schema and a live-PG migration
but left the canonical schema definitions inconsistent, breaking user
deletion on fresh PG installs and on all SQLite dev installs.

- schema.sql: add ON DELETE SET NULL to context_files.updated_by (was the
  only user FK missing it; fresh PG installs could not delete an authoring
  user).
- schema_sqlite.sql: bring five user_id FK columns into lockstep with PG
  (drop NOT NULL, add ON DELETE SET NULL): project_context.updated_by,
  context_files.updated_by, change_requests.submitted_by,
  reviews.reviewer_id, audit_log.user_id.
- schema_sqlite.sql: remove the audit_log append-only UPDATE/DELETE triggers.
  ON DELETE SET NULL on audit_log.user_id is an UPDATE the trigger aborted,
  so deleting any user who had ever logged in failed. This mirrors schema.sql,
  which dropped the equivalent PG triggers in fc1a2f5; append-only is enforced
  at the application layer (db.py only INSERTs into audit_log).
- db.py: user_delete no longer swallows non-FK exceptions on the SQLite path
  (Exception masked sqlite3.IntegrityError); only FK violations map to the
  soft "user_has_references" response, everything else propagates. PG
  rollback-on-any-error (shared-connection cascade fix) is preserved.
- db.py: document that SQLite cannot ALTER FK constraints in place; existing
  dev DBs must be recreated to pick up these changes.
- server.py: the global 409 handler no longer leaks raw psycopg text (index
  names, column expressions) to API callers; it is logged instead.
- migrate_user_fk_set_null.py: use the column from FKS_TO_FIX directly instead
  of re-deriving it from the constraint name.

Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>

overseer merged commit 295693f641 into main

2026-06-30 15:31:16 -04:00

overseer deleted branch fix/user-delete-fk-schema-lockstep

2026-06-30 15:31:22 -04:00

Sign in to join this conversation.

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: CubeCraftLabs/CTXD#1