Story: Jersey Order Sheet Stays In Sync With DB

story-westside-jersey-sheet-sync Doc

active user-story

Project

Story: Jersey Order Sheet Stays In Sync With DB

Key: sheet-sync · Label: story:sheet-sync · Role: Head Coach (Marcus) + Ops (Lucas/Ava)

Story

As head coach Marcus, I need new paid jersey orders from the basketball-api database to automatically appear in my "Westside" Google Sheet, so that my operational view of jersey orders stays current without me having to cross-reference the database or ask Lucas. As ops, I need the sync to be append-only and one-directional (DB → Sheet) so that Marcus's manual fulfillment-status edits are never overwritten.

Why

The basketball-api database is the system of record for paid jersey orders (via Stripe checkout + future cash payment records). Marcus's Google Sheet is his operational working doc — he uses it to track where each order is in the supplier fulfillment pipeline (Placed → Submitted → Invoice Sent → Invoice Paid → Shipped → Received). The two are currently out of sync: tonight we discovered Arseni Litvinau paid via Stripe but was never added to Marcus's sheet.

Without automatic sync, every new Stripe order requires someone to manually add a row to the sheet. That's a coordination tax that scales badly and creates silent gaps (like Arseni) whenever the manual step is forgotten.

Sync direction decision

Append-only, DB → Sheet only. The opposite direction (Sheet → DB) is explicitly out of scope because:

Marcus's fulfillment states (Placed / Submitted / etc.) don't have a matching enum in the DB. Trying to reflect them would require a schema change and is a separate concern.
Append-only is a safe default: we can never corrupt Marcus's manual edits, only add rows he's missing.
If Marcus wants to delete a row from the sheet, that's his operational decision and the sync won't re-add it (idempotency check by player name).

Acceptance criteria

When a new player in the DB has jersey_order_status = 'paid', is on an active team, and their name is not already in the sheet, then within 1 hour they appear as a new row in the "Westside 2" tab.
When Marcus edits any row's status column (Placed → Submitted → etc.), the sync never overwrites it.
When the sync runs with no new orders to add, it logs "no changes" and exits cleanly.
When the sync fails (API error, auth expired, network issue), an alert fires through the existing Telegram alertmanager pipeline.
When a player name in the DB and in the sheet differ only by capitalization or punctuation (e.g., "Rigitano de Paula" vs "RIGITANO DE PAULA"), they are treated as the same player (no duplicate row inserted).

Out of scope

Sheet → DB sync (reading Marcus's fulfillment states back into the DB)
Writing to any tab other than the current one (single-tab for now)
Managing sheet formatting, conditional formatting, or headers
Bi-directional conflict resolution

Supporting tickets

All of these are on board-jersey-sheet-sync and trace to story:sheet-sync:

Upgrade Google Sheets OAuth scope to spreadsheets (write)
Add name normalization helper to basketball-api
Create sheet_sync service module in basketball-api
Deploy sheet_sync as a k8s CronJob (hourly)
Alert on sync failures via Telegram alertmanager