Model identity and time explicitly
Store competitions, seasons, teams, and matches in separate tables linked by stable public IDs or local surrogate keys with unique public-ID constraints. A match row should retain its stable ID, competition and season relationships, home and away teams, UTC kickoff, status, and result fields that are actually present in the contract.
Keep source_updated_at and ingested_at as different columns. The first represents the data record’s freshness when provided by your normalized source; the second records when your job processed it. These timestamps support audits, stale-data policy, and recovery. Do not overwrite them with a single updated_at column whose meaning changes across code paths.
Load pages transactionally and retain provenance
Validate each page before opening a transaction, then upsert parent records before dependent matches. Use parameterized statements, enforce foreign keys, and update only mutable fields. Commit the page and its checkpoint together. If validation or a write fails, roll back so the job can safely repeat the same page.
Record the requested window, API request ID, retrieval time, and ingestion run. Test with repeated data, changed kickoff times, changed statuses, empty pages, and a failure in the middle of a page. Before a production migration, create an automated backup and perform a restore test against an isolated database rather than assuming a dump is usable.
| Field | Purpose | Rule |
|---|---|---|
| public_id | Stable external identity | Unique and immutable |
| starts_at | Kickoff | UTC |
| source_updated_at | Source freshness | Never replace with ingest time |
| ingested_at | Pipeline audit | Set by your worker |
A handoff your agent can actually follow
Treat an AI agent as a planner and transformer, not as the database. Give it a narrow task, the exact resources it may call, the response fields it may quote, and a stop condition for missing data. Keep bearer credentials in the server-side tool implementation rather than in the prompt, transcript, browser, or generated source file.
The handoff below is deliberately operational. It asks for evidence before prose, makes uncertainty visible, and keeps the model inside the current football API contract. Adapt the output format to your product, but preserve the rules about stable IDs, UTC timestamps, freshness, and error handling.
Design normalized MySQL tables keyed by stable Stats API IDs.
Store UTC datetime values, source_updated_at, and ingested_at separately.
Use transactions and idempotent upserts.
Retain a checkpoint only after every row in the page commits.What the human reviewer still owns
Automation can verify schemas and repeatable checks, but publication and product decisions still need a person. Review the selected competition, season, team, and match IDs; confirm that the time window matches the user’s question; and read the final answer against the retrieved JSON. A fluent explanation is not evidence that the underlying call was correct.
For time-sensitive football AI, record when the source was ingested and when the agent retrieved it. If the workflow cannot establish those timestamps, qualify the result instead of presenting it as current. The same rule applies to unavailable capabilities: do not quietly substitute fixtures or results for lineups, player statistics, odds, expected goals, injuries, or live events.
- Confirm every quoted fact appears in the retained API response.
- Exercise the empty, 401, 404, 429, and 503 paths before launch.
- Keep model interpretation separate from source facts in logs and user-facing output.
- Restore a database backup into an isolated database and repeat the same ingestion window before approving the schema.
Continue with the contract, not a guess
Start with the public contract and coverage ledger, then move into implementation only when the capability you need is marked available. The related guide gives your next agent-first pattern without requiring an undocumented endpoint.