Highclere Castle Ticketing & Commerce · Disaster Recovery Programme Updated: October 2026 · Status: Phase 0 Complete · Component 1 Built & Verified
Highclere Castle Commerce & Ticketing DR Plan
Component 1 · Architectural Reference

Design & Engineering Decisions

Built & Verified

This document records what we decided during our Component 1 domain-modelling and forensic audit sessions, why each decision was made after auditing the legacy CodeIgniter 3 / MariaDB application, and how it guarantees seamless continuity for Highclere Castle visitors and gate staff.

← Return to Component 1 Spec View Component Roadmap Domain Glossary
1 · Authentication & Gate-Side Operations

Authentication Strategy & Gate Scanner Protection

Decision 1.1

Passwordless Email OTP & Magic Link (Only)

What we decided: Customers sign into /login/ exclusively using a 6-digit Email OTP or Magic Link sent via the Postmark REST API (15-minute expiry, SHA-256 hashed in PostgreSQL auth_otp_tokens, rate-limited per IP and email).

Why Legacy Passwords Were Dropped

The legacy users table stored passwords in plaintext and allowed multiple accounts with the same email address. Migrating plaintext passwords is an unacceptable security risk, and forcing password resets across duplicate email rows caused widespread login failures in the legacy site.

Decision 1.2

Keep On-Site Gate Lookups on the Existing Staff Gate List Stack

What we decided: We will not build a public unauthenticated "Order Number + Postcode" lookup form on the customer portal in Component 1. On-site gate lookups remain handled by Castle staff on their dedicated Gate List scanner/report stack.

Why Public Lookup Was Avoided

Castle gate staff already operate from a daily downloaded Gate List report on a separate stack when assisting visitors at the gates. Avoiding a second public unauthenticated lookup surface keeps Component 1 simple (KISS) and eliminates order-enumeration attack vectors.

2 · Customer Consolidation & Barcode Parity

Unbreakable Gate Scanner Barcode Contract

Decision 2.1: Consolidate Customers by Normalised Email, Preserve legacy_user_id per Order

Architectural ADR

What we decided:

  1. All legacy users rows sharing the same LOWER(TRIM(email)) are consolidated into a single PostgreSQL customers record.
  2. When duplicate users rows have differing contact/address details, the most recent users row (highest id) wins for canonical profile fields, while all merged IDs are preserved in customers.legacy_user_ids.
  3. If an historical orders.user_id is orphaned (missing from users), the ETL sync falls back to order_user_info.email (the purchase-time billing snapshot) so no historical order is ever lost.
  4. Every orders row in PostgreSQL explicitly preserves its original MariaDB orders.user_id in orders.legacy_user_id.
Operational Contract with Castle Gate Scanners

On-site Castle gate scanners verify entry against the Gate List using a Code 128B barcode formatted as:
{8-digit zero-padded order_number}-{legacy_order_item_id}-{legacy_user_id}
Preserving orders.legacy_user_id per order guarantees that every barcode rendered by the new portal matches the staff Gate List bit-for-bit, while still giving the customer a single unified view of their entire order history.

3 · Gift Vouchers

Individual Credit Grants vs. Single Indistinct Pot

Decision 3.1: Model Each Gift Voucher as an Individual Grant (Oldest-Expiry-First)

Domain Sharpening

What we decided: In our domain model (CONTEXT.md), a Gift Voucher is an individual pre-paid monetary credit grant attached to a Customer with its own initial_amount (including any percentage uplift), net remaining balance_amount (after deducting redemptions in user_voucher_usage), and expires_at date. The Customer Portal displays both the total combined available credit and the individual breakdown of active Gift Vouchers with their exact expiry dates.

Resolving a 5-Year Legacy Ambiguity

Since February 2021, customers have been able to hold multiple active vouchers simultaneously with different expiry dates. The legacy Customers::orders() controller summed the balances but had an unresolved bug regarding which expiry date to show. Showing each individual voucher and its net remaining balance removes all ambiguity for both customers and support staff.

4 · Scannable Tickets, Catering & Add-Ons

SKU Filtering & Presentation at the Gate

Decision 4.1

Complete Non-Ticket SKU Filtering at the ETL Boundary

The ETL normaliser (src/etl/) computes is_scannable_ticket using the complete union of legacy rules:

  • Order is confirmed (status = 'confirmed') and item is not voided.
  • Item has a valid visit_date and non-empty visit_time.
  • legacy_product_id < 500000 (excludes champagne/hamper add-ons).
  • Excludes promotional book/gift SKUs (174005, 177025, 178002–178027, 181165–181170).
  • Picnic Afternoon Tea (Venue 16, SKUs 177014, 177024): Remains is_scannable_ticket = true with collection instructions.
Decisions 4.2 & 4.3

One Ticket Stub per Item + Family Breakdown + Pre-Resolved Times

What we decided:

  • Each eligible order_items row renders its own ticket stub with its own Code 128B barcode.
  • Schema preserves family_adult_qty and family_child_qty so Family Tickets display "Family Ticket (2 Adults, 2 Children)".
  • Rescheduled tickets display staff notes from date_changed_note.
  • During ETL, event_launch, event_venues, and ticket text templates are pre-resolved onto order_items—eliminating 1,600 lines of hardcoded PHP conditionals.
5 · Order Lifecycle & VAT Invoices

Quarantining Voided Tickets & Reconciling Taxes

Decision 5.1

Hide Unpaid 'Pending' Orders & Quarantine Voided Barcodes

What we decided: Legacy order_status = 'P' orders (abandoned at SagePay) are synced to PostgreSQL as 'pending' for audit completeness but hidden from the Customer Portal.

On /account/orders/:orderNumber/, voided items show a clear "Voided — No Longer Valid" badge. On /tickets/ and /invoice/, voided items are strictly excluded so a voided barcode can never be printed or scanned at the gate.

Decision 5.2

Reconciled HTML/Print VAT Invoices (First-Class Domain Term)

What we decided: Added VAT Invoice to CONTEXT.md. The ETL normaliser computes each item's effective vat_rate (20.00% for UK admission/shop; 0.00% for books/exports).

Unlike legacy Pdf_vatinvoice.php (which omitted order discounts and produced mismatched totals), our /invoice/ view includes discount_amount so Total including VAT always reconciles with orders.total_amount, bearing Highclere Enterprises LLP's tax registration (GB 724 6472 28).

6 & 7 · Sync & Compatibility

60-Second Incremental ETL & Deep-Link Routing

Decision 6.1

Bounded-Window Upsert Catches Staff Edits Without mdate

What we decided: Auditing legacy Orders_model.php revealed that staff actions in /admin (such as ticket voiding or date swaps) do not update mdate.

Our 60-second incremental ETL sync (guarded by pg_try_advisory_lock) does not rely solely on mdate. Instead, it syncs:

  • Recent orders (order_id >= max_synced_order_id - 5000 or mdate >= now - 7 days),
  • All active/upcoming tickets (VisitDate >= CURRENT_DATE - INTERVAL 7 DAY or date_changed IS NOT NULL), and
  • All user_vouchers + user_voucher_usage.
Decision 7.1

Preserve ?next= Across OTP Login & Catch Legacy Links

What we decided:

  • Unauthenticated requests to ticket views redirect to /login/?next=... and land the customer directly on their tickets immediately after OTP verification.
  • Legacy /customers/lostpassword and /customers/passwordreset/* redirect to /login/?reason=passwordless with a clear notice explaining passwords are no longer needed.
  • Legacy deep links from old confirmation emails (/customers/printorder/*) route straight to the new ticket view.