# Company-channel Booking Excel header parse failure

## Metadata

- Date: 2026-08-11
- Status: Parser 2.1.0 compatibility implemented locally and verified; deployment pending
- Scope: company-channel Booking-source parser 2.1.0, exact supplied Lian Tai/QBD July and August workbooks,
  wrong-input controls and existing one-file/full-replacement source lifecycle
- Confidence: Fact for all supplied workbooks, synthetic regressions and parser/coordinator behavior; the original
  rejected request did not durably record its selected filename
- Source: spreadsheet metadata/header-only renders, parser/upload/source-lifecycle inspection, exact-parser replay,
  focused/full tests and read-only PostgreSQL snapshot
- Last verified: 2026-08-11 local workspace
- Stale trigger: Booking Excel header matching, source-upload persistence or accepted source batch changes

## Observed state

- The deployed ARR service on public port 8765 remained healthy and protected authenticated APIs normally.
- A read-only `booking_test` snapshot found no review draft, Booking Excel artifact or source batch created after
  2026-08-03. Accepted batch 7, `july-test.xlsx`, remains current with 72 source rows and 72 distinct Group Codes.
- `ProgramBookingSourceCoordinator.submit` parses the workbook before uploading it to object storage or creating a
  review draft. A header rejection therefore leaves no durable upload identity and cannot replace the current source.

## Deterministic reproduction

The exact production function `booking_ingestion.excel.parse_excel_bytes` was run against four recently modified
workbooks without changing them. Each returned:

`BOOKING_EXCEL_HEADERS_MISSING — Excel 中未找到同时包含 Tour Code 和โรงแรม的工作表`

- `8.4 (1).xlsx` and `各渠道情况-2026年08月-更新至8.5.xlsx` contain report-output columns such as `BLOCK_CODE` and
  `RATE_CODE`, not the Booking-source columns. If either was selected, rejection is correct because it is the wrong
  input type.
- The Lian Tai source sheet has `Tour Code / 主团号` and `วางข้อมูลที่นี่ / 酒店明细`.
- The QBD source sheet has `Tour Code / 主团号` and `Raw Hotel Detail / วางข้อมูลที่นี่ / 酒店明细`.

The parser normalizes punctuation and whitespace, then requires the entire normalized cell to equal one allowlisted
alias. Its Tour Code aliases are `TOURCODE`, `GROUPCODE`, `GOURPCODE`, `团号` and `团队代码`; the hotel alias is only
the exact Thai `โรงแรม`. The bilingual cells normalize to concatenated text and match neither set.

An in-memory-only copy of each raw workbook was then given exact `Tour Code` and `โรงแรม` headers, with every other
cell unchanged:

- Lian Tai parsed successfully: 67 source rows, 100 extracted items and 16 pending review items.
- QBD parsed successfully: 129 source rows, 252 extracted items and 245 pending review items.

Changing only the hotel header was insufficient because the bilingual Tour Code header also fails exact matching.
These results isolate a parser/template compatibility gap rather than corrupt XLSX bytes, database failure or
company-report publication failure.

## Exact supplied-template compatibility experiment

The user explicitly confirmed that both supplied formats must be accepted. Their exact operational header cells are:

- Lian Tai `วางข้อมูล(BOOKING AUG.26)`: `A2 = Tour Code / 主团号`,
  `D2 = วางข้อมูลที่นี่ / 酒店明细`.
- QBD `2026年8月｜AUGUST_26`: `C3 = Tour Code / 主团号`,
  `E3 = Raw Hotel Detail / วางข้อมูลที่นี่ / 酒店明细`.

A process-memory-only experiment retained exact whole-cell matching and added only the three corresponding normalized
aliases: `TOURCODE主团号`, `วางข้อมูลที่นี่酒店明细` and
`RAWHOTELDETAILวางข้อมูลที่นี่酒店明细`.

- The unchanged Lian Tai file parsed only its intended source sheet: 67 rows, 100 items and 16 pending.
- The unchanged QBD 2026-08-06 file parsed only its intended source sheet: 104 rows, 218 items and 212 pending.
- AUTO_CALC, dashboard, summary, guide and daily-usage sheets were not recognized as data sheets.
- `8.4 (1).xlsx` and `各渠道情况-2026年08月-更新至8.5.xlsx` remained rejected with
  `BOOKING_EXCEL_HEADERS_MISSING` under the same experiment.
- The focused current parser/coordinator baseline passed 14/14 tests.

This establishes that broad substring or fuzzy matching is unnecessary. Readable raw alias labels normalized into an
exact allowlist are sufficient and preserve the wrong-report-input boundary.

## Local implementation and verification

- `booking_ingestion.excel` now reports parser version `2.1.0` and keeps readable raw labels for all approved legacy
  and bilingual header forms. It normalizes those labels into the pre-existing exact full-cell allowlist; no filename,
  month, worksheet-name, substring or fuzzy fallback was added.
- Missing and duplicate header failures now describe `Tour Code/主团号` and `酒店明细` neutrally, and the Web error
  catalog maps both stable codes into Chinese, English and Thai.
- New synthetic parser tests prove both Lian Tai and QBD bilingual forms work at their real offset rows, unrelated
  `AUTO_CALC` sheets are ignored, similar-but-longer labels fail, and duplicate recognized labels fail closed.
  Coordinator tests prove a valid bilingual upload reaches private storage/draft creation while an unrecognized
  bilingual label reaches neither storage nor draft persistence.
- The unchanged supplied files replay under parser 2.1.0 as follows: Lian Tai July `79/111/16`, Lian Tai August
  `67/100/16`, QBD July `235/447/432` and QBD August `104/218/212` for rows/extracted items/pending items. The two
  wrong report-output workbooks remain `BOOKING_EXCEL_HEADERS_MISSING`. Focused tests and full test discovery pass.

## Impact and repair boundary

The failed request stopped before object-store registration and draft persistence, so no Booking source, Finance fact,
company report or downstream event changed. The current accepted batch remains available.

The implemented Booking parser 2.1.0 behavior normalizes the three readable full-header labels into the existing
exact allowlist, retains all current aliases, adds synthetic Lian Tai/QBD plus wrong-input and ambiguity regressions,
and updates neutral error copy/current-contract documentation. The existing rule-set hash already includes aliases and
version, and draft/run/parse lineage stores both, so no database migration is needed.

The upload endpoint and UI accept one workbook, and activation switches the singleton full Booking source. Supporting
both header formats therefore does not mean that two separate uploads append or remain active together; multi-file
merge would be a separate product change. The high QBD pending count also remains an operational review workload after
header acceptance. No workbook, database, object-store object or runtime process was changed during implementation;
deployment and any real upload/review/activation remain separate authorized actions.
