# Evidence Topic: Booking import dimensions and live database inventory

## Metadata

- Date: 2026-07-31
- Status: Database findings active; intake implementation superseded by later manual-review audit
- Scope: Controlled `booking_test`, checked-in Booking Excel/current-source path, current Finance and company-report projections
- Confidence: Fact
- Source: repeatable-read/read-only PostgreSQL queries, schema/view/importer/Web composition inspection, focused tests
- Last verified: 2026-07-31 11:34 +08:00
- Stale trigger: Booking source activation, Finance current-version change, runtime restart, or Booking join-contract change

## Question

What data is currently in the ARR database, and which dimensions must an operator provide when Booking data is manually
imported for Group Code enrichment?

## Evidence

- Read-only snapshots between 10:44 and 10:49 +08:00 confirmed `booking_test` contains:
  - Booking: one accepted fixture batch, 867 source rows/current parses/room items, and 348 Group Code summaries.
  - Finance: five current daily versions, 475 immutable records, and 417 active retained facts; 58 records are
    historical/non-current. The active 417 still include the known one-row `local_fixture` fact.
  - Ingestion: 25 runs, 24 attempts, five deliveries, 42 artifacts and ten outbox events.
  - Reporting: four monthly runs, 14 daily-version lineage rows and 22 channel-manifest rows.
  - `condon`: 11 room-type reference rows and no owner/entitlement/usage rows; it is not the Booking enrichment source.
- The only Booking batch is accepted `expected_fixture`/`md/1.0` from `RES_COMMENT_TYPE_OF_ROOM.md`: six worksheets,
  867 rows, 348 distinct normalized Group Codes, four room types and total quantity 867.
- Booking currently groups by normalized `group_code_key + room_type` and sums quantity. It then emits one room summary
  per Group Code. Worksheet, source row, artifact and parse version remain provenance/version fields, not report join
  dimensions.
- Of 348 Group Codes, 122 have multiple source rows, none has multiple room types, and 23 appear in both
  `DY-AI-Easy-KB` and `LIANTAI-FIT`. Those 23 are globally merged by the current view and none is used by the current
  Finance projection.
- Finance has 417 active retained rows: 121 rows/27 distinct Group Codes are matched, 285 rows have no Group Code, and
  11 rows/two Group Codes are unmatched. The 29 nonblank Group Codes show no reuse across stay segment, company,
  channel, business date or block in the current snapshot.
- The company-report projection contains nonblank Booking enrichment for all 121 matched rows and blank room/quantity
  values for all 285 missing-code plus 11 unmatched rows. This proves the accepted blank fallback in the database
  projection without publishing a report.
- During this audit the worktree parser changed from a normalized three-column workbook to processor 2.0.0. The latest
  parser expects `Tour Code` (Group Code aliases accepted) plus `โรงแรม`/Hotel text, keeps the physically last row per
  Tour Code, removes last-row cancellations, and derives one or more room-type/quantity items from the hotel text.
  Known TWN/DBL families are confirmed and unknown labels are marked for review.
- At 10:59 the earlier direct PostgreSQL importer and old Web fixtures were not aligned with parser 2.0. This was a
  point-in-time observation superseded by the later draft repository/editor implementation described in the dedicated
  manual-review audit.
- The intended Excel path treats each workbook as a complete replacement source: it creates an immutable batch and
  switches the singleton `booking.current_source_batch`; equal-content uploads are SHA-256 idempotent/reactivatable.
- A later 11:19 read-only snapshot found migrations 014/015 structurally live. `current_source_batch` selects historical
  batch 1 and both review draft tables are empty. The audit did not perform those concurrent migrations.
- The port-8766 process started at 2026-07-30 23:19, before the new Booking composition, and has no hot reload. The current
  HTTP adapter also lacks PATCH/DELETE even though the review editor requires them, so the end-to-end review path remains
  unavailable despite the new database tables.
- The latest focused parser/migration/review/Web suite runs 27/27, but it uses fake repositories/router calls and does not
  exercise real PostgreSQL or the HTTP method adapter. No Booking import, draft activation or report publication was
  performed by this audit.

## Finding

For the current data, `Group Code + room type + quantity` is a sufficient normalized Booking allocation model. It is the
database/report dimension, not a currently stable statement about manual workbook headers. The necessary invariant is
that a normalized Group Code globally identifies one room allocation across worksheets, companies and stay segments.
If the same Group Code can represent different allocations by stay dates or channel, the Booking schema, current views
and company-report join must also gain those dimensions.

Parser 2.0 targets `Tour Code + Hotel` extraction. The separate review draft design is now present in source and live
database; the later 11:30 runtime loads it with PATCH/DELETE and source-upload readiness. The first real draft activation
remains controlled acceptance work. Existing blank/missing lookup behavior remains correct.

## Impact

- Keep `Group Code + room type + quantity` as the normalized result dimensions, while separately choosing and freezing
  whether operators upload a normalized template or the real raw `Tour Code + Hotel` export.
- Treat workbook/sheet/row/hash as audit provenance, not as hidden business keys.
- Accept the parser/draft repository through a real PostgreSQL activation before claiming manual Excel import business-
  write acceptance; HTTP transport/runtime readiness are now complete.

## Open Items

- Decide whether the 23 cross-worksheet Group Codes are intentionally additive or should be isolated by channel/date.
- Add real-PostgreSQL repository/rollback coverage; PATCH/DELETE handlers and server tests are complete.
- Record the concurrent 014/015 migration provenance, then restart and import one controlled workbook under the accepted
  format, test review/idempotence/replacement, and run one released job with explicit write authorization.
