# Tarivo Database Specification Document status: Active schema specification Version: 2.0 Database engine: MySQL 8 Default timezone: Africa/Casablanca Default currency: MAD ## 1. Database Strategy MySQL is the system of record. Redis may be used for cache, queues and realtime support, but no critical business state may exist only in Redis. Schema priorities: - Referential integrity through foreign keys - Clear ownership for every business record - Indexed realtime workloads - Idempotent payment and offline sync processing - Auditability for sensitive operations - Recoverability through soft deletes where business-appropriate ## 2. Naming and Type Rules - Tables use plural snake_case names. - Primary keys use unsigned big integers. - Public identifiers use `uuid` where records may be exposed externally. - Monetary values use `decimal(12,2)` by default, never floats. - Exchange rates or provider fee rates, if added later, must use explicit decimal precision approved in the migration notes. - Coordinates use `decimal(10,7)` for latitude and longitude. - Heading, speed and accuracy use decimals with explicit precision, normally `decimal(8,2)` unless a feature requires more. - Status values must be represented by enums in application code. - Every user-owned table stores `created_at` and `updated_at`. - Soft deletes are used for users, drivers, vehicles, rides, trips and files. - Foreign key behavior must be explicit in every migration. Prefer `restrict` for financial, audit and trip history; use `cascade` only for dependent rows that have no independent business value. - JSON columns must have a documented shape in the owning module before feature work is marked complete. ## 3. Data Protection and Retention - Passwords and tokens are hashed or stored only through Laravel/Sanctum mechanisms. - Private file paths are internal and must not be exposed by API Resources. - Audit logs are append-only application records. - Payment webhook payload storage must exclude raw card data and secrets. Store only the provider payload required for idempotency, dispute support and operational debugging. - Deleting a user is soft-delete first. Legal retention for trips, invoices, payments, audit logs and support records overrides user-facing deletion. - Production backups must include MySQL and private file storage. ## 4. Identity and Access ### users Stores platform accounts for passengers, drivers, support and admins. Columns: - id - uuid unique - name - email nullable unique - phone unique - password - preferred_language default `fr` - timezone default `Africa/Casablanca` - status: active, suspended, deleted - email_verified_at nullable - phone_verified_at nullable - last_seen_at nullable - created_at, updated_at, deleted_at Indexes: - unique phone - unique email - status ### roles, permissions and pivots Use a standard Laravel role-permission package or equivalent schema with: - roles - permissions - model_has_roles - model_has_permissions - role_has_permissions Roles: - passenger - driver - support - admin - super_admin ## 5. Reference Data ### cities Columns: - id - name_fr - name_ar nullable - name_en nullable - slug unique - country_code default `MA` - latitude decimal(10,7) nullable - longitude decimal(10,7) nullable - is_active - created_at, updated_at Initial records: - Casablanca - Rabat - Marrakech - Agadir - Tangier - Fes - Meknes - Oujda - Nador - Laayoune - Dakhla ### vehicle_types Columns: - id - name - slug unique - base_fare decimal(12,2) - price_per_km decimal(12,2) - price_per_minute decimal(12,2) - capacity unsigned tiny integer - is_active - created_at, updated_at Initial records: - Petit Taxi - Grand Taxi - Premium - Moto - Delivery ## 6. Driver Domain ### drivers Columns: - id - user_id foreign unique - city_id foreign - status: pending, approved, rejected, suspended - availability: offline, online, busy - rating_average decimal(3,2) default 0 - rating_count unsigned integer default 0 - approved_at nullable - rejected_reason nullable - created_at, updated_at, deleted_at Indexes: - user_id - city_id, status - availability ### vehicles Columns: - id - driver_id foreign - vehicle_type_id foreign - city_id foreign - brand - model - color - registration_number unique - seats unsigned tiny integer - status: pending, approved, rejected, suspended - created_at, updated_at, deleted_at Indexes: - driver_id - vehicle_type_id - city_id, status - unique registration_number ### driver_documents Columns: - id - driver_id foreign - file_id foreign - type: identity_card, driving_license, insurance, vehicle_registration, vehicle_photo - status: pending, processing, approved, rejected - ai_status: queued, processing, completed, failed - ai_processing_job_id nullable foreign - rejection_reason nullable - expires_at nullable - reviewed_by nullable foreign users - reviewed_at nullable - created_at, updated_at, deleted_at Indexes: - driver_id, type - status - ai_status ## 7. Ride and Trip Domain ### ride_requests Columns: - id - uuid unique - passenger_id foreign users - city_id foreign - vehicle_type_id foreign - pickup_label - pickup_latitude decimal(10,7) - pickup_longitude decimal(10,7) - dropoff_label - dropoff_latitude decimal(10,7) - dropoff_longitude decimal(10,7) - requested_at - scheduled_at nullable - estimated_distance_meters unsigned integer nullable - estimated_duration_seconds unsigned integer nullable - estimated_price decimal(12,2) nullable - payment_method: cash, wallet, card - status: requested, searching, accepted, cancelled, expired - cancellation_reason nullable - created_at, updated_at, deleted_at Indexes: - passenger_id, created_at - city_id, status - vehicle_type_id, status - requested_at ### trips Columns: - id - uuid unique - ride_request_id foreign unique - passenger_id foreign users - driver_id foreign drivers - vehicle_id foreign vehicles - status: accepted, driver_arriving, driver_arrived, started, paused, completed, cancelled - started_at nullable - completed_at nullable - cancelled_at nullable - final_distance_meters unsigned integer nullable - final_duration_seconds unsigned integer nullable - final_price decimal(12,2) nullable - created_at, updated_at, deleted_at Indexes: - passenger_id, created_at - driver_id, status - status, created_at ### ride_request_driver_responses Stores durable driver accept/reject decisions for a ride request. Rejection does not close the ride for other drivers, but prevents duplicate responses from the same driver. Columns: - id - ride_request_id foreign - driver_id foreign - action: accepted, rejected - reason nullable - created_at, updated_at Indexes: - unique ride_request_id, driver_id - driver_id, action - ride_request_id, action ### trip_locations Columns: - id - trip_id foreign - driver_id foreign - latitude decimal(10,7) - longitude decimal(10,7) - heading decimal(8,2) nullable - speed decimal(8,2) nullable - accuracy decimal(8,2) nullable - recorded_at - created_at Indexes: - trip_id, recorded_at - driver_id, recorded_at ### trip_status_events Columns: - id - trip_id foreign - actor_id nullable foreign users - from_status nullable - to_status - reason nullable - metadata json nullable - created_at Indexes: - trip_id, created_at - actor_id, created_at ## 8. Notifications, Chat and Calls ### notifications Columns: - id - uuid unique - user_id foreign - type - title - body nullable - data json nullable - priority: normal, high, critical - persistence: ephemeral, persistent, blocking - sound_key nullable - read_at nullable - acted_at nullable - expires_at nullable - created_at, updated_at Indexes: - user_id, read_at - user_id, priority - expires_at Application enums: - priority: `normal`, `high`, `critical` - persistence: `ephemeral`, `persistent`, `blocking` Audited API actions: - `notification.read` - `notification.acted` - `notification.read_all` - `notification.deleted` ### conversations Columns: - id - uuid unique - type: ride, support, admin - trip_id nullable foreign - support_ticket_id nullable foreign - status: active, archived - created_at, updated_at ### conversation_participants Columns: - id - conversation_id foreign - user_id foreign - last_read_at nullable - muted_until nullable - created_at, updated_at Constraints: - unique conversation_id, user_id ### messages Columns: - id - uuid unique - conversation_id foreign - sender_id foreign users - type: text, image, file, voice, system - body nullable - file_id nullable foreign - status: sent, delivered, read, failed - created_at, updated_at, deleted_at Indexes: - conversation_id, created_at - sender_id, created_at ### call_sessions Columns: - id - uuid unique - conversation_id foreign - caller_id foreign users - recipient_id foreign users - type: audio, video - status: ringing, accepted, declined, missed, ended, failed - started_at nullable - answered_at nullable - ended_at nullable - ended_reason nullable - created_at, updated_at Indexes: - caller_id, created_at - recipient_id, status - conversation_id Application enums: - type: `audio`, `video` - status: `ringing`, `accepted`, `declined`, `missed`, `ended`, `failed` Audited API actions: - `call.started` - `call.accepted` - `call.declined` - `call.ended` - `call.signal_sent` ## 9. Uploads and AI ### files Columns: - id - uuid unique - owner_id foreign users - disk - path - original_name - mime_type - size_bytes unsigned big integer - checksum nullable - visibility: private, public - status: uploaded, processing, ready, rejected, deleted - created_at, updated_at, deleted_at Indexes: - owner_id, created_at - mime_type - status ### ai_processing_jobs Columns: - id - uuid unique - file_id nullable foreign - subject_type nullable - subject_id nullable - provider default `openai` - task: driver_document_validation, ocr, image_analysis, complaint_summary, translation - status: queued, processing, completed, failed - input_hash nullable - result json nullable - error_code nullable - error_message nullable - processed_at nullable - created_at, updated_at Indexes: - file_id - task, status - subject_type, subject_id ## 10. Offline Sync ### sync_actions Columns: - id - uuid unique - user_id foreign - idempotency_key unique - action_type: trip_start, trip_complete - payload json - status: received, applied, rejected, conflict - conflict_reason nullable - processed_at nullable - created_at, updated_at Indexes: - user_id, status - action_type, created_at - unique idempotency_key Application enums: - action_type: `trip_start`, `trip_complete` - status: `received`, `applied`, `rejected`, `conflict` Audited API actions: - `sync.applied` - `sync.conflict` ## 11. Payments and Accounting ### wallets Columns: - id - user_id foreign unique - balance decimal(12,2) default 0 - currency default `MAD` - status: active, frozen - created_at, updated_at ### transactions Columns: - id - uuid unique - wallet_id nullable foreign - user_id foreign - trip_id nullable foreign - type: debit, credit, refund, withdrawal, adjustment - amount decimal(12,2) - currency default `MAD` - status: pending, completed, failed, reversed - reference nullable unique - metadata json nullable - created_at, updated_at Indexes: - user_id, created_at - trip_id - status ### payments Columns: - id - uuid unique - trip_id foreign - user_id foreign - provider: cash, stripe, wallet - provider_reference nullable unique - amount decimal(12,2) - currency default `MAD` - status: pending, authorized, captured, completed, failed, refunded - paid_at nullable - created_at, updated_at Indexes: - trip_id - user_id, created_at - provider, provider_reference - status Application enums: - provider: `cash`, `stripe`, `wallet` - status: `pending`, `authorized`, `captured`, `completed`, `failed`, `refunded` Audited API actions: - `payment.created` - `payment.cash_confirmed` ### payment_webhook_events Columns: - id - provider - event_id unique - type - payload json - status: received, processed, ignored, failed - processed_at nullable - created_at, updated_at ### invoices Columns: - id - uuid unique - payment_id foreign unique - trip_id foreign - invoice_number unique - amount decimal(12,2) - currency default `MAD` - issued_at - file_id nullable foreign - created_at, updated_at Initial invoice rule: - Cash confirmation creates one invoice per payment after the payment reaches `completed`. Payment constraints: - `payments.trip_id, provider` should be unique unless a future split-payment design explicitly changes this rule. - `payment_webhook_events.provider, event_id` must be unique for idempotency. - Wallet balance changes must be represented by transaction rows and applied in database transactions. - Invoice numbers must be generated server-side and remain unique. ## 12. Support, Audit and Integrations ### support_tickets Columns: - id - uuid unique - requester_id foreign users - assigned_to nullable foreign users - trip_id nullable foreign - subject - status: open, pending, resolved, closed - priority: low, normal, high, urgent - created_at, updated_at - closed_at nullable ### audit_logs Columns: - id - actor_id nullable foreign users - action - subject_type nullable - subject_id nullable - ip_address nullable - user_agent nullable - metadata json nullable - created_at Indexes: - actor_id, created_at - subject_type, subject_id - action, created_at Initial authentication actions: - auth.registered - auth.login_succeeded - auth.login_failed - auth.login_blocked - auth.logged_out - auth.email_verification_placeholder_requested - auth.phone_verification_placeholder_requested - auth.verification_resend_placeholder_requested Initial driver and vehicle onboarding actions: - driver.created - driver.updated - driver.availability_updated - vehicle.created - vehicle.updated - vehicle.deleted - driver_document.uploaded - driver_document.reviewed Initial ride actions: - ride_request.created - ride_request.accepted - ride_request.cancelled - ride_request.rejected_by_driver - trip.created - trip.location_recorded - trip.arrived - trip.started - trip.paused - trip.resumed - trip.completed - trip.cancelled Initial communication actions: - conversation.created - conversation.read - message.sent ### external_api_logs Columns: - id - provider - operation - request_id nullable - status: success, failed - http_status nullable - duration_ms nullable - error_message nullable - created_at Indexes: - provider, created_at - status, created_at ## 13. Migration Standards - Migrations must be reversible. - Shared migrations are never edited after release. - New schema changes use new migrations. - Indexes required by the feature are included with the table. - Payment and wallet writes are covered by database transactions. - Seeders use Moroccan examples and never contain secrets. - Every foreign key must document `cascade`, `restrict` or `nullOnDelete` behavior in the migration. - Every enum-like string column must have a matching PHP enum or equivalent centralized constant. - Every JSON column must have tests for required keys before the related module is complete.