Giovanni Zappa

<!DOCTYPE html>

Metziah — Israeli Supermarket Data Platform

Python PostgreSQL + PostGIS FastAPI REST API / OpenAPI HTMX Pydantic psycopg3 pydantic-settings lxml httpx Playwright Docker pytest GitHub Actions

A continuously updated data platform for Israeli supermarket prices, promotions, and stores — from raw government-regulated feeds, through scraped product enrichment (images, ingredients, nutrition), to a queryable PostgreSQL + PostGIS database exposed by a documented REST API and a web interface.

Metziah collects and processes the XML feeds published by Israeli supermarket chains under the 2014 Price Transparency regulations (Reshumot 7442). It supports multiple feed protocols and data sources, normalizes heterogeneous data, reconciles full snapshots with incremental updates, tracks file state and load dependencies, and provides monitoring, inspection, analytics, and daily promotion notifications. Products are then enriched with metadata scraped from retailer websites, and the whole catalog is served through a FastAPI service.

The current pipeline processes 15,000+ feed files per day, covering approximately 2,369 stores, 550,000 unique products, 30.9 million promotion items*, and 33 supermarket chains. The enrichment layer adds ~48,000 scraped products, ~48,000 product images, and 16,000+ products with parsed nutrition facts.

*The promotion count reflects records published by retailers and may be inflated by the way some publishers represent and repeat promotion data.

Demo

GitHub Repository

https://github.com/gzappaa/metziah

What makes it interesting

• Multi-protocol ingestion — supports genuinely different supermarket feed systems including LaibCatalog, BinaProjects, Carrefour, MishnatYosef, PublishedPrices, Wolt, and generic HTML endpoints.
• Full + incremental reconciliation — full snapshots establish state while incremental price and promotion feeds are applied only when their required snapshot dependencies have been loaded.
• Real-world data inconsistencies — handles inconsistent identifiers, duplicate item codes, malformed XML, heterogeneous publishing formats, store naming differences, and chain-specific behavior.
• Product identity across chains — separates shared product identity based on real barcodes from store-scoped internal item codes.
• Product enrichment at scale — scrapes retailer websites (JSON dumps, server-rendered pages, and replayed JSON APIs), parses them to JSONL, and merges them into the database. One retailer is the source of truth; the others only fill empty fields. Missing barcodes can be recovered from GS1 image URLs.
• Nutrition normalization — about 120 Hebrew and English label spellings are mapped to canonical nutrients, units are converted, and each value is tagged with its basis (per 100 g, per 100 ml, per serving, %DV). Nothing is dropped silently, and the raw scrape is kept so rows can be rebuilt.
• REST API and web interface — a versioned, read-only FastAPI service with interactive Swagger docs, Pydantic response models, pagination, and search, plus a server-rendered HTMX site. A parity test keeps the API and the website in sync.
• Stateful file tracking — every feed file is tracked by chain, store, filename, download/load state, and related metadata, allowing the pipeline to determine which files are eligible for processing and enforce full-before-delta ordering.
• PostgreSQL + PostGIS — stores products, prices, promotions, stores, and geographic data in a relational database, with spatial queries used by the notification system. Large tables are partitioned by chain.
• Monitoring and inspection — dedicated tooling detects missing chains or stores, duplicate stores, filename-pattern drift, full-vs-delta inconsistencies, source staleness, scraper coverage, and data-quality problems.
• Real database integration tests — the test suite runs against a separately seeded PostgreSQL/PostGIS database using real psycopg connections rather than mocked database interactions.

Pipeline

Supermarket feed sources
(LaibCatalog / BinaProjects / Carrefour / MishnatYosef / PublishedPrices / Wolt / HTML)
↓
clients/ → protocol-specific feed clients
↓
downloaders/ → full + incremental feed orchestration
↓
gzip'd XML / source data
↓
parsers/xml.py → typed Product / Promo / Store models
↓
utils/update_* → normalization + reconciliation
↓
PostgreSQL + PostGIS
  → products / store_products / prices
  → promotions / promotion_groups / promotion_items
  → stores / file_tracking
  → product_enrichment / product_nutrition
↓
api/ → FastAPI REST API (/api/v1) + HTMX website
analytics + monitoring + inspection
↓
price/promotion change detection + daily promotion notifications

Enrichment pipeline

Retailer websites
↓
scrapers/ → JSON dumps, server-rendered pages, replayed JSON APIs
↓
parsers/ → flat product records (barcode recovery, categories)
↓
data/parsed/*.jsonl → one file per retailer
↓
utils/products/load_* → product_enrichment + normalized product_nutrition
↓
utils/processing/download_img → one image per barcode, served by the API

REST API

The API is a read-only FastAPI service under /api/v1 with interactive Swagger docs at /docs and an OpenAPI schema at /openapi.json. It serves the whole catalog, so a product appears whether or not it has been enriched. List endpoints take limit and offset and return items, total, limit, and offset.

Endpoint	Description
GET /health	Health check
GET /stats	Catalog totals: stores, brands, fresh products
GET /categories/packaged	Packaged-product categories with counts and subcategories
GET /products?category=	Enriched packaged products in a category
GET /products/search?q=	Search enriched products, each with its cheapest price and store count
GET /products/unenriched?q=	Browse or search products that have no enrichment
GET /products/{barcode}	Full detail for any barcode: product, prices per store, promotions, enrichment and nutrition
GET /fresh/categories	Fresh-product categories (produce, butcher, fish, bakery)
GET /fresh/categories/{id}/subcategories	Subcategories of a fresh category
GET /fresh/products?subcategory_id=	Fresh products in a subcategory, cheapest first
GET /fresh/products/detail	A fresh product's price and active promotions in every store
GET /fresh/search?q=	Search fresh (unbarcoded) products by name or item code

The web interface also serves product images at /img/{item_code}, with a placeholder when none exists.

Architecture

• clients/ — one client per genuinely distinct feed protocol.
• downloaders/ — shared full/delta download orchestration, runner, and scheduler.
• parsers/ — converts raw feeds and scraped product pages into typed domain models and flat records.
• scrapers/ — retailer product scrapers, including browser-based and replayed-API page types.
• database/ — repository and database migrations.
• utils/ — loading, normalization, reconciliation, file tracking, nutrition normalization, and image download logic.
• api/ — FastAPI app: routes, service layer, SQL queries, Pydantic schemas, and the HTMX website.
• analytics/ — log parsing, price/promotion change detection, and notifications.
• monitoring/ — source and pipeline health checks.
• inspection/ — ad-hoc data-quality, scraper, and audit reports.

Getting Started

• Clone: git clone https://github.com/gzappaa/metziah
• Install: pip install -r requirements.txt
• Configure .env.dev and .env.test.
• First-time setup: make setup or make setup-test
• Run the scheduler: ENV=dev python -m downloaders.scheduler
• Run the enrichment pipeline: make enrich-prod
• Run the API and website: ENV=prod uvicorn api.main:app --reload, then open http://localhost:8000/docs
• Run tests: make test
• Docker: docker compose --env-file .env.dev up -d (production adds an api service with --env-file .env.prod)

Status

• Phase 1 — done: Core price and promotion pipeline for a single chain.
• Phase 2 — done: Multi-chain ingestion, promotion notifications, monitoring, and Dockerization.
• Phase 3 — done: Product enrichment, canonicalization of product names and cross-chain product data, store-product categories, and conservative data cleaning.
• Phase 4 — done: REST API and web interface.
• Phase 5 — done: Enrichment at scale (~48,000 scraped products and images, 16,000+ parsed nutrition records), barcode fallback scraping, and production Docker deployment with a dedicated API service.

Legal Basis

Feed access is based on Israel's 2014 Price Transparency regulations (Reshumot 7442), which require supermarket chains to publish machine-readable price and promotion data.

hebrew_vocab_hub — API & Database

FastAPI PostgreSQL SQLAlchemy async Pydantic v2 Docker Compose pg_trgm Python pytest

13,000 Hebrew lemmas. 150,000 unique word forms. Every conjugation, every root, every translation — queryable in milliseconds.

A production-ready REST API and web interface built on top of a custom-scraped Hebrew vocabulary dataset. Search words by meaning (in English), by Hebrew form, by root, by part of speech, or by Latin transcription — with fuzzy matching powered by PostgreSQL trigrams. Each result returns the full conjugation table, bilingual example sentences, and a real-world frequency score from Israeli songs, news, and YouTube.

Spin up the entire stack — Postgres, data import, and API — with a single docker compose up.

GitHub Repository

https://github.com/gzappaa/hebrew_vocab_hub_API

What makes it interesting

• 5 search dimensions — meaning, word form, root, part of speech, transcription — each with a deep mode that reaches inside conjugation cells
• Fuzzy transcription search — type katav or catav, get שָלוֹם back. Exact matches surface first, trigram fallback handles typos and transliteration variants
• Normalized relational schema — roots, lemmas, inflected forms, conjugation cells, sentences, and frequency data all properly linked; GIN indexes on every searchable column
• Dual interface — clean JSON REST API (/api/*) and a server-rendered HTML web UI sharing the same query layer
• One-command deploy — Docker Compose orchestrates Postgres, schema init, data import, and the API with health checks and correct startup order

API at a glance

GET /api/search?query=peace&type=meaning
GET /api/search?query=כתב&type=word&deep=true
GET /api/search?query=katavti&type=transcription
GET /api/lemmas/{uuid}          → full conjugation tables + sentences + frequency
GET /api/browse?page=2          → paginated alphabetical index

// GET /api/lemmas/{uuid}
{
  "hebrew": "כָתַב",
  "transcription": "katav",
  "meaning": "to write",
  "root": { "display": "כ - ת - ב" },
  "conj_tables": [ ... ],
  "sentences": [
    { "sentence": "הוא כתב מכתב.", "translation": "He wrote a letter." }
  ],
  "sources": { "songs": 12, "news": 34, "youtube": 5, "total": 51 }
}

Getting Started

• Clone: git clone https://github.com/gzappaa/hebrew_vocab_hub_API
• Setup: python DATABASE/setup_data.py (downloads dataset, creates env files)
• Run: docker compose up --build → API live at http://localhost:8000
• Tests: cd API && pytest / cd DATABASE && pytest

hebrew_vocab_hub — Data Analysis

pandas Jupyter SQL matplotlib / seaborn Gemini API Power BI Excel Python

Does Hebrew vocabulary follow Zipf's law? Do songs, news, and YouTube really speak differently? Is a word's frequency predictable from how many domains it shows up in?

The analysis and reporting layer built on top of hebrew_vocab_hub_API's dataset — three Jupyter notebooks covering exploratory analysis, cross-source vocabulary comparison, and a statistical test of linguistic patterns, followed by an Excel workbook and a Power BI dashboard built from the same queries.

GitHub Repository

https://github.com/gzappaa/hebrew-vocab-hub-analysis

What makes it interesting

• Confirmed Zipf's law in the dataset's word frequency distribution (power-law exponent ≈ -1), with an explicit caveat that the frequencies reflect the scraped sources, not general spoken Hebrew
• LLM-assisted semantic labeling — used the Gemini API to classify the top 50 words per source into register categories (emotional/social, public/formal, informal/everyday, neutral), then compared distributions across songs, news, and YouTube
• A real hypothesis test — checked whether words appearing across more sources have higher frequency (they do), with a full limitations section covering corpus representativeness, source imbalance, and unvalidated LLM labels
• Root productivity analysis — surfaced which of Hebrew's tri-consonantal roots generate the largest families of derived words in the dataset
• Same data, three formats — the notebooks, a 9-sheet Excel workbook, and a 3-page Power BI dashboard all trace back to the same Postgres queries, so nothing is hand-copied between them

At a glance

01_eda.ipynb                 → dataset structure, POS distribution, Zipf's law check
02_source_comparison.ipynb   → vocabulary overlap + LLM semantic register labeling
03_linguistic_patterns.ipynb → source coverage vs. word frequency, limitations & bias

excel/hebrew_vocab_hub_report.xlsx  → 9-sheet export, same data source as the dashboard
powerbi/hebrew-vocab-hub-analysis.pbix + .pdf

Getting Started

• Clone: git clone https://github.com/gzappaa/hebrew-vocab-hub-analysis
• Install: pip install -r requirements.txt
• Connect: point .env at the same Postgres instance used by hebrew_vocab_hub_API
• Run notebooks from notebooks/, or regenerate the Excel workbook: python scripts/generate_workbook.py

hebrew_vocab_hub

Scrapy Playwright MongoDB MySQL PostgreSQL JSONB Python pandas GitHub Actions

A data pipeline that builds a structured Hebrew vocabulary dataset from multiple real-world Israeli sources — dictionary entries, song lyrics, news articles, and YouTube comments — and outputs a single unified JSON ready for use in language learning APIs, apps, or databases.

The core challenge was building a robust multi-source data pipeline across heterogeneous Hebrew data sources, dealing with rate limits, API restrictions, and inconsistent web structures, while ensuring ethical scraping practices and data normalization in a language outside my full fluency.

GitHub Repository

Full code and instructions are available at: https://github.com/gzappaa/hebrew_vocab_hub

What it produces

~13,000 Hebrew words, each entry containing meanings with niqqudot, transcription, root, part of speech, full conjugation/declension tables, bilingual example sentences, and real-world frequency across songs, news, and YouTube.

{
  "word": "חברה",
  "multiple_meanings": true,
  "meanings": [
    { "hebrew": "חֶבְרָה", "transcription": "chevra", "meaning": "company, society", ... },
    { "hebrew": "חֲבֵרָה", "transcription": "chavera", "meaning": "girlfriend; female friend", ... }
  ],
  "sentences": [
    { "sentence": "החברה הזו גדולה.", "translation": "This company is big.", "source": "reverso" }
  ],
  "sources": { "songs": 12, "news": 45, "youtube": 8, "total": 65 }
}

The dataset is PostgreSQL JSONB-ready — insert the whole document into a jsonb column and index on word.

Pipeline

pealim.com → spider_dict + spider_words → dict-complete.json
Spotify Israel charts → lyrics (Genius API + spider_lyrics) → all_lyrics.txt
hadshon.co.il → news articles → hadshon_articles.json
YouTube Data API → trending comments → word_sources.json
Tatoeba API + Reverso (Playwright) → example sentences
↓
final_dataset.py → vocab_dataset.json

Data sources

• pealim.com — dictionary entries with niqqudot, transcription, conjugation tables
• hadshon.co.il — Israeli government Hebrew learning site; news articles used for word frequency
• Spotify Israel top charts — top ~2,000 songs since 2018, filtered for Hebrew titles
• Genius — song lyrics scraped for each Hebrew track
• Tatoeba — bilingual example sentences (public API)
• Reverso Context — bilingual sentences for words missing from Tatoeba, via Playwright
• YouTube Data API v3 — daily trending video comments from Israel

Getting Started

• Clone: git clone https://github.com/gzappaa/hebrew_vocab_hub
• Install: pip install -r requirements.txt && playwright install chromium
• Full bootstrap: make bootstrap
• Daily refresh: make daily
• Tests: make test

Automated Book Data Pipeline

This project is a modular, reproducible, and testable pipeline for scraping, processing, and analyzing book data from Books to Scrape. It collects book information, cleans and analyzes the data, downloads cover images, and generates PDF and Excel reports.

GitHub Repository

Full code and instructions are available at: https://github.com/gzappaa/Automated-Book-Data-Pipeline

Pipeline Modules

• Scraper: Collects book data using BeautifulSoup + multithreading.
• Parser: Extracts structured info from HTML catalog and book pages.
• Data Cleaning: Normalizes categories, converts prices, extracts availability.
• Cover Downloader: Saves book images as UPC_title.jpg.
• Analytics: Computes totals, averages, min/max prices, per-category stats.
• Reporting: Generates PDF and Excel reports.
• Testing: Unit tests with mocks and CI-ready configuration.
• Docker: Containerized pipeline for easy reproducibility.

Workflow

scrape books (basic info) → fetch detailed info → clean data → download covers → generate reports

Getting Started

• Clone the repo: git clone https://github.com/gzappaa/Automated-Book-Data-Pipeline
• Install dependencies: pip install -r requirements.txt
• Run scraper: python -m src.scraper
• Run with Docker: docker build -t book-pipeline . and docker run --rm -v ${PWD}/data:/app/data book-pipeline

Notes

All prices are numeric only (no € symbol), but amounts are in euros. All data outputs (JSON, PDFs, Excel, images) are saved in the data/ folder.

Screenshots / Reports (Optional)

You can include some screenshots of PDFs, Excel sheets, or cover images here to make it more visual.

</body> </html>