rate-card-agent-improvements

Changes Made (2025-11-21)

1. Runs Directory Structure ✅

Problem: No way to track multiple agent runs and compare outputs over time.

Solution: Implemented timestamped run directories:

Rate cards/runs/
  ├── 2025-11-21-17-02-30/
  │   ├── output.xlsx
  │   ├── generator-script.py
  │   ├── debug-service-detection.log
  │   └── agent-output.log
  ├── 2025-11-21-17-15-45/
  │   └── ...
  └── 2025-11-21-17-30-12/
      └── ...

Benefits:

• Track agent behavior over time
• Compare different runs
• Debug failures by examining logs
• Keep history of generated scripts

2. Improved Agent Prompt ✅

Problem: Service level detection failed (found 0 service levels).

Key Improvements to .claude/agents/rate-card-extractor-generator.md:

a) Reference File Analysis

**ALWAYS start by examining the reference output file** (if provided):
- This file shows the EXACT expected output format
- Analyze its structure: number of sheets, sheet names, data layout
- Use it as ground truth for understanding the transformation pattern

Why: The reference file (The Absorption Company Freight Rate Card - Implentio v1.6.xlsx) shows exactly what service levels exist and how they should be structured.

b) Enhanced Service Level Detection

**CRITICAL: Service Level Detection** - Identify all service level sections:
  - Method 1: Look for merged cells (service level headers are often merged)
  - Method 2: Look for blank row patterns (preceded by 1-2 blank rows)
  - Method 3: Look for specific text patterns (ECONOMY, SUB 1LB, etc.)
  - Method 4: Analyze the reference output to see which service levels appear
  - DEBUG: Print every row where column A has text

Why: The previous approach was too narrow. Multiple detection methods increase robustness.

c) Debugging Instructions

**SECOND: Debug service level detection**:
Create a quick analysis script to print ALL potential service level headers:
```python
# Print rows that might be service level headers
if col_a and len(col_a) < 50 and col_a.isupper() and not col_a.replace('.','').isdigit():
    print(f"Row {row_idx}: '{col_a}'")
    # Check if followed by "Weight" or "ZONE"

**Why**: Without seeing what the script finds, it's impossible to debug why service levels aren't being detected.

#### d) Exact Matching Requirements
```markdown
**CRITICAL:** The service level keys in the mapping file (column 3) must EXACTLY match
the service level headers found in the Rate Card
  - Example: If mapping says "SUB 1LB 2025", look for exactly "SUB 1LB 2025"
  - Use case-insensitive match recommended

Why: The mapping file says “SUB 1LB 2025” but if the rate card has “Sub 1lb 2025” or “SUB1LB 2025”, exact matching will fail.

e) Structured Response Format

**Step 0: Setup run directory...**
[Create timestamped directory: Rate cards/runs/YYYY-MM-DD-HH-MM/]

**Step 1: Analyzing reference output (if provided)...**
[Inspect reference file structure]

**Step 2: Analyzing source files...**
[Debug service level detection - print potential headers found]

**Step 3: Creating rate card generator script...**
[Create Python script with robust detection]

**Step 4: Executing generation...**
[Run script with full output]

**Step 5: Results**
[Report output details and comparison with reference]

Why: Clear workflow ensures the agent follows best practices in the right order.

3. Test Harness Script ✅

Created test-rate-card-agent.sh with:

• Automatic timestamped run directory creation
• Comprehensive prompt including reference file path
• Stream JSON output with verbose logging
• Automatic output analysis after completion
• File listing and quick Excel inspection

Usage:

cd /home/uptown/Projects/research/claude-code-agents
./test-rate-card-agent.sh

Expected Improvements

Before (Previous Run)

Service Levels Found: 0
Total Sheets Generated: 0 + 1 summary sheet
Issues Encountered: 34
  - Service level not found for ALL carriers

After (Expected with Improvements)

Service Levels Found: 5-7 (ECONOMY, SUB 1LB, 3 DAY, SECOND DAY, OVERNIGHT, etc.)
Total Sheets Generated: 50-60 + 1 summary sheet
Issues Encountered: 0-5 (minor edge cases only)
  - Successfully matched most/all carrier mappings

Testing the Improvements

Manual Test

cd /home/uptown/Projects/research/claude-code-agents

# Run with new improved prompt
claude -p "Use the rate-card-extractor-generator agent and provide its complete response.

**Task**: Generate a freight rate card workbook from the source files.

**Files provided:**
- Source rate card: 'Rate cards/Absorption Co 2025 - Rate Card.xlsx'
- Mapping file: 'Rate cards/Stord Mapping key - Generic.xlsx'
- Reference output: 'Rate cards/The Absorption Company Freight Rate Card - Implentio v1.6.xlsx'

**Output location**: 'Rate cards/runs/2025-11-21-17-30/output.xlsx'

**Important**:
1. Analyze the reference file FIRST
2. Use multiple service level detection methods
3. Create debug scripts
4. Save all outputs to the run directory

Generate the complete workbook." \
--permission-mode bypassPermissions \
--output-format stream-json \
--verbose > "Rate cards/runs/manual-test.log" 2>&1

Automated Test

./test-rate-card-agent.sh

Root Cause Analysis

Why Previous Attempt Failed

Issue: Service level detection returned 0 results

Likely causes:

1. Case sensitivity: Mapping file has “SUB 1LB 2025”, rate card might have different case
2. Whitespace: Extra spaces or different whitespace in headers
3. Row structure: Detection logic was too rigid, expected specific patterns
4. No debugging: No visibility into what the script was finding
5. No reference: Agent didn’t know what service levels to look for

How Improvements Address This

1. ✅ Reference file analysis: Agent now knows exactly what service levels should exist
2. ✅ Multiple detection methods: More robust, tries different patterns
3. ✅ Case-insensitive matching: Recommended in improved prompt
4. ✅ Debug output: Agent instructed to print all potential headers
5. ✅ Verbose logging: Full visibility into what’s being detected

Next Steps

1. Run ./test-rate-card-agent.sh to test improvements
2. Examine the output in the new run directory
3. Check if service levels are now being detected
4. If still failing, examine debug logs to see what headers are being found
5. Iterate on detection logic based on actual data patterns

Documentation Updates

Updated files:

• ✅ .claude/agents/rate-card-extractor-generator.md - Improved agent prompt
• ✅ test-rate-card-agent.sh - New test harness
• ✅ rate-card-agent-improvements.md - This document
• ✅ Created Rate cards/runs/ directory structure

Comparison: Before vs After

Success Criteria

The improvements will be considered successful if:

• Service levels detected > 0 (ideally 5-7)
• Rate card sheets generated > 0 (ideally 50-60)
• Carrier mapping success rate > 80%
• Output file structure matches reference file
• Debug logs show clear visibility into detection process
• Run directory contains all artifacts for review

Rollback Plan

If improvements don’t work:

1. Previous agent version is in git history
2. Can revert .claude/agents/rate-card-extractor-generator.md
3. Test script is standalone, doesn’t affect existing workflows
4. Runs directory is isolated, doesn’t impact existing files