Extract Invoice and Receipt Data from Email into Postgres

What This Builds

This recipe builds an event-driven ETL pipeline for unstructured financial documents. When a new invoice or receipt lands in an inbox (or a watched folder), a worker reads the attachment or body, runs a LangChain extraction chain that maps the messy text to a strict schema (vendor, invoice number, date, line items, total, currency), validates it, and inserts a row into Postgres.

The core idea is using an LLM as the extraction step in a classic scrape/parse → structure → store flow, with a Pydantic schema as the contract so only well-formed records reach the database.

The Stack

• LangChain provides structured extraction via with_structured_output(schema) (or the extraction-chain pattern), turning free text into a typed object that matches your Pydantic model.
• OpenAI (or any function/tool-calling model) is the extraction model; tool-calling is what makes the structured output reliable.
• Neon (serverless Postgres) is the store. Any Postgres works — Neon’s free tier and instant branching make it convenient for an always-on worker.

Step-by-Step Outline

1. Define the target schema as a Pydantic model: vendor, invoice_number, issue_date, currency, total, and a list of LineItem objects. Mark uncertain fields Optional so the model can emit null instead of hallucinating.
2. Set up the trigger: a Gmail/IMAP watch (or a webhook from an inbox provider) that fires the worker when a matching email arrives; pull the body and any PDF/image attachment text.
3. For PDF/image attachments, extract text first (a PDF text layer, or an OCR/vision model for scans), then pass the text into the chain.
4. Build the extraction step: llm.with_structured_output(Invoice) and a prompt that instructs the model to use only what is present and return null for missing fields.
5. Validate the returned object against the Pydantic model; reject or route to a review queue anything that fails (e.g. total not parseable).
6. Insert the validated record into Postgres (parent invoices row + child line_items rows), using the invoice number + vendor as a uniqueness key so reprocessing the same email is idempotent.

Why This Shape Works

Invoices and receipts are notoriously inconsistent across vendors, which is exactly where rule-based parsers break and LLM extraction shines. Constraining the model with a Pydantic schema and tool-calling converts “summarize this” into “fill these fields, or say you can’t,” so the database only ever sees structured, validated rows. Making the insert idempotent on a natural key keeps an event-driven worker safe against duplicate webhooks.

Source

Based on LangChain’s “Use Case Accelerant: Extraction Service” and the DEV Community walkthrough “Extract Invoice Data Automatically Using LangChain”. LangChain: https://www.langchain.com/blog/use-case-accelerant-extraction-service — DEV: https://dev.to/aws-builders/extract-invoice-data-automatically-using-langchain-ga7