Case study · In production · public code

Gmail Document Parser

HTML email → JSONB with dynamic field discovery

PythonGmail APIPostgresBeautifulSoupSupabase
30-second version
Problem
Close-out status reports arrived as HTML emails with five package types and shifting fields; data was copy-pasted by hand.
Built
Incremental Gmail fetcher plus a separate BeautifulSoup parser that strips hidden spans and extracts label:value pairs into JSONB.
Result
5 package types parsed into the warehouse daily in production; new fields need no schema change.
Role
Sole engineer

The Problem

A team was using Gmail as a structured-data channel. Close-out package status reports landed in the inbox as HTML emails. There were five distinct package types (landlord close-outs, standard close-outs, interim-package reviews, package reviews, live reviews), each with its own field set, all evolving over time. Those fields needed to flow into the warehouse and onto dashboards without anyone copy-pasting.

The trap: every parser written against email is one template change away from breaking. The HTML wasn't clean either. The sender embedded zero-size tracking spans that fractured words mid-token when extracted. The solution had to absorb format drift without code changes for new fields, and survive HTML that actively fought the parser.

Architecture

rendering diagram…

Scraping and parsing are two separable stages writing to two tables. The scraper does incremental fetch: it reads MAX(received_at) from the warehouse and appends an after: filter to the Gmail query, so only newer mail gets pulled. Raw headers plus the full HTML body land in data_raw.raw_emails and data_staging.stg_emails.

The parser reads those bodies separately. It strips hidden tracking spans, finds the package header, walks the containing table extracting every label:value pair into a JSONB fields column, and writes data_staging.stg_status_emails. Because parsing is decoupled from scraping, a parser improvement can be re-applied to the entire history without re-fetching a single email from Gmail.

Key Decisions

  • JSONB over fixed columns. Five package types with overlapping-but-different fields, and the templates change. JSONB absorbs new fields without migrations. The Sheets export carries the known columns and rolls any unrecognized keys into a single Other Fields (JSON) column, so a new field never breaks the export.
  • Ordered header classification. LANDLORD CLOSE OUT PACKAGE contains CLOSE OUT PACKAGE as a substring. The patterns are checked longest-first, so a landlord email is never misclassified as a generic close-out.
  • Hidden-span stripping with artifact repair. The sender hides hash IDs inside font-size:0/0pt/1pt spans. Decomposing them is step one; step two repairs the damage left behind. get_text(" ") had split Construction into C onstruction and a hidden-span hash had eaten the LA off LANDLORD, leaving NDLORD. Both are repaired with targeted regex.
  • Re-parse without re-fetch. The parser upserts with ON CONFLICT (message_id) DO UPDATE, refreshing fields and stamping parsed_at = NOW(), not DO NOTHING. Improving the parser and re-running re-applies to every stored email without touching the Gmail API. The scrape stage stays DO NOTHING; the parse stage is the one that re-derives.

Code Samples

Ordered header patterns, longest prefix first so landlord packages never collapse into the generic bucket:

# Header patterns to detect structured package tables (checked in order)
_HEADER_PATTERNS = [
    "LANDLORD CLOSE OUT PACKAGE",  # must be before the generic close-out
    "CLOSE OUT PACKAGE",           # review, revision and related variants
    "INTERIM PACKAGE",             # interim-package reviews
    "PACKAGE REVIEW",              # catch-all for other package types
    "LIVE REVIEW",                 # in-progress live review
]

Hidden-span removal, then repair of the damage it leaves behind:

# Remove tiny-font hidden spans (hash IDs use 0/0pt/1pt font-size)
for span in soup.find_all("span", style=lambda s: s and re.search(
    r"font-size:\s*(?:0(?:px|pt|%)?|1pt)\b", s
)):
    span.decompose()

# Repair span-removal artifact: a hidden hash ate the leading "LA"
if t.startswith('NDLORD CLOSE OUT PACKAGE'):
    t = 'LA' + t

# Rejoin labels split by get_text: "C onstruction Engineer" -> "Construction…"
label = re.sub(r'\b([A-Z]) ([a-z])', r'\1\2', label)

Re-parseable upsert, so improvements re-apply to stored mail without re-scraping:

INSERT INTO data_staging.stg_status_emails
    (message_id, package_type, fields, file_url, vendor_url, parse_error)
VALUES ($1, $2, $3, $4, $5, $6)
ON CONFLICT (message_id) DO UPDATE SET
    package_type = EXCLUDED.package_type,
    fields       = EXCLUDED.fields,
    file_url     = EXCLUDED.file_url,
    vendor_url   = EXCLUDED.vendor_url,
    parse_error  = EXCLUDED.parse_error,
    parsed_at    = NOW();

Metrics

  • Five package header types classified in priority order into a single JSONB fields column, with no schema change when a new field appears.
  • Incremental fetch keyed on MAX(received_at); fetch ceiling raised from 2,000 to 10,000 results per run.
  • Daily evening chain. The scrape runs once a day in the evening (Philippine time), fired by a Google Apps Script time trigger that posts a repository_dispatch, with a once-per-day guard. On a scheduled run the scraper then dispatches the task pipeline, which refreshes and dispatches the date validator: scraper → task pipeline → validator. The validator's preflight checks the ordering, so it never reconciles today's emails against a stale task snapshot. The chain is skipped on weekends, and a rejected dispatch fails the run rather than passing silently.
  • Re-parse cost is decoupled from Gmail. Parser fixes re-apply across the full stored corpus without re-fetching.
  • Known parser gap (pinned 2026-06-02): revision emails frequently drop dates. 3,834 rows over a 60-day window carried a null completion date. The gap is upstream of the date-validator and tracked openly rather than papered over.

What I Learned

  • Email parsers fail at the smallest scale. A zero-size span isn't visible, isn't in your test fixtures, and silently splits one word into two. The only way to find these is to diff parsed output against the rendered message and chase the one-character anomalies.
  • Separating scrape from parse pays for itself. Because the expensive, rate-limited stage (Gmail) and the iterate-heavy stage (parsing) write to different tables, every parser experiment runs against stored HTML for free. That single boundary turned parser tuning from a network-bound chore into a tight local loop.
  • Dynamic schemas are a feature, not infrastructure. Choosing JSONB up front let the team add fields by editing the email template, not the parser. They own their own data shape.

Public reference repository. The production system runs in a private employer repository.