Multi-Source Real Estate Data Pipeline (Venezuela) — built on Apify → AWS PostgreSQL

# Multi-Source Real Estate Data Pipeline (Venezuela) — Apify → AWS PostgreSQL

## Overview
We are a real estate brokerage with three offices in Venezuela (RM I — Táchira; RM II — Greater Caracas; RM III — Anzoátegui). We need a scheduled pipeline that scrapes **8 Venezuelan property portals** for these areas, normalizes everything into one schema, and stores it in **our AWS PostgreSQL** database. It runs **once per week**.

Purpose: (1) property valuations & market intelligence — pricing/comparables and market movement over time (urgent priority); (2) an internal search tool showing which agent published each listing. We want all listing details + the agency + the publishing agent. **No photos** — just the URL and structured data.

## Platform & approach (please read)
The whole system runs on **Apify** — one account (ours), one scheduler, one dataset, one integration to our DB. Build **one custom Apify Actor** (TypeScript + Crawlee preferred; Python + Scrapy OK) with a **router that sends each domain to its own adapter**. The 8 sites are built differently, so there is one adapter per site, all feeding one normalized schema. Apify unifies the platform (proxies, headless browser, scheduling); it does not remove per-site logic.

Flow: Apify Actor (weekly) → normalize → Apify dataset → webhook → AWS Lambda → PostgreSQL (insert/update + change detection + price history). The only piece outside Apify is a small Lambda our developer deploys.

**Method hierarchy per site (cheapest/most stable first):**
1. **Internal API first** — find the site's XHR/JSON endpoint (expected for TuHome24 SPA, Century21 AJAX, and the Wasi-based MLS Caracas).
2. **Plain HTML next** — simple HTTP + parser for server-rendered sites (Rivinotinto, RE/MAX). No browser, no expensive proxy.
3. **Headless browser last** — only where there's hard anti-bot (MercadoLibre, PERAIG): Playwright + Apify residential proxy.

Do NOT use a headless browser on every site. Two-stage pattern: list pages → URLs + card data; detail pages → agent, agency, full attributes.

## The 8 portals (recon already done)
- **MercadoLibre VE** — server-rendered + DataDome anti-bot. - Hard but doable. **START HERE.** Largest source (~63.7k in Greater Caracas). Public API blocked (403). Needs residential proxy.
- **Rivinotinto** — classic PHP, data already in HTML, simple pagination (`?pagina=N`), no anti-bot. - Trivial.
- **MLS Caracas** — built on Wasi.co (CRM with its own API — likely a backdoor). Large inventory. - Easy-Medium.
- **RE/MAX VE** — server-rendered, data in HTML (price, unique RE/MAX code, location, beds/baths/parking, m²). Agent exposed. **Search is map-based** (`ubi=` + lat/long). No hard anti-bot. --Easy-Medium.
- **Rent-a-House** — large national network. robots.txt blocked our tool; recon it first in its phase. Medium.
- **Century21 VE** — WordPress + Elementor, AJAX-loaded (HTML is a shell). Agent exposed. Medium.
- **TuHome24** — single-page app (pure JS); HTML empty. Intercept its API or use a browser. Medium-Hard.
- **PERAIG** — active bot detection; blocks direct requests. Medium.

## Data to capture
**Per property (valuation fields are priority):** listing URL; source portal; office (RM I/II/III) + geographic state; operation (sale/rent); property type; price + currency (USD/VES); price per m²; built & land area (m²); bedrooms; bathrooms; parking; city/municipality/zone; full address; lat/long if available; title; description; external code (e.g. RE/MAX code) for dedup; status (active/paused/closed); date published/updated; first/last seen; and `raw_json` (full payload, JSONB — lose nothing).
**Per agency:** name, contact (if available).
**Per agent (best-effort, secondary):** name, phone, email, nickname. Availability varies — franchises and CRM sites usually expose it; MercadoLibre hides the phone. Not a blocker for valuations.

## Normalization (important)
Tag every property with **office (`oficina_rm`: RM I/II/III)** AND **geographic state**. These differ for RM II: Greater Caracas spans two states — Libertador = Distrito Capital; Baruta/Chacao/El Hatillo/Sucre = Miranda. Derive state from the municipality. Each weekly run must detect new/changed/removed listings; price changes go to a history table.

**Duplicates:** the same property may appear on several portals — do NOT de-duplicate aggressively. Store each appearance tagged by source; capture the external code where present to match later. Key by (portal, listing URL).

## Suggested schema (PostgreSQL)
`portales`, `propiedades` (with portal_id, external_id/code, url UNIQUE per portal, oficina_rm, estado, municipality, zone, lat/lng, operation, type, price, currency, price_m2, areas, beds, baths, parking, title, description, agency_id, agent_id, status, dates, first/last_seen, raw_json JSONB), `inmobiliarias`, `agentes`, `precio_historico`, `scrape_runs`. Final DDL/indexes/migrations are part of your deliverable.

## Coverage by office
- **RM I — Táchira:** MercadoLibre, RE/MAX, Century21, Rent-a-House.
- **RM II — Greater Caracas (DC + East Miranda, 5 municipalities):** all 8 portals. The MercadoLibre "Distrito Capital" search already covers all 5 municipalities — verified.
- **RM III — Anzoátegui:** MercadoLibre, RE/MAX, Rivinotinto, Rent-a-House, Century21, TuHome24.

(Full target URLs and state IDs will be shared with the selected freelancer.)

## Phases (fixed price — each phase is a milestone)
1. **Base pipeline + MercadoLibre (START HERE).** Actor skeleton, PostgreSQL schema, normalization (oficina_rm/estado), insert/update + price history, Apify→Lambda→PostgreSQL integration, and the MercadoLibre adapter with residential proxy. Valuation fields are priority #1; agent is best-effort. Proof of full extraction of the ~63.7k Greater Caracas set. **Go/no-go gate.**
2. **High-value comparables:** RE/MAX, MLS Caracas (Wasi), Rivinotinto, Rent-a-House.
3. **Remaining:** Century21, TuHome24, PERAIG.
4. **Monitoring + handover:** Slack failure alerts (e.g. adapter returns 0 or drops >X%), docs (setup, runbook, schema), handover session.

## Deliverables
Git repo (one adapter per portal); the Apify Actor (deployable to our account); PostgreSQL schema + migrations; Apify→Lambda→PostgreSQL integration; weekly schedule; Slack alerts + CloudWatch logging; documentation; handover session.

## Skills
Node.js/TypeScript + Crawlee (or Python + Scrapy) · Apify (Actors, datasets, proxy, scheduler) · Playwright · reverse-engineering internal XHR/JSON APIs · anti-bot + residential proxies · PostgreSQL · AWS (Lambda, RDS, EventBridge) · data normalization.

## Deployment & security
You will NOT receive our production AWS credentials. Final AWS deployment is done by our in-house AWS developer: you hand over code + Infrastructure-as-Code (Terraform or AWS SAM) + instructions, he runs the deploy. The scraper lives in our Apify account, which you can be granted access to.

## Cost control (mandatory)
Recurring cost is dominated by MercadoLibre residential-proxy traffic, so: block images/CSS/fonts (no photos needed); full initial crawl then **incremental** detail fetches (only new/changed listings re-fetched in full; existing ones price-checked from list pages); residential proxy ONLY for MercadoLibre/PERAIG, plain HTTP/datacenter for the rest. A poor design costs 5–10× more for the same data.

## Budget
Fixed price, **billed per phase** (Phase 1 first, as a go/no-go gate) — please quote each phase separately. **Do not include Apify/proxy/AWS costs in your price** (those run on our accounts). Also tell us your rate for ongoing maintenance after delivery (sites change their HTML over time).

## Include in your bid (so we can spot real scrapers)
In 3 short lines, tell us how you'd approach: (1) **MercadoLibre** anti-bot reliably at ~63k listings; (2) **the Wasi-based MLS Caracas** — would you use its underlying API, and how would you find it; (3) **RE/MAX** map-based search (lat/long) — pagination and full coverage. Also: your stack, your Apify experience, and one similar pipeline you've built.

**Note:** follow the API → HTML → headless hierarchy; keep each adapter isolated so one broken site doesn't stop the others; no photos.