# PHUT Builder A Cloudflare Worker webapp for building GetThere Preferred Hotel Upload Tool (PHUT) CSV files. Pre-loads Sabre property data into Cloudflare D1 so admins can search, select, and configure hotels — then export a valid CSV for the PHUT upload. ## Prerequisites - Node.js 18+ - Cloudflare account with Workers & D1 access - Azure Entra ID app registration (for authentication) ## 1. Entra ID App Registration 1. Go to [Azure Portal → App registrations](https://portal.azure.com/#view/Microsoft_AAD_RegisteredApps/ApplicationsListBlade) 2. Click **New registration** - **Name:** `PHUT Builder` - **Supported account types:** `Accounts in this organizational directory only (Enperso only - Single tenant)` - **Redirect URI:** `Web` → `https://phut-builder..workers.dev/auth/callback` (also add `http://localhost:8787/auth/callback` for local dev) 3. After creation, note down: - **Application (client) ID** → this is `ENTRA_CLIENT_ID` - **Directory (tenant) ID** → already configured as `b52fc953-9412-41ef-8169-b8672c9c002c` 4. Go to **Certificates & secrets** → **New client secret** - Note the **Value** → this is `ENTRA_CLIENT_SECRET` 5. Go to **API permissions** → Ensure these are granted: - `openid` (delegated) - `profile` (delegated) - `email` (delegated) ## 2. Project Setup ```bash # Clone and install cd phut-builder npm install cd frontend && npm install && cd .. # Create D1 database wrangler d1 create phut-db # Copy the database_id into wrangler.toml # Set secrets wrangler secret put ENTRA_CLIENT_ID wrangler secret put ENTRA_CLIENT_SECRET wrangler secret put SESSION_SECRET # Generate with: openssl rand -hex 32 # Run migrations npm run db:migrate # Remote D1 # OR npm run db:migrate:local # Local D1 (for dev) ``` ## 3. Import Sabre Property Data The Sabre property flat file is included at `data/sabre-properties.csv` (64,999 properties, converted from the Jan 2026 active properties export). ```bash # Generate SQL from the included CSV npx tsx scripts/import-properties.ts --file=./data/sabre-properties.csv # Import to local D1 (for dev) wrangler d1 execute phut-db --local --file=./migrations/seed_properties.sql # Import to remote D1 (production) # For 65k rows, split into chunks to stay within D1 limits: split -l 5000 migrations/seed_properties.sql migrations/chunk_ for f in migrations/chunk_*; do wrangler d1 execute phut-db --file=$f; done rm migrations/chunk_* ``` To update the property data later, replace `data/sabre-properties.csv` with a new export. Expected CSV columns: ``` gds_property_id,property_chain,property_name,street_address,city_address,state_province,country_address,zip_postal_code,primary_airport,latitude,longitude ``` ## 4. Development ```bash # Start Worker (serves API on :8787) npm run dev # In another terminal, start frontend dev server (with HMR on :5173) npm run dev:frontend ``` The Vite dev server proxies `/api/*` and `/auth/*` to the Worker on port 8787. For local development, create a `.dev.vars` file: ```env ENTRA_CLIENT_ID=your-client-id ENTRA_CLIENT_SECRET=your-client-secret SESSION_SECRET=your-random-hex-string ``` ## 5. Deploy ```bash npm run deploy ``` This builds the frontend and deploys the Worker with static assets to Cloudflare. ## Architecture ``` Cloudflare Worker (Hono) ├── /auth/* Entra ID OAuth login/callback/logout ├── /api/properties/* Search pre-loaded Sabre properties ├── /api/projects/* PHUT project CRUD ├── /api/projects/:id/entries/* Entry CRUD (add/edit/remove properties) ├── /api/projects/:id/export/* CSV export & validation ├── /api/admin/* Property import, stats └── /* React SPA (static assets) Cloudflare D1 (SQLite) ├── sabre_properties Pre-loaded Sabre hotel data ├── chain_codes Hotel chain reference codes ├── sessions User sessions (Entra ID) ├── phut_projects Upload file projects └── phut_entries Individual property rows ``` ## PHUT CSV Format The exported CSV matches the GetThere Preferred Hotel Upload Template: | Column | Description | |--------|-------------| | `property_name` | Hotel name | | `is_property_in_the_gds_flag` | Y or N | | `supplier_property_id` | Hotel Connect ID (non-GDS) | | `channel_code` | Hotel Connect channel (non-GDS) | | `property_chain` | 2-letter chain code | | `gds_property_id` | Sabre property ID | | `primary_airport` | 3-letter IATA code | | `rate1..rate40` | Negotiated rates | | `begin_date1..begin_date40` | Rate start dates (mm/dd/yy) | | `end_date1..end_date40` | Rate end dates (mm/dd/yy) | | `currency1..currency40` | 3-letter currency codes | | `street_address` | Hotel street | | `city_address` | Hotel city | | `state/province_address` | US state (2-letter) | | `country_address` | Country (2-letter) | | `zip/postal_code_address` | Postal code | | `property_tier` | 1-10, D (demoted), X (excluded) | | `green_property` | Y or N | | `property_note` | Custom note (max 4000 chars) | | `custom_tag1..custom_tag4` | Property attributes (Y/N) | # phut