## Context & Motivation Administrators currently need direct database access to monitor online garbage collection (GC) health via SQL queries on `gc_blob_review_queue` and `gc_manifest_review_queue` tables, as documented in [online garbage collection monitoring](https://docs.gitlab.com/administration/packages/container_registry_metadata_database/#online-garbage-collection-monitoring). This is especially important post-import when queues can be large and administrators need to verify GC is processing tasks effectively. The focus should be on **actionable metrics** (tasks pending removal, long overdue tasks requiring attention, and high retry counts) rather than raw queue sizes, which aren't reliable health indicators. ## Proposed Solution A new CLI command `registry database gc-stats` that provides a snapshot of GC queue health with: **For both blob and manifest queues:** 1. **Tasks Pending Removal:** * Count of tasks where `review_after < NOW()` * Sample of up to 10 entries (digest, repository path, review_after timestamp) 2. **Long Overdue Tasks:** * Count of tasks where `review_after < NOW() - configured_delay` * Sample of up to 10 entries (digest, repository path, review_after timestamp, time overdue) 3. **High Retry Tasks:** * Count of tasks with \>10 retries * Sample of up to 10 entries (digest, repository path, retry count, review_after timestamp) **Output Formats:** * Human-readable table format (default) with interpretation guidance using tablewriter library * JSON format (via `--format json` flag) for programmatic consumption * Optional `--limit` flag to control sample size (default: 10) * Each section shows count + sample entries with basic guidance ## Implementation Tasks - [x] **CLI command structure with mock data** * Create `registry database gc-stats` command structure using existing command framework * Implement hardcoded sample data demonstrating all output sections (counts + sample entries) * Implement both JSON and human-readable output formats with mock data * Add `--format` flag * Add `--limit` flag for sample size (default: 10) * Use tablewriter library for human-readable table formatting * Include basic guidance/interpretation text in each output section * This allows omnibus integration work to begin in parallel - [x] **Database queries - Tasks pending removal** * Add count query for blobs pending removal (where `review_after < NOW()`) * Add count query for manifests pending removal (where `review_after < NOW()`) * Add sample query for blobs pending removal (limit N, ordered by review_after) * Add sample query for manifests pending removal (limit N, ordered by review_after) * Include unit tests for all queries - [x] **Database queries - Long overdue tasks** * Add count query for blobs overdue by configured delay (where `review_after < NOW() - configured_delay`) * Add count query for manifests overdue by configured delay (where `review_after < NOW() - configured_delay`) * Add sample query for blobs overdue by configured delay (limit N, ordered by review_after) * Add sample query for manifests overdue by configured delay (limit N, ordered by review_after) * Include unit tests for all queries - [x] **Database queries - High retry statistics** * Add count query for blobs with \>10 retries * Add count query for manifests with \>10 retries * Add sample query for blobs with \>10 retries (limit N, ordered by retry count DESC) * Add sample query for manifests with \>10 retries (limit N, ordered by retry count DESC) * Return detailed information: digest, repository path, retry count, review_after timestamp * Include unit tests for all queries - [x] **Wire real data into CLI command** * Replace mock data with actual database queries * Wire up the stats service to the command * Ensure both output formats work with real data * Refine guidance/interpretation text based on real data patterns - [ ] **Documentation** * Update CLI documentation with command usage and examples * Update admin documentation to reference the new command as replacement to SQL queries (aligning with [MR !217417](https://gitlab.com/gitlab-org/gitlab/-/merge_requests/217417) approach) ## Related Work * Reference MR: https://gitlab.com/gitlab-org/gitlab/-/merge_requests/217417 * Omnibus integration target: https://gitlab.com/gitlab-org/omnibus-gitlab/-/blob/master/files/gitlab-ctl-commands/lib/gitlab_ctl/registry/database.rb * Pattern to follow: https://gitlab.com/gitlab-org/omnibus-gitlab/-/blob/master/files/gitlab-ctl-commands/lib/gitlab_ctl/registry/migrate.rb