OJPH Jobs Discovery Pipeline

Overview

The OJPH Jobs Discovery Pipeline is a serverless job-discovery platform for freelancers hunting for work on OnlineJobs.ph. It collects thousands of new remote-gig listings per week into a Cloudflare D1 database, reconciles them with years of legacy scraped data, and serves a searchable static frontend — all without a server.

Problem

OnlineJobs.ph is a walled-garden job board: no public API, thousands of new remote-gig listings per week, and no discovery feed — just a clunky search page. Earlier Python scripts had accumulated ~34K scraped jobs locally, but a TypeScript rewrite moved new listings into a fresh Cloudflare D1 database. The old and new datasets never met: the live frontend only showed jobs scraped since the rewrite, silently dropping months of legitimate older listings.

The project needed one reconciled dataset, a fast way to browse and search it, and an unattended pipeline that kept it fresh without a server.

Approach

Migrated 33,476 legacy Python-era jobs into the same Cloudflare D1 (SQLite) database that holds new writes, deduplicating by job ID to reach 43,188 unique listings, then replaced the frontend with a new static client app that loads exported JSON chunks and offers search, category filters, sorting, and pagination — old and new jobs side by side.

The pipeline is an hourly GitHub Actions cron: scrape list pages, scrape per-listing detail, categorize, upsert into D1, export to JSON chunks, and auto-commit so the site stays fresh with zero servers.

Architecture

Scraping Stack

TypeScript list + detail scrapers (Cheerio) with a categorizer; the CLI exposes subcommands (pipeline, backfill, chunks) via the citty argument parser.

Storage

Cloudflare D1 (SQLite) reached over the D1 HTTP API from Node on GitHub Actions — no Worker, no binding — writing a 26-column jobs table via multi-row INSERT OR REPLACE upserts. Batches are capped at 3 rows because D1 limits bound parameters to 100 per statement (3 rows × 26 columns = 78).

Legacy Migration

A deduplicating backfill command streams the last full enriched_jobs.json blob into D1 (8 parallel upsert streams), skipping job IDs that already exist, so old and new datasets reconcile instead of colliding.

Extract

The chunks command exports the whole table into 14 JSON files (~10MB each), byte-identical copies in chunks/ and frontend/chunks/.

Frontend

A new static client app (dark, GitHub-style) loads those chunks in the browser and implements client-side search, category filtering, sorting by posted date/title/company, and 20-per-page pagination. Served from Vercel with vercel.json, taking the frontend/ folder as root.

CI

GitHub Actions runs on push plus an hourly cron, skips chunk-commits to avoid ping-pong loops, uses an OAuth token with workflow scope, and auto-commits refreshed chunks back to main.

Results

• 43,188 unique jobs live (33,476 legacy + new listings), spanning May 2026 and September 2026 concurrently in one searchable feed
• Search, category filter, sort, and pagination fully client-side — no server, no per-visitor database reads
• Self-healing pipeline: when Cloudflare's new D1 free-tier write quota was exhausted mid-day, the pipeline was made to detect code 7500, log a warning, and continue exporting chunks instead of aborting — unwritten listings stay 'new' and get imported on the next run after the quota resets at 00:00 UTC
• Hourly cron keeps the site current; chunk files byte-identical across deployments for reproducible verification