ldraney/basketball-api

Fork

You've already forked basketball-api

Code Issues 90 Pull requests 11 Projects Releases Packages Wiki Activity Actions

Create sheet_sync service module (DB to Sheet, append-only) #438

New issue

Open

opened 2026-04-10 23:31:12 +00:00 by forgejo_admin · 0 comments

forgejo_admin commented

2026-04-10 23:31:12 +00:00

Contributor

Copy link

Type

Feature

Lineage

Standalone — spawned from the westside-sheet-sync project scaffold on 2026-04-10. The core implementation ticket. Depends on the OAuth scope upgrade and the name normalization helper.

Repo

forgejo_admin/basketball-api

User Story

As head coach Marcus
I want paid jersey orders from the DB to automatically appear in my Google Sheet
So that I don't miss orders and don't have to manually cross-check two systems

Ties to story:sheet-sync.

Context

Tonight we found that Arseni Litvinau paid via Stripe weeks ago but was never added to Marcus's Google Sheet. That's the concrete bug this service fixes: the gap between "paid in the DB" and "visible in Marcus's working doc."

The sheet is 1odx6WSKB5Sg5O3aNzgFoGMR458sA75xb8D5jQJolH8g, single tab "Westside 2", 28 columns. The first row is the header: Player Name | Team | K/Q | Number | Jersey top size | Jersey Short size | Shooter shirt size | Coaches Shirts | Key. The "Coaches Shirts" column actually contains order pipeline status (Placed / Submitted / Invoice Sent / Invoice Paid / shipped / received) — the header label is stale. The sync must NOT rely on header text; it keys off column position.

Sync is append-only and one-directional (DB → Sheet). See the sync direction decision in story-westside-jersey-sheet-sync. Never write to existing rows; never delete rows; only append new rows at the bottom.

Idempotency rule: before appending, read the current sheet. For each paid DB player not already in the sheet (by normalized name), append one row. If the same sync runs twice in a row, the second run is a no-op.

File Targets

Files to create:

src/basketball_api/services/sheet_sync.py — contains sync_jersey_orders(db: Session) -> SyncResult. SyncResult is a dataclass: {added: list[str], skipped: list[str], errors: list[str]}.
src/basketball_api/services/sheets_client.py — thin wrapper around requests calls to sheets.googleapis.com/v4. Handles token refresh, GET values, POST values:append. Takes a token file path from config.

Files to modify:

src/basketball_api/config.py — add sheets_token_path: str and jersey_sheet_id: str settings, defaulting to reasonable values.

Files to create (tests):

tests/test_sheet_sync.py — mocks the sheets_client, asserts that new paid orders get appended, existing players get skipped, and empty DB results in a no-op.
tests/test_sheets_client.py — mocks requests, asserts the HTTP calls have the right shape (URL, headers, body).

Files NOT to touch:

src/basketball_api/services/email.py — unrelated.
src/basketball_api/routes/* — no routes in this ticket; exposure is via the CronJob ticket, not an HTTP endpoint.

Acceptance Criteria

When sync_jersey_orders(db) runs with a DB containing Arseni Litvinau (paid) and the sheet not containing him, then his row is appended to the sheet with at least his name, team, and K/Q populated.
When the same function runs a second time back-to-back, then result.added is empty (idempotency).
When a player is already in the sheet with a different capitalization (e.g., "LITVINAU, Arseni" vs DB "Arseni Litvinau"), then the sync treats them as the same player and does NOT append a duplicate (uses name_normalize from the other ticket).
When a player has jersey_order_status != 'paid', then they are NOT appended regardless of whether they're on the sheet.
When a player has contract_status = 'declined', then they are NOT appended regardless of jersey status.
When the Sheets API returns a 401, then the token refresh is attempted once; if still failing, the function raises with a clear error message.
When the function completes, it returns a SyncResult with counts of added, skipped, and errors.

Test Expectations

Unit test: test_sync_new_paid_order_appended — seed a fake DB with one paid player not in a mock sheet; assert the mock sheet gets one new row.
Unit test: test_sync_idempotent — run the sync twice, assert second call appends zero rows.
Unit test: test_sync_respects_normalization — mock sheet has "LITVINAU, Arseni"; DB has "Arseni Litvinau" paid; assert no duplicate appended.
Unit test: test_sync_skips_unpaid — DB has an unpaid player; assert not appended.
Unit test: test_sync_skips_declined — DB has a declined player; assert not appended.
Unit test: test_sheets_client_appends_to_correct_range — mock requests, assert the POST URL is https://sheets.googleapis.com/v4/spreadsheets/<id>/values/<tab>!A:Z:append?valueInputOption=USER_ENTERED.
Run command: pytest tests/test_sheet_sync.py tests/test_sheets_client.py -v

Constraints

Use only requests for HTTP (already in basketball-api's dependencies) — do NOT add google-api-python-client, gspread, or any other SDK.
The sync must read the sheet before appending — never blind-append.
Row schema (column order) must match the existing sheet: Player Name, Team, K/Q, Number, Jersey top size, Jersey Short size, Shooter shirt size, status (currently mis-labeled "Coaches Shirts"), Key. Missing data → empty string, never None.
Do NOT modify existing rows under any circumstance.
The module must not import FastAPI, Pydantic models, or route helpers — it's a plain service layer callable from both HTTP routes and cron jobs.
Token path and spreadsheet ID come from config, never hardcoded.

Checklist

PR opened
Tests pass
No unrelated changes

westside-sheet-sync — project
story-westside-jersey-sheet-sync — user story
Blocks on: OAuth scope upgrade ticket, name normalization helper ticket
Blocks: CronJob deployment ticket, sync failure alerts ticket

### Type Feature ### Lineage Standalone — spawned from the westside-sheet-sync project scaffold on 2026-04-10. The core implementation ticket. Depends on the OAuth scope upgrade and the name normalization helper. ### Repo `forgejo_admin/basketball-api` ### User Story As head coach Marcus I want paid jersey orders from the DB to automatically appear in my Google Sheet So that I don't miss orders and don't have to manually cross-check two systems Ties to `story:sheet-sync`. ### Context Tonight we found that Arseni Litvinau paid via Stripe weeks ago but was never added to Marcus's Google Sheet. That's the concrete bug this service fixes: the gap between "paid in the DB" and "visible in Marcus's working doc." The sheet is `1odx6WSKB5Sg5O3aNzgFoGMR458sA75xb8D5jQJolH8g`, single tab "Westside 2", 28 columns. The first row is the header: `Player Name | Team | K/Q | Number | Jersey top size | Jersey Short size | Shooter shirt size | Coaches Shirts | Key`. The "Coaches Shirts" column actually contains order pipeline status (Placed / Submitted / Invoice Sent / Invoice Paid / shipped / received) — the header label is stale. The sync must NOT rely on header text; it keys off column position. **Sync is append-only and one-directional (DB → Sheet).** See the sync direction decision in `story-westside-jersey-sheet-sync`. Never write to existing rows; never delete rows; only append new rows at the bottom. **Idempotency rule:** before appending, read the current sheet. For each paid DB player not already in the sheet (by normalized name), append one row. If the same sync runs twice in a row, the second run is a no-op. ### File Targets Files to create: - `src/basketball_api/services/sheet_sync.py` — contains `sync_jersey_orders(db: Session) -> SyncResult`. SyncResult is a dataclass: `{added: list[str], skipped: list[str], errors: list[str]}`. - `src/basketball_api/services/sheets_client.py` — thin wrapper around `requests` calls to `sheets.googleapis.com/v4`. Handles token refresh, GET values, POST values:append. Takes a token file path from config. Files to modify: - `src/basketball_api/config.py` — add `sheets_token_path: str` and `jersey_sheet_id: str` settings, defaulting to reasonable values. Files to create (tests): - `tests/test_sheet_sync.py` — mocks the sheets_client, asserts that new paid orders get appended, existing players get skipped, and empty DB results in a no-op. - `tests/test_sheets_client.py` — mocks `requests`, asserts the HTTP calls have the right shape (URL, headers, body). Files NOT to touch: - `src/basketball_api/services/email.py` — unrelated. - `src/basketball_api/routes/*` — no routes in this ticket; exposure is via the CronJob ticket, not an HTTP endpoint. ### Acceptance Criteria - [ ] When `sync_jersey_orders(db)` runs with a DB containing Arseni Litvinau (paid) and the sheet not containing him, then his row is appended to the sheet with at least his name, team, and K/Q populated. - [ ] When the same function runs a second time back-to-back, then `result.added` is empty (idempotency). - [ ] When a player is already in the sheet with a different capitalization (e.g., "LITVINAU, Arseni" vs DB "Arseni Litvinau"), then the sync treats them as the same player and does NOT append a duplicate (uses name_normalize from the other ticket). - [ ] When a player has `jersey_order_status != 'paid'`, then they are NOT appended regardless of whether they're on the sheet. - [ ] When a player has `contract_status = 'declined'`, then they are NOT appended regardless of jersey status. - [ ] When the Sheets API returns a 401, then the token refresh is attempted once; if still failing, the function raises with a clear error message. - [ ] When the function completes, it returns a SyncResult with counts of `added`, `skipped`, and `errors`. ### Test Expectations - [ ] Unit test: `test_sync_new_paid_order_appended` — seed a fake DB with one paid player not in a mock sheet; assert the mock sheet gets one new row. - [ ] Unit test: `test_sync_idempotent` — run the sync twice, assert second call appends zero rows. - [ ] Unit test: `test_sync_respects_normalization` — mock sheet has "LITVINAU, Arseni"; DB has "Arseni Litvinau" paid; assert no duplicate appended. - [ ] Unit test: `test_sync_skips_unpaid` — DB has an unpaid player; assert not appended. - [ ] Unit test: `test_sync_skips_declined` — DB has a declined player; assert not appended. - [ ] Unit test: `test_sheets_client_appends_to_correct_range` — mock `requests`, assert the POST URL is `https://sheets.googleapis.com/v4/spreadsheets/<id>/values/<tab>!A:Z:append?valueInputOption=USER_ENTERED`. - Run command: `pytest tests/test_sheet_sync.py tests/test_sheets_client.py -v` ### Constraints - Use only `requests` for HTTP (already in basketball-api's dependencies) — do NOT add `google-api-python-client`, `gspread`, or any other SDK. - The sync must read the sheet before appending — never blind-append. - Row schema (column order) must match the existing sheet: Player Name, Team, K/Q, Number, Jersey top size, Jersey Short size, Shooter shirt size, status (currently mis-labeled "Coaches Shirts"), Key. Missing data → empty string, never None. - Do NOT modify existing rows under any circumstance. - The module must not import FastAPI, Pydantic models, or route helpers — it's a plain service layer callable from both HTTP routes and cron jobs. - Token path and spreadsheet ID come from config, never hardcoded. ### Checklist - [ ] PR opened - [ ] Tests pass - [ ] No unrelated changes ### Related - `westside-sheet-sync` — project - `story-westside-jersey-sheet-sync` — user story - Blocks on: OAuth scope upgrade ticket, name normalization helper ticket - Blocks: CronJob deployment ticket, sync failure alerts ticket

No Branch/Tag specified

main

feat/payment-links-outstanding-balance

monthly-payment-email-may

510-grant-tournament-tables-to-ro-role

458-skip-proration-v2

458-skip-proration

466-cherry-pick-first-payment

458-merge-first-payment-blast

369-first-payment-email-blast

408-player-nickname

313-local-first-week-email

320-mjml-sponsor-outreach-template

270-contract-reminder-email

205-clean-test-data-from-production-db-playe

114-email-verification

147-feat-add-get-jersey-player-info-endpoint

124-feat-many-to-many-player-team-assignment

125-feat-add-new-players-manually-seydou-gou

126-feat-team-placement-congratulations-emai

121-admin-teams-spa-endpoints

70-add-graduating-class-to-roster-api-respo

59-fix-auth-test

62-phase-4l-sveltekit-dashboard-wkq-tailsca

11-phase-1a-fix-ci-pipeline-venv-in-test-st

No results found.

Labels

Clear labels

QA passed, awaiting merge approval

status:in-progress

Dev agent is actively working

status:needs-fix

QA found issues, back to dev

status:qa

PR submitted, awaiting QA review

type:bug

Bug fix

type:devops

Infrastructure/CI/config work

No labels

Milestone

Clear milestone

No items

No milestone

Projects

Clear projects

No items

No project

Assignees

Clear assignees

No assignees

1 participant

Notifications

Due date

The due date is invalid or out of range. Please use the format "yyyy-mm-dd".

No due date set.

Dependencies

No dependencies set.

Reference

ldraney/basketball-api#438

Reference in a new issue

Repository

ldraney/basketball-api

Title

Body

No description provided.

Delete branch "%!s()"

Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?

Rows
Columns