# Database Design — Full Table List

Conventions used throughout:
- `id` BIGINT UNSIGNED autoincrement PK on every table (internal joins/perf), plus a `uuid` CHAR(36) UNIQUE column on any table exposed via public API/URL (teams, players, matches, leagues, news, users).
- `deleted_at` (soft deletes) on all "editorial"/master-data tables (teams, players, leagues, news, users, etc.), NOT on high-volume transactional/log tables (match_events, api_request_logs, ai_generation_logs) — those use hard deletes via retention jobs instead, to keep indexes lean.
- `created_at` / `updated_at` on all tables.
- All FKs `->constrained()->cascadeOnDelete()` or `->nullOnDelete()` depending on whether the child is meaningless without the parent.
- Composite indexes documented per table where they matter for the live-scores hot path.

---

## A. Core / Geography (4)
1. `countries` — id, uuid, name, code (ISO), flag_path, continent, is_active
2. `cities`
3. `timezones`
4. `languages` — code, name, native_name, is_rtl, is_active (drives i18n incl. Arabic RTL)

## B. Competitions (12)
5. `sports` (future-proofing beyond football)
6. `leagues` — country_id FK, sport_id FK, type (league/cup), tier, logo, external_ids (per-provider mapping, JSON)
7. `league_translations` — league_id, language_code, name, description
8. `seasons` — league_id FK, year label, start_date, end_date, is_current
9. `stages` (group stage / knockout / regular season)
10. `rounds` — season_id FK, stage_id FK, name, sort_order
11. `groups` (competition groups, e.g. "Group A")
12. `group_teams` (pivot: group_id, team_id)
13. `standings` — season_id, group_id (nullable), team_id, played, won, drawn, lost, gf, ga, gd, points, form, position — unique(season_id, group_id, team_id)
14. `standings_history` (daily snapshot for position-change graphs)
15. `top_scorers` (materialized/cached leaderboard: season_id, player_id, goals, assists, penalties)
16. `season_awards` (player/team of the season, golden boot, etc.)

## C. Teams & Squads (9)
17. `teams` — uuid, country_id, name, short_name, logo, founded_year, external_ids(JSON)
18. `team_translations`
19. `venues` (stadiums) — team_id (home), name, city_id, capacity, surface, image, coordinates
20. `coaches` — uuid, name, nationality_country_id, photo, birth_date
21. `team_coach` (pivot with date ranges: team_id, coach_id, joined_at, left_at)
22. `players` — uuid, name, nationality_country_id, birth_date, height_cm, weight_kg, preferred_foot, photo, external_ids(JSON)
23. `player_translations`
24. `team_player` (squad membership, date-ranged: team_id, player_id, shirt_number, position, joined_at, left_at)
25. `player_market_values` (historical valuation snapshots)

## D. Transfers, Injuries, Referees (4)
26. `transfers` — player_id, from_team_id (nullable=free agent), to_team_id, transfer_date, fee, transfer_type(loan/permanent/free)
27. `injuries` — player_id, type, injured_at, expected_return_at, status
28. `referees` — uuid, name, nationality_country_id, photo
29. `referee_match` (pivot: referee_id, match_id, role[main/var/assistant])

## E. Matches — the hot path (11)
30. `matches` — uuid, league_id, season_id, round_id, home_team_id, away_team_id, venue_id, kickoff_at, status (enum), minute, home_score, away_score, home_score_ht, away_score_ht, external_ids(JSON) — **indexes**: (status, kickoff_at) for live polling, (league_id, season_id), (home_team_id), (away_team_id)
31. `match_lineups` — match_id, team_id, player_id, is_starting, position, shirt_number
32. `match_formations` (match_id, team_id, formation string e.g. "4-3-3")
33. `match_events` — match_id, team_id, player_id, related_player_id(nullable, e.g. assist/sub-in), minute, extra_minute, type(enum: goal/yellow/red/sub/var), detail — index(match_id, minute)
34. `match_statistics` — match_id, team_id, possession, shots_total, shots_on_target, corners, fouls, offsides, passes, pass_accuracy — unique(match_id, team_id)
35. `match_odds` (optional bookmaker feed)
36. `match_predictions` — match_id, home_win_pct, draw_pct, away_win_pct, predicted_score, model_version
37. `head_to_head_cache` (materialized H2H summary between two team_ids, refreshed on demand)
38. `match_commentary` (minute-by-minute text feed, distinct from structured match_events)
39. `match_broadcasters` (TV/streaming info per country)
40. `match_attendance`

## F. Users, Auth, Personalization (8)
41. `users` — uuid, name, email, password, locale, timezone
42. `roles` / 43. `permissions` / 44. `model_has_roles` / 45. `model_has_permissions` / 46. `role_has_permissions` (Spatie Permission — generated by package, listed for completeness)
47. `favorites` (polymorphic: user_id, favoritable_type, favoritable_id — team/player/league)
48. `notifications` (Laravel default notifications table) + `notification_preferences` (per-user, per-event-type opt in/out)

## G. RSS Aggregator (7)
49. `rss_sources` — name, feed_url, country_id, language_code, category_id (default), is_active, fetch_interval_minutes, last_fetched_at, keyword_include(JSON), keyword_exclude(JSON)
50. `rss_import_logs` — source_id, started_at, finished_at, items_found, items_imported, items_skipped_duplicate, items_failed, error_message
51. `news_categories` — name, slug, parent_id(nullable, self-referencing)
52. `news` — uuid, category_id, source_id(nullable, FK rss_sources), title, slug, excerpt, body, featured_image, status(enum: draft/scheduled/published), published_at, is_featured, is_trending, views_count, source_credit_url
53. `news_translations` — news_id, language_code, title, excerpt, body, meta_title, meta_description
54. `news_tags` / 55. `news_tag_pivot` (news_id, tag_id)
56. `news_related` (self-referencing pivot: news_id, related_news_id, relevance_score)

## H. AI Content Engine (7) — the differentiator
57. `ai_provider_settings` — provider(enum: openai/anthropic/gemini/deepseek/mistral/ollama), is_active, api_key(encrypted), base_url(for ollama/self-hosted), model_name, extra_config(JSON) — **only one row `is_active=true` at a time**; changing this changes the whole platform's AI behavior with zero code deploys
58. `ai_generation_logs` — news_id(nullable while processing), provider, capability(enum: rewrite/summarize/seo/tags/translate/classify/duplicate_check/slug/title), prompt_tokens, completion_tokens, latency_ms, status(success/failed), error_message, created_at — audit trail + cost tracking, no soft delete (pure log, pruned by retention job)
59. `article_similarity_scores` — news_id, compared_news_id, similarity_score(float 0-1), algorithm(enum: embedding_cosine/simhash/jaccard), checked_at — unique(news_id, compared_news_id); rows above the configured threshold block auto-publish and route to manual review
60. `article_translations_queue` — news_id, target_language_code, status(pending/processing/done/failed), attempts
61. `article_internal_links` — news_id, linkable_type(team/player/league), linkable_id, anchor_text, position_in_body — generated by the AI internal-linking pass, rendered at read-time so links stay valid if slugs change
62. `content_embeddings` — embeddable_type, embeddable_id, provider, vector(JSON or a vector column if pgvector/MySQL 9 vector type is available), dims — powers `detectDuplicates()` similarity search, indexed per provider since embedding spaces aren't comparable across providers
63. `ai_prompt_templates` — capability, language_code, template_body, is_active — lets you tune prompts (e.g. Arabic sports-journalism tone) from the admin panel without a deploy

## I. SEO & System (6)
64. `seo_meta` (polymorphic: seoable_type, seoable_id, meta_title, meta_description, canonical_url, og_image, schema_type)
65. `redirects` (old_path, new_path, status_code) — for slug-change safety
66. `sitemap_entries` (cached, regenerated by scheduler)
67. `activity_logs` (Spatie Activitylog-style audit trail: user_id, subject_type, subject_id, event, properties JSON)
68. `settings` (key/value platform config, cached in Redis)
69. `advertisements` (placement enum: header/sidebar/article/in_feed, code/script, is_active, starts_at, ends_at)

## J. External API Layer (3)
70. `api_providers` (api_football / sportmonks — which is "primary" vs "fallback", credentials, is_active) — mirrors `ai_provider_settings` pattern for sports-data provider abstraction
71. `api_request_cache` (endpoint, params_hash, response JSON, expires_at) — every outbound API-Football/Sportmonks call is cached here per the requirement "cache every API request"
72. `api_sync_jobs` (entity_type, entity_id, status, last_synced_at, next_sync_at)

---

**Total: 72 explicit tables** (+ Spatie Permission's 5 pivot/lookup tables + Laravel's built-in `jobs`, `failed_jobs`, `cache`, `sessions`, `password_reset_tokens` = **~90 tables**, matching the target range without inventing padding tables for their own sake).

This batch (migrations delivered now) covers **Sections E (partial: matches, match_events, match_statistics), G (RSS), and H (AI Content Engine)** — the modules needed to ship the RSS + AI pipeline end-to-end, plus the minimum core (leagues/teams/news) they depend on. Remaining sections follow in the next batches per the START ORDER.