/thermoextract - AI-Powered Thermochronology Data Extraction

# /thermoextract - AI-Powered Thermochronology Data Extraction

**Purpose:** Extract thermochronology data from research papers using pdfplumber + AI analysis, validate against Kohn et al. (2024) standards, and import to database.

**Key Innovation:** Iterative AI-guided extraction with pdfplumber for reliable text extraction + AI structure understanding + validation loop

---

## ✅ SCHEMA: EarthBank camelCase (IDEA-014 Complete)

**Database Schema:** EarthBank-native camelCase schema
- **Tables:** `earthbank_samples`, `earthbank_ftDatapoints`, `earthbank_heDatapoints`, etc.
- **Fields:** camelCase naming (e.g., `sampleID`, `centralAgeMa`, `pooledAgeMa`)
- **Primary Keys:** UUID with `uuid_generate_v4()`
- **Foreign Keys:** String-based (e.g., `sampleID`, `datapointName`)

**Extraction Output:** This command should now output camelCase CSVs matching EarthBank schema
- Old: `sample_id`, `central_age_ma`, `pooled_age_ma`
- New: `sampleID`, `centralAgeMa`, `pooledAgeMa`

**Migration Status:** Phase 6 complete (2025-11-18)
**See:** `build-data/ideas/debug/IDEA-014-INDEX.md` for migration details

---

## Workflow Overview

0.1. Detect supplementary DOIs/URLs → Parse paper for data availability statements 0.2. Resolve DOIs → Convert DOIs to download URLs 0.3. Download supplementary files → Auto-download with retry (OSF, Zenodo, etc.) 0.4. Validate downloads → Check file integrity and contents 0.5. Check for local supplementary data → Use downloaded/existing files (PRIORITY)

1. Read paper-index.md → Get table locations & page numbers
2. Extract PDF pages → Isolate individual table pages (SKIP if using supplementary)
3. pdfplumber text extraction → Get raw text from each page
4. AI structure analysis → Understand headers & data patterns
5. Create extraction plan → Bespoke CSV generation strategy
6. Extract to CSV → Generate structured data file (ALL columns, ALL tables)
7. Validate extraction → Check column count, empty columns, data ranges
8. Retry loop → Delete & retry until perfect (MAX 3 attempts per table)
9. Compare to Kohn 2024 → Check required fields
10. Calculate FAIR score → Rate completeness (0-100)
11. Transform to EarthBank → Map to camelCase schema (1:1 mapping)
12. Import to database → Load into earthbank_* tables
13. Generate SQL metadata → Create dataset tracking scripts

---

## 🚨 CRITICAL EXTRACTION RULES

**BEFORE starting extraction, understand these NON-NEGOTIABLE requirements:**

### 1. Extract ALL Tables (No Exceptions)

**Rule:** EVERY table marked "extractable: YES" in paper-index.md MUST be extracted

- ❌ **WRONG:** "Extract Table 1 and Table 2, skip Table A1/A2/A3 for later"
- ✅ **CORRECT:** "Extract ALL tables (Table 1, 2, A1, A2, A3) before marking extraction complete"

**Why:** 50-70% of data is in "supplementary" appendix tables (A1, A2, A3) - skipping these loses:
- Grain-by-grain ages (Table A1)
- Individual track length measurements (Table A2)
- Reference material QC data (Table A3)

**Priority** determines extraction ORDER (HIGH first, MEDIUM second), NOT whether to extract

### 2. Extract ALL Columns (No Partial Extraction)

**Rule:** EVERY column in the source table MUST appear in the extracted CSV

- ❌ **WRONG:** Extract 12/24 columns and mark table "complete"
- ✅ **CORRECT:** Extract ≥90% of columns, validate with Step 7.1, retry if <90%

**Why:** Missing columns = lost data. Examples of commonly missed columns:
- rmr0D (kinetic parameter)
- Cl wt% (chlorine content)
- eCl apfu (effective chlorine)
- Analyst names, dates, batch IDs

**Validation enforces this:** Step 7.1 fails extraction if column count <90% of source

### 3. Validate Before Proceeding (Mandatory)

**Rule:** NO table is "complete" until ALL validation checks pass (Steps 7.1-7.4)

- ❌ **WRONG:** Extract CSV → move to next table
- ✅ **CORRECT:** Extract CSV → validate (column count, empty cols, ranges) → retry if fails → THEN next table

**Why:** Without validation:
- Empty columns go undetected (parsing failures)
- Values in wrong columns (age data in U column, etc.)
- Missing rows/samples

**Auto-retry:** If validation fails, Step 8 automatically retries up to 3 times

### 4. Download Supplementary Data First (Steps 0.1-0.4)

**Rule:** ALWAYS attempt to download supplementary data from DOIs/URLs before PDF extraction

- ❌ **WRONG:** See no `supplementary/` directory → proceed with PDF extraction
- ✅ **CORRECT:** Parse paper for DOIs → download files → validate → use if available → fall back to PDF

**Why:** 30-40% of papers host complete datasets externally (OSF, Zenodo, etc.)
- Supplementary Excel files are cleaner than PDF tables
- Grain-level data often ONLY in supplementary files
- Saves 30-60 minutes of PDF extraction time

**New workflow:** Steps 0.1-0.4 detect and download automatically (no manual intervention)

---


## Step 0.2: Resolve DOIs to Download URLs

**Task:** For each DOI found, resolve to actual download URLs and identify file types

**Why this is critical:**
- DOIs are **persistent identifiers** but don't directly link to files
- Need to resolve through doi.org → actual repository → download links
- Different repositories have different URL structures (Zenodo vs OSF vs Figshare)

**Process:**

```python
import requests
from bs4 import BeautifulSoup
import time

print()
print('━' * 80)
print('STEP 0.2: RESOLVING DOIs TO DOWNLOAD URLs')
print('━' * 80)
print()

download_links = []

# Process DOIs
for doi in supplementary_sources['dois']:
    print(f'🔗 Resolving DOI: {doi}')

    try:
        # Follow DOI redirect
        response = requests.get(doi, allow_redirects=True, timeout=10)
        final_url = response.url

        print(f'   → Resolved to: {final_url}')

        # Parse HTML to find download links
        soup = BeautifulSoup(response.content, 'html.parser')

        # Look for common download link patterns
        for link in soup.find_all('a', href=True):
            href = link['href']

            # Check if it's a data file
            if any(ex