System Design: Car Rental Platform

Objective

Design a car rental platform with:

• Core entities and relationships (customer, vehicle, vehicle_type, branch/location, inventory/unit, reservation, rental/contract, payment, pricing rule, promotion, insurance, availability/hold, staff, damage report).
• A relational database schema with primary/foreign keys, unique constraints, and essential indexes (e.g., availability lookups by location and date/time windows).
• End-to-end workflows and consistency requirements for:
  1. Searching availability by location and date/time window.
  2. Creating, modifying, and canceling reservations with holds to prevent double-booking.
  3. Pick-up (vehicle assignment, contracts, inspection) and return (mileage, fuel, damage, late fees).
  4. Pricing (base rate, mileage, insurance add-ons, taxes, promotions).
  5. Payments (authorization, capture, refunds).
• 3–5 representative REST endpoints with request/response fields (e.g., POST /search, POST /reservations, PATCH /reservations/{id}, POST /rentals/{id}/return).
• Cross-cutting concerns: idempotency, pagination, authentication/authorization, concurrency control (row locks or optimistic versioning), and scaling (read replicas, partitioning by branch/region, caching hot availability queries).

Constraints and Assumptions

• Use a relational database (e.g., PostgreSQL). Time stored in UTC; branch has local timezone metadata.
• Availability is primarily per vehicle type at a branch; specific unit assignment occurs at pickup.
• Overlaps and double-booking are prevented via transactional holds and constraints.