forgejo_admin/basketball-api

Fork

You've already forked basketball-api

Code Issues 88 Pull requests 9 Projects Releases Packages Wiki Activity Actions

[westside-email-agent] Add query_monthly_fee_due to email_queries.py registry #512

New issue

Open

opened 2026-04-25 02:24:45 +00:00 by forgejo_admin · 0 comments

forgejo_admin commented

2026-04-25 02:24:45 +00:00

Owner

Copy link

Type

Feature

Lineage

Standalone — discovered while scoping westside-email-agent project on 2026-04-23. The blast system is generic and the agent CLAUDE.md is wired, but the QUERY_REGISTRY has no audience query for monthly fee reminders. This is the only blocker for sending monthly billing emails today.

Repo

forgejo_admin/basketball-api

User Story

As Lucas (or Marcus via the agent)
I want a monthly_fee_due audience query registered in basketball-api
So that I can blast monthly fee reminders to the right parents using the existing /admin/email/blast endpoint without further code changes

Context

The email blast endpoint (routes/admin.py:1233) is fully generic — it validates query name against QUERY_REGISTRY and supports per-recipient placeholder substitution. Adding a new audience = add a query function + register it in the dict. No endpoint changes needed.

The audience for "monthly fee due": parents whose players have an active monthly subscription (Stripe subscription or recorded monthly_fee field) where the next payment is due within N days, OR has failed in the last billing cycle. Exact filter logic TBD with Lucas at /review-ticket — likely starts as "active subscription, next payment within 5 days OR last invoice failed."

The result rows must include a cta_url pointing to the parent's Stripe customer portal (or equivalent self-service payment update URL).

File Targets

Files to modify:

src/basketball_api/services/email_queries.py — add query_monthly_fee_due function (model on existing query_unsigned_contracts shape); register in QUERY_REGISTRY
tests/services/test_email_queries.py (or equivalent) — add test for the new query

Files to NOT touch:

src/basketball_api/routes/admin.py — endpoint is already generic, no changes needed
Existing query functions — do not refactor in this PR
templates/email/compiled/ — using existing action layout, no template changes

Acceptance Criteria

query_monthly_fee_due(db, tenant, *, test_email=None) exists and returns list[dict] with keys: to, parent_id, parent_name, player_name, cta_url (Stripe portal or payment-update URL), plus any other fields Lucas decides to template into the body
Function is registered in QUERY_REGISTRY under the key "monthly_fee_due"
test_email filter works (returns at most 1 row when matching parent's email is provided)
Existing 3 queries still pass their tests
GET of get_available_queries() returns the new query name in the sorted list

Test Expectations

Unit test: test_query_monthly_fee_due — given seed data with N parents (some with active subs, some without), function returns the right N rows
Unit test: test_query_monthly_fee_due_test_email_filter — filters to a single parent when test_email matches
No integration test required for this PR (the blast endpoint is already exercised by existing query tests)
Run command: cd ~/basketball-api && pytest tests/services/test_email_queries.py -k monthly

Constraints

Pattern to follow: query_unsigned_contracts (lines 36–91 of email_queries.py) — it's the cleanest single-table example
Filter logic for "due": confirm with Lucas at /review-ticket before implementation. Default proposal: active subscription, next billing date within 5 days OR most recent invoice failed
Do NOT add new EmailType enum values in this PR unless one is missing for monthly_fee — confirm at /review-ticket
Keep the function pure-DB-read (no Stripe API calls) — Stripe state should already be reflected in DB via existing webhook

Checklist

PR opened
Tests pass
No unrelated changes
EmailType enum verified (added in same PR if missing)

westside-email-agent — project this serves
westside-basketball — repo this lives in
arch:basketball-api — architecture component
arch-email — system architecture
sop-email-send — usage workflow
project-westside-email-agent — parent project

### Type Feature ### Lineage Standalone — discovered while scoping westside-email-agent project on 2026-04-23. The blast system is generic and the agent CLAUDE.md is wired, but the QUERY_REGISTRY has no audience query for monthly fee reminders. This is the only blocker for sending monthly billing emails today. ### Repo `forgejo_admin/basketball-api` ### User Story As Lucas (or Marcus via the agent) I want a `monthly_fee_due` audience query registered in basketball-api So that I can blast monthly fee reminders to the right parents using the existing /admin/email/blast endpoint without further code changes ### Context The email blast endpoint (`routes/admin.py:1233`) is fully generic — it validates query name against `QUERY_REGISTRY` and supports per-recipient placeholder substitution. Adding a new audience = add a query function + register it in the dict. No endpoint changes needed. The audience for "monthly fee due": parents whose players have an active monthly subscription (Stripe subscription or recorded `monthly_fee` field) where the next payment is due within N days, OR has failed in the last billing cycle. Exact filter logic TBD with Lucas at /review-ticket — likely starts as "active subscription, next payment within 5 days OR last invoice failed." The result rows must include a `cta_url` pointing to the parent's Stripe customer portal (or equivalent self-service payment update URL). ### File Targets Files to modify: - `src/basketball_api/services/email_queries.py` — add `query_monthly_fee_due` function (model on existing `query_unsigned_contracts` shape); register in `QUERY_REGISTRY` - `tests/services/test_email_queries.py` (or equivalent) — add test for the new query Files to NOT touch: - `src/basketball_api/routes/admin.py` — endpoint is already generic, no changes needed - Existing query functions — do not refactor in this PR - `templates/email/compiled/` — using existing `action` layout, no template changes ### Acceptance Criteria - [ ] `query_monthly_fee_due(db, tenant, *, test_email=None)` exists and returns `list[dict]` with keys: `to`, `parent_id`, `parent_name`, `player_name`, `cta_url` (Stripe portal or payment-update URL), plus any other fields Lucas decides to template into the body - [ ] Function is registered in `QUERY_REGISTRY` under the key `"monthly_fee_due"` - [ ] `test_email` filter works (returns at most 1 row when matching parent's email is provided) - [ ] Existing 3 queries still pass their tests - [ ] `GET` of `get_available_queries()` returns the new query name in the sorted list ### Test Expectations - [ ] Unit test: `test_query_monthly_fee_due` — given seed data with N parents (some with active subs, some without), function returns the right N rows - [ ] Unit test: `test_query_monthly_fee_due_test_email_filter` — filters to a single parent when test_email matches - [ ] No integration test required for this PR (the blast endpoint is already exercised by existing query tests) - Run command: `cd ~/basketball-api && pytest tests/services/test_email_queries.py -k monthly` ### Constraints - Pattern to follow: `query_unsigned_contracts` (lines 36–91 of `email_queries.py`) — it's the cleanest single-table example - Filter logic for "due": confirm with Lucas at `/review-ticket` before implementation. Default proposal: active subscription, next billing date within 5 days OR most recent invoice failed - Do NOT add new EmailType enum values in this PR unless one is missing for monthly_fee — confirm at /review-ticket - Keep the function pure-DB-read (no Stripe API calls) — Stripe state should already be reflected in DB via existing webhook ### Checklist - [ ] PR opened - [ ] Tests pass - [ ] No unrelated changes - [ ] EmailType enum verified (added in same PR if missing) ### Related - `westside-email-agent` — project this serves - `westside-basketball` — repo this lives in - `arch:basketball-api` — architecture component - [arch-email](https://pal-e-docs.tail5b443a.ts.net/notes/arch-email) — system architecture - [sop-email-send](https://pal-e-docs.tail5b443a.ts.net/notes/sop-email-send) — usage workflow - [project-westside-email-agent](https://pal-e-docs.tail5b443a.ts.net/notes/project-westside-email-agent) — parent project

No Branch/Tag specified

main

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

forgejo_admin/basketball-api#512

Reference in a new issue

Repository

forgejo_admin/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