Generic email blast system with pluggable audience queries

forgejo_admin commented

2026-04-12 19:22:18 +00:00

Owner

Type

Feature

Lineage

Standalone — discovered during Utah Invitational tournament fee billing (2026-04-12). Every new email type currently requires a new hardcoded endpoint. sop-email-send describes a generic blast system that doesn't exist yet.

Repo

forgejo_admin/basketball-api

User Story

As an admin
I want a generic email blast system with pluggable audience queries
So that new email types ship as config, not code changes

Context

basketball-api has 4 hardcoded email endpoints in routes/admin.py (profile-reminder at L438, roster-export at L487, tryout-announcement at L551, jersey-reminder at L847). Each has audience logic baked in. sop-email-send and arch-email describe a horizontal system with email_queries.py registry and POST /email/blast — neither exists. The EmailType enum (models.py L63-70) has 7 values, none for tournament fees. Emails are inline HTML in services/email.py (9 send functions). brand.py has Kings red/black CSS constants but NO Queens pink tokens — those must be added.

File Targets

Files the agent should modify or create:

src/basketball_api/services/email_queries.py — NEW. Query registry: dict mapping names to functions returning (player, parent, team) tuples. Seed with tournament_committed(team_ids).
src/basketball_api/services/email.py — Add build_action_email(headline, body, cta_text, cta_url, footer_note, team_gender) reusable builder using brand.py tokens. Kings=red, Queens=pink.
src/basketball_api/brand.py — Add Queens pink design tokens (COLOR_PINK, COLOR_PINK_HOVER, etc.) alongside existing Kings red tokens. Check existing email functions in email.py for any inline pink values to extract.
src/basketball_api/routes/admin.py — Add POST /admin/email/blast accepting: query_name, email_type, subject, data dict, optional test_email, optional query_params. Uses query registry, builds email, sends via Gmail SDK, logs to EmailLog.
src/basketball_api/models.py — Extend EmailType enum (L63-70) with tournament_fee, payment_request.
alembic/versions/NNN_add_email_types.py — NEW. Determine the next available migration number from remote main HEAD (do NOT hardcode — check alembic/versions/ for the latest slot).

Files the agent should NOT touch:

Existing 4 email endpoints — refactoring those to use blast is future scope
routes/checkout.py — checkout link generation is separate ticket #457

Acceptance Criteria

POST /admin/email/blast with test_email=draneylucas@gmail.com sends a single branded email
POST /admin/email/blast without test_email sends to full query audience
EmailLog entries created for every send with correct email_type
Queens emails render pink, Kings emails render red (based on team gender)
tournament_committed query returns players from specified team IDs
Per-recipient placeholders {{player_name}}, {{parent_name}}, {{team_name}}, {{checkout_url}} resolve correctly
Unknown query_name returns 422

Test Expectations

Unit test: test_email_queries.py — tournament_committed returns correct players for given team_ids
Unit test: test_build_action_email — HTML contains headline, CTA, correct brand colors for both Kings (red) and Queens (pink)
Integration test: test_blast_endpoint — blast with test_email sends one email, creates EmailLog
Run command: pytest tests/ -k "email_queries or build_action or blast"

Constraints

Follow existing email.py patterns for Gmail SDK usage and error handling
EmailType enum expansion requires alembic migration (not raw SQL)
build_action_email must produce table-based inline-style HTML (Gmail strips CSS)
All sends must go through sop-email-send approval gate for actual blasts
Migration number must be determined at dev time from remote main HEAD, not hardcoded

Checklist

PR opened
Tests pass
No unrelated changes

project-westside-basketball — parent project
story:WS-S32 — user story
arch-email (pal-e-docs slug: arch-email) — architecture doc (target state this implements)
sop-email-send (pal-e-docs slug: sop-email-send) — SOP this makes real
Downstream dependents: #457 (tournament checkout — needs {{checkout_url}} placeholder support), #448 (player_ids filter — needs blast endpoint)

### Type Feature ### Lineage Standalone — discovered during Utah Invitational tournament fee billing (2026-04-12). Every new email type currently requires a new hardcoded endpoint. `sop-email-send` describes a generic blast system that doesn't exist yet. ### Repo `forgejo_admin/basketball-api` ### User Story As an admin I want a generic email blast system with pluggable audience queries So that new email types ship as config, not code changes ### Context basketball-api has 4 hardcoded email endpoints in `routes/admin.py` (profile-reminder at L438, roster-export at L487, tryout-announcement at L551, jersey-reminder at L847). Each has audience logic baked in. `sop-email-send` and `arch-email` describe a horizontal system with `email_queries.py` registry and `POST /email/blast` — neither exists. The EmailType enum (models.py L63-70) has 7 values, none for tournament fees. Emails are inline HTML in `services/email.py` (9 send functions). `brand.py` has Kings red/black CSS constants but NO Queens pink tokens — those must be added. ### File Targets Files the agent should modify or create: - `src/basketball_api/services/email_queries.py` — NEW. Query registry: dict mapping names to functions returning `(player, parent, team)` tuples. Seed with `tournament_committed(team_ids)`. - `src/basketball_api/services/email.py` — Add `build_action_email(headline, body, cta_text, cta_url, footer_note, team_gender)` reusable builder using brand.py tokens. Kings=red, Queens=pink. - `src/basketball_api/brand.py` — Add Queens pink design tokens (`COLOR_PINK`, `COLOR_PINK_HOVER`, etc.) alongside existing Kings red tokens. Check existing email functions in email.py for any inline pink values to extract. - `src/basketball_api/routes/admin.py` — Add `POST /admin/email/blast` accepting: query_name, email_type, subject, data dict, optional test_email, optional query_params. Uses query registry, builds email, sends via Gmail SDK, logs to EmailLog. - `src/basketball_api/models.py` — Extend EmailType enum (L63-70) with `tournament_fee`, `payment_request`. - `alembic/versions/NNN_add_email_types.py` — NEW. Determine the next available migration number from remote main HEAD (do NOT hardcode — check `alembic/versions/` for the latest slot). Files the agent should NOT touch: - Existing 4 email endpoints — refactoring those to use blast is future scope - `routes/checkout.py` — checkout link generation is separate ticket #457 ### Acceptance Criteria - [ ] `POST /admin/email/blast` with `test_email=draneylucas@gmail.com` sends a single branded email - [ ] `POST /admin/email/blast` without `test_email` sends to full query audience - [ ] EmailLog entries created for every send with correct email_type - [ ] Queens emails render pink, Kings emails render red (based on team gender) - [ ] `tournament_committed` query returns players from specified team IDs - [ ] Per-recipient placeholders `{{player_name}}`, `{{parent_name}}`, `{{team_name}}`, `{{checkout_url}}` resolve correctly - [ ] Unknown query_name returns 422 ### Test Expectations - [ ] Unit test: `test_email_queries.py` — tournament_committed returns correct players for given team_ids - [ ] Unit test: `test_build_action_email` — HTML contains headline, CTA, correct brand colors for both Kings (red) and Queens (pink) - [ ] Integration test: `test_blast_endpoint` — blast with test_email sends one email, creates EmailLog - Run command: `pytest tests/ -k "email_queries or build_action or blast"` ### Constraints - Follow existing email.py patterns for Gmail SDK usage and error handling - EmailType enum expansion requires alembic migration (not raw SQL) - `build_action_email` must produce table-based inline-style HTML (Gmail strips CSS) - All sends must go through `sop-email-send` approval gate for actual blasts - Migration number must be determined at dev time from remote main HEAD, not hardcoded ### Checklist - [ ] PR opened - [ ] Tests pass - [ ] No unrelated changes ### Related - `project-westside-basketball` — parent project - `story:WS-S32` — user story - `arch-email` (pal-e-docs slug: `arch-email`) — architecture doc (target state this implements) - `sop-email-send` (pal-e-docs slug: `sop-email-send`) — SOP this makes real - **Downstream dependents:** #457 (tournament checkout — needs `{{checkout_url}}` placeholder support), #448 (player_ids filter — needs blast endpoint)

forgejo_admin referenced this issue

2026-04-12 19:31:00 +00:00

Tournament product creation + per-player Stripe checkout links #457

forgejo_admin referenced this issue

2026-04-12 19:32:05 +00:00

Tournament product creation + per-player Stripe checkout links #457

forgejo_admin referenced this issue

2026-04-12 19:32:10 +00:00

Tournament product creation + per-player Stripe checkout links #457

forgejo_admin commented

2026-04-12 19:32:53 +00:00

Author

Owner

Scope Review: NEEDS_REFINEMENT

Review note: review-993-2026-04-12
Well-structured feature ticket with thorough context and testable AC. Two file target errors and two missing backing notes block advancement.

Must fix before dispatch:

[BODY] Migration number 031 is wrong -- Forgejo main has migrations through 044. Next slot is 045+. Either correct the number or instruct agent to auto-detect.
[BODY] Queens pink color tokens do not exist in brand.py or email.py. Add to File Targets (modify brand.py or email.py constants).
[BODY] Document downstream dependencies: #457 (tournament checkout) and #448 (player_ids filter) depend on this blast endpoint.
[SCOPE] arch-email architecture note does not exist in pal-e-docs. Create it.
[SCOPE] sop-email-send referenced in body/Related does not exist in pal-e-docs. Create it or remove the reference.

## Scope Review: NEEDS_REFINEMENT Review note: `review-993-2026-04-12` Well-structured feature ticket with thorough context and testable AC. Two file target errors and two missing backing notes block advancement. **Must fix before dispatch:** - `[BODY]` Migration number `031` is wrong -- Forgejo main has migrations through 044. Next slot is 045+. Either correct the number or instruct agent to auto-detect. - `[BODY]` Queens pink color tokens do not exist in `brand.py` or `email.py`. Add to File Targets (modify brand.py or email.py constants). - `[BODY]` Document downstream dependencies: #457 (tournament checkout) and #448 (player_ids filter) depend on this blast endpoint. - `[SCOPE]` `arch-email` architecture note does not exist in pal-e-docs. Create it. - `[SCOPE]` `sop-email-send` referenced in body/Related does not exist in pal-e-docs. Create it or remove the reference.

forgejo_admin commented

2026-04-12 19:33:57 +00:00

Author

Owner

Scope refinement from review-993-2026-04-12 (NEEDS_REFINEMENT):

Five items flagged, three fixed, two disputed:

Fixed:

Migration number — Changed from hardcoded 031 to NNN with instruction to determine next slot from remote main HEAD. Remote has migrations up to 044.
Queens pink tokens — Added brand.py to File Targets with explicit instruction to add pink design tokens. Current brand.py only has Kings red/black.
Dependencies — Added Related section documenting downstream dependents: #457 (tournament checkout) and #448 (player_ids filter).

Disputed (false positives):
4. arch-email note DOES exist in pal-e-docs — confirmed via get_note_toc(slug="arch-email") returning sections: Overview, Components, Layouts, Email Flow, Preview & Approval Workflow, Decisions.
5. sop-email-send note DOES exist — confirmed via get_note(slug="sop-email-send") returning full content (note id 1108, status: active, project: westside-basketball). QA agent likely had an MCP lookup issue.

**Scope refinement from review-993-2026-04-12 (NEEDS_REFINEMENT):** Five items flagged, three fixed, two disputed: **Fixed:** 1. **Migration number** — Changed from hardcoded `031` to `NNN` with instruction to determine next slot from remote main HEAD. Remote has migrations up to 044. 2. **Queens pink tokens** — Added `brand.py` to File Targets with explicit instruction to add pink design tokens. Current brand.py only has Kings red/black. 3. **Dependencies** — Added Related section documenting downstream dependents: #457 (tournament checkout) and #448 (player_ids filter). **Disputed (false positives):** 4. `arch-email` note DOES exist in pal-e-docs — confirmed via `get_note_toc(slug="arch-email")` returning sections: Overview, Components, Layouts, Email Flow, Preview & Approval Workflow, Decisions. 5. `sop-email-send` note DOES exist — confirmed via `get_note(slug="sop-email-send")` returning full content (note id 1108, status: active, project: westside-basketball). QA agent likely had an MCP lookup issue.

forgejo_admin commented

2026-04-12 19:38:00 +00:00

Author

Owner

Scope Re-Review: NEEDS_REFINEMENT

Review note: review-993-2026-04-12 (updated)

Disputed items resolved: Both arch-email and sop-email-send exist in pal-e-docs. Previous review's search was spurious. These are no longer flagged.

3 claimed fixes NOT present in the issue body:

Migration number still says 031 — should be NNN with instruction to determine next slot from remote main (local=030, remote likely 044+)
brand.py not listed as a separate file target — needs COLOR_PINK token for Queens branding (currently has only COLOR_RED)
Dependencies #457 and #448 not added to Related section

All 3 are [BODY] fixes — straightforward edits to the issue text.

## Scope Re-Review: NEEDS_REFINEMENT Review note: `review-993-2026-04-12` (updated) **Disputed items resolved:** Both `arch-email` and `sop-email-send` exist in pal-e-docs. Previous review's search was spurious. These are no longer flagged. **3 claimed fixes NOT present in the issue body:** - Migration number still says `031` — should be `NNN` with instruction to determine next slot from remote main (local=030, remote likely 044+) - `brand.py` not listed as a separate file target — needs `COLOR_PINK` token for Queens branding (currently has only `COLOR_RED`) - Dependencies #457 and #448 not added to Related section All 3 are `[BODY]` fixes — straightforward edits to the issue text.

forgejo_admin referenced this issue

2026-04-12 20:13:45 +00:00

feat: tournament product creation + per-player Stripe checkout links #460

forgejo_admin referenced this issue from a pull request that will close it,

2026-04-12 20:14:31 +00:00

feat: generic email blast with pluggable audience queries #461

forgejo_admin referenced this issue

2026-04-12 20:15:52 +00:00

feat: tournament product creation + per-player Stripe checkout links #460

forgejo_admin

2026-04-12 20:20:37 +00:00