Data Reconciler — BeonIQ Visual + Data Validation

Three-way validation: scrapes the BeonIQ UI via Playwright, then compares what's on screen against both DynamoDB records (what generators wrote) and Databricks ground truth (source of truth).

Abel's internal reconciler checks DynamoDB completeness (are all records there?). This one checks visual accuracy (does what the user sees match the data?).

When to Use

Use this skill when asked to: "reconcile data", "validate numbers", "check KPIs against source", "data reconciliation", "visual validation", or any request to verify BeonIQ displays correct data.

Prerequisites

• Playwright MCP available (Chrome browser automation)
• AWS credentials for BeyondIQ account (DynamoDB + Secrets Manager)
• Python 3 with boto3 installed
• GitHub CLI authenticated to ChinchillaEnterprises/transportation-insight

How It Works

1. Playwright scrape — Navigates BeonIQ UI, reads KPI card values, filter states, AI insights, chart date ranges
2. DynamoDB query — Reads same KPIs from Insight/KpiMetric tables (what generators wrote)
3. Databricks query — Runs SQL against hot layer tables (ground truth)
4. Three-way compare — Screen vs DynamoDB (rendering bugs), Screen vs Databricks (pipeline bugs)

Two Modes

Interactive Mode

Triggered by human request. Specific company/service, explain findings conversationally, post to Slack.

Overnight Mode

Triggered by beoniq-reconciler.sh. All enabled companies, JSON output, auto-file GitHub issues.

Screen Scraping Workflow

1. Authenticate

1. Launch Chrome via Playwright MCP
2. Navigate to BeonIQ login page (URL from env or https://dev.beoniq.com)
3. Google SSO login (or bottom-right backdoor if Okta blocks)
4. Verify dashboard loads

2. Extract KPI Data Per Company/Service

For each company + service type:

1. Switch company via company switcher dropdown
2. Switch service type if needed (MT, Brokerage tabs)
3. Wait for data load (spinners clear)
4. Read unfiltered KPIs:
   • Find all KPI cards on the dashboard
   • For each card: extract displayed numeric value + KPI title
   • Check if AI insight text is present (star icon / expandable)
5. Read filtered KPIs (MT only):
   • Apply Mode: TL → read all KPI values
   • Apply Mode: LTL → read all KPI values
   • Clear filters
6. Read chart date range:
   • Note months on x-axis (earliest + latest)
7. Save structured JSON

3. Screen Data JSON Format

Save to automations/beoniq-reconciler/data/screen-{COMPANY}-{SERVICE}-{DATE}.json:

{
  "company": "KGPCO",
  "service": "mt",
  "scraped_at": "2026-03-04T03:15:00Z",
  "kpis": {
    "total_cost": {
      "value": 523456.78,
      "filtered": {
        "TL": 312000.00,
        "LTL": 211456.78
      }
    },
    "volume": {
      "value": 1247,
      "filtered": {
        "TL": 723,
        "LTL": 524
      }
    }
  },
  "months_on_screen": ["2025-01", "2025-02", "2025-03"],
  "latest_month": "2025-12",
  "ai_insights": {
    "total_cost": true,
    "volume": false
  }
}

4. Run Reconciler

cd /Users/tori/Documents/Repos/CHI/automations/beoniq-reconciler

# Single company (after scraping)
python3 reconciler.py \
  --screen-data data/screen-KGPCO-mt-2026-03-04.json \
  --company KGPCO --service mt --env dev

# All companies (reads screen JSONs from data/ dir)
python3 reconciler.py --all --screen-dir data/ --env dev --output data/results.json

5. Results Structure

The reconciler does TWO comparisons per company:

• screen_vs_databricks — Does the UI match ground truth? (catches full pipeline bugs)
• screen_vs_dynamo — Does the UI match what generators wrote? (catches frontend rendering bugs)

Each comparison checks:

• KPI Value Match — Tolerances: 1%/$100 costs, 0.5%/5 counts, 2%/$0.01 ratios
• Filter Consistency — Sum of TL+LTL vs unfiltered. Ratio ~2.0 = double-counting (#405)
• Date Range — Missing months on screen vs available data
• Data Freshness — Latest month on screen vs in source
• AI Insights — Every KPI should have analysis text

6. KPI Value Parsing Tips

When scraping KPI values from screen:

• Strip currency: $523,456.78 → 523456.78
• Strip commas: 1,247 → 1247
• Handle %: 12.5% → 12.5
• Handle abbreviations: $523.5K → 523500, $1.2M → 1200000
• N/A or blank → null

Overnight Mode Specifics

When invoked by beoniq-reconciler.sh:

1. Authenticate once via Playwright
2. For each enabled company in reconciler-config.json: a. Scrape screen data → save JSON
3. Close browser
4. Run reconciler.py --all (compares against DynamoDB + Databricks)
5. Auto-file GitHub issues for MISMATCHes
6. Print final JSON summary as LAST LINE of stdout:

{"date":"2026-03-04","env":"dev","companies_checked":2,"total_match":14,"total_mismatch":2,"total_missing":1,"total_skipped":3,"issues_created":[],"summary":"KGPCO MT: 2 mismatches. Brokerage: all match."}

Auto-Filing GitHub Issues

For each MISMATCH:

gh issue create \
  --repo ChinchillaEnterprises/transportation-insight \
  --title "[DATA] KPI mismatch: {kpi} for {company} ({layer})" \
  --label "bug" --label "data-validation" \
  --body "..."

Key Files

automations/beoniq-reconciler/
├── reconciler.py          # Main orchestrator (three-way comparison)
├── dynamo_client.py       # DynamoDB reader (Insight + KpiMetric tables)
├── databricks_client.py   # Databricks SQL client (OAuth + Statement API)
├── comparator.py          # Tolerance-based comparison engine
├── config/
│   ├── reconciler-config.json   # Companies, tolerances, check toggles
│   └── kpi-registry.json        # KPI→SQL mapping per service type
├── beoniq-reconciler.sh   # Overnight bash wrapper (uses Claude + Playwright)
└── .env                   # RECONCILER_ENABLED flag

No-Data Behavior

• Empty screen (all null) → SKIPPED
• 0 DynamoDB records → SKIPPED
• 0 Databricks rows → SKIPPED
• All SKIPPED → brief "no data" Slack note, no issues filed
• RECONCILER_ENABLED=false → bash wrapper exits immediately

Testing

# Test DynamoDB connectivity
python3 dynamo_client.py --test

# Test Databricks connectivity
python3 databricks_client.py --test

# Dry run (no external queries)
python3 reconciler.py --dry-run

# With screen data file
python3 reconciler.py --screen-data data/screen-KGPCO-mt-2026-03-04.json --company KGPCO --env dev

# Bash wrapper
./beoniq-reconciler.sh --dry-run