record_formats data cleanup: typo dedup, orphan backfill, prices FK reconciliation

[holden]

Background

While implementing #370 Phase 2 (cascading Format sub-facet), the originally-planned data cleanup migration was abandoned because the scope turned out to be larger and more delicate than expected. This issue tracks the remaining record_formats cleanup work as a standalone, low-priority task to do later when we have time to do it carefully.

The Phase 2 cascading sub-facet UI is being built without any data changes — the existing INNER JOIN already hides formats with no records. The cleanup below is a separate concern.

What's wrong with the data today

record_formats has 57 rows but only 24 are actually used by records in the active collection (user_id=1). The rest fall into three buckets:

1. Orphan formats: have records but no record_type_id (4 records affected)

id	name	record count	suggested action
48	EPs: 7-inch 33/45	1	backfill type_id=1 (45s)
54	LPs 10/12-inch	1	merge into id=2 LPs: 10/12-inch (typo, missing colon)
58	Singles: 14-inch	1	backfill type_id=2 (LPs) — unusual size, but closest fit
64	Picture Sleeve	1	merge into id=4 Picture Sleeves (singular vs plural)

These 4 records are currently invisible to the Type facet (they don't belong to any record_type), so they fall through filters. A small amount of data cleanup would push Type coverage from 99.98% → 100%.

2. Pure typo dups: empty of records, but referenced by prices (12 pairs, ~60 price rows)

These are the same physical format spelled differently. Cleaning them up would require also reassigning the price rows that reference them, which is the part that makes this delicate — we'd be moving real price-guide data.

Src (typo)	→	Canonical	Prices to move
id=65 Gold Standard "447" (double-quote)	→	id=11 Gold Standard '447'	37
id=66 Gold Standard "GB" (double-quote)	→	id=32 Gold Standard 'GB'	3
id=67 Plastic Soundsheets/Flexi-discs	→	id=12 Plastic Soundsheets/Flexi-Discs	1
id=50 Plastic Soundsheets/Flexi-Discs**:**	→	id=12 (no trailing colon)	1
id=29 EPs: 78 RPM	→	id=22 EPs: 78 rpm	4
id=62 Singles: 7-Inch	→	id=1 Singles: 7-inch	2
id=57 Singles: 7-inch**.** (trailing period)	→	id=1	1
id=63 Singles: 7-rpm	→	id=1 Singles: 7-inch	1
id=68 Album: 78 rpm	→	id=10 Albums: 78 rpm	2
id=61 EP: 10-inch	→	id=40 EPs: 10-inch	1
id=59 Single: 78 rpm	→	id=3 Singles: 78 rpm	6
id=52 Single	→	id=9 Singles	1

3. Distinct-but-unused formats: empty of records, referenced by prices (~18 rows)

These are real, distinct formats — just not in this collection. They have legitimate price-guide entries. They should probably stay.

Examples: id=9 "Singles" (83 prices), id=19 "EPs: 10-inch 78 rpm" (19 prices), id=23 "Promotional EPs" (14 prices), id=51 "Singles: 10-inch" (14 prices), id=55 "79LPs: 10/12-inch" (8 prices — looks like a typo of "70s LPs" but unclear).

Why we deferred this

The original Phase 2 plan assumed we could just DELETE FROM record_formats WHERE id NOT IN (SELECT record_format_id FROM records). That fails with an FK violation because prices.record_format_id references record_formats.id. To clean up, we'd need to:

1. Cascade typo merges to the prices table (UPDATE 60+ price rows)
2. Make editorial calls about which formats are typos vs distinct (e.g. is "Single: 78 rpm" a typo of "Singles: 78 rpm", or intentional?)
3. Decide what happens to formats that are distinct-but-unused (keep, soft-delete, archive?)

That's not the kind of work to do under deadline. Better to come back to it deliberately.

Proposed approach when we do tackle this

1. Audit prices referenced by typo dups to confirm they're really typos (look at the price detail strings; if two formats hold prices for the same artist/label/period, they're almost certainly the same thing).
2. Add an editorial mapping doc (could even be a CSV in db/data/) that lists each merge with an explicit yes/no/defer decision per pair, reviewed by a human before any UPDATE runs.
3. Single transactional migration that cascades the approved merges to both records and prices, then deletes only the truly-orphan formats.
4. Optional follow-up: add a UNIQUE index on record_formats.name to prevent future drift.
5. Optional follow-up: add an active boolean to record_formats so distinct-but-unused formats can be hidden from the records edit-form dropdown without losing their price history.

Out of scope for this issue

• Anything that touches records.record_format_id or prices.record_format_id data.
• Schema changes to record_formats.
• Changes to the records sidebar facet (handled in Records index: replace granular Format filter with Record Type (with later format sub-facet) #370 / Phase 2 UI work, which doesn't depend on this cleanup).