xt.md

Cross-Tables API Integration

This document outlines all API calls made to cross-tables.com (scrabbleplayers.org) and how that data is used throughout the application.

Summary

We fetch three types of data from Cross-Tables to power our overlays:

Data	Purpose	Overlay
Basic player data	Rating, ranking, W-L-T record, photo, location	Player cards, ticker
Tournament history	Tournament count, avg scores, recent results	CrossTablesPlayerProfile
Head-to-head games	Historical game records between players	HeadToHead

All data is fetched once during tournament creation/update and cached locally. Overlays read from the cache - no API calls at display time.

---

API Endpoints

Base URL

https://cross-tables.com/rest

1. Get Single Player (Basic Info)

GET /player.php?player={playerid}

Parameters:

• playerid (number) - Cross-tables player ID

Returns: Basic player profile with ratings, rankings, W-L-T record

Used in: CrossTablesClient.getPlayer()

---

2. Get Single Player (Detailed with Tournament History)

GET /player.php?player={playerid}&results=1

Parameters:

• playerid (number) - Cross-tables player ID
• results=1 - Flag to include tournament history

Returns: Detailed player data including array of ~20 tournament results

Used in: CrossTablesClient.getDetailedPlayer()

Use case: Fetching tournament history for player profile overlays

---

3. Get Multiple Players (Batch)

GET /players.php?playerlist={id1},{id2},...

Parameters:

• playerlist - Comma-separated player IDs

Returns: Array of basic player profiles

Used in: CrossTablesClient.getPlayers()

Batch size: 50 players per request

Known issue: API silently drops some players when batch is too large, causing fallback to individual fetches

---

4. Search Players by Name

GET /players.php?search={encodedName}

Parameters:

• search - URL-encoded player name

Returns: Array of matching players

Used in: CrossTablesClient.searchPlayers()

Use case: Discovering xtids for players who don't have embedded IDs in tournament files

---

5. Get All Player IDs

GET /players.php?idsonly=1

Parameters:

• idsonly=1 - Flag to return only IDs and names

Returns: Array of {playerid, name}

Limitation: Only returns first 200 players

Used in: CrossTablesClient.getAllPlayersIdsOnly() (mostly unused due to limitation)

---

6. Head-to-Head Games

GET /headtohead.php?players={id1}+{id2}+...

Parameters:

• players - Plus-separated (space-separated) player IDs

Returns: Array of head-to-head games between all provided players

Used in: CrossTablesHeadToHeadService.fetchBulkHeadToHeadData()

---

Data Types

CrossTablesPlayer

Basic player profile returned from the API.

interface CrossTablesPlayer {
  playerid: number;           // Cross-tables ID
  name: string;
  twlrating?: number;         // TWL rating
  cswrating?: number;         // CSW rating
  twlranking?: number;
  cswranking?: number;
  w?: number;                 // Wins
  l?: number;                 // Losses
  t?: number;                 // Ties
  b?: number;                 // Byes
  photourl?: string;
  city?: string;
  state?: string;
  country?: string;
  // Added when results=1:
  tournamentCount?: number;
  averageScore?: number;
  opponentAverageScore?: number;
  results?: TournamentResult[];
}

TournamentResult

Tournament history entry for a player.

interface TournamentResult {
  tourneyid: number;
  name: string;
  date: string;
  division: string;
  wins: number;
  losses: number;
  ties: number;
  place: number;
  totalplayers: number;
  rating: number;
  ratingchange: number;
  points: number;
  averagepoints: number;
}

HeadToHeadGame

A single game between two players.

interface HeadToHeadGame {
  gameid: number;
  date: string;
  tourneyname?: string;
  player1: {
    playerid: number;
    name: string;
    score: number;
    oldrating: number;
    newrating: number;
    position?: number;
  };
  player2: {
    playerid: number;
    name: string;
    score: number;
    oldrating: number;
    newrating: number;
    position?: number;
  };
  annotated?: string;  // URL to annotated game
}

---

Database Storage

cross_tables_players Table

Caches player profiles from cross-tables.

Column	Type	Description
cross_tables_id	integer (PK)	The xtid
name	varchar	Player name
twl_rating	integer	TWL rating
csw_rating	integer	CSW rating
twl_ranking	integer	TWL ranking
csw_ranking	integer	CSW ranking
wins	integer	Career wins
losses	integer	Career losses
ties	integer	Career ties
byes	integer	Career byes
photo_url	varchar	Photo URL
city	varchar	City
state	varchar	State
country	varchar	Country
tournament_results	jsonb	Array of TournamentResult
tournament_count	integer	Number of tournaments
average_score	numeric(5,1)	Average score
opponent_average_score	numeric(5,1)	Opponent average
created_at	timestamp
updated_at	timestamp

cross_tables_head_to_head Table

Stores head-to-head game history.

Column	Type	Description
id	serial (PK)
game_id	integer (unique)	Cross-tables game ID
date	date	Game date
tourney_name	varchar	Tournament name
player1_id	integer	Player 1 xtid
player1_name	varchar	Player 1 name
player1_score	integer	Player 1 score
player1_old_rating	integer	P1 rating before
player1_new_rating	integer	P1 rating after
player1_position	integer	P1 standing
player2_id	integer	Player 2 xtid
player2_name	varchar	Player 2 name
player2_score	integer	Player 2 score
player2_old_rating	integer	P2 rating before
player2_new_rating	integer	P2 rating after
player2_position	integer	P2 standing
annotated	varchar	Game replay URL

players Table (Tournament Players)

Links tournament players to cross-tables via xtid column.

Column	Type	Description
xtid	integer (FK)	References cross_tables_players.cross_tables_id

---

Data Flow

Tournament Creation/Update

1. Load tournament file from URL or upload

   • No API call

2. Extract xtids from tournament data:

   • From player.etc.xtid field
   • From player name suffix :XT{id} (e.g., "Smith, John:XT012345")
   • No API call (local parsing)

3. Discover missing xtids by searching cross-tables by name

   • API call: GET /players.php?search={encodedName} (one per player without xtid)
   • Converts "Last, First" → "First Last" before searching

4. Fetch player profiles from cross-tables API

   • API call: GET /players.php?playerlist={id1},{id2},... (batches of 50)
   • Fallback API call: GET /player.php?player={id} (individually if batch fails or returns incomplete data)

5. Store in cross_tables_players table

   • No API call (database insert/update)

6. Fetch head-to-head games for all players in each division

   • API call: GET /headtohead.php?players={id1}+{id2}+... (one call per division, all player IDs joined with +)

7. Store in cross_tables_head_to_head table

   • No API call (database insert)

8. Link tournament players to cross-tables via xtid

   • No API call (database update)

9. Fetch detailed tournament history for player profiles

   • API call: GET /player.php?player={id}&results=1 (one per player)
   • Stores: tournament_count, average_score, opponent_average_score, tournament_results (jsonb)
   • Used by: CrossTablesPlayerProfile overlay (shows tournament count, avg scores, recent tournament)

API Call Counts

For a tournament with P players across D divisions, assuming all players have embedded xtids:

Step	Calls	Formula
3. Discover xtids	0 to P	0 if embedded, 1 per player without
4. Player profiles	ceil(P/50)	Batches of 50
6. Head-to-head	D	1 per division
9. Detailed history	P	1 per player
Total	P + ceil(P/50) + D	Best case (all embedded)

Examples:

Tournament Size	Divisions	Best Case	Worst Case (no embedded xtids)
40 players	1	42 calls	82 calls
150 players	3	156 calls	306 calls
500 players	5	515 calls	1015 calls

Data Retrieval for Overlays

When fetching tournament data for display:

1. JOIN players with cross_tables_players on xtid
2. Include xtData (full CrossTablesPlayer) on each player
3. Include headToHeadGames array on each division

No API calls - all data served from local database cache

---

XTID Discovery

Sources of XTIDs

1. Embedded in tournament file - player.etc.xtid field
2. In player name - Suffix format :XT{playerid}
3. Name search - Search cross-tables API by player name

Name Format Conversion

Tournament files use "Last, First" format. Cross-tables uses "First Last" format.

Conversion: "Tunnicliffe, Matthew" → "Matthew Tunnicliffe"

Helper Functions (xtidHelpers.ts)

stripXtidFromPlayerName(name)      // Remove :XT suffix
extractXtidFromEtc(xtidValue)      // Parse etc.xtid field
extractXtidFromPlayerName(name)    // Extract from :XT suffix
getBestXtid(name, etcXtid)         // Get xtid from best source

---

Rate Limiting

Setting	Value
Batch size	50 players
Delays	None (requests made as fast as possible)

---

Frontend Usage

Components Using Cross-Tables Data

Component	Data Used
CrossTablesPlayerProfile	Full player profile, photo, ratings, tournament history
HeadToHead	Head-to-head game history, computed W-L record
PlayerStats	Ratings, rankings, W-L-T record
CombinedTicker	Ratings, photo
PictureRenderer	Photo URL

Stats Computation (HeadToHeadStats.purs)

Computes from stored head-to-head games:

• Win/loss record between two players
• Average scores
• Recent games list

---

Service Classes

Class	Purpose
CrossTablesClient	Static API call methods
CrossTablesSyncService	Orchestrates sync workflow
CrossTablesHeadToHeadService	Fetches/stores H2H games
CrossTablesPlayerRepository	Database operations for players
CrossTablesHeadToHeadRepository	Database operations for H2H games

---

Error Handling

• API failures don't block tournament creation
• Missing cross-tables data handled gracefully (players shown without xtData)
• Individual fetch fallback if batch fails
• Head-to-head fetch failures don't prevent tournament creation

---

File Locations

Backend Services:

• backend/src/services/crossTablesClient.ts
• backend/src/services/crossTablesSync.ts
• backend/src/services/crossTablesHeadToHeadService.ts

Repositories:

• backend/src/repositories/crossTablesPlayerRepository.ts
• backend/src/repositories/crossTablesHeadToHeadRepository.ts

Utils:

• backend/src/utils/xtidHelpers.ts

Types:

• shared/types/domain.ts
• backend/src/types/database.ts

Migrations:

• backend/migrations/20250824174500_cross_tables_integration.ts
• backend/migrations/20250824180000_add_cross_tables_tournament_results.ts
• backend/migrations/20250827010000_add_cross_tables_head_to_head.ts