Ride-sharing / Geospatial

Design the schema for an Uber-like service: riders request trips, drivers accept them, and the system must find the nearest available drivers to a pickup point in real time. Trip history powers earnings and dispute resolution.

Clarifying questions

Are we restricted to a few cities or global? Drives whether geohash is enough or we need PostGIS.
What's the driver-location update rate — every 4 seconds? It changes the write strategy entirely.
Do we model surge pricing as a property of trips, of zones, or of moments in time?
Is dispatch a single matching service that owns the live driver state, or do we want it queryable from the DB?
How long do we retain raw GPS breadcrumbs vs sampled trip paths?

Core requirements

Find the K nearest available drivers to a lat/lng in under 100ms.
Track driver state (offline / available / on_trip) with safe transitions.
Persist trip lifecycle: requested, matched, started, ended, settled.
Store trip route as a sampled polyline for receipt and dispute use.
Compute driver earnings over a time window.
Handle hot zones (airport, stadium) without one row becoming a hot spot.

Canonical schema (PostgreSQL)

CREATE EXTENSION IF NOT EXISTS postgis;

CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  kind TEXT NOT NULL CHECK (kind IN ('rider', 'driver')),
  phone TEXT UNIQUE NOT NULL,
  display_name TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE drivers (
  user_id UUID PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
  vehicle_class TEXT NOT NULL,
  license_plate TEXT NOT NULL,
  rating NUMERIC(3, 2) NOT NULL DEFAULT 5.00,
  status TEXT NOT NULL CHECK (status IN ('offline', 'available', 'on_trip')),
  current_location GEOGRAPHY(POINT, 4326),
  location_updated_at TIMESTAMPTZ
);

CREATE TABLE trips (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  rider_id UUID NOT NULL REFERENCES users(id),
  driver_id UUID REFERENCES users(id),
  status TEXT NOT NULL CHECK (status IN ('requested', 'matched', 'in_progress', 'completed', 'cancelled')),
  pickup GEOGRAPHY(POINT, 4326) NOT NULL,
  dropoff GEOGRAPHY(POINT, 4326),
  fare_minor BIGINT,
  currency CHAR(3),
  requested_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  matched_at TIMESTAMPTZ,
  started_at TIMESTAMPTZ,
  ended_at TIMESTAMPTZ
);

CREATE TABLE trip_breadcrumbs (
  trip_id UUID NOT NULL REFERENCES trips(id) ON DELETE CASCADE,
  occurred_at TIMESTAMPTZ NOT NULL,
  location GEOGRAPHY(POINT, 4326) NOT NULL,
  speed_mps REAL,
  PRIMARY KEY (trip_id, occurred_at)
);

CREATE TABLE driver_earnings_daily (
  driver_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  day DATE NOT NULL,
  trips_count INTEGER NOT NULL DEFAULT 0,
  gross_minor BIGINT NOT NULL DEFAULT 0,
  payout_minor BIGINT NOT NULL DEFAULT 0,
  currency CHAR(3) NOT NULL,
  PRIMARY KEY (driver_id, day)
);

Key indexes

CREATE INDEX idx_drivers_available_loc ON drivers USING GIST (current_location) WHERE status = 'available'

Partial GIST on point geometry — the dispatch query 'nearest K available drivers to point P' touches only matchable rows.

CREATE INDEX idx_trips_driver_recent ON trips(driver_id, ended_at DESC) WHERE status = 'completed'

Driver earnings page paginates completed trips by recency.

CREATE INDEX idx_trips_rider_recent ON trips(rider_id, requested_at DESC)

Rider trip history is sorted by request time descending — supports receipts and disputes.

CREATE INDEX idx_trips_status_requested ON trips(status, requested_at) WHERE status = 'requested'

Dispatcher pulls the open request queue; partial index keeps the working set bounded by demand, not history.

Key decisions to defend

Live driver state lives in Postgres for durability, but the real-time matching path reads from Redis with a geo set — Postgres is the system of record, not the dispatch hot loop.
PostGIS GEOGRAPHY(POINT, 4326) plus a GIST index is the right primitive for global service; geohash strings work for one city but degenerate at antimeridians and high latitudes.
trip_breadcrumbs is a sampled polyline (e.g., every 5 seconds), not raw GPS — storing every fix is a 100x cost increase with no product value.
driver_earnings_daily is denormalized rollup written asynchronously — the driver app reads this, never SUMs over trips at request time.
Hot zones (airport pickups) need surge applied at the trip row, not the zone row, otherwise the zone becomes a write hot spot during peaks.

Likely follow-ups

· Add shared rides (two riders, one driver) — how does trip state model multiple legs?
· Driver locations update every 4s for 1M drivers — what hits Postgres and what doesn't?
· A 50k-fan stadium lets out — how do you prevent dispatch collapse?
· Pay drivers within 5 minutes of a completed trip — what changes in the schema?

Dive deeper

Geospatial questions test whether you know when to leave the database. The dispatch loop should not be running PostGIS queries at 100k QPS — Redis geo or a custom in-memory index owns that path, and Postgres owns durability plus the long tail of history queries. The senior signal is naming this split out loud and not trying to make one system do both jobs.

All database design prompts