Property Scraper: An Engineering Retrospective
A technical retrospective on the architectural evolution of an autonomous property scraping platform for Nigerian Real Estate Markets
Introduction & Core Rationale
In mature real estate markets, access to listing data is facilitated by standardized interfaces like the Multiple Listing Service (MLS) or unified APIs. In developing regions such as Nigeria, however, the real estate market is highly fragmented. Property Listings are spread and even duplicated across several online portals, as agents seek to maximize every opportunity at their disposal to dispose of a property. Each portal has distinct API limitations, inconsistent HTML structures, and aggressive anti-bot protections.
In order to aggregate pricing trends, map canonical neighbourhoods, and track listing lifecycles, I built Property Scraper: an autonomous data pipeline designed to ingest, normalize, and monitor real estate listings.
┌──────────────────────────────┐
│ Orchestrator Loop │
└──────────────┬───────────────┘
│
┌───────────────────────┴───────────────────────┐
▼ ▼
┌──────────────────────────────┐ ┌──────────────────────────────┐
│ Discovery Mode (Weekly) │ │ Health Check Mode (Daily) │
├──────────────────────────────┤ ├──────────────────────────────┤
│ Scrapes portal search pages │ │ Crawls active listings │
│ to discover new active items │ │ directly to check statuses │
└──────────────┬───────────────┘ └──────────────┬───────────────┘
│ │
└───────────────────────┬───────────────────────┘
▼
┌──────────────────────────────┐
│ Database Writer / Normal │
├──────────────────────────────┤
│ Ingests, updates, normalises │
│ listings & records history │
└──────────────────────────────┘The system runs in two primary modes that make up the lifecycle for listings by providing entry and exit nodes:
- Discovery Scraper (Weekly): The primary entry node. It crawls search result feeds on a weekly schedule to ingest new listings.
- Health Checker (Daily): The primary exit node. It checks direct URLs of active listings to detect removals, sales, or price adjustments.
Running a basic crawler is simple, but running it autonomously under strict resource limits presented structural, database, and network challenges. This post documents the architectural bottlenecks I encountered and how I engineered solutions to resolve them.
The Database Constraint: Bypassing the 500MB Free Tier Cap
The backend is backed by a PostgreSQL database hosted on Supabase’s free tier, which imposes a strict 500 MB storage cap. When scaling to tens of thousands of listings, database design must account for index size and write-amplification.
The Threat of Index Bloat
B-Tree indexes on search keys (e.g. (source, external_id), neighbourhood, and dates) grow dynamically. For a table containing 40,000+ listings, index storage can easily exceed raw table storage, threatening to trigger Supabase read-only lockouts.
Additionally, the listing history table (raw_data.listing_history) records every lifecycle event (LISTED, PRICE_CHANGE, REMOVED). Because listings undergo relatively frequent price revisions, the history table grows exponentially faster than the primary listings table.
To mitigate this, I defined optimized schemas and partial indexes. The simplified SQL definitions are as follows:
CREATE SCHEMA IF NOT EXISTS raw_data;
CREATE TABLE raw_data.scraped_listings (
id SERIAL PRIMARY KEY,
source VARCHAR(50) NOT NULL,
external_id VARCHAR(100) NOT NULL,
url TEXT NOT NULL,
title VARCHAR(255),
price_kobo BIGINT,
neighbourhood VARCHAR(100),
listing_status VARCHAR(20) DEFAULT 'ACTIVE',
missed_run_count INT DEFAULT 0,
first_seen_at TIMESTAMPTZ DEFAULT NOW(),
last_seen_at TIMESTAMPTZ DEFAULT NOW(),
last_health_check_at TIMESTAMPTZ,
next_health_check_at TIMESTAMPTZ,
CONSTRAINT unique_source_external UNIQUE (source, external_id)
);
CREATE TABLE raw_data.listing_history (
id SERIAL PRIMARY KEY,
listing_id INT REFERENCES raw_data.scraped_listings(id) ON DELETE CASCADE,
event_type VARCHAR(20) CHECK (event_type IN ('LISTED', 'PRICE_CHANGE', 'REMOVED')),
old_value BIGINT,
new_value BIGINT,
event_date TIMESTAMPTZ DEFAULT NOW()
);
-- Partial index targeting only active items to prevent index bloat on historic/removed records
CREATE INDEX IF NOT EXISTS idx_sl_next_health_check
ON raw_data.scraped_listings (next_health_check_at ASC NULLS FIRST)
WHERE listing_status = 'ACTIVE';The Adaptive Cooldown Mechanism (Deep-Dive)
The Linear Cooldown Trap
I’ve had to learn a lot by trial-and-error. Initially, a flat 2-day cooldown was applied across all listings. The health checker was forced to request and parse every single active listing absent from current feeds on every run.
- At 1,000 database rows, this required ~850 direct HTTP requests per health check run.
- At 10,000 database rows, this required ~8,500 direct HTTP requests per health check run.
- Projected to 40,000 listings, the health checker would need to check ~38,500 URLs per run, resulting in linear growth in runtime, increased proxy costs, and IP bans.
What’s worse? Health-checks have to possess considerable cooldown mechanisms in order to enforce some level of fair usage and also to fly under the listing portal’s bot detection radar. This meant that health checks took hours, and it got worse every week. Add that to constant network and electricity issues, and you have yourself some real problems.
Volatility Cohort Selection
To optimize check frequency, I analyzed the statistical distribution of listing deletions and price updates relative to a listing’s age (NOW() - first_seen_at). The data demonstrated a clear Pareto distribution: listings are highly volatile during their first two weeks (high rate of transaction closures and price drops). Between weeks two and eight, activity stabilizes. Beyond eight weeks, listing updates become rare.
I mapped this behavior to three volatility cohorts:
- Volatile (
< 14 days old): check every1.9 days(45h). - Medium (
14–60 days old): check every6.8 days(163h). - Stable (
> 60 days old): check every13.8 days(331h).
Jitter buffers (the -0.1 day offsets) prevent scheduling variations from shifting a check past the execution window and delaying it by 24 hours. This cohort structure reduces daily steady-state HTTP checks by ~79% (from 4,533 to 959 checks/day at 10k active listings).
Dynamic SQL vs. Write-Time Execution Plans
The attempt to implement this adaptive cooldown mehanism also brought with it some unique problems. One of such problems was the realization that evaluating cohort intervals dynamically inside SQL queries prevents database index optimization. Consider the dynamic query structure:
-- Query A: Dynamic interval comparison
SELECT id FROM raw_data.scraped_listings
WHERE last_health_check_at < NOW() - (
CASE
WHEN first_seen_at >= NOW() - INTERVAL '14 days' THEN INTERVAL '1.9 days'
WHEN first_seen_at >= NOW() - INTERVAL '60 days' THEN INTERVAL '6.8 days'
ELSE INTERVAL '13.8 days'
END
);Database Query Planner Analysis
When PostgreSQL compiles Query A, it cannot predict the target value of the comparison because it is a variable expression dependent on the first_seen_at column of each row. As a result:
- Query Plan: The PostgreSQL query planner aborts B-Tree index lookups on
last_health_check_atand falls back to aSeq Scan(Sequential Table Scan). - CPU Utilization: Every single active record in the database must be loaded into memory and evaluated against the conditional statement.
- Execution Scaling: At 40,000 rows, this query takes several seconds and generates severe CPU spikes, triggering Supabase CPU throttling limits.
The Write-Time Resolution
To ensure sub-millisecond query performance, I shifted the scheduling logic to write-time and front-ran it. Essentially, when a listing’s health check is completed, the application computes the next check date in Python and stores it directly in the next_health_check_at column:
# Write-time calculation in db_writer.py
age_days = (now - first_seen_at).days
if age_days < 14:
interval = timedelta(days=1.9)
elif age_days < 60:
interval = timedelta(days=6.8)
else:
interval = timedelta(days=13.8)
next_health_check_at = now + intervalThis updates the database state directly. The scheduler lookup is simplified to a direct range comparison:
-- Query B: Optimized static comparison
SELECT id, source, external_id, url, first_seen_at, price_kobo
FROM raw_data.scraped_listings
WHERE listing_status = 'ACTIVE'
AND missed_run_count > 0
AND (
next_health_check_at IS NULL
OR next_health_check_at < NOW()
)
ORDER BY ...
LIMIT %s;Query Execution Plan Comparison
-- Query A (Dynamic case evaluation):
Seq Scan on scraped_listings (cost=0.00..1245.00 rows=3500 width=128)
Filter: (last_health_check_at < (now() - CASE WHEN ... END))
-- Query B (Write-time static calculation with Partial Index):
Index Scan using idx_sl_next_health_check on scraped_listings (cost=0.28..12.50 rows=150 width=128)
Index Cond: (next_health_check_at < now())Partial Index Architecture
Defining the B-Tree index with a WHERE listing_status = 'ACTIVE' filter leads to the following advantageous scenarios:
- Reduced Storage Footprint: Historical and removed records (which account for an increasing share of the database over time) are excluded from the index.
- Buffer Pool Efficiency: The index remains small enough to fit completely in the database’s RAM cache (buffer pool), preventing slow disk reads during scheduler checks.
- Index Maintenance Overhead: Write operations on
REMOVEDlistings bypass this index entirely, reducing write-amplification.
Bounded Execution: Starvation Prevention & Overdue Ratio Sorting
As mentioned earlier, the running time of the scraper on health-checks was starting to become a real problem. In order to cap runner execution time, I enforced a strict check limit of 1,000 listings per health check run. However, capping the queue introduced a bottleneck: **Queue Starvation.
The Queue Starvation Bottleneck
Because fresh volatile listings (checked every 1.9 days now) generate missed runs rapidly, sorting candidates by missed_run_count DESC or last_health_check_at ASC meant that volatile listings dominated the 1,000 queue slots. Older stable listings (checked every 13.8 days) were repeatedly pushed out, causing them to wait indefinitely.
Resolution: The Overdue Ratio Sorting Algorithm
To solve this, I replaced the static sorting with a dynamic Overdue Ratio calculation. The ratio measures how late a listing is relative to its specific cohort interval:
$$\text{Overdue Ratio} = \frac{\text{NOW}() - \text{last_health_check_at}}{\text{Cohort Interval}}$$
By sorting listings by this ratio in descending order, I prioritized listings by their relative urgency. A stable listing overdue by 5 days ($\text{ratio} \approx 1.36$) will always be checked before a volatile listing overdue by only 4 hours ($\text{ratio} \approx 1.08$).
See Implementation details below:
ORDER BY
CASE
WHEN next_health_check_at IS NULL THEN 1.0
ELSE EXTRACT(EPOCH FROM (NOW() - last_health_check_at)) /
EXTRACT(EPOCH FROM (
CASE
WHEN first_seen_at >= NOW() - INTERVAL '14 days' THEN INTERVAL '1.9 days'
WHEN first_seen_at >= NOW() - INTERVAL '60 days' THEN INTERVAL '6.8 days'
ELSE INTERVAL '13.8 days'
END
))
END DESC,
last_health_check_at ASC NULLS FIRST
LIMIT 1000;Network Resiliency: Bypassing Anti-Bot Walls via Residential IP Proxying
Real estate portals actively block known data center and cloud server IP ranges (such as AWS, Azure, and GitHub Actions runners), returning 403 Forbidden errors. They also maintain blacklists of public Tor exit nodes. This means that running the scraper on a schedule with GitHub Actions or Circle CI fails horribly due to the fact that the IP ranges of these data-centres are known, and as such, listing portals can simply maintain a whitelist of allowed IPs from these ranges and block out the rest.
To run the scraper autonomously in the cloud, I routed outgoing runner requests through my PC’s residential IP.
[ GitHub Actions Runner (Cloud) ]
│ (HTTPS Request routed via HTTP_PROXY environment variables)
▼
[ ngrok Cloud Server ]
│ (Secure TCP Tunnel)
▼
[ ngrok Client (Home PC) ]
│ (TCP Forwarding)
▼
[ start_tunnel.py Proxy (Port 8118) ]
│ (CONNECT / GET forwarding to target server)
▼
[ Real Estate Portal (e.g. PropertyPro) ]Multi-threaded Local Proxy Daemon
I built a local python proxy server (start_tunnel.py) that runs constantly on my PC. Its workflow is as follows:
- Proxy Server Loop: Listens on port
8118. For HTTPSCONNECTrequests, it resolves the destination host, opens a raw TCP connection, returns a200 Connection Establishedresponse to the client, and spawns a thread to stream bytes bidirectionally. - Socket Multiplexing: Uses
select.select()to multiplex data transfer between sockets. - Subprocess Tunneling: Launches an
ngrok tcp 8118tunnel and queries ngrok’s local management API (http://127.0.0.1:4040/api/tunnels) to fetch the active public URL. - Secret Rotation: Converts the
tcp://URL tohttp://and calls the GitHub CLI (gh secret set PROXY_URL --body "<http-url>") to update the remote repository secrets automatically.
Below is the socket multiplexing block in start_tunnel.py:
# handle_proxy_client connection stream handler
if method == 'CONNECT':
host, port = url.split(':')
port = int(port)
remote_socket = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
remote_socket.connect((host, port))
client_socket.sendall(b"HTTP/1.1 200 Connection Established\r\n\r\n")
else:
# Handle direct HTTP requests...
pass
# Multiplexing stream sockets using select()
sockets = [client_socket, remote_socket]
while True:
r, _, _ = select.select(sockets, [], [], 15)
if not r:
break
for s in r:
data = s.recv(4096)
if not data:
return
if s is client_socket:
remote_socket.sendall(data)
else:
client_socket.sendall(data)On the GitHub Actions runner, the environment variable configuration maps the dynamic URL:
env:
HTTP_PROXY: ${{ secrets.PROXY_URL }}
HTTPS_PROXY: ${{ secrets.PROXY_URL }}The python health checker session uses trust_env=True on aiohttp.ClientSession to automatically route requests through the active residential tunnel.
The Local Workaround for Autonomous Execution
While routing GitHub Actions through the my PC’s residential IP works, running the scraping pipeline directly on a home local computer (e.g. a desktop or laptop) provides full local autonomy. This bypasses GitHub runner execution limits entirely.
However, running production scripts on personal computers instead of servers introduces real-world issues: irregular boots, sleep mode disruptions, power cuts, and unstable local network connectivity. To combat this, I implemented four systems to support my local execution.
Background Service Configuration (systemd Daemon)
To run the local proxy tunnel and secret rotation daemon automatically in the background on local Linux systems, I configured a systemd user service (~/.config/systemd/user/scraper-tunnel.service):
[Unit]
Description=Residential Proxy Tunnel for Property Scraper
After=network.target
[Service]
ExecStart=/home/path/to/property-scraper/.venv/bin/python /home/path/to/property-scraper/start_tunnel.py
WorkingDirectory=/home/path/to/property-scraper
Restart=always
RestartSec=10
Environment=PATH=/usr/local/bin:/usr/bin:/bin PYTHONUNBUFFERED=1
[Install]
WantedBy=default.target[!TIP] The environment directive
PYTHONUNBUFFERED=1is critical. When standard output is piped to non-TTY system interfaces (likesystemd-journald), Python block-buffers logs by default. This causes systemd logs to appear frozen. Disabling buffering ensures real-time logging insidejournalctl.
Pre-flight DNS Verification
In order to safely handle situations where the computer boots offline or loses Wi-Fi, the scraper runs a quick DNS resolution check before establishing database or parser sessions:
import socket
from urllib.parse import urlparse
def check_database_dns(db_url: str) -> bool:
"""Resolve database hostname via DNS to verify local network connectivity."""
try:
parsed = urlparse(db_url)
hostname = parsed.hostname
if hostname:
socket.gethostbyname(hostname)
return True
except socket.gaierror:
log.warning("[dns_preflight] Database host %s is unreachable. PC is offline.", hostname)
return FalseIf the pre-flight DNS check fails, the application exits cleanly with a warning log. This prevents database connection exceptions (psycopg2.OperationalError) from throwing loud error traces (which I really detest) or generating false-positive cron failure alerts.
Script-Level Timing Guards
When running locally, execution is triggered frequently (e.g., hourly via cron) so that the script runs as soon as the system boots. However, triggering checks hourly without restriction causes redundant checks and network waste.
To resolve this, the orchestrator queries the database for the last successful health check run:
# Check elapsed hours in orchestrator.py
last_run = db.fetch_last_successful_run(source="health_check")
if last_run:
elapsed_hours = (datetime.now(timezone.utc) - last_run["created_at"]).total_seconds() / 3600.0
if elapsed_hours < 22:
log.info("[timing_guard] Last run was %.1f hours ago. Exiting early.", elapsed_hours)
sys.exit(0)This timing guard restricts checks to once per calendar day while allowing hourly boot triggers to run. The guard can be bypassed manually while going on a health check run by appending --force or --all via the CLI.
Micro-Batched Health Check Resumption
In the initial health check design, candidates were fetched concurrently in memory and committed to PostgreSQL at the end of the run (Phase 2). If the computer lost power or entered sleep mode during Phase 1, all scraped results were lost.
To resolve this, I restructured health_checker.py to use transactional micro-batching:
- The 1,000-listing check queue is divided into slices of
HEALTH_CHECK_BATCH_SIZE = 50. - For each batch, the script executes asynchronous requests concurrently.
- Once a batch is completed, the script commits the updates to Supabase immediately.
- If interrupted by a shutdown or network loss, committed batches remain saved. On reboot, the selection query excludes the completed listings because their
next_health_check_athas been updated, allowing the run to resume exactly where it was interrupted.
# Micro-batched async execution block in health_checker.py
candidates = db.fetch_health_check_candidates()
batch_size = 50
for i in range(0, len(candidates), batch_size):
batch = candidates[i:i + batch_size]
# Asynchronously fetch this batch concurrently
tasks = []
for record in batch:
tasks.append(check_single_url(session, record["url"], record["external_id"]))
results = await asyncio.gather(*tasks)
# Commit the batch results to PostgreSQL immediately
db.commit_health_check_batch(batch, results)
log.info("[health_checker] Committed batch %d of %d", i // batch_size + 1, (len(candidates) + batch_size - 1) // batch_size)Self-Healing Network Safeguards
Due to the messy and erratic nature of scraping locally within the Nigerian context, different safeguards had to be implemented on the network level in order to mitigate several of such bottlenecks.
Transient Outage Filtering (Bottleneck 3)
If a portal experiences temporary downtime or blocks the scraper during a discovery run, the raw list of ingested items would drop to 0 for that portal. Under the naive model, all active listings of that portal would be flagged as absent, incrementing their missed_run_count and routing them to the health checker queue all at once. This is wrong.
- The Safeguard: To combat this, the orchestrator tracks which portals successfully returned at least one listing. In doing this, I effectively restricted
missed_run_countupdates only to listings belonging to successfully scraped portals:missing = set(active_listings.keys()) - seen_this_run if scraped_sources is not None: missing = {k for k in missing if k[0] in scraped_sources}
Soft-404 & Redirect Trace Verification (Bottleneck 4)
When a property is deleted, some portals do not return a standard 404 Not Found response. Instead, they issue a 302/301 Redirect to a generic category listing page (e.g., /properties-for-sale) while returning a 200 OK status. This is a classic “soft-404” trap. If this is treated as a healthy page, the scraper keeps checking it forever, wasting bandwidth.
- The Safeguard: To fight this, analyze the redirected URL. Since legitimate redirects (e.g., adding parameters or changing trails) preserve the listing’s
external_idin the URL, I checked if theexternal_idwas still present in the final redirected URL:if final_url != url and external_id not in final_url: log.debug("[health_checker] redirect stripped external_id: %s → %s", url, final_url) return True, None # Listing confirmed as removed!
This eliminates the need to maintain fragile regex patterns for every portal’s generic page path, offering self-healing protection.
Defending Against Markup Layout Drift (Heuristic Parser Fallbacks)
Real estate portals frequently modify their design or update their frontend frameworks. Once during the past threee month, a major layout update on NigeriaPropertyCentre.com broke the static CSS selectors, causing details to be missed.
To prevent future layout drift from completely blinding our pipeline, I retrofitted our parsers (PropertyProParser and PrivatePropertyParser) with heuristic text-based scanners that run as fallbacks when primary CSS selectors return empty:
- Title Fallbacks: If the primary title class is absent, parsers fall back to extracting the page’s
<h1>element text. - Price Fallbacks: If the primary selector fails, the parser recursively scans page elements (spans, divs, h2, h3) looking for text starting with currency symbols (
₦,$,USD) accompanied by numeric values:for el in soup.find_all(["span", "strong", "h2", "h3", "div"]): txt = el.get_text(strip=True) if txt and (txt.startswith("₦") or txt.startswith("$") or txt.startswith("USD")): if any(c.isdigit() for c in txt): raw_price = txt break - Feature Fallbacks (Bedrooms/Bathrooms): I scanned lists and paragraph segments for keywords matching
"bed","bedroom","bath", or"bathroom"that contain digits, extracting feature counts dynamically. - Address Fallbacks: If the designated address elements are missing, the parser scans the DOM for short text blocks containing regional keywords (e.g.
lagos,abuja,enugu,ibadan,ph) to extract the location.
This shift from static parsing to heuristic-driven scraping ensures that layout changes merely result in minor warning logs rather than catastrophic failures, resulting in more robust parsing.
Cascading Fuzzy Normalization
Scraped addresses are messy. Listings might show "Lekki Phase 1", "Lekki Ph 1", or "Lekki, Lagos". Normalizing these variations into a clean, canonical list of neighbourhoods is essential for aggregate statistical analysis.
I replaced the initial simple string-matching lookup with a 4-tier cascading normalization pipeline in normaliser.py:
[ Raw Scraped Address ]
│
▼
[ Tier 1: Exact Cleaned Match ] ──(Match Found)──► [ Return Canonical Name ]
│ (No Match)
▼
[ Tier 2: Noise-Insensitive Substring Match ] ──(Match Found)──► [ Return Canonical Name ]
│ (No Match)
▼
[ Tier 3: Chunk-Level Fuzzy Match (SequenceMatcher >= 0.85) ] ──(Match Found)──► [ Return Canonical Name ]
│ (No Match)
▼
[ Tier 4: Word-Level Fuzzy Match Fallback (SequenceMatcher >= 0.85) ] ──(Match Found)──► [ Return Canonical Name ]
│ (No Match)
▼
[ Keep Raw Address (Flag as Unnormalised) ]The 4 Normalization Tiers:
- Exact Cleaned Match: Compares space-normalized, punctuation-stripped versions of raw address tokens.
- Noise-Insensitive Substring Match: Strips out noise-words (like
"estate","phase","district","road","axis","bus stop") from both raw text and canonical candidates before checking for substrings:def _clean_noise(text: str) -> str: cleaned = _clean_for_match(text) noise_words = ["estate", "phase", "road", "axis", "bus stop", "district", "close", "court"] pattern = r'\b(' + '|'.join(noise_words) + r')\b' return " ".join(re.sub(pattern, ' ', cleaned).split()) - Chunk-Level Noise-Free Fuzzy Match: Splits raw addresses by delimiters (commas and slashes) and compares the filtered chunks with canonical names using
difflib.SequenceMatcher(matching at a strict threshold of $\ge 0.85$). - Word-Level Fuzzy Match Fallback: Splits the cleaned address into tokens (ignoring words shorter than 4 characters) and compares individual words. This captures common typing variations and phonetic typos (e.g., Gwarimpa $\leftrightarrow$ Gwarinpa, Mabuchi $\leftrightarrow$ Mabushi).
By using this cascading model, I expanded neighbourhood matching accuracy significantly, mapping the vast majority of scraped records to our canonical dataset.
Point-in-Time History Reconstruction & Snapshot Rebuilding
With normalized neighbourhood data in place, the next step was to aggregate the weekly market metrics: median prices, price percentiles (p25, p75, p90), active inventory volumes, and average Days on Market (DOM).
Historical reports, however, present a data consistency problem: listings are dynamic. Over its lifecycle, a property’s price changes and its status transitions to REMOVED. Aggregating metrics for Week 12 using current database rows would yield incorrect results because the listing’s price in the database reflects its current status, not its history.
The Point-in-Time Reconstruction Solution
To combat this, I implemented a standalone analytics engine rebuild_snapshots.py that reconstructs the database state week-by-week in the following steps:
- Define Timeline: The script scans active listings to find the oldest
first_seen_atdate, then segments time into completed calendar weeks (Monday to Sunday). - Historical Price Rewinding: For each historical week, the script loads active listings. To find the price of a listing during that week, it loads the property’s events from
listing_history. If aPRICE_CHANGEevent occurs after the end of that week, the script walks backward and rewinds the listing’s price to the event’sold_value:# Reconstructing price for a specific historical week price = item["price_kobo"] history = history_by_listing.get(lid, []) for ev in history: if ev["event_date"] > w_end: if ev["old_value"] is not None: price = ev["old_value"] # Rewind price to pre-change value - Days on Market (DOM) Calculations: Calculated using the time elapsed between
first_seen_atand the week’s end (or the listing’s removal date, if it was deleted during that week). - Unique Snapshot Keying: To prevent data duplication during backfills, the primary key of each weekly snapshot is generated as an MD5 hash of
(neighbourhood + snapshot_week_str). The data is then written in transactional batches usingON CONFLICT (id) DO NOTHING.
Through this retrospective point-in-time calculation, I built a historical snapshot database (market.neighbourhood_snapshots) that serves as a foundation for charting local market dynamics. This piece of logic is more comprehensively executed within property-intel.
Next Steps: The Property Intelligence Platform (property-intel)
Data ingestion and normalization are only half the battle. To extract value from this scraped real estate database, I built Property Intel (available at github.com/islajr/property-intel)—a real-time data intelligence and analytics platform.
Property Intel sits directly on top of the optimized PostgreSQL/PostGIS database layer and is built as a decoupled monorepo:
┌────────────────────────────────────────┐
│ React / Vite Client Dashboard │
│ - Zustand State, Recharts Trends │
│ - Leaflet Georeferenced Mapping │
└───────────────────┬────────────────────┘
│ (HTTPS REST API / JWT)
▼
┌────────────────────────────────────────┐
│ Spring Boot Core API Service │
│ - Java 21 & Spring Boot 4.0.5 │
│ - JWT RS256 Stateless Auth │
│ - Redis Cache & Rate Limiting │
└───────────────────┬────────────────────┘
│ (SQL Queries / ST_DWithin)
▼
┌────────────────────────────────────────┐
│ PostgreSQL + PostGIS │
│ - Spatially indexed coordinate data │
│ - Flyway Schema Migrations │
└────────────────────────────────────────┘Core Architecture Features:
- Java 21 & Spring Boot API: Primary backend engine utilizing constructor dependency injection, record DTO configurations, and strict schema structures representing all prices as 64-bit integer values in kobo (cents/lowest denomination) to prevent floating-point inaccuracy.
- React 18 & Vite Frontend: A client dashboard designed to plot aggregate real estate trends (median pricing, percentile distributions, Days on Market) using Recharts, and visual maps indicating listing density using georeferenced leaflet markers.
- Redis Caching & Distributed Rate Limiting: Implements Redis caching for heavy spatial endpoints (6-hour SpEL-keyed caches) and Bucket4j rate limiters to protect the platform.
- PostGIS & Spatial Lookups: The database is spatially extended with PostGIS. This allows geocoded listings to support radial neighbourhood searches (using the
ST_DWithinHaversine formula) to locate properties within a given meter radius. - Isolated Testing (Testcontainers): Backend testing uses Testcontainers to dynamically spin up local Docker database/Redis instances. This guarantees that Flyway migrations and domain rules are validated against clean instances on pushes to the remote repository.
By serving as a structured REST layer on top of our resilient python crawler infrastructure, Property Intel translates raw listing lines into actionable property valuations.
12. Summary of Architectural Takeaways
Looking back at the progression of the scraper, here are the key design lessons learned within the context of this project:
- Evaluate Queries at Write-Time: When scaling database tables, avoid conditional checks in your
WHEREclauses. Perform calculations at write-time and store the outcome directly in an indexed column (next_health_check_at). - Prioritize Queues Intelligently: Standard sorting mechanisms (
ASC/DESCon a single column) lead to queue starvation. Use weighted metrics (like our Overdue Ratio) to balance processing across different categories. - Isolate Verification Pathways: Never mark a listing as removed simply because it’s missing from search result pages. Perform targeted URL checks, and build smart soft-404 detection that looks for listing identifiers in redirected URLs.
- Build Defensive Heuristics: Don’t rely solely on static CSS selectors for web parsing. Implement text-based fallbacks to protect the pipeline against layout drift.
- Reconstruct States Historically: Utilize transaction/event tables (
listing_history) to rewind entity states dynamically for point-in-time weekly aggregations.