Add DependencyFirewallPreventedPackage model and dashboard_v2 flag

Arpit Gogiarequested to merge
627538-df-dashboard-stat-part-1-model into master

What does this MR do and why?

The table MR (!253941 (merged)) has merged, adding the migrations, the table dictionary, the retention declaration, and a foreign-key grandfathering entry. This MR now targets master directly — it is no longer stacked on anything, and nothing needs to merge before it.

This is the application-code layer for Part 1 of a 5-part series on work item https://gitlab.com/gitlab-org/gitlab/-/work_items/627538. Parts 2 and 3 build on this work; Parts 4 and 5 stack on those. Nothing writes to the table in production yet — the write path is Part 2, gated by the dependency_firewall_dashboard_v2 flag.

It adds:

  • Security::DependencyFirewallPreventedPackage, the model backing the prevented-packages ledger. It carries scopes for_rule_types, blocked_since, warned_only_since, and for_projects, a bulk_upsert! class method wrapping upsert_all, and a severity_only_for_vulnerability_rules validation that mirrors the database CHECK constraint.
  • Security::DependencyFirewallPolicyRule.severity_carrying_types, a single place that decides which rule types carry a severity, so the model layer no longer re-encodes the migration's rule_type IN (1, 4) in a second location.
  • The dependency_firewall_dashboard_v2 feature flag (wip, default off, per root namespace), plus Security::DependencyFirewall::Availability.dashboard_v2_enabled?.
  • A change to the development seed fixture, which already exists on master and inserted rows through an anonymous ApplicationRecord subclass because the model did not exist yet. It now uses Security::DependencyFirewallPreventedPackage.bulk_upsert!, so development and upgrade-testing seeds exercise the real LEAST/GREATEST merge semantics rather than a plain insert. Verified to produce the same rows as before.
  • scripts/lint/keela_excluded.yml entries for the scopes and dashboard_v2_enabled?, which have no callers until Parts 2 and 5 land.
  • Factories and specs for all of the above.

Some design points worth calling out:

  • The ledger holds one row per (project, rule_type, purl). bulk_upsert! merges rather than overwrites: LEAST on first_seen_at, GREATEST on last_blocked_at, last_warned_at, and severity, so replays and out-of-order flushes can only advance a row, never regress it. The merge clause lives in a frozen ON_DUPLICATE_MERGE constant.
  • bulk_upsert! requires rows pre-folded by unique key before being passed in. PostgreSQL raises "cannot affect row a second time" if a single upsert statement carries duplicate conflict targets.
  • for_projects accepts either an ID list or an ActiveRecord relation. The relation branch becomes a correlated EXISTS via where_exists, so a group's all_projects hierarchy is resolved inside PostgreSQL rather than serialized into the query.
  • Dashboard windows always end at "now", so last_blocked_at >= from answers "was this package prevented in the window". Warned-only means warned in the window but never blocked in that same window.
  • dependency_firewall_dashboard_v2 gates both the ledger write path (Part 2) and the dashboard's GraphQL stats (Part 5). Nothing writes to the table until it is enabled.
  • bulk_upsert! calls upsert_all, which bypasses ActiveRecord validations and callbacks. In practice this means the database CHECK constraints, not the model validation, are what actually guard real writes — the model validation only protects code paths that go through save/create. This is a known property and is under discussion in review.

How to set up and validate locally

  1. Make sure the table migrations are applied: bin/rails db:migrate.
  2. Open bin/rails console.
  3. Create a row: create(:dependency_firewall_prevented_package).
  4. Call Security::DependencyFirewallPreventedPackage.bulk_upsert! twice with overlapping rows and confirm first_seen_at, last_blocked_at, and last_warned_at merge as expected rather than being overwritten.
  5. Confirm the database CHECK constraint rejects a license-family rule_type combined with a non-nil severity.
  6. Run bin/rake db:seed_fu FILTER=dependency_firewall_prevented_packages to exercise the seed fixture.

Database review

The table was added in !253941 (merged); this MR adds the scopes and the upsert that read and write it.

Raw SQL for new scopes and the upsert

Expand 
-- for_rule_types
SELECT "dependency_firewall_prevented_packages".* FROM "dependency_firewall_prevented_packages" WHERE "dependency_firewall_prevented_packages"."rule_type" IN (1, 4)

-- blocked_since
SELECT "dependency_firewall_prevented_packages".* FROM "dependency_firewall_prevented_packages" WHERE "dependency_firewall_prevented_packages"."last_blocked_at" >= '2026-08-09 10:23:46.430762'

-- warned_only_since
SELECT "dependency_firewall_prevented_packages".* FROM "dependency_firewall_prevented_packages" WHERE "dependency_firewall_prevented_packages"."last_warned_at" >= '2026-08-09 10:23:46.431157' AND ("dependency_firewall_prevented_packages"."last_blocked_at" IS NULL OR "dependency_firewall_prevented_packages"."last_blocked_at" < '2026-08-09 10:23:46.431157')

-- for_projects (relation branch, group hierarchy)
SELECT "dependency_firewall_prevented_packages".* FROM "dependency_firewall_prevented_packages" WHERE EXISTS (SELECT 1 FROM "projects" WHERE "projects"."namespace_id" IN (SELECT "namespaces"."id" FROM UNNEST(
  COALESCE(
    (SELECT ids FROM (SELECT "namespace_descendants"."self_and_descendant_group_ids" AS ids FROM "namespace_descendants" WHERE "namespace_descendants"."outdated_at" IS NULL AND "namespace_descendants"."namespace_id" = 92) cached_query),
    (SELECT ids FROM (SELECT ARRAY_AGG("namespaces"."id") AS ids FROM (SELECT namespaces.traversal_ids[array_length(namespaces.traversal_ids, 1)] AS id FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND (traversal_ids @> ('{92}'))) namespaces) consistent_query))
) AS namespaces(id)
) AND "projects"."id" = "dependency_firewall_prevented_packages"."project_id")

-- for_projects (id-list branch, single project)
SELECT "dependency_firewall_prevented_packages".* FROM "dependency_firewall_prevented_packages" WHERE "dependency_firewall_prevented_packages"."project_id" = 42

-- composed dashboard read (the shape that matters)
SELECT COUNT(DISTINCT identifier) FROM "dependency_firewall_prevented_packages" WHERE EXISTS (SELECT 1 FROM "projects" WHERE "projects"."namespace_id" IN (SELECT "namespaces"."id" FROM UNNEST(
  COALESCE(
    (SELECT ids FROM (SELECT "namespace_descendants"."self_and_descendant_group_ids" AS ids FROM "namespace_descendants" WHERE "namespace_descendants"."outdated_at" IS NULL AND "namespace_descendants"."namespace_id" = 92) cached_query),
    (SELECT ids FROM (SELECT ARRAY_AGG("namespaces"."id") AS ids FROM (SELECT namespaces.traversal_ids[array_length(namespaces.traversal_ids, 1)] AS id FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND (traversal_ids @> ('{92}'))) namespaces) consistent_query))
) AS namespaces(id)
) AND "projects"."id" = "dependency_firewall_prevented_packages"."project_id") AND "dependency_firewall_prevented_packages"."rule_type" IN (1, 4) AND "dependency_firewall_prevented_packages"."last_blocked_at" >= '2026-08-09 10:23:46.464611'

-- bulk_upsert! (hand-written; Rails does not to_sql upsert_all)
INSERT INTO dependency_firewall_prevented_packages
  (project_id, rule_type, identifier, first_seen_at, last_blocked_at, last_warned_at, severity, created_at, updated_at)
VALUES (...), (...)
ON CONFLICT (project_id, rule_type, identifier)
DO UPDATE SET
  first_seen_at = LEAST(dependency_firewall_prevented_packages.first_seen_at, excluded.first_seen_at),
  last_blocked_at = GREATEST(dependency_firewall_prevented_packages.last_blocked_at, excluded.last_blocked_at),
  last_warned_at = GREATEST(dependency_firewall_prevented_packages.last_warned_at, excluded.last_warned_at),
  severity = GREATEST(dependency_firewall_prevented_packages.severity, excluded.severity),
  updated_at = now()

Query plans

Measurements come from a Database Lab clone of GitLab.com, seeded with 4,686,200 rows over 208,924 projects (300 packages per project, assumed). The clone has since expired, so these are the recorded numbers; the raw EXPLAIN output can't be re-pulled. All three indexes are exercised by their intended query, and project-level reads are fast: 8.9 ms.

The group-level read does not meet the 100 ms guideline under any index configuration we tried. The best we measured was 541 ms, and the cause is volume, not indexing: the query scans and sorts around 309,000 rows to return one count. We're keeping the shipped index configuration (A) rather than switching to the covering index that gets closer (C), because group-level reads for this feature are planned to move to ClickHouse in Parts 3 and 4. This tradeoff was raised as an open question on !253941 (merged) but the MR merged before it got an answer, so revisiting it means a follow-up MR.

Expand

Numbers below are medians of 5 runs, in ms, from the gitlab-org subset of the seed (namespace 9970): 2,686,500 rows across 8,951 projects in 1,586 groups. The group-level read uses i_dep_fw_prevented_packages_blocked with an Index Cond on all three columns, via a nested loop seeded from the projects side of the EXISTS subquery (the for_projects relation branch). The upsert uses i_dep_fw_prevented_packages_unique as its conflict arbiter.

Config Timestamp indexes Size Group blocked Group warned-only Upsert 50k
A (shipped) (p,r,blocked) + (p,r,warned) 256 MB 706 ms 316 ms 947 ms
B (merged) (p,r,blocked,warned) 222 MB 731 ms 432 ms 900 ms
C (covering) (p,r,blocked,identifier) + (p,r,warned) 400 MB 541 ms 335 ms 1126 ms
D (both covering) both widened 596 MB 561 ms 478 ms 1177 ms
baseline none 0 1599 ms 1279 ms 992 ms

The planner estimates rows=4 for the group query where 309,019 actually materialize, an underestimate of roughly 77,000x. The plan survives anyway because each nested-loop iteration is individually cheap.

Config B merges the two timestamp indexes into one and costs 37% more on the warned-only read, because that query ranges on both timestamps and last_warned_at stops being a usable index bound once it sits fourth in the composite key.

Config C appends identifier to the blocked index, which turns the group read into an Index Only Scan at 541 ms for +144 MB. That's the floor we found; it doesn't clear 100 ms either. Storage cost across configs works out to roughly 245 bytes per row including indexes.

Caveats: 300 packages per project is an unvalidated assumption, and the seed only produces 300 distinct identifier values, so treat the absolute numbers here as an upper bound. If real usage is much lower, both the covering-index argument and the ClickHouse-migration argument weaken. gitlab-org is also not the worst case: namespace 128823565 has 107,919 descendant groups against gitlab-org's 1,586. "Warm" in Database Lab means fully cached, so these numbers are best-case on the caching side.

Access patterns

Writes are not in the request path: a batched cron flush every 5 minutes (Part 2), so at most 12 batched statements per hour instance-wide. A row is updated when an already-known (project, rule type, purl) is blocked or warned again. Reads are one aggregate query set per dashboard load (Part 5) — human-driven and low frequency. Group-level reads resolve the hierarchy inside PostgreSQL via EXISTS rather than serializing an ID list.

References

🤖 Generated with Claude Code

Edited by Arpit Gogia

Merge request reports