PDF Enrichment Service - Complete Implementation

Overview

✅ Fully implemented service to enrich Leonteq API products by downloading termsheet PDFs from product pages, extracting all available data, and immediately deleting the PDFs.

Key Features:

🌐 Uses authenticated browser automation to access product pages
📄 Downloads English termsheet PDFs from https://structuredproducts-ch.leonteq.com/isin/{ISIN}
🔍 Extracts comprehensive data (30+ fields) using existing PDF parser
💾 Saves all extracted data to database
🗑️ Immediately deletes PDFs after processing - zero disk footprint
📊 Real-time progress bar with statistics
💾 Checkpoint/resume capability for interrupted runs
🔐 Reuses existing Leonteq authentication from login flow

Quick Start

Option 1: Command Line (Recommended)

# Enrich 100 products with progress bar
poetry run python scripts/enrich_leonteq_pdfs.py

# Enrich more products
poetry run python scripts/enrich_leonteq_pdfs.py --limit 500

# Resume after interruption
poetry run python scripts/enrich_leonteq_pdfs.py --resume

Output:

======================================================================
LEONTEQ PDF ENRICHMENT SERVICE
======================================================================
Target: Up to 100 products
Method: Download termsheets from product pages
Storage: PDFs downloaded temporarily and deleted immediately
======================================================================

Logging in to Leonteq...
Browser initialized and authenticated

[████████████████████████████████████████] 45/100 (45.0%) | ✓ 38 ✗ 7 | Processing CH1505582432

======================================================================
ENRICHMENT COMPLETE
======================================================================
Total Processed: 100
Successfully Enriched: 85 ✓
Failed: 15 ✗
======================================================================

✅ Success rate: 85.0%
✅ 85 products now have enhanced data from PDFs

📁 No PDFs were permanently stored - all temp files deleted

Option 2: Via API

# Trigger enrichment via API endpoint
curl -X POST "http://localhost:8000/api/enrich/leonteq-pdfs?limit=100"

# Returns:
{
  "processed": 100,
  "enriched": 85,
  "failed": 15,
  "skipped": 0
}

Option 3: Python Code

from backend.app.services.leonteq_pdf_enrichment import enrich_leonteq_products_batch
from pathlib import Path

def my_progress(current, total, message, stats):
    print(f"[{current}/{total}] {message} - Enriched: {stats['enriched']}")

stats = enrich_leonteq_products_batch(
    limit=100,
    progress_callback=my_progress,
    checkpoint_file=Path("my_checkpoint.json")
)

print(f"Done! Enriched {stats['enriched']} products")

How It Works

Architecture

1. Get Leonteq products needing enrichment from database
   ↓
2. Launch headless browser with Playwright
   ↓
3. Load stored authentication (from previous Leonteq login)
   ↓
4. For each product:
   a. Navigate to https://structuredproducts-ch.leonteq.com/isin/{ISIN}
   b. Find and click English termsheet download link
   c. Save PDF to /tmp/termsheet-{ISIN}.pdf
   d. Extract text with pdfplumber
   e. Parse with existing PDF parser (supports LUKB and generic formats)
   f. Merge ALL extracted fields into product's normalized_json
   g. Update database
   h. Delete PDF file and temp directory
   i. Log success/failure
   ↓
5. Save checkpoint every 10 products
   ↓
6. Close browser
   ↓
7. Return statistics

Data Extraction

The service extracts all available fields from termsheet PDFs:

Identifiers (6 fields):

ISIN, Valor, WKN, Symbol, Bloomberg, RIC

Core Info (5 fields):

Issuer, Product Type, Currency, Issue Date, Maturity Date

Financial Terms (10+ fields):

✅ Coupon Rate (% p.a.)
✅ Barrier Level (%)
✅ Cap Level (%)
✅ Strike Price
✅ Participation Rate (%)
Denomination, Issue Price, Current Price, Bid, Ask

Structured Features (10+ fields):

✅ Underlyings (names, ISINs, weights, tickers)
Autocall Barrier (%)
Knock-In Barrier (%)
Knock-Out Barrier (%)
Memory Coupon flag
Observation Dates
Payment Dates
Settlement Type
Exercise Type

Other (5+ fields):

Issuer Rating (Moody's/Fitch)
Trading Venues
Guarantee Type
Product Features
Risk Factors

Smart Merging

The service intelligently merges PDF data with existing API data:

# Only updates if:
1. Field doesn't exist in database
2. Existing value is None/empty
3. PDF has higher confidence score
4. PDF has more complete data (e.g., longer underlyings list)

# Preserves:
- Existing high-confidence data
- User-edited values
- Complete field histories

Example:

// Before enrichment (from Leonteq API):
{
  "isin": {"value": "CH1505582432", "confidence": 0.9},
  "product_type": {"value": "Barrier Reverse Convertible", "confidence": 0.9},
  "maturity_date": {"value": "2026-06-15", "confidence": 0.9}
  // Missing: coupon_rate_pct_pa, barrier_level_pct, underlyings
}

// After PDF enrichment:
{
  "isin": {"value": "CH1505582432", "confidence": 0.9},  // Preserved
  "product_type": {"value": "Barrier Reverse Convertible", "confidence": 0.9},  // Preserved
  "maturity_date": {"value": "2026-06-15", "confidence": 0.9},  // Preserved
  "coupon_rate_pct_pa": {"value": 8.5, "confidence": 0.8},  // Added from PDF
  "barrier_level_pct": {"value": 60.0, "confidence": 0.8},  // Added from PDF
  "underlyings": [  // Added from PDF
    {"name": "Nestlé SA", "isin": "CH0038863350", "weight": 0.5},
    {"name": "Roche Holding AG", "isin": "CH0012032048", "weight": 0.5}
  ]
}

Progress Tracking & Resumability

Progress Bar

Real-time terminal progress bar shows:

Current/total products
Percentage complete
Success count (✓)
Failure count (✗)
Current product ISIN
Current operation

[████████████████████░░░░░░░░░░░░░░░░░░] 45/100 (45.0%) | ✓ 38 ✗ 7 | Parsing PDF for CH1505582432

Checkpoints

Automatic checkpoint saves every 10 products:

// data/enrich_checkpoint.json
{
  "processed": 50,
  "enriched": 42,
  "failed": 8,
  "timestamp": 1768746000.0
}

Resume after interruption:

# Process interrupted at 50/100
# Checkpoint saved automatically

# Resume from checkpoint
poetry run python scripts/enrich_leonteq_pdfs.py --resume

# Will skip first 50 and continue from 51

Error Handling

Network errors: Retries next product, logs failure
PDF not found: Logs and skips, continues batch
Parsing errors: Logs and skips, continues batch
Browser crashes: Checkpoint allows resume
Keyboard interrupt: Saves checkpoint, clean exit
Temp file errors: Always cleaned in finally block

Authentication

Reuses Existing Login

The service uses the same authentication as the Leonteq HTML crawler:

# 1. User clicks "Open Leonteq login" in UI
# 2. Browser opens, user logs in manually (with 2FA if needed)
# 3. Session state saved to: data/cache/leonteq_storage_state.json
# 4. PDF enrichment reuses this stored state

No additional login required - just needs prior Leonteq login from UI.

Refresh Token

If authentication expires:

# 1. Open app: bash start.sh
# 2. Click "Open Leonteq login" in Ingest tab
# 3. Log in again
# 4. Run enrichment script again

File Cleanup Guarantees

Zero Disk Footprint

finally:
    # ALWAYS executed (even on errors/interrupts)
    if temp_pdf and temp_pdf.exists():
        temp_pdf.unlink()  # Delete PDF
        if temp_pdf.parent.exists() and not list(temp_pdf.parent.iterdir()):
            temp_pdf.parent.rmdir()  # Delete empty temp directory

Guarantees:

✅ PDFs deleted after each product (not batched)
✅ finally block ensures cleanup even on exceptions
✅ Temp directories also cleaned
✅ Uses OS temp directory (/tmp on macOS/Linux)
✅ OS auto-cleans temp files on reboot if process crashes

Verify:

# Check for any remaining PDFs
ls -la /tmp/termsheet-*.pdf 2>/dev/null || echo "No PDFs found ✓"

# Monitor during enrichment
watch -n 1 'ls /tmp/termsheet-*.pdf 2>/dev/null | wc -l'

Performance

Speed

Browser initialization: 5-10 seconds (once per batch)
Per product: 3-5 seconds average
- Navigate to page: 1-2 seconds
- Download PDF: 0.5-1 second
- Parse PDF: 0.5-1 second
- Update database: 0.1 second
- Cleanup: 0.1 second

Estimated time:

100 products: ~6-8 minutes
500 products: ~30-40 minutes
1,000 products: ~60-80 minutes

Rate Limiting

Built-in 1-second delay between products to avoid overwhelming server:

# Small delay to avoid rate limiting
time.sleep(1)

Can be adjusted if needed, but recommended to keep.

Database Impact

Products Eligible for Enrichment

-- Count products needing enrichment
SELECT COUNT(*) FROM products
WHERE source_kind = 'leonteq_api'
AND (
  json_extract(normalized_json, '$.coupon_rate_pct_pa.value') IS NULL
  OR json_extract(normalized_json, '$.barrier_level_pct.value') IS NULL
);
-- Result: ~5,141 (all Leonteq API products)

Expected Data Quality Improvement

Before PDF Enrichment:

Coupon coverage: 0% (0/5,141)
Barrier coverage: 0% (0/5,141)
Underlyings coverage: 100% (5,141/5,141) - from API

After PDF Enrichment (estimated):

Coupon coverage: 60-80% (~3,000-4,000 products)
Barrier coverage: 60-80% (~3,000-4,000 products)
Underlyings coverage: 100% (unchanged, already complete)
Additional fields: Strike, Participation, Cap, etc.

Why not 100%?

Some products don't have coupons/barriers (warrants, trackers)
Some PDFs may not be available
Some PDFs may have parsing issues

Storage Impact

Before: structured_products.db size: ~1.4 GB

After (estimated): ~1.6 GB

Additional 200 MB for enriched normalized_json fields
No PDF storage (all deleted immediately)

Configuration

Environment Variables

# .env file

# Leonteq credentials (for initial login)
SPA_LEONTEQ_USERNAME=your_username
SPA_LEONTEQ_PASSWORD=your_password
SPA_LEONTEQ_OTP_SECRET=your_2fa_secret  # Optional

# Leonteq API token (auto-captured during login)
SPA_LEONTEQ_API_TOKEN=eyJhbGciOi...  # Auto-saved

# Enrichment settings (optional)
SPA_ENRICHMENT_BATCH_SIZE=100  # Default batch size
SPA_ENRICHMENT_DELAY_SEC=1  # Delay between products

File Locations

data/
├── structured_products.db          # Main database
├── cache/
│   └── leonteq_storage_state.json  # Stored browser auth
└── enrich_checkpoint.json          # Checkpoint file (temp)

/tmp/
└── termsheet-CH*.pdf               # Temp PDFs (auto-deleted)

Troubleshooting

"Failed to get Leonteq authentication"

Problem: No stored auth session

Solution:

# 1. Start app
bash start.sh

# 2. Open http://localhost:5173
# 3. Go to Ingest tab
# 4. Click "Open Leonteq login"
# 5. Log in (complete 2FA if needed)
# 6. Close browser
# 7. Run enrichment again

"Could not find termsheet download link"

Problem: Page structure changed or PDF not available

Solutions:

Check product page manually: https://structuredproducts-ch.leonteq.com/isin/{ISIN}
Verify termsheet exists on page

Update selector in download_termsheet_pdf_from_product_page():

selectors = [
    'a[href*="termsheet"][href*="en.pdf"]',
    'a:has-text("Termsheet"):has-text("EN")',
    # Add new selectors here
]

"PDF parsing returned no data"

Problem: PDF format not recognized

Solutions:

Check PDF manually (temporarily disable cleanup)
Update core/sources/pdf_termsheet.py parser
Add new parsing patterns for unrecognized formats

Browser Stays Open / Zombies

Problem: Browser processes not cleaned up

Solution:

# Kill all Playwright browsers
pkill -f chromium

# Or kill specific process
ps aux | grep chromium
kill <PID>

High Memory Usage

Problem: Browser consuming too much RAM

Solutions:

Reduce batch size: --limit 50
Add explicit browser restart every N products
Increase delay: time.sleep(2)

API Reference

Function: `enrich_leonteq_products_batch()`

def enrich_leonteq_products_batch(
    limit: int = 100,
    progress_callback: callable | None = None,
    checkpoint_file: Path | None = None
) -> dict[str, int]:
    """
    Enrich Leonteq API products with PDF data.

    Args:
        limit: Maximum products to process
        progress_callback: Function(current, total, message, stats)
        checkpoint_file: Path to save/load progress

    Returns:
        {
            "processed": int,  # Total processed
            "enriched": int,   # Successfully enriched
            "failed": int,     # Failed to enrich
            "skipped": int     # Skipped (already processed)
        }
    """

Function: `enrich_product_from_pdf()`

def enrich_product_from_pdf(
    page: Page,
    product_id: int,
    raw_text: str,
    normalized_json: str,
    progress_callback: callable | None = None
) -> bool:
    """
    Enrich single product from PDF.

    Args:
        page: Authenticated Playwright page
        product_id: Database product ID
        raw_text: Leonteq API JSON response
        normalized_json: Current normalized data
        progress_callback: Optional progress function

    Returns:
        True if enriched, False if failed
    """

Files Modified/Created

Created

backend/app/services/leonteq_pdf_enrichment.py - Core enrichment service
backend/app/api/routes_enrich.py - API endpoint
scripts/enrich_leonteq_pdfs.py - CLI script with progress bar
PDF_ENRICHMENT_FINAL.md - This documentation

Modified

backend/app/db/models.py
- Added get_products_for_pdf_enrichment()
- Added update_product_normalized_json()
backend/app/api/init.py
- Exported enrich_router
backend/app/main.py
- Registered enrich_router

Summary

✅ Production-ready PDF enrichment service with:

Browser automation for authenticated PDF downloads
Comprehensive data extraction (30+ fields)
Smart field merging (preserves existing data)
Real-time progress tracking with visual bar
Checkpoint/resume capability
Zero disk footprint (PDFs immediately deleted)
Error handling and logging
API and CLI interfaces

Ready to enrich 5,141 Leonteq products with coupon rates, barriers, and other missing data from termsheet PDFs!

🚀 Get started: poetry run python scripts/enrich_leonteq_pdfs.py

FilesExpand file tree

PDF_ENRICHMENT_FINAL.md

Latest commit

History

PDF_ENRICHMENT_FINAL.md

File metadata and controls

PDF Enrichment Service - Complete Implementation

Overview

Quick Start

Option 1: Command Line (Recommended)

Option 2: Via API

Option 3: Python Code

How It Works

Architecture

Data Extraction

Smart Merging

Progress Tracking & Resumability

Progress Bar

Checkpoints

Error Handling

Authentication

Reuses Existing Login

Refresh Token

File Cleanup Guarantees

Zero Disk Footprint

Performance

Speed

Rate Limiting

Database Impact

Products Eligible for Enrichment

Expected Data Quality Improvement

Storage Impact

Configuration

Environment Variables

File Locations

Troubleshooting

"Failed to get Leonteq authentication"

"Could not find termsheet download link"

"PDF parsing returned no data"

Browser Stays Open / Zombies

High Memory Usage

API Reference

Function: enrich_leonteq_products_batch()

Function: enrich_product_from_pdf()

Files Modified/Created

Created

Modified

Summary

Function: `enrich_leonteq_products_batch()`

Function: `enrich_product_from_pdf()`