Primary Database System

Intent & Business Context

• Application: busflow (Bus tourism engine).
• Core Requirements: Enterprise-grade stability for transactions, high schema flexibility for variable travel itineraries, and native foundation for AI capabilities (Magic Upload, Copilot).
• Data Taxonomy:
  • Transactional: Bookings, payments, passenger lists, fleet inventory (Requires strict ACID compliance).
  • Variable: Itineraries, AI parser outputs (Requires semi-structured schema support).
  • Network: Geographical routes, stop topologies.
  • Semantic: Vector arrays for AI similarity mapping.

Decision: PostgreSQL

We selected PostgreSQL as the unified primary data store. This choice consolidates relational, document, and vector databases into a single sovereign system, aligning with the "Do More With Less" operational pillar.

Concrete Database Schemas (Modular Monolith)

To support the Domain-Driven Design architecture while leveraging a single PostgreSQL instance, the database is physically partitioned into distinct schemas representing our Bounded Contexts.

Detailed physical schemas, Entity-Relationship Diagrams (ERDs), and table definitions reside individually per pillar:

1. Backoffice Schema (schema-backoffice.md):
   • Scope: Configuration, operational staff, abstract product definitions (TourTemplate), concrete scheduled departures (TourDeparture), third-party inventory (Allotment), CRM, and financial planning (CostingSheet).
2. Commerce & Finance Schema (schema-commerce.md):
   • Scope: Conversion and accounting engine, handling TourOfferings, B2C/B2B Bookings, Payments, ticketing, and actual margin taxation (FinancialLedger).
3. Operations Schema (schema-operations.md):
   • Scope: Execution layer managing ServiceLegs, dispatching (LegAssignment), IoT fleet telemetry, OCR expense scanning, and offline app sync.
4. Communications Schema (schema-communications.md):
   • Scope: Shared Core Domain providing omnichannel inbox capabilities (Conversations, Messages) and trigger-based automated messaging.

High-Level Architecture Strategy

• Core Relational Storage:
  • Intent: Enforce absolute data integrity for high-stakes financial and operational truth.
  • Target Entities: Users, Operators, Bookings, Payments, Vehicles.
• Variable Document Storage (JSONB):
  • Intent: Prevent schema bloat (empty columns/complex joins) while accommodating inherently unpredictable trip structures and dynamic multi-tenant configurations.
  • Target Entities: CostingSheet (Price Matrices, Cost Components), Vehicle (Seat Map Layouts), Itineraries.
• AI & Semantic Storage (pgvector):
  • Intent: Keep AI context immediately adjacent to operational data for zero-latency retrieval.
• Target Entities: Text embeddings generated from AI-assisted PDF PDF pipelines (TourTemplate); Copilot conversational histories.

Rejected Alternatives

• MongoDB:
  • Reasoning: Discarded due to insufficient strict relational constraints. Unsuitable as a primary source of truth for seat maps, digital tickets, and financial ledgers.
• Neo4j:
  • Reasoning: Discarded as overly complex for primary CRUD operations (Apple Pay processing, user profiles).
  • Future Consideration: We may adopt it later as an isolated microservice specifically handling complex geographical routing and fleet optimization.