Add oauth_consent_grants and oauth_consent_grant_granular_scopes tables

What does this MR do and why?

This merge request adds the schema and models for the consent grant anchor (D1) of the fine-grained OAuth token work. Two new tables store what a user allows an OAuth application to do. oauth_consent_grants holds the grants, with a partial unique index ensuring at most one authorized grant per user and application. Duo workflow session grants are exempt because each session gets its own row, and revoked rows stay as consent history. oauth_consent_grant_granular_scopes is a join table linking grants to granular scopes, mirroring the existing personal_access_token_granular_scopes design.

The trigger that cleans up orphaned granular scopes is rewritten to check both join tables before deleting, so a delete on one join table cannot remove a scope the other still references. A spec covers both directions. Two new models provide ActiveRecord access to the grants and their scopes.

Grants carry the user's organization, so a grant lives on the user's cell together with the granular scopes written for it, as described in the design proposal. Deleting a user removes grants through the User association (dependent: :delete_all), the same approach as project_authorization_reverifications. Deleting an application relies on the foreign key cascade: applications are destroyed from the admin area, group settings and owner cleanup as well as from their destroy service, and RuboCop forbids dependent: options outside the User model. The destroy-service todo list records that this cascade is intended. The trigger fires on cascaded deletes, so granular scopes are cleaned up on every path.

At this point, nothing reads or writes these tables. Follow-up work under https://gitlab.com/gitlab-org/gitlab/-/work_items/605869 covers that integration. This is a schema and models change with no user-facing text or UI, so documentation, feature flags, and i18n do not apply.

Database

Both tables are gitlab_main_org with organization_id as sharding key, the same as oauth_access_tokens, granular_scopes and personal_access_token_granular_scopes. Each table's migration creates the table and then adds its foreign keys with add_concurrent_foreign_key, the same shape as recent tables such as dependency_list_export_upload_states. All migrations are reversible.

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 either a personal access token or a consent grant, has just been deleted, which is the same behaviour the existing trigger already has for personal access tokens. Such a row has no owner left to recover it for; the next consent or token creation writes a fresh scope row. There is no user-facing impact.

Indexes on oauth_consent_grants: partial unique on (user_id, application_id) where status = 0 AND source <> 3 for active grant lookups, plus user_id, application_id and organization_id for the foreign keys and per-user or per-application listings. Indexes on the join table: unique on (oauth_consent_grant_id, granular_scope_id), plus granular_scope_id and organization_id.

Expected growth is bounded by the number of distinct (user, application) pairs that consent to applications requesting granular scopes, plus revoked history rows that are kept rather than deleted. The join table holds a few rows per grant. Both tables are empty on GitLab.com until follow-up work starts writing to them, so there is no production data to base query plans on.

Access patterns: looking up the active grant by (user_id, application_id), listing grants per user and per application, cascading deletes from users and oauth_applications, and organization transfer updates. The only new queries in this MR are in the transfer service: batched UPDATE statements on oauth_consent_grant_granular_scopes filtered by oauth_consent_grant_id IN (grants of the moved users), and on granular_scopes filtered by id IN (scope ids from those join rows). Both follow the existing personal access token path.

One thing to keep in mind for later: deleting an application with very many grants runs the orphan cleanup trigger once per cascaded join row. Nothing writes grants yet, so batching that path can be decided once the writers exist.

References

Closes #608634 (closed)

Epic: #468924

Sequence: https://gitlab.com/gitlab-org/gitlab/-/work_items/605869

Design proposal: https://gitlab.com/gitlab-org/architecture/auth-architecture/design-doc/-/merge_requests/136

Screenshots or screen recordings

No UI change in this MR.

How to set up and validate locally

1. Run bin/rails db:migrate.
2. Run these specs: spec/models/authz/oauth_consent_grant_spec.rb, spec/models/authz/oauth_consent_grant_granular_scope_spec.rb, spec/services/organizations/transfer/users_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 two links to one granular scope (one personal_access_token_granular_scope and one oauth_consent_grant_granular_scope), delete one of them, and confirm the scope still exists.

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