POFA Data Model Spec v1

2026-11-03 · Status: decided, being implemented · Chinese: 数据模型规范 v1 · Related: Multi-tenant database architecture (commercial stage)

1. Why this spec

A read-only relational audit of the production database on 2026-11-03 found:

Of 112 rows in the tournaments table only 18 are live events; the rest mix in call-screen and test tooling data. The format field appears as single, single_elimination and se at the same time.
Entries (tournament_entries), draws (client-derived / MCP-materialized) and scores (embedded by MCP) have each only ever run on test data. No single chain has ever run end to end on a real event.
Players in the page flow are bare name strings with no stable ID; two players with the same name are indistinguishable.

This spec only fixes the birth shape of the next real event. It does not restructure the production database or split tables.

2. Canonical fields (event data)

New field	Values	Meaning	Legacy field (kept in sync)
`discipline`	singles (doubles reserved)	Singles / doubles — how many play	none (new dimension)
`draw_type`	single_elimination / double_elimination / round_robin	Draw type — how elimination works	`format`: single / double / rr
`draw_random_seed`	integer or null	Shuffle seed for random seeding; only applies when seeding=random	`seed`

Sync rule: the server (POST /api/tournaments and PATCH) keeps both sides in sync — whichever side a client sends, the other is filled in. Existing pages and tools keep reading legacy fields; new code reads only the new fields. The historical value se is read-mapped (= single_elimination) and never written back.

3. Player IDs

data.player_ids is parallel to data.players: same length, same order, one to one.
Issued by the server at creation, format p_ + 8 random hex chars. Renaming a player never changes their ID.
When the roster changes, existing IDs are preserved by matching on (name, occurrence index). A removed player's ID is never reused for a newcomer.
Known limitation: same-name players are distinguished by occurrence order. Claims and scores will attach to IDs, not names (migration direction, implemented separately).

4. Locking

data.locked (boolean) and data.lock_log (array of {ts, action}).
Lock points: the first successful score report (MCP report_result) locks automatically; organizers may also set it manually via PATCH.
While locked, draw-shaping fields are immutable: roster, discipline, draw_type/format, seeding, seed/draw_random_seed, third. Calling, scores and audit are unaffected.
Unlocking: a PATCH must carry the manage code plus explicit unlock: true; the server appends to lock_log and clears the lock. Silent roster edits become impossible.

5. Score integrity and the DUPR submission gate

A result has two representations: the text score (e.g. "7-5 6-3") and the structured score_sets (per set). They must agree, or score_sets must be empty — never contradicting (fixed 2026-11-03: changing the text score without sets now clears the old score_sets with an explicit warning, instead of silently keeping them).
Submission gate (hard rule): any result exported or submitted to DUPR whose score_sets is empty must be blocked and flagged for per-set entry; an empty value must never pass as a valid score. This rule binds all future DUPR integration code.
Undoing a result wipes the whole match (winner, score, sets); the full history stays in mcp_audit.

6. Core data chain today (at audit time)

Link	State
Event → entries	The entries table is used only by the MCP flow; live events have zero entry rows
Entries → draw	Page flow derives the draw live from the roster; MCP materializes mcp_bracket. Two tracks coexist
Draw → matches	No matches table; matches are derived results or objects inside mcp_bracket
Matches → scores → advancement	Implemented in MCP (scores, winner, auto-advance, cascading undo, mcp_audit); never used on a real event
Completion / archive	Missing; the current end state is the deleted_at soft delete

7. Layered roadmap

Now (this spec): field canonicalization, player_id, locking. All three land in one server choke point; no bulk migration of old data.

Before the first real-event test: entry review and check-in state, withdrawals, referee assignment, scheduling fields (court / scheduled_at), score correction flow.

Commercial stage: multi-tenant organizers, a standalone matches table, DUPR account linking, notifications. Trigger for splitting matches into their own table: one real event run end to end, plus an actual need for cross-match queries or concurrent multi-referee scoring.

Principle: not everything belongs in the event JSON. Scores, entries and player identity will eventually be separate entities — but the timing is decided by evidence from real events, not by imagination.