Review: Stripe ↔ DB data reconciliation: parent names, tournament status, orphan charges

Verdict: NEEDS_REFINEMENT

Template Completeness

• [x] Type — Feature
• [x] Lineage — Standalone, discovered during manual audit
• [x] Repo — ldraney/westside-basketball
• [x] User Story — present
• [x] Context — detailed, 5 categories of data inconsistency documented
• [x] File Targets — 2 modify/create, 2 do-not-touch
• [x] Feature Flag — none (appropriate for rake task)
• [x] Acceptance Criteria — 5 items, all verifiable
• [x] Test Expectations — present with run command
• [x] Constraints — 5 constraints including idempotency and secret handling
• [x] Checklist — present
• [x] Related — present
• [x] Validation Queries — bonus section with concrete reproduction commands

Traceability

• [x] story:WS-S12 label — "As an admin, I want to manage user accounts (reset passwords, view profiles) so that I can support parents directly"
• [x] story note verified — WS-S12 found in project-westside-basketball user-stories section under Admin (Marcus)
• [~] story alignment — WS-S12 is a weak match for Stripe/DB reconciliation. The ticket's user story ("I want the database to accurately reflect all Stripe payment activity and parent identities") maps more closely to WS-S9 ("track payment status per player") or WS-S33 ("bill players for tournament entry fees at team-specific rates"). The "parent identities" angle loosely connects to WS-S12 but the bulk of the work is payment reconciliation. [LABEL] Consider relabeling to story:WS-S9.
• [x] arch:rails label — present on board item
• [ ] arch note MISSING — search_notes("arch-rails") returned empty. [SCOPE] Create architecture note arch-rails for the Rails component.
• [x] Forgejo issue — #131, open

File Targets

• [x] lib/tasks/stripe_reconciliation.rake — NEW file. Directory lib/tasks/ exists with 2 existing rake files (seed_tournament_attendance.rake, tournament_email.rake). Correct location for new rake task.
• [x] app/models/player_tournament.rb — EXISTS (337 bytes). Model defines PAYMENT_STATUSES = %w[pending paid_stripe paid_cash paid_venmo waived]. The paid_stripe status referenced in AC #2 is a valid enum value. Schema has amount_cents (integer, nullable) and payment_status (string, default "pending").
• [x] Do-not-touch boundaries verified — app/views/admin/** and app/controllers/** correctly excluded from scope.

Repo Placement

Dependencies

• Board item #1660 "Add missing env vars to rails-env k8s secret (Keycloak + Stripe + APP_URL)" is in backlog. If STRIPE_API_KEY is not set on the production pod, the rake task's StripeClient.new call (which defaults to ENV["STRIPE_API_KEY"]) will fail. However, the app already has working Stripe integration (webhooks at app/controllers/webhooks_controller.rb, payment status at app/services/stripe_client.rb), indicating the key is likely available. Not a hard blocker.
• The seed_tournament_attendance.rake task (PR #125, merged) creates the PlayerTournament records that this task reconciles. This is upstream and already done.
• No blocking in_progress items found on the board.

Acceptance Criteria

• 5 criteria, all testable by an agent.
• AC #1 (rake task runs audit): Clear — run bin/rails stripe:reconcile and verify output.
• AC #2 (tournament status update): Clear — 16 pending records with matching paid orders get updated to paid_stripe. The matching logic (player_id + amount_cents) is specified in constraints. Note: could produce false matches if a player has multiple paid orders with the same amount, but this is an edge case the implementer can guard against.
• AC #3 (markdown report): Clear — output covers payment gaps, orphan charges, data quirks.
• AC #4 (idempotent): Clear — re-run produces same result without duplication.
• AC #5 (orphan charge email matching): Clear — orphans with known parent emails are flagged.
• Validation queries section provides concrete reproduction commands for post-implementation verification. Well-structured.

Blast Radius

• Low blast radius. Creates 1 new rake task file, modifies no existing files structurally.
• Data writes are confined to player_tournaments.payment_status column updates (pending → paid_stripe).
• No schema migrations, no controller changes, no view changes.
• Existing StripeClient service (app/services/stripe_client.rb) provides Stripe API access patterns the rake task can reuse.
• The orderstatus enum values are: pending, paid, refunded, canceled — confirmed in db/schema.rb.
• The admin dashboard views that display this data are explicitly out of scope (separate ticket).

Decomposition Assessment

• 2 file targets in 1 repo — under threshold.
• 5 acceptance criteria — at threshold, not over.
• Estimated agent work: <5 minutes. Single rake task creation, no schema changes, clear patterns from existing seed_tournament_attendance.rake and StripeClient.
• No decomposition needed.

Recommendation

• [SCOPE] Create architecture note arch-rails for the Rails component in pal-e-docs. The arch:rails label is used across multiple board items but has no backing documentation.
• [LABEL] Consider relabeling from story:WS-S12 to story:WS-S9. WS-S12 ("manage user accounts") is a weak fit for Stripe/DB payment reconciliation. WS-S9 ("track payment status per player") aligns directly with the ticket's purpose. If WS-S12 is kept, the rationale should be documented (parent name cleanup touches user accounts).