AI-Powered Supplier Evaluation System

Introduction

30 Second Summary

Every procurement team drowns in supplier documents. Quotations, certifications, and price lists arrive in different formats, making it nearly impossible to quickly compare options and choose the right partner.

In this project, you will build a Python pipeline that extracts data from supplier documents, uses AI to interpret unstructured text into structured records, stores everything in a queryable database, and ranks suppliers based on configurable scoring criteria.

What You'll Build

A supplier intelligence system that processes PDFs and Excel files, extracts structured data using Claude's AI, and produces ranked comparison tables for procurement decisions.

By the end of this project, you'll have:

• A document processing pipeline that extracts text from supplier PDFs and Excel files using pdfplumber and pandas.
• An AI extraction module that uses Claude Haiku's structured outputs to parse unstructured text into validated Pydantic models.
• A SQLite database and scoring engine that stores normalized supplier data and ranks suppliers by configurable weighted criteria.
• Secret Mission: Build a smart supplier match feature that takes a natural language requirement and returns the top matching suppliers with compatibility scores.

Are there any prerequisites?

You need basic Python knowledge (functions, dictionaries, and loops). No prior experience with PDF extraction, databases, or AI APIs is required.

Set Up Your Python Environment

Before your pipeline can process supplier documents, it needs a clean environment with all the right packages installed.

You'll install five libraries that each handle a different part of the pipeline. pdfplumber reads text and tables from PDFs. pandas and openpyxl handle Excel files. The anthropic SDK connects to Claude's AI. pydantic validates the structured data Claude returns.

In this step, get ready to:

• Create a virtual environment and install all dependencies.
• Configure your Anthropic API key.
• Verify everything works with a quick test script.

Create a virtual environment and install dependencies

A virtual environment isolates your project's packages from the rest of your system. This prevents version conflicts between projects.

• In your terminal, make sure you're inside your project folder.
• Create a virtual environment by running the command for your operating system:

macOS

python3 -m venv venv

• Activate the virtual environment by running:

source venv/bin/activate

Windows

python -m venv venv

• Activate the virtual environment by running:

venv\Scripts\activate

You should see (venv) appear at the start of your terminal prompt. This confirms the virtual environment is active.

Don't see (venv) in your prompt?

On Windows PowerShell, you may need to enable script execution first. Run Set-ExecutionPolicy -ExecutionPolicy RemoteSigned -Scope CurrentUser and then try the activate command again.

On macOS, make sure you used source before the path. Without it, the script runs in a subprocess and won't affect your terminal session.

help me activate my Python virtual environment

• Create a file called requirements.txt in your project folder with the following content:

pdfplumber
pandas
openpyxl
anthropic
pydantic

What does this file do?

A requirements.txt file lists all the packages your project depends on. Running pip install -r requirements.txt installs them all at once.

• Save requirements.txt.
• Install all dependencies by running:

pip install -r requirements.txt

You should see pip downloading and installing each package. The output ends with a success message listing all installed packages.

Seeing a permission error or install failure?

Make sure (venv) appears in your prompt before running pip. Installing without an active virtual environment can cause permission issues.

If you see a network timeout, check your internet connection and try again.

help me fix pip install errors in my virtual environment

✔️ Awesome, I've got everything!

Great. Double check you've saved requirements.txt and all packages installed successfully.

ⓧ I'd like to double check the full code

Here's what your requirements.txt should look like:

pdfplumber
pandas
openpyxl
anthropic
pydantic

Set up your Anthropic API key

The anthropic SDK reads your API key from an environment variable called ANTHROPIC_API_KEY. You need to create this key in the Anthropic console and then set it in your terminal.

• Open your browser and go to console.anthropic.com.
• Sign in or create a free account.
• Navigate to API Keys in the settings.
• Click Create Key.
• Copy the key immediately. It is only shown once.

What is an API key?

An API key is a unique credential that identifies your application to a service. The Anthropic SDK uses it to authenticate every request you send to Claude.

Keep this key private. Never commit it to version control or share it publicly.

• Back in your terminal (with (venv) still active), set your API key as an environment variable:

macOS

export ANTHROPIC_API_KEY='your-api-key-here'

Windows

set ANTHROPIC_API_KEY=your-api-key-here

• Replace your-api-key-here with the actual key you copied from the console.

This key only lasts for your current session

The environment variable disappears when you close your terminal. You'll need to run the export command again each time you open a new terminal session for this project.

Verify your setup with a test script

A quick test confirms that all packages import correctly and that your API key connects to Claude.

• Create a file called test_setup.py in your project folder with the following content:

import pdfplumber
import pandas as pd
from anthropic import Anthropic
from pydantic import BaseModel

# Verify all imports loaded
print("All imports successful!")

# Test Claude API connection
client = Anthropic()
response = client.messages.create(
    model="claude-haiku-4-5",
    max_tokens=100,
    messages=[{"role": "user", "content": "Say hello in one sentence."}]
)
print(f"Claude says: {response.content[0].text}")

What does this script do?

The first four lines import each package your project needs. If any install failed, Python raises an ImportError here.

The Anthropic() client automatically reads ANTHROPIC_API_KEY from your environment. The client.messages.create() call sends a simple prompt to Claude Haiku 4.5 and prints the response.

• Save test_setup.py and run it:

python test_setup.py

You should see two lines of output: a confirmation that imports succeeded and a short response from Claude.

Seeing an authentication error?

If you see a message about an invalid API key, double check that you ran the export command in the same terminal session. Run echo $ANTHROPIC_API_KEY (macOS) or echo %ANTHROPIC_API_KEY% (Windows CMD) to verify it is set.

If you see an ImportError, your virtual environment may not be active. Check for (venv) in your prompt and re-run pip install -r requirements.txt if needed.

help me fix my Claude API connection test

• Once the test passes, delete test_setup.py since it was only for verification.

Your environment is locked and loaded. Next up, you'll create the sample supplier documents that your pipeline will process.

Create Sample Supplier Documents

Your Python environment is ready with all the tools you need. Now you need data to process.

Real supplier evaluation systems process messy documents from dozens of vendors. Each supplier sends information in their own format. Quotations, capability statements, audit reports, price lists. Your pipeline needs to handle all of them.

In this step, you will create sample supplier documents that simulate what a procurement team receives in practice. These include text-based supplier documents (simulating PDFs) and structured Excel spreadsheets.

In this step, get ready to:

• Create 3 text-based supplier documents simulating PDF quotations, capability statements, and audit reports.
• Create 2 Excel files with pricing and performance data.
• Run the script to generate all documents in a supplier_docs/ folder.

Create the text-based supplier documents

Procurement teams receive supplier information as PDFs. Quotation letters, capability statements, and audit summaries all arrive as unstructured text inside documents.

For this project, you will create text files that simulate the content extracted from real PDFs. This keeps the focus on the pipeline logic without needing actual PDF generation libraries.

• In your project folder, create a new file called create_sample_docs.py and add the following code to start the function and define three supplier documents:

import pandas as pd
import os


def create_sample_documents():
    os.makedirs("supplier_docs", exist_ok=True)

    pdf1_text = """
QUOTATION

FreshPack Solutions Ltd.
123 Industrial Avenue, Birmingham, UK
Date: 2024-03-15

Product: Multi-layer barrier film (food grade)
Unit Price: $2.45 per kg
MOQ: 1,000 kg
Lead Time: 21 days from order confirmation
Production Capacity: 50,000 kg per month
Delivery Cost: $350 (FOB Birmingham)

Certifications:
- BRC Global Standard for Packaging (Grade AA)
- ISO 9001:2015
- ISO 14001:2015
- FSSC 22000

Sustainability: Carbon Neutral certified since 2022.
Quality: quality score: 98/100
"""

    pdf2_text = """
SUPPLIER CAPABILITY STATEMENT

GreenLeaf Ingredients GmbH
Industriestrasse 45, Hamburg, Germany

Product Categories: Dried Fruits, Nuts, Seeds, Plant-Based Proteins, Organic Ingredients

Certifications Held:
- ISO 22000:2018
- HACCP Certified
- EU Organic Certification
- ISO 9001:2015

Monthly Capacity: 200,000 kg
Minimum Order: 500 kg
Standard Lead Time: 14 days (EU)
Average Unit Price: $8.50/kg
Delivery: EUR 500 (~$545 USD)

Quality Metrics: Customer satisfaction score: 95/100
"""

    pdf3_text = """
SUPPLIER AUDIT SUMMARY REPORT

Supplier: AsiaSpice Trading Co., Ltd.
Location: Bangkok, Thailand

Product Range: Spices, Herbs, Seasonings, Curry Pastes

Pricing: Representative average: $5.75/kg
Minimum Order Quantity: 2,000 kg
Lead Time: 35 days
Shipping Cost: $800 per shipment
Capacity: 80,000 kg/month

Certifications Verified:
- HACCP (valid until 2025)
- ISO 9001:2015
- Halal Certified

Quality Audit Score: 78/100
"""

    for name, content in [
        ("freshpack_quotation.txt", pdf1_text),
        ("greenleaf_capability.txt", pdf2_text),
        ("asiaspice_audit.txt", pdf3_text),
    ]:
        with open(f"supplier_docs/{name}", "w") as f:
            f.write(content)
        print(f"  Created: supplier_docs/{name}")

What does this code do?

This function simulates what you would get after extracting text from real supplier PDFs:

• os.makedirs("supplier_docs", exist_ok=True) creates the output folder. The exist_ok=True flag means it won't error if the folder already exists.
• Each pdf_text variable holds the kind of unstructured text your AI extractor will later need to parse. Notice how each supplier presents information differently.
• The loop writes each text string to a separate file in the supplier_docs/ folder.

Why text files instead of actual PDFs?

In a real scenario, pdfplumber extracts text from actual PDFs. By using text files here, you skip the PDF generation step and focus on what matters: building the extraction and scoring pipeline.

The processor module you build next will handle real PDFs too. These text files just give you reliable test data without needing a PDF creation library.

FreshPack Solutions (UK) is a packaging supplier. GreenLeaf Ingredients (Germany) supplies organic food ingredients. AsiaSpice Trading (Thailand) supplies spices and seasonings. Each has different pricing, lead times, and certifications.

You won't see a change yet. The next chunk adds the Excel data before you can run the script.

Add the Excel supplier data

Suppliers also send structured data in spreadsheets. Price comparison lists and performance scorecards arrive as Excel files. Your pipeline needs to handle these alongside the unstructured text documents.

• Still in create_sample_docs.py, add the following code below the file-writing loop (still inside the create_sample_documents() function) to create the price comparison spreadsheet:

    price_data = {
        "Supplier": ["NordicPure AB", "MediterraneanFoods SRL"],
        "Country": ["Sweden", "Italy"],
        "Product Category": ["Dairy Ingredients", "Olive Oils & Vinegars"],
        "Unit Price (USD/kg)": [6.20, 9.80],
        "MOQ (kg)": [750, 300],
        "Lead Time (days)": [10, 18],
        "Certifications": ["ISO 9001, ISO 14001, FSSC 22000", "BRC, ISO 9001, Organic EU"],
        "Monthly Capacity (kg)": [120000, 45000],
        "Delivery Cost (USD)": [420, 280],
        "Quality Score": [92, 88],
        "Sustainability": ["Carbon neutral, renewable energy", "Organic certified, local sourcing"],
    }
    df_prices = pd.DataFrame(price_data)
    df_prices.to_excel("supplier_docs/price_comparison.xlsx", index=False)
    print("  Created: supplier_docs/price_comparison.xlsx")

What does this code do?

This creates a structured price comparison file for two additional suppliers: NordicPure AB (Sweden) and MediterraneanFoods SRL (Italy).

• pd.DataFrame(price_data) converts the dictionary into a tabular format.
• to_excel() writes the DataFrame to an .xlsx file. The index=False parameter prevents row numbers from being written to the file.

You won't see a change yet. The next chunk completes the file.

• Add the performance scorecard data and the script entry point below the price comparison code (still inside the function, then add the if __name__ block outside it):

    perf_data = {
        "Supplier": ["FreshPack Solutions", "GreenLeaf Ingredients", "AsiaSpice Trading", "NordicPure AB", "MediterraneanFoods SRL"],
        "On-Time Delivery %": [96, 97, 85, 99, 93],
        "Quality Conformance %": [99.5, 99.0, 95.0, 99.8, 98.5],
        "Response Time (hours)": [4, 8, 24, 2, 12],
        "Overall Rating": [4.5, 4.3, 3.5, 4.8, 4.1],
    }
    df_perf = pd.DataFrame(perf_data)
    df_perf.to_excel("supplier_docs/performance_scorecard.xlsx", index=False)
    print("  Created: supplier_docs/performance_scorecard.xlsx")


if __name__ == "__main__":
    create_sample_documents()

What does this code do?

This adds the final piece of the sample data:

• The performance scorecard tracks all 5 suppliers together. It includes on-time delivery rates, quality conformance, and response times.
• if __name__ == "__main__" means the create_sample_documents() function runs when you execute the script directly from the terminal.

✔️ Awesome, I've got everything!

Great. Make sure you have saved create_sample_docs.py before moving on.

ⓧ I'd like to double check the full code

import pandas as pd
import os


def create_sample_documents():
    os.makedirs("supplier_docs", exist_ok=True)

    pdf1_text = """
QUOTATION

FreshPack Solutions Ltd.
123 Industrial Avenue, Birmingham, UK
Date: 2024-03-15

Product: Multi-layer barrier film (food grade)
Unit Price: $2.45 per kg
MOQ: 1,000 kg
Lead Time: 21 days from order confirmation
Production Capacity: 50,000 kg per month
Delivery Cost: $350 (FOB Birmingham)

Certifications:
- BRC Global Standard for Packaging (Grade AA)
- ISO 9001:2015
- ISO 14001:2015
- FSSC 22000

Sustainability: Carbon Neutral certified since 2022.
Quality: quality score: 98/100
"""

    pdf2_text = """
SUPPLIER CAPABILITY STATEMENT

GreenLeaf Ingredients GmbH
Industriestrasse 45, Hamburg, Germany

Product Categories: Dried Fruits, Nuts, Seeds, Plant-Based Proteins, Organic Ingredients

Certifications Held:
- ISO 22000:2018
- HACCP Certified
- EU Organic Certification
- ISO 9001:2015

Monthly Capacity: 200,000 kg
Minimum Order: 500 kg
Standard Lead Time: 14 days (EU)
Average Unit Price: $8.50/kg
Delivery: EUR 500 (~$545 USD)

Quality Metrics: Customer satisfaction score: 95/100
"""

    pdf3_text = """
SUPPLIER AUDIT SUMMARY REPORT

Supplier: AsiaSpice Trading Co., Ltd.
Location: Bangkok, Thailand

Product Range: Spices, Herbs, Seasonings, Curry Pastes

Pricing: Representative average: $5.75/kg
Minimum Order Quantity: 2,000 kg
Lead Time: 35 days
Shipping Cost: $800 per shipment
Capacity: 80,000 kg/month

Certifications Verified:
- HACCP (valid until 2025)
- ISO 9001:2015
- Halal Certified

Quality Audit Score: 78/100
"""

    for name, content in [
        ("freshpack_quotation.txt", pdf1_text),
        ("greenleaf_capability.txt", pdf2_text),
        ("asiaspice_audit.txt", pdf3_text),
    ]:
        with open(f"supplier_docs/{name}", "w") as f:
            f.write(content)
        print(f"  Created: supplier_docs/{name}")

    price_data = {
        "Supplier": ["NordicPure AB", "MediterraneanFoods SRL"],
        "Country": ["Sweden", "Italy"],
        "Product Category": ["Dairy Ingredients", "Olive Oils & Vinegars"],
        "Unit Price (USD/kg)": [6.20, 9.80],
        "MOQ (kg)": [750, 300],
        "Lead Time (days)": [10, 18],
        "Certifications": ["ISO 9001, ISO 14001, FSSC 22000", "BRC, ISO 9001, Organic EU"],
        "Monthly Capacity (kg)": [120000, 45000],
        "Delivery Cost (USD)": [420, 280],
        "Quality Score": [92, 88],
        "Sustainability": ["Carbon neutral, renewable energy", "Organic certified, local sourcing"],
    }
    df_prices = pd.DataFrame(price_data)
    df_prices.to_excel("supplier_docs/price_comparison.xlsx", index=False)
    print("  Created: supplier_docs/price_comparison.xlsx")

    perf_data = {
        "Supplier": ["FreshPack Solutions", "GreenLeaf Ingredients", "AsiaSpice Trading", "NordicPure AB", "MediterraneanFoods SRL"],
        "On-Time Delivery %": [96, 97, 85, 99, 93],
        "Quality Conformance %": [99.5, 99.0, 95.0, 99.8, 98.5],
        "Response Time (hours)": [4, 8, 24, 2, 12],
        "Overall Rating": [4.5, 4.3, 3.5, 4.8, 4.1],
    }
    df_perf = pd.DataFrame(perf_data)
    df_perf.to_excel("supplier_docs/performance_scorecard.xlsx", index=False)
    print("  Created: supplier_docs/performance_scorecard.xlsx")


if __name__ == "__main__":
    create_sample_documents()

Run the script and verify the folder structure

Now you will execute the script to generate all five supplier documents and confirm they landed in the right place.

• Save create_sample_docs.py, then run it by entering this command in your terminal:

python create_sample_docs.py

• You should see output confirming each file was created:

  Created: supplier_docs/freshpack_quotation.txt
  Created: supplier_docs/greenleaf_capability.txt
  Created: supplier_docs/asiaspice_audit.txt
  Created: supplier_docs/price_comparison.xlsx
  Created: supplier_docs/performance_scorecard.xlsx

• Confirm the files exist by running:

ls supplier_docs/

• You should see all five files listed: freshpack_quotation.txt, greenleaf_capability.txt, asiaspice_audit.txt, price_comparison.xlsx, and performance_scorecard.xlsx.

Not seeing all five files?

• Check that you saved create_sample_docs.py before running it. Unsaved changes won't execute.
• Make sure you are running the command from your project folder (the same folder that contains create_sample_docs.py).
• If you see an import error for pandas, confirm your virtual environment is still active.

Help me debug why my create_sample_docs.py script isn't generating all five files.

Your sample data is ready. Next up, you will build the document processing pipeline that reads these files and extracts their text content into a format your AI extractor can work with.

Build the Document Processing Pipeline

Your supplier documents are sitting in a folder, but they're in different formats. Some are PDFs full of free-form text, others are structured Excel spreadsheets.

In this step, you'll build a processing pipeline that knows how to read both formats and convert them into plain text strings. This plain text is what the AI extractor (in the next step) will analyze to pull out structured supplier data.

In this step, get ready to:

• Define a data model for standardized supplier attributes.
• Build PDF and Excel text extraction functions.
• Create a unified processor that routes files to the correct extractor.

Define the supplier data model

Before extracting data, you need a clear definition of what "supplier data" looks like. A Pydantic model acts as a contract. It defines every field the pipeline expects, the data types, and which fields are optional.

• Create a new file called models.py in your project folder and add the following code:

from pydantic import BaseModel, Field
from typing import Optional
from enum import Enum


# Standard certification types in the food/packaging industry
class CertificationType(str, Enum):
    BRC = "BRC"
    ISO_9001 = "ISO 9001"
    ISO_14001 = "ISO 14001"
    ISO_22000 = "ISO 22000"
    HACCP = "HACCP"
    FSSC_22000 = "FSSC 22000"
    IFS = "IFS"
    OTHER = "OTHER"


# The schema every supplier record must follow
class SupplierData(BaseModel):
    supplier_name: str = Field(description="The company name of the supplier")
    country: str = Field(description="Country where the supplier is located")
    product_categories: list[str] = Field(description="List of product categories the supplier offers")
    certifications: list[str] = Field(default_factory=list, description="List of certifications held")
    unit_price_usd: Optional[float] = Field(None, description="Representative unit price in USD")
    moq: Optional[int] = Field(None, description="Minimum order quantity")
    lead_time_days: Optional[int] = Field(None, description="Typical lead time in days")
    production_capacity_units_per_month: Optional[int] = Field(None, description="Monthly production capacity")
    delivery_cost_usd: Optional[float] = Field(None, description="Typical delivery/shipping cost in USD")
    sustainability_rating: Optional[str] = Field(None, description="Sustainability rating or initiative")
    quality_score: Optional[float] = Field(None, description="Quality score from 0-100")
    notes: Optional[str] = Field(None, description="Any additional relevant information")

What does this code do?

CertificationType is an enum that lists standard industry certifications (BRC, ISO, HACCP, etc.). This gives you a controlled vocabulary for comparing suppliers.

SupplierData is the Pydantic model that defines every attribute the pipeline tracks. Required fields like supplier_name and country ensure every record has basics. Optional fields (marked with Optional) handle cases where a document doesn't mention pricing or capacity.

• Save models.py.

Seeing a red underline on list[str]?

The list[str] syntax requires Python 3.9+. If your editor flags it, confirm you're using the correct Python version by running python3 --version in your terminal.

help me fix a Pydantic model type error

Build the PDF extraction module

Supplier PDFs often mix free-form paragraphs with pricing tables. The extraction function needs to capture both text blocks and tabular data so the AI doesn't miss anything.

• Create a new file called processor.py and add the imports and PDF extraction function:

import os
import pdfplumber
import pandas as pd
from pathlib import Path


def extract_text_from_pdf(file_path: str) -> str:
    # Collect text from every page, including table content
    text_parts = []
    with pdfplumber.open(file_path) as pdf:
        for page in pdf.pages:
            # Get the main text content from the page
            page_text = page.extract_text()
            if page_text:
                text_parts.append(page_text)
            # Also extract any tables and flatten them into text rows
            tables = page.extract_tables()
            for table in tables:
                for row in table:
                    if row:
                        cleaned_row = [str(cell).strip() if cell else "" for cell in row]
                        text_parts.append(" | ".join(cleaned_row))
    return "\n".join(text_parts)

What does this code do?

pdfplumber.open() opens the PDF and gives you access to each page. For each page, the function grabs two things:

• page.extract_text() pulls out all paragraph-style text (supplier names, addresses, descriptions).
• page.extract_tables() finds any tabular data (pricing grids, capacity charts) and flattens each row into a pipe-separated string so nothing is lost.

• Save processor.py.

Why pdfplumber over other PDF libraries?

pdfplumber is MIT-licensed and has strong table extraction accuracy. Supplier quotations often include pricing tables, and pdfplumber handles those complex table structures better than many alternatives.

Build the Excel extraction and unified processor

The Excel extractor converts spreadsheet data into readable text. Then a routing function ties everything together, automatically picking the right extractor based on file extension.

• In processor.py, add the Excel extraction function below your extract_text_from_pdf function:

def extract_text_from_excel(file_path: str) -> str:
    # Read every sheet and convert it to a text representation
    text_parts = []
    excel_file = pd.ExcelFile(file_path, engine="openpyxl")
    for sheet_name in excel_file.sheet_names:
        df = pd.read_excel(file_path, sheet_name=sheet_name, engine="openpyxl")
        text_parts.append(f"=== Sheet: {sheet_name} ===")
        text_parts.append(df.to_string(index=False))
    return "\n".join(text_parts)

What does this code do?

pd.ExcelFile() opens the workbook and lists all sheet names. For each sheet, it reads the data into a DataFrame and converts it to a string representation. The openpyxl engine handles the .xlsx format.

• Now add the unified processing functions below extract_text_from_excel:

def process_document(file_path: str) -> str:
    # Route to the correct extractor based on file extension
    path = Path(file_path)
    extension = path.suffix.lower()
    if extension == ".pdf":
        return extract_text_from_pdf(file_path)
    elif extension in (".xlsx", ".xls"):
        return extract_text_from_excel(file_path)
    else:
        raise ValueError(f"Unsupported file type: {extension}")


def process_all_documents(folder_path: str) -> dict[str, str]:
    # Scan the folder and process every supported file
    results = {}
    supported_extensions = {".pdf", ".xlsx", ".xls"}
    for file_name in os.listdir(folder_path):
        file_path = os.path.join(folder_path, file_name)
        if Path(file_name).suffix.lower() in supported_extensions:
            try:
                text = process_document(file_path)
                results[file_name] = text
                print(f"  Processed: {file_name} ({len(text)} chars)")
            except Exception as e:
                print(f"  Error processing {file_name}: {e}")
    return results

What does this code do?

• process_document() checks the file extension and routes to either the PDF or Excel extractor. Unsupported types raise a clear error.
• process_all_documents() scans an entire folder, processes every supported file, and returns a dictionary mapping each filename to its extracted text. It wraps each file in a try/except so one bad file doesn't crash the whole batch.

• Save processor.py.
• Test the pipeline by running this command in your terminal:

python3 -c "from processor import process_all_documents; docs = process_all_documents('supplier_docs'); print(f'\nFiles processed: {len(docs)}'); print('Filenames:', list(docs.keys()))"

You should see output showing the 2 Excel files processed with their character counts, plus a summary listing the filenames.

Why only 2 files?

The processor only picks up files with .pdf or .xlsx extensions. Your sample supplier documents are .txt files (simulating what real PDF content would look like). The Excel files get processed because they have the right extension. In production, real PDFs dropped into this folder would be extracted by the pdfplumber function.

Seeing a ModuleNotFoundError?

Make sure your virtual environment is active. You should see the environment name in your terminal prompt. If not, activate it again before running the test command.

If you see an error about pdfplumber or pandas not found, re-run pip install -r requirements.txt to install missing packages.

help me fix a ModuleNotFoundError in processor.py

✔️ Awesome, I've got everything!

Great. Make sure both models.py and processor.py are saved.

ⓧ I'd like to double check the full code

Here is the complete models.py:

from pydantic import BaseModel, Field
from typing import Optional
from enum import Enum


class CertificationType(str, Enum):
    BRC = "BRC"
    ISO_9001 = "ISO 9001"
    ISO_14001 = "ISO 14001"
    ISO_22000 = "ISO 22000"
    HACCP = "HACCP"
    FSSC_22000 = "FSSC 22000"
    IFS = "IFS"
    OTHER = "OTHER"


class SupplierData(BaseModel):
    supplier_name: str = Field(description="The company name of the supplier")
    country: str = Field(description="Country where the supplier is located")
    product_categories: list[str] = Field(description="List of product categories the supplier offers")
    certifications: list[str] = Field(default_factory=list, description="List of certifications held")
    unit_price_usd: Optional[float] = Field(None, description="Representative unit price in USD")
    moq: Optional[int] = Field(None, description="Minimum order quantity")
    lead_time_days: Optional[int] = Field(None, description="Typical lead time in days")
    production_capacity_units_per_month: Optional[int] = Field(None, description="Monthly production capacity")
    delivery_cost_usd: Optional[float] = Field(None, description="Typical delivery/shipping cost in USD")
    sustainability_rating: Optional[str] = Field(None, description="Sustainability rating or initiative")
    quality_score: Optional[float] = Field(None, description="Quality score from 0-100")
    notes: Optional[str] = Field(None, description="Any additional relevant information")

Here is the complete processor.py:

import os
import pdfplumber
import pandas as pd
from pathlib import Path


def extract_text_from_pdf(file_path: str) -> str:
    text_parts = []
    with pdfplumber.open(file_path) as pdf:
        for page in pdf.pages:
            page_text = page.extract_text()
            if page_text:
                text_parts.append(page_text)
            tables = page.extract_tables()
            for table in tables:
                for row in table:
                    if row:
                        cleaned_row = [str(cell).strip() if cell else "" for cell in row]
                        text_parts.append(" | ".join(cleaned_row))
    return "\n".join(text_parts)


def extract_text_from_excel(file_path: str) -> str:
    text_parts = []
    excel_file = pd.ExcelFile(file_path, engine="openpyxl")
    for sheet_name in excel_file.sheet_names:
        df = pd.read_excel(file_path, sheet_name=sheet_name, engine="openpyxl")
        text_parts.append(f"=== Sheet: {sheet_name} ===")
        text_parts.append(df.to_string(index=False))
    return "\n".join(text_parts)


def process_document(file_path: str) -> str:
    path = Path(file_path)
    extension = path.suffix.lower()
    if extension == ".pdf":
        return extract_text_from_pdf(file_path)
    elif extension in (".xlsx", ".xls"):
        return extract_text_from_excel(file_path)
    else:
        raise ValueError(f"Unsupported file type: {extension}")


def process_all_documents(folder_path: str) -> dict[str, str]:
    results = {}
    supported_extensions = {".pdf", ".xlsx", ".xls"}
    for file_name in os.listdir(folder_path):
        file_path = os.path.join(folder_path, file_name)
        if Path(file_name).suffix.lower() in supported_extensions:
            try:
                text = process_document(file_path)
                results[file_name] = text
                print(f"  Processed: {file_name} ({len(text)} chars)")
            except Exception as e:
                print(f"  Error processing {file_name}: {e}")
    return results

Your document processing pipeline is ready. It can handle any mix of PDFs and Excel files dropped into a folder. Next up, you'll build the AI-powered extractor that turns this raw text into structured, validated supplier records.

Build the AI-Powered Data Extractor

Your document processing pipeline can extract raw text from PDFs and Excel files. The next challenge is turning that messy, unstructured text into clean, structured data your database can store.

In this step, you'll use Claude Haiku's structured outputs to interpret supplier documents and return validated data that matches your Pydantic model exactly. The AI reads the raw text, identifies supplier attributes, and returns a JSON object that conforms to your schema.

In this step, get ready to:

• Configure the Claude API client and extraction system prompt.
• Build a function that extracts structured supplier data from document text using structured outputs.
• Add batch extraction with error handling for multiple documents.

Set up the Anthropic client and system prompt

Before calling the Claude API, you need to configure the client and define a system prompt that tells Claude how to behave as a procurement data extraction specialist. The system prompt sets the rules for extraction: only pull explicit facts, leave unknowns as null, and be precise with numbers.

• Create a new file called extractor.py in your project folder by running:

touch extractor.py

• Open extractor.py and add the following imports, client setup, and system prompt:

import json
from anthropic import Anthropic
from models import SupplierData


# Initialize the Anthropic client (reads ANTHROPIC_API_KEY from environment)
client = Anthropic()

# System prompt that tells Claude how to extract supplier data
EXTRACTION_SYSTEM_PROMPT = """You are a procurement data extraction specialist.
Your job is to extract structured supplier information from raw document text.

Rules:
- Extract only information explicitly stated in the document.
- If a field is not mentioned, leave it as null.
- For certifications, use standard abbreviations (BRC, ISO 9001, HACCP, etc.).
- Convert all prices to USD if possible, or note the original currency.
- Lead times should be in days. Convert weeks to days (1 week = 7 days).
- Be precise with numbers. Do not round or estimate.
"""

What does this code do?

The Anthropic() client automatically reads your ANTHROPIC_API_KEY environment variable. No need to pass the key directly in code.

The EXTRACTION_SYSTEM_PROMPT gives Claude specific rules for how to interpret supplier documents. These rules prevent hallucination by telling Claude to only extract facts explicitly stated in the text and to leave missing fields as null.

• Save extractor.py.

Why use a system prompt for extraction?

Without clear rules, an LLM might guess values that aren't in the document or format data inconsistently. The system prompt constrains Claude to behave like a careful data entry specialist who only records what the document actually says.

Build the extraction function with structured outputs

Claude's structured outputs feature guarantees the API response is valid JSON matching your schema. Instead of asking Claude to output JSON and hoping it's well-formed, you pass the schema directly and the API enforces it.

• In extractor.py, add the following function below the EXTRACTION_SYSTEM_PROMPT constant:

def extract_supplier_data(document_text: str, source_filename: str) -> SupplierData:
    # Generate JSON schema from the Pydantic model
    schema = SupplierData.model_json_schema()

    # Call Claude with structured output to guarantee valid JSON
    response = client.messages.create(
        model="claude-haiku-4-5",
        max_tokens=1024,
        system=EXTRACTION_SYSTEM_PROMPT,
        messages=[
            {
                "role": "user",
                "content": f"Extract supplier information from this document (source: {source_filename}):\n\n{document_text[:4000]}",
            }
        ],
        output_config={
            "format": {
                "type": "json_schema",
                "schema": {
                    "type": "object",
                    "properties": schema.get("properties", {}),
                    "required": schema.get("required", []),
                    "additionalProperties": False,
                },
            }
        },
    )

    # Parse the JSON response and validate with Pydantic
    raw_json = response.content[0].text
    data = json.loads(raw_json)
    supplier = SupplierData(**data)
    return supplier

What does this function do?

• SupplierData.model_json_schema() converts your Pydantic model into a JSON Schema that Claude can use as a blueprint for its response.
• output_config with type: "json_schema" tells the API to constrain Claude's output to valid JSON matching your schema. This eliminates malformed responses entirely.
• document_text[:4000] caps the input at 4000 characters to keep costs low while including the most relevant content.
• response.content[0].text contains the raw JSON string. You parse it with json.loads() and validate it by creating a SupplierData instance.

Why structured outputs instead of asking Claude for JSON?

Without structured outputs, you would need to parse free-form text and handle malformed JSON, missing fields, and schema violations. The output_config.format parameter guarantees the response is always valid JSON that matches your schema. This removes the need for retry logic or regex-based parsing.

• Save extractor.py.

Add batch extraction with error handling

In production, you process multiple documents in sequence. Some documents may fail to extract cleanly. You need a function that processes all documents while gracefully handling errors so one bad document does not crash the entire pipeline.

• In extractor.py, add the following function below extract_supplier_data():

def extract_all_suppliers(documents: dict[str, str]) -> list[SupplierData]:
    suppliers = []

    for filename, text in documents.items():
        print(f"  Extracting from: {filename}")
        try:
            supplier = extract_supplier_data(text, filename)
            suppliers.append(supplier)
            print(f"    -> {supplier.supplier_name} ({supplier.country})")
        except Exception as e:
            # Log the error but continue processing remaining documents
            print(f"    Error: {e}")

    return suppliers

What does this function do?

• It loops through a dictionary of filename: text pairs (the output from process_all_documents() in the previous step).
• The try/except block catches any API errors, malformed responses, or validation failures without stopping the entire batch.
• Progress logging (print() statements) shows which documents processed successfully and which ones failed.

• Save extractor.py.
• Test the extractor by running this command in your terminal:

python -c "from processor import process_all_documents; from extractor import extract_all_suppliers; docs = process_all_documents('supplier_docs'); suppliers = extract_all_suppliers(docs); print(f'\nExtracted {len(suppliers)} suppliers')"

You should see output listing each document being processed, followed by each supplier's name and country, ending with a count of extracted suppliers.

Seeing an authentication error?

• Make sure your ANTHROPIC_API_KEY environment variable is still set in this terminal session. Run echo $ANTHROPIC_API_KEY to check.
• Verify your API key is valid and has available credits on the Anthropic console.
• If you opened a new terminal, you need to re-export the key.

Help me debug my Claude API authentication error in my Python extractor.

✔️ Awesome, I've got everything!

Great. Double check you've saved extractor.py and that your test returned extracted supplier data.

ⓧ I'd like to double check the full code

Here is the complete extractor.py file:

import json
from anthropic import Anthropic
from models import SupplierData


client = Anthropic()

EXTRACTION_SYSTEM_PROMPT = """You are a procurement data extraction specialist.
Your job is to extract structured supplier information from raw document text.

Rules:
- Extract only information explicitly stated in the document.
- If a field is not mentioned, leave it as null.
- For certifications, use standard abbreviations (BRC, ISO 9001, HACCP, etc.).
- Convert all prices to USD if possible, or note the original currency.
- Lead times should be in days. Convert weeks to days (1 week = 7 days).
- Be precise with numbers. Do not round or estimate.
"""


def extract_supplier_data(document_text: str, source_filename: str) -> SupplierData:
    schema = SupplierData.model_json_schema()
    response = client.messages.create(
        model="claude-haiku-4-5",
        max_tokens=1024,
        system=EXTRACTION_SYSTEM_PROMPT,
        messages=[
            {
                "role": "user",
                "content": f"Extract supplier information from this document (source: {source_filename}):\n\n{document_text[:4000]}",
            }
        ],
        output_config={
            "format": {
                "type": "json_schema",
                "schema": {
                    "type": "object",
                    "properties": schema.get("properties", {}),
                    "required": schema.get("required", []),
                    "additionalProperties": False,
                },
            }
        },
    )
    raw_json = response.content[0].text
    data = json.loads(raw_json)
    supplier = SupplierData(**data)
    return supplier


def extract_all_suppliers(documents: dict[str, str]) -> list[SupplierData]:
    suppliers = []
    for filename, text in documents.items():
        print(f"  Extracting from: {filename}")
        try:
            supplier = extract_supplier_data(text, filename)
            suppliers.append(supplier)
            print(f"    -> {supplier.supplier_name} ({supplier.country})")
        except Exception as e:
            print(f"    Error: {e}")
    return suppliers

Your AI extractor is working. Next up, you'll design a SQLite database and populate it with the structured supplier data your extractor produces.

Design and Populate the SQLite Database

Your pipeline can now extract structured supplier data from raw documents using AI. The next problem is persistence. Every time you run the extraction, the data lives only in memory and disappears when the script ends.

In this step, you'll store all extracted supplier records in a SQLite database. This gives you a queryable, persistent data store that the scoring engine can read from in later steps.

In this step, get ready to:

• Design a database schema that maps to your SupplierData model.
• Write insertion functions that store validated Pydantic models in SQLite.
• Run the full pipeline end-to-end and populate the database with all 5 supplier records.

Design the database schema

Python's built-in sqlite3 module gives you a full relational database with zero installation. Your schema needs columns that mirror the fields in your SupplierData Pydantic model.

• Create a new file called database.py in your project folder.
• Add the imports, database path constant, and the schema initialization function by pasting the following code:

import sqlite3
from models import SupplierData


DB_PATH = "suppliers.db"


def init_database():
    # Connect to SQLite (creates the file if it doesn't exist)
    conn = sqlite3.connect(DB_PATH)
    cursor = conn.cursor()
    # Create the suppliers table with columns matching SupplierData fields
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS suppliers (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            supplier_name TEXT NOT NULL,
            country TEXT,
            product_categories TEXT,
            certifications TEXT,
            unit_price_usd REAL,
            moq INTEGER,
            lead_time_days INTEGER,
            production_capacity_units_per_month INTEGER,
            delivery_cost_usd REAL,
            sustainability_rating TEXT,
            quality_score REAL,
            notes TEXT,
            source_file TEXT,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    """)
    conn.commit()
    conn.close()
    print(f"Database initialized: {DB_PATH}")

What does this code do?

DB_PATH = "suppliers.db" sets the database file name. SQLite stores the entire database in a single file on disk.

The CREATE TABLE IF NOT EXISTS statement defines a suppliers table with columns for every field in your SupplierData model. Lists like certifications are stored as comma-separated TEXT because SQLite has no native array type.

Why SQLite instead of PostgreSQL?

SQLite is built into Python's standard library, so there's nothing to install or configure. For a single-user local application processing hundreds of suppliers, it's the perfect fit. You get full SQL querying power without any server setup.

• Save database.py.

Write the insertion logic

The schema defines the structure. Now you need functions that take your validated SupplierData objects and insert them as rows in the database.

• In database.py, add the following two functions below init_database():

def insert_supplier(supplier: SupplierData, source_file: str = ""):
    # Open a connection and insert one supplier record
    conn = sqlite3.connect(DB_PATH)
    cursor = conn.cursor()
    cursor.execute(
        "INSERT INTO suppliers (supplier_name, country, product_categories, certifications, unit_price_usd, moq, lead_time_days, production_capacity_units_per_month, delivery_cost_usd, sustainability_rating, quality_score, notes, source_file) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
        (supplier.supplier_name, supplier.country, ",".join(supplier.product_categories), ",".join(supplier.certifications), supplier.unit_price_usd, supplier.moq, supplier.lead_time_days, supplier.production_capacity_units_per_month, supplier.delivery_cost_usd, supplier.sustainability_rating, supplier.quality_score, supplier.notes, source_file),
    )
    conn.commit()
    conn.close()


def insert_all_suppliers(suppliers: list[SupplierData], filenames: list[str]):
    # Insert every supplier from the extraction results
    for supplier, filename in zip(suppliers, filenames):
        insert_supplier(supplier, source_file=filename)
    print(f"  Inserted {len(suppliers)} suppliers into database")

What does this code do?

• insert_supplier() takes a single SupplierData instance and writes it to the database. It joins list fields (like certifications) into comma-separated strings using ",".join().
• The ? placeholders prevent SQL injection by letting SQLite handle value escaping.
• insert_all_suppliers() loops through all extracted suppliers and their source filenames, inserting each one.

• Save database.py.

Seeing an import error?

Make sure the first line of database.py imports sqlite3 and the second imports SupplierData from models. Both database.py and models.py must be in the same folder.

Help me fix my database.py import error

✔️ Awesome, I've got everything!

Great. Double check you've saved your file before moving on.

ⓧ I'd like to double check the full code

import sqlite3
from models import SupplierData


DB_PATH = "suppliers.db"


def init_database():
    conn = sqlite3.connect(DB_PATH)
    cursor = conn.cursor()
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS suppliers (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            supplier_name TEXT NOT NULL,
            country TEXT,
            product_categories TEXT,
            certifications TEXT,
            unit_price_usd REAL,
            moq INTEGER,
            lead_time_days INTEGER,
            production_capacity_units_per_month INTEGER,
            delivery_cost_usd REAL,
            sustainability_rating TEXT,
            quality_score REAL,
            notes TEXT,
            source_file TEXT,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    """)
    conn.commit()
    conn.close()
    print(f"Database initialized: {DB_PATH}")


def insert_supplier(supplier: SupplierData, source_file: str = ""):
    conn = sqlite3.connect(DB_PATH)
    cursor = conn.cursor()
    cursor.execute(
        "INSERT INTO suppliers (supplier_name, country, product_categories, certifications, unit_price_usd, moq, lead_time_days, production_capacity_units_per_month, delivery_cost_usd, sustainability_rating, quality_score, notes, source_file) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
        (supplier.supplier_name, supplier.country, ",".join(supplier.product_categories), ",".join(supplier.certifications), supplier.unit_price_usd, supplier.moq, supplier.lead_time_days, supplier.production_capacity_units_per_month, supplier.delivery_cost_usd, supplier.sustainability_rating, supplier.quality_score, supplier.notes, source_file),
    )
    conn.commit()
    conn.close()


def insert_all_suppliers(suppliers: list[SupplierData], filenames: list[str]):
    for supplier, filename in zip(suppliers, filenames):
        insert_supplier(supplier, source_file=filename)
    print(f"  Inserted {len(suppliers)} suppliers into database")

Run the full pipeline to populate the database

Every module in your pipeline is now ready: document processing, AI extraction, and database storage. Time to wire them together and run the full sequence.

• Run the full pipeline by executing this command in your terminal:

python -c "
from create_sample_docs import create_sample_documents
from processor import process_all_documents
from extractor import extract_all_suppliers
from database import init_database, insert_all_suppliers

print('Creating sample documents...')
create_sample_documents()

print('\nProcessing documents...')
documents = process_all_documents('supplier_docs')

print('\nExtracting supplier data with Claude Haiku...')
suppliers = extract_all_suppliers(documents)

print('\nInitializing database...')
init_database()

print('\nInserting suppliers...')
insert_all_suppliers(suppliers, list(documents.keys()))

print(f'\nDone! {len(suppliers)} suppliers stored in suppliers.db')
"

What does this command do?

This runs a mini pipeline that ties all your modules together. It creates the sample documents, processes them into text, sends each document through Claude Haiku for AI extraction, initializes the database, and inserts every extracted supplier record.

You'll build a proper CLI for this in the next steps. For now, this verifies that all the pieces work together.

You should see output showing each document being processed, each supplier being extracted with their name and country, and a final message confirming 5 suppliers were inserted into the database.

• Verify the database was populated by running this query:

python -c "
import sqlite3
conn = sqlite3.connect('suppliers.db')
cursor = conn.cursor()
cursor.execute('SELECT supplier_name, country, unit_price_usd FROM suppliers')
for row in cursor.fetchall():
    print(row)
conn.close()
"

You should see 5 rows, one for each supplier: FreshPack Solutions (UK), GreenLeaf Ingredients (Germany), AsiaSpice Trading (Thailand), NordicPure AB (Sweden), and MediterraneanFoods SRL (Italy).

Seeing fewer than 5 suppliers?

If the extraction step shows errors, your ANTHROPIC_API_KEY environment variable may not be set in your current terminal session. Re-export it and try again.

If you see a "table already exists" message but the data is duplicated, delete suppliers.db and re-run the pipeline. The CREATE TABLE IF NOT EXISTS clause only prevents table creation errors, not duplicate inserts.

Help me debug my supplier pipeline run

Your supplier data is now persisted in a queryable database. Next up, you'll build a scoring engine that ranks these suppliers by configurable criteria like price, lead time, and certifications.

Build the Scoring and Query Engine

Your database is full of structured supplier data. Now you need a way to answer procurement questions like "which supplier is cheapest?" or "who has BRC certification?"

In this step, you will build a scoring engine that normalizes each supplier attribute to a 0-100 scale, combines them with configurable weights, and produces ranked comparison tables. This turns raw data into actionable intelligence.

In this step, get ready to:

• Build weighted scoring functions that normalize price, lead time, and certifications to comparable 0-100 scales.
• Create filter functions that narrow suppliers by certification, lead time, and country.
• Generate formatted comparison tables that rank suppliers by overall score.

Build configurable weighted scoring functions

Comparing suppliers is tricky because their attributes use different units. Price is in dollars, lead time is in days, and certifications are a count. A scoring engine normalizes everything to the same 0-100 scale so you can combine and rank them fairly.

• Create a new file called scorer.py in your project folder.
• Add the imports and data loading function by pasting this code:

import sqlite3
import pandas as pd
from database import DB_PATH


def load_suppliers_df() -> pd.DataFrame:
    """Load all suppliers from the database into a pandas DataFrame."""
    conn = sqlite3.connect(DB_PATH)
    df = pd.read_sql_query("SELECT * FROM suppliers", conn)
    conn.close()

    # Split comma-separated strings back into lists for filtering
    df["certifications_list"] = df["certifications"].apply(
        lambda x: [c.strip() for c in x.split(",") if c.strip()] if x else []
    )
    df["categories_list"] = df["product_categories"].apply(
        lambda x: [c.strip() for c in x.split(",") if c.strip()] if x else []
    )
    return df

What does this code do?

The load_suppliers_df() function reads all supplier records from your SQLite database into a pandas DataFrame. DataFrames make it easy to sort, filter, and compute scores across all suppliers at once.

The two apply() lines convert the comma-separated certification and category strings back into Python lists. This makes it possible to filter by individual certifications later.

• Below your load_suppliers_df() function, add the first scoring function:

def score_price(df: pd.DataFrame) -> pd.Series:
    """Score suppliers by price (lower is better). Returns 0-100 score."""
    prices = df["unit_price_usd"]
    if prices.isna().all():
        return pd.Series(0, index=df.index)
    min_price, max_price = prices.min(), prices.max()
    if max_price == min_price:
        return pd.Series(100, index=df.index)
    # Invert so cheapest gets 100, most expensive gets 0
    return (100 * (1 - (prices - min_price) / (max_price - min_price))).fillna(0)

What is min-max normalization?

Min-max normalization maps a range of values to a 0-100 scale. The formula (value - min) / (max - min) produces a number between 0 and 1. Multiplying by 100 gives you a percentage.

For price, the formula is inverted with 1 - ... so that the cheapest supplier scores 100 (best) and the most expensive scores 0. The edge cases handle missing data and all-same-price scenarios.

• Add the lead time and certification scoring functions below score_price():

def score_lead_time(df: pd.DataFrame) -> pd.Series:
    """Score suppliers by lead time (shorter is better). Returns 0-100 score."""
    lead_times = df["lead_time_days"]
    if lead_times.isna().all():
        return pd.Series(0, index=df.index)
    min_lt, max_lt = lead_times.min(), lead_times.max()
    if max_lt == min_lt:
        return pd.Series(100, index=df.index)
    # Shorter lead time = higher score
    return (100 * (1 - (lead_times - min_lt) / (max_lt - min_lt))).fillna(0)


def score_certifications(df: pd.DataFrame) -> pd.Series:
    """Score by number of certifications. More is better."""
    counts = df["certifications_list"].apply(len)
    max_count = counts.max()
    if max_count == 0:
        return pd.Series(0, index=df.index)
    # Supplier with most certs gets 100, others proportional
    return (counts / max_count) * 100

Why two different scoring approaches?

The score_lead_time() function uses the same inverted normalization as price because shorter delivery is better. The score_certifications() function uses a simpler ratio because certifications are a count. The supplier with the most certifications gets 100, others are scored proportionally.

• Now add the overall scoring function that combines all individual scores with configurable weights:

def calculate_overall_score(
    df: pd.DataFrame,
    weight_price: float = 0.3,
    weight_lead_time: float = 0.25,
    weight_certifications: float = 0.25,
    weight_quality: float = 0.2,
) -> pd.DataFrame:
    """Calculate weighted overall score for all suppliers."""
    result = df.copy()
    result["price_score"] = score_price(df)
    result["lead_time_score"] = score_lead_time(df)
    result["certification_score"] = score_certifications(df)
    # Use quality_score from DB, default 50 for missing values
    result["quality_adj_score"] = df["quality_score"].fillna(50)

    # Weighted combination of all scores
    result["overall_score"] = (
        result["price_score"] * weight_price
        + result["lead_time_score"] * weight_lead_time
        + result["certification_score"] * weight_certifications
        + result["quality_adj_score"] * weight_quality
    )

    return result.sort_values("overall_score", ascending=False)

What do the weights mean?

The default weights (price 0.3, lead time 0.25, certifications 0.25, quality 0.2) total 1.0. Price gets the highest weight because cost is typically the primary driver in procurement decisions.

Because these are function parameters, you can easily adjust them for different scenarios. A food safety team might set weight_certifications=0.5 to prioritize certified suppliers.

• Save scorer.py.

Seeing an indentation error?

Make sure each function starts at the top level (no indentation) and all lines within a function are indented consistently with 4 spaces.

help me fix an indentation error in my scorer.py file

Create query functions that filter suppliers by criteria

Scoring ranks all suppliers, but procurement teams often need to filter first. You might only want BRC-certified suppliers, or only those who can deliver within 21 days.

• Add three filter functions below calculate_overall_score() in scorer.py:

def filter_by_certification(df: pd.DataFrame, cert: str) -> pd.DataFrame:
    """Filter suppliers that hold a specific certification."""
    mask = df["certifications_list"].apply(
        lambda certs: any(cert.upper() in c.upper() for c in certs)
    )
    return df[mask]


def filter_by_max_lead_time(df: pd.DataFrame, max_days: int) -> pd.DataFrame:
    """Filter suppliers with lead time at or below max_days."""
    return df[df["lead_time_days"].notna() & (df["lead_time_days"] <= max_days)]


def filter_by_country(df: pd.DataFrame, country: str) -> pd.DataFrame:
    """Filter suppliers by country."""
    return df[df["country"].str.contains(country, case=False, na=False)]

What does this code do?

• filter_by_certification() does a case-insensitive search through each supplier's certification list. Searching for "BRC" will match "BRC", "brc", or "BRC Global Standard".
• filter_by_max_lead_time() keeps only suppliers whose lead time is at or below the threshold. It also filters out suppliers with missing lead time data.
• filter_by_country() uses a partial, case-insensitive match so "uk" finds "UK" or "United Kingdom".

• Save scorer.py.

Generate formatted comparison tables with ranked output

The final piece is a display function that formats the scored DataFrame into a readable comparison table. This gives you a clean output when running queries from the terminal.

• Add the display function at the bottom of scorer.py:

def display_comparison_table(df: pd.DataFrame, title: str = "Supplier Comparison"):
    """Print a formatted comparison table."""
    display_cols = [
        "supplier_name",
        "country",
        "unit_price_usd",
        "lead_time_days",
        "certifications",
        "moq",
        "overall_score",
    ]
    # Only show columns that exist in the DataFrame
    available_cols = [c for c in display_cols if c in df.columns]
    print(f"\n{'='*60}")
    print(f"  {title}")
    print(f"{'='*60}")
    print(df[available_cols].to_string(index=False))
    print(f"{'='*60}\n")

What does this code do?

The function selects a subset of columns that matter most for procurement decisions (name, country, price, lead time, certifications, MOQ, and overall score). It only displays columns that actually exist in the DataFrame, which prevents errors when calling it on filtered results that may not have scoring columns.

• Save scorer.py.
• Test your scoring engine by running this command:

python -c "from scorer import load_suppliers_df, calculate_overall_score, display_comparison_table; df = load_suppliers_df(); scored = calculate_overall_score(df); display_comparison_table(scored, 'Overall Supplier Ranking')"

You should see a formatted table with all 5 suppliers ranked by their overall score, with columns for name, country, price, lead time, certifications, MOQ, and overall score.

Seeing an import error or empty table?

• If you see ModuleNotFoundError, make sure you are running the command from the same folder where scorer.py and database.py live.
• If the table is empty, confirm that suppliers.db exists in your project folder. You populated it in the previous step.
• If you see a KeyError, check that the column names in your scoring functions match the database schema exactly (e.g. unit_price_usd, not price_usd).

help me debug my scorer.py output

✔️ Awesome, I've got everything!

Great. Double check you have saved scorer.py and that the test command printed a ranked table.

ⓧ I'd like to double check the full code

import sqlite3
import pandas as pd
from database import DB_PATH


def load_suppliers_df() -> pd.DataFrame:
    """Load all suppliers from the database into a pandas DataFrame."""
    conn = sqlite3.connect(DB_PATH)
    df = pd.read_sql_query("SELECT * FROM suppliers", conn)
    conn.close()
    df["certifications_list"] = df["certifications"].apply(
        lambda x: [c.strip() for c in x.split(",") if c.strip()] if x else []
    )
    df["categories_list"] = df["product_categories"].apply(
        lambda x: [c.strip() for c in x.split(",") if c.strip()] if x else []
    )
    return df


def score_price(df: pd.DataFrame) -> pd.Series:
    """Score suppliers by price (lower is better). Returns 0-100 score."""
    prices = df["unit_price_usd"]
    if prices.isna().all():
        return pd.Series(0, index=df.index)
    min_price = prices.min()
    max_price = prices.max()
    if max_price == min_price:
        return pd.Series(100, index=df.index)
    score = 100 * (1 - (prices - min_price) / (max_price - min_price))
    return score.fillna(0)


def score_lead_time(df: pd.DataFrame) -> pd.Series:
    """Score suppliers by lead time (shorter is better). Returns 0-100 score."""
    lead_times = df["lead_time_days"]
    if lead_times.isna().all():
        return pd.Series(0, index=df.index)
    min_lt = lead_times.min()
    max_lt = lead_times.max()
    if max_lt == min_lt:
        return pd.Series(100, index=df.index)
    score = 100 * (1 - (lead_times - min_lt) / (max_lt - min_lt))
    return score.fillna(0)


def score_certifications(df: pd.DataFrame) -> pd.Series:
    """Score by number of certifications. More is better."""
    counts = df["certifications_list"].apply(len)
    max_count = counts.max()
    if max_count == 0:
        return pd.Series(0, index=df.index)
    return (counts / max_count) * 100


def calculate_overall_score(
    df: pd.DataFrame,
    weight_price: float = 0.3,
    weight_lead_time: float = 0.25,
    weight_certifications: float = 0.25,
    weight_quality: float = 0.2,
) -> pd.DataFrame:
    """Calculate weighted overall score for all suppliers."""
    result = df.copy()
    result["price_score"] = score_price(df)
    result["lead_time_score"] = score_lead_time(df)
    result["certification_score"] = score_certifications(df)
    result["quality_adj_score"] = df["quality_score"].fillna(50)

    result["overall_score"] = (
        result["price_score"] * weight_price
        + result["lead_time_score"] * weight_lead_time
        + result["certification_score"] * weight_certifications
        + result["quality_adj_score"] * weight_quality
    )

    return result.sort_values("overall_score", ascending=False)


def filter_by_certification(df: pd.DataFrame, cert: str) -> pd.DataFrame:
    """Filter suppliers that hold a specific certification."""
    mask = df["certifications_list"].apply(
        lambda certs: any(cert.upper() in c.upper() for c in certs)
    )
    return df[mask]


def filter_by_max_lead_time(df: pd.DataFrame, max_days: int) -> pd.DataFrame:
    """Filter suppliers with lead time at or below max_days."""
    return df[df["lead_time_days"].notna() & (df["lead_time_days"] <= max_days)]


def filter_by_country(df: pd.DataFrame, country: str) -> pd.DataFrame:
    """Filter suppliers by country."""
    return df[df["country"].str.contains(country, case=False, na=False)]


def display_comparison_table(df: pd.DataFrame, title: str = "Supplier Comparison"):
    """Print a formatted comparison table."""
    display_cols = [
        "supplier_name",
        "country",
        "unit_price_usd",
        "lead_time_days",
        "certifications",
        "moq",
        "overall_score",
    ]
    available_cols = [c for c in display_cols if c in df.columns]
    print(f"\n{'='*60}")
    print(f"  {title}")
    print(f"{'='*60}")
    print(df[available_cols].to_string(index=False))
    print(f"{'='*60}\n")

Your scoring engine is ready. You can now rank suppliers, filter by any criteria, and display clean comparison tables. Next up, you will wire everything together into a CLI that runs the full pipeline from document ingestion to query results.

Wire Up the CLI and Run the Full Pipeline

You now have every piece of the supplier evaluation system built and tested individually. The document processor reads files, the AI extractor pulls structured data, the database stores it, and the scorer ranks everything.

In this final step, you will create a single entry point that orchestrates the entire pipeline from start to finish. One command will ingest all documents and populate the database. Another will run procurement queries and display ranked comparison tables.

In this step, get ready to:

• Create a main.py CLI that orchestrates ingestion and querying.
• Run the complete pipeline end-to-end on all sample documents.
• Execute sample procurement queries and view ranked supplier tables.

Create the main.py CLI entry point

A CLI (command-line interface) gives your project a clean way to run different tasks from the terminal. Instead of manually calling individual modules, you pass a command like ingest or query and the CLI routes to the right function.

• Create a new file called main.py in your project folder.
• Add the imports and the run_pipeline() function by pasting the following code:

import sys
from processor import process_all_documents
from extractor import extract_all_suppliers
from database import init_database, insert_all_suppliers
from scorer import load_suppliers_df, calculate_overall_score, filter_by_certification, filter_by_max_lead_time, display_comparison_table
from create_sample_docs import create_sample_documents


def run_pipeline():
    """Run the full supplier evaluation pipeline."""
    print("\n" + "=" * 60)
    print("  SUPPLIER EVALUATION SYSTEM")
    print("=" * 60)

    # Step 1: Create sample documents
    print("\n[1/4] Creating sample supplier documents...")
    create_sample_documents()

    # Step 2: Process documents into text
    print("\n[2/4] Processing documents...")
    documents = process_all_documents("supplier_docs")
    print(f"  Total documents processed: {len(documents)}")

    # Step 3: Extract structured data using AI
    print("\n[3/4] Extracting supplier data with Claude Haiku...")
    suppliers = extract_all_suppliers(documents)
    print(f"  Total suppliers extracted: {len(suppliers)}")

    # Step 4: Store in database
    print("\n[4/4] Storing in database...")
    init_database()
    insert_all_suppliers(suppliers, list(documents.keys()))

    print("\n Pipeline complete! Database populated.")
    print("  Run queries with: python main.py query")

What does this code do?

The imports pull in every module you have built so far. Each one handles a specific stage of the pipeline.

The run_pipeline() function orchestrates all four stages in order: create sample documents, process them into text, extract structured data with Claude, and store everything in SQLite. Progress messages print at each stage so you can follow along.

• Add the run_queries() function below run_pipeline() by pasting this code:

def run_queries():
    """Run sample procurement queries."""
    print("\n" + "=" * 60)
    print("  SUPPLIER QUERIES")
    print("=" * 60)

    # Load all suppliers from the database
    df = load_suppliers_df()
    if df.empty:
        print("  No suppliers in database. Run: python main.py ingest")
        return

    # Overall ranking with weighted scores
    scored = calculate_overall_score(df)
    display_comparison_table(scored, "Overall Supplier Ranking")

    # Sort by cheapest price
    cheapest = scored.sort_values("unit_price_usd", ascending=True)
    display_comparison_table(cheapest, "Cheapest Suppliers")

    # Filter for BRC certified suppliers
    brc = filter_by_certification(scored, "BRC")
    if not brc.empty:
        display_comparison_table(brc, "BRC Certified Suppliers")
    else:
        print("  No BRC certified suppliers found.")

    # Filter for fast delivery (21 days or less)
    fast = filter_by_max_lead_time(scored, 21)
    if not fast.empty:
        display_comparison_table(fast, "Suppliers with Lead Time <= 21 Days")
    else:
        print("  No suppliers with lead time under 21 days found.")

What does this code do?

The run_queries() function loads supplier data from the database and runs four different queries: overall weighted ranking, cheapest-first sorting, BRC certification filtering, and lead time filtering (21 days or less).

Each query produces a formatted comparison table. If no suppliers match a filter, a helpful message prints instead of an empty table.

• Add the CLI argument parsing at the bottom of main.py by pasting this code:

if __name__ == "__main__":
    # Parse the command from terminal arguments
    if len(sys.argv) < 2:
        print("Usage:")
        print("  python main.py ingest   - Run full pipeline")
        print("  python main.py query    - Run procurement queries")
        sys.exit(1)

    command = sys.argv[1].lower()

    if command == "ingest":
        run_pipeline()
    elif command == "query":
        run_queries()
    else:
        print(f"Unknown command: {command}")
        print("Use 'ingest' or 'query'")

What does this code do?

The if __name__ == "__main__": block runs only when you execute the file directly from the terminal. It reads sys.argv[1] to determine which command the user typed.

If no command is provided, it prints usage instructions and exits. This pattern is standard for Python CLI tools.

• Save main.py.
• Verify the CLI shows usage by running this command with no arguments:

python main.py

You should see the usage text listing ingest and query as available commands.

Seeing a ModuleNotFoundError?

Make sure your virtual environment is still active. You should see the environment name in your terminal prompt.

Check that main.py is in the same folder as your other module files (processor.py, extractor.py, etc.).

Why is my main.py throwing an import error when I run it?

✔️ Awesome, I've got everything!

Great. Double check you have saved main.py before moving on.

ⓧ I'd like to double check the full code

Here is the complete main.py file for comparison:

import sys
from processor import process_all_documents
from extractor import extract_all_suppliers
from database import init_database, insert_all_suppliers
from scorer import load_suppliers_df, calculate_overall_score, filter_by_certification, filter_by_max_lead_time, display_comparison_table
from create_sample_docs import create_sample_documents


def run_pipeline():
    print("\n" + "=" * 60)
    print("  SUPPLIER EVALUATION SYSTEM")
    print("=" * 60)
    print("\n[1/4] Creating sample supplier documents...")
    create_sample_documents()
    print("\n[2/4] Processing documents...")
    documents = process_all_documents("supplier_docs")
    print(f"  Total documents processed: {len(documents)}")
    print("\n[3/4] Extracting supplier data with Claude Haiku...")
    suppliers = extract_all_suppliers(documents)
    print(f"  Total suppliers extracted: {len(suppliers)}")
    print("\n[4/4] Storing in database...")
    init_database()
    insert_all_suppliers(suppliers, list(documents.keys()))
    print("\n Pipeline complete! Database populated.")
    print("  Run queries with: python main.py query")


def run_queries():
    print("\n" + "=" * 60)
    print("  SUPPLIER QUERIES")
    print("=" * 60)
    df = load_suppliers_df()
    if df.empty:
        print("  No suppliers in database. Run: python main.py ingest")
        return
    scored = calculate_overall_score(df)
    display_comparison_table(scored, "Overall Supplier Ranking")
    cheapest = scored.sort_values("unit_price_usd", ascending=True)
    display_comparison_table(cheapest, "Cheapest Suppliers")
    brc = filter_by_certification(scored, "BRC")
    if not brc.empty:
        display_comparison_table(brc, "BRC Certified Suppliers")
    else:
        print("  No BRC certified suppliers found.")
    fast = filter_by_max_lead_time(scored, 21)
    if not fast.empty:
        display_comparison_table(fast, "Suppliers with Lead Time <= 21 Days")
    else:
        print("  No suppliers with lead time under 21 days found.")


if __name__ == "__main__":
    if len(sys.argv) < 2:
        print("Usage:")
        print("  python main.py ingest   - Run full pipeline")
        print("  python main.py query    - Run procurement queries")
        sys.exit(1)
    command = sys.argv[1].lower()
    if command == "ingest":
        run_pipeline()
    elif command == "query":
        run_queries()
    else:
        print(f"Unknown command: {command}")
        print("Use 'ingest' or 'query'")

Run the full pipeline end-to-end

Your database already has supplier records from the previous step. To demonstrate the full pipeline working from scratch, you will delete the existing database and let the ingest command recreate everything.

• Delete the existing database by running:

rm suppliers.db

• Run the full ingestion pipeline by running this command:

python main.py ingest

You should see output showing all four stages completing: sample documents created, documents processed, supplier data extracted from each file with Claude, and records inserted into the database.

What should I see?

The pipeline prints progress at each stage. You will see document filenames being processed, supplier names being extracted (like FreshPack Solutions, GreenLeaf Ingredients, etc.), and a final confirmation that the database is populated.

The AI extraction step takes a few seconds per document as Claude processes each one. This is normal.

Seeing an authentication error?

Make sure your ANTHROPIC_API_KEY environment variable is still set in this terminal session. If you opened a new terminal, you will need to export it again.

Check that your API key has not expired and still has available credits on your Anthropic account.

Why is my pipeline failing at the AI extraction step?

Execute sample procurement queries

With the database freshly populated, you can now answer real procurement questions. The query command runs multiple analyses in sequence.

• Run the query command to see all supplier comparisons:

python main.py query

You should see four formatted comparison tables printed to your terminal: an overall ranking, cheapest suppliers, BRC certified suppliers, and suppliers with lead time of 21 days or less.

What do these tables tell you?

• Overall Supplier Ranking shows every supplier scored by the weighted formula (30% price, 25% lead time, 25% certifications, 20% quality).
• Cheapest Suppliers sorts purely by unit price, so you can quickly identify the lowest-cost options.
• BRC Certified Suppliers filters to only those with BRC food safety certification.
• Suppliers with Lead Time <= 21 Days shows only suppliers that can deliver within three weeks.

Seeing "No suppliers in database"?

This means the ingest step did not complete successfully. Run python main.py ingest again and check for any error messages during extraction.

Why is my query returning no results even after running ingest?

Your supplier evaluation system is fully operational. You have a single CLI that takes raw documents, extracts intelligence with AI, stores it in a queryable database, and produces actionable procurement insights. Next up, try the Secret Mission to add a smart supplier matching feature.

Secret mission

Add a Supplier Match Feature

Your scoring engine ranks all suppliers, but what about finding the best match for a specific requirement? Build a smart match function that parses natural language requirements like "BRC certified, lead time under 14 days, MOQ under 500" and returns the top suppliers with a per-criterion compatibility breakdown.

Clean Up Your Resources

Clean Up Your Resources

This project runs entirely on your local machine. The only external cost comes from Anthropic API calls, which are pay-per-use and only charged when you actively run the pipeline. There are no running servers or subscriptions that accumulate charges while idle.

Decide whether to keep your resources running, pause them to come back later, or delete them entirely.

Resources you used:

• Project files (main.py, models.py, processor.py, extractor.py, database.py, scorer.py, matcher.py, create_sample_docs.py, requirements.txt).
• supplier_docs/ folder with sample text and Excel files.
• suppliers.db SQLite database.
• Python packages installed via pip (pdfplumber, pandas, openpyxl, anthropic, pydantic).
• ANTHROPIC_API_KEY environment variable.

Keep everything running

No action needed. Choose this if you plan to keep experimenting with different supplier queries or adding new documents to the pipeline.

• Your project files, database, and sample documents stay on your machine.
• Your ANTHROPIC_API_KEY environment variable remains set for future API calls.
• Your Anthropic account stays active. You are only charged when you run the pipeline and make API calls.

Pause - I'll come back to this later

Remove the database and environment variable to free up state, but keep your source code so you can rebuild later.

• Delete the database file by running:

rm suppliers.db

• Remove the ANTHROPIC_API_KEY from your shell profile (~/.zshrc on macOS or ~/.bashrc on Linux) by deleting the export ANTHROPIC_API_KEY=... line.
• On Windows, remove it from System Environment Variables in Settings.
• Your Python source files and supplier_docs/ folder remain intact for when you return.

Delete - I don't want to use this again

Remove all project files, the database, installed packages, and environment variable completely.

• Navigate to the folder that contains your project, then delete the entire project folder.

On macOS/Linux:

cd ~/Desktop
rm -rf supplier-evaluation

On Windows:

cd ~\Desktop
rmdir /s /q supplier-evaluation

Use your actual folder name

Replace supplier-evaluation with whatever you named your project folder on the Desktop.

• Uninstall the Python packages by running:

pip uninstall -y pdfplumber pandas openpyxl anthropic pydantic

• Remove the ANTHROPIC_API_KEY from your shell profile (~/.zshrc on macOS or ~/.bashrc on Linux) by deleting the export ANTHROPIC_API_KEY=... line, then run:

source ~/.zshrc

• On Windows, remove ANTHROPIC_API_KEY from your System Environment Variables through Settings.

Nice Work!

Nice Work!

You just built a working AI-assisted supplier intelligence system that turns messy procurement documents into structured, queryable business data.

You've learned how to:

• Built a document processing pipeline using pdfplumber and pandas that extracts text from PDFs and Excel files into a unified format.
• Used Claude Haiku's structured outputs with Pydantic validation to extract reliable supplier attributes from unstructured text and store them in a SQLite database.
• Created a weighted scoring engine that ranks suppliers by configurable criteria and answers real procurement questions with filterable comparison tables.
• Secret Mission: Built a smart supplier matching feature that parses natural language requirements and returns the top matches with per-criterion compatibility scores.

Ready to quiz yourself?