data: read-only inventory for the Item migration (seed key drift, legacy keys, slug collisions)

M0. Run the inventory queries from the design doc read-only (public anon REST or approved read-only access only, no writes, no secrets) and write the data-fix list: seed key drift, legacy keys, slug collisions. Anything needing a privileged read goes to the PO as a request for JOY.

Part of the Item / variant / listing model approved by JOY on 2026-10-27 (#1129, design doc Docs/Proposals/2026-10-27-item-variant-listing-model.md, section 10 task 2).

Depends on: none

See the design doc sections 4, 5 and 7 for the schema, impact and migration plan.

Claimed by a Core sub-session of the Rate Core PO. Running the M0 inventory (design doc section 7.3) read-only through the public anon REST endpoint and the public site API. No writes, no secrets. Anything needing a privileged read will be listed as an exact SQL request for JOY.

[Agent: Core] M0 inventory and data-fix list for the Item migration (#1129 design doc, sections 7 and 7.3).

How it was read

Prod project gulptwduchsjcsbndmua, schema rate, through the public anon REST endpoint that rate.vn uses (the publishable key in the site bundle). SELECTs only, about 15 requests in total, run on 2026-10-28.
Anon can read every row of master_products and products: both have a FOR SELECT USING (true) policy in the reference copy. price_alerts is not readable by anon (42501).
Seed and normalizer checks ran today's ProductNormalizer (dev 645c155e) on every seed name, master name and listing title. SQL cannot do this part.
The run is repeatable: apps/importer/src/shopee/jobs/item-inventory-report.ts (PR feat(importer): read-only Item migration inventory report (#1148) #1168). It is read-only and uses the anon key.

Counts (section 7.3 inventory)

Check	Result
Masters total / published / draft / archived / NULL status	37 / 16 / 21 / 0 / 0
`bad_shape` (not three segments)	0
`khc:` keys	0
`:unknown` masters	0
`:standard` masters	2
Items (distinct `brand:model`)	37
Items with more than one variant	0
Items whose variants disagree on brand or category	0
Listings total / without a master / on a missing master	128 / 0 / 0
Item slug collisions (section 7.1 helper)	0
Item names still carrying a size after the 7.1 helper	0
Earbuds with a size in the key	5
Masters whose key differs from the normalizer key	6

Data-fix list

1. Seed key drift: 6 masters (fix in M0, before M2)

The normalizer keys these differently from the stored key, so Tier 1 never hits them. No master holds the normalizer key yet, so no duplicate exists today. A re-key in place is enough: no archiving, no merge, no alert move. Ids and slugs stay the same, so the FKs from listings and alerts and the public URLs do not change.

Stored `model_key`	Normalizer key	Status	Listings	Slug (unchanged)
`samsung:galaxy-buds3-pro:64gb`	`samsung:galaxy-buds3-pro:unknown`	published	4	`samsung-galaxy-buds3-pro`
`apple:airpods-pro-3:standard`	`apple:airpods-pro-3:unknown`	published	9	`apple-airpods-pro-3-usb-c`
`sony:wf-1000xm5:64gb`	`sony:wf-1000xm5:unknown`	draft	8	`sony-wf-1000xm5`
`sony:wf-1000xm6:standard`	`sony:wf-1000xm6:unknown`	draft	9	`sony-wf-1000xm6`
`apple:airpods-pro-2-usbc:64gb`	`apple:airpods-pro-2:unknown`	draft	4	`apple-airpods-pro-2-usb-c`
`apple:watch-ultra-3-49mm:64gb`	`apple:watch-ultra-3:64gb`	draft	4	`apple-watch-ultra-3-49mm-64gb`

38 listings sit on these 6 masters. Every listing title keys to the normalizer key, not the stored key. Sample: tgdd-tai-nghe-bluetooth-true-wireless-samsung-galaxy-buds-3-pro-r630n ("Tai nghe TWS Samsung Galaxy Buds3 Pro R630N") keys as samsung:galaxy-buds3-pro:unknown; hhm-MEWM4SA ("Apple Watch Ultra 3 - GPS + Cellular, 49mm ...") keys as apple:watch-ultra-3:64gb.

Proposed fix

The re-key SQL is already written and reviewed in data: published master keys differ from normalizer keys (iPad Pro M5 11, Buds3 Pro, Watch Ultra 3 49mm) #1113 (comment). It covers all six rows, is idempotent and skips a pair whose target key exists. It ships as a DOS-Me data migration with JOY's approval, beta first.
data: migrate stored model_keys for earbuds and Watch Ultra 3 to normalizer keys #1123 should list all six rows. Its scope names only three (Buds3 Pro, AirPods Pro 3, Watch Ultra 3); the three draft rows (sony:wf-1000xm5, sony:wf-1000xm6, apple:airpods-pro-2-usbc) are in the same SQL and need the same seed change.
In the same migration, set storage_gb = NULL on the five earbuds rows. Today they hold 64, 64, 64, 1 and 1. The normalizer gives earbuds a NULL storage (ProductNormalizer: earbuds keyed :1gb, iPad RAM taken as storage, AirPods Pro by year #643, ProductNormalizer: benchmark and phone-template specs override the title (RAM, earbud storage, SAR, Face ID) #657), and the 1 on the two :standard rows matches no key:
```
UPDATE rate.master_products mp SET storage_gb = NULL, updated_at = now()
FROM rate.categories c
WHERE c.id = mp.category_id AND c.slug = 'earbuds' AND mp.storage_gb IS NOT NULL;
```
The flagship-seed-data.ts change (six seed keys to the normalizer keys, and storage_gb: null on the earbuds) merges right after the migration, as data: migrate stored model_keys for earbuds and Watch Ultra 3 to normalizer keys #1123 requires. backfill-specs.ts --apply must not run between the two steps, or it inserts duplicate masters.
apple:airpods-pro-2-usbc becomes Item apple:airpods-pro-2. The normalizer does not split USB-C from Lightning. That is fine today (only the USB-C model is sold new), but if a Lightning listing shows up it will join the same Item.
After the fix, the 5 earbuds Items are single-variant Items with an unknown variant, as section 4.2 intends. The M3 guard query ("sized earbuds") then returns 0 rows.

2. Legacy keys: none

No khc: keys, no key outside the three-segment shape, no NULL status. Section 7.1's legacy branch has nothing to handle. No action.

3. Slug collisions: none

With the 7.1 helper (strip -(\d+(gb|tb)|standard|unknown)$), the 37 masters give 37 distinct Item slugs. No Item slug equals another Item's variant slug. No action.

Two notes for the M2 and task 5 authors, neither of them a collision:

The 5 earbuds slugs have no size suffix, so their Item slug equals their variant slug. Section 7.3 allows this ("An Item slug may only collide with one of its own variants' slugs").
11 stored slugs (3 of them on the drift rows above) differ from the slug the normalizer would build today, for example apple-ipad-pro-m5-11-inch-256gb vs apple-ipad-pro-m5-11-256gb, apple-watch-series-11-46mm-gps-64gb vs apple-watch-series-11-46mm-64gb, samsung-galaxy-tab-s10-ultra-5g-256gb vs samsung-galaxy-tab-s10-ultra-256gb. Matching is by key, so nothing breaks. But a new size of an existing Item must take the Item's stored slug, not a freshly built one, or the Item and its new variant get unrelated slugs (task 5).

4. Multi-size Items and `unknown` masters: none yet

37 Items, each with one master. There is no :unknown master, so M3 has nothing to retire today, and the 7.3 query "unknown masters with alerts or reviews" returns 0 rows whatever the alerts table holds. No privileged read is needed for it.

5. A storage-less listing on a sized master (feeds #1105 / task 5)

fptshop fpt-00911517 ("Apple Watch Series 10 GPS 42mm Viền nhôm Dây vải") sits on apple:watch-series-10-42mm:64gb (draft), but its title keys as apple:watch-series-10-42mm:unknown. The registry has no apple:watch-series-10 entry with only_storage_gb: 64, unlike watch-series-11, watch-ultra-2 and watch-ultra-3. Under the new model it would land on the Item with no variant.

Proposed fix: add an apple:watch-series-10 registry entry with only_storage_gb: 64 in task 6 (seeds per Item), or as a small normalizer fix before task 5. Code only, no data change.

6. Listings whose price spans several SKUs: 3 (design 4.4 defect)

Master	Listing	`price_min`	`price_max`
`apple:iphone-15:128gb`	shopee	20,490,000	21,820,000
`apple:iphone-16:128gb`	shopee	22,590,000	22,965,000
`sony:wf-1000xm6:standard`	shopee `50757465973`	8,013,533	9,287,600

Their price_max feeds the master's max_price. This is the known defect of section 4.4. Proposed fix: none in M0. Task 5 (SKU's own price as price_max) or M5 (listing_skus) handles it.

7. Draft masters with no listing: 6 (information only)

apple:iphone-16-pro:256gb, lenovo:thinkpad-x1-carbon-gen-12:512gb, samsung:galaxy-a55:256gb, xiaomi:17-pro-max:512gb, xiaomi:18-pro-max:512gb, xiaomi:pad-6s-pro:256gb. The backfill gives each a draft Item. No action.

Privileged reads

None of the M0 decisions above needs one. One optional check for JOY, to confirm that anon sees every row (the DOS-Me ledger has 8 rate migrations not mirrored here, and one could have narrowed a policy):

SELECT (SELECT count(*) FROM rate.master_products) AS masters,          -- anon saw 37
       (SELECT count(*) FROM rate.products) AS listings,                -- anon saw 128
       (SELECT count(*) FROM rate.products WHERE cluster_id IS NULL) AS no_master,  -- anon saw 0
       (SELECT count(*) FROM rate.price_alerts a JOIN rate.master_products mp ON mp.id = a.master_product_id
         WHERE mp.model_key IN ('samsung:galaxy-buds3-pro:64gb','apple:airpods-pro-3:standard',
           'sony:wf-1000xm5:64gb','sony:wf-1000xm6:standard','apple:airpods-pro-2-usbc:64gb',
           'apple:watch-ultra-3-49mm:64gb')) AS alerts_on_rekeyed;      -- information only: the re-key keeps ids

Effect on M2 (#1150)

The backfill can run as drafted. It meets no legacy keys, no slug collision, no multi-size Item and no unlinked listing.
Order: data: migrate stored model_keys for earbuds and Watch Ultra 3 to normalizer keys #1123's re-key and seed change first, so the Items are created with the normalizer's Item keys (apple:watch-ultra-3, apple:airpods-pro-2). If M2 runs first, these two Items get the old keys, and fixing them later means re-keying an Item as well as a master.

feat(importer): read-only Item migration inventory report (#1148) (#1168

[Agent: PO] Done: merged into dev (PR #1168). Docs/tooling change, no production runtime effect, so Done at merge per the lifecycle rule.

Navigation Menu

Repository navigation

data: read-only inventory for the Item migration (seed key drift, legacy keys, slug collisions) #1148

How it was read

Counts (section 7.3 inventory)

Data-fix list

1. Seed key drift: 6 masters (fix in M0, before M2)

2. Legacy keys: none

3. Slug collisions: none

4. Multi-size Items and `unknown` masters: none yet

5. A storage-less listing on a sized master (feeds #1105 / task 5)

6. Listings whose price spans several SKUs: 3 (design 4.4 defect)

7. Draft masters with no listing: 6 (information only)

Privileged reads

Effect on M2 (#1150)

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

data: read-only inventory for the Item migration (seed key drift, legacy keys, slug collisions) #1148

Description

Activity

JOY commented on Oct 27, 2026

JOY commented on Oct 27, 2026

How it was read

Counts (section 7.3 inventory)

Data-fix list

1. Seed key drift: 6 masters (fix in M0, before M2)

2. Legacy keys: none

3. Slug collisions: none

4. Multi-size Items and unknown masters: none yet

5. A storage-less listing on a sized master (feeds #1105 / task 5)

6. Listings whose price spans several SKUs: 3 (design 4.4 defect)

7. Draft masters with no listing: 6 (information only)

Privileged reads

Effect on M2 (#1150)

JOY commented on Oct 28, 2026

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions

4. Multi-size Items and `unknown` masters: none yet