Unregister mobile push subscriptions by device token for any user

What does this MR do and why?

Before: DELETE /user/push_subscriptions removed only the calling user's rows for the device token. A sign-out whose delete did not reach the server (the device was offline) left the row in place for 90 days when another account then used the device with push off, so the previous account's to-dos kept arriving on that device.

After: any signed-in user holding the device token can take its rows down, whichever user registered them. This is the same trust the register endpoint already grants: a token registered by another user is reassigned to the caller, because APNs tokens identify the device, not the account.

What changed

• UnregisterService deletes every row for the token instead of only the caller's, and logs a structured line naming the other users whose rows were removed.
• The API route description says every subscription for the token is removed, with a comment next to the authorization setting on why that is within the existing permission.
• The API doc and the OpenAPI definition say the same and explain the sign-out case in one sentence.
• Specs cover rows registered by another user (both APNs environments) being deleted, rows for that user's other tokens staying, the request-level 204, the existing 404, and the new model scope.

Permission

The route keeps delete_mobile_push_subscription with a user boundary. Deleting a row another user registered is within that permission because the register route already lets a token holder take that row over; taking it down is the smaller action.

Database

UnregisterService#execute runs two statements per request. device_token is encrypted deterministically, so both look the value up through the unique index on (device_token, apns_environment) and touch at most two rows (one per APNs environment). The plans below use a stand-in JSON literal, since real tokens are ciphertext, so they match no row; a hit adds at most two heap pages to the same index scan.

Ids of the other users who registered the token (feeds the log line only):

SELECT "mobile_device_push_subscriptions"."user_id"
FROM "mobile_device_push_subscriptions"
WHERE "mobile_device_push_subscriptions"."device_token" = '<deterministically encrypted token>'
  AND "mobile_device_push_subscriptions"."user_id" != 1
LIMIT 2

Cold cache: https://postgres.ai/console/gitlab/gitlab-production-main/sessions/82386/commands/189718 (Index Scan on idx_mobile_push_subscriptions_on_token_and_environment, 2 shared buffers read, 2.071 ms)

Cold cache plan with execution

 Limit  (cost=0.27..3.29 rows=1 width=8) (actual time=1.418..1.427 rows=0 loops=1)
   Buffers: shared read=2
   I/O Timings: read=1.386 write=0.000
   ->  Index Scan using idx_mobile_push_subscriptions_on_token_and_environment on public.mobile_device_push_subscriptions  (cost=0.27..3.29 rows=1 width=8) (actual time=1.417..1.424 rows=0 loops=1)
         Index Cond: (mobile_device_push_subscriptions.device_token = '{"h": {"at": "YWJjZGVmZ2hpamtsbW5vcA==", "iv": "YWJjZGVmZ2hpamts"}, "p": "dGVzdA=="}'::jsonb)
         Filter: (mobile_device_push_subscriptions.user_id <> 1)
         Buffers: shared read=2
         I/O Timings: read=1.386 write=0.000

Warm cache: https://postgres.ai/console/gitlab/gitlab-production-main/sessions/82386/commands/189720 (Index Scan on idx_mobile_push_subscriptions_on_token_and_environment, 2 shared buffer hits, 0.536 ms)

Warm cache plan with execution

 Limit  (cost=0.27..3.29 rows=1 width=8) (actual time=0.015..0.016 rows=0 loops=1)
   Buffers: shared hit=2
   I/O Timings: read=0.000 write=0.000
   ->  Index Scan using idx_mobile_push_subscriptions_on_token_and_environment on public.mobile_device_push_subscriptions  (cost=0.27..3.29 rows=1 width=8) (actual time=0.014..0.014 rows=0 loops=1)
         Index Cond: (mobile_device_push_subscriptions.device_token = '{"h": {"at": "YWJjZGVmZ2hpamtsbW5vcA==", "iv": "YWJjZGVmZ2hpamts"}, "p": "dGVzdA=="}'::jsonb)
         Filter: (mobile_device_push_subscriptions.user_id <> 1)
         Buffers: shared hit=2
         I/O Timings: read=0.000 write=0.000

Delete of every row for the token (before this MR the same statement carried an additional "user_id" = <caller> condition):

DELETE FROM "mobile_device_push_subscriptions"
WHERE "mobile_device_push_subscriptions"."device_token" = '<deterministically encrypted token>'

Cold cache: https://postgres.ai/console/gitlab/gitlab-production-main/sessions/82386/commands/189722 (Index Scan on idx_mobile_push_subscriptions_on_token_and_environment, 2 shared buffers read, 1.748 ms, after a reset of the clone)

Cold cache plan with execution

 Delete on public.mobile_device_push_subscriptions  (cost=0.27..3.28 rows=0 width=0) (actual time=1.272..1.272 rows=0 loops=1)
   Buffers: shared read=2
   I/O Timings: read=1.254 write=0.000
   ->  Index Scan using idx_mobile_push_subscriptions_on_token_and_environment on public.mobile_device_push_subscriptions  (cost=0.27..3.28 rows=1 width=6) (actual time=1.270..1.270 rows=0 loops=1)
         Index Cond: (mobile_device_push_subscriptions.device_token = '{"h": {"at": "YWJjZGVmZ2hpamtsbW5vcA==", "iv": "YWJjZGVmZ2hpamts"}, "p": "dGVzdA=="}'::jsonb)
         Buffers: shared read=2
         I/O Timings: read=1.254 write=0.000

Warm cache: https://postgres.ai/console/gitlab/gitlab-production-main/sessions/82386/commands/189719 (Index Scan on idx_mobile_push_subscriptions_on_token_and_environment, 2 shared buffer hits, 0.555 ms)

Warm cache plan with execution

 Delete on public.mobile_device_push_subscriptions  (cost=0.27..3.28 rows=0 width=0) (actual time=0.013..0.014 rows=0 loops=1)
   Buffers: shared hit=2
   I/O Timings: read=0.000 write=0.000
   ->  Index Scan using idx_mobile_push_subscriptions_on_token_and_environment on public.mobile_device_push_subscriptions  (cost=0.27..3.28 rows=1 width=6) (actual time=0.012..0.012 rows=0 loops=1)
         Index Cond: (mobile_device_push_subscriptions.device_token = '{"h": {"at": "YWJjZGVmZ2hpamtsbW5vcA==", "iv": "YWJjZGVmZ2hpamts"}, "p": "dGVzdA=="}'::jsonb)
         Buffers: shared hit=2
         I/O Timings: read=0.000 write=0.000

References

• App-side fix in GitLab Mobile: https://gitlab.com/mvanremmerden/gitlab-mobile/-/commit/08717779231546d8a18a3675fc78901a12c7b769 (the sign-out delete is awaited before the OAuth grant is revoked, and a failed delete is retried on that account's next sign-in).

🤖 Generated with Claude Code