Multiple packages: data model, migration, hash-dedupe guard, and multi-active regressions

[cdcme]

Related user story

#28108

Task

Let a software title hold many custom packages. Reshape software_installers uniqueness so custom packages dedupe by content hash and FMA version pinning keeps deduping by version. Settle is_active for custom packages, clean up existing duplicate rows, enforce the 10-package limit, flip the read path to first-added, and fix the four regressions that multiple active rows cause.

Background: the model already allows many installers per title. FMA version pinning (#47808, migration 20260218175704_FMAActiveInstallers.go) dropped the UNIQUE (global_or_team_id, title_id) key, added UNIQUE (global_or_team_id, title_id, version), and added is_active. That had a P1 side effect: a second custom-package upload silently succeeds and leaves two rows with is_active = 1, which breaks the edit flow and inflates lists and counts.

The dedupe rule differs by regime, so the unique key must respect both:

• Custom packages dedupe by hash. Architecture isn't modeled (platform is the OS, there's no arch column). An Arm build and an Intel build of one title match on every stored field except their bytes, so storage_id is the only safe discriminator.
• FMA packages dedupe by version. InsertFleetMaintainedAppVersion relies on the version key for idempotency (it catches IsDuplicate and recovers by version), and the auto-update cron caches several rows with the same storage_id across versions (the rollback path). A blanket hash key would break this; a blanket version key blocks custom arch builds.

Migration

Create the migration with make migration name=MultipleCustomPackagesPerTitle. Follow 20260218175704_FMAActiveInstallers.go.

Replace the version key with one regime-aware key. MySQL unique indexes have no WHERE clause, so use a generated column:

• Add dedup_token VARCHAR(255) GENERATED ALWAYS AS (IF(fleet_maintained_app_id IS NULL, storage_id, version)) STORED.
• Add UNIQUE KEY idx_software_installers_dedup (global_or_team_id, title_id, dedup_token) and drop idx_software_installers_team_title_version.

Custom rows resolve the token to storage_id, so they dedupe by hash (Arm and Intel coexist, identical bytes are rejected). FMA rows resolve it to version, so version-uniqueness is unchanged and same-hash-across-versions rows still work. A (team, title) is single-regime (the FMA/VPP guard blocks mixing), and a 64-hex hash never equals a version string, so the token spaces don't collide.

Resolve violating rows before adding the key: duplicate-active rows from the P1 bug, and any custom (team, title, storage_id) duplicates. Keep the first-added row (smallest id) as the survivor. Document the rule in the migration comment. Existing FMA rows already satisfy version-uniqueness.

Add the _test.go: applyUpToPrev, seed single-package, duplicate-active, and FMA same-hash-different-version titles, applyNext, then assert custom hash-duplicates collapse while custom same-version-different-hash and FMA same-hash-different-version rows survive. Time the migration against a large seeded dataset.

Duplicate guard (including Linux)

The DB key is the backstop. Keep checkSoftwareConflictsByIdentifier (server/datastore/mysql/software_installers.go:4111, called from MatchOrCreateSoftwareInstaller:201) on top of it to return a friendly error and to catch FMA/VPP cross-type conflicts the hash key can't express. The guard is identifier-based today and skips Linux. Extend it so a custom upload whose hash matches a package already on the title returns: Couldn't add. <file> package is already added (same SHA-256 hash).

MatchOrCreateSoftwareInstaller (:194) must insert an additional row instead of erroring on a second package, subject to the guard, the unique key, and the limit.

10-package limit

Reject the 11th package on a (team, title). Error copy: Couldn't add. <title> already has 10 packages. Before adding, delete one you no longer use. Keep the existing FMA and VPP "already has" errors.

First-added read and list-all read

Flip GetSoftwareInstallerMetadataByTeamAndTitleID (:1261) from ORDER BY uploaded_at DESC LIMIT 1 to first-added (installer_id ASC). Add a datastore method that returns all packages for a (team, title) with per-package label scope, for the API (#48397) and precedence (#48398). Run make generate-mock after the interface change, then go test ./server/service/.

Fix multiple-active regressions

• ListSoftwareTitles emits one row per active installer (server/service/software_titles.go, query around :614, GROUP BY around :680-689). Collapse to one row per title.
• Installer count and list inflation in the same file (around :845, :910, :950).
• The PATCH edit path fails when SoftwareInstallersCount != 1.

Condition of satisfaction

• Migration: MYSQL_TEST=1 go test ./server/datastore/mysql/... passes. The test asserts the version key is replaced by the dedup_token key, custom hash-duplicates collapse to one first-added active row, custom same-version-different-hash rows survive, and FMA same-hash-different-version rows survive. The migration runs in acceptable time against a large seeded dataset.
• Custom dedupe: a custom upload whose SHA-256 hash matches a package on the title returns a 409 with the copy above. A different build (same version, different hash) is accepted. Linux .deb and .rpm duplicates are rejected.
• FMA unaffected: InsertFleetMaintainedAppVersion idempotency still works, and the cron still caches same-storage_id rows across versions.
• Limit: the 11th package is rejected; 10 succeed.
• Reads: GetSoftwareInstallerMetadataByTeamAndTitleID returns the first-added package, and the list method returns all packages with correct per-package label scope.
• Regressions: a title with two active packages appears once in ListSoftwareTitles, counts are correct, and the PATCH edit path works with more than one package.
• Existing titles: single-package titles behave as before after the migration.
• MYSQL_TEST=1 REDIS_TEST=1 go test ./server/datastore/mysql/... ./server/service/... passes.