⚠️ Data Quality Alert: Database Anomaly Detected #34

Open
opened 2026-11-17 13:01:15 +00:00 by forgejo-actions · 0 comments

⚠️ Data Quality Alert: Database Anomaly Detected

Audit Timestamp: 2026-11-17 13:01 UTC
Repository: john4064/Sports-Ingestion
Commit SHA: 381bd371bc4a5438a301a4abfe35d8a9f8e477ec


Gridiron Ledger: Comprehensive Accuracy & Integrity Audit Report

Audit Timestamp: 2026-11-17 05:01:00 PST
Target Season: 2026 (and historical backfill downward)
Live Deployment Target: https://nfl.rcslabs.dev
Database Host Target: postgres://rcsdev:%E2%80%A2%E2%80%A2%E2%80%A2%E2%80%A2@192.168.0.140:5432/zeta?sslmode=disable

1. Executive Summary & Quality Scorecard

Metric Count Status
🚨 Critical Anomalies 0 CLEAN (0)
⚠️ High Priority Issues 0 CLEAN (0)
🔍 Medium Discrepancies 0 CLEAN (0)
ℹ️ Low / Informational 1 FLAGGED (1)
✅ Verified Assertions (Passed) 17 PASS
📊 Total Audit Checks Executed 18 —

2. Detailed Findings by Category

Live Deployment (nfl.rcslabs.dev)

1. ✅ PASS /api/seasons contract verified

Successfully verified 28 seasons descending from 2026 to 1999.


2. ✅ PASS /api/teams 32-franchise count verified

Exactly 32 NFL franchises returned with valid division mappings.


3. ✅ PASS /api/teams?team=SF franchise detail verified

Successfully retrieved 44 roster players and 17 schedule games for SF.


4. ✅ PASS /api/matchups Week 11 slate verified

All 13 games populated with kickoff times, venues, betting odds, and impact players.


5. ✅ PASS /api/player/advanced contract verified

Profile and advanced telemetry successfully returned for p_patrick_mahomes (Season 2026).


6. ✅ PASS /api/player/career contract verified

Successfully retrieved 8 game log rows and career totals for p_patrick_mahomes.


7. ✅ PASS Passing leader ground truth reconciliation verified

Verified 5 top passing leaders in DB against external telemetry.


Database Relational & Schema Integrity

1. ✅ PASS Team code canonicalization verified

Zero legacy or ghost team aliases ('LA', 'OAK', 'SD') found across all player stats tables.


2. ✅ PASS Player identity deduplication verified

Zero suffix-induced fragmented player records found.


3. ✅ PASS Player ID slug canonicalization verified

All player_id entries in weekly_stats conform to canonical ^p_ prefix.


4. ✅ PASS Referential integrity verified

All weekly_stats rows successfully link to master players directory.


5. ✅ PASS Game score status consistency verified (Season 2026)

All scheduled games have NULL scores, and all final games have valid non-null scores.


6. ✅ PASS Closing betting lines verified for completed games (Season 2026)

All finalized games have non-null closing spreads and over/under totals.


7. ✅ PASS Weekly stats week boundaries verified

Zero seasonal aggregate rows (week <= 0) found in weekly_stats.


Database Statistical Consistency

1. ✅ PASS Statistical bounds verified (Season 2026)

All single-game yardage metrics remain within legitimate historical NFL maximums.


2. ℹ️ LOW Cross-source stat variance between ESPN and Sleeper (Season 2026)

Out of 7011 matched player-games, found 1 pass yard mismatches, 1 rush yard mismatches, and 1 rec yard mismatches.

  • Expected: 0 discrepancies between official box scores
  • Actual: 3 total stat variances
  • Impact: Minor fantasy point variance depending on selected source.

External Ground Truth: Yahoo Sports

1. ✅ PASS Yahoo Sports cross-validation clean (150 games verified)

All 150 audited games across Season 2026 Weeks 1-5 matched Yahoo Sports official scores 100%.


External Ground Truth: Historical Games Registry

1. ✅ PASS Canonical games registry parity verified (272 games, Season 2026)

All 272 games in Season 2026 matched 100% against canonical GSIS/PFR game records.


3. Master Idempotent Remediation Script

Below is the consolidated, safe idempotent SQL script to remediate all database discrepancies identified during this audit run:

-- Gridiron Ledger Master Remediation Script
-- Generated: 2026-11-17T05:01:00-08:00

BEGIN;

-- No automated SQL remediation actions required; database is clean.
COMMIT;

Raw Execution Logs

go: downloading github.com/jackc/pgx/v5 v5.11.0
go: downloading github.com/robfig/cron/v3 v3.0.1
go: downloading github.com/jackc/puddle/v2 v2.2.2
go: downloading github.com/jackc/pgpassfile v1.0.0
go: downloading github.com/jackc/pgservicefile v0.0.0-20240606120523-5a60cdf6a761
go: downloading golang.org/x/text v0.42.0
go: downloading golang.org/x/sync v0.23.0
2026/11/17 05:00:58 Running Data Quality report on existing database...
2026/11/17 05:00:58 === Data Quality & Auto-Remediation Audit ===
2026/11/17 05:00:59 DQ Warning: 151 records in weekly_stats are missing Team.
2026/11/17 05:00:59 --- Cross-Source Validation (ESPN vs Sleeper) ---
2026/11/17 05:00:59 Matched Player Games: 77885
2026/11/17 05:00:59 Mismatches - Pass Yds: 13 | Rush Yds: 65 | Rec Yds: 124
2026/11/17 05:00:59   Mismatch Sample: S2009 Wk1 p_chris_johnson (LV) - Pass: 0/0, Rush: 0/57, Rec: 0/11
2026/11/17 05:00:59   Mismatch Sample: S2009 Wk3 p_adrian_peterson (CHI) - Pass: 0/0, Rush: 12/85, Rec: 0/14
2026/11/17 05:00:59   Mismatch Sample: S2009 Wk4 p_chris_johnson (LV) - Pass: 0/0, Rush: 0/83, Rec: 0/11
2026/11/17 05:00:59   Mismatch Sample: S2009 Wk5 p_zach_miller (LV) - Pass: 0/0, Rush: 0/0, Rec: 69/2
2026/11/17 05:00:59   Mismatch Sample: S2009 Wk6 p_chris_johnson (LV) - Pass: 0/0, Rush: 0/128, Rec: 0/0
2026/11/17 05:00:59 =============================================
2026/11/17 05:00:59 Data Quality report complete. Exiting.
2026/11/17 05:01:00 Starting Gridiron Ledger Accuracy & Integrity Audit...
2026/11/17 05:01:00 Target Season: 2026 | Live App: https://nfl.rcslabs.dev | Output: audit_report.md
2026/11/17 05:01:00 --> [1/3] Auditing Live Deployment (nfl.rcslabs.dev)...
2026/11/17 05:01:04     Live audit completed with 6 findings.
2026/11/17 05:01:04 --> [2/3] Auditing Database Integrity for Season 2026...
2026/11/17 05:01:07     Database audit completed with 9 findings.
2026/11/17 05:01:07 --> [3/3] Reconciling Against External Ground Truth (Yahoo Sports & Canonical Registries)...
2026/11/17 05:01:15     External reconciliation completed with 3 findings.
2026/11/17 05:01:15 Detailed audit report written to audit_report.md

=======================================================
             AUDIT EXECUTION SCORECARD                 
=======================================================
 🚨 Critical Anomalies:            0
 ⚠️ High Priority Issues:          0
 🔍 Medium Discrepancies:          0
 ℹ️ Low / Informational:          1
 ✅ Verified Assertions (Passed): 17
=======================================================

This issue was automatically generated by the weekly Tuesday Data Quality Forgejo Action.

## ⚠️ Data Quality Alert: Database Anomaly Detected **Audit Timestamp:** 2026-11-17 13:01 UTC **Repository:** `john4064/Sports-Ingestion` **Commit SHA:** `381bd371bc4a5438a301a4abfe35d8a9f8e477ec` --- # Gridiron Ledger: Comprehensive Accuracy & Integrity Audit Report **Audit Timestamp**: `2026-11-17 05:01:00 PST` **Target Season**: `2026` (and historical backfill downward) **Live Deployment Target**: [https://nfl.rcslabs.dev](https://nfl.rcslabs.dev) **Database Host Target**: `postgres://rcsdev:%E2%80%A2%E2%80%A2%E2%80%A2%E2%80%A2@192.168.0.140:5432/zeta?sslmode=disable` ## 1. Executive Summary & Quality Scorecard | Metric | Count | Status | | :--- | :--- | :--- | | 🚨 **Critical Anomalies** | `0` | `CLEAN (0)` | | ⚠️ **High Priority Issues** | `0` | `CLEAN (0)` | | 🔍 **Medium Discrepancies** | `0` | `CLEAN (0)` | | ℹ️ **Low / Informational** | `1` | `FLAGGED (1)` | | ✅ **Verified Assertions (Passed)** | `17` | `PASS` | | 📊 **Total Audit Checks Executed** | `18` | — | ## 2. Detailed Findings by Category ### Live Deployment (nfl.rcslabs.dev) #### 1. `✅ PASS` /api/seasons contract verified Successfully verified 28 seasons descending from 2026 to 1999. --- #### 2. `✅ PASS` /api/teams 32-franchise count verified Exactly 32 NFL franchises returned with valid division mappings. --- #### 3. `✅ PASS` /api/teams?team=SF franchise detail verified Successfully retrieved 44 roster players and 17 schedule games for SF. --- #### 4. `✅ PASS` /api/matchups Week 11 slate verified All 13 games populated with kickoff times, venues, betting odds, and impact players. --- #### 5. `✅ PASS` /api/player/advanced contract verified Profile and advanced telemetry successfully returned for p_patrick_mahomes (Season 2026). --- #### 6. `✅ PASS` /api/player/career contract verified Successfully retrieved 8 game log rows and career totals for p_patrick_mahomes. --- #### 7. `✅ PASS` Passing leader ground truth reconciliation verified Verified 5 top passing leaders in DB against external telemetry. --- ### Database Relational & Schema Integrity #### 1. `✅ PASS` Team code canonicalization verified Zero legacy or ghost team aliases ('LA', 'OAK', 'SD') found across all player stats tables. --- #### 2. `✅ PASS` Player identity deduplication verified Zero suffix-induced fragmented player records found. --- #### 3. `✅ PASS` Player ID slug canonicalization verified All player_id entries in weekly_stats conform to canonical ^p_ prefix. --- #### 4. `✅ PASS` Referential integrity verified All weekly_stats rows successfully link to master players directory. --- #### 5. `✅ PASS` Game score status consistency verified (Season 2026) All scheduled games have NULL scores, and all final games have valid non-null scores. --- #### 6. `✅ PASS` Closing betting lines verified for completed games (Season 2026) All finalized games have non-null closing spreads and over/under totals. --- #### 7. `✅ PASS` Weekly stats week boundaries verified Zero seasonal aggregate rows (week <= 0) found in weekly_stats. --- ### Database Statistical Consistency #### 1. `✅ PASS` Statistical bounds verified (Season 2026) All single-game yardage metrics remain within legitimate historical NFL maximums. --- #### 2. `ℹ️ LOW` Cross-source stat variance between ESPN and Sleeper (Season 2026) Out of 7011 matched player-games, found 1 pass yard mismatches, 1 rush yard mismatches, and 1 rec yard mismatches. - **Expected**: `0 discrepancies between official box scores` - **Actual**: `3 total stat variances` - **Impact**: Minor fantasy point variance depending on selected source. --- ### External Ground Truth: Yahoo Sports #### 1. `✅ PASS` Yahoo Sports cross-validation clean (150 games verified) All 150 audited games across Season 2026 Weeks 1-5 matched Yahoo Sports official scores 100%. --- ### External Ground Truth: Historical Games Registry #### 1. `✅ PASS` Canonical games registry parity verified (272 games, Season 2026) All 272 games in Season 2026 matched 100% against canonical GSIS/PFR game records. --- ## 3. Master Idempotent Remediation Script Below is the consolidated, safe idempotent SQL script to remediate all database discrepancies identified during this audit run: ```sql -- Gridiron Ledger Master Remediation Script -- Generated: 2026-11-17T05:01:00-08:00 BEGIN; -- No automated SQL remediation actions required; database is clean. COMMIT; ``` --- ### Raw Execution Logs ```text go: downloading github.com/jackc/pgx/v5 v5.11.0 go: downloading github.com/robfig/cron/v3 v3.0.1 go: downloading github.com/jackc/puddle/v2 v2.2.2 go: downloading github.com/jackc/pgpassfile v1.0.0 go: downloading github.com/jackc/pgservicefile v0.0.0-20240606120523-5a60cdf6a761 go: downloading golang.org/x/text v0.42.0 go: downloading golang.org/x/sync v0.23.0 2026/11/17 05:00:58 Running Data Quality report on existing database... 2026/11/17 05:00:58 === Data Quality & Auto-Remediation Audit === 2026/11/17 05:00:59 DQ Warning: 151 records in weekly_stats are missing Team. 2026/11/17 05:00:59 --- Cross-Source Validation (ESPN vs Sleeper) --- 2026/11/17 05:00:59 Matched Player Games: 77885 2026/11/17 05:00:59 Mismatches - Pass Yds: 13 | Rush Yds: 65 | Rec Yds: 124 2026/11/17 05:00:59 Mismatch Sample: S2009 Wk1 p_chris_johnson (LV) - Pass: 0/0, Rush: 0/57, Rec: 0/11 2026/11/17 05:00:59 Mismatch Sample: S2009 Wk3 p_adrian_peterson (CHI) - Pass: 0/0, Rush: 12/85, Rec: 0/14 2026/11/17 05:00:59 Mismatch Sample: S2009 Wk4 p_chris_johnson (LV) - Pass: 0/0, Rush: 0/83, Rec: 0/11 2026/11/17 05:00:59 Mismatch Sample: S2009 Wk5 p_zach_miller (LV) - Pass: 0/0, Rush: 0/0, Rec: 69/2 2026/11/17 05:00:59 Mismatch Sample: S2009 Wk6 p_chris_johnson (LV) - Pass: 0/0, Rush: 0/128, Rec: 0/0 2026/11/17 05:00:59 ============================================= 2026/11/17 05:00:59 Data Quality report complete. Exiting. 2026/11/17 05:01:00 Starting Gridiron Ledger Accuracy & Integrity Audit... 2026/11/17 05:01:00 Target Season: 2026 | Live App: https://nfl.rcslabs.dev | Output: audit_report.md 2026/11/17 05:01:00 --> [1/3] Auditing Live Deployment (nfl.rcslabs.dev)... 2026/11/17 05:01:04 Live audit completed with 6 findings. 2026/11/17 05:01:04 --> [2/3] Auditing Database Integrity for Season 2026... 2026/11/17 05:01:07 Database audit completed with 9 findings. 2026/11/17 05:01:07 --> [3/3] Reconciling Against External Ground Truth (Yahoo Sports & Canonical Registries)... 2026/11/17 05:01:15 External reconciliation completed with 3 findings. 2026/11/17 05:01:15 Detailed audit report written to audit_report.md ======================================================= AUDIT EXECUTION SCORECARD ======================================================= 🚨 Critical Anomalies: 0 ⚠️ High Priority Issues: 0 🔍 Medium Discrepancies: 0 ℹ️ Low / Informational: 1 ✅ Verified Assertions (Passed): 17 ======================================================= ``` --- *This issue was automatically generated by the weekly Tuesday Data Quality Forgejo Action.*
Sign in to join this conversation.
No labels
No milestone
No project
No assignees
1 participant
Notifications
Due date
The due date is invalid or out of range. Please use the format "yyyy-mm-dd".

No due date set.

Dependencies

No dependencies set

Reference
john4064/Sports-Ingestion#34
No description provided.