Add a summary table for group prevented-package stats

Arpit Gogiarequested to merge
631890-group-stats-1-substrate into master

What does this MR do and why?

Part 1 of 7 for https://gitlab.com/gitlab-org/gitlab/-/work_items/631890.

This MR adds the building blocks for precomputed group-level prevented-package counts. It adds the table dependency_firewall_namespace_prevented_stats, with one row per group and window (7 or 30 days), storing the six dashboard counts, six severity counts and window_start. The table has a unique index on (namespace_id, window_days), a check constraint window_days IN (7, 30), a foreign key to namespaces with ON DELETE CASCADE, and sharding key namespace_id. It lives in the main database (gitlab_main_org), next to the other dependency firewall tables.

It also adds the model Security::DependencyFirewallNamespacePreventedStat with a #to_counts method returning the collector hash shape, plus a factory and a development seed. The feature is gated by a new flag, dependency_firewall_group_prevented_stats (beta, default off), exposed through Availability.group_prevented_stats_enabled?.

Nothing writes to or reads from the table yet; later parts in the stack wire that up. to_counts is excluded from the unused-code linter until part 5, which removes that exclusion once it starts calling it.

Stack

Part MR Targets
1 Summary table and flag !261477 (merged) master
2 Paged group refresh service and refresh job !261478 part 1
3 Affected-group lookup and refresh scheduling on flush and transfers !261479 part 2
4 Daily refresh !261485 part 2
5 Group read path !261481 part 2
6 ClickHouse reads !261486 part 5
7 Group page !261483 part 1

Database

This MR adds a new table in the main database (gitlab_main_org), with at most 2 rows per group, one per window, updated in place on each refresh rather than growing per event. Once the whole stack is in, a group gets rows when it has prevented-package activity or when its dashboard is loaded with the flag on, even if every count is zero. Rows are removed with the group through the ON DELETE CASCADE foreign key. They are derived and have nothing else to clean up, so the foreign key is listed as cascade-only in spec/support/database/destroy_service_foreign_keys_todo.yml. The retention policy is 90 days (delete_rows, not enforced yet). Deleting a group's rows is safe: the next dashboard load for that group schedules a refresh that recreates them.

The counts are stored as integer columns rather than one JSONB column. Their shape is fixed (five dashboard counts and six severity counts, matching the GraphQL fields), and GitLab's schema guidance is to use separate columns instead of serialized data. Columns keep every count typed, NOT NULL and defaulting to 0, keep rows small, and allow plain SQL on any count later. A JSONB hash would need a JSON schema validator with a size limit, and adding a property under that schema takes a multi-step deployment, so it wouldn't make a new stat cheaper than an add_column on a table this small.

Writes will be one upsert of 2 rows per group refresh, introduced in part 2. Reads will be one lookup by the unique index per group dashboard load, introduced in part 5. No queries run against the table in this MR.

Growth and access

Measured on a Database Lab clone of the main database (2026-10-13), where the ledger and the group hierarchy live:

Measurement Result
Groups above projects with prevented-package activity 2 (1 root group), so 4 rows today
Root groups with the dependency firewall enabled 5, with 309 groups under them, so at most 618 rows today
Groups touched by flushes in the busiest hour of the last 30 days 2
Projects with enforcement activity in the last 30 days 7, under 21 groups

Estimates, assuming the number of root groups with the dependency firewall enabled grows to 25, 100 and 500, and each has about 62 groups (today's average):

Now 3 months 6 months 1 year
Rows (worst case, every group has rows) 618 3,100 12,400 62,000
Size, including indexes (about 170 bytes per row) under 0.2 MB about 0.5 MB about 2 MB about 10 MB
Upserts per hour from flushes (today's peak of 2 touched groups scaled with root groups, at most 12 per touched group) up to 24 up to 120 up to 480 up to 2,400
Upserts per day from the daily refresh (one per group with rows) 309 1,550 6,200 31,000

Each upsert writes 2 rows by the unique (namespace_id, window_days) index. The daily refresh runs after 00:05 UTC, at most 5 jobs at a time. Reads are one unique-index lookup per group dashboard load. Rows are updated in place by each refresh and removed by the cascade when the group is deleted.

We don't expect an availability risk for GitLab.com or self-managed: even the 1-year worst case is tens of thousands of small rows, and every read and write goes through the unique index.

How to set up and validate locally

Run the migration up and down to confirm it is reversible. Then run:

bin/rspec ee/spec/models/security/dependency_firewall_namespace_prevented_stat_spec.rb ee/spec/lib/security/dependency_firewall/availability_spec.rb

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.

References

Edited by Arpit Gogia

Merge request reports