QuranFlow — Domain & Data Model Specification

Storage-agnostic domain model for the new QuranFlow backend. Every entity, field, and rule below traces to either the stakeholder feedback synthesis (FEEDBACK-SYNTHESIS.md), the adjudicated design review (../mockup/REVIEW.md), the simplification audit (../mockup/AUDIT.md), or the Sakīna mockup source it was built from (../mockup/src/*.jsx). Field names are suggestions, not a schema DDL — the backend team should map them onto whatever store (relational, document, etc.) they choose. Where mockup and source docs disagree, the mockup's adjudicated REVIEW.md resolution wins (per that document's own rule).

1. Entity catalogue

User

The single account type; role and per-role fields distinguish behavior.

Relationships: a coach User has many student Users (via coach_id); a student has one coach at a time but historical Feedback/CoachNote rows may reference a different coach than the student's current one (substitute coaches, prior-semester coaches — see SakCoachStudent's intentional Ust. Rania reference, REVIEW.md A4).

Semester + Week

A student's "current week" is derived (Semester.active + today's date falls within Week.starts_at..ends_at), not stored redundantly per user.

Lesson / Video

Assignment

Submission

The core Y1 object. One row per attempt (not per week) — a week can have up to 2 submissions (the weekly attempt limit) plus any number of revoked/re-recorded takes that don't count, and any number of practice takes attached afterward (see PracticeTake below).

Relationships: one Submission has zero-or-one Feedback (coach hasn't reviewed yet vs has); one Submission may have many PracticeTakes (attached after feedback, don't consume attempts).

Feedback

Coach's response to a Submission. One-to-one with Submission.

PracticeTake

A re-recording the student makes after receiving feedback, to practice a correction. Does not consume a weekly attempt and is not itself submitted for new review — it attaches to the existing Feedback thread.

Session

Live scheduled events: classes, QRC (Qur'an Reading Circle), Office Hours, and (for Y2) one-to-one appointments (modeled separately, see Appointment, because appointments have a distinct per-student booking/attendance lifecycle; Session covers the shared/group event types).

SessionRecording (child of Session, one per occurrence):

SessionWatchProgress (per user, per SessionRecording): percent_watched (drives the thumbnail progress bar and "N% in" / "watched" chip, RecordingRow in sak-schedule.jsx).

Appointment

Year-2-only 1:1 booking. Distinct from Session because it has a per-student lifecycle (booked → attended/missed → escalation) rather than a shared roster.

AppointmentOccurrence (one per week, generated from the Appointment booking or from a one-off this_week_only booking):

CoachNote

Shared, persistent notes about a student, visible to every coach who has ever met with that student — not scoped to the currently-assigned coach. This is what powers SakCoachStudent's "Coach notes" section, explicitly labeled "visible to all coaches", including the intentional Ust. Rania substitute-coach example (REVIEW.md A4).

Message

1:1 async thread between a student and their coach. Text or voice, with a weekly cap and a length guard.

Replaces the old community board; this is 1:1 only (both Y1 and Y2) — one-to-many broadcast is Announcement, below.

Announcement

One-way board post. Replaces the community board (FEEDBACK-SYNTHESIS.md decision #2).

Business rule (REVIEW.md B4): when an announcement duplicates a fact also pushed as a Notification (e.g. a schedule change), the copy must be identical in both places — this is a content-authoring constraint, not a separate field, but worth encoding as a single source-of-truth: the Notification row of type announcement should reference the Announcement.id and render its title, not carry independent copy.

Notification

Per-user delivery record. Types match the mockup's NotifRow type map (sak-schedule.jsx): feedback, session, deadline, announcement.

NotificationPreference (per user, per type): enabled: boolean. Announcements default ON (REVIEW.md B4 — the one exception; all other types' defaults are a product decision left to the backend/product team, but must be explicit per-type toggles, not one master switch, per FEEDBACK-SYNTHESIS.md's "per-type preferences" wishlist item and the mockup's SakNotifSheet/settings screens).

Reminder timing (business rules, not schema): 15-min reminder before student sessions/appointments (SakWeekAtGlance: "Reminders arrive 15 minutes before each session"; SakAppointmentDetail: "Reminder 15 minutes before · every week"); coaches get a 30-min reminder (FEEDBACK-SYNTHESIS.md: "15-min student / 30-min teacher reminders").

TimeLog / Attendance

Auto-derived, not manually entered — retires the "Time Clock Wizard" (FEEDBACK-SYNTHESIS.md coach ask #4).

Attendance (derived view over Session/AppointmentOccurrence + Zoom webhook data): a participant is marked attended if their Zoom join duration is ≥ 75% of the scheduled duration (FEEDBACK-SYNTHESIS.md coach ask #4: "auto-log hours/attendance from Zoom duration ≥75%"). This threshold directly sets AppointmentOccurrence.attendance_status and a parallel SessionAttendance { user_id, session_occurrence_id, attended: boolean, zoom_duration_pct } record for group sessions — addressing the "100% of the time currently, we are going off of assumptions" attendance black hole (FEEDBACK-SYNTHESIS.md top finding #4).

IssueReport

FEEDBACK-SYNTHESIS.md top finding #5: "Report Issue button on every screen"; SakReportIssue (sak-comms.jsx F4).

2. State machines

Submission lifecycle

States (from SubStatusPill, sak-extras.jsx, and REVIEW.md A2's adjudication): in_review → reviewed → heard → practiced.

Weekly cap: at most 2 Submission rows with a non-voided (revoked_at IS NULL) status per (student, week) pair. Revoked submissions do not count.

Appointment lifecycle

Applies to AppointmentOccurrence.attendance_status, with the parent Appointment.booking_mode/status governing whether occurrences continue to be generated.

Post-session survey loop (flagged in FEEDBACK-SYNTHESIS.md as a priority even ahead of the app) is out of scope for this model's stored entities beyond triggering off attendance_status transitions — see Backlog note in §5/6.

3. Business rules table

4. Derived views the UI needs

• Week at a glance (SakWeekAtGlance): for a given student + week, all Session occurrences and AppointmentOccurrences the student is party to, plus the week's submission deadline, grouped by day, in the student's local timezone, each flagged with whether a 15-min reminder is set. Query shape: sessions ∪ appointments ∪ {deadline} filtered to student.enrollment ∩ week, sorted by starts_at_utc, projected into student.timezone.

• Sessions tab — Upcoming (SakSchedule): same underlying data as Week at a Glance but rendered as a day-by-day timeline for the selected date, with a day-strip covering the current week (± adjacent days). The Sessions tab's calendar-icon action routes to Week at a Glance — same data source, two density levels (REVIEW.md B2 — intentionally not merged into one component, but the spec should document that both read from the same query).

• Sessions tab — Recordings (SakScheduleRecordings): SessionRecording rows for sessions the student was enrolled in, joined to SessionWatchProgress, grouped by Week descending (most recent week first), each row showing type chip, title, coach, date, duration, and watched/partial/unwatched state.

• Coach review queue (SakCoachQueue): all Submissions with status = in_review where coach_id = current coach, default sort oldest submitted_at first, with the exact filter set coaches asked for: "Oldest first" (default), "By level", "Fewest submissions" (students with the fewest submissions this term — the coach questionnaire's "filter by number of submissions" ask, surfacing students at risk of disengagement). Each row surfaces: student name, level, week, wait duration, recording duration, and a flag if wait time exceeds the 48-hour SLA.

• Submissions archive (SakSubmissions, student-facing): all of a student's Submissions across all semesters, grouped by recency ("This month" vs "Earlier", progressively disclosed past ~4 rows), each showing week, āyah range, days-since, duration, and SubStatusPill status. Summary stat header: count since semester start + count currently in_review, plus the semester progress strip. Must support multi-semester history (FEEDBACK-SYNTHESIS.md wishlist: "full multi-semester history").

• Coach's student record — submission history (SakCoachStudent): same underlying Submission rows as the student archive, scoped to one student, visible to the assigned coach (and any coach with a CoachNote on this student).

• "Working on" tag rollup (SakCoachStudent): derived, not stored — collate the last 3 CoachNote/Feedback.notes entries for a student into short tag phrases (e.g. "Madd length", "Ghunnah hold"). Explicitly called out in the mockup as AI-collated ("from your last 3 notes") — this is the one AI-assist wishlist item beyond feedback summarization that already appears in a screen; treat it as a lightweight summarization call over the same 3-note window, cached per student.

• Announcements board (SakAnnouncements): all Announcements where audience = everyone OR (audience = level AND level_value = viewer's level) OR (audience = year AND year_value = viewer's year), pinned posts first, then reverse-chronological, with filter chips "All / My level / Schedule" (the third filter is a content-category filter, not an audience filter — likely a category field on Announcement distinct from audience, worth clarifying with product before implementation).

• Notifications inbox (SakNotifSheet): all Notifications for the viewer, with a pinned "Announcements · N pinned" row above an "New · N" unread group, then "Earlier" — grouped by read_at IS NULL then reverse chronological. Announcement-type rows deep-link to the Announcements board (REVIEW.md B4), not to inline content.

• Coach schedule (SakCoachSchedule): the coach's own Sessions and AppointmentOccurrences for a day, each row tagged Recurring or This week only for 1:1 rows (REVIEW.md D1), with a live "Start" CTA when a session is imminent, in the coach's own timezone (not the student's).

• Recordings calendar (cross-cutting, feeds both SakScheduleRecordings and a coach-side equivalent): SessionRecording grouped by Week, the calendar-view requirement from the wishlist rather than a flat reverse-chronological list.

5. Integration points

• Zoom

  • Every Session occurrence and AppointmentOccurrence carries a zoom_join_url (per-occurrence, since recurring meetings may rotate links or use a personal-meeting-room pattern — confirm with IT which Zoom account model is in use).
  • Join-duration webhook: Zoom's participant join/leave events feed the Attendance derivation (TimeLog/attendance §1) — percentage of scheduled duration attended, threshold ≥75% for "attended." This webhook is the single source of truth for attendance; no manual RSVP/check-in exists in the mockup.
  • Recording ingestion: Zoom cloud recording completion webhook creates a SessionRecording row and triggers processing (thumbnail, duration).
  • Embedded Zoom (in-app join without leaving the app) is explicitly backlog, not in scope (FEEDBACK-SYNTHESIS.md "Explicit backlog").

• Push notifications

  • Dispatches Notification rows per user per NotificationPreference (per-type opt-in/out, announcement defaulting ON).
  • Timing rules: 15 minutes before student sessions/appointments; 30 minutes before coach sessions. Deadline reminders keyed to Week.ends_at (Sunday 11:59 PM, student-local).
  • Feedback-ready and message-received notifications fire on Feedback creation and Message creation respectively.

• Email automation

  • Absence escalation: triggered when AppointmentOccurrence .consecutive_absence_count reaches 2 for a student — sends an email to program staff (recipient list is a program/admin configuration, not modeled here). This was flagged as a priority even ahead of the app itself (FEEDBACK-SYNTHESIS.md decisions).
  • Coach 48-hour SLA breach could analogously trigger an internal alert email, though the mockup only surfaces this as a UI promise/flag in the coach queue, not a confirmed email rule — flag for product confirmation.
  • Post-session survey loop ("attended? why not?" with AI-aggregated trends) is called out as a priority in the IT discussion but has no corresponding mockup screen; model it as a follow-up Survey/ SurveyResponse pair keyed to AppointmentOccurrence/SessionAttendance if/when scoped — currently backlog.

• Calendar export (.ics)

  • "Add to calendar" / "Add the week to my calendar" / "Add start date to calendar" actions appear on SakWeekAtGlance, SakAppointmentDetail, SakSessionDetail(QRC), and SakTodayCountdown. Each should generate an .ics VEVENT (or series, for recurring Appointments) using the same UTC starts_at/duration fields as the in-app schedule, so the exported calendar and the in-app timezone rendering never disagree — the exact bug class the timezone-math fix (REVIEW.md A5) was created to prevent.

• Stripe

  • A subscription "passport" per student, per the mockup's Profile → Subscription screens (AUDIT.md per-screen log: "Subscription — Passport card", "Manage subscription — Plan card").
  • Model as Subscription { id, user_id, stripe_customer_id, stripe_subscription_id, plan, status, current_period_end }, with the Manage Subscription screen deep-linking to the Stripe customer portal (mockup dropped a duplicate in-app portal link, keeping one path to Stripe — AUDIT.md: "dropped duplicate Stripe portal link at bottom").
  • Payment processing and PCI-relevant data stay entirely on Stripe's side; the backend only stores the customer/subscription IDs and cached status/plan for display.

6. Explicitly out of scope (backlog)

Carried forward from FEEDBACK-SYNTHESIS.md and REVIEW.md so the schema doesn't over-build for features not yet committed:

• Two-coach model for Year 2 (rejected for now — single assigned coach only).
• 20 appointment credits per semester (make-up sessions) — not modeled; revisit if scheduling capacity demands it.
• AI feedback summarization and AI post-session-survey trend analysis — beyond the "Working on" tag rollup (§4), which is a small, already-scoped exception.
• Student-centric QRC scheduling algorithm (cost-vs-value unresolved).
• Ready-made lesson asset banks for coaches.
• Peer community channel revival.
• Embedded Zoom (in-app playback without leaving the app).
• Program-strategy items (enrollment policy, email SLA, marketing) — not a data-model concern.