A – Merchant Harvest & Verification -- 2
Budget: €499 – €501 EUR
A – Merchant Harvest & Verification
Objective: This module creates the foundation for the entire process by building and maintaining a comprehensive, yet verified, database of all relevant jewelry merchants. It collects merchants, supports a workflow for manual review by VAs, and ensures a clean data foundation.
Tasks & Deliverables:
Quick-Check & Ingest Client: At the beginning, you will conduct a thorough test of the AliExpress API. Subsequently, you will develop a robust Python script that collects merchant IDs via a broad product search, intelligently handling paging, rate limits, and timeouts.
DB Schema & Logging: You will design and create the tables sellers (with fields like seller_id, shop_url, approval_status, first_seen_at, last_seen_at) and a detailed job_runs table to log every script execution with start/end times, duration, counts (found/new/skipped/errors), and job type.
Logic & Delta Runs: The core logic must be idempotent ("upsert" command) to avoid duplicates. New merchants will automatically receive the approval_status "PENDING". The possible status values are fixed to PENDING, WHITELIST, BLACKLIST. Module B will later process only merchants with WHITELIST status. The system must support daily "Delta Runs".
VA Interface for Merchant Verification: To enable a controlled and verifiable review process, you will create two CLI commands:
review:export-pending: This command exports all merchants with the status "PENDING" into a CSV file. To avoid compatibility issues, the CSV format will be clearly defined as UTF-8 encoded, with a comma as the delimiter, and will contain the columns seller_id, shop_url, approval_status, and note.
review:import-results: This command reads a CSV file edited by the VA (with the same format) and updates the approval_status in the database accordingly.
Handover: The handover includes the clean code in the Git repo, a README.md file with detailed instructions for setup and execution of the explicitly named CLI commands (harvest:init, harvest:delta, harvest:status, review:export-pending, review:import-results), and a "dashboard-light" in the form of a small text file with 2-3 simple SQL queries.
Acceptance Criteria: The script must be idempotent and stable, timestamps must be maintained correctly, and the harvest:status command must display correct counts from the job_runs table. The CSV export/import workflow for merchant verification must function reliably.
Technical Guardrails (Principles for the Entire Project)
Architecture: The entire codebase must be designed to be modular from the ground up. This means that logically related functions will be structured in separate directories (e.g., /harvester, /processor, /common). Every main process step, such as collecting merchants or processing products, must be executable via its own clearly named command in the command-line interface (CLI). Additionally, all data-intensive commands must support the safety switches --limit X (processes only the first X entries) and --dry-run (simulates the run without writing data).
Runtime Environment: The target runtime environment for all scripts is a Linux system with Python 3.10 or newer. The scripts must be designed to run "headless," i.e., without a graphical user interface, to allow for future automation via a cron job. The README.md file must include clear instructions for setting up the development environment using python -m venv and pip install -r requirements.txt.
Database: As the central data system, we will use a professional cloud database, preferably PostgreSQL (e.g., via a provider like Supabase). It is a crucial requirement that the database stores both the complete, unaltered raw data from the API (for example, in a raw_json column) and the clean, "normalized" data that we process and prepare. All timestamps in the database (e.g., in job_runs, first_seen_at) must be stored consistently in UTC to ensure server-independent consistency.
No Binary Data in DB: To keep the database lean and performant, it is a firm requirement that no image files (binary data) are stored directly in the PostgreSQL database. The database will exclusively store metadata about the images (such as URLs, hashes, or paths to embedding files). The actual image files will be stored in a dedicated cloud storage solution (S3).
Keys: To uniquely identify products across all system boundaries, we will consistently use the combination of seller_id and item_id as a composite primary key.
Configuration: No critical settings such as API keys, price limits, or keyword lists may be hardcoded in the source code. The search terms for the merchant harvest must be located in an external file like keywords.csv. All other parameters must be managed in an external configuration file (preferably in .env format). As part of the handover, a sample keywords.csv and an .env.example file with all necessary variables are expected.
Region & Currency: To obtain correct prices and delivery times, all requests to the AliExpress API or any scraper sessions must be configured to set the shipping destination to Germany (DE). All prices must be consistently converted to the EUR currency for comparisons and calculations.
Code Ownership & Version Control: All work will be conducted in a private GitHub repository provided by me, with traceable commits. All source code and created artifacts will become my sole property upon full payment for the respective milestone.
Collaboration & Scope Management
Scope Definition: The scope of this contract (Version 1.0) is strictly limited to the tasks and deliverables described in the four modules. Any additional ideas, requests, or new requirements that arise during the project will be collected in a separate backlog and can be commissioned as standalone follow-up projects after this contract is completed.
Communication Protocol: You are expected to proactively clarify any uncertainties about the requirements before implementation. Any changes or extensions beyond the scope defined here require my prior, explicit, written approval to be valid.
Objective: This module creates the foundation for the entire process by building and maintaining a comprehensive, yet verified, database of all relevant jewelry merchants. It collects merchants, supports a workflow for manual review by VAs, and ensures a clean data foundation.
Tasks & Deliverables:
Quick-Check & Ingest Client: At the beginning, you will conduct a thorough test of the AliExpress API. Subsequently, you will develop a robust Python script that collects merchant IDs via a broad product search, intelligently handling paging, rate limits, and timeouts.
DB Schema & Logging: You will design and create the tables sellers (with fields like seller_id, shop_url, approval_status, first_seen_at, last_seen_at) and a detailed job_runs table to log every script execution with start/end times, duration, counts (found/new/skipped/errors), and job type.
Logic & Delta Runs: The core logic must be idempotent ("upsert" command) to avoid duplicates. New merchants will automatically receive the approval_status "PENDING". The possible status values are fixed to PENDING, WHITELIST, BLACKLIST. Module B will later process only merchants with WHITELIST status. The system must support daily "Delta Runs".
VA Interface for Merchant Verification: To enable a controlled and verifiable review process, you will create two CLI commands:
review:export-pending: This command exports all merchants with the status "PENDING" into a CSV file. To avoid compatibility issues, the CSV format will be clearly defined as UTF-8 encoded, with a comma as the delimiter, and will contain the columns seller_id, shop_url, approval_status, and note.
review:import-results: This command reads a CSV file edited by the VA (with the same format) and updates the approval_status in the database accordingly.
Handover: The handover includes the clean code in the Git repo, a README.md file with detailed instructions for setup and execution of the explicitly named CLI commands (harvest:init, harvest:delta, harvest:status, review:export-pending, review:import-results), and a "dashboard-light" in the form of a small text file with 2-3 simple SQL queries.
Acceptance Criteria: The script must be idempotent and stable, timestamps must be maintained correctly, and the harvest:status command must display correct counts from the job_runs table. The CSV export/import workflow for merchant verification must function reliably.
Technical Guardrails (Principles for the Entire Project)
Architecture: The entire codebase must be designed to be modular from the ground up. This means that logically related functions will be structured in separate directories (e.g., /harvester, /processor, /common). Every main process step, such as collecting merchants or processing products, must be executable via its own clearly named command in the command-line interface (CLI). Additionally, all data-intensive commands must support the safety switches --limit X (processes only the first X entries) and --dry-run (simulates the run without writing data).
Runtime Environment: The target runtime environment for all scripts is a Linux system with Python 3.10 or newer. The scripts must be designed to run "headless," i.e., without a graphical user interface, to allow for future automation via a cron job. The README.md file must include clear instructions for setting up the development environment using python -m venv and pip install -r requirements.txt.
Database: As the central data system, we will use a professional cloud database, preferably PostgreSQL (e.g., via a provider like Supabase). It is a crucial requirement that the database stores both the complete, unaltered raw data from the API (for example, in a raw_json column) and the clean, "normalized" data that we process and prepare. All timestamps in the database (e.g., in job_runs, first_seen_at) must be stored consistently in UTC to ensure server-independent consistency.
No Binary Data in DB: To keep the database lean and performant, it is a firm requirement that no image files (binary data) are stored directly in the PostgreSQL database. The database will exclusively store metadata about the images (such as URLs, hashes, or paths to embedding files). The actual image files will be stored in a dedicated cloud storage solution (S3).
Keys: To uniquely identify products across all system boundaries, we will consistently use the combination of seller_id and item_id as a composite primary key.
Configuration: No critical settings such as API keys, price limits, or keyword lists may be hardcoded in the source code. The search terms for the merchant harvest must be located in an external file like keywords.csv. All other parameters must be managed in an external configuration file (preferably in .env format). As part of the handover, a sample keywords.csv and an .env.example file with all necessary variables are expected.
Region & Currency: To obtain correct prices and delivery times, all requests to the AliExpress API or any scraper sessions must be configured to set the shipping destination to Germany (DE). All prices must be consistently converted to the EUR currency for comparisons and calculations.
Code Ownership & Version Control: All work will be conducted in a private GitHub repository provided by me, with traceable commits. All source code and created artifacts will become my sole property upon full payment for the respective milestone.
Collaboration & Scope Management
Scope Definition: The scope of this contract (Version 1.0) is strictly limited to the tasks and deliverables described in the four modules. Any additional ideas, requests, or new requirements that arise during the project will be collected in a separate backlog and can be commissioned as standalone follow-up projects after this contract is completed.
Communication Protocol: You are expected to proactively clarify any uncertainties about the requirements before implementation. Any changes or extensions beyond the scope defined here require my prior, explicit, written approval to be valid.