# W1_W2_RESOLUTION.md

**The eight "invalid migration source tables", investigated against the real D7 schema.**
2026-08-25. **D7 unmodified. No data migrated.**

---

## 0. 🛑 First correction: five of the eight were not source tables

`STAGE_0_CAPTURES.md` §7 reported 8 of 51 source tables as absent. That was produced by grepping
`table_name:` across each YAML — which matches **both** `source.table_name` **and**
`destination.table_name`. Re-parsed properly with a YAML parser:

| originally reported | what it actually is | verdict |
|---|---|---|
| `ce_webform_component_archive` | **destination** of `ce_webform_component` (source `webform_component`, 28 rows ✅) | not a source defect |
| `ce_webform_settings` | **destination** of `ce_webform_definition` (source `webform`, 1 row ✅) | not a source defect |
| `ce_webform_submissions` | **destination** of `ce_webform_submission_parent` (source `webform_submissions`, 21,064 ✅) | not a source defect |
| `ce_webform_submitted_data` | **destination** of `ce_webform_submission` (source `webform_submitted_data`, 441,970 ✅) | not a source defect |
| `commerce_payment` | **destination** of `ce_commerce_payment`; its source is `commerce_payment_transaction`, 26,797 ✅ | not a source defect |
| `commerce_coupon_usage_transaction` | source **and** destination | 🛑 genuinely absent |
| `commerce_tax_rate` | source **and** destination | 🛑 genuinely absent |
| `commerce_tax_type` | source **and** destination | 🛑 genuinely absent |

**Corrected count: 43 of 46 source tables exist. Three are absent, all Commerce, all W-2.**
The `ce_` prefix theory was wrong — there was no prefix slip.

**W-1's premise is therefore void: no webform migration needs renaming.** The instruction not to
rename without verifying the columns is what surfaced this — the rename would have been wrong.

---

## 1. W-1 — the four webform migrations, column by column

Every source column of every webform migration was compared against `information_schema`
(`.logs/task35-webform-schema.out`, audit `.logs/task39-column-audit.php`).

### 1.1 `ce_webform_definition`

- **current source → actual source:** `webform` → **unchanged, correct** (1 row, nid 27)
- **column mapping:** 11 of 14 columns were mapped. Three were not: `block`, `teaser`,
  `submit_notice`. **`submit_notice` is not at its default** — D7's default is `1`, node 27 has
  **`0`**, i.e. the "submit another response" notice is deliberately suppressed. Unmapped, D10
  would have shown a notice D7 hides.
- **strategy:** ✅ **fixed** — all three added; the destination row is now a faithful copy.
- **dependency:** `ce_node` (unchanged).
- **verification:** `SELECT nid, block, teaser, submit_notice FROM webform;` → `27, 0, 0, 0`.
  Runtime source probe now emits `submit_notice 0`.

### 1.2 `ce_webform_component`

- **current source → actual source:** `webform_component` → **unchanged, correct** (28 rows)
- **column mapping:** all 10 real columns mapped (`nid, cid, pid, form_key, name, type, value,
  extra, mandatory, weight`). **No gaps, no phantom columns.**
- **strategy:** ✅ **no change required.** This migration was already correct.
- **dependency:** `ce_node`.
- **verification:** `SELECT COUNT(*) FROM webform_component;` → 28, matching capture C-1.

### 1.3 `ce_webform_submission_parent`

- **current source → actual source:** `webform_submissions` → **unchanged, correct** (21,064)
- **column mapping:** all 6 real columns mapped (`sid, nid, uid, is_draft, submitted,
  remote_addr`); `submitted` deliberately feeds both `created` and `completed`.
- **strategy:** ✅ **no change required.**
- **dependency:** `ce_node`, `ce_user`.
- **verification:**
  `SELECT COUNT(*) FROM webform_submissions;` → 21,064 (R-8) ·
  `SELECT COUNT(*) FROM webform_submitted_data d LEFT JOIN webform_submissions s ON s.sid=d.sid WHERE s.sid IS NULL;` → **0** (no orphan value rows).

### 1.4 `ce_webform_submission`

- **current source → actual source:** `webform_submitted_data` → **unchanged, correct** (441,970)
- **column mapping:** all 5 real columns used (`nid, sid, cid, no, data`).
- **the one real question — the `ids` omit `nid`.** The table's PRIMARY KEY is
  `(nid, sid, cid, no)`; the migration declares `(sid, cid, no)`. **Verified safe, not assumed:**
  `COUNT(*) = COUNT(DISTINCT sid,cid,no) = COUNT(DISTINCT nid,sid,cid,no) = 441,970`, and **0**
  sids span more than one nid. `sid` is globally unique, so `nid` is redundant in the key.
- **strategy:** ✅ **no change required.** Left as-is deliberately, with the uniqueness now proven.
- **dependency:** `ce_node`, `ce_user` + the component map (already captured and live).
- **verification:** the three COUNT DISTINCTs above; plus
  `SELECT COUNT(*) FROM webform_submitted_data d LEFT JOIN webform_component c ON c.nid=d.nid AND c.cid=d.cid WHERE c.cid IS NULL;` → **0** (every cid resolves to a real component).

> **Incidental finding:** `no` is `'0'` for **all 441,970 rows** — there are no multi-value
> components on this webform. The `delta` mapping is correct but will only ever see one value.

**W-1 conclusion: all four sources were valid; one genuine mapping gap (`submit_notice`) fixed;
one key assumption proven rather than trusted. No table was renamed.**

---

## 2. W-2 — the three genuinely absent Commerce sources

### 2.1 `ce_commerce_tax_rate` and `ce_commerce_tax_type` — ✅ RESOLVED (retired)

> Closed 2026-08-25. Nine zero-evidence checks; both migrations retired to `migrations-retired/`.
> See `COMMERCE_DESTINATION_ARCHITECTURE.md` §W-2a.

- **current source → actual source:** `commerce_tax_rate` / `commerce_tax_type` → **there is no
  actual source.**
- **evidence** (`.logs/task38-w2-commerce.sh`, `.logs/task43-w2-final.sh`):
  - `commerce_tax` **is enabled** — but at `schema_version = 0`, i.e. never configured.
  - **No tax table of any kind exists** in D7's 338 tables.
  - **No `commerce_tax%` variable exists** (Commerce 1 stores UI-created rates/types there) —
    `SELECT COUNT(*) FROM variable WHERE name LIKE 'commerce_tax%';` → **0**.
  - **No tax line item has ever existed.** `commerce_line_item` types across 116,472 rows are
    `product` (113,271), `commerce_discount` (3,047), `product_discount` (154). Tax: **0**.
- **strategy:** these cannot be repaired by renaming a table or adding a source plugin. There is
  no data in any form. **They should be retired from the migration set.**
- **why this is a decision, not a mechanical fix:** deleting declared migration scope is a
  statement that *CE Online has never charged tax and has no historical tax record to preserve*.
  The data says exactly that, but confirming it is a business call, not mine.
- **dependency:** none — nothing depends on them.
- **verification query (should stay at zero):**
  ```sql
  SELECT (SELECT COUNT(*) FROM information_schema.tables
            WHERE table_schema='db' AND table_name LIKE 'commerce_tax%') AS tax_tables,
         (SELECT COUNT(*) FROM variable WHERE name LIKE 'commerce_tax%')  AS tax_variables,
         (SELECT COUNT(*) FROM commerce_line_item WHERE type LIKE '%tax%') AS tax_line_items;
  ```

### 2.2 `ce_commerce_coupon_usage` — ✅ RESOLVED (mapping determined)

> Closed 2026-08-25. `commerce_promotion_usage` has no `uid` and no date column either, so the
> reconstruction question is moot. Source is `field_data_commerce_coupons` (2,629 rows, 713
> orphaned). See `COMMERCE_DESTINATION_ARCHITECTURE.md` §W-2b.

- **current source → actual source:** `commerce_coupon_usage_transaction` →
  **`field_data_commerce_coupons`** (2,629 rows, every one `entity_type = commerce_order`).
- **why the declared table does not exist:** `commerce_coupon_usage_transaction` belongs to
  **commerce_coupon 1.x**. This site runs **2.x** (`schema_version 7202`), which dropped that
  table and records redemption as an **entity-reference field on the order** instead.
- **the data is real and currently uncovered.** `ce_commerce_coupon` migrates the 120 coupons
  themselves, but **nothing** migrates the 2,629 order↔coupon links. Left as-is, every historical
  discount becomes unattributable:

  | coupon | code | times used |
  |---|---|---|
  | 80 | `saloncentric` | 331 |
  | 36 | `ADMIN100%OFF1212` | 301 |
  | 24 | `SOUTHCAROLINA` | 148 |

- **column mapping — and where it runs out:**

  | declared | real equivalent | note |
  |---|---|---|
  | `transaction_id` | *(none)* | 2.x has no surrogate key; identity is `(entity_id, delta)` |
  | `coupon_id` | `commerce_coupons_target_id` | ✅ direct |
  | `order_id` | `entity_id` (with `entity_type='commerce_order'`, `deleted=0`) | ✅ direct |
  | `uid` | **not recorded** | recoverable only by joining `commerce_order.uid` |
  | `date` | **not recorded** | recoverable only as `commerce_order.created` |

- **the decision:** `uid` and `date` do **not exist** in 2.x. They can be reconstructed from the
  order (the join support added for line items makes this trivial), but the order's owner and
  creation date are **not** the same fact as "who redeemed this coupon and when" — that would be
  inventing precision D7 does not have. Per `CLAUDE.md`, migrations must not invent. So:
  **migrate `coupon_id + order_id` only, or reconstruct `uid`/`date` from the order?**
- **strategy once decided:** repoint the source, key on `(entity_id, delta)`, drop or reconstruct
  the two absent columns. **Not implemented — this is the specific item to stop on.**
- **dependency:** `ce_commerce_coupon`, `ce_commerce_order`, and `ce_user` only if `uid` is
  reconstructed.
- **verification query:**
  ```sql
  SELECT COUNT(*) FROM field_data_commerce_coupons
   WHERE entity_type='commerce_order' AND deleted=0;   -- expect 2,629
  ```

---

## 3. Four further defects found by the same audit — all fixed

Auditing *every* declared source column (not just the eight tables) found four migrations whose
table existed but whose columns did not.

### 3.1 `ce_commerce_line_item` — ⭐ the "silently free order" risk, confirmed and closed

`EXECUTION_RUNBOOK.md` listed as an open unknown: *"`commerce_unit_price_amount` /
`commerce_total_amount` are field-API names that depend on the field. A wrong name yields NULL,
and a NULL price is a silently free order. Confirm before Stage 2."*

**Confirmed: none of the four price columns exists on `commerce_line_item`.** That table has eight
columns and not one is a price. Commerce 1 stores price as a **field**:

- `field_data_commerce_unit_price` — 113,101 rows
- `field_data_commerce_total`

Unfixed, **all 116,472 line items would have migrated at NULL** — every historical order free,
with no error raised.

- **fix:** added declarative `joins:` support to the `ce_table` **source plugin** (purely
  additive — no other migration declares `joins`, so all 43 others are byte-identically
  unaffected), then joined both field tables on
  `entity_id = line_item_id AND entity_type = 'commerce_line_item' AND deleted = 0`.
  **LEFT** joins, so the 11 line items that genuinely have no unit-price row still migrate
  carrying NULL rather than disappearing (decision 7: PRESERVE + LOG).
- **runtime-verified:** the source now returns
  `line_item_id 74 · unit 1633 USD · total 1633 USD` instead of NULL.
- **verification query:**
  ```sql
  SELECT COUNT(*) AS line_items,
         SUM(up.commerce_unit_price_amount IS NULL) AS null_priced
    FROM commerce_line_item li
    LEFT JOIN field_data_commerce_unit_price up
      ON up.entity_type='commerce_line_item' AND up.entity_id=li.line_item_id AND up.deleted=0;
  -- expect 116,472 / 11
  ```

### 3.2 `ce_commerce_discount_usage` — a primary key that does not exist

Declared `ids: discount_mail_order_id`. **No such column.** The table has three columns
(`discount, mail, order_id`) and **no PRIMARY KEY at all**.

- **fix:** keyed on the composite `(discount, mail, order_id)`, **verified unique first**:
  2,977 rows / 2,977 distinct triples.
- **verification:** `SELECT COUNT(*), COUNT(DISTINCT discount, mail, order_id) FROM commerce_discount_usage;` → `2977 2977`.

### 3.3 / 3.4 `ce_wistia_media` and `ce_wistia_media_track` — written against an imagined schema

Neither migration's columns existed. These were not misspellings; they described a different table.

| migration | declared | actually exists |
|---|---|---|
| `ce_wistia_media` | `id, wistia_id, title, created` | `id_wistia_media, uid, video_hash_id, completed` |
| `ce_wistia_media_track` | `id, media_id, nid, percent, timestamp` | `id_wistia_media_track, video_hash_id, uid, time` |

**Two semantic traps, both now documented in the YAML:**

- **`completed` is a 0/1 FLAG, not a date** — 443 zeros / 32,294 ones across 32,737 rows. The old
  mapping called it `created`, which would have been read as a timestamp.
- **`time` is a PLAYBACK POSITION IN SECONDS, not a unix timestamp** — min 0.00, max
  2,749,965.62, mean 8,661.65. The old mapping called it `timestamp`.

> 🛑 **A finding that outlives the fix:** there is **no `percent` column and no `nid` column** in
> `wistia_media_track`. `CLAUDE.md` §24 preserves *"self-granted video completion"* as a known
> defect — but the evidence base for it is thinner than the migration assumed. D7 records only a
> playback position and a per-video completion flag; there is no per-node completion percentage.
> Any analysis that assumes one is assuming a column D7 does not have.

- **fixed and runtime-verified:** 32,737 and 62,137 rows — matching R-11a / R-11b exactly.
- **verification:** `SELECT COUNT(*), COUNT(DISTINCT id_wistia_media) FROM wistia_media;` → `32737 32737`.

---

## 4. State after this work

`drush migrate:status` — **every `ce_*` migration now reports a real source count** except the
three in §2. Counts match the register throughout: `ce_node` 620 · `ce_user` 13,250 ·
`ce_commerce_order` 87,640 · `ce_commerce_line_item` 116,472 · `ce_flagging` 132,256 ·
`ce_webform_submission` 441,970 · `ce_quiz_result` 3,973 · `ce_url_alias` 13,969.

Section A of the column audit is now down to the three W-2 items:

```
A. DECLARED SOURCE COLUMNS THAT DO NOT EXIST
     ce_commerce_coupon_usage   TABLE ABSENT   (decision W-2b)
     ce_commerce_tax_rate       TABLE ABSENT   (decision W-2a)
     ce_commerce_tax_type       TABLE ABSENT   (decision W-2a)
```

**No migration any longer references a column that does not exist**, apart from those three
absent tables.

### 🛑 Section B is NOT empty — DECISION W-4

An earlier draft of this document claimed it was; that was read off a truncated output and is
corrected here. **20 migrations map fewer columns than their source table has.** Most are
deliberate and harmless — `revision_id`, `language`, `tnid`, `translate`, `theme`, `signature`
have no D10 counterpart. But several look load-bearing and none is annotated as intentional:

| migration | unmapped columns that look load-bearing |
|---|---|
| `ce_referral_discount` | `referred_discount_expiry`, `referrer_discount_expiry` *(both empty — see below)*, `host`, `http_referer` |
| `ce_quiz_properties` | **`max_score`, `time_limit`, `allow_resume`, `allow_skipping`, `allow_jumping`, `repeat_until_correct`, `review_options`, `result_type`** (21 columns total) |
| `ce_quiz_result_answers` | **`points_awarded`, `is_doubtful`**, `answer_timestamp`, `number` |
| `ce_quiz_multichoice_answers` | **`question_nid`, `question_vid`**, `weight` |
| `ce_referral_admin_discount` | **`referrer_discount_amount`**, `created` |
| `ce_commerce_payment` | `uid`, `instance_id`, `message`, `message_variables`, `changed` |
| `ce_user` | `changed`, `picture` |

Two were checked against the data rather than assumed. **They did not come out the same way** —
which is the argument for measuring each of the rest before deciding:

- **`ce_referral_discount`'s two expiry columns are EMPTY — dropping them loses nothing.**
  `SUM(referred_discount_expiry <> 0)` and the referrer equivalent are both **0 of 361 rows**.
  No discount in the ledger has ever carried an expiry, so there is no live/expired distinction to
  preserve. An earlier draft of this section claimed the opposite; the measurement corrects it.
  Omitting them is safe — though it should be *annotated* as deliberate rather than left silent.

- **`ce_quiz_properties` dropping `max_score` is a real loss.** Pass/fail is
  `score >= pass_rate`; `max_score` is what the score is out of, and **it varies**:

  | `max_score` | quizzes |
  |---|---|
  | 1 | 53 |
  | 2 | 8 |
  | 3 | 2 |
  | 0 | 2 |
  | 30 / 80 / 90 | 1 each |

  Keeping `pass_rate` while discarding `max_score` preserves the threshold and throws away the
  scale it is measured against. Three quizzes score out of 30, 80 and 90; most score out of 1.
  Neither migration references the column at all (`grep -c max_score` → 0).

**Not fixed here.** Unlike a column that does not exist, an unmapped column may be a deliberate
scope decision — and there is no annotation either way. Each needs a yes/no on whether D10 carries
it. **Audit command:** `ddev drush php:script /var/www/html/.logs/task39-column-audit.php`

Files changed (originals in `.logs/prefix-backup-2026-08-25/`): five migration YAMLs and the
`ce_table` source plugin. **No data was migrated. D7 was not touched.**

---

## 5. ✅ W-3 RESOLVED — see `COMMERCE_DESTINATION_ARCHITECTURE.md`

> The destination architecture was **already decided** in `PHASE_2_MIGRATION_DECISIONS.md` §6:
> Commerce 3 **entities**, not raw tables. Six migrations had drifted from it. The "38 missing
> tables" count was also wrong — only **16** genuinely need a schema. Two Commerce decisions
> remain open (order status mapping, and the required store). Original text below.

## 5. (original) The next blocker, found on the way — DECISION W-3

Stage 2 still cannot run, for a reason unrelated to sources: **all 38 `ce_table` destination
tables are missing from D10 — 0 of 38 exist.** `CeTable::import()` writes with `merge()` and
**never creates a table**. Only `ce_certificate` (3 tables) and `ce_quiz` (3) define a
`hook_schema` at all, and `ce_certificate` is enabled with its schema never installed. `ce_commerce`
has no `.install` file. **32 of the 38 tables are defined nowhere.**

**And the naming collides with Commerce 3.** `composer.json` requires `drupal/commerce: ^3.3`, and
these `ce_table` destinations write to raw tables named `commerce_order`, `commerce_order_revision`,
`commerce_payment`, `commerce_product_revision`, `commerce_customer_profile_revision`,
`commerce_discount`, `commerce_coupon` — **the same names Commerce 3 uses for its own entity
storage.** Once Commerce 3 is installed those tables exist, and `merge()` would write D7-shaped
rows straight into live entity tables, bypassing the entity API.

The design is also internally inconsistent: `ce_commerce_line_item` targets
`entity:commerce_order_item` and `ce_commerce_product` targets `entity:commerce_product_variation`
— proper entity destinations — while the orders and payments those line items belong to target raw
tables.

**The decision:** do the Commerce migrations write Commerce 3 **entities**, or archival raw tables
under a non-colliding prefix (e.g. `ce_d7_commerce_order`)? That choice determines 11 migrations
and must be made before any schema is written — writing 38 tables first would bake it in.

---

*Read-only against D7 throughout. No migration has been executed.*