Part of the fine-grained OAuth token sequence (TID #605869, task **T2**). Implements the **consent grant anchor (D1)**: granular scopes attach to a dedicated consent grant row rather than the token row. Design: [proposal 017](https://gitlab.com/gitlab-org/architecture/auth-architecture/design-doc/-/merge_requests/136). ## Scope Two new tables. `oauth_consent_grants` holds what a user currently allows one OAuth application to do, one row per user and application. `oauth_consent_grant_granular_scopes` links it to the existing `granular_scopes` table, mirroring the role that `personal_access_token_granular_scopes` plays for PATs. `oauth_consent_grants` columns: `id`, `user_id`, `application_id` (`oauth_applications`), `status` (smallint enum: `authorized`, `revoked`), `source` (smallint enum: `doorkeeper`, `iam_consent`, `trusted_auto_grant`, `duo_session`), `organization_id` (sharding key), timestamps. Indexes: partial unique on `(user_id, application_id)` for `status = authorized` rows, excluding `source = duo_session`, so there is exactly one active grant per pair. Duo session grants are exempt because every workflow session gets its own row. Revoked rows stay as consent history. `oauth_consent_grant_granular_scopes` columns: `id`, `oauth_consent_grant_id`, `granular_scope_id`, `organization_id` (sharding key). Indexes: unique on `(oauth_consent_grant_id, granular_scope_id)`; index on `granular_scope_id`. Foreign keys added concurrently in a post-migration. Models: `Authz::OauthConsentGrant`, `Authz::OauthConsentGrantGranularScope`. ## Schema classification Both tables are classified **`gitlab_schema: gitlab_main_org`** with **`sharding_key: organization_id: organizations`**, the same as `oauth_access_tokens`, `personal_access_tokens` and the PAT join table. `oauth_access_tokens` already references `users` and `oauth_applications` under that classification, so the grant table follows an existing pattern. The earlier `gitlab_main_user` reasoning only applied to anchoring on `oauth_consents`, which is no longer the plan. | Table | `gitlab_schema` | Sharding key | |---|---|---| | `oauth_access_tokens` | `gitlab_main_org` | `organization_id` | | `granular_scopes` | `gitlab_main_org` | `organization_id` | | `personal_access_token_granular_scopes` | `gitlab_main_org` | `organization_id` | | `oauth_consent_grants` | `gitlab_main_org` | `organization_id` | | `oauth_consent_grant_granular_scopes` | `gitlab_main_org` | `organization_id` | ## Organization ownership `granular_scopes.organization_id` is `NOT NULL`. The proposal answers this: the grant carries the user's organization as `organization_id`, so it lives on the user's cell, and the `granular_scopes` rows written for it use the same organization. Users are co-located with the organizations they act in, so the cell that authorizes a request also holds the grant. Cross-cell grant resolution is an open question in the proposal, not something to solve in this task. ## Orphan-cleanup trigger `delete_orphaned_granular_scopes()` currently fires `AFTER DELETE` on `personal_access_token_granular_scopes` and deletes scope rows that lost their last reference. This task must extend that cleanup: - Add the trigger to `oauth_consent_grant_granular_scopes` - The shared function must check **both** join tables before deleting, so a delete on one join table can never destroy a `granular_scopes` row the other still references - Add a spec proving both directions — this was flagged as the riskiest untested piece of the original PoC (see the !225149 assessment): getting it wrong silently destroys permission data, and no spec exists for the original PAT trigger to copy from ## Out of scope Nothing reads the join table yet. ## Acceptance criteria - [ ] Migrations + `db/docs/oauth_consent_grants.yml` and `db/docs/oauth_consent_grant_granular_scopes.yml` with the classification above - [ ] Cross-schema and sharding-key specs pass - [ ] Models with associations and specs - [ ] Partial unique index proves one active grant per user and application, with `duo_session` rows exempt - [ ] Orphan-cleanup trigger covers the new join table; spec proves a scope referenced by the other join table survives a delete ## Data retention Both `oauth_consent_grants` and `oauth_consent_grant_granular_scopes` are declared with `exclude.reason: indefinite_retention` in `db/docs/data_retention/`. Authorized grants represent live authorization state and need to exist for as long as the user and the application do. Revoked grants are kept as a record of consent history. The retention framework's window is counted per table from row creation, so a `delete_rows` policy would remove live grants along with old ones, which is why both tables stay excluded for now. This will need to be revisited once Duo workflow sessions start writing to these tables. Per design proposal 017 (https://gitlab.com/gitlab-org/architecture/auth-architecture/design-doc/-/merge_requests/136), each Duo workflow session will create its own `duo_session` grant row. These rows are not consent history and would accumulate quickly. That write path is not part of phase 1, and nothing writes to these tables yet. Before the `duo_session` writer ships, we need to decide how those rows get cleaned up: a worker that deletes revoked `duo_session` grants after the session token expires, or a separate table with its own retention policy. This section should be updated once that decision is made.