dbMigrations · two migrations · nothing applied

Existing offers become bundles of one

Every thread that exists today is already a bundle of one. The migration copies each thread’s listing into the items table and links its offer rows to it. Once the new API is live, it drops the three old listing columns. No existing value changes.

Checks first
2read-only, each should find nothing
M1
4steps, one transaction, additive
M2
3steps, right after the API is live
Items created
1:1one per existing thread

Why two migrationsthe running API still uses the listing columns

The API that is already running reads and writes the three listing columns on every send, counter and PA. Dropping them, or making the new bundle link required, in the same migration that adds the items table would break it from the moment the migration landed until the new API deployed.

so

M1 only adds: the items table and the bundle link on offer rows, filled for every existing row. The new API then deploys. M2 fills anything the old API wrote in between, makes the link required, and drops the three listing columns.

Checks before M1read-only · each should find nothing

  1. C1A listing-and-vendor pair with more than one thread, counting soft-deleted threads.If any: link offer rows to those threads by when each row was created, not by listing and vendor.mapping
  2. C2An offer row whose listing and vendor have no thread.If any: create a bundle for the pair from its latest row, with one item.mapping

M1 · additive<timestamp>-add-marketplace-offer-items.js · one transaction

  1. Create marketplace_offer_items with its keys and indexes (columns), and add the vendor index on marketplace_offer_associations.
  2. Add marketplace_offer_association_id to marketplace_counter_offers as optional, with its key and index.
  3. Create one item per existing thread: the thread’s listing, its dates, and soft-deleted when the thread is.
  4. Link every offer row to its thread by listing and vendor, or by creation time where C1 found more than one thread.

Its down step drops the column and its index, the vendor index, then the table.

The deploy windowbetween M1 and M2

Once M1 has run, the API and the front end that write items and bundle links deploy, and read only through the items. Until they are live, the old API can still create threads and offer rows the old way. The new API treats a thread with no items as a bundle of its own listing until M2 runs. That fallback is removed together with M2.

M2 · drop the old columns<timestamp>-drop-marketplace-offer-listing-columns.js · right after the API is live

  1. Repeat M1’s steps 3 and 4 for anything the old API wrote in the window.
  2. Make the offer rows’ bundle link required.
  3. Drop marketplace_id from marketplace_offer_associations, marketplace_counter_offers and docusign_requests, with their keys.

Its down step re-adds the three columns as optional and refills them from each bundle’s single item and each PA’s bundle. That is possible only while every bundle has one item.

What to verifyafter each migration

CheckExpectWhen
Items against threads, counting soft-deleted rowsexactly one eachright after M1
Offer rows without a bundle linknoneright after M1
Offer rows per threadunchangedright after M1
Each offer row’s listing against its bundle’s itemthe sameright after M1
Each PA’s listing against the item of its accepted row’s bundlethe sameright after M1
Each listing’s counter_offer_status, recomputed through the itemsunchangedright after M1
The three listing columnsgoneafter M2

Rollbacksafe while every bundle has one item

  1. 1Cancel any open bundle with more than one item. The old API has no way to read one.if bundles exist
  2. 2Run M2’s down step first. It puts back the three listing columns the old API needs and fills them from each bundle’s single item.order
  3. 3Then roll the API back, and run M1’s down step only if the items table has to go too.order

Order per environmentlocal, then dev, then prod

In each environment: the two checks, M1, the API and the front end, then M2. The local capClone1 database comes first. Dropping the columns in local and dev first turns any read that was missed into an error there, before prod.