Core Entities
Property: property_id, address, city, state, zip, lat, lng, type (APARTMENT, HOUSE, CONDO, TOWNHOUSE), status (FOR_SALE, FOR_RENT, SOLD, OFF_MARKET), price, bedrooms, bathrooms, area_sqft, year_built, description, features (JSON array: pool, garage, gym), agent_id, listed_at, updated_at. Agent: agent_id, name, license_number, phone, email, agency_id, areas_served (city/zip list), rating, total_listings. Listing: listing_id, property_id, agent_id, listing_price, listing_type (SALE, RENT), status, open_house_dates. Inquiry: inquiry_id, property_id, user_id, agent_id, message, status (PENDING, RESPONDED, CLOSED), created_at. SavedSearch: search_id, user_id, filters (JSON), alert_enabled, last_notified_at.
Property Search and Filtering
Users filter by: location (city, zip, radius from a point), price range, bedrooms/bathrooms (min), property type, size range, features (pool, garage), listing status, and date listed. Relational database (PostgreSQL): can handle most filters with composite indexes. Performance problem: geospatial queries (properties within 10 miles of lat/lng) are slow without a spatial index. Solution: use PostGIS extension. Add a geometry column: ALTER TABLE properties ADD COLUMN location GEOMETRY(Point, 4326). Index: CREATE INDEX ON properties USING GIST(location). Query: SELECT * FROM properties WHERE ST_DWithin(location, ST_MakePoint(:lng, :lat)::geography, :radius_meters) AND price BETWEEN :min AND :max AND bedrooms >= :beds ORDER BY price ASC LIMIT 20.
For complex faceted search with full-text on description: index properties in Elasticsearch. Sync from PostgreSQL via Debezium CDC. Elasticsearch geo_point field enables: geo_distance queries (within radius), geo_bounding_box (map viewport). Filters in Elasticsearch: term filters for categorical fields (type, status), range filters for price/bedrooms. Full-text on description uses analyzed text. Return property_ids from Elasticsearch, fetch details from PostgreSQL. This pattern keeps the source of truth in Postgres while Elasticsearch handles search.
Map-Based Search
Users drag a map to browse listings. On each map pan/zoom: send the viewport bounding box (sw_lat, sw_lng, ne_lat, ne_lng) to the API. Query PostGIS: WHERE ST_Within(location, ST_MakeEnvelope(sw_lng, sw_lat, ne_lng, ne_lat, 4326)). Return clustered results for high-zoom-out views (too many points to render): use a clustering algorithm (geohash or H3 hex grids). At zoom level 10: return cluster centroids with counts. At zoom level 14: return individual property pins. Cache viewport queries in Redis with a short TTL (60 seconds) — adjacent users viewing the same area get the same result.
Agent Matching
When a user submits an inquiry, route to the best agent. Matching criteria: agent serves the property’s zip code/city (areas_served contains the property’s location), agent is active and has capacity (current_listings < 50), highest rating. Score = rating_weight * rating + recency_weight * (1 / days_since_last_listing). If no agent matches the zip: fall back to city-level match. If no city match: assign to agency's default agent. Store the routing decision: inquiry.agent_id. Notify the assigned agent via push/email. Track agent response time — slow-responding agents are deprioritized in future routing.
Saved Searches and Alerts
Users save search criteria to receive alerts when new listings match. Store saved searches with their filter JSON. When a new property is listed: a background job (runs every 15 minutes or triggered by the listing event) finds all saved searches whose filters match the new property. For each matching saved search with alert_enabled=true: check last_notified_at to rate-limit alerts (max one alert per search per day). Send a push notification or email with the new listing details. Matching saved searches to a new property: query saved_searches and evaluate filters in application code (feasible for < 1M saved searches). For scale: use a reverse index — for each city/zip, maintain a list of saved_search_ids that target that area. On new listing, only evaluate searches for that area.
Media and Virtual Tours
Property photos: upload via pre-signed S3 URLs (client uploads directly, never through the server). Store photo metadata: photo_id, property_id, s3_key, order_index, width, height. On upload: trigger a Lambda to generate thumbnails (800×600 for list view, 400×300 for map pins). Virtual tours: 360-degree photos stitched into a tour (Matterport-style). Store tour_id, property_id, and the tour manifest URL. Embed the viewer iframe in the listing detail page. Video walkthroughs: store as regular video, transcode to HLS for adaptive streaming.
Asked at: Airbnb Interview Guide
Asked at: Stripe Interview Guide
Asked at: Shopify Interview Guide
Asked at: Atlassian Interview Guide