Commerce Database Schema

This document details the concrete physical database schema for the Commerce & Finance Bounded Context (schema: commerce). The Commerce schema acts as the conversion and accounting engine, focusing on B2C/B2B sales, capacity holds, cross-border manifest data, and generalized taxation logic based on actuals.

Physical Entity Relationship Diagram

erDiagram
    TOUR_OFFERING ||--o{ TOUR_OFFERING_PRICE : "priced by"
    TOUR_OFFERING ||--o{ CHECKOUT_SESSION : "initiates"
    TOUR_OFFERING ||--o{ BOOKING : "receives"

    CHECKOUT_SESSION |o--o| BOOKING : "converts to"

    BOOKING ||--|{ PASSENGER : "includes"
    BOOKING ||--|{ PAYMENT : "processes"
    BOOKING ||--o{ ANCILLARY : "adds"
    BOOKING ||--o{ INVOICE : "billed by"
    TOUR_OFFERING ||--|| FINANCIAL_LEDGER : "creates"

    PASSENGER ||--o{ TICKET : "issued to"
    SEAT_RESERVATION |o--o| PASSENGER : "reserves for"

    FINANCIAL_LEDGER ||--o{ TAX_LEDGER_ENTRY : "calculates tax"
    FINANCIAL_LEDGER ||--o{ INVOICE : "generates"

    INVOICE ||--o{ INVOICE_CANCELLATION : "is cancelled by"
    RECONCILIATION_UPLOAD ||--o{ RECONCILIATION_ENTRY : "contains"

Table Definitions

Note on tenant_id: In the Commerce schema, tenant_id is a soft reference (UUID) to the operators table in the Backoffice schema. Hard foreign keys cannot cross database schemas.

CAUTION

Global Conventions:

1. All mutable tables include an updated_at TIMESTAMPTZ DEFAULT now() column.
2. All tables include an index on (tenant_id). Other common indexes include (tenant_id, tour_offering_id) on bookings, and (booking_id) on child entities like passengers and payments.

tour_offerings

The sellable projection of a Backoffice TourDeparture. The system creates/updates this entity when Backoffice publishes a departure via the TripPublished event. The tour_offering_prices table supplies pricing — a read-model synced from the Backoffice PriceMatrix via PriceMatrixPublished events.

Column	Type	Constraints	Description
id	UUID	Primary Key	Unique offering identifier
tenant_id	UUID	Not Null	Soft FK to backoffice.operators
tour_departure_id	UUID	Not Null	Soft FK to backoffice.tour_departures (source departure)
tour_template_id	UUID	Not Null	Soft FK to backoffice.tour_templates
costing_sheet_id	UUID	Not Null	Soft FK to backoffice.costing_sheets
charter_quote_id	UUID		Soft FK to backoffice.charter_quotes (B2B lineage)
active_price_matrix_id	UUID		Soft FK to backoffice.price_matrices — the currently published PriceMatrix version. Updated via PriceMatrixPublished events.
title	VARCHAR	Not Null	Denormalized display title
description	TEXT		Denormalized display description
start_date	TIMESTAMP	Not Null	Start of the offering
end_date	TIMESTAMP	Not Null	End of the offering
status	VARCHAR	Not Null	E.g., SCHEDULED, CANCELLED, COMPLETED
available_ancillaries	JSONB	Nullable	Denormalized array of resolved ancillary catalog items for this offering. Populated from TripPublished payload. See ancillary-catalog.md §Commerce Projection.
available_boarding_points	JSONB	Nullable	Denormalized array of resolved boarding point library items for this offering. Populated from TripPublished payload. Each entry includes stop name, address, coordinates, surcharge, door pickup availability + surcharge + radius, and is_origin flag. The booking widget reads this directly — no cross-schema query to Backoffice. See boarding-points.md §Commerce Projection.
max_door_pickups	INT	Nullable	Maximum door pickup bookings for this offering. Denormalized from TripPublished payload (source: tour_templates.max_door_pickups). Commerce enforces this at checkout time.

checkout_sessions

A short-lived entity tracking purchase intent before confirmation. Lifecycle: ACTIVE → EXPIRED (TTL-based cleanup) or CONVERTED (successful payment). Default TTL: 30 minutes (expires_at = created_at + 30 min).

Column	Type	Constraints	Description
id	UUID	Primary Key	Unique session identifier
tenant_id	UUID	Not Null	Soft FK to backoffice.operators
tour_offering_id	UUID	Foreign Key (tour_offerings.id), Not Null	Offering under checkout
booking_id	UUID	Foreign Key (bookings.id)	Converted booking ID. Set when session converts to a Booking.
session_type	VARCHAR	Not Null, Default: BOOKING	BOOKING (standard B2C/B2B checkout) or ONBOARD_SALE (driver-initiated payment link). Determines event routing on expiry — see sweep note below.
session_token	VARCHAR	Unique, Not Null	Fingerprint / token
status	VARCHAR	Not Null, Default: ACTIVE	ACTIVE (in progress), EXPIRED (TTL elapsed, no payment), CONVERTED (successfully became a Booking)
expires_at	TIMESTAMP	Not Null	TTL expiration time. Default: created_at + interval '30 minutes' for BOOKING; operator-configured for ONBOARD_SALE (see operator_settings.onboard_payment_link_ttl_minutes).
selected_options	JSONB		Typed structure: { passenger_count: int, boarding_point_id: uuid, is_door_pickup: boolean, door_pickup_address?: { formatted_address: string, lat: number, lng: number }, ancillary_ids: uuid[], seat_selections: [{ service_leg_id, seat_identifier }], demographic_breakdown: [{ demographic: string, count: int }] }. boarding_point_id always references backoffice.boarding_point_library. is_door_pickup indicates the pickup mode (stop vs. door). When is_door_pickup = true, door_pickup_address carries the geocoded passenger address validated against the stop's radius.
created_at	TIMESTAMP	Default: now()	Session creation timestamp

NOTE

Abandoned Cart / Payment Link Sweep: A Hasura Scheduled Trigger (checkout_abandoned_sweep) runs every 5 minutes. It sets status = 'EXPIRED' for all rows where status = 'ACTIVE' AND expires_at < now(). Event routing by session_type:

session_type	Event Emitted	Consumer	Side Effects
BOOKING	CheckoutAbandoned	Communications	Re-engagement workflow: Email at expired_at + 1h, WhatsApp at expired_at + 24h (if no response). Opt-out via Contact.marketing_consent.
ONBOARD_SALE	OnboardPaymentExpired	Operations	Sets onboard_sales.payment_status → FAILED, notifies driver via push. No re-engagement — driver decides next step.

bookings

The primary transactional container.

Column	Type	Constraints	Description
id	UUID	Primary Key	Unique booking identifier
tenant_id	UUID	Not Null	Soft FK to backoffice.operators
tour_offering_id	UUID	Foreign Key (tour_offerings.id), Not Null	Booked offering
reseller_id	UUID		Soft FK to backoffice.resellers
reference_number	VARCHAR	Unique, Not Null	Look-up reference for bank imports
source_channel	VARCHAR	Not Null	Enum of booking origin
status	VARCHAR	Not Null	Enum: DRAFT, PENDING_PAYMENT, DEPOSIT_PAID, FULLY_PAID, COMPLETED, CANCELLED, REFUNDED, NO_SHOW. See booking-lifecycle-protocol.md for the full state machine, Hasura Actions, and Scheduled Triggers.
total_amount	DECIMAL	Not Null	Total transaction amount
currency	VARCHAR	Default: EUR
created_at	TIMESTAMP	Default: now()

passengers

The individual traveler on a booking.

Column	Type	Constraints	Description
id	UUID	Primary Key	Unique passenger identifier
tenant_id	UUID	Not Null	Soft FK to backoffice.operators
booking_id	UUID	Foreign Key (bookings.id), Not Null	The parent booking
passenger_profile_id	UUID		Soft FK to backoffice.passenger_profiles
boarding_point_id	UUID		Soft FK to backoffice.boarding_point_library. Always references a single table — no polymorphic FK.
is_door_pickup	BOOLEAN	Not Null, Default: false	Indicates pickup mode: false = boards at the physical stop, true = door pickup at their address.
door_pickup_address	JSONB	Nullable	{ formatted_address: string, lat: number, lng: number }. Set when is_door_pickup = true. The geocoded passenger address validated against the stop's door_pickup_radius_km.
is_primary_contact	BOOLEAN	Default: false	Distinguishes billing contact
first_name	VARCHAR	Not Null
last_name	VARCHAR	Not Null
email	VARCHAR		Highly recommended for primary contact
phone	VARCHAR
date_of_birth	DATE		Required for border manifests
document_number	VARCHAR		Passport/ID for manifests
nationality	VARCHAR
status	VARCHAR	Not Null, Default: ACTIVE	ACTIVE or CANCELLED. Enables partial cancellation — remove one passenger without cancelling the entire booking.

payments

Granular tracking for transactions via Mollie Marketplaces. Supports split payments (deposit + final payment) and refunds. The backoffice.operator_integrations table (type = MOLLIE) stores Mollie sub-account configuration — see schema-backoffice.md §operator_integrations. For the full Mollie integration spec (webhook mapping, sub-account provisioning, payout scheduling), see PRODUCT_mollie-integration.md.

Column	Type	Constraints	Description
id	UUID	Primary Key	Unique payment identifier
tenant_id	UUID	Not Null	Soft FK to backoffice.operators
booking_id	UUID	Foreign Key (bookings.id), Not Null	Parent booking
provider	VARCHAR	Not Null	MOLLIE (Standard platform processing)
provider_transaction_id	VARCHAR	Unique	Mollie transaction ID
payment_type	VARCHAR	Not Null	DEPOSIT, FINAL_PAYMENT, REFUND (full), or PARTIAL_REFUND (single passenger/ancillary)
refund_passenger_id	UUID	Foreign Key (passengers.id)	Nullable. Links a PARTIAL_REFUND to the specific passenger removed.
refund_ancillary_id	UUID	Foreign Key (ancillaries.id)	Nullable. Links a PARTIAL_REFUND to the specific ancillary removed.
payment_method	VARCHAR		Actual method used: SEPA, PAYPAL, APPLE_PAY, GOOGLE_PAY, KLARNA, CREDIT_CARD, IDEAL. Populated from Mollie webhook method field.
amount	DECIMAL	Not Null	Transaction amount
currency	VARCHAR	Not Null	ISO 4217
status	VARCHAR	Not Null	PENDING, COMPLETED, FAILED, REFUNDED
processed_at	TIMESTAMP		Timestamp of final status from Mollie webhook

ancillaries

Upsells added to a booking. Each ancillary has an independent lifecycle to support partial cancellation/refund of individual upsells.

The type column defines the behavioral category (determines system behavior like refund rules and reporting). The label column is operator-defined (the display name passengers see, e.g., "Reiserücktrittsversicherung" or "Premium Sitzplatz"). Operators configure their available ancillaries per TourTemplate via ancillary-catalog.md §Template-Level Assignment.

Column	Type	Constraints	Description
id	UUID	Primary Key	Unique ancillary identifier
tenant_id	UUID	Not Null	Soft FK to backoffice.operators
booking_id	UUID	Foreign Key (bookings.id), Not Null	Parent booking
type	VARCHAR	Not Null	Behavioral category: INSURANCE, UPGRADE (room/seat), EXTRA_LUGGAGE, EXCURSION, MEAL, BOARDING_SURCHARGE, OTHER. Determines system behavior (refund eligibility, reporting bucket). BOARDING_SURCHARGE is auto-created by Commerce when a passenger selects a boarding point with a surcharge — see boarding-points.md §Door Pickup Mechanics.
label	VARCHAR	Not Null	Operator-defined display name shown to passengers (e.g., "Einzelzimmerzuschlag", "Ganztagesausflug Venedig")
quantity	INT	Not Null, Default: 1	Number of units (e.g., 2 extra luggage allowances)
price	DECIMAL	Not Null	Unit price of the upsell
currency	VARCHAR	Not Null	ISO 4217
status	VARCHAR	Not Null, Default: ACTIVE	ACTIVE, CANCELLED, REFUNDED. Independent of parent Booking status.
catalog_item_id	UUID	Nullable	Soft FK to backoffice.ancillary_catalog_items. Set when the ancillary originates from the operator's catalog. Null for system-generated ancillaries (e.g., BOARDING_SURCHARGE) or manually added items. Enables Backoffice → Commerce traceability.

seat_reservations

Time-locked capacity holds for operations.

Column	Type	Constraints	Description
id	UUID	Primary Key	Unique reservation identifier
tenant_id	UUID	Not Null	Soft FK to backoffice.operators
service_leg_id	UUID	Not Null	Soft FK to operations.service_legs
passenger_id	UUID	Foreign Key (passengers.id)	Assigned passenger
seat_identifier	VARCHAR	Not Null	Key mapping to layout
status	VARCHAR	Not Null	HELD, CONFIRMED, RELEASED
hold_expires_at	TIMESTAMP		Expiration for HELD seats

IMPORTANT

Partial unique index: UNIQUE(service_leg_id, seat_identifier) WHERE status IN ('HELD', 'CONFIRMED'). Prevents double-booking the same physical seat on the same leg. The partial unique index excludes RELEASED reservations — the system can release a seat and re-hold it for a different passenger.

NOTE

Seat Hold Cleanup: A Hasura Scheduled Trigger (seat_hold_cleanup) runs every 60 seconds. It sets status = 'RELEASED' for all rows where status = 'HELD' AND hold_expires_at < now(). This prevents ghost holds from blocking inventory. The hold_expires_at TTL is set to match the checkout_sessions.expires_at value (default: 30 minutes) to prevent users from losing their seat hold while still in an active checkout session — see ADR-013.

NOTE

Vehicle Swap Remapping: During vehicle swaps, the swap handler remaps seat_reservations to the new vehicle's seat map via a 3-phase algorithm (match-by-ID → match-by-type → downgrade). The algorithm sets reservations that cannot match to RELEASED. See vehicle-swap-protocol.md for the algorithm, priority rules, and edge states.

tickets

Digital fulfillment and boarding pass.

Column	Type	Constraints	Description
id	UUID	Primary Key	Unique ticket identifier
tenant_id	UUID	Not Null	Soft FK to backoffice.operators
passenger_id	UUID	Foreign Key (passengers.id), Not Null	Ticket owner
ticket_number	VARCHAR	Unique, Not Null	Human-readable identifier
qr_hash	VARCHAR	Unique, Not Null	Validation hash
status	VARCHAR	Not Null, Default: ACTIVE	ACTIVE or VOIDED. Voided tickets fail QR validation at boarding. Voided on booking cancellation or passenger removal.
issued_at	TIMESTAMP	Default: now()

financial_ledgers

The Nachkalkulation (actuals vs planned margins). One row per TourOffering. Auto-created when the first booking reaches DEPOSIT_PAID. The system aggregates revenue from completed Payment records; the system aggregates expenses from ExpenseSubmitted and OnboardSaleRecorded domain events consumed from Operations via Hasura Event Triggers.

Column	Type	Constraints	Description
id	UUID	Primary Key	Unique ledger identifier
tenant_id	UUID	Not Null	Soft FK to backoffice.operators
tour_offering_id	UUID	Foreign Key (tour_offerings.id), Unique, Not Null	Parent departure/offering (one ledger per offering)
costing_sheet_id	UUID	Not Null	Soft FK to backoffice.costing_sheets — the Soll/Ist comparison baseline
planned_price_matrix_version_id	UUID	Not Null	Soft FK to backoffice.price_matrices — the PriceMatrix version used to compute planned_revenue. Immutable after creation.
status	VARCHAR	Not Null, Default: OPEN	OPEN (accepting event-driven mutations) or CLOSED (period-locked, finalized)
realized_revenue	DECIMAL	Default: 0	Actual revenue = Σ Payment.amount WHERE status = COMPLETED across all bookings for this offering
realized_expense	DECIMAL	Default: 0	Actual expenses = Σ from ExpenseSubmitted + OnboardSaleRecorded events (Operations context)
planned_cost	DECIMAL	Not Null	Immutable snapshot of CostingSheet.total_net_cost at ledger creation — provides the Soll baseline for cost comparison
planned_revenue	DECIMAL	Not Null	Immutable snapshot at ledger creation. V1: ADULT variant list_price × CapacityRule.max_capacity — upper-bound Soll baseline assuming 100% occupancy at highest-priced demographic. Version tracked via planned_price_matrix_version_id. V2 will expand to per-variant computation: Σ(PriceMatrix.variant.gross_price × expected pax per variant).
cost_delta	DECIMAL		realized_expense − planned_cost. Positive = overspent vs. plan, negative = underspent.
revenue_delta	DECIMAL		realized_revenue − planned_revenue. Positive = oversold/higher yield, negative = undersold vs. plan.
margin_delta	DECIMAL		(realized_revenue − realized_expense) − (planned_revenue − planned_cost). Positive = outperforming plan, negative = underperforming.
currency	VARCHAR	Not Null, Default: EUR	ISO 4217 currency code
created_at	TIMESTAMPTZ	Default: now()
closed_at	TIMESTAMPTZ		Timestamp of finalization (set when status transitions to CLOSED)

tax_ledger_entries

Calculated taxation burdens. The system structures fields to satisfy the mandatory recording requirements of § 25 Abs. 5 UStG. Relationship: 1:N — a single FinancialLedger can have multiple entries when a departure mixes tax strategies (e.g., MARGIN_SCHEME_25 for tour margin + STANDARD_VAT for onboard sales).

Column	Type	Constraints	Description
id	UUID	Primary Key	Unique tax entry identifier
tenant_id	UUID	Not Null	Soft FK to backoffice.operators
financial_ledger_id	UUID	Foreign Key (financial_ledgers.id), Not Null	Parent ledger
tax_strategy	VARCHAR	Not Null	STANDARD_VAT or MARGIN_SCHEME_25
customer_gross_amount	DECIMAL	Not Null	§ 25 Abs. 5 Nr. 1: Der Betrag, den der Leistungsempfänger aufwendet
procurement_gross_amount	DECIMAL	Not Null	§ 25 Abs. 5 Nr. 2: Die Aufwendungen für Reisevorleistungen (brutto, ohne VSt-Abzug)
margin_taxable_net	DECIMAL	Not Null	§ 25 Abs. 5 Nr. 3/4: Steuerpflichtige Marge (Netto, EU). Bei Negativmarge = 0
margin_exempt_net	DECIMAL	Not Null	§ 25 Abs. 5 Nr. 4: Steuerfreie Marge (Drittlands-Vorleistungen, § 25 Abs. 2)
tax_base_amount	DECIMAL	Not Null	Bemessungsgrundlage = margin_taxable_net
tax_amount	DECIMAL	Not Null	Computed tax = margin_taxable_net × tax_rate
tax_rate	DECIMAL	Not Null	Applicable rate (0.19 EU, 0.0 Drittland)
created_at	TIMESTAMPTZ	Default: now()	Immutable creation timestamp for audit

IMPORTANT

§ 25 Abs. 5 UStG Aufzeichnungspflicht: Aus den Aufzeichnungen müssen vier exakte Werte hervorgehen: (1) Kundenaufwand, (2) Vorleistungskosten, (3) Bemessungsgrundlage, (4) Aufteilung steuerpflichtig/steuerfrei. Diese dürfen nicht nur on-the-fly berechnet werden, sondern müssen als unveränderliche Spalten persistiert werden.

invoices

Consolidated billing information and e-invoicing artifacts. The system generates invoices as PDFs directly from aggregated TaxLedgerEntry and Booking data without a persistent invoice_line_items table. At generation time, the system snapshots volatile data (supplier, recipient, line items) into JSONB columns to ensure GoBD-compliant immutability — even if source entities change later, the invoice remains unchanged. See invoice-service-protocol.md for the full § 14 UStG field mapping, InvoiceService contract, and credit note mechanism.

IMPORTANT

Invoice Numbering Strategy (GoBD): Tenant-scoped gap-free sequential numbering via a tenant_invoice_sequences helper table with SELECT ... FOR UPDATE row-level lock pattern. Format: {tenant_prefix}-{year}-{sequence} (e.g., BUS-2026-00042). The sequence resets per fiscal year per tenant configuration. This ensures GoBD-compliant uninterrupted numbering under concurrent inserts.

Column	Type	Constraints	Description
id	UUID	Primary Key	Unique invoice identifier
tenant_id	UUID	Not Null	Soft FK to backoffice.operators
financial_ledger_id	UUID	Foreign Key (financial_ledgers.id)	Parent ledger
booking_id	UUID	Foreign Key (bookings.id), Not Null	Parent booking (1:1 — one non-cancelled invoice per booking)
invoice_number	VARCHAR	Unique, Not Null	Human-readable ID (Gap-free sequence)
issue_date	DATE	Not Null
due_date	DATE	Not Null
cancelled	BOOLEAN	Default: false	Indicator for Storno workflow
status	VARCHAR	Not Null	DRAFT, ISSUED, PAID, VOIDED
supplier_snapshot	JSONB	Not Null	Operator info at generation time: { company_name, address, tax_number, vat_id } from backoffice.operators
recipient_snapshot	JSONB	Not Null	Primary passenger info at generation time: { first_name, last_name, address }
line_items_snapshot	JSONB	Not Null	Rendered line items array. Structure: [{ position, description, quantity, unit_price, net_amount, tax_rate, tax_amount, gross_amount, tax_strategy }]
total_net	DECIMAL	Not Null	Sum of net amounts across all line items
total_tax	DECIMAL	Not Null	Sum of tax amounts
total_gross	DECIMAL	Not Null	Grand total (total_net + total_tax)

tenant_invoice_sequences

Helper table for GoBD-compliant gap-free sequential invoice numbering. Each row represents one tenant's counter for one fiscal year. The SELECT ... FOR UPDATE pattern ensures that concurrent invoice generation serializes at the DB level, preventing gaps.

Column	Type	Constraints	Description
tenant_id	UUID	Composite PK	Soft FK to backoffice.operators
fiscal_year	INT	Composite PK	Fiscal year for this sequence
last_number	INT	Not Null, Default: 0	Last assigned sequence number

ledger_period_locks

Immutable lock mechanism ensuring no one can alter financial ledgers and invoices after a period closes or exports to DATEV.

Column	Type	Constraints	Description
id	UUID	Primary Key	Unique lock identifier
tenant_id	UUID	Not Null	Soft FK to backoffice.operators
period_start	DATE	Not Null	Start of locked period
period_end	DATE	Not Null	End of locked period
lock_type	VARCHAR	Not Null	EXPORT (auto-locked via DATEV export) or MANUAL
locked_at	TIMESTAMP	Default: now()
locked_by	UUID		Soft FK to backoffice.users

invoice_cancellations

Tracks the GoBD-compliant cancellation (Storno) of a finalized invoice.

Column	Type	Constraints	Description
id	UUID	Primary Key	Unique cancellation identifier
tenant_id	UUID	Not Null	Soft FK to backoffice.operators
cancelled_invoice_id	UUID	Foreign Key (invoices.id)	The voided invoice
replacement_invoice_id	UUID	Foreign Key (invoices.id)	Optional linkage to the corrected new invoice
reason	TEXT	Not Null	Business reason for cancellation
cancelled_at	TIMESTAMP	Default: now()

reconciliation_uploads

Records imports of external general ledger data (BWA/SuSa from DATEV) for Data Drift management.

WARNING

Subscription Tier Gating: reconciliation_uploads are an advanced B2B accounting feature. Insert operations require a PRO or ENTERPRISE plan (X-Hasura-Plan). See L3-6.2.2 Schema Matrix Enforcement for details.

Column	Type	Constraints	Description
id	UUID	Primary Key	Unique upload identifier
tenant_id	UUID	Not Null	Soft FK to backoffice.operators
period	VARCHAR	Not Null	Target financial period (e.g., 2026-04)
uploaded_file_url	VARCHAR		Storage reference
parsed_data	JSONB		Raw parsed rows
status	VARCHAR	Not Null	PENDING, PROCESSED, FAILED
uploaded_at	TIMESTAMP	Default: now()

reconciliation_entries

Line-item comparisons surfacing Soll/Ist deviations between Commerce actuals and DATEV bookings.

Column	Type	Constraints	Description
id	UUID	Primary Key	Unique entry identifier
tenant_id	UUID	Not Null	Soft FK to backoffice.operators
reconciliation_upload_id	UUID	Foreign Key (reconciliation_uploads.id)	Parent upload
account_code	VARCHAR	Not Null	e.g. SKR03/04 code
busflow_amount	DECIMAL		Accumulated value in Busflow
datev_amount	DECIMAL		Value reported from DATEV
delta	DECIMAL		Absolute variance

change_events

Local audit trail for entities within the Commerce schema. Tracks GoBD-relevant mutations on invoices and ledgers. Uses a polymorphic entity reference (entity_type + entity_id) — see ADR-019. The shared AuditTrailService (NestJS) writes all entries.

Column	Type	Constraints	Description
id	UUID	Primary Key	Unique event
tenant_id	UUID	Not Null	Soft FK to backoffice.operators
user_id	UUID	Nullable	Soft FK to auth.users(id). The actor — null for system/automation.
entity_type	VARCHAR	Not Null	Target entity type: tour_offering, booking, payment, ticket, invoice, financial_ledger, tax_ledger_entry, ledger_period_lock
entity_id	UUID	Not Null	ID of the mutated entity. Soft FK — entity table varies by entity_type.
action	VARCHAR	Not Null	INSERT, UPDATE, DELETE
scope	VARCHAR	Not Null, Default: GENERAL	GOBD, COMPLIANCE, DSGVO, CONFIG, GENERAL — canonical enum per ADR-019.
correlation_id	UUID	Nullable	Groups related change_events across bounded contexts (e.g., vehicle swap writes to Operations + Commerce).
old_values	JSONB		Full row snapshot before mutation. Captured via SELECT ... FOR UPDATE in the AuditTrailService. Null for CREATE.
new_values	JSONB		State after mutation. Null for DELETE.
created_at	TIMESTAMPTZ	Default: now()

NOTE

Index: (tenant_id, entity_type, entity_id) — covers all audit trail queries. Additional partial index: (correlation_id) WHERE correlation_id IS NOT NULL.

NOTE

Scope mapping for this schema: GOBD for invoice, financial_ledger, tax_ledger_entry, payment, ledger_period_lock. GENERAL for everything else.

---

Read Models & Views

The following read-model tables exist within the commerce schema to enable cross-context reads without hard foreign keys, per the CQRS read model pattern.

NOTE

Boarding point data is denormalized as available_boarding_points JSONB on tour_offerings (see §tour_offerings). No separate SQL view or read-model table exists for boarding points — the booking widget reads directly from the tour_offerings row. This replaces the earlier pickup_options_view design.

tour_offering_prices

A read-model table (not a SQL view) synced from the Backoffice PriceMatrix via PriceMatrixPublished domain events. Stores flattened price variants for zero-latency storefront reads. Checkout validates the cart's price against the authoritative price_matrix_version_id — mismatches trigger a hard reject and cart refresh. See costing-pricing-separation ADR.

Column	Type	Constraints	Description
id	UUID	Primary Key	Unique price record
tenant_id	UUID	Not Null	Soft FK to backoffice.operators
tour_offering_id	UUID	Foreign Key (tour_offerings.id), Not Null	Parent offering
price_matrix_version_id	UUID	Not Null	Soft FK to backoffice.price_matrices — exact version used for checkout validation
channel	VARCHAR	Not Null, Default: DEFAULT	Sales channel
variants	JSONB	Not Null	Flattened price variants: [{ room_type, demographic, gross_price, net_price, tax_amount }]
list_price	DECIMAL		Base variant gross price for quick display
currency	VARCHAR	Not Null, Default: EUR
synced_at	TIMESTAMPTZ	Not Null	Last sync timestamp from PriceMatrixPublished event

---

Architectural Notes

NOTE

Boarding point ownership (resolved): Boarding point data lives in a single Backoffice table: boarding_point_library (operator-level pickup locations with optional door pickup per stop). Templates reference library items via template_boarding_point_assignments, which also controls per-template origin designation and surcharge overrides. At publish time, the TripPublished event carries the resolved boarding point catalog, and Commerce denormalizes it as available_boarding_points JSONB on tour_offerings. See boarding-points.md for the full data model and adr-001-boarding-point-strategy.md for the cross-context pattern.