Backoffice Database Schema

This document details the concrete physical database schema for the Backoffice Bounded Context (schema: backoffice). The Backoffice schema serves as the root authority for business configuration, operational staff, abstract product definitions, third-party inventory, CRM, and all financial planning/quoting.

Physical Entity Relationship Diagram

erDiagram
    OPERATOR ||--o{ USER_TENANT_ASSIGNMENT : "grants access"
    AUTH_USER ||--o{ USER_TENANT_ASSIGNMENT : "assigned to"
    AUTH_USER ||--o{ USER_ACCESS_GRANT : "receives"
    OPERATOR ||--o{ CREW_MEMBER : "employs"
    AUTH_USER ||--o| CREW_MEMBER : "linked to"
    OPERATOR ||--o{ VEHICLE : "owns"
    OPERATOR ||--o{ SUPPLIER : "contracts"
    OPERATOR ||--o{ RESELLER : "partners"
    OPERATOR ||--o{ PASSENGER_PROFILE : "manages"
    OPERATOR ||--o{ NOTIFICATION_TEMPLATE : "configures"
    OPERATOR ||--o{ COSTING_SHEET : "creates"
    OPERATOR ||--o{ TOUR_TEMPLATE : "defines"
    OPERATOR ||--o{ OPERATOR_INTEGRATIONS : "configures"
    OPERATOR ||--|| OPERATOR_SETTINGS : "configures"
    OPERATOR ||--o| TENANT_SUBSCRIPTION : "subscribes"

    SUPPLIER ||--o{ ALLOTMENT : "provides"
    COSTING_SHEET ||--o| TOUR_TEMPLATE : "baseline budget"
    COSTING_SHEET ||--o| TOUR_DEPARTURE : "instance budget"
    COSTING_SHEET ||--|| CHARTER_QUOTE : "custom budget"
    COSTING_SHEET ||--o{ PRICE_MATRIX : "generates"
    TOUR_TEMPLATE ||--o{ TOUR_DEPARTURE : "schedules"
    OPERATOR ||--o{ BOARDING_POINT_STOP : "maintains"
    OPERATOR ||--o{ DOOR_PICKUP_ZONE : "defines"
    TOUR_TEMPLATE ||--o{ TEMPLATE_BOARDING_POINT_ASSIGNMENT : "assigns stops"
    TOUR_TEMPLATE ||--o{ TEMPLATE_DOOR_PICKUP_ZONE_ASSIGNMENT : "enables zones"
    RESELLER ||--o{ CHARTER_QUOTE : "requests"

    CREW_MEMBER ||--o{ CREW_QUALIFICATION : "holds"
    CREW_MEMBER ||--o{ CREW_ABSENCE : "records"
    VEHICLE ||--o{ VEHICLE_INSPECTION : "schedules"
    OPERATOR ||--o{ IMPORT_JOB : "uploads"
    IMPORT_JOB ||--o{ IMPORT_ERROR_ROW : "captures failures"

Table Definitions

operators

The root tenant entity representing a bus tour company. All domain entities reference the operator via tenant_id. Lifecycle: ONBOARDING → ACTIVE → SUSPENDED → CHURNED.

Column	Type	Constraints	Description
id	UUID	Primary Key	Unique operator identifier
name	VARCHAR	Not Null	Company trading name
legal_name	VARCHAR	Not Null	Registered legal company name (for invoices)
address	VARCHAR		Registered business address
country	VARCHAR	Not Null	ISO 3166-1 alpha-2 country code
default_locale	VARCHAR	Not Null, Default: de-DE	Default language/locale for the tenant
default_currency	VARCHAR	Not Null, Default: EUR	ISO 4217 currency code
vat_id	VARCHAR		EU VAT identification number (USt-IdNr.)
tax_id	VARCHAR		National tax identification number
status	VARCHAR	Not Null, Default: ACTIVE	ONBOARDING (post-signup, pre-first-login), ACTIVE, SUSPENDED (non-payment or manager action), CHURNED (cancellation or GDPR deletion)
legal_form	VARCHAR	Foreign Key (legal_forms.id), Nullable	Reference to a localized legal form (e.g., GmbH, UG, Einzelunternehmen). A global reference table stores legal forms with localized labels. Affects invoice legal text and DATEV mapping.
brand_config	JSONB		[planned] — white-label theming: { logo_url, primary_color, accent_color }. Extended shape TBD during booking widget implementation.
bank_details	JSONB		[planned] — SEPA payout data: { iban, bic, account_holder }. Not PCI-scoped. Volume-level AES-256 encryption sufficient. Consumed by DATEV export.
subscription_tier	VARCHAR	Not Null, Default: CORE	[planned] — Denormalized from tenant_subscriptions.plan_id. The Nhost custom claims webhook reads this column during JWT issuance to inject X-Hasura-Plan. Authoritative source: tenant_subscriptions. Tier values TBD by product.
deposit_config	JSONB	Nullable	Operator-level default deposit configuration: { percentage: number, type: 'PERCENTAGE' | 'FIXED', min_amount: number | null }. Overridable per TourTemplate. See PRODUCT_mollie-integration.md §3 for the cascading resolution logic.
final_payment_config	JSONB	Nullable	Operator-level final payment timing: { reminder_days_before_start: number, escalation_days_before_start: number, flag_days_before_start: number }. Overridable per TourTemplate. See PRODUCT_mollie-integration.md §4.2.
cancellation_policy	JSONB	Nullable	Tenant-level default cancellation fee schedule (CancellationPolicy VO): { tiers: [{ days_before_start: number, fee_percentage: number }], minimum_fee: number | null, currency: string }. Overridable per TourTemplate. See cancellation-protocol.md §CancellationPolicy for resolution cascade and runtime calculation.
ticket_issuance_trigger	VARCHAR	Default: DEPOSIT_PAID	DEPOSIT_PAID or FULLY_PAID. Controls when the system issues tickets for this operator's bookings. Overridable per TourTemplate. See ADR-014.
created_at	TIMESTAMP	Default: now()	Record creation timestamp
updated_at	TIMESTAMP	Default: now()	Record last update timestamp

operator_integrations

Third-party integration status tracking per tenant. Replaces single-purpose provider columns with a generic, extensible registry pattern. Seeded during ProvisionTenant (see ADR-003). The UI reads status to gate provider-dependent features (e.g., publishing TourDepartures to Commerce requires Mollie CONNECTED).

Column	Type	Constraints	Description
id	UUID	Primary Key	Unique integration record
tenant_id	UUID	Foreign Key (operators.id), Not Null	Owning tenant
integration_type	VARCHAR	Not Null	Integration provider: MOLLIE, DPA, DATEV, META_WHATSAPP, AWS_SES, AWS_SNS (extensible enum)
status	VARCHAR	Not Null, Default: PENDING	PENDING (provisioned, awaiting connection), CONNECTED (active and functional), FAILED (connection failed after retry exhaustion), DISCONNECTED (manually disabled or revoked)
external_id	VARCHAR	Nullable	External system identifier (e.g., Mollie merchant/organization ID)
config	JSONB	Nullable	Integration-specific configuration. Shape varies by integration_type — see typed interfaces below.
error_message	TEXT	Nullable	Last error message (populated when status transitions to FAILED)
connected_at	TIMESTAMPTZ	Nullable	When status transitioned to CONNECTED
created_at	TIMESTAMPTZ	Default: now()	Record creation
updated_at	TIMESTAMPTZ	Default: now()	Last update

IMPORTANT

Unique constraint: (tenant_id, integration_type) — one record per integration type per tenant.

WARNING

Subscription Tier Gating: Premium B2B integrations (DATEV, META_WHATSAPP, AWS_SES) require a PRO or ENTERPRISE plan. The system enforces this during Row-Level capability checks (X-Hasura-Plan). See L3-6.2.2 Schema Matrix Enforcement for rules.

Known config Shapes

MOLLIE — see PRODUCT_mollie-integration.md §2.1:

interface MollieIntegrationConfig {
  organization_id: string;           // Mollie sub-account ID
  onboarding_status: 'PENDING' | 'COMPLETED' | 'NEEDS_DATA';
}

DATEV:

interface DATEVIntegrationConfig {
  consultant_number: string;         // Beraternummer
  client_number: string;             // Mandantennummer
}

DPA:

interface DPAIntegrationConfig {
  document_url: string;              // Signed DPA document storage URL
  signed_at: string;                 // ISO 8601 timestamp
}

META_WHATSAPP — see channel-provisioning-protocol.md §9:

interface MetaWhatsAppIntegrationConfig {
  waba_id: string;                   // Meta WhatsApp Business Account ID
  phone_number_display: string;      // Formatted phone number for UI display
  business_verification_status: 'NOT_VERIFIED' | 'PENDING' | 'VERIFIED';
}

AWS_SES — see channel-provisioning-protocol.md §9:

interface AwsSesIntegrationConfig {
  domain: string;                    // Verified sending domain
  sender_email: string;              // Primary sender email address
  dkim_status: 'PENDING' | 'SUCCESS' | 'FAILED';
}

AWS_SNS — see channel-provisioning-protocol.md §9:

interface AwsSnsIntegrationConfig {
  sender_id: string;                 // Registered Sender ID
  sender_id_type: 'PLATFORM' | 'OPERATOR'; // Phase 1: always PLATFORM
}

operator_settings

Tenant-scoped operational configuration. One row per operator — created during tenant provisioning. Settings referenced across bounded contexts (Operations, Commerce) read this table via soft FK. Separating settings from the operators table provides updated_at tracking per config change and avoids bloating the root entity with JSONB config columns.

Column	Type	Constraints	Description
id	UUID	Primary Key	Unique settings identifier
tenant_id	UUID	Foreign Key (operators.id), Unique, Not Null	Owning tenant (1:1 with operator)
driver_cash_refund_enabled	BOOLEAN	Not Null, Default: true	Whether drivers can perform cash refunds on onboard sales
driver_cash_refund_limit	DECIMAL	Nullable	Maximum amount a driver can refund (null = unlimited). Default: 50.00.
onboard_payment_link_ttl_minutes	INT	Not Null, Default: 60	TTL for onboard payment link CheckoutSessions (min: 15, max: 180). Longer than the standard 30 min — bus passengers may not check their phone immediately.
auto_refund_orphaned_onboard_payment	BOOLEAN	Not Null, Default: true	When a passenger pays for a voided OnboardSale (race condition), auto-initiate Mollie refund. If false, dispatcher reviews manually via dispatch board alert (OnboardPaymentOrphaned event).
created_at	TIMESTAMPTZ	Default: now()	Record creation
updated_at	TIMESTAMPTZ	Default: now()	Last update

NOTE

Provisioning: Seeded with defaults during ProvisionTenant (see ADR-003). Operators customize via the Backoffice workspace settings UI.

tenant_subscriptions

Billing plan state per operator. One row per tenant — created during ProvisionTenant with plan_id = CORE. The Nhost custom claims webhook queries this table to inject X-Hasura-Plan into the JWT session variables, enabling Hasura-level feature gating on premium tables and Actions.

Column	Type	Constraints	Description
id	UUID	Primary Key	Unique subscription identifier
tenant_id	UUID	Foreign Key (operators.id), Unique, Not Null	1:1 with operator
plan_id	VARCHAR	Not Null, Default: CORE	Tier values TBD by product (placeholder: CORE, PRO, ENTERPRISE)
status	VARCHAR	Not Null, Default: ACTIVE	ACTIVE, PAST_DUE
payment_provider_sub_id	VARCHAR	Nullable	External billing system reference (e.g., Stripe subscription ID)
created_at	TIMESTAMPTZ	Default: now()	Record creation
updated_at	TIMESTAMPTZ	Default: now()	Last update

NOTE

Provisioning: Seeded with plan_id = CORE, status = ACTIVE during ProvisionTenant (see ADR-003). Plan changes update both this table and the denormalized operators.subscription_tier column atomically.

tenant_scrub_logs

GDPR compliance proof for automated PII scrubbing. The pg_cron pipeline inserts one row per scrubbed entity to maintain an auditable ledger (as defined in L3-5.2.1), recording the UUID without storing any redacted PII. Retention: indefinite (compliance audit trail).

Column	Type	Constraints	Description
id	UUID	Primary Key	Unique log entry
tenant_id	UUID	Foreign Key (operators.id), Not Null	Scrubbed tenant
entity_table	VARCHAR	Not Null	Target entity table (e.g., commerce.passengers, commerce.invoices)
entity_id	UUID	Not Null	ID of the redacted entity
scrub_reason	VARCHAR	Not Null	Applied deletion policy label (e.g., ttl_3y_last_booking, ttl_10y_gobd)
scrubbed_at	TIMESTAMPTZ	Not Null, Default: now()	Pipeline execution timestamp

NOTE

Scope mapping: change_events for operator_settings and tenant_subscriptions use scope = CONFIG.

User Identity (Nhost auth.users)

IMPORTANT

No separate users table exists in the Backoffice schema. Nhost Auth manages user identity and authentication entirely via the auth.users table. The Busflow system references auth.users(id) as a foreign key for all user-related relationships.

Nhost's auth.users provides: id (UUID), email, display_name, default_role (Hasura role), disabled (boolean — replaces the previous ACTIVE/DEACTIVATED lifecycle), created_at, last_seen. Nhost also manages password hashing, SSO providers, and email verification.

Platform vs. tenant roles:

• Platform role (auth.users.default_role): user (regular) or busflow_staff (cross-tenant). Managed via Nhost Admin SDK.
• Tenant role (user_tenant_assignments.default_role): MANAGER, DISPATCHER, DRIVER. Injected into JWT custom claims.
• Capabilities (user_access_grants): Additive fine-grained grants. Resolved at application layer.

User deactivation: Handled via nhost.auth.manager.updateUser({ disabled: true }). Disabled users cannot log in. Active JWTs remain valid until expiry (max 15 min). For tenant-scoped removal, delete the user_tenant_assignment row — the user retains access to other tenants.

user_tenant_assignments

Many-to-many assignment of auth.users to operators (tenants). Each user has a default_role that determines their primary frontend access. In small operators, one person may fill multiple roles — admins grant additional capabilities via user_access_grants.

Column	Type	Constraints	Description
user_id	UUID	PK, Foreign Key (auth.users(id))	The assigned user (Nhost user ID)
tenant_id	UUID	PK, Foreign Key (operators.id)	The assigned tenant
default_role	VARCHAR	Not Null	Primary role: MANAGER, DISPATCHER, DRIVER. Determines x-hasura-default-role in JWT. [planned V1.1]: OWNER, VIEWER.
created_at	TIMESTAMP	Default: now()	Assignment creation timestamp
updated_at	TIMESTAMP	Default: now()	When the assignment was last modified

user_access_grants

Granular capability grants for users within a tenant. Enables small operators where one person fills multiple roles (e.g., an Manager who also dispatches and drives). Each grant adds a capability on top of the default_role.

Column	Type	Constraints	Description
id	UUID	Primary Key	Unique grant identifier
user_id	UUID	Foreign Key (auth.users(id)), Not Null	The user receiving the grant (Nhost user ID)
tenant_id	UUID	Foreign Key (operators.id), Not Null	The tenant scope
capability	VARCHAR	Not Null	Granted capability: DISPATCH, FLEET_MGMT, BOOKING_MGMT, DRIVER_APP, CREW_MGMT, FINANCIAL_REPORTS.
granted_at	TIMESTAMP	Default: now()	Grant creation timestamp
granted_by	UUID	Foreign Key (auth.users(id)), Nullable	The manager who created this grant (Nhost user ID)

NOTE

MANAGER implicitly has all capabilities. Access grants are additive — they never restrict, only extend what the default_role provides.

WARNING

RBAC × Hasura — Reevaluate During Implementation. The current model maps default_role (MANAGER, DISPATCHER, DRIVER) to separate Hasura roles with distinct permission sets. An alternative is a single TENANT_USER Hasura role with uniform tenant-scoped permissions, where the application layer handles capability differentiation (what a user can see/do) via user_access_grants. This simplifies Hasura metadata (one role config) but moves authorization complexity into NestJS. The right approach depends on Hasura's permission granularity for this use case and requires a decision during RBAC implementation.

crew_members

Operational staff record for drivers and guides. Linked to a users row for authentication (nullable — operators can register crew members before the system provisions their login). Lifecycle: ACTIVE → INACTIVE → TERMINATED.

Column	Type	Constraints	Description
id	UUID	Primary Key	Unique crew member identifier
tenant_id	UUID	Foreign Key (operators.id), Not Null	The tenant this crew member belongs to
user_id	UUID	Foreign Key (auth.users(id)), Unique, Nullable	Linked Nhost auth identity. Null if crew member registered but not yet given app access.
first_name	VARCHAR	Not Null	First name
last_name	VARCHAR	Not Null	Last name
role	VARCHAR	Not Null	DRIVER, GUIDE, DRIVER_GUIDE — the crew member's operational capability
status	VARCHAR	Not Null, Default: ACTIVE	ACTIVE, INACTIVE, TERMINATED — only ACTIVE members appear in dispatch
phone	VARCHAR		Contact phone number (used by dispatchers and Communications context)
email	VARCHAR		Contact email (notification delivery)
license_number	VARCHAR		Driving license number (basic field; granular qualifications in crew_qualifications)
license_expiry	DATE		Expiration date of the driving license
created_at	TIMESTAMP	Default: now()	Creation timestamp
updated_at	TIMESTAMP	Default: now()	Last update timestamp

vehicles

Physical fleet assets. Lifecycle: ACTIVE → IN_MAINTENANCE → DECOMMISSIONED.

Column	Type	Constraints	Description
id	UUID	Primary Key	Unique vehicle identifier
tenant_id	UUID	Foreign Key (operators.id), Not Null	The tenant this vehicle belongs to
license_plate	VARCHAR	Unique, Not Null	Vehicle license plate
model	VARCHAR	Not Null	Vehicle model (e.g., "Mercedes-Benz Tourismo")
vehicle_class	VARCHAR	Not Null	COACH, MINIBUS, VAN, DOUBLE_DECKER — determines routing profile (height/weight restrictions)
status	VARCHAR	Not Null, Default: ACTIVE	ACTIVE, IN_MAINTENANCE, DECOMMISSIONED — only ACTIVE vehicles appear in dispatch
transmission_type	VARCHAR	Not Null, Default: MANUAL	MANUAL, AUTOMATIC — matched against crew qualification restrictions
capacity	INT	Not Null	Maximum passenger capacity
current_mileage_km	INT		Current odometer reading. Updated from telemetry sync or manual entry. Used for mileage-based maintenance intervals.
seat_map_layout	JSONB		Dynamic seat layout. Structure: { seats: [{ id: string, row: int, col: int, type: "STANDARD" | "PREMIUM" | "WHEELCHAIR", label: string, accessible: bool }] }. The id field must match SeatReservation.seat_identifier in Commerce.
created_at	TIMESTAMP	Default: now()	Creation timestamp
updated_at	TIMESTAMP	Default: now()	Last update timestamp

suppliers

Third-party relationships essential for executing multi-day tours.

Column	Type	Constraints	Description
id	UUID	Primary Key	Unique supplier identifier
tenant_id	UUID	Foreign Key (operators.id), Not Null	The tenant this supplier belongs to
name	VARCHAR	Not Null	Supplier name
contact_email	VARCHAR		Contact email address
service_type	VARCHAR	Not Null	HOTEL, FERRY, GUIDE, etc.
created_at	TIMESTAMP	Default: now()	Creation timestamp
updated_at	TIMESTAMP	Default: now()	Last update timestamp

allotments

Reserved inventory blocks (e.g., hotel rooms, ferry slots).

Column	Type	Constraints	Description
id	UUID	Primary Key	Unique allotment identifier
tenant_id	UUID	Foreign Key (operators.id), Not Null	The tenant this allotment belongs to
supplier_id	UUID	Foreign Key (suppliers.id), Not Null	The supplier providing the allotment
status	VARCHAR	Not Null, Default: ACTIVE	ACTIVE, CONSUMED, EXPIRED, CANCELLED
start_date	DATE	Not Null	Allotment availability start
end_date	DATE	Not Null	Allotment availability end
capacity	INT	Not Null	Amount of inventory reserved
net_rate	DECIMAL	Not Null	Net price per unit
currency	VARCHAR	Not Null	ISO 4217 currency code (e.g., EUR, CZK)
created_at	TIMESTAMP	Default: now()	Creation timestamp
updated_at	TIMESTAMP	Default: now()	Last update timestamp

resellers

Business clients (travel agencies, corporate accounts, tourism boards).

Column	Type	Constraints	Description
id	UUID	Primary Key	Unique reseller identifier
tenant_id	UUID	Foreign Key (operators.id), Not Null	The tenant this reseller belongs to
name	VARCHAR	Not Null	Reseller name
reseller_type	VARCHAR	Not Null	AGENCY or CORPORATE
commission_rate	DECIMAL		Commission percentage or amount
created_at	TIMESTAMP	Default: now()	Creation timestamp
updated_at	TIMESTAMP	Default: now()	Last update timestamp

passenger_profiles

The central CRM record for a traveler.

Column	Type	Constraints	Description
id	UUID	Primary Key	Unique profile identifier
tenant_id	UUID	Foreign Key (operators.id), Not Null	The tenant managing this profile
email	VARCHAR		Traveler email
phone	VARCHAR		Traveler phone number
first_name	VARCHAR	Not Null	First name
last_name	VARCHAR	Not Null	Last name
date_of_birth	DATE		Required for demographic pricing (age brackets) and border manifests
dietary_needs	JSONB		Structured dietary requirements
created_at	TIMESTAMP	Default: now()	Creation timestamp
updated_at	TIMESTAMP	Default: now()	Last update timestamp

boarding_point_library

Operator-level library of pickup locations. Each entry represents a physical stop that optionally also offers door pickup in its surrounding area. Stops belong to the operator and are reusable across templates via template_boarding_point_assignments. The 3-tier pickup model (Origin → Extra Stops → Door Pickup) is defined in boarding-points.md.

Column	Type	Constraints	Description
id	UUID	Primary Key	Unique library item
tenant_id	UUID	Foreign Key (operators.id), Not Null	Owning tenant
name	VARCHAR	Not Null	Display name (e.g., "Dorfplatz Nachbardorf")
address	VARCHAR		Full address string
geo_coordinates	JSONB		{ lat: number, lng: number } for map display, routing, and door pickup radius validation. Required when door_pickup_available = true (radius validation needs a center point).
zone_label	VARCHAR	Nullable	Grouping label (e.g., "Nachbardorf"). Provides implicit UI grouping — not a FK to a separate entity.
surcharge	DECIMAL	Not Null, Default: 0	Stop pickup surcharge in cents. €0 for stops that typically serve as the origin.
door_pickup_available	BOOLEAN	Not Null, Default: false	Whether this location offers door pickup in its surrounding area.
door_pickup_surcharge	DECIMAL	Nullable	Door pickup premium in cents (e.g., 1500 = €15). Standalone surcharge — not added on top of stop surcharge. Null if door_pickup_available = false.
door_pickup_radius_km	DECIMAL	Nullable	Max distance from stop's geo_coordinates for valid door pickup addresses. Null if door_pickup_available = false.
passenger_instructions	TEXT	Nullable	Traveler-facing guidance (e.g., "South exit, look for BusFlow sign")
is_archived	BOOLEAN	Not Null, Default: false	Soft-delete. Archived items remain on existing template assignments. They disappear from pickers for new assignments.
created_at	TIMESTAMP	Default: now()
updated_at	TIMESTAMP	Default: now()

NOTE

Unique constraint: (tenant_id, name) — prevents duplicate stop names per tenant.

NOTE

No type enum. Whether a stop serves as the origin (free, main departure) or an extra stop (surcharge) is a per-template decision via is_origin on the template assignment. This allows multi-city operators to use different stops as the origin on different tours.

NOTE

No boarding_order. Boarding order and pickup times are operational concerns that depend on the finalized passenger list and actual door pickup bookings. Both are deferred to [v0.2] as a dispatch-side design. See boarding-points.md §Resolution Cascade.

NOTE

CHECK constraint: door_pickup_available = true OR (door_pickup_surcharge IS NULL AND door_pickup_radius_km IS NULL) — ensures door pickup fields are null when the feature is disabled.

template_boarding_point_assignments

Which boarding point library items are assigned to which tour template, with per-template configuration.

Column	Type	Constraints	Description
id	UUID	Primary Key	Unique assignment
tenant_id	UUID	Foreign Key (operators.id), Not Null	Owning tenant
tour_template_id	UUID	Foreign Key (tour_templates.id), Not Null	Target template
boarding_point_id	UUID	Foreign Key (boarding_point_library.id), Not Null	Assigned library item
is_origin	BOOLEAN	Not Null, Default: false	Marks this stop as the departure origin for this template. Origin surcharge is always €0 regardless of overrides.
surcharge_override	DECIMAL	Nullable	Per-template surcharge override. Null = use library default. Ignored if is_origin = true.
door_pickup_override	BOOLEAN	Nullable	Override door pickup availability for this template. Null = inherit from library.
door_pickup_surcharge_override	DECIMAL	Nullable	Override door pickup surcharge. Null = inherit from library.
display_order	INT	Not Null, Default: 0	Display sequence in the booking widget and template config. NOT the operational pickup route — that depends on actual bookings.
enabled	BOOLEAN	Not Null, Default: true	Quick toggle to disable a stop for this template without removing the assignment.
created_at	TIMESTAMP	Default: now()

NOTE

Unique constraint: (tour_template_id, boarding_point_id) — one assignment per library item per template.

NOTE

CHECK constraint: At most one is_origin = true per tour_template_id. Implemented as a partial unique index: UNIQUE(tour_template_id) WHERE is_origin = true.

ancillary_catalog_items

Operator-level library of bookable extras (insurance, room upgrades, excursions, meals). Reusable across templates via template_ancillary_assignments. The centralized-overridable cascade follows the same pattern as boarding points and deposit config. See ancillary-catalog.md for the full design.

Column	Type	Constraints	Description
id	UUID	Primary Key	Unique catalog item
tenant_id	UUID	Foreign Key (operators.id), Not Null	Owning tenant
type	VARCHAR	Not Null	Behavioral category: INSURANCE, UPGRADE, EXTRA_LUGGAGE, EXCURSION, MEAL, OTHER. Determines refund rules, reporting bucket, and invoice line grouping.
label	VARCHAR	Not Null	Operator-defined display name shown to passengers (e.g., "Reiserücktrittsversicherung")
description	TEXT	Nullable	Optional longer description for the booking widget product page
cover_image_key	VARCHAR	Nullable	Optional image key for displaying the item in the booking widget
default_price	DECIMAL	Not Null	Default unit price in the operator's currency
currency	VARCHAR	Not Null, Default: EUR	ISO 4217
is_per_passenger	BOOLEAN	Not Null, Default: true	true: price applies per passenger. false: price applies per booking.
max_quantity	INT	Nullable	Maximum units per booking. Null = unlimited.
tax_strategy_override	VARCHAR	Nullable	STANDARD_VAT or MARGIN_SCHEME_25. Null = inherit from the parent tour's tax strategy.
status	VARCHAR	Not Null, Default: ACTIVE	ACTIVE or ARCHIVED. Archived items remain on existing bookings but disappear from pickers for new assignments.
sort_order	INT	Not Null, Default: 0	Display order in the booking widget and template configuration
created_at	TIMESTAMP	Default: now()
updated_at	TIMESTAMP	Default: now()

NOTE

Unique constraint: (tenant_id, label) — prevents duplicate names within a tenant.

template_ancillary_assignments

Which ancillary catalog items are offered on which tour template, with optional price overrides. Follows the same assignment pattern as template_boarding_point_assignments.

Column	Type	Constraints	Description
id	UUID	Primary Key	Unique assignment
tenant_id	UUID	Foreign Key (operators.id), Not Null	Owning tenant
tour_template_id	UUID	Foreign Key (tour_templates.id), Not Null	Target template
ancillary_catalog_item_id	UUID	Foreign Key (ancillary_catalog_items.id), Not Null	Catalog item being assigned
price_override	DECIMAL	Nullable	Per-template price override. Null = use default_price from catalog.
included_by_default	BOOLEAN	Not Null, Default: false	true: auto-added to every booking (e.g., travel insurance on premium tours). Passenger can opt out.
enabled	BOOLEAN	Not Null, Default: true	Quick toggle to disable an extra for this template without removing the assignment.
created_at	TIMESTAMP	Default: now()

NOTE

Unique constraint: (tour_template_id, ancillary_catalog_item_id) — one assignment per catalog item per template.

notification_templates

Operator-configurable message templates for automated communications.

Column	Type	Constraints	Description
id	UUID	Primary Key	Unique template identifier
tenant_id	UUID	Foreign Key (operators.id), Not Null	The tenant this template belongs to
channel	VARCHAR	Not Null	EMAIL, WHATSAPP, PUSH
trigger_event	VARCHAR	Not Null	System event that triggers the message
subject	VARCHAR		Subject line (if applicable)
body_template	TEXT	Not Null	Template content
locale	VARCHAR	Not Null	Language/locale
meta_template_name	VARCHAR	Nullable	Meta-approved HSM (Highly Structured Message) template identifier. Required for WHATSAPP channel — maps the Busflow-internal template to the Meta-approved version. Null for EMAIL/PUSH.
meta_template_namespace	VARCHAR	Nullable	Meta Business Account namespace. Required for WHATSAPP channel. Null for EMAIL/PUSH.

NOTE

Known trigger_event values: VEHICLE_SWAP_NOTIFICATION (consumed by VehicleSwapped — see vehicle-swap-protocol.md), QUALIFICATION_EXPIRING (consumed by QualificationExpiring), VEHICLE_INSPECTION_OVERDUE (consumed by VehicleInspectionOverdue), INCIDENT_BROADCAST (consumed by IncidentCreated — CRITICAL severity, any type: DELAY/BREAKDOWN/PASSENGER_ISSUE; see incident-broadcast-protocol.md), INCIDENT_ALLCLEAR (consumed by IncidentResolved — CRITICAL severity). For the full registry of all 22 trigger_event entries with channels, context variables, and dispatch modes, see notification-pipeline-protocol.md §7. The provisioning flow must seed these templates during tenant onboarding.

costing_sheets

The universal cost planning engine. Contains projected costs and tax strategy. Selling prices live on price_matrices — see costing-pricing-separation ADR.

Column	Type	Constraints	Description
id	UUID	Primary Key	Unique costing sheet identifier
tenant_id	UUID	Foreign Key (operators.id), Not Null	The tenant this sheet belongs to
source_type	VARCHAR	Not Null	TEMPLATE_BASELINE, DEPARTURE_CLONE, or CHARTER_CUSTOM
status	VARCHAR	Not Null, Default: DRAFT	DRAFT, CALCULATED, LOCKED — LOCKED = costs frozen (not prices). Auto-locks when the system confirms the first booking.
version	INT	Not Null, Default: 1	Optimistic locking counter for concurrent access
parent_sheet_id	UUID	Foreign Key (costing_sheets.id), Nullable	For DEPARTURE_CLONE: references the TEMPLATE_BASELINE the system cloned it from. Tracks revision lineage.
calculated_at	TIMESTAMPTZ	Nullable	Timestamp of last successful cost calculation. Null if status = DRAFT.
fixed_costs	JSONB		Driver rates, depreciation, insurance
variable_costs	JSONB		Distance costs, tolls (charter sheets include leerkilometer_km, leerkilometer_cost, toll_segments[])
procurement_items	JSONB		Typed array of CostComponent
planned_contribution_margin	DECIMAL		Target contribution margin in €
break_even_pax	INT		Minimum passengers to break even
tax_strategy	VARCHAR	Not Null	STANDARD_VAT or MARGIN_SCHEME_25
fx_config	JSONB		Foreign exchange configurations
total_net_cost	DECIMAL		Computed net cost
currency	VARCHAR	Default: EUR	Base currency
created_at	TIMESTAMP	Default: now()	Record creation timestamp
updated_at	TIMESTAMP	Default: now()	Record last update timestamp

price_matrices

Market-driven selling prices derived from a CostingSheet. Supports 1:N per CostingSheet (multi-channel pricing). Uses immutable append versioning — every mutation creates a new version. See costing-pricing-separation ADR and pricing-screen.md.

Column	Type	Constraints	Description
id	UUID	Primary Key	Unique price matrix identifier
tenant_id	UUID	Foreign Key (operators.id), Not Null	The tenant this matrix belongs to
costing_sheet_id	UUID	Foreign Key (costing_sheets.id), Not Null	Source cost calculation this price derives from
tour_departure_id	UUID	Foreign Key (tour_departures.id), Nullable	Null for template-level previews
channel	VARCHAR	Not Null, Default: DEFAULT	Sales channel this matrix serves
status	VARCHAR	Not Null, Default: DRAFT	DRAFT, PUBLISHED, ARCHIVED
version	INT	Not Null, Default: 1	Monotonic version counter
margin_config	JSONB		Array of MarginTarget value objects — margin rules applied to derive selling prices from costs
pricing_rules_snapshot	JSONB	Not Null	Deep copy of PricingRule[] active at generation time. Immutable after creation. Enables historical audit of demographic discount rules.
pricing_config_snapshot	JSONB	Not Null	Deep copy of PricingConfig (room surcharge, accommodation flag, season tiers, early-bird tiers) at generation time. Immutable after creation.
variants	JSONB	Not Null	Typed PriceVariant[] — resolved price variants including variable_cost_snapshot, applied_conditions[], and tax fields. See price-matrix-variant-spec.md for the full TypeScript interface.
list_price	DECIMAL		The List Price — base adult, base-accommodation, default-season, no-early-bird gross selling price. All variants derive from this.
currency	VARCHAR	Not Null, Default: EUR	Selling price currency
generated_at	TIMESTAMPTZ	Not Null	When initially derived from CostingSheet
published_at	TIMESTAMPTZ	Nullable	When made available to Commerce. Null if still DRAFT.
superseded_by	UUID	Foreign Key (price_matrices.id), Nullable	Points to the next version in the immutable chain. Null = current version.
created_at	TIMESTAMPTZ	Default: now()	Record creation timestamp

tour_templates

The abstract commercial product.

Column	Type	Constraints	Description
id	UUID	Primary Key	Unique template identifier
tenant_id	UUID	Foreign Key (operators.id), Not Null	The tenant this template belongs to
costing_sheet_id	UUID	Foreign Key (costing_sheets.id)	Baseline budget
title	VARCHAR	Not Null	Tour title
description	TEXT		Tour description
tags	JSONB	Nullable, Default: '[]'	Freeform operator-defined tags as a string array (e.g., ["Nordsee", "Städtereise", "Weihnachten"]). Supports filtering and booking widget categorization.
duration_days	INT	Not Null	Number of days
status	VARCHAR	Not Null, Default: DRAFT	DRAFT, ACTIVE, ARCHIVED
content	JSONB	Nullable	Rich tour content validated by TourContentSchema: { highlights: string[], included_services: string[], excluded_services: string[], travel_requirements: string[], itinerary: ItineraryDay[], cover_image_key: string?, gallery_image_keys: string[] }. Each ItineraryDay: { day_number, title, description?, locations: string[] }.
pricing_rules	JSONB		Array of PricingRule input VOs: { demographic: string, age_min, age_max, discount_type, discount_value, label }. demographic is operator-defined (e.g., 'ADULT', 'CHILD', 'SENIOR', 'STUDENT'). Snapshotted on PriceMatrix at generation.
pricing_config	JSONB	Nullable	PricingConfig VO: { room_surcharge, includes_accommodation, season_config[], early_bird_config[] }. Null = no room/season/early-bird variants. Snapshotted on PriceMatrix at generation.
capacity_rules	JSONB		Array of CapacityRule value objects: { strategy, max_capacity, overbooking_buffer_pct }
deposit_config	JSONB	Nullable	Per-template deposit override: { percentage: number, type: 'PERCENTAGE' | 'FIXED', min_amount: number | null }. Null = inherit from operators.deposit_config (tenant default). See PRODUCT_mollie-integration.md §3 for the cascading resolution logic.
final_payment_config	JSONB	Nullable	Per-template final payment timing override: { reminder_days_before_start: number, escalation_days_before_start: number, flag_days_before_start: number }. Null = inherit from operators.final_payment_config. See PRODUCT_mollie-integration.md §4.2.
cancellation_policy	JSONB	Nullable	Per-template cancellation fee schedule override (CancellationPolicy VO). Null = inherit from operators.cancellation_policy. Same schema as operator-level config. See cancellation-protocol.md §CancellationPolicy.
max_door_pickups	INT	Nullable, Default: 5	Maximum door pickup bookings per departure. Null = unlimited. Travels via TripPublished → Commerce enforces at checkout. See boarding-points.md §Door Pickup Mechanics.
ai_embedding	VECTOR	pgvector extension	Vector array for AI similarity
created_at	TIMESTAMP	Default: now()	Creation timestamp
updated_at	TIMESTAMP	Default: now()	Last update timestamp

WARNING

Subscription Tier Gating: pricing_rules and pricing_config manipulation is reserved for PRO and ENTERPRISE tenants. Because Hasura column-level permissions do not evaluate session variables (X-Hasura-Plan), direct GraphQL update access on these columns is omitted. Operators must use the upsertPricingConfig Hasura Action, which guards capability at the NestJS layer. See ADR-030.

charter_quotes

B2B commercial wrapper for custom group requests.

Column	Type	Constraints	Description
id	UUID	Primary Key	Unique quote identifier
tenant_id	UUID	Foreign Key (operators.id)
reseller_id	UUID	Foreign Key (resellers.id)	Partner requesting the quote
costing_sheet_id	UUID	Foreign Key (costing_sheets.id)	The internal calculation supporting the quote
status	VARCHAR	Default: DRAFT	DRAFT, SENT, ACCEPTED, REJECTED
requested_date	DATE	Not Null	Date of the requested trip

tour_departures

A concrete, scheduled departure of a TourTemplate. Represents a specific date-bound trip with all operational resources assigned. Publishing a TourDeparture creates the Commerce TourOffering via the TripPublished event.

Column	Type	Constraints	Description
id	UUID	Primary Key	Unique departure identifier
tenant_id	UUID	Foreign Key (operators.id), Not Null	Owning tenant
tour_template_id	UUID	Foreign Key (tour_templates.id), Not Null	Source template
costing_sheet_id	UUID	Foreign Key (costing_sheets.id)	Cloned instance budget (DEPARTURE_CLONE)
start_date	DATE	Not Null	Departure date
end_date	DATE	Not Null	Return date
status	VARCHAR	Not Null, Default: DRAFT	DRAFT, READY, PUBLISHED, COMPLETED, CANCELLED
pricing_config	JSONB	Nullable	Per-departure override of template PricingConfig. Null = inherit from TourTemplate. Same schema as tour_templates.pricing_config.
departure_notes	TEXT	Nullable	Free-form per-departure notes (e.g., specific hotel name, route deviations). Template content remains the source of truth.
created_at	TIMESTAMP	Default: now()	Record creation timestamp
updated_at	TIMESTAMP	Default: now()	Record last update timestamp

change_events

Local audit trail for entities within the Backoffice schema. Uses a polymorphic entity reference (entity_type + entity_id) instead of per-entity nullable FK columns — see ADR-019. Hasura relationship resolution uses a computed field backed by a schema-local SQL function. The shared AuditTrailService (NestJS) writes all change_events, ensuring consistent old_values capture via SELECT ... FOR UPDATE and transactional co-location with the entity mutation.

Column	Type	Constraints	Description
id	UUID	Primary Key	Unique event
tenant_id	UUID	Foreign Key (operators.id), Not Null
user_id	UUID	Nullable	Soft FK to auth.users(id). The actor — null for system/automation.
entity_type	VARCHAR	Not Null	Target entity type: operator, operator_settings, tenant_subscription, costing_sheet, tour_template, tour_departure, vehicle, supplier, allotment, crew_member, passenger_profile, reseller, notification_template, crew_qualification, crew_absence, vehicle_inspection, price_matrix, operator_integration, import_job
entity_id	UUID	Not Null	ID of the mutated entity. Soft FK — no hard constraint (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 for cross-context saga tracing (e.g., vehicle swap writes to both Operations and 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 ("show history for entity X"). Additional partial index: (correlation_id) WHERE correlation_id IS NOT NULL for saga tracing.

NOTE

Scope mapping for this schema: CONFIG for operator, operator_settings, tenant_subscription, notification_template, operator_integration. DSGVO for passenger_profile. GENERAL for everything else. GOBD and COMPLIANCE are valid enum values but not typically used in the Backoffice context.

crew_qualifications

Granular qualification tracking for crew members. Extends the basic license_number/license_expiry on crew_members with typed, expiry-tracked professional certifications required for DACH bus operations.

Column	Type	Constraints	Description
id	UUID	Primary Key	Unique qualification identifier
tenant_id	UUID	Foreign Key (operators.id), Not Null	Owning tenant
crew_member_id	UUID	Foreign Key (crew_members.id), Not Null	The crew member holding this qualification
qualification_type	VARCHAR	Not Null	LICENSE_D, LICENSE_D1, MODULE_95, ADR, FIRST_AID, PERSONENBEFOERDERUNGSSCHEIN, BORDER_VISA, DIGITAL_TACHOGRAPH_CARD
issued_date	DATE		Date the authority granted the qualification
expiry_date	DATE		Expiration date. Null = no expiry (e.g., certain first aid certs)
issuing_authority	VARCHAR		Authority or institution that issued the qualification
restriction_notes	TEXT		Free-text restrictions for human context (e.g., "UK visa — valid until 2027-03"). Not used for programmatic checks — use restriction_type instead.
restriction_type	VARCHAR	Nullable	Structured restriction classification: AUTOMATIC_ONLY, GEOGRAPHIC, VEHICLE_CLASS, OTHER. Null = no restriction. Used by the dispatch availability engine to enforce conflict rules (e.g., AUTOMATIC_ONLY blocks assignment to manual-transmission vehicles).
status	VARCHAR	Not Null, Default: VALID	VALID, EXPIRING_SOON, EXPIRED, REVOKED. EXPIRING_SOON set by Hasura Scheduled Trigger — see workflow-orchestration.md.
created_at	TIMESTAMP	Default: now()	Creation timestamp
updated_at	TIMESTAMP	Default: now()	Last update timestamp

NOTE

A Hasura Scheduled Trigger drives the EXPIRING_SOON status transition (check_qualification_expiry) running daily. It evaluates expiry_date - CURRENT_DATE <= threshold_days (configurable per tenant, default: 30). On transition, it emits a QualificationExpiring domain event consumed by Communications for manager notifications. See workflow-orchestration.md for the event trigger catalog.

Qualification Catalog

The canonical catalog of qualification types for DACH bus operations. All documents and implementations must reference this list.

Type	Full Name (DE)	Expiry?	Dispatch Block	Validation Rules
LICENSE_D	Führerschein Klasse D	✅ Yes	🔴 Hard Block	expiry_date NOT NULL. Required for all crew with role = DRIVER | DRIVER_GUIDE.
LICENSE_D1	Führerschein Klasse D1	✅ Yes	🟡 Warning	expiry_date NOT NULL. Allows driving minibuses only (≤ 16 pax). Warning if dispatched to a COACH.
MODULE_95	Berufskraftfahrerqualifikation (BKrFQG)	✅ Yes (5 yr)	🔴 Hard Block	expiry_date NOT NULL. Mandatory for all commercial bus drivers in the EU. Renewal requires 35h training within 5 years.
PERSONENBEFOERDERUNGSSCHEIN	Personenbeförderungsschein (PBefG §13)	✅ Yes	🔴 Hard Block	expiry_date NOT NULL. Required by German Fahrlaubnis-Verordnung for fee-based passenger transport.
ADR	ADR-Bescheinigung	✅ Yes (5 yr)	🟡 Warning	Only relevant for routes carrying dangerous goods (rare in bus tourism). Warning if missing, not a dispatch block.
FIRST_AID	Erste-Hilfe-Bescheinigung	✅ / ❌	🟡 Warning	expiry_date nullable — some certs are perpetual, others renewed. Warning if expired, not a hard block.
BORDER_VISA	Grenzübertrittsgenehmigung / Visum	✅ Yes	🔴 Conditional	Hard block only for routes crossing the relevant border. Uses restriction_notes to specify country/validity.
DIGITAL_TACHOGRAPH_CARD	Fahrerkarte (EU VO 165/2014)	✅ Yes (5 yr)	🟡 Warning / 🔴 Module-gated	expiry_date NOT NULL. Mandatory for all commercial bus drivers. Separate from the license — the driver must physically carry it. Default: 🟡 Warning. Becomes 🔴 Hard Block when the operator enables the TACHOGRAPH integration (operator_integrations, Phase 2). Operators should not need to track this before opting into the Digital Tachograph module.

Phase 1 simplification: The schema defines all 8 types. The system defers BORDER_VISA conditional blocking (route-dependent) to Phase 2 — in Phase 1 it triggers a 🟡 Warning regardless of route. LICENSE_D1 resolution (D1 sufficient for vehicles with capacity ≤ 16 pax per EU Directive 2006/126/EC, Art 4) is also deferred to Phase 2 — Phase 1 requires LICENSE_D for all vehicles. DIGITAL_TACHOGRAPH_CARD dispatch blocking is module-gated: 🟡 Warning by default, 🔴 Hard Block only when the operator has enabled the TACHOGRAPH integration (Phase 2).

Transmission Restriction Matching

The restriction_type enum provides structured matching against vehicles.transmission_type:

Vehicle	Crew restriction_type	Result
MANUAL	NULL (no restriction)	✅ OK
AUTOMATIC	NULL (no restriction)	✅ OK
MANUAL	AUTOMATIC_ONLY	🔴 BLOCKED
AUTOMATIC	AUTOMATIC_ONLY	✅ OK

For CRUD operations (Create/Update/Delete/Revoke), dispatch validation algorithm (AssignCrew Action), and edge state handling, see crew-qualification-protocol.md.

crew_absences

Leave and absence tracking for crew members. Consumed by the dispatch board to block assignments during approved absences.

Column	Type	Constraints	Description
id	UUID	Primary Key	Unique absence identifier
tenant_id	UUID	Foreign Key (operators.id), Not Null	Owning tenant
crew_member_id	UUID	Foreign Key (crew_members.id), Not Null	The absent crew member
absence_type	VARCHAR	Not Null	VACATION, SICK, REST_DAY, TRAINING, OTHER
start_date	DATE	Not Null	Absence start (inclusive)
end_date	DATE	Not Null	Absence end (inclusive)
status	VARCHAR	Not Null, Default: REQUESTED	REQUESTED, APPROVED, REJECTED. Only APPROVED absences block dispatch.
approved_by	UUID	Foreign Key (users.id), Nullable	The manager/dispatcher who approved. Null while REQUESTED.
notes	TEXT		Free-text notes (e.g., doctor's note reference)
created_at	TIMESTAMP	Default: now()	Creation timestamp
updated_at	TIMESTAMP	Default: now()	Last update timestamp

vehicle_inspections

Scheduled regulatory inspection tracking for vehicles. Tracks deadlines for mandatory DACH bus inspections (HU, SP, tachograph calibration). Consumed by dispatch board to warn or block assignment of non-compliant vehicles.

Column	Type	Constraints	Description
id	UUID	Primary Key	Unique inspection identifier
tenant_id	UUID	Foreign Key (operators.id), Not Null	Owning tenant
vehicle_id	UUID	Foreign Key (vehicles.id), Not Null	The inspected vehicle
inspection_type	VARCHAR	Not Null	HU (Hauptuntersuchung), SP (Sicherheitsprüfung), TACHOGRAPH_CALIBRATION, UVV (Unfallverhütungsvorschrift), EXHAUST_TEST (AU)
due_date	DATE	Not Null	Deadline for this inspection
completed_date	DATE		Date the workshop completed the inspection. Null = still pending/overdue.
status	VARCHAR	Not Null, Default: PENDING	PENDING, COMPLETED, OVERDUE. OVERDUE set by scheduled trigger when due_date < CURRENT_DATE AND completed_date IS NULL.
blocks_dispatch	BOOLEAN	Not Null, Default: false	When true, dispatchers cannot assign the vehicle to any ServiceLeg. The system automatically sets this to true when status transitions to OVERDUE for safety-critical types (HU, SP).
notes	TEXT		Inspector remarks or workshop reference

import_jobs

State tracking for the Legacy Data ETL pipeline. Uploads via the Magic Upload UI initiate a job.

Column	Type	Constraints	Description
id	UUID	Primary Key	Unique job identifier
tenant_id	UUID	Foreign Key (operators.id), Not Null	Owning tenant
entity_type	VARCHAR	Not Null	Target structure: PASSENGER_PROFILES, TOUR_OFFERINGS, BOOKINGS, INVOICES
s3_key	VARCHAR	Not Null	Location of the uploaded blob
status	VARCHAR	Not Null, Default: PENDING	PENDING, PROCESSING, COMPLETED, COMPLETED_WITH_ERRORS, FAILED
bullmq_job_id	VARCHAR	Nullable	Populated via the incoming Hasura Event Webhook to track the background job
total_rows	INT	Default: 0	Calculated after bounding download stream
imported_rows	INT	Default: 0	Rows successfully processed/upserted
error_rows	INT	Default: 0	Count of rows trapped by Dead-Letter Queue
created_at	TIMESTAMPTZ	Default: now()	Record creation timestamp
completed_at	TIMESTAMPTZ	Nullable	Job completion timestamp

import_error_rows

The Dead Letter Queue for the Magic ETL pipeline. Supports partial success for mass data migrations by catching single row constraint failures from GraphQL batch assertions without rolling back the entire batch.

Column	Type	Constraints	Description
id	UUID	Primary Key	Unique error row index
import_job_id	UUID	Foreign Key (import_jobs.id), Not Null	Parent ETL job
row_index	INT	Not Null	Original CSV row number to display to the user
raw_payload	JSONB	Not Null	Exact parsed representation of the bad row
error_message	TEXT	Not Null	Hasura constraint or encoding evaluation failure reasoning
resolved	BOOLEAN	Not Null, Default: false	True when operator manually fixes the item in the UI