Add mobile device push subscription registry

Add mobile device push subscription registry

What does this MR do and why?

Adds the mobile_device_push_subscriptions table and model: the device registry for mobile push notifications. One row per (device_token, apns_environment), owned by a user (FK, cascade delete), with last_seen_at for staleness pruning and a payload_mode privacy option (full / id_only).

Part 2 of 4 in the mobile push notifications stack (stacked on the apnotic gem MR; nothing writes to this table until part 3):

1. apnotic gem — !248021 (merged)
2. → this MR: device push subscription registry
3. Registration REST API (/user/push_subscriptions) — !248023 (merged)
4. To-do dispatch worker + APNs delivery — !248026 (merged)

Database

• New table, gitlab_main_user schema, sharding key user_id.
• Two migrations: the transactional create_table (indexes inline) and a separate disable_ddl_transaction! migration adding the users FK with add_concurrent_foreign_key.
• Unique index on (device_token, apns_environment); indexes on user_id and on last_seen_at (supports the staleness-pruning query in part 4).
• device_token is encrypted at rest (deterministic Active Record encryption on a jsonb column, per the encrypted-attributes section of the migration style guide). Deterministic ciphertext keeps the unique index and token lookups working; plaintext length stays bounded by the model's hex-format validation.
• last_seen_at is NOT NULL DEFAULT NOW(): it is written on every registration and defaults to the row's creation time.
• Text limits on all plaintext text columns; smallint enums for platform / apns_environment / payload_mode.
• Model validations: hex device token (16–200 chars), 20 devices per user — enforced on create and on reassignment (a token moving to a user already at the cap is rejected).
• Device tokens are normalized to lowercase via normalizes (uniqueness is enforced on ciphertext; APNs tokens are opaque hex with client-dependent casing). Normalization also applies to finder values.
• Concurrent registrations of the same token are absorbed by rescuing ActiveRecord::RecordNotUnique and retrying once (hand-rolled because safe_find_or_create_by is rejected by the subtransactions cop).
• The bundle-identifier column is named bundle_identifier (not bundle_id): the schema convention spec requires *_id columns to be foreign keys.
• User association declares dependent: :delete_all (deletion-strategy spec); the FK's ON DELETE CASCADE remains the database-level enforcement.

Migration output

main: == 20260804081500 AddUsersFkToMobileDevicePushSubscriptions: reverting ========
main: == 20260804081500 AddUsersFkToMobileDevicePushSubscriptions: reverted (0.0375s)
ci: == 20260804081500 AddUsersFkToMobileDevicePushSubscriptions: reverting ========
ci: == 20260804081500 AddUsersFkToMobileDevicePushSubscriptions: reverted (0.0784s)
sec: == 20260804081500 AddUsersFkToMobileDevicePushSubscriptions: reverting ========
sec: == 20260804081500 AddUsersFkToMobileDevicePushSubscriptions: reverted (0.0426s)
main: == 20260730120000 CreateMobileDevicePushSubscriptions: reverting ==============
main: == 20260730120000 CreateMobileDevicePushSubscriptions: reverted (0.0262s) =====
ci: == 20260730120000 CreateMobileDevicePushSubscriptions: reverting ==============
ci: == 20260730120000 CreateMobileDevicePushSubscriptions: reverted (0.0306s) =====
sec: == 20260730120000 CreateMobileDevicePushSubscriptions: reverting ==============
sec: == 20260730120000 CreateMobileDevicePushSubscriptions: reverted (0.0301s) =====
main: == 20260730120000 CreateMobileDevicePushSubscriptions: migrating ==============
main: == 20260730120000 CreateMobileDevicePushSubscriptions: migrated (0.0381s) =====
ci: == 20260730120000 CreateMobileDevicePushSubscriptions: migrating ==============
ci: == 20260730120000 CreateMobileDevicePushSubscriptions: migrated (0.0288s) =====
sec: == 20260730120000 CreateMobileDevicePushSubscriptions: migrating ==============
sec: == 20260730120000 CreateMobileDevicePushSubscriptions: migrated (0.0295s) =====
main: == 20260804081500 AddUsersFkToMobileDevicePushSubscriptions: migrating ========
main: == 20260804081500 AddUsersFkToMobileDevicePushSubscriptions: migrated (0.0319s)
ci: == 20260804081500 AddUsersFkToMobileDevicePushSubscriptions: migrating ========
ci: == 20260804081500 AddUsersFkToMobileDevicePushSubscriptions: migrated (0.0310s)
sec: == 20260804081500 AddUsersFkToMobileDevicePushSubscriptions: migrating ========
sec: == 20260804081500 AddUsersFkToMobileDevicePushSubscriptions: migrated (0.0304s)

Queries

The table is created by this MR, so production query plans are not meaningful (empty relation); each query is index-backed by design:

register lookup and with_device_token (unique index idx_mobile_push_subscriptions_on_token_and_environment; deterministic encryption serializes the lookup value to stable ciphertext):

SELECT "mobile_device_push_subscriptions".* FROM "mobile_device_push_subscriptions"
WHERE "mobile_device_push_subscriptions"."device_token" = '{"p":"<ciphertext>","h":{"iv":"<iv>","at":"<tag>","i":"ZGM4Mw=="}}'
  AND "mobile_device_push_subscriptions"."apns_environment" = 0 LIMIT 1

stale (daily prune worker, batched over the primary key by each_batch; supported by index_mobile_device_push_subscriptions_on_last_seen_at):

SELECT "mobile_device_push_subscriptions".* FROM "mobile_device_push_subscriptions"
WHERE "mobile_device_push_subscriptions"."last_seen_at" < '2026-05-06 10:43:21'

MR acceptance checklist

• Database review (new table + db/docs dictionary).

🤖 Generated with Claude Code