Customer Portal & Ticket Retrieval

Component 1 restores Highclere Castle's public web presence immediately while protecting the business from legacy PHP 7.4 vulnerabilities. It provides a read-only, self-service portal for existing customers to sign in, view their order history and Gift Voucher balances, and print or display scannable admission Tickets and VAT Invoices.

Passwordless Email OTP + Gate-Side Order Lookup

Because the legacy users table stored passwords in plaintext and allowed multiple rows with the same email address, Component 1 eliminates password migration entirely and offers two frictionless ways for customers to retrieve their tickets:

Clean PostgreSQL Schema (Drizzle ORM)

The new application never queries the legacy MariaDB database directly. Instead, it queries a clean, normalized, and gateway-agnostic PostgreSQL schema managed via Drizzle migrations:

// src/db/schema.ts (Component 1 Core Tables)
customers (
  id                UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email             TEXT NOT NULL UNIQUE,          -- Normalised lower(trim(email))
  first_name        TEXT NOT NULL,
  last_name         TEXT NOT NULL,
  phone             TEXT,
  billing_address   JSONB,                         -- Structured { line1, line2, city, county, postcode, country, usState }
  legacy_user_ids   INTEGER[] NOT NULL DEFAULT '{}',-- All merged MariaDB users.id values for this email
  created_at        TIMESTAMPTZ NOT NULL,
  updated_at        TIMESTAMPTZ NOT NULL
)

orders (
  id                      UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  legacy_order_id         INTEGER UNIQUE,          -- MariaDB orders.order_id (used by idempotent ETL)
  legacy_user_id          INTEGER NOT NULL,        -- Original MariaDB users.id (REQUIRED for Gate List barcode!)
  customer_id             UUID NOT NULL REFERENCES customers(id),
  order_number            TEXT NOT NULL UNIQUE,    -- Public reference e.g. '428019'
  status                  TEXT NOT NULL,           -- 'confirmed' | 'cancelled' | 'pending' | 'refunded'
  currency                TEXT NOT NULL DEFAULT 'GBP',
  subtotal_amount         NUMERIC(12,2) NOT NULL,
  shipping_amount         NUMERIC(12,2) NOT NULL DEFAULT 0,
  discount_amount         NUMERIC(12,2) NOT NULL DEFAULT 0,
  tax_amount              NUMERIC(12,2) NOT NULL DEFAULT 0,
  total_amount            NUMERIC(12,2) NOT NULL,
  payment_provider        TEXT NOT NULL,           -- 'legacy_sagepay' | 'opayo' | 'stripe' | 'voucher'
  provider_transaction_id TEXT,
  authorization_code      TEXT,                    -- MariaDB orders.AuthorizationNumber
  billing_snapshot        JSONB NOT NULL,          -- Snapshot from order_user_info at purchase time
  shipping_snapshot       JSONB NOT NULL,
  billing_postcode_norm   TEXT NOT NULL,           -- Indexed stripped uppercase postcode for instant lookup
  dietary_standard        INTEGER NOT NULL DEFAULT 0,
  dietary_vegetarian      INTEGER NOT NULL DEFAULT 0,
  dietary_gluten_free     INTEGER NOT NULL DEFAULT 0,
  placed_at               TIMESTAMPTZ NOT NULL,
  updated_at              TIMESTAMPTZ NOT NULL
)

order_items (
  id                      UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  legacy_order_item_id    INTEGER UNIQUE,          -- MariaDB order_item.order_item_id (embedded in barcode!)
  order_id                UUID NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
  legacy_product_id       INTEGER NOT NULL,        -- MariaDB orderProductID
  product_name            TEXT NOT NULL,
  visitor_type            TEXT,                    -- Parsed 'Adult' | 'Child' | 'Carer' | 'Family' etc.
  venue_name              TEXT,                    -- e.g. 'Castle, Egyptian Exhibition and Gardens'
  legacy_venue_id         INTEGER,
  legacy_event_id         INTEGER,
  castle_entry_slot       TEXT,                    -- From events.castle_entry ('AM' | 'PM' | specific guidance)
  quantity                INTEGER NOT NULL,
  unit_price              NUMERIC(12,2) NOT NULL,
  line_total              NUMERIC(12,2) NOT NULL,
  vat_rate                NUMERIC(5,2),
  visit_date              DATE,
  visit_time              TEXT,
  is_scannable_ticket     BOOLEAN NOT NULL DEFAULT false, -- True when non-voided, date-bound, ProductID < 500000, non-promo SKU
  is_voided               BOOLEAN NOT NULL DEFAULT false,
  barcode_value           TEXT,                    -- Precomputed '{8-digit order_number}-{legacy_order_item_id}-{legacy_user_id}'
  updated_at              TIMESTAMPTZ NOT NULL
)

gift_vouchers (
  id                      UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  legacy_voucher_id       INTEGER UNIQUE,
  customer_id             UUID NOT NULL REFERENCES customers(id),
  balance_amount          NUMERIC(12,2) NOT NULL,
  expires_at              DATE NOT NULL,
  updated_at              TIMESTAMPTZ NOT NULL
)

Code 128B SVG Ticket Barcode Specification

In the legacy application (Customers::printorder() and Tickets_model::drawBarcodeAsImage()), on-site gate scanners verify entry against a Code 128 (Start Set B) barcode. Rather than writing temporary .png and .gd2 files to disk on every page view like the legacy app did, the new portal generates a crisp, zero-dependency inline SVG Code 128B barcode.

Legacy Deep-Link & Maintenance Routing Table

Handled directly in Astro SSR middleware (src/middleware.ts) so links inside historical customer emails and links from highclerecastle.co.uk work without broken 404s: