MULTI-LEAGUE EXTENSION · IMPLEMENTATION GUIDE

From one global league to many

Companion guide to v2 swagger.yaml. Covers data model, SQL migration, auth tier resolution, and the deprecation path from v1.

Context: the TFCL Play API (v1) is the in-house API our team runs and consumes to power TFCL Play (the community-facing site, play.tfcleague.com) and TFCL Prime (the seasonal league structure, alpha.tfcleague.com — intended to live at prime.tfcleague.com when out of alpha). v1 is TFCL-specific — it is the API the original TFCL Play league is built on.

Heads-up: v1's /api/premium/* endpoints are the TFCL Play premium tier (paid membership, PayPal orders, gift codes, premium API keys) — a feature within TFCL Play itself. That is unrelated to TFCL Prime the seasonal league, despite the similar name.

v2 is the multi-tenant platform extension of that same API: it lets one deployment host other leagues (our own future sub-leagues, or partner communities we onboard) alongside the original TFCL Play league. TFCL Prime is itself one of those leagues — the first major off-platform tenant we run on prime.tfcleague.com / alpha.tfcleague.com, and what motivated splitting the platform out into v2 in the first place.

1. Goal & constraints

Extend the TFCL Play API from a single-league surface area (v1) into a multi-tenant platform without breaking existing clients. v1 stays TFCL-specific — powers TFCL Play (the community site) + TFCL Prime (the seasonal league currently in alpha at alpha.tfcleague.com / intended prime.tfcleague.com) — and the v2 layer is what lets us host other leagues on the same deployment going forward. TFCL Prime itself is the first off-platform tenant we migrate onto v2; its seasonal-league nature (different formats, separate standings, dedicated moderators) is what drove the multi-league split in the first place.

Existing /api/* v1 endpoints keep working with zero changes.
Add explicit League entity + league-scoped /api/leagues/{leagueId}/... endpoints under v2.
Default/prod keeps behaving exactly as today — TFCL becomes a league with id="tfcl".
Cross-league data leakage prevented by two enforced gates: key-binding and parameterized SQL.

2. Data model

2.1 New tables (post-migration)

league id TEXT PRIMARY KEY -- e.g. 'tfcl' or UUID v7 slug TEXT UNIQUE NOT NULL -- ^[a-z0-9][a-z0-9-]{1,38}[a-z0-9]$ name TEXT NOT NULL description TEXT NULL visibility TEXT NOT NULL -- 'public' | 'private' settings_json TEXT NOT NULL -- { mapPool, formats, eloConfig, serverConfig, ruleset } archived_at TEXT NULL created_at TEXT NOT NULL DEFAULT (datetime('now')) updated_at TEXT NOT NULL DEFAULT (datetime('now')) league_user_profile league_id TEXT NOT NULL user_id TEXT NOT NULL -- steamid64 elo REAL NOT NULL DEFAULT 1500 wins INTEGER NOT NULL DEFAULT 0 losses INTEGER NOT NULL DEFAULT 0 draws INTEGER NOT NULL DEFAULT 0 stats_json TEXT NOT NULL DEFAULT '{}' -- { kills, deaths, assists, matchesPlayed } roles_json TEXT NOT NULL DEFAULT '[]' -- [member, captain, league_moderator, league_admin] banned INTEGER NOT NULL DEFAULT 0 ban_reason TEXT NULL ban_expires_at TEXT NULL joined_at TEXT NOT NULL DEFAULT (datetime('now')) PRIMARY KEY (league_id, user_id) FOREIGN KEY (league_id) REFERENCES league(id) ON DELETE CASCADE league_points_wallet league_id TEXT NOT NULL user_id TEXT NOT NULL balance INTEGER NOT NULL DEFAULT 0 lifetime_earned INTEGER NOT NULL DEFAULT 0 lifetime_spent INTEGER NOT NULL DEFAULT 0 PRIMARY KEY (league_id, user_id) league_points_transaction id INTEGER PRIMARY KEY AUTOINCREMENT league_id TEXT NOT NULL user_id TEXT NOT NULL delta INTEGER NOT NULL balance_after INTEGER NOT NULL kind TEXT NOT NULL -- purchase|spend|refund|earn|admin_adjust reason TEXT NULL created_at TEXT NOT NULL DEFAULT (datetime('now')) league_announcement id INTEGER PRIMARY KEY AUTOINCREMENT league_id TEXT NOT NULL title TEXT NOT NULL body TEXT NOT NULL pinned INTEGER NOT NULL DEFAULT 0 created_by TEXT NOT NULL -- steamid64 expires_at TEXT NULL created_at TEXT NOT NULL DEFAULT (datetime('now')) league_premium_grant league_id TEXT NOT NULL user_id TEXT NOT NULL source TEXT NOT NULL -- global|league_grant|league_purchase|none expires_at TEXT NULL -- NULL means until revoked created_at TEXT NOT NULL DEFAULT (datetime('now')) PRIMARY KEY (league_id, user_id) league_ban league_id TEXT NOT NULL user_id TEXT NOT NULL reason TEXT NOT NULL expires_at TEXT NULL -- NULL = permanent banned_by TEXT NOT NULL -- steamid64 of caller banned_at TEXT NOT NULL DEFAULT (datetime('now'))

2.2 Column additions to existing tables

Table	Add column	Notes
`lobbies`	`league_id TEXT NOT NULL DEFAULT 'tfcl'`	Backfill before migration runs
`tournaments`	`league_id TEXT NOT NULL DEFAULT 'tfcl'`
`cups`	`league_id TEXT NOT NULL DEFAULT 'tfcl'`
`teams`	`league_id TEXT NOT NULL DEFAULT 'tfcl'`	(league_id, slug) becomes a unique pair instead of global slug unique
`servers`	`league_id TEXT NOT NULL DEFAULT 'tfcl'`	Server quotas now derived from `league.settings.serverConfig`
`matches`	`league_id TEXT NOT NULL DEFAULT 'tfcl'`	For league-aware public leaderboard
`api_keys`	`league_id TEXT NULL`	NULL for global (v1) keys; non-null for league-scoped keys
`api_keys`	`scopes_json TEXT NULL`	Comma-sep of allowed scope strings (e.g. `lobby:moderate`)
`announcements`	`league_id TEXT NOT NULL DEFAULT 'tfcl'`	Existing rows migrate; new double-write still allowed during overlap

2.3 Relationship diagram (ER, ASCII)

                          ┌──────────────┐
                          │ users (g)    │   (global, unchanged)
                          └──────┬───────┘
                                 │ 1
                                 │
                                 │ N
              ┌──────────────────┼──────────────────────────────┐
              │                  │                              │
              ▼ N                ▼ N                            ▼ N
   ┌────────────────────┐  ┌──────────────────┐  ┌────────────────────────┐
   │ league_user_profile│  │ league_points_   │  │ team_membership (per  │
   │ (league_id,user)   │  │ wallet+txn       │  │ league_id)             │
   └──────────┬─────────┘  └──────────────────┘  └────────────────────────┘
              │
              │ N
              ▼
   ┌───────────────────────────────────────────────────────────┐
   │                              LEAGUE                       │
   │  id, slug, name, visibility, settings{                   │
   │     mapPool, formats, eloConfig, serverConfig, ruleset} │
   └────┬──────────┬──────────┬──────────┬──────────┬──────────┘
        │ N        │ N        │ N        │ N        │ N
        ▼          ▼          ▼          ▼          ▼
   ┌────────┐ ┌────────────┐ ┌────────┐ ┌────────┐ ┌─────────────────┐
   │lobbies │ │tournaments │ │  cups  │ │ teams  │ │ server + reservation│
   └────┬───┘ └─────┬──────┘ └────┬───┘ └────┬───┘ └─────────┬───────┘
        │ N         │ N           │ N        │ N             │ N
        ▼           ▼             ▼          ▼               ▼
   ┌──────────────────────────────────────────────────────────────┐
   │                    matches (per league)                      │
   └──────────────────────────────────────────────────────────────┘

   (g) = global table, single instance. N cardinality shown is per-league.

3. Authentication tiers

The effective tier is computed once per request, then route-level handlers apply their rule:

Tier	Source	League scope	Notes
1 platform_admin	`Bearer tfcl_ak_…`	any league	Users row has `is_admin=1`
2 league_admin	`Bearer tfcl_lak_…`	only `api_keys.league_id = {path}`	401 if no key, 403 if mismatch
3 league_moderator	`Bearer tfcl_lmk_…`	same gate as 2	Allowed endpoints subset (no points adjust etc.)
4 session_user	`tfcl_session=…` cookie	membership row optional	Premium features gated separately
5 public	no auth	public leagues only	Private league returns 403

3.1 Cross-league gate (the critical bit)

For tier 2/3 keys, the request handler MUST issue SQL with a parameterized WHERE league_id = ? clause. Additionally, the auth middleware MUST verify that api_keys.league_id = c.req.param('leagueId'). Either alone is insufficient:

If only key check runs: a key for league "A" cannot access league "B" — correct behavior.
If only SQL clause runs: two developers could write a list endpoint that accidentally selects all rows. The key check prevents this.
With both: an attacker cannot probe other-league resources.

3.2 Scopes for tier 2/3 keys

Each league key has an array of scope strings. Default scope sets:

league_admin defaults → ["admin:lobby", "admin:tournament", "admin:cup", "admin:points", "admin:elo", "admin:announcement", "admin:user", "admin:keys"]
league_moderator defaults → ["mod:lobby", "mod:tournament", "mod:cup", "mod:user", "mod:announcement"]
league_premium defaults → ["premium:lobby", "premium:points", "premium:matchmaking"]

Routes check the relevant scope; otherwise return 403 with {"error":"scope_required","code":"FORBIDDEN_SCOPE"}.

4. Migration plan

4.1 Rollout phases

Schema prep. Run migrations/0022_multi_league.sql: create new tables (league, league_user_profile, league_points_wallet, …), add league_id column with default 'tfcl' to existing tables, add league_id/scopes_json to api_keys. Backfill league_id='tfcl' for every existing row in those tables.

Seed default league. Insert the legacy "TFCL" league:

INSERT INTO league (id, slug, name, visibility, settings_json)
VALUES ('tfcl','tfcl','TFCL','public','{
  "ruleset":     {"winCondition":"round_limit"},
  "mapPool":     ["koth_product","cp_badlands","cp_granary","cp_process_final"],
  "formats":     ["6v6","9v9","highlander"],
  "eloConfig":   {"kFactor":24,"startingElo":1500},
  "serverConfig":{"regions":["na","eu"],"quotaPerRegion":2}
}');

Index pass. Add composite indexes:

CREATE INDEX IF NOT EXISTS idx_lobbies_league          ON lobbies (league_id, state, created_at);
CREATE INDEX IF NOT EXISTS idx_tournaments_league       ON tournaments (league_id, state);
CREATE INDEX IF NOT EXISTS idx_cups_league              ON cups (league_id, state);
CREATE INDEX IF NOT EXISTS idx_teams_league_slug_unique ON teams (league_id, slug);
CREATE INDEX IF NOT EXISTS idx_matches_league_completed ON matches (league_id, completed_at);
CREATE INDEX IF NOT EXISTS idx_api_keys_league_scope    ON api_keys (league_id, scope);
CREATE INDEX IF NOT EXISTS idx_lup_league_elo           ON league_user_profile (league_id, elo DESC);

v1 aliasing. In Hono, mount v1 endpoints as aliases to v2. Each v1 handler reads its table whichever it was and sets league_id='tfcl' on inserts; deletes/patches already JOIN league_id='tfcl'.
Ship. Deploy with zero breaking changes for old clients. Add deprecation header X-Deprecate-Migration: v1-will-end-YYYY-MM-DD on v1.
Phase 2. New clients use v2 paths. Existing clients migrate.
Phase 3. After all known clients migrated (per analytics on api_key_audit.endpoint), retire v1 endpoints. Hard gate: /api/* returns 410 Gone with location: /api-docs/v2.html.

4.2 Backward-compatibility rules

Existing call POST /api/lobbies/ internally inserts with league_id='tfcl'. Handler verifies no League-Id header is set; if it is, return 410 + redirect to v2 path.
Existing call GET /api/public/leaderboard returns the leaderboard for league_id='tfcl' only.
Existing premiumApiKey still works for the league the user has membership in; mapping is per league_user_profile.is_member.
Existing adminApiKey continues to mean platform admin — cross-league.

4.3 Deprecation timeline

Day	Action
D0	v2 ships. v1 endpoints add header `X-Deprecate-Migration` with date D90.
D30	v1 endpoints add `Sunset` header. `/docs/api-keys` gets a "Migrate to v2" banner.
D60	v1 endpoint metrics reviewed. Slow movers emailed.
D90	v1 endpoints return 410 Gone with v2 path in body. `api` page hides v1 link.
D120	Internal v1 handlers removed. `/api-docs/v1.html` shows "Use v2".

4.4 Migration safety nets

Tier 1 platform keys gain an optional league_id column to restrict scope (default: unrestricted). This makes the eventual per-league isolation testable in staging.
League-Id header (alternatively the path {leagueId}) is the only thing that drives scoping.
For v1 calls during overlap: row-level league_id='tfcl' is set automatically.

All v2 writes go through one SQL helper:

async function leagueTable(c, name) {
  const leagueId = c.req.param('leagueId');
  if (!leagueId) throw new ApiError(404, 'league_not_found');
  // For tier 2/3 keys: enforce equality
  const key = c.get('apiKey');
  if (key?.league_id && key.league_id !== leagueId) {
    throw new ApiError(403, 'cross_league_forbidden');
  }
  return { leagueId, table: name };
}

This is invoked from every v2 handler.

5. Sample: end-to-end flow for "create a lobby in a new league"

Platform admin creates league Aussie Highlander via POST /api/leagues/.
Platform admin issues a league_moderator key bound to league_id="aussie-highlander" via POST /api/leagues/aussie-highlander/admin/keys/ — raw key returned once.
New user authenticates globally via Steam OAuth (cookie).
User opts into Aussie Highlander — first lobby creation auto-creates a league_user_profile row with elo=1500, roles=["member"].
User submits POST /api/leagues/aussie-highlander/lobbies/ with map, format. Server validates against league map pool + formats, inserts lobby with league_id="aussie-highlander".
The Play plugin polls GET /api/leagues/aussie-highlander/lobbies/ and gets only Aussie-Highlander-tenant lobbies.

6. Privilege matrix (cheat-sheet)

Action	Public	Member	Moderator	League admin	Platform admin
List public league	✓	✓	✓	✓	✓
List private league	✗	✓	✓	✓	✓
Create lobby	✗	✓	✓	✓	✓
Ban user from league	✗	✗	✗	✓	✓
Adjust points in league	✗	✗	✗	✓	✓
Override ELO in league	✗	✗	✗	✓	✓
Approve tournament	✗	✗	✓	✓	✓
Issue league admin keys	✗	✗	✗	✓	✓
Cross-league ban	✗	✗	✗	✗	✓

7. Cross-league leakage: how it's prevented

Key-binding gate. For tier 2/3 keys, api_keys.league_id MUST equal path {leagueId}. Enforced in apiAuthOptional middleware (extends src/lib/api-auth.ts).
Parameterized SQL gate. Every v2 handler's query joins on WHERE league_id = ? using c.req.param('leagueId'). Direct user-supplied values cannot reach the WHERE clause.
Test gate. CI test: for every league-scoped endpoint, query with foreign leagueId returns 404 same as if leagueId is fake.
Audit gate. api_key_audit logs every auth attempt including request_path. Sudden spike of 403+/api/leagues/tfcl/... from a single api_key_id=league-A-key is a probe attempt.

8. Keeping TFCL functioning identically

Seeded league_id='tfcl' matches today's behavior. Existing rankings, premium flags, points balances, and ELO are preserved (they are expressed via league_user_profile row keyed on ('tfcl', user_id)).
Edge case: users global table remains source of truth for Steam identity, avatar, display name, and global premium. The v2 spec explicitly says premium in a league is the union of global+league_grant+league_purchase.
The v1 path /api/public/leaderboard becomes /api/leagues/tfcl/public/leaderboard under the hood. Clients don't notice.

9. Files in this drop

/api-docs/v2/swagger.yaml — OpenAPI 3.0.3 spec, 40 paths, 48 schemas, 7 auth schemes.
/api-docs/v2 — Swagger UI bound to v2 spec, same dark theme as v1.
/api-docs/ — Picker page linking v1 and v2.
Migration SQL (sample) — see above. Production migration should be authored as migrations/0022_multi_league.sql following this project's standard migration-file convention.