## Summary The `DELETE /api/scim/:version/groups/:group/Users/:id` endpoint is experiencing database timeouts due to excessive audit event creation and lock contention on the `projects` table. Multiple audit events are being generated during a single SCIM user deletion, causing N+1 UPDATE queries that compete for locks on the same project records. ## Error Message ``` PG::QueryCanceled: ERROR: canceling statement due to statement timeout CONTEXT: while updating tuple (2059996,23) in relation "projects" ``` ## Problem Details When a SCIM user is deleted, the following occurs: 1. **Multiple audit events are created** - At least 9 audit events generated within an 8-second window for a single DELETE request 2. **Each audit event triggers a project update** - Each event calls `Event#update_project` → `Event#reset_project_activity` 3. **Lock contention occurs** - Multiple UPDATE queries compete for locks on the same `projects` table rows 4. **Statement timeout** - Queries waiting for locks eventually timeout after 60 seconds ### Evidence from Production Logs **Correlation ID**: `9c7ecda6c4ceb2e1-ATL` **Timestamp**: 2026-02-03 03:22:22 - 03:23:28 UTC #### Lock Contention Log ```json { "message": "process 2574536 still waiting for ShareLock on transaction 154052046 after 5000.069 ms", "detail": "Process holding the lock: 2773843. Wait queue: 2574536.", "endpoint_id": "DELETE /api/scim/:version/groups/:group/Users/:id", "correlation_id": "9c7ecda6c4ceb2e1-ATL", "sql": "UPDATE \"projects\" SET \"updated_at\" = $1, \"last_activity_at\" = $2 WHERE \"projects\".\"id\" = $3 AND (last_activity_at <= $4)" } ``` #### Statement Timeout Log ```json { "message": "canceling statement due to statement timeout", "context": "while updating tuple (2059996,23) in relation \"projects\"", "endpoint_id": "DELETE /api/scim/:version/groups/:group/Users/:id", "correlation_id": "9c7ecda6c4ceb2e1-ATL", "command_tag": "UPDATE", "sql": "UPDATE \"projects\" SET \"updated_at\" = $1, \"last_activity_at\" = $2 WHERE \"projects\".\"id\" = $3 AND (last_activity_at <= $4)" } ``` #### Exception Backtrace ``` ActiveRecord::QueryCanceled: PG::QueryCanceled: ERROR: canceling statement due to statement timeout CONTEXT: while updating tuple (2059996,23) in relation "projects" Backtrace: app/models/event.rb:429:in `reset_project_activity' app/models/event.rb:414:in `update_project' [after_create callback] app/services/event_create_service.rb:112:in `leave_project' app/models/members/project_member.rb:103:in `post_destroy_member_hook' [after_destroy callback on ProjectMember] ``` #### Audit Events Generated At least **138 audit events** were created during the single DELETE request between 03:22:22 and 03:22:30: ## Root Cause Analysis The execution flow appears to be: 1. SCIM DELETE request removes user from group 2. User's ProjectMember records are destroyed 3. Each ProjectMember destruction triggers `post_destroy_member_hook` 4. Each hook calls `EventCreateService#leave_project` 5. Each leave_project event calls `Event#update_project` 6. Each update_project calls `Event#reset_project_activity` 7. Each reset_project_activity executes: `UPDATE projects SET updated_at = ..., last_activity_at = ... WHERE projects.id = ...` **Result**: If a user has N project memberships, we execute N UPDATE queries on the projects table, many of which may target the same project (if the user had multiple roles or inherited memberships), causing lock contention. ## Impact - **API failures**: SCIM DELETE operations return 500 errors - **Performance degradation**: Database lock contention affects other operations - **SCIM sync failures**: Identity providers may fail to deprovision users properly - **Customer impact**: Affects enterprise customers using SCIM for user lifecycle management ## Proposed Solution Consider one or more of the following approaches: 1. **Batch project updates** - Collect all affected project IDs and update them in a single query or batch 2. **Defer project activity updates** - Move project activity updates to a background job 3. **Optimize audit event creation** - Reduce the number of audit events or batch their creation 4. **Use advisory locks** - Prevent multiple processes from updating the same project simultaneously 5. **Debounce project updates** - Only update each project once, even if multiple memberships are removed ## Related Issues - #506829 - Similar issue with `PATCH /api/scim/:version/groups/:group/Users/:id` having 10,000+ db calls (resolved in 17.10) - #541445 - Slow AuthorizedProjectsWorker with SCIM user deletion taking 10.7s - #512990 - SCIM identities causing 500 errors due to timeout (resolved in 17.9) ## Additional Context This issue is similar to #506829 which was resolved for the PATCH endpoint, but the DELETE endpoint appears to have a similar N+1 pattern that needs investigation and optimization.