db(DOS-Me): M1+M2 rate.items, item_id columns, RLS/grants, backfill and Item RPCs

Prepare the DOS-Me migration (M1+M2) and mirror it in the reference copy. Applying it needs JOY's approval (AGENTS.md 0.1); prod is also blocked until the DOS-Me beta flow reopens (ROADMAP 6.4). Test on a preview or local stack only.

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 4).

Depends on: #1147, #1148

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

Also depends on: #1123 (stored key fixes, must be applied before M2).

[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.

[Agent: PO] Prerequisites from the #1151 matcher (PR #1179), to be covered by M1+M2 before RATE_ITEM_MODEL=nullable_variant is enabled:

A transactional RPC to retire an Item's unknown variant when its first sized variant appears (Codex P1, open in feat(importer): item-first matcher with match_item behind RATE_ITEM_MODEL #1179).
Block the race between reading a manually assigned variant and the listing upsert, preferably in the rate.products trigger (Codex P1).
DB contract the matcher already calls: match_item returning item_id, item_key, name, similarity; refresh_item_stats(p_item_id); the M1 trigger. Details in the PR feat(importer): item-first matcher with match_item behind RATE_ITEM_MODEL #1179 body.

[Agent: PO] One more DB-side prerequisite from the Codex review of #1185 (PR #1190): two overlapping ingests of the same listing can race on the kept-variant / previous-Item refresh. Same class as the manual-variant race noted above; cover it with the products trigger or a row lock in M1+M2.

[Agent: PO] From the Rate Services PO (RS#688, PR #689): Rate Services now filters rate.products by item_id, so M1 should add an index on rate.products(item_id).

[Agent: PO] Hand-over: agent:po was a tracking placeholder. Assigned to the Core session to draft the M1+M2 migration as a proposal under Docs/Proposals/ (not applied; JOY decides and applies through DOS-Me).

docs(proposals): item migration review fixes for locks, retire, old w…

docs(proposals): item migration review round 2 fixes

[Agent: PO] Flag-on prerequisite from the Rate Services PO (RS#700): the reference migrations give rate_worker no grant on rate.items. Rate Services needs SELECT and UPDATE on rate.items (plus EXECUTE on match_item and refresh_item_stats) before RATE_ITEM_MODEL is turned on. Include the grants in the M1+M2 draft (PR #1242) and confirm against DOS-Me.

docs(proposals): rate_worker item grants and a re-runnable rollback r…

docs(proposals): re-check a retired unknown variant at commit

docs(proposals): dos-me m1+m2 item migration proposal with replay test (

docs(proposals): m0 review fixes and the worker grants sized for engi…

docs(proposals): dos-me m0 seed re-key data step with replay test (#1267

Navigation Menu

Repository navigation

db(DOS-Me): M1+M2 rate.items, item_id columns, RLS/grants, backfill and Item RPCs #1150

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

db(DOS-Me): M1+M2 rate.items, item_id columns, RLS/grants, backfill and Item RPCs #1150

Description

Activity

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

JOY commented on Oct 28, 2026

JOY commented on Oct 28, 2026

JOY commented on Oct 28, 2026

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