v2 Admin Spec — Billing & Payments

Billing & Payments

Domain 7 of the Enhanced Candidate A navigation structure. Contains: Payment Overview (NEW), Payments (transaction history), Payment Plans, Coupons, Family Plans (NEW), Scholarships & Deferments (NEW). This domain is entirely new. No current admin backend section exists for billing. All billing operations currently require Stripe Dashboard + Google Sheets. Primary workflows served: WF2 (Student Support — billing questions), WF3 (Semester Close — payment setup), WF4 (Daily Monitoring — failed payment alerts)

1. Domain Overview

Billing & Payments is the highest-impact new domain. The interview identified payment/billing management as the single most impactful addition (Interview Q5, Q9). This domain provides two levels of billing integration:

• Aggregate level (this domain): Payment Overview dashboard, payment configuration (plans, coupons), and special arrangement management (families, scholarships, deferments)
• Individual level: Student Detail Page > Payments tab (specified in 03-student-management.md)

The Dashboard (02-dashboard.md) surfaces billing alerts as tiles that link to either Payment Overview or directly to affected student profiles.

Workarounds Eliminated

Data Architecture

Existing tables (already in QuranFlow database):

• subscriptions: id, stripeSubscriptionId, user_link_level_tag_id, payment_plan_id, coupon_id, status, start_date, end_date, next_billing_date, cycles, cycles_remaining, next_semester_setup_status
• payment_plans: name, type (semester/year/family/discount), sub_type (OneTime/Installment), amount, cycles, currency, product_id
• coupons: name, semester_id, short_code, percentage_off, expiry_date (synced to Stripe)

New tables (assumptions from WS2 — to be validated):

• family_groups: id, name, primary_contact_user_id, discount_coupon_id, created_at
• family_members: id, family_group_id, user_id, member_discount_percentage, semester_status, joined_at
• scholarship_programs: id, name, discount_percentage, eligible_plan_types, description, renewal_policy
• student_scholarships: id, user_id, scholarship_program_id, start_date, end_date, renewal_status, coupon_id
• student_deferments: id, user_id, subscription_id, pause_start, resume_date, reason, status, created_by
• billing_alerts: id, type, user_id, stripe_event_id, data, status (new/acknowledged/resolved), created_at
• semester_repeat_eligibility: id, user_id, semester_id, source (auto_from_fail / manual_override), added_by_admin_id (null for auto), created_at.

Stripe API endpoints used:

• Customer: Customer::search(), customer by ID
• Subscription: GET /v1/subscriptions/{id}, POST /v1/subscriptions/{id} (update/cancel)
• Invoice: GET /v1/invoices?customer={id}, GET /v1/invoices/upcoming
• Payment Method: via subscription expand (expand[]=default_payment_method)
• Pause: subscription.pause_collection with resumes_at
• Coupon: create/apply via subscription

Assumption (OQ6): Single Stripe account (fe-checkout). Assumption (OQ4): QuranFlow subscriptions table is canonical after customer ID reconciliation.

Stripe Data Patterns (domain-specific)

This domain follows the Stripe data patterns defined in 01-global-patterns.md Section 5. Domain-specific notes:

2. Screen: Payment Overview (NEW)

2.1 Purpose

Aggregate billing health dashboard. Shows subscription metrics, surfaces at-risk accounts, and provides actionable alert lists. This is where the admin starts when investigating billing issues.

2.2 Entry Points

• Sidebar: Billing & Payments > Payment Overview
• Dashboard: Failed Payments tile click (navigates here, scrolled to Failed Payments alert section)
• Note: Past due subscriptions surface through the Failed Payments dashboard tile (there is no separate "Past Due" tile — past due is a subsection within Payment Overview, see Section 2.5)

2.3 Layout

Metrics bar (top) → Alert sections (scrollable, expandable) → At-risk accounts list

2.4 Metrics Bar

Five metric cards in a horizontal row (uses Metric Card pattern from 01-global-patterns.md Section 4.5):

2.5 Alert Sections

Each section is an expandable card with a count badge and a list of affected students. Sections are sorted by severity: Failed Payments > Past Due > Missing Subscriptions > Expiring Scholarships > Deferment Resume Due > Recently Cancelled.

A. Failed Payments (source: billing_alerts table, webhook: invoice.payment_failed)

B. Past Due Subscriptions (source: Stripe subscription.status = past_due, periodic sync to billing_alerts)

C. Missing Subscriptions (source: cross-reference: users with user_activation_status=1 and user_type=3 but no active Stripe subscription via subscriptions table)

D. Expiring Scholarships (source: student_scholarships where end_date within 30 days of today and renewal_status != 'Renewed')

E. Deferment Resume Due (source: student_deferments where resume_date within 14 days of today and status = 'Active', cross-checked with Stripe subscription.pause_collection.resumes_at)

F. Recently Cancelled (source: billing_alerts where type=customer.subscription.deleted and created_at within last 14 days)

2.6 Actions (page-level)

2.7 States

2.8 Role Visibility

• Admin: Full view + all actions (Refresh, Export, Send Reminder, Cancel, Retry Payment, Extend, etc.)
• View-Only / Support Staff: Full view of metrics and alert lists. No action buttons except "View Student" links (which navigate to Student Detail).
• TA: Hidden (sidebar item not visible)

2.9 End Checklist Payment Setup Queue (NEW)

2.9.1 Purpose

Per-student payment setup surface for the currently-closing semester's End Checklist Step 4: Set Up Automatic Payments (see 04-semester-management.md §3.8). Shows each eligible passing L1–L4 student's payment_setup_status (Pending / Created / Failed / Exempted), lets the admin trigger Stripe subscription creation individually or in bulk, and auto-completes Step 4 once every eligible student is created (or exempted).

2.9.2 Scope

Students where user_link_level_tag.final_status = 'pass' AND level IN (1, 2, 3, 4) for the closing semester.

• Mastery exclusion: Mastery students are excluded from this queue — they self-enroll in their next elective (per CANONICAL-CONTENT-2026-04-21.md §2.4).
• Leaver exclusion: Leavers are excluded — they stay inactive per STAKEHOLDER-ANSWERS-2026-04-21.md. No admin action is required; they independently re-enroll when ready.
• Failing L1–L4 exclusion: Failing students are excluded — they opt in via the Step 3 Pass/Fail email's re-enroll link and set up payment manually during re-enrollment.

2.9.3 Entry Point

• End Checklist Step 4 "Open" link from 04-semester-management.md §3.8 deep-links here with query string ?from=end-checklist&semester=[id] for the "Return to End Checklist" banner affordance.
• Direct sidebar access is not provided; the queue is only meaningful in the Close phase and is reached via the End Checklist.

2.9.4 Data Displayed

2.9.5 Data Model Note

subscriptions.next_semester_setup_status enum values: 'pending' / 'created' / 'failed' / 'exempted'. This attribute is inferred from Stripe subscription state for the student's next-semester subscription:

• pending: no next-semester Stripe subscription exists yet for this student.
• created: a Stripe subscription has been created with a start date on or after the next semester's start_date.
• failed: a creation attempt was made and returned an error (e.g., expired card, declined payment method).
• exempted: admin marked the student as not requiring automatic payment setup (e.g., student on full scholarship with no payment method on file).

2.9.6 Filter Bar

2.9.7 Actions

2.9.8 States

2.9.9 Role Visibility

• Admin: Full view + all actions (Set Up Payment, Bulk Setup, Mark Exempted, Refresh, View).
• View-Only / Support Staff: Full view of queue and statuses. "View" link works. No action buttons for Set Up Payment, Bulk Setup, Mark Exempted, or Refresh.
• TA: Hidden — Step 4 is admin-only (TAs cannot trigger Stripe subscription creation).

2.10 Repeat Checkout Flow (cross-reference, NEW)

2.10.1 Context

The End Checklist Step 3 fail email (see 04-semester-management.md §3.8) includes an opt-in-to-repeat link for failing L1–L4 students. Clicking that link sends the student to a repeat-discount checkout (50% off the next semester's tuition) — this is an existing production flow preserved in v2.

2.10.2 Admin touchpoints

1. Auto-eligibility: When Step 3 sends a fail email to a student, the system writes a semester_repeat_eligibility row with source='auto_from_fail' for the next semester. No admin action required.
2. Manual override: Students who skip a semester and want to repeat a later one are NOT on the auto-generated list. Admin adds them via the per-student "Mark as Repeat-Eligible" action on Student Detail > Actions (see 03-student-management.md §3.10). This writes a semester_repeat_eligibility row with source='manual_override' and records added_by_admin_id.
3. Checkout validation (production): The public repeat-checkout URL looks up semester_repeat_eligibility for the student + semester. If no row exists, checkout rejects with "You are not currently eligible for the repeat discount. Please contact support." If a row exists, the 50% discount is applied.
4. Visibility: Students with enrollment_type='Repeat' are surfaced on the Students list via the Enrollment Type filter (see 03-student-management.md §2.4). The repeat-eligible list itself is not exposed as a standalone admin screen — it is a data projection of the enrollment records + the semester_repeat_eligibility table.
5. Approval: No admin approval is required for the student-side opt-in. The student self-serves the checkout once on the eligibility list (confirmed STAKEHOLDER-ANSWERS-2026-04-22 §C Q2).

2.10.3 Mockup scope

The "Mark as Repeat-Eligible" modal on Student Detail is the primary interactive surface. The repeat-discount checkout is student-facing and out of scope for the admin mockup.

3. Screen: Payments (Transaction History)

3.1 Purpose

View payment transaction records. Relocated from Reports section. Provides a filterable, sortable list of all payment events.

3.2 Entry Points

• Sidebar: Billing & Payments > Payments

3.3 Layout

Filter bar (top) + data table + pagination (bottom). Uses standard Data Table and Filter Bar patterns from 01-global-patterns.md Sections 4.1 and 4.2.

3.4 Filter Bar

3.5 Data Displayed

3.6 Actions

3.7 Pagination

Standard pagination (01-global-patterns.md Section 4.1): 25 records per page, "1-25 of 342" format.

3.8 States

3.9 Role Visibility

• Admin: Full view + Export button
• View-Only / Support Staff: Full view, no Export button
• TA: Hidden

4. Screen: Payment Plans

4.1 Purpose

Define pricing options available at checkout. Enhanced with Stripe sync visibility and active subscriber counts so admins can see plan usage before editing or deleting. Plans are presented in grouped sections (Standard / Family / Scholarships / Discounts) so admins can see scholarships and family pricing inline with standard plans; coupons remain on their own screen (Section 5) as a different stakeholder category.

4.2 Entry Points

• Sidebar: Billing & Payments > Payment Plans

4.3 Layout

Grouped data tables, one section per category, rendered as stacked collapsible cards (all expanded by default). Each section uses the standard Data Table pattern (01-global-patterns.md Section 4.1). No filter bar (dataset too small — typically <15 plans total across all groups). Page-level actions (Create Plan, Verify Stripe Sync) live in the page header and apply across all groups.

Group sections (in order):

Each group section header shows the group name and a count (e.g., "Standard (4)"). Empty groups collapse to a one-line "No [group] plans configured." message.

4.4 Data Displayed

Columns are identical across all four group tables:

4.5 Actions

4.6 States

4.7 Role Visibility

• Admin: Full CRUD + Stripe sync verification across all groups
• View-Only / Support Staff: View all group tables (no Create, Edit, Delete, or Sync buttons)
• TA / Support Staff: Hidden (sidebar item not visible)

5. Screen: Coupons

5.1 Purpose

Manage discount codes. Enhanced with redemption tracking from Stripe, so admins can see coupon usage without opening Stripe Dashboard.

5.2 Entry Points

• Sidebar: Billing & Payments > Coupons

5.3 Layout

Data table. Uses standard Data Table pattern (01-global-patterns.md Section 4.1). No filter bar (typically <20 coupons).

5.4 Data Displayed

5.5 Actions

5.6 States

5.7 Role Visibility

• Admin: Full CRUD
• View-Only / Support Staff: View table + click "Students Using" links. No Create, Edit, Delete.
• TA / Support Staff: Hidden (sidebar item not visible)

6. Screen: Family Plans (NEW)

6.1 Purpose

Manage multi-student family billing arrangements. Replaces Google Sheets tracking. Eliminates workaround W2.

Assumption (OQ1): ~10-20 family plans per semester, justifying full CRUD rather than a simple tagging approach.

6.2 Entry Points

• Sidebar: Billing & Payments > Family Plans
• Student Detail > Payments tab: family name link (e.g., "Ahmed Family" → Family Detail view)

6.3 Layout

Two views:

1. Family List View (default): Data table of all family groups
2. Family Detail View (click a family): Header with family info + members table

6.4 Data Displayed — Family List View

6.5 Data Displayed — Family Detail View

Header section:

Status bar (below header):

• All active: Green bar: "All [N] members have active subscriptions."
• Issues: Orange bar: "[X] of [Y] members have billing issues." — affected members highlighted in table below.

Members table:

6.6 Actions

6.7 States

6.8 Role Visibility

• Admin: Full CRUD (Create, Edit, Delete families; Add, Remove members; Change Family Default Discount; Change Member Discount; Change Member Semester Status)
• View-Only / Support Staff: View family list and detail views, including per-member discount % and semester status columns. "View Student" links work. No Create, Edit, Delete, Add, Remove, or Change actions.
• TA: Hidden (sidebar item not visible)

7. Screen: Scholarships & Deferments (NEW)

7.1 Purpose

Manage students on special billing arrangements. Replaces Google Sheets tracking. Eliminates workarounds W3 (scholarships) and W4 (deferments).

This screen has two sub-sections presented as tabs within the screen (horizontal tab bar below page header, using Tab pattern from 01-global-patterns.md Section 4.11).

7.2 Entry Points

• Sidebar: Billing & Payments > Scholarships & Deferments
• Student Detail > Payments tab: scholarship or deferment link
• Payment Overview: "Expiring Scholarships" section → "Extend" or "View Student"
• Payment Overview: "Deferment Resume Due" section → "Extend" or "Confirm Resume"

7.3 Sub-Tab: Scholarships

Assumption (OQ2): ~15-30 scholarship students, 3 tiers (Full, 50%, 25%).

Two views within the Scholarships tab:

View toggle: "Programs" | "Students" toggle at top of tab content (default: Programs).

7.3.1 Scholarship Programs View

Manages the program definitions themselves.

7.3.2 Scholarship Students View

Manages individual student scholarship assignments.

Filter bar (Students view only):

7.3.3 Scholarship Actions

7.4 Sub-Tab: Deferments

Assumption (OQ3): ~5-10 per semester, 1-semester typical. Uses Stripe pause_collection.

Two sections within the Deferments tab:

Active Deferments (always visible):

Deferment History (collapsed by default, expandable):

7.4.1 Deferment Actions

7.5 States

7.6 Role Visibility

• Admin: Full CRUD on both sub-tabs (Create/Edit/Delete programs; Assign/Remove/Extend/Renew students; Create/Extend/Resume deferments)
• View-Only / Support Staff: View all data on both sub-tabs. "View Student" links work. No action buttons for Create, Edit, Delete, Assign, Remove, Extend, Renew, or Resume.
• TA: Hidden (sidebar item not visible)

8. Cross-Domain Integration Points

This section documents how Billing & Payments connects to other domains. Each integration is specified in detail in the target domain's spec document; this section provides a summary for reference.

8.1 Dashboard (02-dashboard.md)

8.2 Student Detail > Payments Tab (03-student-management.md)

The individual-level billing view. Shows a single student's:

• Current subscription status (from Stripe, real-time)
• Payment history (invoices from Stripe)
• Active scholarship (from student_scholarships)
• Active deferment (from student_deferments)
• Family membership (from family_members) — including the member's semester_status (Regular/Repeat/Mastery) and member_discount_percentage
• Actions: Cancel Subscription (with 4 variants: cancel-now, cancel-at-date, pause-with-unpause-date, send-payment-link — specified in 03-student-management.md §3.7), Apply Coupon, Create Deferment, linked deactivation

8.3 End Checklist Step 4: Set Up Automatic Payments (04-semester-management.md)

End-of-semester billing handoff:

• The End Checklist in Semester Hub (04-semester-management.md §3.8) includes Step 4: Set Up Automatic Payments — a per-student payment-setup surface plus bulk action that creates/updates Stripe subscriptions for passing L1–L4 students for the upcoming semester.
• Step 4's per-student state lives in the End Checklist Payment Setup Queue (§2.9 of this file). End Checklist Step 4's "Open" link deep-links here with ?from=end-checklist&semester=[id].
• Scope: user_link_level_tag.final_status = 'pass' AND level IN (1, 2, 3, 4). Mastery students are excluded (self-enroll); failing students are excluded (opt in via Step 3 email); leavers are excluded (stay inactive).

• Step 4 uses payment_plans (see §4, including the Standard / Family / Scholarships / Discounts grouped presentation) to determine the correct price for each passing student based on their new level and enrollment type.
• Existing scholarship coupons (from §7.3) and family plan coupons (from §6, respecting per-member member_discount_percentage overrides) are carried forward to the new subscriptions automatically.
• Admins can monitor and re-run Step 4 independently from other End Checklist steps. Step 4 auto-completes when every eligible passing L1–L4 student has next_semester_setup_status = 'created' (or 'exempted'). Failed attempts remain visible in the Payment Setup Queue for admin triage — see §2.9 and 04-semester-management.md §3.8 for full completion criteria.

8.4 Student Detail > Actions Tab (03-student-management.md)

Linked deactivation (W8):

• "Deactivate Student" action checks for active Stripe subscription
• If found: combines account deactivation + Stripe subscription cancellation in one action
• Uses subscriptions.stripeSubscriptionId to call Stripe cancel API

9. Webhook Processing

Billing & Payments depends on Stripe webhooks to populate the billing_alerts table and keep cached data current. This section documents the webhook events consumed.

Alert lifecycle: new → acknowledged (admin has seen it) → resolved (issue addressed or auto-resolved by subsequent event). Alerts auto-resolve when a corrective event arrives (e.g., invoice.paid resolves prior invoice.payment_failed for the same subscription).

10. Assumptions and Open Questions

This domain relies on several assumptions documented in 00-spec-index.md. Summary of billing-specific assumptions:

New questions raised by this spec (to be logged in 00-spec-index.md if not already covered):

• BQ1: Should "Send Reminder" on failed payments use email or SMS? Assumed: email via existing Email Management infrastructure.
• BQ2: Should deferment creation require manager approval or is admin self-service sufficient? Assumed: admin self-service (the admin IS the decision-maker per interview).
• BQ3: When a family discount coupon is changed, should it take effect immediately or at next billing cycle? Assumed: next billing cycle (Stripe default behavior for coupon changes).