Database Schema & ERD

The Auto Glass platform uses PostgreSQL 16 as its primary relational database. The schema is normalized to 3NF with strategic JSONB columns for flexible, schemaless data (ADAS feature sets, photo arrays). All tables use UUID v7 primary keys for sortability and distribution safety.

Entity Relationship Diagram (ERD)

Core Entity Descriptions

Indexes & Query Optimisation

-- Technician availability lookup (Dispatch Engine)
CREATE INDEX idx_bookings_technician_scheduled
  ON bookings(technician_id, scheduled_at)
  WHERE status IN ('confirmed', 'en_route', 'in_progress');

-- Customer booking history (Customer Portal)
CREATE INDEX idx_bookings_customer_created
  ON bookings(customer_id, created_at DESC);

-- Part reorder monitoring
CREATE INDEX idx_parts_low_stock
  ON parts(qty_on_hand)
  WHERE qty_on_hand <= reorder_point;

-- Claims status reporting
CREATE INDEX idx_claims_status_submitted
  ON insurance_claims(status, submitted_at DESC);

-- ADAS calibration lookups per work order
CREATE UNIQUE INDEX idx_calibration_work_order
  ON adas_calibrations(work_order_id);

Data Retention Policy