# DATABASE SCHEMA ANALYSIS

Source basis: migration files in database/migrations plus confirmed runtime usage from models/controllers/services/routes.

## SECTION 1 - ALL TABLES LIST

### Core
- users
- vehicles
- auction_cars
- vehicle_images
- vehicle_documents

### Commerce
- vehicle_reservations
- carts
- orders
- order_items
- payments
- payment_histories
- payment_allocations
- shipments
- bank_accounts
- pricing_rules
- deposit_settings

### Attributes and Catalog
- makes
- models
- attributes
- attribute_values
- model_attributes
- make_attributes
- model_attribute_defaults
- vehicle_attribute_values

### Logistics and Geography
- shipping_countries
- ports
- shipping_rates
- consignees
- remitters
- currencies

### Auth, Team, and Framework
- password_reset_tokens
- sessions
- personal_access_tokens
- teams
- team_user
- team_invitations
- cache
- cache_locks
- jobs
- job_batches
- failed_jobs

## SECTION 2 - TABLE DETAILS

### users
- Purpose:
  - Authentication identity and role partitioning.
- Columns (name | type | nullable):
  - id | bigint PK | no
  - name | string | no
  - email | string unique | no
  - email_verified_at | timestamp | yes
  - password | string | no
  - remember_token | string | yes
  - current_team_id | bigint | yes
  - profile_photo_path | string(2048) | yes
  - two_factor_secret | text | yes
  - two_factor_recovery_codes | text | yes
  - two_factor_confirmed_at | timestamp | yes
  - role | string default customer | no
  - phone | string | yes
  - address | text | yes
  - country | string | yes
  - created_at | timestamp | no
  - updated_at | timestamp | no
- Primary key:
  - id
- Foreign keys:
  - none enforced on current_team_id
- Important indexes:
  - unique(email)

### vehicles
- Purpose:
  - Core sellable inventory records.
- Columns (name | type | nullable):
  - id | bigint PK | no
  - seller_id | bigint FK users | yes
  - auction_car_id | bigint FK auction_cars unique | yes
  - make | string | no
  - model | string | no
  - make_id | bigint FK makes | yes
  - model_id | bigint FK models | yes
  - model_code | string | yes
  - year | year | no
  - mileage | integer | no
  - price | decimal(10,2) | no
  - discount_percentage | decimal(5,2) default 0 | no
  - discount_amount | decimal(10,2) default 0 | no
  - is_featured | boolean default false | no
  - is_clearance | boolean default false | no
  - condition | enum(new,used) default used | no
  - vehicle_type | string | yes
  - stock_country | string | yes
  - country_id | bigint FK shipping_countries | yes
  - color | string | yes
  - transmission | string | yes
  - fuel_type | string | yes
  - engine_capacity | unsignedInteger | yes
  - rec_no | string unique | yes
  - chassis | string | yes
  - grade | string | yes
  - registration_year | string | yes
  - manufacture_year | year | yes
  - location | string | yes
  - length_m | decimal(4,2) | yes
  - width_m | decimal(4,2) | yes
  - height_m | decimal(4,2) | yes
  - volume_m3 | decimal(5,3) | yes
  - seats | integer | yes
  - doors | integer | yes
  - primary_image | string | yes
  - stock_status | enum(available,sold) default available | no
  - status | enum(draft,published,reserved,sold) default draft | no
  - is_ready_for_sale | boolean default true | no
  - created_at | timestamp | no
  - updated_at | timestamp | no
- Primary key:
  - id
- Foreign keys:
  - seller_id -> users.id
  - auction_car_id -> auction_cars.id
  - make_id -> makes.id
  - model_id -> models.id
  - country_id -> shipping_countries.id
- Important indexes:
  - unique(auction_car_id)
  - unique(rec_no)
  - index(make_id, model_id)
  - index(status)
  - index(model_code)
  - index(is_ready_for_sale)

### auction_cars
- Purpose:
  - Auction intake, costing, and sale readiness source for linked vehicles.
- Columns (name | type | nullable):
  - id | bigint PK | no
  - make_id | bigint FK makes | yes
  - model_id | bigint FK models | yes
  - chassis_no | string unique | yes
  - purchase_date | date | yes
  - auction_price | decimal(12,2) | no
  - auction_fee | decimal(12,2) | yes
  - inspection | decimal(12,2) | yes
  - transportation | decimal(12,2) | yes
  - h_charge | decimal(12,2) | yes
  - van | decimal(12,2) | yes
  - insurance | decimal(12,2) | yes
  - freight | decimal(12,2) | yes
  - extra | decimal(12,2) | yes
  - shipper | decimal(12,2) | yes
  - storage | decimal(12,2) | yes
  - total | decimal(12,2) | yes
  - selling_price | decimal(12,2) | yes
  - vessel | string | yes
  - sailing_date | date | yes
  - region | string | yes
  - city | string | yes
  - auction_house | string | yes
  - admin_notes | text | yes
  - status | enum(pending,completed) default pending | no
  - created_at | timestamp | no
  - updated_at | timestamp | no
- Primary key:
  - id
- Foreign keys:
  - make_id -> makes.id
  - model_id -> models.id
- Important indexes:
  - unique(chassis_no)
  - index(make_id, model_id)
  - index(status)

### vehicle_reservations
- Purpose:
  - Time-bound reservation records for inquiry-only inventory.
- Columns (name | type | nullable):
  - id | bigint PK | no
  - vehicle_id | bigint FK vehicles | no
  - user_id | bigint FK users | no
  - status | string(32) | no
  - reserved_at | timestamp useCurrent | no
  - expires_at | timestamp | yes
  - created_at | timestamp | no
  - updated_at | timestamp | no
- Primary key:
  - id
- Foreign keys:
  - vehicle_id -> vehicles.id
  - user_id -> users.id
- Important indexes:
  - index(vehicle_id, status)
  - index(user_id, status)
  - index(expires_at)
  - index(vehicle_id, status, expires_at)

### orders
- Purpose:
  - Purchase/order snapshot and lifecycle record.
- Columns (name | type | nullable):
  - id | bigint PK | no
  - user_id | bigint FK users | no
  - consignee_id | bigint FK consignees | yes
  - remitter_id | bigint FK remitters | yes
  - status | string default pending | no
  - customer_status | enum(reserved,processing,shipping,shipped,delivered,cancelled) default reserved | no
  - total_price | decimal(12,2) | no
  - vehicle_price | decimal(12,2) | yes
  - currency | string(3) default USD | no
  - type | string default C&F | no
  - discount_amount | decimal(10,2) default 0 | no
  - paid_amount | decimal(12,2) default 0 | no
  - remaining_balance | decimal(12,2) default 0 | no
  - min_ship_deposit_percentage | decimal(5,2) default 30.00 | no
  - shipping_cost | decimal(10,2) default 0 | no
  - shipping_cost_reason | text | yes
  - freight_cost | decimal(10,2) | yes
  - inspection_fee | decimal(10,2) | yes
  - insurance_fee | decimal(10,2) | yes
  - cif_total | decimal(12,2) | yes
  - deposit_percentage | decimal(5,2) | yes
  - min_deposit_amount | decimal(12,2) | yes
  - shipping_country_id | bigint FK shipping_countries | yes
  - shipping_port_id | bigint FK ports | yes
  - shipping_type | string(50) | yes
  - inspection_enabled | boolean default false | no
  - insurance_enabled | boolean default false | no
  - tracking_number | string | yes
  - from_port | string | yes
  - arrival_port | string | yes
  - shipping_address | text | yes
  - buy_date | timestamp | yes
  - departure_date | timestamp | yes
  - arrival_date | timestamp | yes
  - dhl_date | timestamp | yes
  - order_date | timestamp | no
  - shipped_at | timestamp | yes
  - delivered_at | timestamp | yes
  - documents | json | yes
  - notes | text | yes
  - consignee_name | string | yes
  - consignee_email | string | yes
  - consignee_phone | string(50) | yes
  - consignee_address | text | yes
  - consignee_country | string | yes
  - remitter_name | string | yes
  - remitter_email | string | yes
  - remitter_phone | string(50) | yes
  - remitter_address | text | yes
  - remitter_country | string | yes
  - created_at | timestamp | no
  - updated_at | timestamp | no
- Primary key:
  - id
- Foreign keys:
  - user_id -> users.id
  - consignee_id -> consignees.id
  - remitter_id -> remitters.id
  - shipping_country_id -> shipping_countries.id
  - shipping_port_id -> ports.id
- Important indexes:
  - created by FK columns and order status usage

### payments
- Purpose:
  - Payment transaction record for orders.
- Columns (name | type | nullable):
  - id | bigint PK | no
  - order_id | bigint FK orders | no
  - bank_account_id | bigint FK bank_accounts | yes
  - approved_by | bigint FK users | yes
  - payment_method | string | no
  - transaction_id | string | yes
  - amount | decimal(10,2) | no
  - status | string default pending | no
  - payment_data | json | yes
  - payment_proof | string | yes
  - bank_reference | string | yes
  - paid_at | timestamp | yes
  - created_at | timestamp | no
  - updated_at | timestamp | no
- Primary key:
  - id
- Foreign keys:
  - order_id -> orders.id
  - bank_account_id -> bank_accounts.id
  - approved_by -> users.id
- Important indexes:
  - created by FK columns

### order_items
- Purpose:
  - Order line items linking orders and vehicles.
- Columns (name | type | nullable):
  - id | bigint PK | no
  - order_id | bigint FK orders | no
  - vehicle_id | bigint FK vehicles | no
  - quantity | integer default 1 | no
  - price | decimal(12,2) | no
  - discount_applied | decimal(12,2) default 0 | no
  - created_at | timestamp | no
  - updated_at | timestamp | no
- Primary key:
  - id
- Foreign keys:
  - order_id -> orders.id
  - vehicle_id -> vehicles.id
- Important indexes:
  - created by FK columns

### carts
- Purpose:
  - Customer pre-checkout basket.
- Columns (name | type | nullable):
  - id | bigint PK | no
  - user_id | bigint FK users | no
  - vehicle_id | bigint FK vehicles | no
  - quantity | integer default 1 | no
  - created_at | timestamp | no
  - updated_at | timestamp | no
- Primary key:
  - id
- Foreign keys:
  - user_id -> users.id
  - vehicle_id -> vehicles.id
- Important indexes:
  - unique(user_id, vehicle_id)

### payment_histories
- Purpose:
  - Aggregated payment receipt and allocation tracking per user/order.
- Columns (name | type | nullable):
  - id | bigint PK | no
  - user_id | bigint FK users | no
  - order_id | bigint FK orders | no
  - currency | string(3) default USD | no
  - received_amount | decimal(12,2) | no
  - allocated_amount | decimal(12,2) default 0 | no
  - balance_amount | decimal(12,2) default 0 | no
  - payment_type | string default deposit | no
  - payment_method | string | no
  - transaction_id | string | yes
  - notes | text | yes
  - receive_date | timestamp | no
  - created_at | timestamp | no
  - updated_at | timestamp | no
- Primary key:
  - id
- Foreign keys:
  - user_id -> users.id
  - order_id -> orders.id
- Important indexes:
  - created by FK columns

### payment_allocations
- Purpose:
  - Allocates a payment to one order amount.
- Columns (name | type | nullable):
  - id | bigint PK | no
  - payment_id | bigint FK payments | no
  - order_id | bigint FK orders | no
  - amount | decimal(12,2) | no
  - created_at | timestamp | no
  - updated_at | timestamp | no
- Primary key:
  - id
- Foreign keys:
  - payment_id -> payments.id
  - order_id -> orders.id
- Important indexes:
  - index(payment_id)
  - index(order_id)

### shipments
- Purpose:
  - Shipment and transport state linked to order and vehicle.
- Columns (name | type | nullable):
  - id | bigint PK | no
  - order_id | bigint FK orders | no
  - vehicle_id | bigint FK vehicles | no
  - arrival_port | string | no
  - buy_date | date | yes
  - ship_ok_date | date | yes
  - departure_date | date | yes
  - arrival_date | date | yes
  - local_dispatch_date | date | yes
  - tracking_number | string | yes
  - origin_port | string | yes
  - destination_port | string | yes
  - shipping_approved_date | date | yes
  - status | string | yes
  - created_at | timestamp | no
  - updated_at | timestamp | no
- Primary key:
  - id
- Foreign keys:
  - order_id -> orders.id
  - vehicle_id -> vehicles.id
- Important indexes:
  - index(status)

### vehicle_images
- Purpose:
  - Stores extra image paths per vehicle.
- Columns (name | type | nullable):
  - id | bigint PK | no
  - vehicle_id | bigint FK vehicles | no
  - image_path | string | no
  - created_at | timestamp | no
  - updated_at | timestamp | no
- Primary key:
  - id
- Foreign keys:
  - vehicle_id -> vehicles.id
- Important indexes:
  - created by FK columns

### vehicle_documents
- Purpose:
  - Stores documents and metadata for each vehicle.
- Columns (name | type | nullable):
  - id | bigint PK | no
  - vehicle_id | bigint FK vehicles | no
  - uploaded_by | bigint FK users | no
  - document_name | string | no
  - file_path | string | no
  - file_name | string | no
  - mime_type | string default application/pdf | no
  - file_size | unsignedBigInteger default 0 | no
  - document_type | string default general | no
  - description | text | yes
  - is_required | boolean default false | no
  - issued_date | timestamp | yes
  - expiry_date | timestamp | yes
  - created_at | timestamp | no
  - updated_at | timestamp | no
- Primary key:
  - id
- Foreign keys:
  - vehicle_id -> vehicles.id
  - uploaded_by -> users.id
- Important indexes:
  - created by FK columns

### makes
- Purpose:
  - Vehicle make catalog.
- Columns (name | type | nullable):
  - id | bigint PK | no
  - name | string unique | no
  - created_at | timestamp | no
  - updated_at | timestamp | no
- Primary key:
  - id
- Foreign keys:
  - none
- Important indexes:
  - unique(name)

### models
- Purpose:
  - Vehicle model catalog under make.
- Columns (name | type | nullable):
  - id | bigint PK | no
  - make_id | bigint FK makes | no
  - name | string | no
  - model_code | string | yes
  - created_at | timestamp | no
  - updated_at | timestamp | no
- Primary key:
  - id
- Foreign keys:
  - make_id -> makes.id
- Important indexes:
  - unique(make_id, name)
  - index(model_code)

### attributes
- Purpose:
  - Attribute definitions for dynamic catalog values.
- Columns (name | type | nullable):
  - id | bigint PK | no
  - name | string unique | no
  - type | enum(select,multi_select,range,boolean) | no
  - created_at | timestamp | no
  - updated_at | timestamp | no
- Primary key:
  - id
- Foreign keys:
  - none
- Important indexes:
  - unique(name)

### attribute_values
- Purpose:
  - Permitted values for attributes.
- Columns (name | type | nullable):
  - id | bigint PK | no
  - attribute_id | bigint FK attributes | no
  - value | string | no
  - created_at | timestamp | no
  - updated_at | timestamp | no
- Primary key:
  - id
- Foreign keys:
  - attribute_id -> attributes.id
- Important indexes:
  - unique(attribute_id, value)

### model_attributes
- Purpose:
  - Maps attributes to models with defaults and numeric ranges.
- Columns (name | type | nullable):
  - id | bigint PK | no
  - model_id | bigint FK models | no
  - attribute_id | bigint FK attributes | no
  - min_value | decimal(12,2) | yes
  - max_value | decimal(12,2) | yes
  - default_value | json | yes
  - created_at | timestamp | no
  - updated_at | timestamp | no
- Primary key:
  - id
- Foreign keys:
  - model_id -> models.id
  - attribute_id -> attributes.id
- Important indexes:
  - unique(model_id, attribute_id)

### make_attributes
- Purpose:
  - Make-level default attribute mappings.
- Columns (name | type | nullable):
  - id | bigint PK | no
  - make_id | bigint FK makes | no
  - attribute_id | bigint FK attributes | no
  - min_value | unsignedInteger | yes
  - max_value | unsignedInteger | yes
  - default_value_id | bigint FK attribute_values | yes
  - default_raw_value | string | yes
  - created_at | timestamp | no
  - updated_at | timestamp | no
- Primary key:
  - id
- Foreign keys:
  - make_id -> makes.id
  - attribute_id -> attributes.id
  - default_value_id -> attribute_values.id
- Important indexes:
  - unique(make_id, attribute_id)

### model_attribute_defaults
- Purpose:
  - Stores model-level default value records.
- Columns (name | type | nullable):
  - id | bigint PK | no
  - model_id | bigint FK models | no
  - attribute_id | bigint FK attributes | no
  - value_id | bigint FK attribute_values | yes
  - raw_value | string | yes
  - created_at | timestamp | no
  - updated_at | timestamp | no
- Primary key:
  - id
- Foreign keys:
  - model_id -> models.id
  - attribute_id -> attributes.id
  - value_id -> attribute_values.id
- Important indexes:
  - unique(model_id, attribute_id)

### vehicle_attribute_values
- Purpose:
  - Vehicle-level stored attributes and selected values.
- Columns (name | type | nullable):
  - id | bigint PK | no
  - vehicle_id | bigint FK vehicles | no
  - attribute_id | bigint FK attributes | no
  - value_id | bigint FK attribute_values | yes
  - raw_value | string | yes
  - created_at | timestamp | no
  - updated_at | timestamp | no
- Primary key:
  - id
- Foreign keys:
  - vehicle_id -> vehicles.id
  - attribute_id -> attributes.id
  - value_id -> attribute_values.id
- Important indexes:
  - unique(vehicle_id, attribute_id, value_id, raw_value)
  - index(vehicle_id)
  - index(attribute_id, value_id)

### shipping_countries
- Purpose:
  - Shipping destinations and optional default currency.
- Columns (name | type | nullable):
  - id | bigint PK | no
  - name | string unique | no
  - code | string(8) | yes
  - currency_id | bigint FK currencies | yes
  - is_active | boolean default true | no
  - created_at | timestamp | no
  - updated_at | timestamp | no
- Primary key:
  - id
- Foreign keys:
  - currency_id -> currencies.id
- Important indexes:
  - unique(name)
  - index(code)

### ports
- Purpose:
  - Destination ports grouped under shipping countries.
- Columns (name | type | nullable):
  - id | bigint PK | no
  - country_id | bigint FK shipping_countries | no
  - name | string | no
  - is_active | boolean default true | no
  - created_at | timestamp | no
  - updated_at | timestamp | no
- Primary key:
  - id
- Foreign keys:
  - country_id -> shipping_countries.id
- Important indexes:
  - unique(country_id, name)

### shipping_rates
- Purpose:
  - Freight pricing matrix by country, port, vehicle type, shipping type.
- Columns (name | type | nullable):
  - id | bigint PK | no
  - country_id | bigint FK shipping_countries | no
  - port_id | bigint FK ports | no
  - vehicle_type | string | no
  - shipping_type | enum(roro,container) | no
  - price | decimal(12,2) | no
  - currency | string(3) default USD | no
  - created_at | timestamp | no
  - updated_at | timestamp | no
- Primary key:
  - id
- Foreign keys:
  - country_id -> shipping_countries.id
  - port_id -> ports.id
- Important indexes:
  - index(country_id, port_id, vehicle_type, shipping_type)
  - unique(country_id, port_id, vehicle_type, shipping_type)

### consignees
- Purpose:
  - Saved consignee profiles per user.
- Columns (name | type | nullable):
  - id | bigint PK | no
  - user_id | bigint FK users | no
  - name | string | no
  - email | string | yes
  - phone | string | no
  - address | text | no
  - city | string | no
  - country | string | no
  - postal_code | string | yes
  - is_default | boolean default false | no
  - created_at | timestamp | no
  - updated_at | timestamp | no
- Primary key:
  - id
- Foreign keys:
  - user_id -> users.id
- Important indexes:
  - index(user_id)

### remitters
- Purpose:
  - Saved remitter profiles per user.
- Columns (name | type | nullable):
  - id | bigint PK | no
  - user_id | bigint FK users | no
  - name | string | no
  - email | string | yes
  - phone | string | no
  - address | text | no
  - city | string | no
  - country | string | no
  - postal_code | string | yes
  - is_default | boolean default false | no
  - created_at | timestamp | no
  - updated_at | timestamp | no
- Primary key:
  - id
- Foreign keys:
  - user_id -> users.id
- Important indexes:
  - index(user_id)

### currencies
- Purpose:
  - Currency master list.
- Columns (name | type | nullable):
  - id | bigint PK | no
  - name | string | no
  - code | char(3) unique | no
  - symbol | string | yes
  - created_at | timestamp | no
  - updated_at | timestamp | no
- Primary key:
  - id
- Foreign keys:
  - none
- Important indexes:
  - unique(code)

### bank_accounts
- Purpose:
  - Bank account master used by payments.
- Columns (name | type | nullable):
  - id | bigint PK | no
  - bank_name | string | no
  - account_title | string | no
  - account_number | string | no
  - iban | string | yes
  - swift_code | string | yes
  - branch | string | yes
  - is_active | boolean default true | no
  - created_at | timestamp | no
  - updated_at | timestamp | no
- Primary key:
  - id
- Foreign keys:
  - none
- Important indexes:
  - none explicit

### pricing_rules
- Purpose:
  - Configurable inspection/insurance pricing overrides.
- Columns (name | type | nullable):
  - id | bigint PK | no
  - type | enum(inspection,insurance) | no
  - country_id | bigint FK shipping_countries | yes
  - port_id | bigint FK ports | yes
  - vehicle_type | string | yes
  - value | decimal(12,2) | no
  - value_type | enum(fixed,percentage) default fixed | no
  - is_active | boolean default true | no
  - created_at | timestamp | no
  - updated_at | timestamp | no
- Primary key:
  - id
- Foreign keys:
  - country_id -> shipping_countries.id
  - port_id -> ports.id
- Important indexes:
  - index(type, is_active)
  - index(country_id, port_id)

### deposit_settings
- Purpose:
  - Stores active deposit policy values.
- Columns (name | type | nullable):
  - id | bigint PK | no
  - type | string(20) | no
  - value | decimal(12,2) | no
  - is_active | boolean default true | no
  - created_at | timestamp | no
  - updated_at | timestamp | no
- Primary key:
  - id
- Foreign keys:
  - none
- Important indexes:
  - index(is_active)

### password_reset_tokens
- Purpose:
  - Password reset token storage.
- Columns (name | type | nullable):
  - email | string PK | no
  - token | string | no
  - created_at | timestamp | yes
- Primary key:
  - email
- Foreign keys:
  - none
- Important indexes:
  - none explicit

### sessions
- Purpose:
  - Session persistence.
- Columns (name | type | nullable):
  - id | string PK | no
  - user_id | bigint | yes
  - ip_address | string(45) | yes
  - user_agent | text | yes
  - payload | longText | no
  - last_activity | integer | no
- Primary key:
  - id
- Foreign keys:
  - none
- Important indexes:
  - index(user_id)
  - index(last_activity)

### personal_access_tokens
- Purpose:
  - Sanctum API token storage.
- Columns (name | type | nullable):
  - id | bigint PK | no
  - tokenable_type | string | no
  - tokenable_id | bigint | no
  - name | text | no
  - token | string(64) unique | no
  - abilities | text | yes
  - last_used_at | timestamp | yes
  - expires_at | timestamp | yes
  - created_at | timestamp | no
  - updated_at | timestamp | no
- Primary key:
  - id
- Foreign keys:
  - polymorphic tokenable reference
- Important indexes:
  - unique(token)
  - index(tokenable_type, tokenable_id)
  - index(expires_at)

### teams
- Purpose:
  - Jetstream team records.
- Columns (name | type | nullable):
  - id | bigint PK | no
  - user_id | bigint | no
  - name | string | no
  - personal_team | boolean | no
  - created_at | timestamp | no
  - updated_at | timestamp | no
- Primary key:
  - id
- Foreign keys:
  - none explicit
- Important indexes:
  - index(user_id)

### team_user
- Purpose:
  - Team memberships and role in team.
- Columns (name | type | nullable):
  - id | bigint PK | no
  - team_id | bigint | no
  - user_id | bigint | no
  - role | string | yes
  - created_at | timestamp | no
  - updated_at | timestamp | no
- Primary key:
  - id
- Foreign keys:
  - none explicit
- Important indexes:
  - unique(team_id, user_id)

### team_invitations
- Purpose:
  - Pending team invitations by email.
- Columns (name | type | nullable):
  - id | bigint PK | no
  - team_id | bigint FK teams | no
  - email | string | no
  - role | string | yes
  - created_at | timestamp | no
  - updated_at | timestamp | no
- Primary key:
  - id
- Foreign keys:
  - team_id -> teams.id
- Important indexes:
  - unique(team_id, email)

### cache
- Purpose:
  - Laravel cache key-value storage.
- Columns (name | type | nullable):
  - key | string PK | no
  - value | mediumText | no
  - expiration | integer | no
- Primary key:
  - key
- Foreign keys:
  - none
- Important indexes:
  - index(expiration)

### cache_locks
- Purpose:
  - Laravel cache lock storage.
- Columns (name | type | nullable):
  - key | string PK | no
  - owner | string | no
  - expiration | integer | no
- Primary key:
  - key
- Foreign keys:
  - none
- Important indexes:
  - index(expiration)

### jobs
- Purpose:
  - Queue jobs table.
- Columns (name | type | nullable):
  - id | bigint PK | no
  - queue | string | no
  - payload | longText | no
  - attempts | unsignedTinyInteger | no
  - reserved_at | unsignedInteger | yes
  - available_at | unsignedInteger | no
  - created_at | unsignedInteger | no
- Primary key:
  - id
- Foreign keys:
  - none
- Important indexes:
  - index(queue)

### job_batches
- Purpose:
  - Queue batch metadata.
- Columns (name | type | nullable):
  - id | string PK | no
  - name | string | no
  - total_jobs | integer | no
  - pending_jobs | integer | no
  - failed_jobs | integer | no
  - failed_job_ids | longText | no
  - options | mediumText | yes
  - cancelled_at | integer | yes
  - created_at | integer | no
  - finished_at | integer | yes
- Primary key:
  - id
- Foreign keys:
  - none
- Important indexes:
  - none explicit

### failed_jobs
- Purpose:
  - Failed queue job records.
- Columns (name | type | nullable):
  - id | bigint PK | no
  - uuid | string unique | no
  - connection | text | no
  - queue | text | no
  - payload | longText | no
  - exception | longText | no
  - failed_at | timestamp useCurrent | no
- Primary key:
  - id
- Foreign keys:
  - none
- Important indexes:
  - unique(uuid)

## SECTION 3 - RELATIONSHIPS

- Vehicle belongs to AuctionCar via vehicles.auction_car_id -> auction_cars.id (1:1 from vehicle side because auction_car_id is unique).
- Vehicle belongs to seller User via vehicles.seller_id -> users.id.
- Vehicle belongs to Make and Model via make_id/model_id.
- Vehicle belongs to ShippingCountry via country_id.
- AuctionCar belongs to Make and Model via make_id/model_id.
- VehicleReservation belongs to Vehicle and User via vehicle_id and user_id.
- Reservation chain is vehicle_reservations -> vehicles -> users (seller/customer contexts used in services and controllers).
- Order belongs to User via orders.user_id.
- Order optionally belongs to Consignee and Remitter via consignee_id and remitter_id.
- Order has many OrderItems, Payments, Shipments.
- OrderItem belongs to Order and Vehicle.
- Payment belongs to Order; optional bank_account_id links to bank_accounts; optional approved_by links to users.
- PaymentAllocation belongs to Payment and Order.
- PaymentHistory belongs to User and Order.
- Cart belongs to User and Vehicle.
- Port belongs to ShippingCountry.
- ShippingRate belongs to ShippingCountry and Port.
- PricingRule optionally belongs to ShippingCountry and Port.
- TeamInvitation belongs to Team.
- PersonalAccessToken uses polymorphic tokenable_type/tokenable_id.

## SECTION 4 - VEHICLE AND AUCTION STRUCTURE (IMPORTANT)

### vehicles table deep structure
- vehicles is the public/commercial inventory table and includes:
  - Legacy text identity fields: make, model.
  - Normalized identity fields: make_id, model_id, model_code.
  - Core commerce fields: price, status, stock_status, is_ready_for_sale.
  - Auction linkage: auction_car_id (nullable unique).
  - Logistics and physical specs: country_id, stock_country, dimensions, seats, doors.
  - Legacy denormalized feature fields: fuel_type, transmission, color, vehicle_type.

### auction_cars table deep structure
- auction_cars is the auction intake and costing table and includes:
  - Auction identity and sourcing: make_id, model_id, chassis_no, purchase_date, auction_house, region, city.
  - Cost stack: auction_price, auction_fee, inspection, transportation, h_charge, van, insurance, freight, extra, shipper, storage.
  - Rollups and sales inputs: total, selling_price.
  - Sale lifecycle state: status pending/completed.

### overlapping fields between vehicles and auction_cars
- Taxonomy overlap:
  - Both tables carry make/model relation context (vehicles.make_id/model_id and auction_cars.make_id/model_id).
- Pricing overlap:
  - vehicles has price.
  - auction_cars has selling_price and full cost stack.
- Sales state overlap:
  - vehicles has status, stock_status, is_ready_for_sale.
  - auction_cars has status.

### pricing control in runtime behavior
- Runtime pricing is auction-prioritized when a linked auction_cars row has selling_price.
- If selling_price is unavailable, runtime falls back to vehicles.price.
- This behavior is implemented in the Vehicle model accessor and used in listing and purchase flows.

### legacy fields currently present
- vehicles.make and vehicles.model are still stored alongside make_id/model_id.
- vehicles stock_country (string) is present alongside country_id relation.
- vehicles fuel_type/transmission/color/vehicle_type coexist with dynamic attribute storage (vehicle_attribute_values).
- Removed fields in schema history:
  - vehicles.features_json was dropped.
  - auction_cars.make and auction_cars.model text columns were dropped.

## SECTION 5 - RESERVATION STRUCTURE

### vehicle_reservations table
- Key structure:
  - vehicle_id + user_id + status + reserved_at + expires_at.
- Status values used by business logic:
  - active
  - expired
  - converted

### status behavior
- Active reservations are those with:
  - status = active
  - expires_at > now
- Expired and converted are terminal states for reservation lifecycle handling.

### expiry behavior
- Time-based expiration is executed by command reservations:expire.
- Scheduling exists in routes/console.php and runs hourly.
- Expiry updates overdue active reservations to expired.

### indexes used
- vehicle_id + status for vehicle-level active lookups.
- user_id + status for customer-level active reservation checks.
- expires_at for expiration scans.
- vehicle_id + status + expires_at composite for combined active/expiry filtering.

## SECTION 6 - USER STRUCTURE

### users table structure
- users contains identity, authentication, optional profile fields, and role.
- Role storage is in users.role (string column, default customer).

### roles
- Application role values used in middleware and route groups:
  - admin
  - seller
  - customer

### role enforcement
- RoleMiddleware checks exact role string on protected routes.
- Route groups are segmented by middleware role:admin, role:seller, role:customer.

## SECTION 7 - MISSING OR UNUSED STRUCTURES

### tables present but framework/system oriented
- cache, cache_locks, jobs, job_batches, failed_jobs, sessions, password_reset_tokens, personal_access_tokens are framework infrastructure tables.

### tables with weak/limited business-surface evidence in current flow
- carts exists with model/controller support but purchase flow is primarily order/order_items based.
- team_user and team_invitations exist from Jetstream teams and are not part of the vehicle commerce domain.

### fields removed in migrations (historical)
- vehicles.features_json removed.
- auction_cars.make and auction_cars.model removed.
- attributes.applies_to added then removed.
- makes.code added then removed.

### duplicate or overlapping data currently stored
- vehicles.make/model duplicates normalized make_id/model_id relationships.
- vehicles.stock_country duplicates normalized country_id relationship.
- vehicles feature-like columns (fuel_type/transmission/color/vehicle_type) overlap with vehicle_attribute_values.
- vehicles.price and auction_cars.selling_price overlap as potential sale-price sources.

## SECTION 8 - COMMERCE STRUCTURE (CRITICAL)

### orders and payments existence
- orders table exists and is active.
- payments table exists and is active.
- Related commerce tables also exist and are active:
  - order_items
  - payment_allocations
  - payment_histories
  - shipments
  - vehicle_reservations

### how purchase is currently handled
- Purchase records are created in orders.
- Purchased vehicle linkage is stored in order_items (orders.vehicle_id was removed by migration).
- Payments are stored in payments and reconciled through payment service logic and payment allocation/history tables.
- Shipment lifecycle is stored in shipments.
- Reservation flow for inquiry-only vehicles is stored in vehicle_reservations and converted/expired via service and scheduled command logic.

## SECTION 9 - SUMMARY

- Current architecture type:
  - Role-segmented Laravel marketplace schema with auction-integrated inventory, dynamic attribute cataloging, and order/payment lifecycle tables.
- What is complete:
  - Core inventory model (vehicles + auction_cars link).
  - Reservation persistence and expiry infrastructure.
  - Order and payment persistence model (orders, order_items, payments, allocations, histories, shipments).
  - Catalog, shipping, and party profile support tables.
- What is missing for a full marketplace system in current schema state:
  - Marketplace-level review/rating tables are not present.
  - Dispute/returns workflow tables are not present.
  - Promotion/coupon campaign tables are not present.
  - Dedicated invoice table is not present (order snapshot fields are embedded in orders).