Add declared granular scope set to OAuth applications

What does this MR do and why?

Closes #630370 (closed). Stacked on !256787 (merged) and targeting its branch 608635-oauth-granular-token-interface until that merges.

This MR adds the set of granular scopes an OAuth application declares. The consent flow copies that set into a user's grant, and a trusted application can be granted from it without a consent screen. There is no user-facing change, no feature flag, and no docs or i18n change.

Nothing writes the table yet. The declaring write path (owner UI or API with boundary validation) and revoking grants when a declared set changes are follow-ups under W3 in #630327.

The new join table oauth_application_granular_scopes has application_id referencing oauth_applications, granular_scope_id referencing granular_scopes, and organization_id as sharding key, with every foreign key ON DELETE CASCADE. It has a unique index on (application_id, granular_scope_id) and indexes on granular_scope_id and organization_id. The delete_orphaned_granular_scopes() trigger function gains a third NOT EXISTS clause and the new table gets the trigger, so a granular scope referenced by a declaration survives deletes on the other two join tables, and vice versa.

Application changes:

• New model Authz::OauthApplicationGranularScope; Authn::OauthApplication gets the associations and #granular?.
• Organizations::Transfer::UsersService (user-owned applications) and GroupsService (group-owned) move the join rows and their granular scopes before moving the applications.
• Factory trait :granular on :oauth_application and the development fixture db/fixtures/development/43_oauth_application_granular_scopes.rb.
• The table is excluded from data retention as indefinite_retention.

Database

The table is gitlab_main_org with sharding key organization_id, the same as oauth_applications, granular_scopes, personal_access_token_granular_scopes and oauth_consent_grant_granular_scopes. Each foreign key is added with add_concurrent_foreign_key and all migrations are reversible.

The oauth_applications foreign key disables Migration/ForeignKeysToDestroyServiceTables with the same justification as the consent grant tables. Applications are also destroyed outside their destroy service (admin area, group settings, owner cleanup), so the cascade is intended and is recorded in spec/support/database/destroy_service_foreign_keys_todo.yml.

Expected growth is a few rows per application that opts into granular scopes. The table stays empty on GitLab.com until the declaring write path exists, so the plans below come from a seeded Database Lab clone of production (Postgres.ai session). Access patterns are loading an application's declared set through the join (used by #granular? and the consent copy), cascading deletes from oauth_applications, granular_scopes and organizations, and organization transfer updates.

To seed the clone, I created the table from db/structure.sql and filled it as if admins and group owners had registered applications with declared scopes. Dynamic applications are excluded (2.46M of the 2.68M rows, no owner, never declare scopes). All 32 instance applications, 10% of group-owned and 2% of user-owned applications declare scopes, capped at 20 declaring applications per owner, because the largest owners are 2023 spam accounts (one user has 28,014 applications). Each declaring application gets 2 to 4 granular_scopes rows, which gives 16,939 join rows for 5,631 applications (2,301 group-owned, 3,298 user-owned, 32 instance). That is a generous estimate for the next year or two.

The new queries live in the transfer services and are batched by each_batch in the existing update_organization_id_for helper. The group variant filters on owner_type = 'Namespace' AND owner_id IN (:namespace_ids). Every owner has under 1,000 join rows, so each_batch runs one batch per table: a start query, a next-bound query that returns nothing, and the UPDATE with only a lower bound.

#granular?

Application 5777291 is an instance application. The query takes 0.04 ms and 11 buffers.

SELECT 1 AS one FROM granular_scopes INNER JOIN oauth_application_granular_scopes ON granular_scopes.id = oauth_application_granular_scopes.granular_scope_id WHERE oauth_application_granular_scopes.application_id = 5777291 LIMIT 1

Plan

 Limit  (cost=0.71..3.53 rows=1 width=4) (actual time=0.035..0.037 rows=1 loops=1)
   Buffers: shared hit=11
   I/O Timings: read=0.000 write=0.000
   ->  Nested Loop  (cost=0.71..9.16 rows=3 width=4) (actual time=0.034..0.035 rows=1 loops=1)
         Buffers: shared hit=11
         I/O Timings: read=0.000 write=0.000
         ->  Index Only Scan using idx_oauth_application_granular_scopes_on_app_id_scope_id on public.oauth_application_granular_scopes  (cost=0.29..1.84 rows=3 width=8) (actual time=0.028..0.028 rows=1 loops=1)
               Index Cond: (oauth_application_granular_scopes.application_id = 5777291)
               Heap Fetches: 0
               Buffers: shared hit=6
               I/O Timings: read=0.000 write=0.000
         ->  Index Only Scan using granular_scopes_pkey on public.granular_scopes  (cost=0.42..2.44 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=1)
               Index Cond: (granular_scopes.id = oauth_application_granular_scopes.granular_scope_id)
               Heap Fetches: 1
               Buffers: shared hit=5
               I/O Timings: read=0.000 write=0.000

Declared set

The same application, loading all four of its scopes. This takes 0.04 ms and 22 buffers.

SELECT granular_scopes.* FROM granular_scopes INNER JOIN oauth_application_granular_scopes ON granular_scopes.id = oauth_application_granular_scopes.granular_scope_id WHERE oauth_application_granular_scopes.application_id = 5777291

Plan

 Nested Loop  (cost=0.71..12.16 rows=3 width=295) (actual time=0.029..0.036 rows=4 loops=1)
   Buffers: shared hit=22
   I/O Timings: read=0.000 write=0.000
   ->  Index Only Scan using idx_oauth_application_granular_scopes_on_app_id_scope_id on public.oauth_application_granular_scopes  (cost=0.29..1.84 rows=3 width=8) (actual time=0.023..0.024 rows=4 loops=1)
         Index Cond: (oauth_application_granular_scopes.application_id = 5777291)
         Heap Fetches: 0
         Buffers: shared hit=6
         I/O Timings: read=0.000 write=0.000
   ->  Index Scan using granular_scopes_pkey on public.granular_scopes  (cost=0.42..3.44 rows=1 width=295) (actual time=0.002..0.002 rows=1 loops=4)
         Index Cond: (granular_scopes.id = oauth_application_granular_scopes.granular_scope_id)
         Buffers: shared hit=16
         I/O Timings: read=0.000 write=0.000

Transfer of the join table, heavy user

User 14251704 owns 28,014 applications, 20 of which declare scopes. The start query takes 30 ms and 19.5k buffers. The UPDATE takes 16 ms and 3.9k buffers and matches 60 rows.

SELECT oauth_application_granular_scopes.id FROM oauth_application_granular_scopes WHERE oauth_application_granular_scopes.organization_id = 1 AND oauth_application_granular_scopes.application_id IN (SELECT oauth_applications.id FROM oauth_applications WHERE oauth_applications.owner_type = 'User' AND oauth_applications.owner_id IN (14251704) AND oauth_applications.organization_id = 1) ORDER BY oauth_application_granular_scopes.id ASC LIMIT 1

Plan

 Limit  (cost=0.73..1227.14 rows=1 width=8) (actual time=30.080..30.082 rows=1 loops=1)
   Buffers: shared hit=19514
   I/O Timings: read=0.000 write=0.000
   ->  Nested Loop  (cost=0.73..15944.14 rows=13 width=8) (actual time=30.078..30.080 rows=1 loops=1)
         Buffers: shared hit=19514
         I/O Timings: read=0.000 write=0.000
         ->  Index Scan using oauth_application_granular_scopes_pkey on public.oauth_application_granular_scopes  (cost=0.29..875.60 rows=17066 width=16) (actual time=0.020..2.890 rows=14439 loops=1)
               Filter: (oauth_application_granular_scopes.organization_id = 1)
               Buffers: shared hit=275
               I/O Timings: read=0.000 write=0.000
         ->  Memoize  (cost=0.44..2.59 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=14439)
               Cache Key: oauth_application_granular_scopes.application_id
               Cache Mode: logical
               Hits: 9630  Misses: 4809  Evictions: 0  Overflows: 0  Memory Usage: 339kB
               Buffers: shared hit=19239
               I/O Timings: read=0.000 write=0.000
               ->  Index Scan using oauth_applications_pkey on public.oauth_applications  (cost=0.43..2.58 rows=1 width=4) (actual time=0.005..0.005 rows=0 loops=4809)
                     Index Cond: (oauth_applications.id = oauth_application_granular_scopes.application_id)
                     Filter: ((oauth_applications.organization_id = 1) AND ((oauth_applications.owner_type)::text = 'User'::text) AND (oauth_applications.owner_id = 14251704))
                     Rows Removed by Filter: 1
                     Buffers: shared hit=19239
                     I/O Timings: read=0.000 write=0.000

UPDATE oauth_application_granular_scopes SET organization_id = 1000002 WHERE oauth_application_granular_scopes.organization_id = 1 AND oauth_application_granular_scopes.application_id IN (SELECT oauth_applications.id FROM oauth_applications WHERE oauth_applications.owner_type = 'User' AND oauth_applications.owner_id IN (14251704) AND oauth_applications.organization_id = 1) AND oauth_application_granular_scopes.id >= 14439

Plan

 Update on public.oauth_application_granular_scopes  (cost=168.14..3159.48 rows=0 width=0) (actual time=16.348..16.350 rows=0 loops=1)
   Buffers: shared hit=3925 dirtied=7 written=3
   WAL: records=375 fpi=0 bytes=37376
   I/O Timings: read=0.000 write=0.172
   ->  Hash Join  (cost=168.14..3159.48 rows=2 width=20) (actual time=12.212..15.029 rows=60 loops=1)
         Hash Cond: (oauth_applications.id = oauth_application_granular_scopes.application_id)
         Buffers: shared hit=3052
         I/O Timings: read=0.000 write=0.000
         ->  Index Scan using index_oauth_applications_on_owner_id_and_owner_type on public.oauth_applications  (cost=0.43..2976.54 rows=2028 width=10) (actual time=0.049..12.075 rows=28014 loops=1)
               Index Cond: ((oauth_applications.owner_id = 14251704) AND ((oauth_applications.owner_type)::text = 'User'::text))
               Filter: (oauth_applications.organization_id = 1)
               Buffers: shared hit=3021
               I/O Timings: read=0.000 write=0.000
         ->  Hash  (cost=136.21..136.21 rows=2520 width=14) (actual time=0.830..0.831 rows=2492 loops=1)
               Buckets: 4096  Batches: 1  Memory Usage: 149kB
               Buffers: shared hit=31
               I/O Timings: read=0.000 write=0.000
               ->  Index Scan using oauth_application_granular_scopes_pkey on public.oauth_application_granular_scopes  (cost=0.29..136.21 rows=2520 width=14) (actual time=0.043..0.538 rows=2492 loops=1)
                     Index Cond: (oauth_application_granular_scopes.id >= 14439)
                     Filter: (oauth_application_granular_scopes.organization_id = 1)
                     Rows Removed by Filter: 9
                     Buffers: shared hit=31
                     I/O Timings: read=0.000 write=0.000
Trigger RI_ConstraintTrigger_c_3200319692 for constraint oauth_application_granular_scopes_organization_id_fkey: time=1.679 calls=60

Transfer of granular_scopes, heavy user

The start query takes 18 ms and 3.6k buffers, the UPDATE 27 ms and 9.5k buffers. Both hash the owner's 28k applications against the full join table (16,939 rows) to find the 60 matches, and the start query then sorts them. This grows linearly with the join table, which is fine at the expected size.

SELECT granular_scopes.id FROM granular_scopes WHERE granular_scopes.organization_id = 1 AND granular_scopes.id IN (SELECT oauth_application_granular_scopes.granular_scope_id FROM oauth_application_granular_scopes WHERE oauth_application_granular_scopes.application_id IN (SELECT oauth_applications.id FROM oauth_applications WHERE oauth_applications.owner_type = 'User' AND oauth_applications.owner_id IN (14251704) AND oauth_applications.organization_id = 1)) ORDER BY granular_scopes.id ASC LIMIT 1

Plan

 Limit  (cost=3428.21..3428.21 rows=1 width=8) (actual time=18.407..18.410 rows=1 loops=1)
   Buffers: shared hit=3625
   I/O Timings: read=0.000 write=0.000
   ->  Sort  (cost=3428.21..3428.24 rows=13 width=8) (actual time=18.406..18.408 rows=1 loops=1)
         Sort Key: granular_scopes.id
         Sort Method: top-N heapsort  Memory: 25kB
         Buffers: shared hit=3625
         I/O Timings: read=0.000 write=0.000
         ->  Nested Loop  (cost=3408.58..3428.14 rows=13 width=8) (actual time=18.307..18.380 rows=60 loops=1)
               Buffers: shared hit=3622
               I/O Timings: read=0.000 write=0.000
               ->  HashAggregate  (cost=3408.16..3408.29 rows=13 width=8) (actual time=18.284..18.292 rows=60 loops=1)
                     Group Key: oauth_application_granular_scopes.granular_scope_id
                     Batches: 1  Memory Usage: 32kB
                     Buffers: shared hit=3382
                     I/O Timings: read=0.000 write=0.000
                     ->  Hash Join  (cost=3002.18..3408.12 rows=13 width=8) (actual time=16.634..18.268 rows=60 loops=1)
                           Hash Cond: (oauth_application_granular_scopes.application_id = oauth_applications.id)
                           Buffers: shared hit=3382
                           I/O Timings: read=0.000 write=0.000
                           ->  Index Only Scan using idx_oauth_application_granular_scopes_on_app_id_scope_id on public.oauth_application_granular_scopes  (cost=0.29..361.41 rows=17075 width=16) (actual time=0.013..1.930 rows=16939 loops=1)
                                 Heap Fetches: 729
                                 Buffers: shared hit=361
                                 I/O Timings: read=0.000 write=0.000
                           ->  Hash  (cost=2976.54..2976.54 rows=2028 width=4) (actual time=15.189..15.190 rows=28014 loops=1)
                                 Buckets: 32768  Batches: 1  Memory Usage: 1241kB
                                 Buffers: shared hit=3021
                                 I/O Timings: read=0.000 write=0.000
                                 ->  Index Scan using index_oauth_applications_on_owner_id_and_owner_type on public.oauth_applications  (cost=0.43..2976.54 rows=2028 width=4) (actual time=0.053..11.880 rows=28014 loops=1)
                                       Index Cond: ((oauth_applications.owner_id = 14251704) AND ((oauth_applications.owner_type)::text = 'User'::text))
                                       Filter: (oauth_applications.organization_id = 1)
                                       Buffers: shared hit=3021
                                       I/O Timings: read=0.000 write=0.000
               ->  Index Scan using granular_scopes_pkey on public.granular_scopes  (cost=0.42..1.53 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=60)
                     Index Cond: (granular_scopes.id = oauth_application_granular_scopes.granular_scope_id)
                     Filter: (granular_scopes.organization_id = 1)
                     Buffers: shared hit=240
                     I/O Timings: read=0.000 write=0.000

UPDATE granular_scopes SET organization_id = 1000002 WHERE granular_scopes.organization_id = 1 AND granular_scopes.id IN (SELECT oauth_application_granular_scopes.granular_scope_id FROM oauth_application_granular_scopes WHERE oauth_application_granular_scopes.application_id IN (SELECT oauth_applications.id FROM oauth_applications WHERE oauth_applications.owner_type = 'User' AND oauth_applications.owner_id IN (14251704) AND oauth_applications.organization_id = 1)) AND granular_scopes.id >= 1395027

Plan

 Update on public.granular_scopes  (cost=3596.18..3615.79 rows=0 width=0) (actual time=26.645..26.647 rows=0 loops=1)
   Buffers: shared hit=9467 dirtied=2 written=2
   WAL: records=308 fpi=0 bytes=28089
   I/O Timings: read=0.000 write=0.668
   ->  Nested Loop  (cost=3596.18..3615.79 rows=1 width=26) (actual time=24.971..25.077 rows=60 loops=1)
         Buffers: shared hit=8814
         I/O Timings: read=0.000 write=0.000
         ->  HashAggregate  (cost=3595.76..3595.89 rows=13 width=20) (actual time=24.949..24.963 rows=60 loops=1)
               Group Key: oauth_application_granular_scopes.granular_scope_id
               Batches: 1  Memory Usage: 32kB
               Buffers: shared hit=8574
               I/O Timings: read=0.000 write=0.000
               ->  Hash Join  (cost=3002.18..3595.72 rows=13 width=20) (actual time=22.061..24.929 rows=60 loops=1)
                     Hash Cond: (oauth_application_granular_scopes.application_id = oauth_applications.id)
                     Buffers: shared hit=8574
                     I/O Timings: read=0.000 write=0.000
                     ->  Index Scan using idx_oauth_application_granular_scopes_on_app_id_scope_id on public.oauth_application_granular_scopes  (cost=0.29..549.01 rows=17075 width=22) (actual time=0.009..4.709 rows=16939 loops=1)
                           Buffers: shared hit=5553
                           I/O Timings: read=0.000 write=0.000
                     ->  Hash  (cost=2976.54..2976.54 rows=2028 width=10) (actual time=18.563..18.564 rows=28014 loops=1)
                           Buckets: 32768  Batches: 1  Memory Usage: 1460kB
                           Buffers: shared hit=3021
                           I/O Timings: read=0.000 write=0.000
                           ->  Index Scan using index_oauth_applications_on_owner_id_and_owner_type on public.oauth_applications  (cost=0.43..2976.54 rows=2028 width=10) (actual time=0.053..14.149 rows=28014 loops=1)
                                 Index Cond: ((oauth_applications.owner_id = 14251704) AND ((oauth_applications.owner_type)::text = 'User'::text))
                                 Filter: (oauth_applications.organization_id = 1)
                                 Buffers: shared hit=3021
                                 I/O Timings: read=0.000 write=0.000
         ->  Index Scan using granular_scopes_pkey on public.granular_scopes  (cost=0.42..1.53 rows=1 width=14) (actual time=0.002..0.002 rows=1 loops=60)
               Index Cond: ((granular_scopes.id = oauth_application_granular_scopes.granular_scope_id) AND (granular_scopes.id >= 1395027))
               Filter: (granular_scopes.organization_id = 1)
               Buffers: shared hit=240
               I/O Timings: read=0.000 write=0.000
Trigger RI_ConstraintTrigger_c_41686 for constraint fk_rails_1f506e10eb: time=1.346 calls=60

Transfer of granular_scopes, heavy group

Group 109725472 owns 797 applications, 20 of which declare scopes. The start query takes 2.9 ms and the UPDATE 3.9 ms, both under 3,200 buffers. The join table queries for groups are similar and stay under 4.5 ms.

SELECT granular_scopes.id FROM granular_scopes WHERE granular_scopes.organization_id = 1 AND granular_scopes.id IN (SELECT oauth_application_granular_scopes.granular_scope_id FROM oauth_application_granular_scopes WHERE oauth_application_granular_scopes.application_id IN (SELECT oauth_applications.id FROM oauth_applications WHERE oauth_applications.owner_type = 'Namespace' AND oauth_applications.owner_id IN (109725472) AND oauth_applications.organization_id = 1)) ORDER BY granular_scopes.id ASC LIMIT 1

Plan

 Limit  (cost=23.57..23.57 rows=1 width=8) (actual time=2.899..2.901 rows=1 loops=1)
   Buffers: shared hit=2398
   I/O Timings: read=0.000 write=0.000
   ->  Sort  (cost=23.57..23.57 rows=1 width=8) (actual time=2.898..2.899 rows=1 loops=1)
         Sort Key: granular_scopes.id
         Sort Method: top-N heapsort  Memory: 25kB
         Buffers: shared hit=2398
         I/O Timings: read=0.000 write=0.000
         ->  Nested Loop  (cost=22.44..23.56 rows=1 width=8) (actual time=2.760..2.868 rows=60 loops=1)
               Buffers: shared hit=2395
               I/O Timings: read=0.000 write=0.000
               ->  HashAggregate  (cost=22.02..22.03 rows=1 width=8) (actual time=2.733..2.741 rows=60 loops=1)
                     Group Key: oauth_application_granular_scopes.granular_scope_id
                     Batches: 1  Memory Usage: 32kB
                     Buffers: shared hit=2155
                     I/O Timings: read=0.000 write=0.000
                     ->  Nested Loop  (cost=0.72..22.01 rows=1 width=8) (actual time=2.123..2.705 rows=60 loops=1)
                           Buffers: shared hit=2155
                           I/O Timings: read=0.000 write=0.000
                           ->  Index Scan using index_oauth_applications_on_owner_id_and_owner_type on public.oauth_applications  (cost=0.43..10.79 rows=6 width=4) (actual time=0.062..1.868 rows=797 loops=1)
                                 Index Cond: ((oauth_applications.owner_id = 109725472) AND ((oauth_applications.owner_type)::text = 'Namespace'::text))
                                 Filter: (oauth_applications.organization_id = 1)
                                 Buffers: shared hit=437
                                 I/O Timings: read=0.000 write=0.000
                           ->  Index Only Scan using idx_oauth_application_granular_scopes_on_app_id_scope_id on public.oauth_application_granular_scopes  (cost=0.29..1.84 rows=3 width=16) (actual time=0.001..0.001 rows=0 loops=797)
                                 Index Cond: (oauth_application_granular_scopes.application_id = oauth_applications.id)
                                 Heap Fetches: 120
                                 Buffers: shared hit=1718
                                 I/O Timings: read=0.000 write=0.000
               ->  Index Scan using granular_scopes_pkey on public.granular_scopes  (cost=0.42..1.53 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=60)
                     Index Cond: (granular_scopes.id = oauth_application_granular_scopes.granular_scope_id)
                     Filter: (granular_scopes.organization_id = 1)
                     Buffers: shared hit=240
                     I/O Timings: read=0.000 write=0.000

UPDATE granular_scopes SET organization_id = 1000002 WHERE granular_scopes.organization_id = 1 AND granular_scopes.id IN (SELECT oauth_application_granular_scopes.granular_scope_id FROM oauth_application_granular_scopes WHERE oauth_application_granular_scopes.application_id IN (SELECT oauth_applications.id FROM oauth_applications WHERE oauth_applications.owner_type = 'Namespace' AND oauth_applications.owner_id IN (109725472) AND oauth_applications.organization_id = 1)) AND granular_scopes.id >= 1386175

Plan

 Update on public.granular_scopes  (cost=47.93..49.05 rows=0 width=0) (actual time=3.860..3.862 rows=0 loops=1)
   Buffers: shared hit=3137
   WAL: records=302 fpi=0 bytes=24688
   I/O Timings: read=0.000 write=0.000
   ->  Nested Loop  (cost=47.93..49.05 rows=1 width=26) (actual time=2.964..3.097 rows=60 loops=1)
         Buffers: shared hit=2395
         I/O Timings: read=0.000 write=0.000
         ->  HashAggregate  (cost=47.51..47.52 rows=1 width=20) (actual time=2.935..2.947 rows=60 loops=1)
               Group Key: oauth_application_granular_scopes.granular_scope_id
               Batches: 1  Memory Usage: 32kB
               Buffers: shared hit=2155
               I/O Timings: read=0.000 write=0.000
               ->  Nested Loop  (cost=0.72..47.51 rows=1 width=20) (actual time=2.307..2.901 rows=60 loops=1)
                     Buffers: shared hit=2155
                     I/O Timings: read=0.000 write=0.000
                     ->  Index Scan using index_oauth_applications_on_owner_id_and_owner_type on public.oauth_applications  (cost=0.43..10.79 rows=6 width=10) (actual time=0.065..1.978 rows=797 loops=1)
                           Index Cond: ((oauth_applications.owner_id = 109725472) AND ((oauth_applications.owner_type)::text = 'Namespace'::text))
                           Filter: (oauth_applications.organization_id = 1)
                           Buffers: shared hit=437
                           I/O Timings: read=0.000 write=0.000
                     ->  Index Scan using idx_oauth_application_granular_scopes_on_app_id_scope_id on public.oauth_application_granular_scopes  (cost=0.29..6.09 rows=3 width=22) (actual time=0.001..0.001 rows=0 loops=797)
                           Index Cond: (oauth_application_granular_scopes.application_id = oauth_applications.id)
                           Buffers: shared hit=1718
                           I/O Timings: read=0.000 write=0.000
         ->  Index Scan using granular_scopes_pkey on public.granular_scopes  (cost=0.42..1.53 rows=1 width=14) (actual time=0.002..0.002 rows=1 loops=60)
               Index Cond: ((granular_scopes.id = oauth_application_granular_scopes.granular_scope_id) AND (granular_scopes.id >= 1386175))
               Filter: (granular_scopes.organization_id = 1)
               Buffers: shared hit=240
               I/O Timings: read=0.000 write=0.000
Trigger RI_ConstraintTrigger_c_41686 for constraint fk_rails_1f506e10eb: time=1.658 calls=60

Application delete

Deleting instance application 5777291 fires the cascade foreign key trigger on the new table in 0.5 ms. The orphan trigger takes 20 ms over 4 calls. The other foreign key trigger times in the plan belong to existing tables.

DELETE FROM oauth_applications WHERE oauth_applications.id = 5777291

Plan

 Delete on public.oauth_applications  (cost=0.43..3.45 rows=0 width=0) (actual time=10.693..10.694 rows=0 loops=1)
   Buffers: shared hit=27 read=5 dirtied=2
   WAL: records=1 fpi=1 bytes=8075
   I/O Timings: read=10.285 write=0.000
   ->  Index Scan using oauth_applications_pkey on public.oauth_applications  (cost=0.43..3.45 rows=1 width=6) (actual time=0.062..0.065 rows=1 loops=1)
         Index Cond: (oauth_applications.id = 5777291)
         Buffers: shared hit=4
         I/O Timings: read=0.000 write=0.000
Trigger RI_ConstraintTrigger_a_229619324 for constraint fk_56ede6e5ab: time=5.179 calls=1
Trigger RI_ConstraintTrigger_a_2745941558 for constraint fk_cfd903d87d: time=1.448 calls=1
Trigger RI_ConstraintTrigger_a_3200319679 for constraint oauth_application_granular_scopes_application_id_fkey: time=0.542 calls=1
Trigger RI_ConstraintTrigger_a_35888 for constraint fk_05f695565a: time=2.261 calls=1
Trigger RI_ConstraintTrigger_a_36762 for constraint fk_308d5b76fe: time=10.016 calls=1
Trigger RI_ConstraintTrigger_a_37165 for constraint fk_4571bb0ccc: time=11.895 calls=1
Trigger RI_ConstraintTrigger_a_37530 for constraint fk_5d9b886930: time=1.350 calls=1
Trigger RI_ConstraintTrigger_a_40372 for constraint fk_f9867b3540: time=2.889 calls=1
Trigger trigger_delete_orphaned_granular_scopes_for_oauth_apps: time=19.999 calls=4

Typical owners (a user with 3 applications, a group with 2) stay under 1 ms for every transfer query.

Data deletion

Danger flags the trigger migration because the rewritten delete_orphaned_granular_scopes() function contains a DELETE. It is not a data migration: the migration only replaces the function body and adds the trigger to the new, empty join table, so it affects no records itself. At run time the function removes a granular_scopes row only when the last join row pointing at it, from a personal access token, a consent grant or an application declaration, has just been deleted, which is the behaviour the existing trigger already has for the first two. Such a row has no owner left to recover it for; the next declaration, consent or token creation writes a fresh scope row. There is no user-facing impact.

References

• Task: #630370 (closed)
• Technical design: https://gitlab.com/gitlab-org/gitlab/-/work_items/605869
• Parent epic: #468924
• Design proposal 017: https://gitlab.com/gitlab-org/architecture/auth-architecture/design-doc/-/merge_requests/136
• Consent grants: !255384 (merged)
• Token interface on OauthAccessToken, base of this stack: !256787 (merged)
• Follow-up W3: #630327
• Next MR in the sequence: !257024 (closed)

Screenshots or screen recordings

Not applicable. This MR has no UI change.

How to set up and validate locally

1. Run bin/rails db:migrate.
2. Run the specs:
   • spec/models/authz/oauth_application_granular_scope_spec.rb
   • spec/models/authn/oauth_application_spec.rb
   • spec/services/organizations/transfer/users_service_spec.rb
   • spec/services/organizations/transfer/groups_service_spec.rb
   • spec/lib/gitlab/database/foreign_keys_to_destroy_service_tables_spec.rb
   • spec/db/docs_spec.rb
   • spec/db/retention_policy_definitions_spec.rb
3. Optionally, in a Rails console, create an Authz::GranularScope and link it with application.oauth_application_granular_scopes.create!(granular_scope:), which is what create(:oauth_application, :granular) does. Check application.granular?, then delete the link and confirm the scope row is gone.

MR acceptance checklist

Evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.

🤖 Generated with Claude Code