GroupMe data model + create groups + auto-invite on contract signing #156

New issue

Closed

opened 2026-03-24 06:43:02 +00:00 by forgejo_admin · 6 comments

forgejo_admin commented 2026-03-24 06:43:02 +00:00

Owner

Copy link

Type

Feature

Lineage

project-groupme-westside → Ticket 2 of 3 (depends on Ticket 1: groupme-sdk)

Repo

forgejo_admin/basketball-api

User Story

As an admin (story:GM-1), I want players auto-added to their team's GroupMe when they sign their contract, so I don't manage membership manually.
As a platform operator (story:GM-6), I want GroupMe group IDs stored per team in the database.
As a platform operator (story:GM-7), I want an audit trail of GroupMe invites.

Context

The basketball-api needs to know which GroupMe group belongs to which team, and automatically invite parents when contracts are signed. Requires:

1. Alembic migration adding groupme_group_id to teams table + new GroupMe tracking tables
2. Import groupme-sdk as dependency
3. Hook into contract signing endpoint to trigger GroupMe invite
4. One-time script to create 11 groups via SDK and store IDs in DB

Known behavior: Adding by phone via API may not match existing GroupMe accounts. Don't mix manual and API adds. Discovered during Marcus onboarding 2026-03-24.

File Targets

• alembic/versions/xxx_add_groupme.py — migration: add groupme_group_id to teams, create groupme_groups and groupme_members tables
• src/basketball_api/models/groupme.py — SQLAlchemy models for GroupMe tables
• src/basketball_api/routes/players.py — add GroupMe invite after contract signing
• src/basketball_api/services/groupme.py — service layer wrapping SDK calls
• scripts/create_groupme_groups.py — one-time script: create 11 groups, store IDs
• pyproject.toml — add groupme-sdk dependency

Files NOT to touch:

• src/basketball_api/routes/teams.py — no API changes needed, just DB column
• src/basketball_api/auth.py — no auth changes

Acceptance Criteria

• Alembic migration adds groupme_group_id VARCHAR(50) to teams table
• GroupMe tracking tables created (groupme_groups, groupme_members)
• 11 GroupMe groups created via SDK (9 teams + announcements + staff)
• Group IDs stored in teams table
• Lucas and Marcus are members of all groups (Marcus user_id: 30257234)
• Announcements group set to office mode (if API supports it)
• Contract signing (POST /api/players/{id}/contract) triggers GroupMe invite
• Parent added to team's GroupMe group by phone number
• Invite logged in groupme_members table

Test Expectations

• Unit test: contract signing triggers GroupMe invite (mocked SDK)
• Unit test: migration applies and rolls back cleanly
• Integration test: create group → sign contract → verify parent added
• Run command: pytest tests/ -k groupme

Constraints

• groupme-sdk must be published to Forgejo PyPI first (Ticket 1)
• GroupMe invite is best-effort — contract signing must succeed even if GroupMe API is down
• Marcus's real GroupMe user_id is 30257234 ("Coach Marcus Draney"), NOT the phone-matched account

Checklist

• PR opened
• Tests pass
• No unrelated changes

Related

• project-groupme-westside — project page
• Ticket 1: groupme-sdk (dependency)

### Type Feature ### Lineage `project-groupme-westside` → Ticket 2 of 3 (depends on Ticket 1: groupme-sdk) ### Repo `forgejo_admin/basketball-api` ### User Story As an admin (story:GM-1), I want players auto-added to their team's GroupMe when they sign their contract, so I don't manage membership manually. As a platform operator (story:GM-6), I want GroupMe group IDs stored per team in the database. As a platform operator (story:GM-7), I want an audit trail of GroupMe invites. ### Context The basketball-api needs to know which GroupMe group belongs to which team, and automatically invite parents when contracts are signed. Requires: 1. Alembic migration adding `groupme_group_id` to teams table + new GroupMe tracking tables 2. Import groupme-sdk as dependency 3. Hook into contract signing endpoint to trigger GroupMe invite 4. One-time script to create 11 groups via SDK and store IDs in DB Known behavior: Adding by phone via API may not match existing GroupMe accounts. Don't mix manual and API adds. Discovered during Marcus onboarding 2026-03-24. ### File Targets - `alembic/versions/xxx_add_groupme.py` — migration: add `groupme_group_id` to teams, create `groupme_groups` and `groupme_members` tables - `src/basketball_api/models/groupme.py` — SQLAlchemy models for GroupMe tables - `src/basketball_api/routes/players.py` — add GroupMe invite after contract signing - `src/basketball_api/services/groupme.py` — service layer wrapping SDK calls - `scripts/create_groupme_groups.py` — one-time script: create 11 groups, store IDs - `pyproject.toml` — add groupme-sdk dependency Files NOT to touch: - `src/basketball_api/routes/teams.py` — no API changes needed, just DB column - `src/basketball_api/auth.py` — no auth changes ### Acceptance Criteria - [ ] Alembic migration adds `groupme_group_id` VARCHAR(50) to teams table - [ ] GroupMe tracking tables created (groupme_groups, groupme_members) - [ ] 11 GroupMe groups created via SDK (9 teams + announcements + staff) - [ ] Group IDs stored in teams table - [ ] Lucas and Marcus are members of all groups (Marcus user_id: 30257234) - [ ] Announcements group set to office mode (if API supports it) - [ ] Contract signing (`POST /api/players/{id}/contract`) triggers GroupMe invite - [ ] Parent added to team's GroupMe group by phone number - [ ] Invite logged in groupme_members table ### Test Expectations - [ ] Unit test: contract signing triggers GroupMe invite (mocked SDK) - [ ] Unit test: migration applies and rolls back cleanly - [ ] Integration test: create group → sign contract → verify parent added - Run command: `pytest tests/ -k groupme` ### Constraints - groupme-sdk must be published to Forgejo PyPI first (Ticket 1) - GroupMe invite is best-effort — contract signing must succeed even if GroupMe API is down - Marcus's real GroupMe user_id is `30257234` ("Coach Marcus Draney"), NOT the phone-matched account ### Checklist - [ ] PR opened - [ ] Tests pass - [ ] No unrelated changes ### Related - `project-groupme-westside` — project page - Ticket 1: groupme-sdk (dependency)

forgejo_admin commented 2026-03-24 06:49:11 +00:00

Author

Owner

Copy link

Architecture Simplification (2026-03-24)

Major scope reduction after brainstorming. Parents self-join via share link in welcome email instead of being API-added by phone number.

Simplified migration:

• Add groupme_group_id VARCHAR(50) to teams table
• Add groupme_share_url VARCHAR(500) to teams table
• No new tables — killed GroupMeGroup and GroupMeMember tables. GroupMe API is source of truth.

Contract signing flow (simplified):

1. Parent signs contract → contract_status = 'signed'
2. Look up team.groupme_share_url
3. Send welcome email via pal-e-mail: "Congrats on joining [team]! Join our GroupMe: [share_link]"
4. Parent clicks link → self-joins group
5. Email tracked in existing email_log table (no new tracking needed)

Removed from scope:

• GroupMe API add_member call on contract signing (replaced by share link in email)
• groupme_groups table
• groupme_members table
• Phone-number duplicate risk eliminated

Updated AC:

• Alembic migration adds groupme_group_id + groupme_share_url to teams table
• 11 GroupMe groups created via SDK, IDs + share URLs stored in teams table
• Contract signing sends welcome email with GroupMe share link
• Welcome email MJML template created in pal-e-mail

## Architecture Simplification (2026-03-24) Major scope reduction after brainstorming. Parents **self-join via share link** in welcome email instead of being API-added by phone number. ### Simplified migration: - Add `groupme_group_id VARCHAR(50)` to teams table - Add `groupme_share_url VARCHAR(500)` to teams table - **No new tables** — killed GroupMeGroup and GroupMeMember tables. GroupMe API is source of truth. ### Contract signing flow (simplified): 1. Parent signs contract → `contract_status = 'signed'` 2. Look up `team.groupme_share_url` 3. Send welcome email via pal-e-mail: "Congrats on joining [team]! Join our GroupMe: [share_link]" 4. Parent clicks link → self-joins group 5. Email tracked in existing `email_log` table (no new tracking needed) ### Removed from scope: - GroupMe API `add_member` call on contract signing (replaced by share link in email) - `groupme_groups` table - `groupme_members` table - Phone-number duplicate risk eliminated ### Updated AC: - [ ] Alembic migration adds `groupme_group_id` + `groupme_share_url` to teams table - [ ] 11 GroupMe groups created via SDK, IDs + share URLs stored in teams table - [ ] Contract signing sends welcome email with GroupMe share link - [ ] Welcome email MJML template created in pal-e-mail

forgejo_admin commented 2026-03-24 06:58:25 +00:00

Author

Owner

Copy link

Code Exploration Findings (2026-03-24)

Contract signing endpoint

POST /api/players/{player_id}/contract in src/basketball_api/routes/players.py:249-320

• Sets contract_status, contract_signed_at, contract_signed_by, contract_signed_ip
• Currently sends NO email — just DB update + log

Email architecture

basketball-api uses gmail-sdk directly (NOT pal-e-mail). All emails built inline in src/basketball_api/services/email.py:

• 6 existing email functions (confirmation, reminder, roster_export, tryout_announcement, password_reset, jersey_reminder)
• Uses _brand_wrapper() for Westside red/black styling
• Logs to email_log table

EmailType enum (models.py:47-51)

Current values: registration, reminder, roster_export, announcement
Missing: contract_signed

Revised File Targets

• src/basketball_api/models.py — add contract_signed to EmailType enum, add groupme_group_id + groupme_share_url to Team model
• alembic/versions/xxx_add_groupme.py — migration for new Team columns
• src/basketball_api/services/email.py — add send_contract_signed_email() following existing pattern
• src/basketball_api/routes/players.py — call send_contract_signed_email() after successful contract signing
• scripts/create_groupme_groups.py — one-time: create 11 groups via SDK, store IDs + share URLs

Revised AC

• contract_signed added to EmailType enum
• groupme_group_id + groupme_share_url columns on teams table (Alembic migration)
• send_contract_signed_email() function in services/email.py — includes team name + GroupMe share link
• Contract signing endpoint calls email function after successful signing
• Email logged in email_log table
• 11 GroupMe groups created via SDK, IDs + share URLs stored in teams table
• End-to-end: sign contract → email arrives → click GroupMe link → join group

## Code Exploration Findings (2026-03-24) ### Contract signing endpoint `POST /api/players/{player_id}/contract` in `src/basketball_api/routes/players.py:249-320` - Sets contract_status, contract_signed_at, contract_signed_by, contract_signed_ip - **Currently sends NO email** — just DB update + log ### Email architecture basketball-api uses **gmail-sdk directly** (NOT pal-e-mail). All emails built inline in `src/basketball_api/services/email.py`: - 6 existing email functions (confirmation, reminder, roster_export, tryout_announcement, password_reset, jersey_reminder) - Uses `_brand_wrapper()` for Westside red/black styling - Logs to `email_log` table ### EmailType enum (models.py:47-51) Current values: `registration`, `reminder`, `roster_export`, `announcement` Missing: `contract_signed` ### Revised File Targets - `src/basketball_api/models.py` — add `contract_signed` to EmailType enum, add `groupme_group_id` + `groupme_share_url` to Team model - `alembic/versions/xxx_add_groupme.py` — migration for new Team columns - `src/basketball_api/services/email.py` — add `send_contract_signed_email()` following existing pattern - `src/basketball_api/routes/players.py` — call `send_contract_signed_email()` after successful contract signing - `scripts/create_groupme_groups.py` — one-time: create 11 groups via SDK, store IDs + share URLs ### Revised AC - [ ] `contract_signed` added to EmailType enum - [ ] `groupme_group_id` + `groupme_share_url` columns on teams table (Alembic migration) - [ ] `send_contract_signed_email()` function in services/email.py — includes team name + GroupMe share link - [ ] Contract signing endpoint calls email function after successful signing - [ ] Email logged in email_log table - [ ] 11 GroupMe groups created via SDK, IDs + share URLs stored in teams table - [ ] End-to-end: sign contract → email arrives → click GroupMe link → join group

forgejo_admin referenced this issue 2026-03-24 07:12:01 +00:00

groupme-mcp: MCP server for GroupMe group management + messaging #157

forgejo_admin commented 2026-03-24 07:12:41 +00:00

Author

Owner

Copy link

Scope Review: NEEDS_REFINEMENT

Review note: review-304-2026-03-24
Two file target and acceptance criteria issues found before this ticket is agent-ready.

• Wrong models path: src/basketball_api/models/groupme.py does not exist — repo uses a single models.py file, not a models/ package. Fix to src/basketball_api/models.py or scope a package refactor.
• Missing edge case criteria: parent.phone is nullable and player.team_id is nullable. Contract signing can happen before team assignment or without a phone number. Acceptance criteria must specify behavior for both cases (skip invite? queue? log warning?).

## Scope Review: NEEDS_REFINEMENT Review note: `review-304-2026-03-24` Two file target and acceptance criteria issues found before this ticket is agent-ready. - **Wrong models path**: `src/basketball_api/models/groupme.py` does not exist — repo uses a single `models.py` file, not a `models/` package. Fix to `src/basketball_api/models.py` or scope a package refactor. - **Missing edge case criteria**: `parent.phone` is nullable and `player.team_id` is nullable. Contract signing can happen before team assignment or without a phone number. Acceptance criteria must specify behavior for both cases (skip invite? queue? log warning?).

forgejo_admin referenced this issue 2026-03-24 07:12:59 +00:00

groupme-mcp: MCP server for GroupMe group management + messaging #157

forgejo_admin commented 2026-03-24 07:13:26 +00:00

Author

Owner

Copy link

Review Fixes (2026-03-24)

Addressing review-304-2026-03-24 findings:

1. Wrong file path

Corrected: the repo uses a single src/basketball_api/models.py, not a models/ package. GroupMe columns get added to the existing Team class in models.py. No new models/groupme.py file.

Revised file targets:

• src/basketball_api/models.py — add groupme_group_id + groupme_share_url to Team model, add contract_signed to EmailType enum
• alembic/versions/xxx_add_groupme.py — migration for new columns
• src/basketball_api/services/email.py — add send_contract_signed_email()
• src/basketball_api/routes/players.py — call email after contract signing
• scripts/create_groupme_groups.py — one-time group creation + DB population

2. Nullable edge cases

parent.phone and player.team_id are both nullable. Contract signing with missing data:

• No team assignment (team_id IS NULL): skip GroupMe invite, log warning. Email still sent but without GroupMe link — just "Welcome to Westside, team assignment coming soon."
• No parent phone: doesn't matter — we use share link in email, not phone-based invite. Parent just needs an email address (which is required).

These are best-effort: contract signing always succeeds. GroupMe invite is a bonus, not a gate.

## Review Fixes (2026-03-24) Addressing review-304-2026-03-24 findings: ### 1. Wrong file path Corrected: the repo uses a single `src/basketball_api/models.py`, not a `models/` package. GroupMe columns get added to the existing `Team` class in `models.py`. No new `models/groupme.py` file. Revised file targets: - `src/basketball_api/models.py` — add `groupme_group_id` + `groupme_share_url` to Team model, add `contract_signed` to EmailType enum - `alembic/versions/xxx_add_groupme.py` — migration for new columns - `src/basketball_api/services/email.py` — add `send_contract_signed_email()` - `src/basketball_api/routes/players.py` — call email after contract signing - `scripts/create_groupme_groups.py` — one-time group creation + DB population ### 2. Nullable edge cases `parent.phone` and `player.team_id` are both nullable. Contract signing with missing data: - **No team assignment** (`team_id IS NULL`): skip GroupMe invite, log warning. Email still sent but without GroupMe link — just "Welcome to Westside, team assignment coming soon." - **No parent phone**: doesn't matter — we use share link in email, not phone-based invite. Parent just needs an email address (which is required). These are best-effort: contract signing always succeeds. GroupMe invite is a bonus, not a gate.

forgejo_admin commented 2026-03-24 07:38:59 +00:00

Author

Owner

Copy link

Architecture Update: Outbox Pattern (2026-03-24)

Problem

If GroupMe API or email service is down when a parent signs their contract, the inline call fails and the parent gets no welcome email. Contract signing should never fail because of an external service.

Solution: Event Outbox

Contract signing writes an event to an outbox table (same DB transaction). A worker processes events asynchronously.

Contract Signed (sync, same transaction)
    → INSERT INTO outbox (event_type='contract_signed', payload={player_id, team_id, parent_email}, status='pending')
    → UPDATE player SET contract_status='signed'
    → COMMIT (both succeed or both fail)

Worker (cron, every 30s)
    → SELECT * FROM outbox WHERE status='pending'
    → For each event:
        1. Look up team.groupme_share_url
        2. Send welcome email via gmail-sdk with GroupMe link
        3. Log in email_log
        4. UPDATE outbox SET status='processed'
    → If send fails: leave as 'pending', retry next cycle

Revised File Targets

• src/basketball_api/models.py — add groupme_group_id + groupme_share_url to Team, add contract_signed to EmailType enum, add Outbox model (id, event_type, payload JSON, status, created_at, processed_at)
• alembic/versions/xxx_add_groupme_and_outbox.py — migration for Team columns + outbox table
• src/basketball_api/services/email.py — add send_contract_signed_email()
• src/basketball_api/services/outbox.py — worker logic: poll pending events, process, mark done
• src/basketball_api/routes/players.py — contract signing writes outbox event (NOT inline email call)
• scripts/create_groupme_groups.py — reconciliation script: query GroupMe for existing groups, match to teams by name, store IDs. Idempotent.

Reconciliation Script Logic

For each team in DB:
    1. Query GroupMe API for all groups
    2. Match by name (normalized: lowercase, strip "WKQ" prefix)
    3. If match: store existing group_id + share_url
    4. If no match: create via SDK, store new group_id + share_url
    5. Interactive: show proposed matches, confirm before committing

Why Outbox > Inline Call

• Contract signing NEVER fails due to external services
• Retries are automatic (pending events get reprocessed)
• Idempotent (event ID prevents double-processing)
• Observable (query pending events = health check)
• Extensible (add more handlers without changing contract endpoint)

Revised AC

• Alembic migration: groupme_group_id + groupme_share_url on teams, outbox table
• Contract signing writes contract_signed event to outbox (same transaction)
• Worker processes pending events: sends welcome email with GroupMe share link
• Failed sends stay pending and retry next cycle
• Reconciliation script: matches existing GroupMe groups to teams, stores IDs
• 11 GroupMe groups exist with IDs stored in DB
• End-to-end: sign contract → event in outbox → worker sends email → parent clicks link → joins group

## Architecture Update: Outbox Pattern (2026-03-24) ### Problem If GroupMe API or email service is down when a parent signs their contract, the inline call fails and the parent gets no welcome email. Contract signing should never fail because of an external service. ### Solution: Event Outbox Contract signing writes an event to an outbox table (same DB transaction). A worker processes events asynchronously. ``` Contract Signed (sync, same transaction) → INSERT INTO outbox (event_type='contract_signed', payload={player_id, team_id, parent_email}, status='pending') → UPDATE player SET contract_status='signed' → COMMIT (both succeed or both fail) Worker (cron, every 30s) → SELECT * FROM outbox WHERE status='pending' → For each event: 1. Look up team.groupme_share_url 2. Send welcome email via gmail-sdk with GroupMe link 3. Log in email_log 4. UPDATE outbox SET status='processed' → If send fails: leave as 'pending', retry next cycle ``` ### Revised File Targets - `src/basketball_api/models.py` — add `groupme_group_id` + `groupme_share_url` to Team, add `contract_signed` to EmailType enum, add `Outbox` model (id, event_type, payload JSON, status, created_at, processed_at) - `alembic/versions/xxx_add_groupme_and_outbox.py` — migration for Team columns + outbox table - `src/basketball_api/services/email.py` — add `send_contract_signed_email()` - `src/basketball_api/services/outbox.py` — worker logic: poll pending events, process, mark done - `src/basketball_api/routes/players.py` — contract signing writes outbox event (NOT inline email call) - `scripts/create_groupme_groups.py` — reconciliation script: query GroupMe for existing groups, match to teams by name, store IDs. Idempotent. ### Reconciliation Script Logic ``` For each team in DB: 1. Query GroupMe API for all groups 2. Match by name (normalized: lowercase, strip "WKQ" prefix) 3. If match: store existing group_id + share_url 4. If no match: create via SDK, store new group_id + share_url 5. Interactive: show proposed matches, confirm before committing ``` ### Why Outbox > Inline Call - Contract signing NEVER fails due to external services - Retries are automatic (pending events get reprocessed) - Idempotent (event ID prevents double-processing) - Observable (query pending events = health check) - Extensible (add more handlers without changing contract endpoint) ### Revised AC - [ ] Alembic migration: `groupme_group_id` + `groupme_share_url` on teams, `outbox` table - [ ] Contract signing writes `contract_signed` event to outbox (same transaction) - [ ] Worker processes pending events: sends welcome email with GroupMe share link - [ ] Failed sends stay `pending` and retry next cycle - [ ] Reconciliation script: matches existing GroupMe groups to teams, stores IDs - [ ] 11 GroupMe groups exist with IDs stored in DB - [ ] End-to-end: sign contract → event in outbox → worker sends email → parent clicks link → joins group

forgejo_admin referenced this issue from a commit 2026-03-24 07:51:49 +00:00

feat: GroupMe data model, outbox pattern, and contract welcome email

forgejo_admin referenced this issue from a pull request that will close it, 2026-03-24 07:52:43 +00:00

feat: GroupMe data model, outbox pattern, contract welcome email #158

forgejo_admin referenced this issue 2026-03-24 07:56:24 +00:00

feat: GroupMe data model, outbox pattern, contract welcome email #158

forgejo_admin added the

status:approved

label 2026-03-24 07:56:25 +00:00

forgejo_admin commented 2026-03-24 08:00:08 +00:00

Author

Owner

Copy link

Architecture Diagram Updated (2026-03-24)

Outbox pattern + admin trigger endpoint now documented in the data flow diagram:
https://pal-e-docs.tail5b443a.ts.net/notes/arch-dataflow-westside-basketball

Shows: contract signing → outbox (same transaction) → admin/cron triggers worker → welcome email with GroupMe link → parent self-joins.

## Architecture Diagram Updated (2026-03-24) Outbox pattern + admin trigger endpoint now documented in the data flow diagram: https://pal-e-docs.tail5b443a.ts.net/notes/arch-dataflow-westside-basketball Shows: contract signing → outbox (same transaction) → admin/cron triggers worker → welcome email with GroupMe link → parent self-joins.

forgejo_admin closed this issue 2026-03-24 08:06:46 +00:00

forgejo_admin referenced this issue 2026-03-24 09:41:39 +00:00

Fix: reconciliation script should create groups with requires_approval=false #159

forgejo_admin referenced this issue 2026-03-27 21:18:55 +00:00

fix: set requires_approval=false on new GroupMe groups #195

Sign in to join this conversation.

No Branch/Tag specified

Branches Tags

main

220-registration-admin-email

221-admin-leads-endpoint

222-dedup-interest-leads

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   

domain:backend

  

domain:devops

  

domain:frontend

  

status:approved

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

  

type:feature

New functionality

No labels

domain:backend

domain:devops

domain:frontend

status:approved

status:in-progress

status:needs-fix

status:qa

type:bug

type:devops

type:feature

Milestone

Clear milestone

No items

No milestone

Projects

Clear projects

No items

No project

Assignees

Clear assignees

No assignees

1 participant

Notifications

Subscribe

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#156

No description provided.