Scrapy to PostgreSQL Integration + Metabase Setup
Budget: $30 – $250 USD
Integrate Multiple Scrapy Projects with PostgreSQL on Hetzner + Metabase Setup
Description:
We are running several existing Scrapy-based web scraping projects that collect job offer data (title, company, location, etc.). We now want to centralize and store all scraped data in a PostgreSQL database hosted on a dedicated Hetzner server, and make it accessible through Metabase for reporting and visualization.
Scope of Work:
1. PostgreSQL Setup (Hetzner)
- Install and configure PostgreSQL on a Hetzner Ubuntu server
- Enable remote access (securely) and configure for performance
- Set up a database (`jobs_db`) and create a `job_offers` table with appropriate fields and indexing
- Configure RAID 1 for 2 × NVMe SSDs (using `mdadm` or similar)
2. Data Import Script
- Write a Python script that can:
- Read exported data files from Scrapy (`.json`, `.csv`, or `.jl`)
- Insert into PostgreSQL efficiently (with deduplication by URL or hash)
- Be reusable for multiple Scrapy projects
- Optional: batch import with CLI arguments or cron integration
3. Metabase Connection
- Connect the PostgreSQL database to Metabase (hosted or cloud)
- Provide access or a test dashboard for filtering/searching job data
- Example dashboards: jobs per day, company heatmaps, title trends
4. Basic Security
- Configure UFW firewall on Hetzner to only allow PostgreSQL access from Metabase
- Secure PostgreSQL with strong credentials
- Disable root SSH login and ensure basic server hardening
Deliverables:
- Working PostgreSQL setup with `job_offers` table
- Import script (Python) with documentation
- Metabase integration with at least one working dashboard
- Setup documentation or deployment steps (text or Markdown)
Requirements:
- Strong experience with Scrapy and PostgreSQL
- Comfortable with Linux server setup (Ubuntu on Hetzner)
- Familiarity with Metabase or similar BI tools
- Ability to write clean, well-documented Python
Please answer these questions:
1. Have you worked with Scrapy before, and can you show examples of exporting or handling scraped data in JSON/CSV?
2. How would you design a PostgreSQL schema for job offer data to handle deduplication and scaling to millions of rows?
3. What’s your experience setting up PostgreSQL on a remote Linux server (e.g., Hetzner or similar cloud VPS)?
4. Can you configure RAID 1 on two NVMe drives using mdadm or another tool? Have you done this before in production?
5. How would you securely expose PostgreSQL to allow Metabase to connect but keep everything else locked down?
6. Have you worked with Metabase before? Can you describe how you would connect it to a PostgreSQL database and create a basic dashboard?
7. What steps would you take to optimize daily imports of 10,000 rows to prevent performance or data duplication issues?
8. Can you write a reusable Python script or CLI tool to import multiple Scrapy export files (JSON/CSV) into PostgreSQL with logging and error handling?
Description:
We are running several existing Scrapy-based web scraping projects that collect job offer data (title, company, location, etc.). We now want to centralize and store all scraped data in a PostgreSQL database hosted on a dedicated Hetzner server, and make it accessible through Metabase for reporting and visualization.
Scope of Work:
1. PostgreSQL Setup (Hetzner)
- Install and configure PostgreSQL on a Hetzner Ubuntu server
- Enable remote access (securely) and configure for performance
- Set up a database (`jobs_db`) and create a `job_offers` table with appropriate fields and indexing
- Configure RAID 1 for 2 × NVMe SSDs (using `mdadm` or similar)
2. Data Import Script
- Write a Python script that can:
- Read exported data files from Scrapy (`.json`, `.csv`, or `.jl`)
- Insert into PostgreSQL efficiently (with deduplication by URL or hash)
- Be reusable for multiple Scrapy projects
- Optional: batch import with CLI arguments or cron integration
3. Metabase Connection
- Connect the PostgreSQL database to Metabase (hosted or cloud)
- Provide access or a test dashboard for filtering/searching job data
- Example dashboards: jobs per day, company heatmaps, title trends
4. Basic Security
- Configure UFW firewall on Hetzner to only allow PostgreSQL access from Metabase
- Secure PostgreSQL with strong credentials
- Disable root SSH login and ensure basic server hardening
Deliverables:
- Working PostgreSQL setup with `job_offers` table
- Import script (Python) with documentation
- Metabase integration with at least one working dashboard
- Setup documentation or deployment steps (text or Markdown)
Requirements:
- Strong experience with Scrapy and PostgreSQL
- Comfortable with Linux server setup (Ubuntu on Hetzner)
- Familiarity with Metabase or similar BI tools
- Ability to write clean, well-documented Python
Please answer these questions:
1. Have you worked with Scrapy before, and can you show examples of exporting or handling scraped data in JSON/CSV?
2. How would you design a PostgreSQL schema for job offer data to handle deduplication and scaling to millions of rows?
3. What’s your experience setting up PostgreSQL on a remote Linux server (e.g., Hetzner or similar cloud VPS)?
4. Can you configure RAID 1 on two NVMe drives using mdadm or another tool? Have you done this before in production?
5. How would you securely expose PostgreSQL to allow Metabase to connect but keep everything else locked down?
6. Have you worked with Metabase before? Can you describe how you would connect it to a PostgreSQL database and create a basic dashboard?
7. What steps would you take to optimize daily imports of 10,000 rows to prevent performance or data duplication issues?
8. Can you write a reusable Python script or CLI tool to import multiple Scrapy export files (JSON/CSV) into PostgreSQL with logging and error handling?