Detailed Component Design

Deep dive into 2-3 key components. Explain how they work, how they scale, discuss tradeoffs, capacity, and any relevant algorithms or data structures.

DB design:

CREATE TABLE lots (

lot_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

name VARCHAR(100) NOT NULL,

address TEXT NOT NULL,

timezone VARCHAR(50) NOT NULL DEFAULT 'UTC',

-- Geospatial for "find nearby" feature

location GEOGRAPHY(POINT, 4326) NOT NULL,

geo_fence GEOGRAPHY(POLYGON, 4326), -- For mobile app auto-check-in

-- Configuration

max_height_cm INTEGER, -- For "will my truck fit?"

is_active BOOLEAN DEFAULT true,

config JSONB NOT NULL DEFAULT '{

"gate_type": "automated",

"entry_lead_time_minutes": 15,

"grace_period_minutes": 15,

"overstay_rate_per_hour": 10.00

}',

-- Operational hours (ISO 8601 format or custom)

operating_hours JSONB DEFAULT '{

"monday": {"open": "00:00", "close": "23:59"},

"tuesday": {"open": "00:00", "close": "23:59"}

}',

created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),

updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),

CONSTRAINT valid_lot CHECK (name <> '')

);

-- Indexes for geospatial queries (find lots near user)

CREATE INDEX idx_lots_location ON lots USING GIST(location);

CREATE INDEX idx_lots_active ON lots(lot_id) WHERE is_active = true;

CREATE TYPE spot_type AS ENUM (

'COMPACT', -- Small cars only

'REGULAR', -- Standard sedan/SUV

'LARGE', -- Trucks, vans

'HANDICAP', -- Accessibility required

'ELECTRIC', -- EV charging available

'MOTORCYCLE', -- Bike spots (multiple per physical space?)

'BUS', -- Multiple contiguous spots

'VALET_ONLY' -- No self-parking

);

CREATE TYPE spot_status AS ENUM (

'AVAILABLE', -- Free for reservation or drive-up

'OCCUPIED', -- Physically has vehicle

'RESERVED', -- Held for upcoming reservation

'MAINTENANCE', -- Out of order

'BLOCKED' -- Admin blocked (event, cleaning)

);

CREATE TABLE parking_spots (

spot_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

lot_id UUID NOT NULL REFERENCES lots(lot_id) ON DELETE CASCADE,

-- Physical identification

floor_number INTEGER DEFAULT 1,

section_code VARCHAR(10), -- "A", "B", "North", etc.

spot_number VARCHAR(20) NOT NULL, -- "A-101", "P2-45"

-- Physical characteristics

spot_type spot_type NOT NULL DEFAULT 'REGULAR',

width_cm INTEGER,

length_cm INTEGER,

-- Real-time state (cached in Redis, persisted here)

current_status spot_status DEFAULT 'AVAILABLE',

current_vehicle_id UUID REFERENCES vehicles(vehicle_id),

sensor_last_seen TIMESTAMP WITH TIME ZONE, -- IoT heartbeat

-- Hardware integration

sensor_id VARCHAR(50), -- IoT device ID

gate_id VARCHAR(50), -- Which gate controls access

-- Constraints

is_active BOOLEAN DEFAULT true,

-- Prevent duplicate spot numbers per lot

UNIQUE(lot_id, floor_number, section_code, spot_number),

created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()

);

-- Critical indexes for availability queries

CREATE INDEX idx_spots_lot_status ON parking_spots(lot_id, spot_type, current_status)

WHERE is_active = true;

CREATE INDEX idx_spots_sensor ON parking_spots(sensor_id) WHERE sensor_id IS NOT NULL;

CREATE TABLE users (

user_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

email VARCHAR(255) UNIQUE NOT NULL,

phone VARCHAR(20),

-- Payment defaults

default_payment_method_id VARCHAR(50),

-- Reputation/scoring (for dynamic pricing)

no_show_count INTEGER DEFAULT 0,

trust_score INTEGER DEFAULT 100, -- 0-100

created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()

);

CREATE TABLE vehicles (

vehicle_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

user_id UUID NOT NULL REFERENCES users(user_id) ON DELETE CASCADE,

license_plate VARCHAR(20) NOT NULL,

state_province VARCHAR(50),

country VARCHAR(2) DEFAULT 'US',

vehicle_type spot_type NOT NULL DEFAULT 'REGULAR',

color VARCHAR(30),

make_model VARCHAR(100),

-- For license plate recognition confidence

plate_image_url TEXT,

-- Unique per user (one user can't add same plate twice)

UNIQUE(user_id, license_plate),

-- But different users can have same plate (rental cars, family sharing)

created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()

);

-- Lookup for gate LPR (License Plate Recognition)

CREATE INDEX idx_vehicles_plate ON vehicles(license_plate, state_province);

CREATE TYPE reservation_status AS ENUM (

'PENDING', -- Initial hold, not paid yet (15-min TTL)

'CONFIRMED', -- Paid, waiting for start time

'ACTIVE', -- Vehicle entered, currently parked

'COMPLETED', -- Exited normally

'NO_SHOW', -- Never arrived

'CANCELLED', -- User cancelled

'EXPIRED' -- Hold expired without payment

);

CREATE TYPE payment_model AS ENUM (

'HOURLY', -- Pay for actual duration

'DAILY', -- Flat daily rate

'MONTHLY_PASS', -- Subscription covers this

'YEARLY_PASS', -- Subscription covers this

'VALIDATED', -- 3rd party validation (employer, event)

'FREE' -- Comp/promo

);

CREATE TABLE reservations (

reservation_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

-- Relationships

spot_id UUID NOT NULL REFERENCES parking_spots(spot_id),

user_id UUID NOT NULL REFERENCES users(user_id),

vehicle_id UUID NOT NULL REFERENCES vehicles(vehicle_id),

-- Time boundaries (with timezone!)

scheduled_start TIMESTAMP WITH TIME ZONE NOT NULL,

scheduled_end TIMESTAMP WITH TIME ZONE NOT NULL,

-- Actual times (for analytics & billing adjustments)

actual_entry TIMESTAMP WITH TIME ZONE,

actual_exit TIMESTAMP WITH TIME ZONE,

-- Status tracking

status reservation_status NOT NULL DEFAULT 'PENDING',

-- Payment configuration

payment_model payment_model NOT NULL DEFAULT 'HOURLY',

subscription_id UUID REFERENCES subscriptions(subscription_id),

-- Pricing snapshot (prices can change, but reservation locks them)

base_rate_per_hour DECIMAL(10,2),

total_paid DECIMAL(10,2) DEFAULT 0.00,

refund_amount DECIMAL(10,2) DEFAULT 0.00,

-- Early departure handling

early_exit_eligible BOOLEAN DEFAULT false,

early_exit_refund_processed BOOLEAN DEFAULT false,

-- No-show tracking

no_show_charge_processed BOOLEAN DEFAULT false,

-- Concurrency control (Optimistic Locking)

version INTEGER NOT NULL DEFAULT 1,

-- Soft delete for audit trails

deleted_at TIMESTAMP WITH TIME ZONE,

created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),

updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),

-- Constraints

CONSTRAINT valid_time_range CHECK (scheduled_end > scheduled_start),

CONSTRAINT future_reservation CHECK (scheduled_start > created_at - INTERVAL '1 hour'),

-- Prevent double booking at database level!

CONSTRAINT no_double_booking EXCLUDE USING GIST (

spot_id WITH =,

tsrange(scheduled_start, scheduled_end) WITH &&

)

);

-- Critical indexes

CREATE INDEX idx_reservations_user ON reservations(user_id, scheduled_start DESC);

CREATE INDEX idx_reservations_spot ON reservations(spot_id, scheduled_start DESC);

CREATE INDEX idx_reservations_active ON reservations(vehicle_id, status)

WHERE status IN ('CONFIRMED', 'ACTIVE');

CREATE INDEX idx_reservations_pending_cleanup ON reservations(status, created_at)

WHERE status = 'PENDING'; -- For cleanup cron

-- For finding reservations by license plate at gate (LPR lookup)

CREATE INDEX idx_reservations_vehicle_time ON reservations(vehicle_id, scheduled_start, scheduled_end);

Submissions

Displaying Solutions with Scores Ranging from 1 to 10