Data Architecture · Anti-Corruption Layer

Database & Legacy ETL Sync Architecture

1,177,622 Production Records Synced

This automated data pipeline bridges the Castle's previous ticketing system and our modern, secure platform. It ensures that customer bookings, admission tickets, and gift vouchers remain completely accurate and up to date, while office staff continue assisting visitors in their everyday office portal.

The Database & Legacy ETL Anti-Corruption Layer (src/db/ + src/etl/) bridges the vulnerable legacy PHP 7.4 / MariaDB system and our clean, modern PostgreSQL core. It guarantees zero legacy bleed into the new web application while office staff continue using the legacy /admin portal safely isolated behind NetBird VPN.

1 · Architectural Strategy

Zero-Legacy-Bleed Anti-Corruption Layer

How We Protect Customer Data & Daily Castle Operations

The Castle's old website ran on software that was no longer safe to leave exposed to the public internet. Rather than risking customer data or rushing an untested replacement, we took an immediate two-part action:

Zero Risk to the Castle: The old system was moved behind our secure, private office VPN. Staff continue using it inside the office as normal to answer customer queries and print daily Gate Lists.
Automatic 60-Second Copying: A background process continuously cleans and copies every booking, ticket, and gift voucher into our new modern database every 60 seconds.
1.17 Million Historical Records Protected: Every valid booking since January 2018 is already verified and safely stored in the new system.

Per ADR-0001, the new web application exclusively queries our clean PostgreSQL database. Legacy MariaDB quirks - Unix integer timestamps (cdate), MySQL zero-dates (0000-00-00), magic SKU numbers (ProductID < 500000), void_tickets null vs empty-string discrepancies, and duplicate user emails - are sanitized and normalized at the ETL boundary inside src/etl/.

┌──────────────────────────────────────────────────────────────────────────────────┐
│                   PUBLIC WEB APPLICATION (Astro SSR + Hono API)                  │
│               Exclusively queries clean, normalized PostgreSQL tables            │
└────────────────────────────────────────┬─────────────────────────────────────────┘
                                         │ SELECT / INSERT / UPDATE
                                         ▼
┌──────────────────────────────────────────────────────────────────────────────────┐
│                             CLEAN POSTGRESQL 16 DATABASE                         │
│             Strict Foreign Keys · ACID Transactions · TIMESTAMPTZ · JSONB        │
│       (customers · orders · order_items · gift_vouchers · auth_tokens · sessions)│
└────────────────────────────────────────▲─────────────────────────────────────────┘
                                         │
                 highclere-etl-sync.timer│ pg_try_advisory_lock(84729104)
                 Every 60 Seconds        │ 1,000-row Batched Multi-Row Upserts
                 (Out-of-Process)        │ ON CONFLICT DO UPDATE
                                         │
┌────────────────────────────────────────┴─────────────────────────────────────────┐
│                     ETL ANTI-CORRUPTION LAYER (src/etl/)                         │
│       Email Consolidation · Barcode Preservation · Refund Aggregation · Guidance │
└────────────────────────────────────────▲─────────────────────────────────────────┘
                                         │ READ-ONLY (Over Private NetBird VPN)
                                         │
┌────────────────────────────────────────┴─────────────────────────────────────────┐
│                       LEGACY MARIADB 10.11 (118 MyISAM Tables)                   │
│          Isolated behind NetBird VPN · Office Staff /admin Access Only           │
└──────────────────────────────────────────────────────────────────────────────────┘

2 · Production Data Scale

Live Production Dataset Sync Metrics (850 MB Dump)

The data pipeline was thoroughly tested against the complete historical Castle database backup. By removing incomplete and abandoned checkout attempts and focusing on confirmed visits dating back to 2018, the modern platform securely holds:

The ETL pipeline was executed and validated against the full 850 MB production backup (castle_2026-10-01_10-29-32.sql : 497,923 legacy users, 428,033 orders, 646,035 items, 6,318 vouchers). By filtering out 62,000 abandoned/failed carts and applying the 2018-01-01 statutory cutoff, the clean PostgreSQL database holds:

Customers

497,662

Consolidated Customer records deduplicated by email across all historical accounts.

Orders

263,647

Confirmed, cancelled, and shipped orders from 2018+ with purchase-time billing snapshots.

Order Items

409,988

Scannable admission tickets, afternoon tea collection vouchers, and gift shop items.

Gift Vouchers

6,325

Individual credit grants; 2,958 expired COVID vouchers archived, leaving 73 active vouchers.

Total Database Scale: 1,177,622 Synced Rows

The initial data transfer safely copied 1,177,622 verified customer and ticket records in high-speed batches, ensuring the platform was fully populated and ready for immediate service.

The initial full synchronization ingests 1,177,622 production rows using 1,000-row multi-row batched PostgreSQL upserts rather than slow row-by-row round-trips, ensuring rapid disaster recovery deployment.

3 · Database Core

Clean Database Structure & Schema

How Records Are Organised in the Modern Database

Instead of 118 disorganized legacy tables, all customer and ticketing data is organized into six clean, secure records:

Customers: Each visitor has one unified profile identified by their email address, linking all historical purchases.
Orders: Confirmed purchases with payment records, VAT breakdowns, and original scanner codes.
Tickets & Items: Every date-bound admission ticket, afternoon tea box, and gift shop product with clear arrival times and visitor instructions.
Gift Vouchers: Active pre-paid vouchers with exact expiry dates (expired COVID vouchers from years past are safely archived).
Security Passes (Tokens & Sessions): Secure, temporary 6-digit email sign-in codes that replace vulnerable passwords.

All data access is governed by the strictly typed schema in src/db/schema.ts:

// src/db/schema.ts (Anti-Corruption Core Tables)

// Consolidated Customers (Deduplicated by Email)
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 AddressSnapshot { line1, line2, city, postcode, country }
  legacy_user_ids   INTEGER[] NOT NULL DEFAULT '{}',-- Merged MariaDB users.id values
  created_at        TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at        TIMESTAMPTZ NOT NULL DEFAULT now()
)

// Confirmed & Audited Orders
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.00,
  discount_amount         NUMERIC(12,2) NOT NULL DEFAULT 0.00,
  coupon_code             TEXT,
  tax_amount              NUMERIC(12,2) NOT NULL DEFAULT 0.00,
  total_amount            NUMERIC(12,2) NOT NULL,
  voucher_used_amount     NUMERIC(12,2) NOT NULL DEFAULT 0.00,
  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,           -- AddressSnapshot from order_user_info at purchase time
  shipping_snapshot       JSONB NOT NULL,
  billing_postcode_norm   TEXT NOT NULL,            -- Normalised uppercase alphanumeric postcode
  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 DEFAULT now()
)

// Order Items (Scannable Tickets, Catering & Merchandise)
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' | 'Family' | 'Carer' etc.
  venue_display_name      TEXT,                     -- Pre-resolved in ETL from event_venues / category
  visit_time_display      TEXT,                     -- Pre-resolved in ETL e.g. 'Morning (10:00am - 12:30pm)'
  visitor_instructions    TEXT,                     -- Pre-resolved in ETL from category & event_type_ticket_text
  legacy_venue_id         INTEGER,
  legacy_event_id         INTEGER,
  castle_entry_slot       TEXT,                     -- From events.castle_entry ('AM' | 'PM' | slot guidance)
  quantity                INTEGER NOT NULL,
  family_adult_qty        INTEGER NOT NULL DEFAULT 0,-- Preserves Family Ticket adult breakdown
  family_child_qty        INTEGER NOT NULL DEFAULT 0,-- Preserves Family Ticket child breakdown
  unit_price              NUMERIC(12,2) NOT NULL,
  line_total              NUMERIC(12,2) NOT NULL,
  vat_rate                NUMERIC(5,2) NOT NULL DEFAULT 20.00, -- Computed effective UK VAT rate (20.00% or 0.00%)
  visit_date              DATE,
  visit_time              TEXT,
  is_scannable_ticket     BOOLEAN NOT NULL DEFAULT false, -- True when confirmed, non-voided, date-bound admission/tea
  is_voided               BOOLEAN NOT NULL DEFAULT false, -- True if void_tickets is populated in MariaDB
  date_changed_note       TEXT,                     -- Preserves staff transfer note from legacy order_item.date_changed
  barcode_value           TEXT,                     -- Precomputed '{8-digit order_number}-{legacy_order_item_id}-{legacy_user_id}'
  updated_at              TIMESTAMPTZ NOT NULL DEFAULT now()
)

// Individual Gift Voucher Credit Grants
gift_vouchers (
  id                      UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  legacy_voucher_id       INTEGER UNIQUE,           -- MariaDB user_vouchers.id
  customer_id             UUID NOT NULL REFERENCES customers(id) ON DELETE CASCADE,
  source_order_id         INTEGER,                  -- Original purchase order ID
  initial_amount          NUMERIC(12,2) NOT NULL,   -- Face value including percentage uplift grant
  balance_amount          NUMERIC(12,2) NOT NULL,   -- Net remaining balance after user_voucher_usage deductions
  expires_at              DATE NOT NULL,
  is_active               BOOLEAN NOT NULL DEFAULT true, -- False if expires_at < CURRENT_DATE
  updated_at              TIMESTAMPTZ NOT NULL DEFAULT now()
)

// Passwordless Authentication Tokens
auth_otp_tokens (
  id                      UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email                   TEXT NOT NULL,
  otp_hash                TEXT NOT NULL,            -- SHA-256 hashed 6-digit OTP code
  magic_token_hash        TEXT NOT NULL UNIQUE,     -- SHA-256 hashed hex token for magic links
  redirect_to             TEXT,                     -- Preserves destination (e.g. '?next=/account/orders/428019/tickets/')
  attempts                INTEGER NOT NULL DEFAULT 0,
  request_ip              TEXT,
  expires_at              TIMESTAMPTZ NOT NULL,     -- 15-minute sliding TTL
  consumed_at             TIMESTAMPTZ,
  created_at              TIMESTAMPTZ NOT NULL DEFAULT now()
)

// Customer Sessions
sessions (
  id                      UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  token_hash              TEXT NOT NULL UNIQUE,     -- SHA-256 hashed 32-byte session token
  customer_id             UUID NOT NULL REFERENCES customers(id) ON DELETE CASCADE,
  expires_at              TIMESTAMPTZ NOT NULL,     -- Sliding 30-day session expiry
  created_at              TIMESTAMPTZ NOT NULL DEFAULT now(),
  last_seen_at            TIMESTAMPTZ NOT NULL DEFAULT now()
)

4 · Operational Execution

60-Second Incremental Sync & Safety Guardrails

How Office Changes Reach the Website in 60 Seconds

When office staff change a visitor's date, void an unwanted ticket, or issue a voucher in the office back-office, our system detects the update and syncs it to the public customer portal within 60 seconds.

Built-in safety locks ensure two updates can never collide, and running the sync multiple times never creates duplicate bookings.

Automatic 60-Second Refresh

Every minute, the system automatically checks for:

Any new customer enquiries or order adjustments handled by office staff,
Transfers of ticket dates, arrival time slots, or cancellations, and
Active gift voucher balances and credit redemptions.

Reliability & Double-Booking Protection

Concurrency Lock: Only one update cycle runs at any moment, ensuring records can never conflict or cross-wire.
Duplicate Proof: Repeating the update process produces zero duplicate records, keeping order totals exact.
Independent Background Service: Operates silently in the background, so peak visitor website traffic never slows down office updates.

Bounded Incremental Sync Query

Every 60 seconds, npm run db:sync-legacy -- --incremental 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 active user_vouchers + user_voucher_usage.

Concurrency & Safety Guardrails

PostgreSQL Advisory Lock: Acquires pg_try_advisory_lock(84729104). If another sync is in progress, it exits cleanly (code 0) without contention.
Two-Run Idempotency Gate: Pure upsert semantics (ON CONFLICT DO UPDATE). Running the sync back-to-back produces zero duplicate records.
Out-of-Process systemd Timer: Managed by highclere-etl-sync.timer completely independent of the Astro SSR Node process.