Database & Data Pipeline · Architectural Reference

ETL & Legacy Data Decisions

1,177,622 Records Synced

This reference guide documents the forensic audit findings and engineering decisions governing the MariaDB → PostgreSQL Anti-Corruption Layer (src/db/ + src/etl/), proven across the full 850 MB production database dump (castle_2026-10-01_10-29-32.sql).

View ETL Sync Architecture → Component 1 Portal Decisions Target System Architecture

1 · Isolation Strategy

Zero-Legacy-Bleed Anti-Corruption Layer

Decision 1.1: Web Application Exclusively Queries Clean PostgreSQL Core

ADR-0001

What we decided: The public web application and domain layer never query the legacy 118-table MariaDB database directly. MariaDB is strictly an asynchronous ETL source while the legacy /admin remains accessible to office staff over private NetBird VPN during Phases 1–4.

Stopping 15 Years of Technical Debt at the Boundary

Legacy MariaDB quirks (Unix integer timestamps cdate, MySQL zero-dates 0000-00-00, magic product number ranges < 500000, void_tickets null vs empty-string discrepancies, and duplicate user emails) are cleaned and normalized inside src/etl/. The legacy database can be decommissioned with zero changes to public web code.

2 · Customer Consolidation & Scanner Contract

Unbreakable Gate Scanner Barcode Contract

Decision 2.1 · Consolidation

Consolidate Customers by Normalised Email

All legacy users rows sharing the same LOWER(TRIM(email)) are consolidated into a single PostgreSQL customers record. Across the 850 MB production dataset, 497,923 legacy user rows consolidated into 497,662 unique Customer profiles.

When contact details differ across merged accounts, the most recent account (highest users.id) wins for canonical profile fields, with all merged IDs preserved in customers.legacy_user_ids.

Decision 2.2 · Scanner Contract

Preserve `legacy_user_id` per Order

Castle gate scanners verify visitor entry against the daily Gate List using a Code 128B barcode formatted as:
{8-digit order_number}-{legacy_order_item_id}-{legacy_user_id}

Preserving orders.legacy_user_id on every PostgreSQL order guarantees that barcodes rendered in the portal match the staff Gate List bit-for-bit, while still presenting the customer with a consolidated order history.

3 · Production Scale & Scope

850 MB Production Database Sync Metrics & Cutoff Scope

Decision 3.1 · Scope

2018-01-01 Historical Cutoff & Skipping Unpaid Carts

What we decided: The ETL excludes 42,871 unpaid 'P' (Pending / Abandoned at SagePay) orders, 19,051 'F' (Failed payment) attempts, and any orders marked deleted = 1.

Orders placed prior to 2018-01-01 are excluded because they are over 8 years old, fully expired, and fall outside statutory HMRC VAT retention requirements.

Result: Syncs 263,647 orders and 409,988 order items using 1,000-row batched multi-row PostgreSQL upserts, preventing 164,000 obsolete pre-2018 rows from bloating the new database.

Decision 3.2 · Live Metrics

1,177,622 Records Synced & Verified

The production ETL pipeline was verified against the full 850 MB production backup with the following verified record counts:

Consolidated Customers: 497,662 rows
Confirmed / Audited Orders (2018+): 263,647 rows
Order Items (Tickets & Merchandise): 409,988 rows
Gift Vouchers: 6,325 rows (73 active unexpired)
Total Synced Database Rows: 1,177,622 rows

4 · Financial Integrity & Vouchers

Partial Refunds & Gift Voucher Expiry Enforcement

Decision 4.1 · Refunds

Partial Refunds vs Full Refunds (`sagepay_refunds`)

What we decided: Legacy PHP routinely set orders.covid_refund even on partial refunds recorded in sagepay_refunds (190 orders, such as refunding a £35 book on a £320.50 Christmas Tour booking).

The ETL preloads SUM(Amount) from sagepay_refunds. Only orders where covid_chargeback is set, refunded >= order_total, or covid_refund has no partial entries are marked 'refunded'. Partially refunded orders remain 'confirmed' so non-voided tickets retain scannable barcodes.

Decision 4.2 · Vouchers

Gift Voucher Expiry Enforcement (`user_vouchers.expiry_date`)

What we decided: Out of 6,325 voucher rows, 2,958 unused COVID vouchers issued between 2020 and 2023 have passed their explicit expiry_date, leaving 73 genuinely active vouchers.

The ETL sets gift_vouchers.is_active = false whenever expires_at < CURRENT_DATE. Both the ETL and portal queries strictly enforce expires_at >= CURRENT_DATE, protecting Castle commercial revenue from honouring multi-year expired COVID credits.

5 · Data Cleaning & Guidance

Product Lookup Priority Chain & Ticket Guidance

Decision 5.1 · Lookup Chain

Three-Tier Product Name Resolution & Clean Entities

What we decided: Resolves product names in priority order:

products.ProductID = order_item.orderProductID
products.id = order_item.product_id (recovers 1,577 items where staff later modified ProductID)
hampers.ProductID = order_item.orderProductID (for hampers/champagne)
Deleted Gift Shop SKUs (>= 500000) fall back to 'Gift Shop Merchandise (SKU #...)'

All strings undergo full HTML entity decoding (&, ', £, numeric entities) before database storage.

Decision 5.2 · Enrichment

Pre-Resolved Event Opening Times & Guidance

During ETL sync, event_launch, event_venues, and event_type_ticket_text are pre-resolved to populate visitor_type, venue_display_name, visit_time_display, and visitor_instructions on order_items.

VisitTime is converted via convertAmPmTimeToSql (e.g. '10.30am' → '10:30:00') when matching time-slot guidance, eliminating 1,600 lines of hardcoded PHP conditionals.

6 · Real-Time Operations

60-Second Incremental ETL & Advisory Lock Guard

Decision 6.1: Bounded-Window Upsert & Advisory Lock Concurrency Guard

ADR-0004

Staff actions in legacy /admin (ticket voiding, date rescheduling) do not bump mdate on orders. To ensure operational changes reflect on the public customer portal within 60 seconds:

Bounded Query Window: Syncs recent orders (order_id >= max_synced_order_id - 5000 or mdate >= now - 7 days), all active/upcoming ticket items (VisitDate >= CURRENT_DATE - INTERVAL 7 DAY or date_changed IS NOT NULL), and all active vouchers.
PostgreSQL Advisory Lock: Acquires pg_try_advisory_lock(84729104) at startup and exits cleanly (code 0) if another sync is currently running, eliminating race conditions.
Two-Run Idempotency Gate: The ETL is strictly idempotent using ON CONFLICT DO UPDATE. Running the script twice yields zero duplicate rows and zero drift.
Out-of-Process Execution: Scheduled via a native Linux systemd timer (highclere-etl-sync.timer), ensuring a slow or unreachable MariaDB host over NetBird VPN can never stall public HTTP requests.