Add archived column to security_scan_profiles_projects

What does this MR do and why

Adds an archived column to security_scan_profiles_projects, laying the schema groundwork for excluding archived projects from a security scan profile's project count.

The GraphQL projectCount field for a security scan profile currently counts archived projects, inflating the number returned to users. The natural fix is a JOIN against projects.archived, but security_scan_profiles_projects lives in the gitlab_sec database while projects lives in gitlab_main_org, and cross-database JOINs are prohibited. Instead, this denormalizes the effective archived state (project.self_or_ancestors_archived?) onto the join table itself, following the existing pattern used by vulnerability_reads, sbom_occurrences, and analyzer_project_statuses.

This MR only adds the schema. Populating the column via event-driven sync workers, and using it in the project count query, are handled in follow-up MRs.

Migration details

• AddArchivedToSecurityScanProfilesProjects — adds archived boolean DEFAULT false NOT NULL to security_scan_profiles_projects. This table has table_size: small in its DB dictionary, so the column is added as a plain transactional migration.
• AddIndexOnScanProfilesProjectsUnarchived (post-deployment migration) — adds a partial index idx_scan_profiles_projects_on_profile_id_unarchived on security_scan_profile_id WHERE archived = false, created concurrently.

Database

Up migration:

-- 20260919120000 AddArchivedToSecurityScanProfilesProjects
ALTER TABLE security_scan_profiles_projects ADD COLUMN archived boolean DEFAULT false NOT NULL;

-- 20260919120100 AddIndexOnScanProfilesProjectsUnarchived (post-deploy)
CREATE INDEX CONCURRENTLY idx_scan_profiles_projects_on_profile_id_unarchived
  ON security_scan_profiles_projects USING btree (security_scan_profile_id)
  WHERE (archived = false);

Down migration:

-- 20260919120100 AddIndexOnScanProfilesProjectsUnarchived (post-deploy)
DROP INDEX CONCURRENTLY IF EXISTS idx_scan_profiles_projects_on_profile_id_unarchived;

-- 20260919120000 AddArchivedToSecurityScanProfilesProjects
ALTER TABLE security_scan_profiles_projects DROP COLUMN archived;

The column add is metadata-only in PostgreSQL (constant default, no table rewrite). No query plans are included: this MR adds no queries. Plans for the grouped count query will be provided in the MR that introduces that query.

Testing

• Both migrations are reversible via standard down methods.
• Verified locally with rails db:migrate across the main, ci, and sec databases.
• structure.sql regenerated and committed.

Related

Implementation issue: #629984

This is the first of several incremental MRs delivering this change. The feature that consumes this schema lives on branch ccharnolevsky/624191-Add-Project-Count-To-Scan-Profile.

• Database review