AGL × PitchKount Integration Architecture Decisions & Development Estimate

2025-12-18-agl-integration-architecture.md

AGL × PitchKount Integration Architecture Decisions

Document Version: 1.0 DRAFT Date: December 18, 2025 Status: Architecture Review - Pending Team Input

---

Executive Summary

This document captures the architectural decisions made during planning for the Athletes Go Live (AGL) × PitchKount integration. These decisions should be reviewed by the team before finalizing the development estimate.

---

Integration Scope

PitchKount will build:

1. Inbound Event Receivers - Receive real-time events from AGL
2. Outbound Webhook Sender - Push alerts/blocks to AGL
3. Nightly Sync Service - Pull data from AGL's REST API
4. Supporting Infrastructure - Auth, retry logic, entity mapping, exception handling

---

Events We Will Receive from AGL

Event	Purpose
pitch.recorded	Core pitch tracking, rule evaluation
pitch.reversed	Scorer corrections
game.started	Initialize game state
game.ended	Create game report
game.suspended	Preserve state for cross-day handling
game.resumed	Continue suspended game
game.cancelled	Clean up without creating game report
run.scored	Track score (requested addition)
out.recorded	Advance IP by 0.1 (requested addition)
inning.changed	Track current inning (requested addition)
new_batter	Legal overage window tracking (TBD - need to confirm with AGL)

Note: run.scored, out.recorded, and inning.changed were requested additions beyond AGL's original spec. Need to confirm AGL will expose these.

---

Architecture Decisions

1. Organization/Association Onboarding

Options Considered:

1. They must already exist in PitchKount - Link existing PK association to AGL org ID
2. Auto-create from AGL data - If AGL org doesn't exist in PK, create new association automatically during sync ✅

Decision: Auto-create associations from AGL data during sync

• When nightly sync encounters an AGL organization not in PitchKount, automatically create a new association
• No manual linking required

---

2. Billing for AGL-Sourced Associations

Options Considered:

1. AGL handles billing - Associations from AGL are automatically "paid" (marked as paidManually or partnership flag) ✅
2. Separate PitchKount subscription required - Auto-created associations still need Stripe subscription
3. Tiered access - Basic pitch tracking free for AGL, premium features require subscription

Decision: AGL handles billing - associations are pre-authorized

• Associations created from AGL integration are automatically "paid"
• No Stripe subscription required for AGL-sourced associations
• Implementation: Flag or account type indicating AGL partnership

---

3. Player Date of Birth

Options Considered:

1. Trust AGL DOB completely - Use their DOB directly for rule calculations ✅
2. DOB required for sync - Reject/flag players missing DOB
3. Fallback to conservative limits - If no DOB, apply most restrictive age bracket rules

Decision: Trust AGL DOB data completely

• Use AGL-provided DOB directly for age-based rule calculations
• No validation or override mechanism needed

---

4. Association Level Mapping

Options Considered:

1. Auto-create association levels - If AGL sends "12U" and that level doesn't exist, create it with default rules
2. Pre-configured mapping required - AGL age groups must map to existing PK levels, fail/flag if no match ✅
3. Single default level - All AGL players go into one default level, admin configures later

Decision: Pre-configured mapping required with exception logging

• AGL age_group (e.g., "12U") must map to existing PK association levels
• If no mapping exists, log to exception table for review
• Do NOT auto-create association levels

---

5. Exception/Error Logging

Options Considered:

1. AGL integration specific - Dedicated agl_sync_exceptions table just for this integration
2. Generic integration exceptions - Broader integration_exceptions table for any external integration ✅
3. Extend existing activity logging - Use current activity logging with new activity types

Decision: Generic integration_exceptions table

• Designed for any external integration (not AGL-specific)
• Will capture: level mapping failures, sync errors, discrepancies, unmapped entities

Schema (proposed):

integration_exceptions (
  id UUID PRIMARY KEY,
  integration_source VARCHAR NOT NULL, -- 'agl', future integrations
  exception_type VARCHAR NOT NULL,     -- 'level_mapping', 'sync_failure', etc.
  entity_type VARCHAR,                 -- 'player', 'team', 'game', etc.
  external_id VARCHAR,                 -- AGL ID or other external reference
  pk_entity_id UUID,                   -- PK entity if applicable
  details JSONB,                       -- Exception-specific data
  resolved_at TIMESTAMP,
  resolved_by UUID,
  resolution_notes TEXT,
  created_at TIMESTAMP DEFAULT NOW()
)

---

6. Real-Time Event Processing Model

Options Considered:

1. Parallel system - AGL events create/update separate agl_live_games structure, convert to game report on game.ended
2. Feed into existing PK Live - AGL events create/update records in existing liveGames/liveGamePlayers tables
3. Direct to game reports - Skip PK Live, accumulate pitch data, create game report directly on game.ended ✅

Decision: Direct to game reports (skip PK Live system)

• AGL events accumulate in event storage
• On game.ended, create game report directly from accumulated events
• Do NOT feed into existing liveGames/liveGamePlayers tables
• Avoids schema friction with PK Live's per-inning manual tracking model

---

7. Outbound Alert Webhook Timing

Options Considered:

1. Synchronous on pitch event - Immediately calculate eligibility and respond/webhook back to AGL
2. Asynchronous but near real-time - Queue events, process within seconds, send webhook if alert needed ✅
3. Polling-based from AGL - AGL calls us to check player status rather than us pushing

Decision: Asynchronous, near real-time processing

• Queue incoming pitch events
• Process within seconds (not synchronous with AGL's request)
• Send webhook if alert/block threshold crossed
• Provides resilience and retry capability without blocking AGL's scoring flow

---

8. Pitch Count Discrepancy Handling

Options Considered:

1. AGL is source of truth - If their pitching-stats shows different totals, update our records to match
2. PitchKount is source of truth - Flag discrepancies but don't auto-correct; our real-time counts are authoritative
3. Manual resolution required - Discrepancies logged for human review, no auto-correction either direction ✅

Decision: Manual resolution required

• Nightly reconciliation compares AGL pitch counts vs PitchKount accumulated counts
• Discrepancies logged to integration_exceptions table
• No auto-correction in either direction
• Human review required to resolve

---

9. Game Report Verification Flow

Options Considered:

1. Auto-verified - AGL data is authoritative, skip verification workflow (status = Verified)
2. Standard workflow - Enter as PendingVerification, allow coaches to dispute within normal window ✅
3. New AGL-specific status - Distinct status indicating external source, different review rules

Decision: Standard verification workflow

• AGL-sourced game reports enter as PendingVerification
• Coaches can dispute within normal window
• Same flow as manually-created game reports
• AGL data is NOT auto-verified

---

10. Schedule/Game Creation

Options Considered:

1. Create schedules only - Sync creates schedules records; game reports only created when game.ended events arrive ✅
2. Create schedules + placeholder games - Sync creates both schedule and Draft game report placeholder
3. Schedules optional - Don't sync schedules; create game reports directly from real-time events

Decision: Sync schedules only; games created from real-time events

• Nightly sync creates schedules records from AGL data
• Game reports only created when game.ended event received
• Schedules exist for reference/lookup, not pre-created game reports
• Must handle game.suspended events for cross-day scenarios

---

11. Event Storage Strategy

Options Considered:

1. Single agl_game_events table - All event types in one table with event_type discriminator and JSONB payload ✅
2. Separate tables per event type - agl_pitch_events, agl_out_events, agl_run_events, etc.
3. Pitches separate, others combined - Keep pitches (high volume) separate, other events in shared table

Decision: Single agl_game_events table with event type discriminator

• All event types in one table
• event_type field discriminates between pitch, out, run, game status, etc.
• JSONB payload for event-type-specific data

Schema (proposed):

agl_game_events (
  id UUID PRIMARY KEY,
  agl_event_id VARCHAR UNIQUE NOT NULL,  -- For idempotency
  event_type VARCHAR NOT NULL,           -- 'pitch.recorded', 'out.recorded', etc.
  agl_game_id VARCHAR NOT NULL,
  agl_player_id VARCHAR,                 -- NULL for game-level events
  agl_team_id VARCHAR,
  pk_player_id UUID,                     -- FK, nullable until mapped
  pk_game_id UUID,                       -- FK, nullable until game report created
  timestamp TIMESTAMP NOT NULL,          -- When event occurred (UTC)
  game_date DATE NOT NULL,               -- Calendar date for rule calculations
  payload JSONB NOT NULL,                -- Normalized event data
  raw_payload JSONB,                     -- Original AGL payload (purged after 7 days)
  is_reversed BOOLEAN DEFAULT FALSE,     -- For pitch.reversed corrections
  processed_at TIMESTAMP,                -- When we calculated/sent alerts
  created_at TIMESTAMP DEFAULT NOW()
)

---

12. Payload Storage Strategy

Options Considered:

1. Store raw AGL payload - Keep exactly what AGL sends, parse on read
2. Normalize to our schema - Transform AGL payload into standardized structure on write
3. Both - Store raw in one field (debugging/audit), normalized in another (querying) ✅

Decision: Store both raw and normalized, purge raw after 7 days

• payload - Normalized to our schema for querying
• raw_payload - Original AGL payload for debugging
• Cron job nulls out raw_payload on events older than 7 days

---

13. Game Date Timezone

Options Considered:

1. Use association timezone - Convert UTC to PK association's configured timezone
2. Use game location timezone - Derive from venue/location info from AGL ✅
3. Derive from AGL org settings - Ask AGL to provide timezone in payloads or org data

Decision: Use organization timezone from AGL

• Capture timezone at organization level during nightly sync
• Convert event UTC timestamps to org timezone to determine calendar date
• Critical for suspended games spanning midnight

---

14. Unknown Player Handling (Pop-up Players)

Options Considered:

1. Create PK player immediately - Auto-create minimal player from pitch event, apply conservative limits
2. Store event, defer player creation - Log pitch with null pk_player_id, create during nightly sync
3. Fetch from AGL API with fallback - Call AGL REST API immediately; if fails, create stub with conservative limits ✅

Decision: Fetch from AGL API with fallback to stub

1. When pitch.recorded arrives for unknown agl_player_id:

   • Immediately call GET /api/v2/players/{player_id} to fetch full details
   • Create complete PK player record
   • Run proper rule calculations

2. If AGL API call fails/times out:

   • Create stub player with conservative limits
   • Apply most restrictive rules (youngest association level)
   • Nightly sync will enrich with full data

---

15. Innings Pitched (IP) Calculation

Options Considered:

1. Calculate from outs - Store out events, calculate IP on demand (outs / 3, remainder as decimal) ✅
2. Store as running total - Maintain innings_pitched decimal field, increment by 0.1 on each out

Decision: Calculate from out events (event-sourced)

• Store out.recorded events in event log
• Calculate IP on demand: outs / 3 with remainder as decimal (0.1, 0.2, 1.0, etc.)
• Do NOT store running IP total

---

16. Reversed Pitch Handling

Options Considered:

1. Mark original event as reversed - Set is_reversed = TRUE on original, exclude from calculations ✅
2. Create separate negative event - Insert a "negative" pitch event in the log

Decision: Mark original event as reversed

• When pitch.reversed received, set is_reversed = TRUE on original event
• Original event stays in log for audit trail
• Exclude reversed events from sum/count calculations

---

17. External ID Storage

Options Considered:

1. Add columns to existing tables - Add agl_id nullable column to players, teams, associations, schedules, games ✅
2. Separate mapping table - integration_entity_mappings(integration_source, entity_type, pk_id, external_id)
3. JSONB metadata field - Add external_ids JSONB to each entity

Decision: Add columns to existing tables

• Add nullable agl_id column to: associations, teams, players, schedules, games
• Simple, direct lookup without joins
• Index on agl_id for efficient queries during event processing

---

Open Items Requiring Clarification

From AGL

1. new_batter event - Need to confirm AGL will provide this event for legal overage tracking
2. Organization timezone - Confirm timezone is available in org data from their REST API
3. Rate limits - What are the limits on their REST API for player lookups during live games?
4. Events requested - Confirm run.scored, out.recorded, inning.changed will be exposed

Internal Decisions Needed

1. Conservative limits definition - Which rules apply to stub players when AGL API unavailable (youngest level? fixed limit?)
2. Alert thresholds - What pitch counts trigger "approaching limit" vs "at limit" vs "blocked" alerts?

---

Infrastructure Components to Build

New Database Tables

1. agl_game_events - Event log for all AGL events
2. integration_exceptions - Error/exception tracking
3. agl_organizations - Cached AGL org data with timezone (or extend associations table)

Schema Changes to Existing Tables

Add agl_id VARCHAR (nullable, indexed) to:

• associations
• teams
• players
• schedules
• games

New API Endpoints (Inbound from AGL)

1. POST /api/agl/events - Receive all event types
2. Webhook signature validation (HMAC-SHA256)
3. Idempotency handling via agl_event_id

New Webhook Sender (Outbound to AGL)

1. POST {agl_url}/api/v2/webhooks/pitchkount/alerts - Approaching limit
2. POST {agl_url}/api/v2/webhooks/pitchkount/blocks - Limit exceeded
3. POST {agl_url}/api/v2/webhooks/pitchkount/status - General updates

New Services

1. Event Processor - Async queue processing for incoming events
2. Alert Evaluator - Run pitching rules, determine if webhook needed
3. Nightly Sync Service - Pull from AGL REST API, upsert to PK
4. Reconciliation Service - Compare pitch counts, flag discrepancies

New Cron Jobs

1. AGL Nightly Sync - 2:00-3:00 AM EST window per AGL spec
2. Raw Payload Purge - Clear raw_payload on events > 7 days old
3. Exception Report - Daily summary of unresolved integration exceptions

OAuth 2.0 Client

• Client credentials flow for AGL API authentication
• Token caching and refresh logic
• Circuit breaker for API failures

---

Development Hour Estimate

Estimate Summary

Scenario	Hours	With 10% Buffer
Optimistic	130	143
Realistic	178	196
Pessimistic	249.5	275

Recommended Budget: 196-275 hours (Realistic to Pessimistic with buffer)

---

Detailed Breakdown by Component

1. Database Schema & Migrations

Task	Opt	Real	Pess
Create agl_game_events table	2	3	4
Create integration_exceptions table	1	2	3
Add agl_id columns to existing tables	2	3	5
Create indexes for AGL lookups	1	1	2
Migration testing & rollback verification	2	3	4
Subtotal	8	12	18

2. OAuth 2.0 Client & Authentication

Task	Opt	Real	Pess
OAuth 2.0 client credentials flow	3	4	6
Token caching & refresh logic	2	3	5
Circuit breaker implementation	2	3	4
Error handling & retry logic	2	3	4
Unit tests for auth module	2	3	4
Subtotal	11	16	23

3. Inbound Webhook Receiver

Task	Opt	Real	Pess
POST /api/agl/events endpoint	2	3	4
HMAC-SHA256 signature validation	2	3	4
Idempotency handling (duplicate detection)	2	3	5
Event type routing/dispatch	2	3	4
Payload normalization	3	4	6
Error responses & logging	1	2	3
Integration tests	3	4	6
Subtotal	15	22	32

4. Event Processing Service

Task	Opt	Real	Pess
Async queue setup (or direct processing)	3	4	6
pitch.recorded handler	3	4	6
pitch.reversed handler	2	3	4
game.started/ended/suspended/resumed handlers	4	6	8
out.recorded handler (IP tracking)	2	3	4
run.scored handler	1	2	3
inning.changed handler	1	2	3
Entity mapping (AGL ID → PK ID)	3	4	6
Unknown player fetch & fallback	3	4	6
Unit & integration tests	4	6	8
Subtotal	26	38	54

5. Alert Evaluator & Outbound Webhooks

Task	Opt	Real	Pess
Pitching rules integration	4	6	8
Alert threshold logic	3	4	6
Webhook sender (alerts/blocks/status)	3	4	6
Retry logic with exponential backoff	2	3	4
Webhook delivery logging	1	2	3
Unit tests	3	4	6
Subtotal	16	23	33

6. Nightly Sync Service

Task	Opt	Real	Pess
Organization sync	3	4	6
Team sync	3	4	6
Player sync	4	5	7
Schedule/Game sync	3	4	6
Upsert logic with conflict handling	3	4	6
Association auto-creation	2	3	4
Level mapping with exception logging	2	3	5
Cron job setup (2-3 AM EST window)	1	2	3
Integration tests	4	5	7
Subtotal	25	34	50

7. Game Report Generation

Task	Opt	Real	Pess
Event aggregation → game report	4	5	7
Player stats calculation from events	3	4	6
IP calculation from out events	2	2	3
Score calculation from run events	1	2	3
Suspended game handling	3	4	6
Timezone/date boundary handling	2	3	5
Integration with verification workflow	2	3	4
Tests	3	4	6
Subtotal	20	27	40

8. Reconciliation & Exception Handling

Task	Opt	Real	Pess
Pitch count reconciliation logic	2	3	5
Discrepancy detection & logging	2	3	4
Exception table queries & UI hooks	2	3	4
Raw payload purge cron job	1	1	2
Daily exception report cron	1	2	3
Subtotal	8	12	18

9. Testing, Documentation & Polish

Task	Opt	Real	Pess
End-to-end integration testing	4	6	10
Load/stress testing for webhook receiver	2	3	5
Error scenario testing	2	3	5
Documentation & runbooks	2	3	4
Code review & refactoring	3	4	6
Subtotal	13	19	30

---

Estimate Assumptions

1. Existing infrastructure: Leverages existing Drizzle ORM, Next.js API routes, and cron patterns
2. Pitching rules engine: Already implemented and tested; integration only
3. No UI work: Estimate covers backend integration only (no admin dashboards for AGL data)
4. Single developer: Hours represent one senior developer; parallel work could compress calendar time
5. AGL API stability: Assumes AGL APIs work as documented with minimal surprises
6. Events confirmed: Assumes AGL will expose run.scored, out.recorded, inning.changed as requested

Risk Factors That Could Increase Hours

Risk	Impact
AGL API undocumented behaviors	+10-20%
Complex timezone edge cases	+5-10%
Suspended game scenarios more complex	+5-15%
Rate limiting issues during live games	+5-10%
Schema migrations affecting existing data	+5-10%

Recommended Development Sequence

1. Phase 1: Database schema, OAuth client, basic webhook receiver
2. Phase 2: Event processing, entity mapping, unknown player handling
3. Phase 3: Alert evaluator, outbound webhooks
4. Phase 4: Nightly sync service
5. Phase 5: Game report generation, reconciliation
6. Phase 6: Testing, documentation, hardening

---

Next Steps

1. Team review of architecture decisions
2. Resolve open items (conservative limits, alert thresholds)
3. Confirm additional events with AGL
4. Approve development estimate
5. Create detailed implementation plan

---

Document History

Version	Date	Author	Changes
1.0	2025-12-18	Architecture Session	Initial draft
1.1	2025-12-18	Architecture Session	Added development estimate