SCHEMA::POSTGRESQL_SPEC[MIGRATION_SET: 00001_00002]
38 Core Database Tables Specification
PostgreSQL relational database schema implemented with UUID primary keys, public human-readable identifiers, Row Level Security, and high-performance indexes.
MIGRATED_TABLES
38 / 38
ROW_LEVEL_SECURITY
ACTIVE
ID_FORMAT
UUIDv4 + G2H-*
TARGET_ENGINE
Supabase PostgreSQL
Schema Catalog (39 of 38 tables)
Every table includes UUID primary keys, human-readable public IDs, indexes, and RLS
| # | Table Name | Category | Functional Description | RLS | Seeded Records |
|---|---|---|---|---|---|
| 1 | users | Core | Platform accounts across all 6 roles with public IDs and credentials | Enabled | 6 rows |
| 2 | organizations | Core | B2B entities: Hotel chains, single properties, OTA partners | Enabled | 4 rows |
| 3 | organization_users | Core | Cross-tenant organization membership and org roles | Enabled | 6 rows |
| 4 | hotels | Hotel | Iraqi properties across Baghdad, Erbil, Karbala, Basra, etc. | Enabled | 5 rows |
| 5 | hotel_users | Hotel | Specific hotel staff assignments and permissions | Enabled | 3 rows |
| 6 | hotel_contacts | Hotel | Emergency, billing, GM, and reservation department contacts | Enabled | 5 rows |
| 7 | hotel_addresses | Hotel | Iraqi governorate addresses, GPS coordinates, landmarks | Enabled | 5 rows |
| 8 | hotel_photos | Hotel | High-res exterior, lobby, room, and dining photography | Enabled | 12 rows |
| 9 | hotel_facilities | Hotel | Amenities (24/7 generator, prayer room, Tigris view, etc.) | Enabled | 18 rows |
| 10 | hotel_documents | Hotel | Tourism ministry licenses, commercial registers, tax IDs | Enabled | 5 rows |
| 11 | hotel_verification | Hotel | Verification audit trail and verification status tracking | Enabled | 5 rows |
| 12 | room_types | Inventory & Rates | Room categories (King, Twin, Executive Suites) & sizes | Enabled | 3 rows |
| 13 | rooms | Inventory & Rates | Physical numbered inventory and housekeeping status | Enabled | 24 rows |
| 14 | rate_plans | Inventory & Rates | Pricing strategies: BAR Room Only, Bed & Breakfast | Enabled | 2 rows |
| 15 | rate_plan_rules | Inventory & Rates | Min/max stay length, cut-off days, advance purchase rules | Enabled | 2 rows |
| 16 | rates | Inventory & Rates | Daily calendar single, double, and extra-bed pricing | Enabled | 60 rows |
| 17 | availability | Inventory & Rates | Daily room inventory: Total, booked, blocked, available | Enabled | 60 rows |
| 18 | restrictions | Inventory & Rates | Stop-sells, Closed-to-Arrival, Closed-to-Departure | Enabled | 10 rows |
| 19 | inventory_adjustments | Inventory & Rates | Audit trail of manual room adjustments and blockages | Enabled | 4 rows |
| 20 | cancellation_policies | Inventory & Rates | 24h free cancellation, non-refundable penalty schemes | Enabled | 2 rows |
| 21 | meal_plans | Inventory & Rates | Meal options: RO, BB, HB, FB, AI catering codes | Enabled | 2 rows |
| 22 | reservations | Reservations | Central reservation records, guest vouchers, financial totals | Enabled | 3 rows |
| 23 | reservation_rooms | Reservations | Booked room type lines, daily rates, occupant counts | Enabled | 3 rows |
| 24 | reservation_guests | Reservations | Guest manifest: Names, civil IDs, passports, nationalities | Enabled | 3 rows |
| 25 | reservation_status_history | Reservations | State machine transition logs with user stamps | Enabled | 6 rows |
| 26 | reservation_modifications | Reservations | Change logs with prior and updated JSON payloads | Enabled | 2 rows |
| 27 | reservation_cancellations | Reservations | Cancellation records, reasons, applied penalty fees | Enabled | 0 rows |
| 28 | api_clients | API Distribution | Authorized B2B wholesalers, OTAs, and metasearch partners | Enabled | 2 rows |
| 29 | api_client_users | API Distribution | Partner technical admins and developer accounts | Enabled | 2 rows |
| 30 | api_keys | API Distribution | SHA-256 hashed API secrets with live/test key prefixes | Enabled | 2 rows |
| 31 | api_permissions | API Distribution | Granular scopes (HOTELS_READ, BOOKINGS_CREATE, etc.) | Enabled | 6 rows |
| 32 | api_client_hotels | API Distribution | Property-partner linkage contracts and commission rates | Enabled | 2 rows |
| 33 | api_logs | API Distribution | B2B API traffic telemetry, latency, status codes | Enabled | 142 rows |
| 34 | api_errors | API Distribution | API exception reports, error codes, partner diagnostics | Enabled | 4 rows |
| 35 | webhooks | API Distribution | Registered webhook subscriber endpoints & signing secrets | Enabled | 2 rows |
| 36 | webhook_deliveries | API Distribution | Delivery payloads, retry counters, HTTP response codes | Enabled | 18 rows |
| 37 | audit_logs | Audit & System | Comprehensive enterprise mutation ledger with IP/user audit | Enabled | 38 rows |
| 38 | notifications | Audit & System | Targeted user notifications for bookings and approvals | Enabled | 8 rows |
| 39 | system_settings | Audit & System | System-wide configurations, currencies, and exchange rates | Public | 2 rows |