Create dependency_firewall_prevented_packages table

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

What does this MR do and why?

Part 1 of 5 for gitlab-org/gitlab#627538. It adds the dependency_firewall_prevented_packages table, which will hold one row per (project, rule type, package purl), recording when the Dependency Firewall last blocked or warned on a given package. Nothing writes to this table yet — Parts 2 and 3 branch off this work, and Parts 4-5 stack on those.

Columns: project_id, rule_type (smallint), severity (smallint, nullable), identifier (text, limit 512 — holds a full purl like pkg:type/name@version; the limit matches pm_malware_affected_packages), first_seen_at (not null), last_blocked_at (nullable), last_warned_at (nullable), created_at, updated_at.

Indexes:

  • unique i_dep_fw_prevented_packages_unique on (project_id, rule_type, identifier)
  • i_dep_fw_prevented_packages_blocked on (project_id, rule_type, last_blocked_at)
  • i_dep_fw_prevented_packages_warned on (project_id, rule_type, last_warned_at)

CHECK constraints:

  • check_dep_fw_prevented_packages_severity_rule_type: severity IS NULL OR rule_type IN (1, 4) — severity is only meaningful for vulnerability-family rules.
  • check_dep_fw_prevented_packages_timestamps: num_nonnulls(last_blocked_at, last_warned_at) >= 1 — a row with neither timestamp would be invisible to both read scopes while still occupying the unique ledger slot.

The design assumes dashboard windows always end at "now", so last_blocked_at >= from answers "was this package prevented in the window" with an index read instead of a scan over enforcement events. Warned-only means warned in the window but never blocked in that same window.

The FK to projects is added in a separate migration, concurrently, with ON DELETE CASCADE.

Two bookkeeping files come with the table:

  • db/docs/data_retention/dependency_firewall_prevented_packages.yml: retention_window: 90, enforcement_strategy: delete_rows, enforcing: false, pause_mechanism: none. Required for every new table in an eligible schema by the Data Retention Policy Framework. delete_rows rather than drop_partition because the table is upserted in place — the unique index is the upsert conflict target and the statement advances the timestamp columns, so no time column can serve as a partition key. The table is narrow, low-traffic, with three secondary indexes. enforcing: false until a cleanup worker lands; justification is recorded on the work item.
  • spec/support/database/destroy_service_foreign_keys_todo.yml: grandfathers dependency_firewall_prevented_packages.project_id under projects:. A guard spec requires every new FK into a destroy-service table to be either handled at application level or declared cascade-only. Cascade-only is correct here: there is no object storage, counters, or audit events for Projects::DestroyService to clean up, and the table's ClickHouse mirror collapses the cascade delete via _siphon_deleted.

The model, its scopes, and the dependency_firewall_dashboard_v2 feature flag follow in a chained MR off this branch.

How to set up and validate locally

  1. bin/rails db:migrate
  2. Inspect the table and its constraints in psql
  3. Run bin/rails db:rollback STEP=2 and confirm both migrations reverse cleanly
Migration output (main database) 
main: == 20260907130002 AddDependencyFirewallPreventedPackagesProjectFk: reverting ==
main: == 20260907130002 AddDependencyFirewallPreventedPackagesProjectFk: reverted (0.0556s) 
main: == 20260907130001 CreateDependencyFirewallPreventedPackages: reverting ========
main: == 20260907130001 CreateDependencyFirewallPreventedPackages: reverted (0.0388s) 
main: == 20260907130001 CreateDependencyFirewallPreventedPackages: migrating ========
main: == 20260907130001 CreateDependencyFirewallPreventedPackages: migrated (0.0480s) 
main: == 20260907130002 AddDependencyFirewallPreventedPackagesProjectFk: migrating ==
main: == 20260907130002 AddDependencyFirewallPreventedPackagesProjectFk: migrated (0.0552s)

Database review

Plans measured on a Database Lab clone of gitlab.com: 4,686,200 rows over 208,924 projects, with gitlab-org (namespace 9970) at 2,686,500 rows across 8,951 projects in 1,586 groups, assuming 300 packages per project. Medians of 5 runs.

All three indexes are exercised by their intended query. The group-level read uses i_dep_fw_prevented_packages_blocked with Index Cond on all three columns, via a nested loop seeded from the projects side of the EXISTS. Project-level reads are 8.9 ms. 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 (shipping) (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
  1. Two timestamp indexes rather than one merged index: the warned-only query ranges on both timestamps, so last_warned_at stops being an index bound when it sits fourth (B costs 37% there to save 34 MB and ~5% on writes). D doesn't become index-only either, because that query also filters last_blocked_at.
  2. Index maintenance is not what dominates the write path: the whole spread is 900-1126 ms per 50k-row upsert, and the no-timestamp-index baseline (992 ms) falls between A and C. Against a 5-minute cron flush that is ~0.3% duty cycle.
  3. C, appending identifier to the blocked index, would make the group read an Index Only Scan at 541 ms for +144 MB. Held pending review, since group reads are slated to move to ClickHouse in Parts 3-4.

No configuration meets the 100 ms guideline; 541 ms is the floor. The residual is volume rather than indexing — 309,019 rows scanned and quicksorted to return one integer, with the planner estimating rows=4.

Caveats: 300 packages per project is an assumption and the seed yields only 300 distinct identifiers, so treat the absolutes as an upper bound; gitlab-org is not the worst case (namespace 128823565 has 107,919 descendant groups against 1,586); Database Lab "warm" means fully cached.

New-table questions

  1. Growth: ~245 bytes per row including indexes, so growth is rows × 245 bytes. Row count is projects using the firewall × distinct packages × rule types triggered — assumption-driven pre-GA.
  2. Reads and writes per hour: writes are not in the request path — a batched cron flush every 5 minutes (Part 2), at most 12 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 set per dashboard load, human-driven.
  3. Availability risk: low. No request-path writes; reads are index-backed aggregates over a deduplicated table. Group reads move to a ClickHouse replica in Parts 3-4, with PostgreSQL as source of truth and the Self-Managed fallback.
  4. Retention: delete_rows via a cron cleanup worker, enforcing: false until it lands.
  5. db:gitlabcom-database-testing reported VALIDATE CONSTRAINT fk_94075635ff at 107.07 ms against the 100 ms guideline.

References

Edited by Arpit Gogia

Merge request reports