Skip to content

SwissTennis Data-Import Integration — Implementation Plan

Owner: Morgan (Solution Architect) Version: 0.1 (2026-05-23) Status: First-cut implementation plan, awaiting PO sign-off on the decision points called out in §11. Companion / requirements doc: swisstennis-api-integration.md (v0.3, Alex + Rafael). Read that first — this document is the buildable counterpart and assumes the requirements spec is fully resolved (§8.1, §8.2, §8.3, §8.4, §8.8 all closed). Scope: Phase 1 only (anonymous Advantage servlets + per-tournament background sync). Phase 2 (organizer-credential auth + Calendar/Hasura) is touched only via the one-paragraph door-opener in §10.

This plan translates the requirements into a concrete buildable architecture: package layout, schema migrations, transactional data flow, sync scheduler, error handling, caching, test coverage targets, GitLab issue breakdown, and consult sequencing.

1. Working assumptions (carried forward, not re-litigated)

Per the PO's dispatch note, Morgan accepts the following Rafael judgement calls as binding:

  1. *Final flag handling = import-with-warning (not refuse-import). See requirements §8.1(d). The importer resolves DrawFormat from non-final flags only and surfaces a per-event warning when any evtNoAdFinal / evtShortSetsFinal / evtMatchTiebreakFinal is non-zero.
  2. ageCategoryEvtIdAgeCategory2 divergence assumed absent. Sam runs a probe at impl time (see requirements §8.2(d)) to confirm; until a divergent example surfaces, slot 2 is ignored for Draw.name and a "should never happen" diagnostic is logged if slot1.agcName ≠ slot2.agcName.
  3. Draw.draw_format_locally_overridden is required in the first migration (per requirements §8.5 follow-up). Without it the merge contract silently stomps a manually chosen F6/F7 on re-import.

Additional binding constraints from the dispatch:

  • Anonymous Advantage servlets only in Phase 1.
  • Bulk insert event mode fails loudly — DisplayBulkMatches is never called.
  • Polling cadence: 60 s in-window, 15 min pre-tournament, one final pull at trnEnd + 1 h, then stop.
  • Lang=DE is pinned for Phase 1.

2. Package / module layout

2.1 Where the import code lives

A new workspace package packages/swisstennis is created. Rationale:

  • packages/scoring is the domain math layer (DrawFormat validation, RR standings tiebreak, bracket advancement). The SwissTennis importer is not domain math — it is an external-IO adapter. Keeping it in scoring would pollute the pure-function boundary and force a new dependency on node:fetch / HTTP into the scoring package's runtime.
  • packages/db is the schema + typed query helpers layer. The importer writes to the DB but is not generic schema code — it owns merge logic and audit-trail bookkeeping specific to the SwissTennis integration. Embedding it in packages/db couples the schema package to an external surface.
  • packages/trpc/src/routers/import.ts is the existing offline (XLS/PDF) importer. That router stays. The new SwissTennis tRPC procedures live alongside it (see §2.3) but call out to packages/swisstennis for all the heavy lifting. The router stays a thin glue layer, consistent with the rest of the codebase.

The new package is dependency-free of apps/web (Next.js) and dependency-free of packages/trpc. It depends on @courtsidedesk/db (for schema types and for the typed Drizzle client passed in by callers) and @courtsidedesk/scoring (for the MatchResult Zod schema — write-time validation).

2.2 Public surface (the entry points consumers call)

packages/swisstennis/
├── package.json                    # name: @courtsidedesk/swisstennis
├── src/
│   ├── index.ts                    # barrel — re-exports the public API
│   ├── client.ts                   # HTTP fetcher (anonymous Advantage servlets)
│   ├── urls.ts                     # URL builders, ID parser (URL → trnId)
│   ├── cache.ts                    # response cache (body-hash dedupe + TTL)
│   ├── parsers/
│   │   ├── tournament.ts           # TournamentDisplay → { header, events, players }
│   │   ├── draw.ts                 # DisplayDraw → { cells, topology, results }
│   │   ├── pools.ts                # DisplayPools → { pools, standings, rr matches }
│   │   ├── player-whitelist.ts     # PII whitelist projection (§4.4)
│   │   ├── date.ts                 # Java-Calendar struct → UTC Date
│   │   ├── score.ts                # "6/3 6/2" + "10/8" → MatchResult
│   │   └── draw-name.ts            # §8.2 composer
│   ├── mappers/
│   │   ├── draw-format.ts          # §8.1 SwissTennis flags → DrawFormat row
│   │   ├── tournament.ts           # raw → upsert payload
│   │   ├── draw.ts                 # raw → upsert payload
│   │   ├── match-ko.ts             # KO cells → match-row payloads + topology
│   │   ├── match-rr.ts             # RR rrmatches → match-row payloads
│   │   └── pool.ts                 # pools + standings payloads
│   ├── merge/
│   │   ├── tournament.ts           # upsert + merge rules (§6.2)
│   │   ├── draw.ts                 # upsert + draw_format_locally_overridden
│   │   ├── player.ts               # upsert on swisstennis_person_id → license_number fallback
│   │   ├── registration.ts         # upsert keyed on (player_id, draw_id); preserve manual rows
│   │   ├── match.ts                # KO + RR upsert; preserve court_id; respect locally_overridden flags
│   │   ├── pool.ts                 # upsert pools and standings
│   │   └── audit.ts                # ImportEvent row writer
│   ├── pipeline/
│   │   ├── one-shot.ts             # US-ST-01 orchestrator (single trnId)
│   │   ├── event-bracket.ts        # US-ST-02 orchestrator (single eventId)
│   │   └── sync.ts                 # US-ST-04 orchestrator (one poll cycle)
│   ├── scheduler/
│   │   ├── cadence.ts              # cadence resolution: pre / in-window / final / stopped
│   │   └── dispatch.ts             # the function pg_cron-invoked-via-tRPC calls each minute
│   ├── errors.ts                   # typed error union (NetworkError, UnsupportedMode, ...)
│   ├── observability.ts            # structured-log helpers, metric counters
│   └── types/
│       ├── raw.ts                  # RawIoTournament, RawIoPlayer (Record<string,unknown> at the boundary)
│       └── parsed.ts               # internal post-parse types
└── test/                           # vitest unit tests + fixtures
    ├── fixtures/                   # captured JSON from trn 158149 + trn 154477
    └── *.test.ts

Public surface (what index.ts re-exports):

  • importTournamentOneShot({ db, trnIdOrUrl, triggeredBy }): Promise<OneShotResult> — US-ST-01.
  • importEventBracket({ db, drawId, triggeredBy }): Promise<EventBracketResult> — US-ST-02 (requires the Draw row to already have swisstennis_event_id).
  • syncTournamentOnce({ db, tournamentId, kind: 'background' | 'manual' }): Promise<SyncResult> — US-ST-04 single poll cycle.
  • parseSwissTennisTournamentUrl(input: string): { trnId: number } | { error: string } — URL/ID parser (also exported for the UI to validate input).
  • dispatchScheduler({ db, now }): Promise<DispatchResult> — the function pg_cron triggers (via tRPC) every minute; see §5.

Nothing else is part of the public surface. Parsers, mappers, and the merge layer are internal; consumers go through the three pipeline orchestrators and the scheduler dispatch.

2.3 tRPC router placement

A new router file packages/trpc/src/routers/swisstennis.ts is added (kept separate from the existing import.ts for the offline path). It exposes:

Procedure Auth Calls into packages/swisstennis
importTournament (mutation) protectedProcedure importTournamentOneShot
importEventBracket (mutation) protectedProcedure importEventBracket
syncNow (mutation) protectedProcedure syncTournamentOnce({ kind: 'manual' })
setSyncEnabled (mutation) protectedProcedure toggles Tournament.swisstennis_sync_enabled
listImportEvents (query) protectedProcedure reads ImportEvent rows for the tournament
dispatchScheduler (mutation) internal-only — protected by a shared secret header, NOT protectedProcedure dispatchScheduler

The dispatchScheduler mutation is the only call surface for pg_cron (see §5). It is auth-gated by a x-swisstennis-scheduler-secret header checked in the procedure body. The secret is a new env var (SWISSTENNIS_SCHEDULER_SECRET).

3. Database schema migration

Concrete column / entity additions, ordered as a single Drizzle migration (packages/db/drizzle/0010_swisstennis_integration.sql — number is illustrative; reserve at impl time).

Schema-source files updated in lockstep under packages/db/src/schema/. Owner of every change: Sam.

3.1 New columns on existing tables

Table Column Type Default Index? Notes
tournaments swisstennis_tournament_id integer null unique nullable (tournaments_swisstennis_tournament_id_unique_idx) Upsert key. Nullable because manually created tournaments don't have one.
tournaments swisstennis_sync_enabled boolean false no AC-04.1 opt-in toggle.
tournaments swisstennis_last_synced_at timestamptz null no AC-04.7. Updated on every successful poll cycle (no-op-deduped cycles still update this — "we checked, nothing changed").
tournaments swisstennis_sync_stopped_at timestamptz null no Set to non-null when the trnEnd + 1h final pull completes (AC-04.2). The scheduler skips tournaments with swisstennis_sync_stopped_at IS NOT NULL.
tournaments swisstennis_consecutive_failures integer 0 no AC-04.6. Reset to 0 on a successful poll. UI alarms at >= 5 (Morgan-confirmed N).
tournaments swisstennis_locally_edited boolean false no §6.2 — when true, header fields (name, dates) are not overwritten on re-import.
tournaments location text null no §5.1 — round-trips trnLocation. Display-only.
tournaments organizer_club text null no §5.1 — round-trips trnOrganizer. Display-only. Named organizer_club (not organizer) to avoid confusion with tournaments.organizer_id (the Supabase Auth user).
tournaments source record_source (existing enum) 'MANUAL' no Set to 'SCRAPE' for SwissTennis-imported tournaments. ENTITY-001 currently lacks a source column; this aligns it with Player/Match.
draws swisstennis_event_id integer null unique nullable (draws_swisstennis_event_id_unique_idx) Upsert key.
draws draw_format_locally_overridden boolean false no §8.1 / §6.2. Set automatically the first time an organizer changes the format away from the importer-derived value.
players swisstennis_person_id integer null unique nullable (players_swisstennis_person_id_unique_idx) Distinct from license_number. Used as the primary upsert key (license_number is fallback).
matches swisstennis_match_id integer null unique nullable (matches_swisstennis_match_id_unique_idx) Populated for RR matches (rRMatchId). NULL for KO.
matches swisstennis_venue_label text null no §5.3 / §6.5 — denormalised; does not resolve to Court.
matches schedule_locally_overridden boolean false no §6.2 — when true, planned_start_time / swisstennis_venue_label are not overwritten on re-import.
matches result_locally_entered boolean false no §6.2 — when true, the locally-entered result is preserved and a "SwissTennis disagrees" warning is surfaced if the source has a different result.
registrations source record_source (existing enum) 'MANUAL' no §6.2 — manually added registrations are preserved on re-import via this flag.

3.2 New entities

pools (new table — implements requirements §5.5 + §8.5)

Column Type Notes
id uuid PK
tournament_id uuid FK → tournaments.id ON DELETE CASCADE Scoping for RLS.
draw_id uuid FK → draws.id ON DELETE CASCADE The pool's parent Draw (which is in ROUND_ROBIN mode).
name text NOT NULL Pass-through of polName.
swisstennis_pool_id integer NULL UNIQUE (partial: WHERE swisstennis_pool_id IS NOT NULL) Upsert key.
created_at, updated_at timestamptz Standard.

Indexes: (draw_id), (tournament_id), partial unique on swisstennis_pool_id.

pool_standings (new table — denormalised pool-member stats; cached, not the source of truth)

Column Type Notes
id uuid PK
tournament_id uuid FK → tournaments.id ON DELETE CASCADE Scoping for RLS.
pool_id uuid FK → pools.id ON DELETE CASCADE
player_id uuid FK → players.id ON DELETE RESTRICT
final_rank integer From plpRank.
force_rank integer From plpForceRank (0 = computed normally; do not treat as rank 0).
seed_position integer From plpPosition.
wins_total integer From plpNbVictories.
wins_excluding_wo integer From plpNbVictoriesNoWO.
matches_played integer From plpNbMatches.
sets_won / sets_lost integer
games_won / games_lost integer
created_at, updated_at timestamptz

Indexes: (pool_id), unique (pool_id, player_id).

Why a separate table rather than denormalising onto Registration: Standings are per-pool aggregates that update independently of registration state. Denormalising onto Registration would force the importer to touch the registration row on every standings change, polluting the audit trail. The standings table is the importer's responsibility; CSD's own scoring package can also write to it from packages/scoring/src/standings.ts when the organizer enters results locally, keeping the two import paths converged. This is the smaller blast radius.

swisstennis_import_events (new table — audit trail per §7.2)

Column Type Notes
id uuid PK
tournament_id uuid FK → tournaments.id ON DELETE CASCADE Scoping.
kind new enum swisstennis_import_kind — ONE_SHOT / EVENT_DRAW / BACKGROUND_SYNC / MANUAL_SYNC §7.2
triggered_by uuid NULL User ID; NULL for background sync. No FK (Supabase Auth users live in auth.users which we do not reference directly from public-schema tables — same pattern as tournaments.organizer_id).
started_at timestamptz NOT NULL
finished_at timestamptz NULL
status new enum swisstennis_import_status — SUCCESS / PARTIAL / FAILED / NO_OP NO_OP added for the body-hash-dedupe case where the cycle ran cleanly but wrote nothing.
endpoints_called jsonb NOT NULL Array of { url, status, body_sha256, ms }.
summary jsonb NOT NULL { players_created, players_updated, registrations_created, matches_created, matches_updated, results_imported, pools_created, pools_updated, errors: [{ event_id?, reason, message }], warnings: [{ event_id?, kind: 'format_guessed' | 'final_override_ignored' | 'unsupported_mode' | ..., message }] }.
error text NULL Top-level error message when status = FAILED.
created_at timestamptz Standard.

Indexes: (tournament_id, started_at DESC) for the "list import events" query.

No raw IoPlayer payloads stored — only the SHA-256 hash of the raw response body lives in endpoints_called. The whitelisted projection is implicit in what was written to the DB.

3.3 Realtime publications

Add to packages/db/src/policies/realtime.sql:

alter publication supabase_realtime add table public.pools;
alter publication supabase_realtime add table public.pool_standings;
-- swisstennis_import_events deliberately NOT added: audit trail is queried on demand, not pushed.

matches, draws, registrations, tournament_player_status are already on the publication. Background sync's writes propagate to existing signage screens for free.

3.4 RLS policies

  • pools, pool_standings: same scoping pattern as draws — tournament-collaborator read + organizer-or-admin write.
  • swisstennis_import_events: organizer-or-admin read; only the service role writes (the scheduler dispatcher and the tRPC mutations run under the service role for these writes).

3.5 Migration script outline

-- 0010_swisstennis_integration.sql

-- 1. New enums.
do $$ begin
  create type swisstennis_import_kind as enum
    ('ONE_SHOT', 'EVENT_DRAW', 'BACKGROUND_SYNC', 'MANUAL_SYNC');
exception when duplicate_object then null; end $$;

do $$ begin
  create type swisstennis_import_status as enum
    ('SUCCESS', 'PARTIAL', 'FAILED', 'NO_OP');
exception when duplicate_object then null; end $$;

-- 2. Tournaments columns.
alter table tournaments add column if not exists swisstennis_tournament_id integer;
alter table tournaments add column if not exists swisstennis_sync_enabled boolean not null default false;
alter table tournaments add column if not exists swisstennis_last_synced_at timestamptz;
alter table tournaments add column if not exists swisstennis_sync_stopped_at timestamptz;
alter table tournaments add column if not exists swisstennis_consecutive_failures integer not null default 0;
alter table tournaments add column if not exists swisstennis_locally_edited boolean not null default false;
alter table tournaments add column if not exists location text;
alter table tournaments add column if not exists organizer_club text;
alter table tournaments add column if not exists source record_source not null default 'MANUAL';
create unique index if not exists tournaments_swisstennis_tournament_id_unique_idx
  on tournaments (swisstennis_tournament_id)
  where swisstennis_tournament_id is not null;

-- 3. Draws columns.
alter table draws add column if not exists swisstennis_event_id integer;
alter table draws add column if not exists draw_format_locally_overridden boolean not null default false;
create unique index if not exists draws_swisstennis_event_id_unique_idx
  on draws (swisstennis_event_id)
  where swisstennis_event_id is not null;

-- 4. Players columns.
alter table players add column if not exists swisstennis_person_id integer;
create unique index if not exists players_swisstennis_person_id_unique_idx
  on players (swisstennis_person_id)
  where swisstennis_person_id is not null;

-- 5. Matches columns.
alter table matches add column if not exists swisstennis_match_id integer;
alter table matches add column if not exists swisstennis_venue_label text;
alter table matches add column if not exists schedule_locally_overridden boolean not null default false;
alter table matches add column if not exists result_locally_entered boolean not null default false;
create unique index if not exists matches_swisstennis_match_id_unique_idx
  on matches (swisstennis_match_id)
  where swisstennis_match_id is not null;

-- 6. Registrations column.
alter table registrations add column if not exists source record_source not null default 'MANUAL';

-- 7. New tables: pools, pool_standings, swisstennis_import_events.
--    Standard create table ... if not exists statements with FKs + indexes.

-- 8. RLS policies for the three new tables.

-- 9. Realtime publication adds for pools + pool_standings.

Drizzle schema source files updated:

  • packages/db/src/schema/tournaments.ts — add the seven new columns + source.
  • packages/db/src/schema/draws.ts — add the two new columns.
  • packages/db/src/schema/players.ts — add swisstennis_person_id.
  • packages/db/src/schema/matches.ts — add the four new columns.
  • packages/db/src/schema/registrations.ts — add source.
  • packages/db/src/schema/pools.ts (new file).
  • packages/db/src/schema/poolStandings.ts (new file).
  • packages/db/src/schema/swisstennisImportEvents.ts (new file).
  • packages/db/src/schema/enums.ts — add the two new enums.
  • packages/db/src/schema/index.ts — barrel updates.

3.6 Resolution of requirements §8.5 open questions

  • Tournament.location — added as a nullable text column (round-trips trnLocation for display).
  • Tournament.organizer_club — added as a nullable text column (round-trips trnOrganizer; named to avoid colliding with organizer_id).
  • Tournament.source — added using the existing record_source enum.
  • Pool / PoolStanding as separate tables (not denormalised on Registration) — see rationale in §3.2.
  • Draw.draw_format_locally_overridden — added per Rafael's §8.1 follow-up.

4. Data flow diagram

4.1 US-ST-01 — one-shot tournament import

Organizer (web UI)
  │
  │  paste "https://www.mytennis.ch/de/turniere/158149" or "158149"
  │  click "Import from SwissTennis"
  ▼
tRPC `swisstennis.importTournament({ trnIdOrUrl })`
  │
  ├─ parseSwissTennisTournamentUrl(input)              → { trnId: 158149 }
  │                                                       (fail-fast on malformed input)
  │
  ▼
importTournamentOneShot({ db, trnIdOrUrl, triggeredBy })
  │
  ├─ insert ImportEvent row (status=STARTED implied by finished_at=null)
  │
  ├─ HTTP fetch via packages/swisstennis/client.ts:
  │     GET /TournamentDisplay?tournament=Id158149&outputFormat=JSON&Lang=DE
  │     - cache lookup by URL → SHA-256(body) (response cache, §6)
  │     - timeout 10 s, exponential backoff on 5xx (1/2/4/8/16 s)
  │     - 301 detection → throw EndpointDeprecatedError
  │
  ├─ parsers/tournament.ts:
  │     raw JSON → {
  │       header: ParsedTournamentHeader,
  │       events: ParsedEvent[],            // includes mode + flags + name composition inputs
  │       playersByEvent: Map<eventId, ParsedWhitelistedPlayer[]>
  │     }
  │     - whitelist projection runs HERE (parsers/player-whitelist.ts)
  │     - raw IoPlayer never escapes the parser
  │     - unsupported event modes are NOT filtered out here — they survive into the merge layer
  │       so the audit trail records them
  │
  ├─ mappers/tournament.ts → tournament upsert payload
  ├─ mappers/draw.ts       → draw upsert payloads (one per non-cancelled event)
  │                          - mapDrawFormat() from mappers/draw-format.ts (§5.2.1 replacement table)
  │                          - composeDrawName() from parsers/draw-name.ts (§8.2)
  │                          - unsupported event modes are tagged with `unsupportedMode: 'Bulk insert' | string`
  │                            and DO NOT produce a draw payload — they appear in the summary's errors[]
  ├─ mappers/player + registration → upsert payloads
  │
  ▼
db.transaction(async tx => {
  │
  ├─ merge/tournament.ts:   upsert by swisstennis_tournament_id
  ├─ merge/draw.ts:         upsert by swisstennis_event_id; preserve draw_format_id if locally_overridden
  ├─ merge/player.ts:       upsert by swisstennis_person_id (fallback license_number);
  │                         overwrite whitelisted fields
  ├─ merge/registration.ts: upsert by (player_id, draw_id); preserve manual rows (source='MANUAL')
  │
  └─ merge/audit.ts:        finalize ImportEvent row (finished_at, status, summary, endpoints_called)
})
  │
  ▼
return OneShotResult to UI:
  {
    tournamentId,
    summary: { players_created, players_updated, ..., warnings, errors }
  }
  │
  ▼
Supabase Realtime emits row-level events for tournaments / draws / registrations
  - any signage screen already mounted on this tournament rebroadcasts
  - the organizer's bracket page re-renders via the existing Realtime subscriptions

Transactional boundary: the entire merge runs in a single db.transaction. If any merge step throws, the whole import is rolled back and the audit-trail row is written with status = FAILED. The HTTP fetches happen outside the transaction (network calls inside a Postgres transaction would hold a connection unnecessarily).

The audit-trail row is written in two phases: the started-row is inserted outside the transaction (so failures still produce a visible audit entry), then the finalisation update happens inside the transaction so a transactional rollback also rolls back the summary. If the transaction rolls back and the started-row update is lost, a separate post-transaction step writes a FAILED summary referencing the started row's id. This is the same pattern the offline import.ts router uses.

4.2 US-ST-02 — per-event bracket import

Organizer (Draw page)
  │  click "Import from SwissTennis" on a Draw that has swisstennis_event_id set
  ▼
tRPC `swisstennis.importEventBracket({ drawId })`
  │
  ▼
importEventBracket({ db, drawId, triggeredBy })
  │
  ├─ load Draw → resolve swisstennis_event_id (404 if absent)
  ├─ insert ImportEvent (kind='EVENT_DRAW')
  │
  ├─ fetch DisplayDraw or DisplayPools depending on Draw.type
  │  (Draw.type was set at one-shot import time from ioEventMode.evmName)
  │
  ├─ parsers/draw.ts OR parsers/pools.ts
  │
  ├─ mappers/match-ko.ts OR mappers/match-rr.ts + mappers/pool.ts
  │
  ▼
db.transaction:
  ├─ merge/match.ts:    upsert; preserve court_id, schedule_locally_overridden, result_locally_entered
  ├─ merge/pool.ts:     upsert pools + standings (RR only)
  └─ merge/audit.ts:    finalize ImportEvent

4.3 US-ST-04 — background sync (one cycle)

pg_cron (every minute, in the keystone-managed Postgres)
  │
  │  SELECT cron.schedule('swisstennis-scheduler', '* * * * *',
  │     'SELECT net.http_post(url, headers, body) ...');
  │
  ▼
HTTP POST  https://<app-host>/api/trpc/swisstennis.dispatchScheduler
            with header x-swisstennis-scheduler-secret: <SWISSTENNIS_SCHEDULER_SECRET>
  │
  ▼
tRPC `swisstennis.dispatchScheduler({})` (secret-gated, NOT user-protected)
  │
  ▼
dispatchScheduler({ db, now })
  │
  ├─ load all tournaments where swisstennis_sync_enabled = true
  │                       AND swisstennis_sync_stopped_at IS NULL
  │
  ├─ for each tournament, scheduler/cadence.ts decides:
  │     - is_in_window (trnBegin 00:00 → trnEnd 23:59 Europe/Zurich)?            → due every 60 s
  │     - is_pre_tournament (now < trnBegin 00:00 Europe/Zurich)?                → due every 15 min
  │     - is_final_pull_due (now >= trnEnd + 1h Europe/Zurich AND not stopped)?  → due once, then stop
  │     - otherwise                                                              → skip
  │
  │   Due-ness is checked against tournaments.swisstennis_last_synced_at and the
  │   target cadence. With pg_cron firing every minute we naturally get 60 s
  │   in-window cadence and ~15 min pre-tournament cadence (the dispatcher just
  │   skips most minutes for pre-tournament rows).
  │
  ├─ for each due tournament: await syncTournamentOnce(...)
  │   (concurrency-capped: max 5 in-flight tournaments at once — see §7.1)
  │
  ▼
syncTournamentOnce({ db, tournamentId, kind: 'BACKGROUND_SYNC' })
  │
  ├─ insert ImportEvent (kind, started_at)
  │
  ├─ fetch TournamentDisplay (1 request)
  ├─ for each non-cancelled supported event: fetch DisplayDraw or DisplayPools
  │     (concurrency-capped within a tournament: max 4 in-flight events)
  │     body-hash dedupe applied per URL — unchanged responses skip parse + merge
  │
  ├─ if every fetched body hashed-unchanged:
  │     - update tournaments.swisstennis_last_synced_at
  │     - finalise ImportEvent (status='NO_OP', summary populated with hashes)
  │     - early return
  │
  ├─ otherwise: parsers → mappers → db.transaction(merge/*) as in US-ST-01/02
  │
  ├─ if this is the trnEnd+1h pull:
  │     - set tournaments.swisstennis_sync_stopped_at = now
  │     - log "background sync stopped (trnEnd+1h reached)"
  │
  ├─ reset tournaments.swisstennis_consecutive_failures = 0 on success
  ├─ increment on failure; if >= 5, emit user-surfaceable health-degraded alert
  │
  └─ Supabase Realtime emits row events as usual

Transactional boundary per tournament-per-cycle: one transaction per tournament. A single cycle that hits 10 tournaments produces 10 independent transactions; one tournament's failure does not roll back another's writes.

5. Sync scheduler design

v0.2 correction — scheduler substrate revised to keystone systemd timer (2026-05-24, Casey).

The §5.1–§5.5 text below describes the v0.1 pg_cron-inside-the-keystone-Postgres design that this document originally specified. That design is no longer what runs in production. During #180 it surfaced that pg_cron + pg_net were retired from the keystone-Postgres contract (keystone#56 + ADR-0007) — the presence_reset_cron.sql precedent this section leans on was already a dead file by the time #180 opened. The substrate that does run is a systemd timer on the keystone platform host, firing every 60 s, that issues an HTTPS POST to the same secret-gated tRPC mutation (swisstennisScheduler.dispatchScheduler) over the public Caddy frontend. The cadence rules in §5.3, the per-cycle summary shape, and the failure-mode handling in §5.5 are unchanged — only the heartbeat substrate moved. The wrapper, unit naming, secret-handling, and log-tailing conventions are owned by the keystone platform.

Current operational documentation: docs/runbooks/swisstennis-scheduler.md. Read that first for anything operational. The prose below is retained as the historical architectural record of the v0.1 choice and the reasoning we would have followed if pg_cron had still been available.

5.1 Choice

pg_cron job inside the keystone-managed Postgres, firing every minute, that issues an HTTP POST to a secret-gated tRPC mutation on the Next.js app.

5.2 Rationale

The CSD codebase already has a precedent for pg_cron — see packages/db/src/policies/presence_reset_cron.sql. The keystone platform also provides cron and pg_net.http_post() via the Supabase-compatible self-hosted Postgres on kst1.wagen.io (per tech-stack-morgan.md Section 3 and gitlab.com/wagen-public/keystone-public/-/blob/main/docs/runbooks/12-platform-deploy-target.md). Re-using that machinery avoids introducing:

  • A second process tier (a Node.js worker) that would need its own ops, restart policy, health probe, log aggregation, and Casey-time. The Next.js app is the only runtime today and we want to keep it that way for Phase 1.
  • A new dependency on Redis or a job queue. None exists today and we deliberately keep the dependency surface small per the tech-stack doc Section 3 ("Removed from architecture: Upstash Redis").

A periodic in-Postgres trigger that wakes the Next.js app over HTTP is the lowest-marginal-cost option that respects the existing platform contract. The dispatcher itself is stateless — pg_cron is the heartbeat; all decision logic ("is this tournament due?") is in scheduler/cadence.ts reading current state from the DB.

5.3 Cadence resolution

The cron job runs every minute. The dispatcher resolves due-ness per tournament:

Tournament state Due condition
swisstennis_sync_enabled = false Never. Skip.
swisstennis_sync_stopped_at IS NOT NULL Never. Skip. (Manual "Sync now" still works — it does not go through this code path.)
Now < start_date 00:00 Europe/Zurich (pre-tournament) Due if swisstennis_last_synced_at IS NULL OR (now - last_synced_at) >= 15 minutes.
Now between start_date 00:00 and end_date 23:59 Europe/Zurich (in-window) Due if swisstennis_last_synced_at IS NULL OR (now - last_synced_at) >= 60 seconds.
Now between end_date 23:59 and end_date 23:59 + 1 hour Europe/Zurich (after-window-grace) Not due. We do not poll between the last 60 s in-window slot and the trnEnd+1h final pull, to avoid a flurry of polls at the end of day.
Now >= end_date 23:59 + 1 hour Europe/Zurich AND swisstennis_sync_stopped_at IS NULL Due once (the final pull). After this cycle completes successfully (or fails terminally), set swisstennis_sync_stopped_at = now.

All time math runs in Europe/Zurich and converts via AT TIME ZONE 'Europe/Zurich' where needed (DST-safe; date_start_date is a date, not a timestamp).

5.4 Cost / ops impact

  • pg_cron adds 1 cron row firing once a minute. Existing cron entries: presence-daily-reset (hourly). Adding one minute-cron is well within the keystone-managed Postgres's capacity.
  • Each minute-fire is a single HTTP POST locally on the same VPS (Caddy → Next.js). Localhost round-trip, ~tens of ms even with a no-op result.
  • During an active tournament with 16 events and ~17 requests per cycle, peak outbound traffic to SwissTennis is 17 req/min per tournament, ~170 req/min worst-case at 10 concurrent active tournaments. Comfortably below any sane rate-limit threshold.
  • No new infrastructure components. Casey only needs to (1) verify pg_cron + pg_net are enabled on the keystone Postgres for the CSD app (they already are per the presence-reset cron), (2) add the SWISSTENNIS_SCHEDULER_SECRET env var via the keystone deploy contract.

5.5 Failure modes

Failure Behaviour
App is down when pg_cron fires The POST fails. pg_cron logs the error. The next minute's fire is a fresh attempt; no backlog accumulates because each cycle is idempotent.
Two cron fires overlap (a cycle takes >60 s) The dispatcher uses an advisory lock per tournament (pg_advisory_xact_lock(hashtext('swisstennis:' || tournament_id))) to prevent a second concurrent cycle on the same tournament. Different tournaments can proceed concurrently.
pg_cron itself misfires (server reboot etc.) The next fire catches up. Last-synced timestamp drives due-ness, not a tick counter.
Scheduler secret leaks Rotate via the keystone secret-rotation flow; old fires fail; pg_cron entry continues with the new secret on next deploy.

6. Caching strategy

6.1 Choice

In-memory per-process body-hash cache with a 60 s TTL, scoped to a single Next.js process.

6.2 Rationale

Compared with the three options floated in the dispatch:

  • In-memory — single-process today (the keystone deploy runs CSD as one compose service). Multi-process becomes a concern only if/when we horizontally scale; at v1's traffic profile (single VPS, single Next.js instance) it is a non-issue, and we explicitly do not need cross-process coherence here because the cache is a transient bandwidth optimisation, not a correctness primitive.
  • Redis — adds a new infra dependency that the tech-stack doc explicitly removed at v1. Not justified to re-add for what is effectively a "skip parse if the bytes are identical" optimisation.
  • Postgres-backed — slow (round-trips defeat the purpose of caching) and pollutes the DB with transient state. The audit-trail table already stores per-cycle hashes, but it stores them for forensics, not for hot-path dedupe.

The body-hash dedupe primarily serves two purposes: (1) avoid re-parsing identical responses across consecutive poll cycles, (2) skip the merge transaction entirely when the response is byte-for-byte identical. Both purposes are well-served by a per-process LRU.

6.3 Concrete shape

In packages/swisstennis/src/cache.ts:

// LRU keyed by URL → { sha256, fetchedAt, parsedRef? }
//   - sha256: SHA-256 of response body bytes
//   - fetchedAt: wall-clock fetched timestamp
//   - parsedRef: WeakRef<ParsedResponse> | undefined (best-effort parse-cache)
//
// TTL: 60 s. After TTL the entry is considered stale (parsed payload discarded
// from the WeakRef; the hash entry is kept for body-hash dedupe so consecutive
// fetches against the same URL within ~5 min still benefit from "unchanged"
// detection).
//
// Eviction: max 256 entries (16 events x 16 active tournaments worst case).

client.ts consults this cache:

  1. Issue the HTTP GET.
  2. Compute SHA-256 of the body.
  3. If the URL is in cache and the hash matches, return { unchanged: true, ...cached }.
  4. Else, parse + update the cache + return { unchanged: false, parsed }.

Conditional HTTP requests (If-Modified-Since / If-None-Match) are sent if the servlet honours them — Sam verifies during impl. If not, body-hash is the only dedupe mechanism. Either way the cache shape above is correct.

6.4 Cache invalidation

  • TTL-based, no explicit invalidation.
  • Process restart drops the cache. First poll after restart re-parses. Audit-trail row records this as a normal (non-NO_OP) cycle.

7. Error handling and observability

7.1 Failure taxonomy

Defined as a discriminated union in packages/swisstennis/src/errors.ts:

Class When Surfaced to UI? Logged?
NetworkError HTTP 5xx, timeout, connection reset Yes (toast on manual; silent on background until N=5 consecutive) Yes (structured)
EndpointDeprecatedError HTTP 301 → mytennis.ch Yes (banner: "SwissTennis has changed their public API. Background sync paused.") Yes (alert-worthy)
TournamentNotFoundError Empty IoTournament envelope (404-like) Yes (clear "tournament not found" error) Yes
UnsupportedEventModeError evmName != "Draw" | "Pool" Yes (per-event row in import summary) Yes (audit)
MalformedPayloadError JSON parse fails or required fields missing Yes (toast on manual; alert on N=5 consecutive background) Yes (with first 200 bytes of body + SHA)
MergeConflictError Local-edit conflict (result_locally_entered = true and SwissTennis disagrees) Yes (warning surfaced in summary) Yes (audit)

7.2 Distinguishing SwissTennis-side vs CSD-side failures

The structured log shape carries an explicit side field:

type SwissTennisLog =
  | { side: 'swisstennis'; kind: 'network' | 'protocol' | 'payload'; ... }
  | { side: 'csd';         kind: 'merge' | 'persistence' | 'validation'; ... };
  • side: 'swisstennis' is anything originating before the merge layer (HTTP, parse, whitelist).
  • side: 'csd' is anything in the merge / persistence / audit-write layer.

This drives the alerting split — DevOps's "SwissTennis is down" alarm fires only on side: 'swisstennis' 5-in-a-row patterns; "CSD persistence is broken" fires on any side: 'csd' error.

7.3 Structured log fields (every log line)

Field Source
swisstennis_op 'one_shot' | 'event_draw' | 'sync_cycle' | 'sync_dispatch'
tournament_id UUID (CSD-side)
swisstennis_trn_id numeric (SwissTennis-side) — present on every line for the duration of the op
swisstennis_event_id numeric, when an event-level op
import_event_id UUID — joins log lines to the audit-trail row
side 'swisstennis' | 'csd'
duration_ms on completion lines
endpoint_url on HTTP lines
http_status, body_sha256 on HTTP lines
error_kind, error_message on error lines

The logger is packages/swisstennis/src/observability.ts. It wraps the existing CSD pino-style logger (or whatever apps/web uses today — Sam adopts the existing pattern; there is no new logging library).

7.4 Metrics

Counters / histograms exposed via the same metrics endpoint Casey already runs (or, if none exists yet, exposed as log lines for log-based metrics on the keystone side):

  • swisstennis_imports_total{kind, status} counter.
  • swisstennis_import_duration_ms{kind} histogram.
  • swisstennis_endpoint_requests_total{endpoint, status} counter.
  • swisstennis_endpoint_duration_ms{endpoint} histogram.
  • swisstennis_tournament_health{tournament_id, state} gauge with state ∈ {connected, degraded, disabled, stopped}.

7.5 UI surfacing

(Jamie-owned, but spec'd here so Sam has a contract.)

Place What appears
Tournament list page "Create from SwissTennis" entry point next to "Create blank tournament" (US-ST-01).
Tournament settings page (a) Background-sync toggle. (b) "Last synced" timestamp (relative + absolute on hover). (c) "Sync now" button. (d) Integration health badge — green/connected, amber/degraded, red/disconnected, grey/stopped. (e) Link to "Import history" (the per-tournament list of ImportEvent rows).
Draw page "Import bracket from SwissTennis" button on Draws with swisstennis_event_id set; disabled with tooltip otherwise.
One-shot import summary Per-event row showing imported/skipped/failed status, with the failure reason inline (e.g. "Bulk insert mode not supported").
Match card on SCR-005 Subtle swisstennis_venue_label subtitle when set.
Import history detail Per-cycle drill-down: endpoints called (with hashes), summary counters, warnings list, errors list.

8. Test plan inputs for Riley

This section enumerates what needs coverage, ordered by risk. Riley turns this into the actual test plan with concrete cases.

8.1 Unit-test targets (packages/swisstennis/test/)

Parsers — must cover every documented shape variation:

  • parsers/player-whitelist.ts
  • Negative coverage as the headline test: assert that no whitelisted output object contains any key from the dropped-list (email, address, phone, birthdate, company, *2 partner fields, internal numerics) — even if a future typo accidentally re-introduces one. This is mandated by requirements §4.4.
  • Whitelisted projection covers a populated IoPlayer, a sparse IoPlayer (only ID + license + name), the self-referencing IoPlayer (where lastname is missing — requirements §4.4 footnote).
  • plySeedNb 0 vs > 0 → is_seeded false vs true.
  • plyCoeffF numeric vs plyRank fallback (Rafael open item §4.4).
  • parsers/date.ts
  • Java-Calendar 0-indexed month conversion: {month:4, day:23} → 2026-05-23 (May, not April). Requirements §8.6 — explicit test driving the verification.
  • DST boundary: a date in late March (Europe/Zurich DST start) and late October (DST end) — both should round-trip through UTC correctly.
  • parsers/score.ts
  • "6/3 6/2" → two NORMAL sets.
  • "6/3 2/6 10/8" → two NORMAL sets + matchTiebreak 10-8.
  • U+00A0 NBSP separator — same parse result as ASCII space.
  • "" / missing → null (not-yet-played).
  • "WO" patterns (per existing offline importer regex) → WALKOVER outcome.
  • parsers/draw.ts
  • Bracket-of-4 topology reconstruction (the verified example in requirements §5.3 step 6) — full ground-truth assertion against the worked example.
  • Bracket-of-8 topology.
  • BYE handling — first-round cell content == "BYE" → match with player2=null, status=COMPLETED, outcome=WALKOVER.
  • Cell with no name.person_nr (TBD slot) → match with player2_id=null.
  • parsers/pools.ts
  • RR pool standings extraction with a player having empty-string ioRRMatchRrmIdPlayer1Set (the documented shape-polymorphism case in requirements §5.5).
  • Pool partial-fill: evtSize=4 but pool has 3 actual members.
  • Per-RR-match: -1 sentinel for unplayed sets — drop from SetScore array.
  • WO flag handling: rrmPlayer1WO=1 → outcome=WALKOVER, winner=player2.
  • parsers/draw-name.ts — exhaustive over requirements §8.2 table:
  • MS A R3/R6 → "Herren R3-R6".
  • MS S3 (55+) R3/R6 → "Herren 55+ R3-R6".
  • WS A R1/R4 → "Damen R1-R4".
  • MS A R4/R1 (payload-order inverted) → "Herren R1-R4" (always low-to-high).
  • MD A R3/R6 → "Herren Doppel R3-R6".
  • MX A R3/R6 → "Mixed R3-R6".
  • XD A R3/R6 → "Mixed R3-R6" (alternate spelling accepted).
  • Junior MS U14 R6/R9 → "Herren U14 R6-R9".
  • agcName === null → suffix omitted + warning logged.
  • Unknown mtpName (e.g. "ZZ") → falls back to raw code + warning.
  • mappers/draw-format.ts — exhaustive over requirements §8.1 replacement table:
  • Every catalogued combination of (evtNoAd, evtShortSets, evtMatchTiebreak, bestOf, junior?) maps to the expected DrawFormat row.
  • Each *Final flag (3 of them, each in {0,1}) — verify the resolution ignores *Final for format choice and that a non-zero *Final produces a "final-match override ignored" warning.
  • Junior-vs-adult fallback split — agcName="U14" falls back to F2 (not F1); agcName="A" falls back to F1.
  • The previously-incorrect evtShortSets=1 AND evtNoAd=0 → F7 row is dropped — that combination falls into the fallback path and emits "format guessed" warning. Riley's test enumerates the dropped combination explicitly to lock the new behaviour.
  • urls.ts — URL parser:
  • Numeric ID "158149" → 158149.
  • SPA URL "https://www.mytennis.ch/de/turniere/158149" → 158149.
  • FR/IT/EN locale variants → same trnId.
  • Trailing slashes, query strings, fragments — all stripped.
  • Malformed input → { error: '...' }.

Merge layer — must cover every requirements §6.2 row:

  • merge/player.ts — upsert priority: swisstennis_person_id first, fallback license_number, fallback create.
  • merge/draw.ts
  • Importer-derived DrawFormat overwrites when draw_format_locally_overridden = false.
  • Importer-derived DrawFormat preserved when draw_format_locally_overridden = true.
  • merge/match.ts
  • court_id preserved on re-import.
  • planned_start_time overwritten when schedule_locally_overridden = false; preserved when true.
  • result overwritten when result_locally_entered = false; preserved + conflict warning when true and source disagrees.
  • KO matches keyed on (draw_id, alevel, rposition_lower); RR matches keyed on swisstennis_match_id.
  • merge/registration.ts
  • Manually-added registration (source = 'MANUAL') preserved on re-import.
  • SwissTennis-sourced registration upserts whitelisted fields.

Pipeline — orchestration tests:

  • pipeline/one-shot.ts
  • Happy path: 16-event tournament fully imports.
  • One event is Bulk insert → that event fails-loud with the correct user-facing message; the other 15 succeed; the audit trail records the failure.
  • HTTP 5xx on first try, success on retry — single ImportEvent row, retries logged in endpoints_called.
  • HTTP 5xx exhausted → status=FAILED, no DB writes.
  • Empty IoTournament → TournamentNotFoundError, no DB writes.
  • pipeline/sync.ts
  • All-unchanged poll → status=NO_OP, last_synced_at updated, no row writes.
  • One event changed → only that event's draw fetched/parsed, no full tournament re-parse (because TournamentDisplay was hash-unchanged but the event endpoint changed — actually re-read the spec: the spec fetches TournamentDisplay every cycle then per-event. So this test verifies "TournamentDisplay unchanged → skip player upsert but still hit per-event endpoints").
  • Final pull at trnEnd + 1h → swisstennis_sync_stopped_at set; next cycle no-ops because stopped.
  • Consecutive-failure counter increments correctly and resets on success.

8.2 Integration / e2e targets (apps/web/e2e/ Playwright)

  • e2e-01: Organizer pastes URL → import succeeds → tournament + draws + registrations visible in UI. Use a fixture-recorded HTTP response (vcr-style) — not a live SwissTennis call.
  • e2e-02: Background sync enabled → fixture clock advances → poll cycle runs → schedule change is reflected on signage screen via Realtime.
  • e2e-03: Local edit (override DrawFormat) → re-import → local choice preserved.
  • e2e-04: Bulk-insert event in fixture → one-shot import shows the per-event failure row clearly; the rest of the tournament imports.

8.3 Live-data smoke (manual, Drew)

Two reference tournaments per the dispatch:

  • trn 158149 (in-progress, 16 events, all KO) — covers the typical in-window cycle.
  • trn 154477 (completed, KO + RR mix, real scores) — covers result-attribution, pool standings, the verified worked example.

Drew runs each through the UI once per release and confirms parity with what the SwissTennis SPA shows.

8.4 Testing conventions (binding) — real-payload fixtures + eyes-on verification

These three rules are binding for every PR that touches the SwissTennis import path (parsers / topology / mappers / merge / pipeline). They are not aspirational — they were promoted to convention after three production regressions in May 2026 (#202, #206, #207) all shipped green-on-CI because synthetic fixtures matched the buggy code's expectations rather than what the live wire actually emits.

Rule 1 — Verbatim real-payload fixtures are mandatory. Every parser, mapper, or merge layer that consumes an HTTP-fetched external payload must have at least one test fixture that is byte-for-byte verbatim from the live anonymous endpoint. "Verbatim" means: captured with curl (or equivalent) directly to disk, then PII-sanitised per the §4.4 whitelist by in-place string replacement only — no re-emit through a JS object literal, no Unicode normalisation, no whitespace re-indentation, no key reordering. Hand-built fixtures and builder helpers remain valuable for edge-case coverage (BYEs, sentinel values, error paths) but cannot be the only or primary fixture for any parser. The verbatim fixture is the one that asserts "we parse what SwissTennis actually sends," everything else asserts "we handle the documented variations."

Rule 2 — A real-payload fixture must be present in the first PR. Any PR touching packages/swisstennis/src/parsers/, src/mappers/, src/topology/, or src/merge/ must include or extend at least one verbatim fixture in packages/swisstennis/test/fixtures/. When a new external endpoint or response shape is added, the verbatim fixture lands in the same PR as the parser — not as a follow-up. Reviewers (and CI guards, once added) should reject parser PRs whose only fixtures are hand-built.

Rule 3 — Eyes-on verification is required for any import-path change. Post-deploy verification of any change to the parser / topology / mapper / merge / pipeline layer requires one of:

  • (a) hands-on UI verification by a human on the deployed environment (csd-test or csd-prod), opening the affected tournament/draw/match and confirming the rendered output matches what the SwissTennis SPA shows; or
  • (b) a live DB query against an actually-imported tournament, confirming the affected columns are populated with non-empty / non-default values that match the upstream payload.

JSON-trace verification — statically reading the deployed code on disk against a dumped JSON payload — is not sufficient on its own. It failed twice in May 2026 (#206 and #207) by tracing through code paths that looked correct against a hand-built or normalised payload but silently no-op'd against the real wire format. Static trace is a useful triage step, never a sign-off step.

Evidence (three incidents, same class of failure):

Incident Commit Class of failure How it shipped How it was caught
#202 Iotto inner-wrapper 1c2e295 Hand-built fixtures were one envelope-level too shallow — matched the buggy parser's expected shape; live Io<UpperCase> wrapper had one more layer. Fixtures and parser were written together against the spec; both were equally wrong; tests green. csd-prod import failure (Stefan, eyes-on).
#206 Destination-cell schedule layout c64a31f Existing KO fixture used source-cell schedule layout; live trn 157798 puts schedule data on destination cells across all 8 court-bearing events. Auto-create-courts code (#205) shipped starved of input. #205 unit tests passed on the source-cell fixture; JSON-trace post-deploy looked correct. csd-test post-deploy UI verification (Drew, eyes-on).
#207 NBSP separator in name.content e143e10 Live name.content uses U+00A0 NBSP between every name token; hand-built fixtures and source-file ASCII normalisation made every space ASCII 0x20. A replace(/ /g, ' ') in the name-bridge no-op'd at runtime; the entire #203 name bridge silently failed in prod. Tests green against ASCII fixtures; Drew's JSON-trace against the dumped payload also looked clean (the dump tool had already normalised NBSP). csd-prod, eyes-on (Stefan), after Drew's JSON-trace verification had cleared it.

Common thread: all three shipped because the test artefact (fixture or static dump) was a re-encoding of the wire data through some tool that silently normalised the bytes that actually mattered. The only defence is the verbatim fixture (Rules 1–2) plus eyes-on or live-DB verification (Rule 3).

Implementation pointers:

  • The fixtures directory README at packages/swisstennis/test/fixtures/README.md documents the sanitiser convention and flags trn-155892-tournament-display.json as the worked example of a properly-captured, sanitised verbatim fixture (#202 retro).
  • When sanitising, prefer sed-style replacement of the PII tokens to a JS JSON.parse → mutate → JSON.stringify round-trip — the latter is what loses NBSP, key order, and structural whitespace.
  • When in doubt, diff the sanitised fixture against the raw curl output with diff -u and confirm the only changed lines are PII-bearing strings.

9. GitLab issue breakdown

Epic: SwissTennis Phase 1 — anonymous Advantage import + background sync

Tickets below are proposed — Nora files them when impl kicks off. Sizing: S = ~½ day, M = ~1-2 days, L = ~3-5 days. Dependency arrows are explicit; the same prefix (E1, E2, ...) groups tickets that can ship in parallel.

  • E1-A · Schema migration 0010 — SwissTennis columns + new tables. Owner: Sam. Size: M. New columns on tournaments / draws / players / matches / registrations, plus pools, pool_standings, swisstennis_import_events. Realtime publication adds. RLS policies. Dependency: none — first ticket. Blocks everything else.
  • E1-B · Scaffold packages/swisstennis workspace package. Owner: Sam. Size: S. package.json, tsconfig.json, vitest config, barrel index.ts. No logic yet. Dependency: none — runs parallel to E1-A.
  • E2-A · HTTP client + URL builder + ID/URL parser + response cache. Owner: Sam. Size: M. client.ts, urls.ts, cache.ts. Includes Java-Calendar month-indexing verification probe (requirements §8.6, see §11 below). Tests against fixture JSON. Dependency: E1-B.
  • E2-B · Parsers — tournament + draw + pools + score + date + whitelist + draw-name. Owner: Sam. Size: L. All of packages/swisstennis/src/parsers/*. Includes the PII-whitelist negative test (mandatory per requirements §4.4). Dependency: E1-B.
  • E2-C · Mappers — DrawFormat + draw-name + tournament/draw/match/pool payload builders. Owner: Sam. Size: M. Implements requirements §8.1 replacement table and §8.2 composition rule. Dependency: E2-B.
  • E3-A · Merge layer + audit-trail writer. Owner: Sam. Size: L. All of packages/swisstennis/src/merge/*. Implements requirements §6.2 merge contract end-to-end. Dependency: E1-A + E2-C.
  • E3-B · Pipeline orchestrators — one-shot + event-bracket + sync. Owner: Sam. Size: M. pipeline/*. Wires parsers + mappers + merge + audit into the three top-level functions. Dependency: E3-A.
  • E4-A · tRPC router — swisstennis.ts (importTournament, importEventBracket, syncNow, setSyncEnabled, listImportEvents). Owner: Sam. Size: M. Thin wrappers over the pipeline functions. Dependency: E3-B.
  • E4-B · tRPC procedure + secret guard — dispatchScheduler. Owner: Sam. Size: S. The scheduler entrypoint. Includes SWISSTENNIS_SCHEDULER_SECRET env-var plumbing. Dependency: E3-B.
  • E5-A · pg_cron schedule + pg_net.http_post job. Owner: Casey. Size: S. New file packages/db/src/policies/swisstennis_scheduler_cron.sql mirroring presence_reset_cron.sql. Coordination: confirm pg_net is enabled on the keystone Postgres. Dependency: E4-B. (Can be drafted before E4-B lands; just can't be applied to prod until the secret is set.)
  • E5-B · Keystone deploy contract — SWISSTENNIS_SCHEDULER_SECRET env var via the platform-deploy onboarding flow. Owner: Casey. Size: S. Per gitlab.com/wagen-public/keystone-public/-/blob/main/docs/runbooks/12-platform-deploy-target.md. Dependency: none — can run in parallel.
  • E6-A · UX: tournament list "Create from SwissTennis" entry point + URL input dialog. Owner: Jamie (design) → Taylor (impl). Size: M. Requirements §8.7. See §11 below for sequencing decision. Dependency: Jamie's design (which is unblocked now — see §11(e)).
  • E6-B · UX: tournament settings page — sync toggle, last-synced, sync now, health badge. Owner: Jamie → Taylor. Size: M. Dependency: E4-A.
  • E6-C · UX: draw page — "Import bracket from SwissTennis" button. Owner: Taylor. Size: S. Dependency: E4-A.
  • E6-D · UX: one-shot import summary screen with per-event failure rows. Owner: Jamie → Taylor. Size: M. Dependency: E4-A.
  • E6-E · UX: SwissTennis venue label on match card (SCR-005). Owner: Taylor. Size: S. Dependency: E1-A (column landed) — no API dependency.
  • E6-F · UX: import-history detail view. Owner: Jamie → Taylor. Size: M. Dependency: E4-A.
  • E7-A · Riley test plan + unit-test coverage. Owner: Riley. Size: L. Per §8 above. Dependency: each of the parser/mapper/merge tickets exposes its public API; Riley can pair with Sam on each.
  • E7-B · Live-data smoke runbook (Drew). Owner: Drew. Size: S. Two reference tournaments, repeatable checklist. Dependency: E4-A + E6-A available.
  • E8-A · Observability — structured-log shape + metrics counters + alerts. Owner: Sam (impl), Casey (alert thresholds). Size: M. Dependency: E3-B. Can ship before E5-A because alerts are useful even on manual imports.
  • E8-B · Doc — docs/help/ page for organizer-facing SwissTennis import instructions. Owner: Quinn. Size: S. Dependency: E6-A through E6-F shipped (so the screenshots are stable).

Dependency arrows (text-only DAG)

E1-A ─┬─────────────────────────────────────┐
      │                                     │
      └─► E3-A ─► E3-B ─► E4-A ─► E6-B / E6-C / E6-D / E6-F
                          │
                          └─► E4-B ─► E5-A ─► (prod-enabled)
                          │
                          └─► E7-A (test plan)
                          └─► E7-B (live-data smoke)
                          └─► E8-A (observability)
                          └─► E8-B (help docs)

E1-B ─► E2-A ─► (parsers HTTP fixtures)
      └► E2-B ─► E2-C ─► E3-A

E5-B and Jamie's E6-A design can run fully in parallel from day one.

10. Phase-2 door-opener

Phase 2 unlocks the authenticated Calendar/Hasura surface (organizer-supplied SwissTennis credentials). Three Phase-1 design choices keep that door open: (1) the HTTP client in packages/swisstennis/src/client.ts is parametrised on an auth?: SwissTennisAuth discriminated union — Phase 1 passes { kind: 'anonymous' }; Phase 2 adds { kind: 'organizer-credential', cookieJar, ... } without restructuring the call sites; (2) the audit-trail row stores endpoints_called as opaque URLs, so Phase 2's Hasura GraphQL operations slot in alongside the Advantage servlet hits without a schema change to swisstennis_import_events; (3) the merge layer keys on stable SwissTennis IDs (tournamentId, eventId, playerId, rRMatchId) which Hasura also exposes, so a Phase 2 Calendar pull that merges schedule data against the same matches will upsert correctly without rework. The one place we will want to revisit is the response cache shape — Phase 2 will need per-request cache keys that include the auth identity to avoid cross-organizer cache hits. That is a small, contained change.

11. Decisions Morgan made that warrant PO sign-off

Listed here so the PO can ratify before Sam starts coding. Defaults are Morgan's recommendation; the PO can flip any of them by replying.

  1. Sync scheduler = pg_cron in the keystone-managed Postgres (not a Node.js worker, not external cron). §5 above. Trade-off: leans on pg_net.http_post and an internal secret-gated tRPC mutation. The only alternative that meaningfully changes the design is a dedicated worker tier, which I do not recommend at Phase-1 scale.
  2. Caching = in-memory per-process LRU with 60 s TTL (not Redis, not Postgres-backed). §6 above. This is single-process today; Phase 2 may revisit when/if we scale horizontally.
  3. Tournament.location and Tournament.organizer_club added (rather than left in the audit trail). Tradable for "store in swisstennis_import_events.summary only and never on Tournament" — see requirements §8.5 last bullet. Recommendation: add them; cheap, useful for the settings page and signage badging.
  4. PoolStanding as a separate table (rather than denormalising onto Registration). §3.2 rationale.
  5. The dispatcher's secret-gated tRPC mutation runs at protectedProcedure granularity but with an explicit header check instead of an auth context. The alternative is a dedicated handler outside the tRPC tree (e.g. a Next.js route handler). Recommendation: stick with tRPC for consistency with the rest of the API; the secret-check is one line.
  6. Background sync's failure threshold N = 5 (per AC-04.6's "recommend N=5 — Morgan to confirm"). Locking in 5.

None of these are reversible-only-with-pain decisions; flipping any of them costs less than a day of Sam's time.

12. Open consults wired into the sequence

12.1 §8.6 — Sam: verify Java-Calendar 0-indexed months

Wired into: E2-A (HTTP client + parsers/date.ts). Specifically, the first parser unit test for parsers/date.ts runs against two real fixtures (trn 158149's trnBegin plus a known second tournament with a different start month) and asserts the resolved date matches the human-readable date on the SwissTennis SPA. If that test passes, the convention is locked; if it fails, Sam blocks on a domain consult with Rafael before continuing.

Blocks if unresolved: all date parsing — which means Tournament.start_date, Tournament.end_date, RR Match.planned_start_time. The KO court.content parsing uses a different format (dd/MM/yy string) and is not blocked by this.

12.2 §8.7 — Jamie: UX placement

Wired into: E6-A, E6-B, E6-D, E6-F. Jamie produces low-fidelity wireframes for each. Recommendation for sequencing: Jamie's design runs in parallel with Sam's first three tickets (E1-A, E1-B, E2-A). Rationale:

  • E1-A and E1-B are schema + scaffold — they do not depend on any UX decision.
  • E2-A is the HTTP client + URL parser — it does not depend on any UX decision.
  • By the time Sam reaches E4-A (tRPC router), Jamie's design needs to exist for the import-summary shape, the settings-page toggle layout, and the import-history detail view so Taylor can pick those tickets up immediately.
  • Running Jamie in parallel saves ~2 days of total elapsed time. Running Jamie sequentially (block Sam until Jamie is done) saves no real work and burns the parallelism for nothing.

Blocks if unresolved: E6-A through E6-F. Sam's tickets through E4-A are unaffected.

One Jamie-specific clarification Morgan needs upfront: Does the "Create from SwissTennis" entry point on the tournament list page (E6-A) replace or sit-alongside the existing "Create blank tournament" CTA? Recommendation: sit-alongside, with the SwissTennis path as the primary CTA when the organizer is logged in and has imported before, and "blank" as the secondary always-available option. Jamie to confirm.

13. Changelog

  • v0.3 (2026-05-24, Riley) — added §8.4 "Testing conventions (binding)" promoting the real-payload-fixtures + eyes-on convention to binding status, with the three May 2026 regressions (#202 / #206 / #207) cited as evidence. No behaviour change; pure documentation. Cross-referenced from packages/swisstennis/test/fixtures/README.md.
  • v0.2 (2026-05-24, Casey) — §5 superseded: scheduler substrate moved from pg_cron-in-Postgres to a keystone-side systemd timer. Cadence rules, dispatcher contract, per-cycle summary shape, and failure-mode handling are unchanged; only the heartbeat substrate moved. Operational documentation: docs/runbooks/swisstennis-scheduler.md. Close-out of GitLab #180 + #181 + #199. The v0.1 §5 text is retained as the historical architectural record.
  • v0.1 (2026-05-23, Morgan) — initial implementation plan against swisstennis-api-integration.md v0.3.