Draft: Database changes for adding oauth to credential inventory

What does this MR do and why?

Database changes for adding oauth to credential inventory

Database changes to add oauth tokens to the crendeitla inventory so that instance admin and group owners get better visibility into the oauth access tokens used by the team.

SQL expires_in

SELECT "oauth_access_tokens".* FROM "oauth_access_tokens" WHERE "oauth_access_tokens"."expires_in" IS NOT NULL AND (oauth_access_tokens.created_at + oauth_access_tokens.expires_in * INTERVAL '1 second' > '2025-01-01') LIMIT 20 OFFSET 0

Query plan:

Time: 24.116 ms
  - planning: 1.669 ms
  - execution: 22.447 ms
    - I/O read: 6.999 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 402 (~3.10 MiB) from the buffer pool
  - reads: 415 (~3.20 MiB) from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

Link: https://console.postgres.ai/gitlab/gitlab-production-main/sessions/40360/commands/124229

Expires before

SELECT "oauth_access_tokens".* FROM "oauth_access_tokens" WHERE "oauth_access_tokens"."expires_in" IS NOT NULL AND (oauth_access_tokens.created_at + oauth_access_tokens.expires_in * INTERVAL '1 second' < '2026-01-01') LIMIT 20 OFFSET 0

Query plan:

  
Time: 1.907 ms  
  - planning: 1.825 ms  
  - execution: 0.082 ms  
    - I/O read: 0.000 ms  
    - I/O write: 0.000 ms  
  
Shared buffers:  
  - hits: 1 (~8.00 KiB) from the buffer pool  
  - reads: 0 from the OS file cache, including disk I/O  
  - dirtied: 0  
  - writes: 0  

Link: https://postgres.ai/console/gitlab/gitlab-production-main/sessions/40360/commands/124230

Revoked

explain SELECT "oauth_access_tokens".* FROM "oauth_access_tokens" WHERE "oauth_access_tokens"."revoked_at" IS NOT NULL LIMIT 20 OFFSET 0

Query plan:

Time: 1.893 ms  
  - planning: 1.808 ms  
  - execution: 0.085 ms  
    - I/O read: 0.000 ms  
    - I/O write: 0.000 ms  
  
Shared buffers:  
  - hits: 1 (~8.00 KiB) from the buffer pool  
  - reads: 0 from the OS file cache, including disk I/O  
  - dirtied: 0  
  - writes: 0  

Link: https://postgres.ai/console/gitlab/gitlab-production-main/sessions/40360/commands/124231

Inactive

SELECT "oauth_access_tokens".* FROM "oauth_access_tokens" WHERE ("oauth_access_tokens"."revoked_at" IS NOT NULL OR "oauth_access_tokens"."expires_in" IS NOT NULL AND (oauth_access_tokens.created_at + oauth_access_tokens.expires_in *
          INTERVAL '1 second' < '2025-06-11 11:20:52.871539')) LIMIT 20 OFFSET 0

Query plan

  
Time: 2.699 ms  
  - planning: 1.467 ms  
  - execution: 1.232 ms  
    - I/O read: 1.106 ms  
    - I/O write: 0.000 ms  
  
Shared buffers:  
  - hits: 0 from the buffer pool  
  - reads: 1 (~8.00 KiB) from the OS file cache, including disk I/O  
  - dirtied: 0  
  - writes: 0 

Link: https://postgres.ai/console/gitlab/gitlab-production-main/sessions/40360/commands/124233

By users

SELECT "oauth_access_tokens".* FROM "oauth_access_tokens" INNER JOIN "users" ON "users"."id" = "oauth_access_tokens"."resource_owner_id" INNER JOIN "user_details" ON "users"."id" = "user_details"."user_id" WHERE "user_details"."enterprise_group_id" = 4249178 LIMIT 20 OFFSET 0

Query plan

Time: 16.184 ms
  - planning: 15.006 ms
  - execution: 1.178 ms
    - I/O read: 0.838 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 21 (~168.00 KiB) from the buffer pool
  - reads: 36 (~288.00 KiB) from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0 
  

Link: https://console.postgres.ai/gitlab/gitlab-production-main/sessions/40457/commands/124553

References

• Add OAuth tokens to credentials inventory (!192829) MR with user facing changes
• Related to #526129

Screenshots or screen recordings

Before	After

How to set up and validate locally

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.