Tahmid Jawad Shafi data engineering, home
← All projects
app · case study

ProtoMacro

Grounds a nutrition tracker's 567 foods in Bangladeshi and USDA reference tables instead of unsourced hand-entered values.

RoleDesigned and shipped
TeamSolo
Period2026-09 – 2026-11
Statuscompleted
DataReal reference data
StackJavaScript · Python · DuckDB · SQL · Pandas · Vite · Chart.js · Vitest · Pytest · Playwright

TL;DR

Local-first nutrition & fitness PWA backed by a reproducible DuckDB food composition pipeline for Bangladeshi and international foods. Source: README.md:3 @ e635153.

Context & motivation

Bangladeshi foods are thinly covered by the reference databases most trackers build on, and ProtoMacro's original list of 194 foods was hand-entered with no sources. A DuckDB ETL pipeline replaces that list with foods from the Food Composition Table for Bangladesh (FCTB 2013) and USDA FoodData Central, and migrates saved favourites, recents, planner, recipes and saved meals without changing logged history. Source: README.md:21 @ e635153.

My contribution

  • Built a tested DuckDB pipeline that extracts, SHA-256-verifies, normalises and validates FCTB 2013 and USDA FoodData Central data into a catalog with provenance on every row.
  • Audited the legacy hand-entered values against exact primary-source matches and migrated saved v2 IDs to v3 without altering logged history.
  • Shipped an offline-first PWA (Vanilla JS + Workbox) with Vitest, pytest and Playwright checks in CI.

How I used AI tools

Implementation by an AI coding agent (Google Antigravity). I set the scope, approved every plan, ran the checks, and accepted only work that passed automated gates.

Pipeline run

Counts in foods. Each count links to its recorded source in Data sources.

Key decisions

  1. Exact-only replacement: only match_type = exact replaces legacy macros

    Alternatives
    approximate and different_state items keep curated values, linked via related_ids
    Why
    Wrong-dish or wrong-state macros are never silently substituted.
    source: README.md:50 @ e635153
  2. Flag, don’t drop: non-fatal checks annotate rows; hard errors (negative macros, duplicate IDs, missing provenance) fail the build

    Why
    Real foods are never silently dropped.
    source: README.md:52 @ e635153
  3. Model carbs_total and carbs_available separately, with fibre exposed separately

    Why
    FCTB reports available carbohydrate (excluding fibre), whereas USDA FDC reports carbohydrate by difference (including fibre).
    source: README.md:51 @ e635153

Results

567Foods in the sourced catalog, each with row-level provenance (381 FCTB 2013 · 69 USDA FDC SR Legacy · 117 labelled estimates)verified 2026-11 · source: README.md:11, 63 @ e635153
2.5% / 34.9%Calorie error found in the legacy hand-entered data (n = 74) (median / 90th percentile, absolute)verified 2026-11 · source: README.md:21 @ e635153

Limitations & next steps

Next steps

Full list in README

Data sources