Add Geo replication DB schema for Packages::Debian::ProjectComponentFile

Chad Woolleyrequested to merge
geo-packages_debian_project_component_file-replication-1-db-schema into master

What does this MR do and why?

Create database tables for Geo replication and verification of Debian project component files (Packages::Debian::ProjectComponentFile):

  • State table (packages_debian_project_component_file_states) — tracks verification state on the primary site
  • Registry table (packages_debian_project_component_file_registry) — tracks sync/verification state on secondary Geo sites
  • Database dictionary YAML files for both tables

This follows the established Geo SSF blob replicator pattern used for Packages::Nuget::Symbol and other replicable types.

References

Migration SQL

State table (db/migrate)

CREATE TABLE packages_debian_project_component_file_states (
    id bigint NOT NULL,
    verification_started_at timestamp with time zone,
    verification_retry_at timestamp with time zone,
    verified_at timestamp with time zone,
    packages_debian_project_component_file_id bigint NOT NULL,
    project_id bigint NOT NULL,
    verification_state smallint DEFAULT 0 NOT NULL,
    verification_retry_count smallint DEFAULT 0 NOT NULL,
    verification_checksum bytea,
    verification_failure text,
    CONSTRAINT check_c4ce9c5ee5 CHECK ((char_length(verification_failure) <= 255))
);

Registry table (ee/db/geo/migrate)

CREATE TABLE packages_debian_project_component_file_registry (
    id bigserial NOT NULL,
    packages_debian_project_component_file_id bigint NOT NULL,
    created_at timestamp with time zone NOT NULL,
    last_synced_at timestamp with time zone,
    retry_at timestamp with time zone,
    verified_at timestamp with time zone,
    verification_started_at timestamp with time zone,
    verification_retry_at timestamp with time zone,
    state smallint DEFAULT 0 NOT NULL,
    verification_state smallint DEFAULT 0 NOT NULL,
    retry_count smallint DEFAULT 0 NOT NULL,
    verification_retry_count smallint DEFAULT 0 NOT NULL,
    checksum_mismatch boolean DEFAULT false NOT NULL,
    verification_checksum bytea,
    verification_checksum_mismatched bytea,
    verification_failure text,
    last_sync_failure text
);

Screenshots or screen recordings

Not applicable — database-only changes.

End-to-end acceptance verification

This is the DB-schema-only Task 1 MR; replication behavior is exercised by the stacked Task 2 MR Add Geo replication for Packages::Debian::Proje... (!228959 - merged) • Chad Woolley • 19.1. Both MRs were verified together against the pkg-geo-get Geo test environment (a GitLab Environment Toolkit deployment with a Geo primary + secondary pair on AWS). See the verification process docs.

Latest verification — 2026-04-23, PASS (Task 1 + Task 2 combined; Task 2 @ 3b7d2ac):

  • Omnibus build: job 14047100437 from pipeline 2472582935
  • Migrations applied successfully on primary + secondary tracking DB during GET deploy
  • Loose-FK cleanup mechanism (added to this MR on 2026-04-22 to satisfy informal pre-review feedback) verified end-to-end: hard FK removed from state→parent, replaced with gitlab_loose_foreign_keys.yml entry + track_record_deletions post-migration. Destroying a parent record on primary now correctly cascades cleanup of state + registry rows on both sites via LooseForeignKeys::DeletedRecord + CleanupWorker.
  • Full result artifact: results/228959/2026-04-23-035104-manual-verification-and-loose-fk.md in the pkg-geo-get repo
  • See Add Geo replication for Packages::Debian::Proje... (!228959 - merged) • Chad Woolley • 19.1 description for full verification history (automated counter sync, manual GUI verification, etc.)

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.

Edited by Chad Woolley

Merge request reports