Chapter 10.1 — Database Schema Overview
1. Purpose
This chapter is the entry point to the database documentation. It defines the overall schema organisation, the conventions every table follows, the relationships between major module clusters, the partitioning strategy, the indexing approach, and the rules for schema change.
A full table-by-table reference lives in Chapter 10.3; entity-relationship diagrams in Chapter 10.2; module-table mappings in Chapter 10.7.
2. Why it matters
The database is the platform's source of truth. Every decision encoded here — table naming, dimension columns, history-table pattern, foreign-key rules — propagates across every chapter, every API, every report. A coherent schema is the foundation of a coherent platform.
3. Schema organisation
Tables are grouped into schemas logically (whether or not MySQL schemas/databases physically separate them):
| Schema group | Purpose | Example tables |
|---|---|---|
core |
Platform-wide foundations | partners, users, audit_logs, accounting_periods |
ops_customer |
Customer master | customers, customer_addresses, customer_balances |
ops_supplier |
Supplier master | suppliers, supplier_bank_details, supplier_balances |
ops_booking |
Bookings + child tables | bookings, booking_segments, booking_tickets, booking_pnrs, booking_history |
ops_inventory |
Reference data | airport_codes, location_codes, airlines, currencies |
fin_ledger |
Double-entry ledger | chart_of_accounts, journal_entries, journal_entry_lines, fx_rates |
fin_invoicing |
Customer billing | invoices, invoice_lines, credit_notes |
fin_payments |
Money-in/out | payments, payouts, payment_invoice_applications, bank_accounts |
fin_recon |
Reconciliation | bank_statement_files, recon_runs, recon_exceptions |
fin_tax |
Tax | tax_rules, tax_returns, booking_tax_lines |
fin_commission |
Commission accounting | commission_accruals, commission_settlements, commission_disputes |
integ |
Integration state | gds_credentials, supplier_request_log, webhook_deliveries |
bsp |
BSP/ARC | bsp_import_files, memos, memo_disputes |
notif |
Notifications | email_outbox, sms_outbox, notification_templates |
subs |
Subscription billing | subscriptions, subscription_invoices (travoBooks billing partners themselves) |
audit_history |
History/audit tables | *_history mirror tables per mutable entity |
In Phase 1 these are logical groupings only — all live in a single MySQL database. Phase 4 sharding may physically separate them.
4. Table conventions
4.1 Mandatory columns
Every business table has:
| Column | Type | Required |
|---|---|---|
*_id (primary key) |
BIGINT AUTO_INCREMENT | Yes |
partner_id |
BIGINT NOT NULL | Yes (except global reference data) |
created_at |
DATETIME(6) NOT NULL DEFAULT NOW(6) | Yes |
updated_at |
DATETIME(6) NOT NULL ON UPDATE NOW(6) | Yes |
created_by_user_id |
BIGINT NULL | Yes (NULL allowed for system events) |
updated_by_user_id |
BIGINT NULL | Yes |
version |
INT UNSIGNED NOT NULL DEFAULT 1 | Yes (optimistic concurrency) |
Soft-delete uses deleted_at DATETIME(6) NULL on tables where appropriate; financial tables do not support soft-delete (append-only).
4.2 Naming
- Tables:
snake_case, plural (customers,journal_entry_lines). - Columns:
snake_case. - FK columns:
<referenced_table_singular>_id(customer_id,supplier_id). - Enum-like values stored as VARCHAR with CHECK constraint or as ENUM; preference is VARCHAR with named SQL constraint for ease of extension.
- Money columns:
*_amountDECIMAL(18,2) for 2-decimal currencies;*_amount_minorBIGINT for store-as-minor-units paths. - Currency code:
*_currencyCHAR(3). - Dates: pure dates as DATE, datetimes as DATETIME(6).
- JSON-typed columns:
metadata,*_payload,*_breakdown.
4.3 Indexes
Every table has:
- Primary key on *_id.
- Unique index on (partner_id, code) where code is the partner-visible identifier (e.g., customer_code).
- Index on (partner_id, created_at) for partitioned scans.
- FK indexes on every foreign key column.
- Module-specific indexes per query patterns (covered in Chapter 10.4).
4.4 Foreign keys
- Enforced at DB level (InnoDB).
ON UPDATE CASCADEfor routine FKs.ON DELETEis typicallyRESTRICTfor business-critical references (don't allow orphans);SET NULLfor optional references.- Financial tables never
CASCADE DELETEupward — referential integrity preserves history.
4.5 History tables
For mutable entities, a parallel *_history table captures every change:
| Column | Type | Notes |
|---|---|---|
history_id |
BIGINT AUTO_INCREMENT PK | |
<entity>_id |
BIGINT | FK back to live row |
changed_at |
DATETIME(6) | |
changed_by_user_id |
BIGINT | |
change_type |
ENUM(INSERT, UPDATE, DELETE, RESTORE) |
|
| Full mirror of original columns | Snapshot of row state | |
diff_json |
JSON | Optional: only changed fields with before/after |
Triggers maintain history rows; alternatively the persistence layer writes both in same transaction (preferred — explicit, testable). travoBooks uses application-level writes, not DB triggers.
4.6 Financial-table specifics
journal_entries,journal_entry_linesare append-only: no UPDATEs except forposted_atflag transitions allowed pre-posting; onceposted_atis set, immutable.- Reversal pattern (Chapter 5.2) handles corrections.
- Financial tables retained for 7+ years per audit requirements.
- Sequences (invoice numbers, JE numbers, receipt numbers) gap-free per partner — managed via row-locked sequences table.
5. Partitioning (multi-tenancy)
Per Chapter 1.1, every business table has partner_id as the partition column. Phase 1: enforced at application layer + composite indexes. Phase 4: physical partitioning by partner_id HASH or RANGE.
6. Schema change management
- All schema changes via migrations in a versioned migration directory (
/db/migrations/YYYYMMDDHHMM_description.sql). - Migrations are forward-only; rollback only via compensating forward migrations.
- DB user for migrations is separate from application user; application user lacks DDL.
- Production migrations run in two-phase: schema change first (always backward-compatible), then app-code deploy that uses the new shape.
- Destructive changes (DROP COLUMN, DROP TABLE) require a deprecation period ≥ one release.
7. Reference data
Some tables hold reference data (no partner_id):
- airport_codes — IATA airport codes (~9,800 rows per current dataset)
- location_codes — city/region/country codes (~81,000 rows)
- airlines — IATA airline directory
- currencies — ISO-4217 with minor-units
- countries — ISO-3166
- gds_provider_codes — reference for GDS-specific code lookups
These tables are platform-shared and read-only from partner-scope code.
8. Major relationship clusters
9. Cross-cutting concerns
9.1 Audit log
audit_logs captures every business-meaningful event. Schema:
| Column | Notes |
|---|---|
audit_log_id |
PK |
partner_id |
|
event_type |
booking.created, je.posted, customer.credit_changed, etc. |
actor_type |
user, system, api_token |
actor_id |
|
target_entity_type |
|
target_entity_id |
|
event_payload |
JSON snapshot |
request_id |
Correlate with request trace |
ip_address |
|
user_agent |
|
created_at |
Retained ≥ 7 years; archived to S3-Glacier for older records.
9.2 Currency precision
DECIMAL(18,2) for 2-decimal currencies (USD, EUR, BDT, INR);
DECIMAL(18,3) for 3-decimal currencies (BHD, KWD, OMR);
DECIMAL(18,0) for zero-decimal currencies (JPY, KRW, VND).
The currencies table holds minor_units per currency; persistence layer enforces precision.
FX rate stored DECIMAL(18,8) for high precision.
9.3 Idempotency keys
API endpoints accept Idempotency-Key header. Stored in idempotency_keys table keyed by (partner_id, scope, key). Replay returns cached response. TTL 24 hours.
9.4 Sequence management
Gap-free sequences for invoice numbers, JE numbers, receipt numbers, etc.:
- sequences table with (partner_id, sequence_name, current_value).
- Acquired via row-lock + increment in the same transaction as the entity insert.
- Rollback safe — uncommitted increments rolled back.
10. Read replicas
Reporting and search use read replicas. Important rules: - Never read from replica when the just-written entity must be immediately visible (booking issuance → ticket immediately read). - Use replica for: reports, search, list views with eventual consistency. - Replica lag monitored; if > 60s, alerts fire.
11. Backup & retention
| Data category | Retention | Backup method |
|---|---|---|
| Operational (bookings, customers) | 7+ years | Nightly logical + hourly physical |
| Financial (JE, invoices) | 10+ years (or per local statute) | Same + WORM archive at year-end |
| Audit logs | 7+ years | Same + cold storage |
| PII (after consent withdrawal) | Anonymise within 30 days | Standard backup; restore process honours deletion |
| Documents (S3) | Per category | S3 versioning + replication |
12. Common pitfalls
- ⚠️ Forgetting
partner_idin WHERE clauses. This breaks tenant isolation. Use the persistence layer's enforced scoping. - ⚠️ Updating financial tables. Don't. Reverse with new entries.
- ⚠️ DDL from application user. Migrations are run by a separate role; the app user lacks DDL.
- ⚠️ N+1 queries on booking detail views. Use EAGER joins for the standard booking detail bundle.
- ⚠️ Putting JSON-heavy queries in hot paths. Extract critical fields to columns.
- 🔒 Adding sensitive PII columns without classification. Document, encrypt, audit-flag.