API-First Wine Valuation Platform

1.1 Overview

Build a cloud‑hosted, API‑first Wine Valuation Platform that:
Ingests auction & retail raw data into a Data Lake (GCS).
Processes & normalizes into a Processed Data Layer (Cloud SQL).
Maintains rich metadata & aggregates (mean, std dev, min/max, last value, clearance rate) over an admin‑configurable window.
Exposes REST APIs for CRUD, valuations (single & batch), reporting, scheduling, and integrations (e.g., NetSuite, AuctionWorx).
Provides a React SPA admin and customer UI (product management, job scheduling, reporting, collection management, valuation requests).
Authenticates users & services via Firebase Auth (JWT).
Deploys via GitHub Actions → Docker → Google Cloud Run with IaC via Terraform.

The app will need to handle large datasets, integrate APIs for seamless data exchange, and provide distinct access levels for both admin and consumer users. Web-based valuation tool hosted on GCP (but interoperable with other cloud providers). The system must be built as microservices and allow for direct access to individual services from external systems (with full auth for security). Each service must be independently callable by external systems (via open API standards) and secured via robust auth. Expected integrations include Oracle NetSuite, Shopify, AuctionWorx, internal tools via API. API-first design: All major features must be exposed via REST (or gRPC if justified) Cloud SQL (PostgreSQL) will be the main DB, hosted on GCP Heavy data processing involved (cleansing, deduping, rule-based matching, trend analysis, etc.). Rule-based valuation logic to provide a customisable valuation algorithm that can be adapted. Should allow for weighting of rules (eg, data from source A is more relevant than sources B or C).
For performance, we expect pre-computed aggregates that must be kept updated on a scheduled and triggered basis (ie, nightly updates + subset updates triggered by ingesting new data). With the ability to handle custom and on-demand queries as well to ensure flexibility. Consumer users should have the ability to create, update, and manage their own lists of wines (their private collections) which can be valued at any time using the system. This data should also be used for insights on product demand, consumer preferences (suggested products for consumer A will be influenced when browsing our website based on their buying history AND their current private collection in this system), etc. The system should use integrated systems and cleaned raw data to build and maintain a substantial and robust source of truth master database of products which can be called by external systems to extract product data (tasting notes, pricing historics, demand, product metadata, etc)

1.1 Project Expectations
This project revolves around a few core functions of the system that must be fully understood to properly implement. Below is a high level explanation of these core functions.

First Core Function: Data ingestion and processing. Raw data is ingested from a wide range of sources with the majority of raw data coming from web scraping. However, the system must be able to ingest data from manual CSV uploads as well as ingestion via API. Since the raw data is sourced from many different sources, the raw data must be cleaned, standardized, and importantly deduped as part of the processing phase. All data ingestion must trigger data processing of the newly ingested data. Deduping must be completed in such a way that the product database has a master-child approach with each product object having alternate child names/aliases. These aliases must be used for data matching to ensure fast data matching. Any metadata and aggregations should be updated following data ingestion and processing.

Second Core Function: Enriched Product Database. This system will involve vast sums of data and this must all be stored intelligently to create a fully enriched and trustworthy database of product data. This product database shall form the basis for valuations being processed by this system, but the product database shall also provide support for all of our connected systems as a source of truth for product information. Further information on this use case can be found in section 4.4 External System via API.

Third Core Function: Product List Valuation. The main output of this system is a final table of historical pricing metadata and current sell value estimates for an inputted list of products. This is the main user interaction with this system, in which an admin can request via UI a valuation to be processed for a list of products (selectable via UI/autofill, CSV upload, and Collection selection). There are two main types of valuations: Internal and External. Internal is only accessible for admin and shows sensitive information such as expected margin, etc. External is for customers and shows a limited subset of information. This valuation process can be initiated via UI or API and can be run on one or many products. An example of the output can be found in section 8.3 Example API Requests & Responses. The outputted valuation should be exportable and shareable from the UI (csv download, pdf download, email).