[Urgent · V1] Unified Base Account + Progressive Roles + Child Sub-Profiles + Dual-Sided Verified Hours Ledger

URGENT V1 foundation — CEO 2026-10-25.
Parent epic of the Living-CV / Account Platform Track (children below).
Ships in parallel with desk density (#438 · #449 · #451 · #454) and forms (#466) — do not block macros on this; do not deprioritise either.
Brand: Azure #1D9BF0 · Sky #8ECDF8 · Wash #E3ECF6 · Ink #0F1419.

UX overlay (CEO 2026-10-31) — ADOPT Learning vs Teaching: Product labels Learning / Teaching (not Parent/Tutor as mode names). One base account + additive Become a Tutor + learner sub-profiles; Centres = avatar Workspaces link (not a third mode). Full screen-by-screen UX + ACs are in-body below → § Learning vs Teaching UX (CEO 2026-10-31) (merged from comment so eng cannot miss it). Guide/Help copy: #541 under #516. Do not open a parallel Unified Account epic.

1. Goal / problem

Rigid early parent/tutor lock + one-login multi-child collision mixes student records and histories. Students who later become Independent Tutors lose verified learning history. Hours must start recording now so history exists when roles upgrade. Public “Verified Hours” without an immutable ledger will drift.

Replace (live destructive behaviour):

• complete_onboarding(_choice) — supabase/migrations/20260918010000_add_onboarding_role.sql + src/features/auth/OnboardingGate.tsx
• switch_role(_role) — 20260918020000_add_switch_role.sql + src/features/settings/sections/account-section.tsx (switching currently deletes the other role)

Learning vs Teaching UX (CEO 2026-10-31)

In-body product/UX overlay — eng must read this section (not only comments). Merged from ADOPT comment on 2026-10-31 so developers cannot miss it.
Verdict: ADOPT one base account + Learning / Teaching modes + learner sub-profiles + progressive activation.
Rejected: second Gmail · keep Parent XOR Tutor delete · Student vs Parent as separate account types · Centres as a third header mode.

This section is the product/UX overlay for eng mapping. It does not replace the other numbered §§ in this issue (schema, ledger, claim, RPCs). Map UI below onto the existing additive-role + LearnerProfile model.

Problem (live today)

• Onboarding still forces Parent XOR Tutor; complete_onboarding / switch_role delete the sibling role.
• /en/account defaults to parent-centric copy (“Your child’s lessons”) — breaks independent students and dual-role users.
• Tutors cannot tell Parent-managed vs Independent Student contact context at first touch.
• Real users need both sides (parent who also tutors; student who later teaches) without a second email.

Core product logic (labels locked)

Analogy: Airbnb Travelling/Hosting · Carousell buy/sell · Taobao buyer + seller tools — one identity, context switcher.

Screen-by-screen UX (eng map)

A. Sign-up / first onboarding — zero lock-in

• Where: Auth modal / post-auth (OnboardingGate.tsx).
• Remove mandatory “What brings you to MatchMax? (Parent vs Tutor)” blocking dialog as the only path.
• Required: Display Name + Phone/Email (Handle per existing §4.1 / §15).
• Optional intent pills only; Skip allowed. Always create base user + default Myself LearnerProfile.
• Progressive: navbar shows Become a Tutor until Teaching is activated. If user arrived via tutor guide / clicked Become a Tutor pre-auth, route into tutor onboarding wizard after signup.

B. Header — mode switcher

• Where: Global header (desktop top-right by avatar; mobile slide-out).
• Learning-only: Become a Tutor link; no mode switcher yet.
• After Teaching activated: replace with pill toggle
  Learning (Active) | Switch to Teaching ↔ Switch to Learning | Teaching (Active).
• Centre staff: avatar dropdown Workspaces section → Centre Portal link (not a mode pill).

C. /en/account dashboard

Short-term copy (ship ahead of full role rewrite — can land with #516 content track):

• “Your child’s lessons” → Registered Lessons (or Active Cases & Lessons).
• “Find a tutor for your child” → Find a Tutor.

Who is learning? bar (Learning Mode only):

• Hidden until ≥1 child exists.
• + Add a Child → modal: First name/nickname, Curriculum, Year/Grade.
• Then horizontal pills: [ All ] [ Myself ] [ Anson (IB Y11) ] [ + Add Child ] filtering Registered Lessons / Saved Tutors / Verified Hours.

Teaching Mode view:

• Prioritise incoming case leads & active students.
• Surface public tutor card + verification status (Blue Tick / stated) at top of dashboard.
• Verified teaching hours + fee status.

D. Booking / request modal

• First step: Who is this lesson for?
  • For myself (Independent Student) → Myself profile.
  • For my child (Parent/Guardian) → pick or create child inline.
• Persist on case: learner_type: independent_student | parent_child (+ learnerProfileId already required in §4.3).

E. Tutor case / lead cards

• Parent booking: blue badge [Parent Account] — Contact: Mrs. Chan (Parent); Student: Anson · IB Y11 · …
• Independent: green badge [Independent Student] — Contact: Hauzton (Student booking directly); …
• Lets tutors frame the first message correctly.

F. Guardrails

• Self-booking: disable Book on own tutor listing (“You cannot book your own listing”).
• Settings: remove destructive Switch Role wipe. Replace with Active Capabilities:
  • Learning & Parent Management: Active (manage child profiles)
  • Independent Tutor Profile: Active / Paused / Hidden (visibility toggle; no data delete) or Activate Tutor Profile
• Inbox threads tagged [Learning] / [Teaching] so dual-role users don’t mix client msgs with their child’s tutor.

Acceptance criteria (UX overlay — in addition to §11)

• Signup: name + contact (+ handle); no forced exclusive Parent/Tutor lock-in.
• Become a Tutor adds Teaching without deleting learning data / children.
• Pure student never sees “child” language or Who-is-learning pills until they add a child.
• Parent can manage multiple children; lessons/hours stay per learnerProfileId.
• Tutor case cards show Parent vs Independent Student contact context.
• User cannot book own listing.
• Mode switcher appears only after Teaching is activated; labels are Learning / Teaching.
• Centres remain avatar → Workspaces link (not a third mode).
• Short-term: /en/account uses Registered Lessons / Find a Tutor (neutral) even before full switcher ships.

Short-term ship-ahead note

Neutralize child-centric copy on /en/account now (independent of RPC rewrite). Soften or hide exclusive switcher UI until additive RPCs land. Public Guide/Help alignment lives under #516 (new child issue) — do not fork fee SoT.

Cross-links

• Content/Guide/Help: epic [Epic · V1] Content, Guide, Help & Trust copy #516 · child [Content · #516] Guide + Help: Learning/Teaching accounts & Centres & Educators guide #541 (Learning/Teaching + Centres & Educators Guide/Help).
• Siblings: [Feature · V1] /guide — 7 photo slots + layout + Blue Tick #498 guide photos · [Feature · V1] Help Centre redesign (Airbnb-diet + article pack) #500 Help redesign · [Content · V1] FAQ accuracy pass + Tutor Playbook vs Guide/Help IA #515 FAQ — cross-link only; fee SoT unchanged (1.5 private, $0 parent match fee, Blue Tick optional, centre stepped ladder).
• Centres deep product: [Parked Epic – V3] Institutional Layer: Profiles, Events, Verification & Network #458 parked; B2B track [Track] B2B Partners & Cap.57 — Overflow / Referral / Storefront #465. Guide tab can still publish.

Do not open a second “Unified Base Account” epic. This section lives in #459 only.

2. Phase cut

Platform Track (parent → children)

Ideas stay in child epics — park, don’t drop.

3. Exact website placement

4. User journeys (step-by-step)

4.1 New signup ≤30s

1. Phone (WA OTP) or Email+password.
2. Screen: Display Name + Handle only (required).
3. Optional intent pills: Learner / Parent+first child / Independent Tutor (Cap.57 disclaimer) / Org create (reuse [Compliance / B2B] Extend centre partnership (#251/#256–258): 3-step EA-safe triage, BR org fields, COMPLIANCE_HOLD, How-it-works Partners tab #412/[Feature · #465] Broker overflow + org referral #442). Skip allowed.
4. Always create base User + default LearnerProfile. Set profiles.onboarding_completed_at.
5. Land /dashboard or role-appropriate home. Zero lock-in.

4.2 Become Independent Tutor later

1. Settings → Account Switcher → Become Independent Tutor.
2. Show Cap.57 copy (self-employed; not MatchMax employee/agent). Confirm.
3. INSERT tutor role / TutorProfile — do not DELETE learner role or hours.
4. If verifiedLearningHours ≥ 10 → show Verified MatchMax Scholar badge on TutorProfile.
5. activeViewRole switches to tutor workspace.

4.3 Parent multi-child

1. Parent activates PARENT_MANAGER (or pill at onboarding).
2. Add child sub-profiles (nickname, school, curriculum, gradYear, avatar). Soft warn at >5 children (no hard block).
3. Case request / booking / chat must select learnerProfileId.
4. Hours for sibling A never appear on sibling B.

4.4 Child claim

1. Parent sends claim → 7-day magic link to /account/claim.
2. Child account inherits 100% learner history (ownerUserId set).
3. Toggle: parent retains view-only and/or billing after claim (ParentLearnerLink).
4. Status: MANAGED_BY_PARENT → CLAIMED_INDEPENDENT (or LINKED_INDEPENDENT).

4.5 Lesson verify → dual-sided hours

1. Known MatchMax lesson completes ([V1 · #467] Scheduling + revenue/earnings engine (4 buckets) #461 logCompletedLesson / existing SoR confirm path).
2. Status → VERIFIED_COMPLETED (both confirm or 24h auto — see [V1 · #467] Scheduling + revenue/earnings engine (4 buckets) #461 machine; this issue owns ledger row shape).
3. Insert ledger rows: teaching (tutor/org) + learning (learnerProfile).
4. Public counters recompute from sum. Badges flip at 25 / 100 teaching hours.

4.6 Staff reconcile

1. Admin opens user/tutor → Hours reconcile panel.
2. Shows displayed Teaching/Learning vs SUM(durationHours) WHERE status=VERIFIED_COMPLETED.
3. Drift flagged red; no overwrite button — fix via void/dispute on session ([V1 · #467] Scheduling + revenue/earnings engine (4 buckets) #461), then recompute.

5. Logic & state machines

5.1 Roles (additive)

enabledRoles: RoleType[]   // LEARNER | PARENT_MANAGER | INDEPENDENT_TUTOR | ORGANIZATION
activeViewRole: RoleType

• Activate = append. Never delete sibling roles on switch.
• switch_role RPC must be rewritten to not DELETE FROM user_roles other roles.
• complete_onboarding must not force single role.

5.2 Ledger status

SCHEDULED
  → PENDING_CONFIRMATION
      → VERIFIED_COMPLETED   // credits hours both sides
      → DISPUTED             // no credit until resolved
      → CANCELLED            // no credit

Immutable credit rule: only VERIFIED_COMPLETED counts in public sums. No manual profile hour edits. Gamification (#468) never writes hours.

5.3 Sub-profile status

MANAGED_BY_PARENT · LINKED_INDEPENDENT · CLAIMED_INDEPENDENT

5.4 Timezone

Persist UTC; display Asia/Hong_Kong. 24h auto-verify clock = HKT end-of-lesson + 24h (owned jointly with #461).

5.5 Dual confirm / dispute (ledger fields; UX owned with #461)

• Tutor marks complete → PENDING_CONFIRMATION.
• Parent confirms → VERIFIED_COMPLETED.
• 24h auto-verify if unflagged.
• Dispute → DISPUTED + disputedBy + disputeReason; hours not credited.
• Undo window before verify locks: see [V1 · #467] Scheduling + revenue/earnings engine (4 buckets) #461 (voidSession / undo within N hours).

6. Data model (Prisma-ish → SQL migration)

enum RoleType {
  LEARNER
  PARENT_MANAGER
  INDEPENDENT_TUTOR
  ORGANIZATION
}

enum SubProfileStatus {
  MANAGED_BY_PARENT
  LINKED_INDEPENDENT
  CLAIMED_INDEPENDENT
}

enum LedgerSourceType {
  ONE_ON_ONE_LESSON
  GROUP_CENTRE_CLASS
  PLATFORM_EVENT_WORKSHOP
}

enum LedgerStatus {
  SCHEDULED
  PENDING_CONFIRMATION
  VERIFIED_COMPLETED
  DISPUTED
  CANCELLED
}

model LearnerProfile {
  id                      String   @id @default(uuid())
  ownerUserId             String?  // null while parent-managed unclaimed
  managedByUserId         String?  // parent
  nickname                String
  school                  String?
  curriculum              String?
  graduationYear          Int?
  avatarUrl               String?
  verifiedLearningHours   Decimal  @default(0) @db.Decimal(10,2) // CACHE only — must = ledger sum
  verifiedEventsAttended  Int      @default(0)
  createdAt               DateTime @default(now())
  updatedAt               DateTime @updatedAt
}

model ParentLearnerLink {
  id                 String @id @default(uuid())
  parentUserId       String
  learnerProfileId   String
  relationshipLabel  String? // e.g. "Mother"
  status             SubProfileStatus @default(MANAGED_BY_PARENT)
  canManageBilling   Boolean @default(true)
  canViewSchedule    Boolean @default(true)
  createdAt          DateTime @default(now())
}

model VerifiedHoursLedger {
  id               String @id @default(uuid())
  sourceType       LedgerSourceType
  status           LedgerStatus
  tutorProfileId   String?
  organizationId   String?
  learnerProfileId String
  subjectOrTopic   String?
  curriculumCode   String?
  durationHours    Decimal @db.Decimal(6,2)
  occurredAt       DateTime
  verifiedAt       DateTime?
  caseLessonId     String?  // FK case_lessons.id when bridged from SoR
  tuitionSessionId String?  // FK #461 ScheduledSession when present
  disputedBy       String?
  disputeReason    String?
  createdAt        DateTime @default(now())
  // append-only: updates only status/dispute fields; never delete row for audit
}

Also on profiles: preferredLocale, timezone (default Asia/Hong_Kong), handle, displayName, avatarUrl, bannerUrl stub, bio ≤300, DOB + showAgePublicly / showBirthdayPublicly, institutionName, curriculumOrMajor, graduationYear, enabledRoles, activeViewRole.

Cache rule: verifiedLearningHours / teaching hours on tutor profile are materialized caches refreshed by trigger/RPC from ledger — never user-typed.

7. API / server actions (names)

Bridge: until #461 TuitionCase ships, allow ledger insert from existing case_lessons when both confirms present or 24h auto job runs.

8. UI wireframes

8.1 Onboarding (Name + Handle)

┌─ Welcome to MatchMax ─────────────────────┐
│ Display name [________________]            │
│ Handle       [@_______________]            │
│                                            │
│ Optional: What brings you here?            │
│ ( ) Learner  ( ) Parent  ( ) Tutor  ( ) Org│
│          [Skip]            [Continue]      │
└────────────────────────────────────────────┘

8.2 Account Switcher

┌─ Account ──────────────────────────────────┐
│ Viewing as: Independent Tutor              │
│ Roles: Learner ✓  Parent ✓  Tutor ✓        │
│ [Become Independent Tutor] [Create Centre] │
│ Cap.57: You are self-employed; not an      │
│ employee/agent of MatchMax.                │
└────────────────────────────────────────────┘

8.3 Public hours + Phase 0 badges

┌─ Profile ──────────────────────────────────┐
│ Verified Teaching Hours  126               │
│ Verified Learning Hours   40               │
│ Badges: [Rising 25h] [Azure 100h]          │
└────────────────────────────────────────────┘

8.4 Child selector

┌─ Booking for: [Anson ▾] [+ Add child] ─────┐
│ Soft warn if >5 children                   │
└────────────────────────────────────────────┘

8.5 Admin reconcile

┌─ Reconcile hours — Tutor T-088 ────────────┐
│ Displayed teaching: 126.0                  │
│ Ledger SUM VERIFIED_COMPLETED: 126.0  ✓    │
│ Pending confirmation: 2.0 (not in public)  │
│ Disputed: 1.0                              │
│ [Export ledger CSV]  (no Edit hours btn)   │
└────────────────────────────────────────────┘

9. Empty / error / edge cases

10. Cap.57 / Fee / PDPO locks

• Matching = self-employed tutors only; MatchMax ≠ employment agency; vacancy board = [PARKED · Cap.57] Partner storefront SaaS + vacancy ad board — blocked pending solicitor/EAA; storefront-only slice under D1/D3 #413.
• Admin fee = tutor pays 1.5-lesson (L1 100% + L2 50%); short ≤5 → 20% package ([Ops / Fee · Track #464] Short-Case Handling (≤5 Lessons, 20% package) #443); pro-bono 0%. Parents/centres do not pay MatchMax matching fees.
• Live helper today: src/features/cases/sor.ts → FEE_MULTIPLIER / computeFeeAmountHkd (extend or extract to fee-breakdown.ts in [V1 · #467] Scheduling + revenue/earnings engine (4 buckets) #461 — do not invent parent-pays).
• Minors: parent-managed sub-profiles + claim gate; no public child PII by default.
• Known live label debt: tutor-home.tsx comment still says admin fee is “PARENT'S fee” — correct to tutor-pays when touching that surface ([V1 · #467] Scheduling + revenue/earnings engine (4 buckets) #461/[Legal · Track #464] ToS §15 admin-fee payer = tutor (align CEO #412/#443) #444).

11. Acceptance criteria

• Signup ≤30s (Name + Handle + Phone/Email); pills optional; zero lock-in.
• Activating INDEPENDENT_TUTOR preserves 100% verifiedLearningHours; Cap.57 disclaimer shown.
• complete_onboarding / switch_role no longer delete sibling roles (migration + UI).
• Parent creates ≥3 child sub-profiles; separate bookings → separate hours per learnerProfileId.
• Claim transfers ownership without duplicate/loss; parent view/billing toggle works.
• Completing a known lesson credits both sides via ledger; public counters = ledger sum.
• Users cannot manually type/edit verified hour numbers (no API/UI path).
• Supabase RLS: parent sees only linked learners; tutors cannot read unrelated learner PII.
• Cross-epic: any public “verified hours” reconciles to this ledger (or labelled unverified).
• Ledger reconciliation: staff read-only view/RPC; mismatch fails QA; no manual overwrite.
• Phase 0 badges: Teaching ≥25 Rising · ≥100 Azure on account preview and [V1 Phased · under #467] Trophy Wall Roster — KPI Banner → Full 3-Tier Privacy Wall #462 Trophy KPI strip; pure recompute; gamification never writes hours.
• Bilingual readiness: new strings EN + 繁中 keys added (parity with site i18n).
• Soft warn >5 children; no hard block.

12. Out of scope

• Achievement grid, banners polish, CV timeline → [Parked Epic · under #459] Academic LinkedIn × Instagram Profile Overhaul #460
• Scheduling calendar, revenue KPI UI → [V1 · #467] Scheduling + revenue/earnings engine (4 buckets) #461
• Public student roster / Trophy Wall / anti-poach → [V1 Phased · under #467] Trophy Wall Roster — KPI Banner → Full 3-Tier Privacy Wall #462
• Heavy org admin / events network → [Parked Epic – V3] Institutional Layer: Profiles, Events, Verification & Network #458
• Matching outcome ledger → [Feature · Matching] Outcome ledger + two-way matching engine #428 (separate)
• Desk track / “New” tutor soft-prefer → [Ops · #463] Manual tutor level + first-match guide #445/[Ops · #463] Internal desk track + matching backboard #448
• Community posts / follow → [PARKED] Epic: community public posts — do not build in V1 #218/[PARKED] Who-to-follow onboarding — do not build #331
• Full streaks/vault/rebate → [Parked · Full Epic] Gamified Retention Engine — Streaks · MaxPoints · Trophy Ladder · Rewards Vault · Fee Rebate #468 (reads ledger only)

Phase 0 gamification fold-in (display only — keep)

Ownership lock: #459 owns ledger-sum truth + account/profile preview badges; #462 Phase A owns Rising/Azure on public Trophy KPI. Both ACs must pass.

13. Dependencies

• Existing auth / profiles / user_roles.
• Parallel: desk [Feature] V1 WhatsApp Auto-Greeting & Manual P1–P4 Triage UI #438/[Ops] Failed-trial pivot + soft tutor inactive #449/[Ops] Matching speed & reliability pack #451/[Ops] Matching cockpit + website conversion polish #454 · forms [Urgent · V1] Case/Tutor Form Progressive Disclosure + Parent Match-Status Card + Admin Filters #466.
• Fee: [Ops / Fee · Track #464] Short-Case Handling (≤5 Lessons, 20% package) #443 · [Legal · Track #464] ToS §15 admin-fee payer = tutor (align CEO #412/#443) #444 · sor.ts.
• Orgs: [Compliance / B2B] Extend centre partnership (#251/#256–258): 3-step EA-safe triage, BR org fields, COMPLIANCE_HOLD, How-it-works Partners tab #412 · [Feature · #465] Broker overflow + org referral #442 · [Track] B2B Partners & Cap.57 — Overflow / Referral / Storefront #465.
• Session writer for volume: [V1 · #467] Scheduling + revenue/earnings engine (4 buckets) #461 Phase A.
• Consumers: [V1 Phased · under #467] Trophy Wall Roster — KPI Banner → Full 3-Tier Privacy Wall #462 · [Epic · V1] Revenue Calendar × Trophy Wall #467 · [Parked · Full Epic] Gamified Retention Engine — Streaks · MaxPoints · Trophy Ladder · Rewards Vault · Fee Rebate #468 (On Hold).

14. QA fixtures / example numbers

HK$ note: hours ledger is time-only; fee math stays in #461 / sor.ts — do not store dollars on ledger rows.

15. Zero-guesswork implementation ticket (CEO quality pass 2026-10-25)

Turns this issue from a product spec into an implementable ticket. Does not replace §§1–14 — it locks the missing schema, migration, claim, first ledger write, RLS matrix, i18n, empty states, and build order.

15.0 Live code baseline (do not invent parallel paths)

15.1 Exact schema (SQL shape vs current)

15.1.1 profiles additive columns

ALTER TABLE public.profiles
  ADD COLUMN IF NOT EXISTS handle text UNIQUE,              -- public @handle; CI unique index
  ADD COLUMN IF NOT EXISTS banner_url text,
  ADD COLUMN IF NOT EXISTS bio text CHECK (char_length(bio) <= 300),
  ADD COLUMN IF NOT EXISTS date_of_birth date,
  ADD COLUMN IF NOT EXISTS show_age_publicly boolean NOT NULL DEFAULT false,
  ADD COLUMN IF NOT EXISTS show_birthday_publicly boolean NOT NULL DEFAULT false,
  ADD COLUMN IF NOT EXISTS institution_name text,
  ADD COLUMN IF NOT EXISTS curriculum_or_major text,
  ADD COLUMN IF NOT EXISTS graduation_year int,
  ADD COLUMN IF NOT EXISTS timezone text NOT NULL DEFAULT 'Asia/Hong_Kong',
  ADD COLUMN IF NOT EXISTS active_view_role text
    CHECK (active_view_role IS NULL OR active_view_role IN ('parent','tutor','learner')),
  ADD COLUMN IF NOT EXISTS verified_teaching_hours numeric(10,2) NOT NULL DEFAULT 0, -- CACHE
  ADD COLUMN IF NOT EXISTS rising_badge_at timestamptz,     -- set when teaching cache crosses 25
  ADD COLUMN IF NOT EXISTS azure_badge_at timestamptz;      -- set when teaching cache crosses 100

CREATE UNIQUE INDEX IF NOT EXISTS uq_profiles_handle_ci
  ON public.profiles (lower(handle)) WHERE handle IS NOT NULL;

enabledRoles is derived from user_roles + claimed learner_profiles.owner_user_id — do not store a duplicate array unless needed for cache; if cached, refresh via trigger on user_roles / claim.

15.1.2 New tables

CREATE TABLE public.learner_profiles (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  owner_user_id uuid REFERENCES auth.users(id) ON DELETE SET NULL, -- null while parent-managed
  managed_by_user_id uuid REFERENCES auth.users(id) ON DELETE SET NULL,
  nickname text NOT NULL,
  school text,
  curriculum text,
  graduation_year int,
  avatar_url text,
  verified_learning_hours numeric(10,2) NOT NULL DEFAULT 0, -- CACHE = SUM ledger VERIFIED
  verified_events_attended int NOT NULL DEFAULT 0,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE public.parent_learner_links (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  parent_user_id uuid NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  learner_profile_id uuid NOT NULL REFERENCES public.learner_profiles(id) ON DELETE CASCADE,
  relationship_label text, -- e.g. Mother
  status text NOT NULL DEFAULT 'MANAGED_BY_PARENT'
    CHECK (status IN ('MANAGED_BY_PARENT','LINKED_INDEPENDENT','CLAIMED_INDEPENDENT')),
  can_manage_billing boolean NOT NULL DEFAULT true,
  can_view_schedule boolean NOT NULL DEFAULT true,
  created_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE (parent_user_id, learner_profile_id)
);

CREATE TABLE public.verified_hours_ledger (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  source_type text NOT NULL CHECK (source_type IN (
    'ONE_ON_ONE_LESSON','GROUP_CENTRE_CLASS','PLATFORM_EVENT_WORKSHOP')),
  status text NOT NULL CHECK (status IN (
    'SCHEDULED','PENDING_CONFIRMATION','VERIFIED_COMPLETED','DISPUTED','CANCELLED')),
  tutor_profile_id uuid,          -- tutors.id when teaching side
  organization_id uuid,
  learner_profile_id uuid NOT NULL REFERENCES public.learner_profiles(id),
  subject_or_topic text,
  curriculum_code text,
  duration_hours numeric(6,2) NOT NULL CHECK (duration_hours > 0),
  occurred_at timestamptz NOT NULL,
  verified_at timestamptz,
  case_lesson_id uuid UNIQUE REFERENCES public.case_lessons(id) ON DELETE SET NULL,
  tuition_session_id uuid UNIQUE, -- FK when #461 ScheduledSession exists
  disputed_by uuid,
  dispute_reason text,
  created_at timestamptz NOT NULL DEFAULT now()
  -- append-only: UPDATE status/dispute/verified_at only; NEVER DELETE fee-touched rows
);
CREATE INDEX idx_vhl_tutor_verified ON public.verified_hours_ledger (tutor_profile_id)
  WHERE status = 'VERIFIED_COMPLETED';
CREATE INDEX idx_vhl_learner_verified ON public.verified_hours_ledger (learner_profile_id)
  WHERE status = 'VERIFIED_COMPLETED';

CREATE TABLE public.learner_claim_tokens (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  learner_profile_id uuid NOT NULL REFERENCES public.learner_profiles(id) ON DELETE CASCADE,
  parent_user_id uuid NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  token_hash text NOT NULL UNIQUE,          -- store SHA-256 of raw token only
  delivery_channel text NOT NULL CHECK (delivery_channel IN ('email','whatsapp')),
  delivery_target text NOT NULL,            -- email or E.164 phone snapshot at send
  expires_at timestamptz NOT NULL,          -- created_at + 7 days
  used_at timestamptz,
  used_by_user_id uuid REFERENCES auth.users(id),
  revoked_at timestamptz,
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX idx_claim_tokens_active ON public.learner_claim_tokens (learner_profile_id)
  WHERE used_at IS NULL AND revoked_at IS NULL;

15.1.3 RPC replacements (signatures)

-- OLD: complete_onboarding(_choice text)  -- destructive
-- NEW:
CREATE OR REPLACE FUNCTION public.complete_onboarding_v2(
  _display_name text,
  _handle text,
  _initial_roles text[]  -- subset of {'parent','tutor'}; default {'parent'}
) RETURNS void;

-- Behaviour:
-- 1) Validate handle (3–24 [a-z0-9_], unique CI); set profiles.display_name, handle, onboarding_completed_at=now()
-- 2) FOR EACH role in _initial_roles: INSERT user_roles ON CONFLICT DO NOTHING
-- 3) NEVER DELETE any user_roles row
-- 4) Set active_view_role = first of (_initial_roles) preferring tutor if both
-- 5) If 'parent' in roles and no learner_profiles yet: create default child nickname from display_name + link MANAGED_BY_PARENT (optional thin default — or wait until first case; prefer create stub "Child 1" only when parent explicitly adds — see journey 4.3)

-- OLD: switch_role(_role app_role) -- destructive
-- NEW:
CREATE OR REPLACE FUNCTION public.set_active_view_role(_role text) RETURNS void;
-- Ensures user_roles contains _role for parent|tutor (INSERT ON CONFLICT DO NOTHING);
-- sets profiles.active_view_role; NEVER deletes roles.
-- For 'learner': allowed only if EXISTS learner_profiles.owner_user_id = auth.uid().

-- Keep old RPCs as wrappers that RAISE 'DEPRECATED_USE_V2' after cutover, or rewrite in place with additive semantics + comment. Prefer **rewrite in place** so OnboardingGate / account-section call sites stay, but behaviour becomes additive (document in migration COMMENT).

15.2 One-time data migration (existing single-role users) — numbered, idempotent

Run once in the same migration (or follow-up) after columns/tables exist. Safe to re-run.

1. Backfill timezone / active_view_role
   UPDATE profiles SET timezone = COALESCE(timezone,'Asia/Hong_Kong') WHERE timezone IS NULL OR timezone = '';
   For each user: if user_roles has tutor and not parent → active_view_role='tutor'; else if has parent → 'parent'; else leave null.

2. Do not invent handles for existing users — leave handle NULL until settings; public URL keeps using tutor code where applicable.

3. Parents without learner_profiles: for each user_id with role parent, if zero parent_learner_links, create one learner_profiles row:
   nickname = COALESCE(display_name, 'Child') || ' · home', managed_by_user_id = user_id, owner_user_id = NULL, plus parent_learner_links status MANAGED_BY_PARENT.
   Idempotent guard: skip if any link already exists for that parent.

4. Tutors: ensure tutors.user_id row exists (already required for desk). Set profiles.verified_teaching_hours = 0 until ledger backfill (§15.4 secondary).

5. Rewrite RPCs complete_onboarding / switch_role to additive semantics (see §15.1.3). Deploy UI that no longer assumes exclusive role.

6. Verification query (QA):
   SELECT user_id, count(*) FROM user_roles WHERE role IN ('parent','tutor') GROUP BY 1 HAVING count(*) > 1; may be >0 after users add second role — that is success, not a bug.
   SELECT count(*) FROM user_roles ur JOIN profiles p ON p.id=ur.user_id WHERE p.onboarding_completed_at IS NOT NULL; unchanged count pre/post.

7. Rollback note: additive columns/tables only; RPC rewrite can be reverted by redeploying prior migration SQL from git history. No destructive data wipe.

15.3 Child claim flow — full spec (7-day magic link)

Happy path

1. Parent on /dashboard child selector → Invite child to claim → modal: choose delivery email | whatsapp, enter target, confirm which learner_profile_id (required when parent has multiple children — dropdown of nicknames; no silent default to wrong child).
2. Server createLearnerClaimToken({ learnerProfileId, channel, target }):
   • AuthZ: caller must own parent_learner_links for that profile and status ∈ {MANAGED_BY_PARENT, LINKED_INDEPENDENT}.
   • Revoke any prior unused tokens for same learner_profile_id (revoked_at=now()).
   • Generate raw = urlsafe 32 bytes; store token_hash = sha256(raw); expires_at = now() + interval '7 days'.
   • Return link https://<host>/account/claim?token=<raw> (raw only in message, never logged).
3. Delivery:
   • email → existing transactional mail path (same provider as auth mail); template keys in §15.7.
   • whatsapp → MatchMax WA desk / automation send of the link text (ops channel); if WA send fails, surface error and keep token valid so parent can resend.
4. Child opens link while signed in (or signs up / signs in first). Page /account/claim:
   • Validate token: hash match, used_at IS NULL, revoked_at IS NULL, expires_at > now().
   • Preview nickname + hours that will transfer.
   • Confirm → claimLearnerProfile({ token }): set learner_profiles.owner_user_id = auth.uid(), link status → CLAIMED_INDEPENDENT, used_at=now(), used_by_user_id=auth.uid().
   • Parent retain toggles: default can_view_schedule=true, can_manage_billing=true until parent changes in settings.
5. Child gains active_view_role option learner; learning hours cache already on profile (no rebuild).

Multiple children

• Token is always bound to exactly one learner_profile_id.
• Parent UI must pick the child before send.
• Claiming child A never touches child B.

Edge behaviours (lock)

15.4 First ledger-write trigger before full #461 calendar

Problem: #462 / #468 / public hours need rows before Phase B calendar exists.

PRIMARY (ship with #459) — bridge live case_lessons

Action name: creditVerifiedHoursFromCaseLesson(lessonId) (server fn in e.g. src/features/accounts/ledger.functions.ts).

Trigger points (call the same fn):

1. After markLessonDoneTutor succeeds (tutor.functions.ts) when new status is done.
2. After lessonAdminMark with status: 'done' (admin-desk.functions.ts).
3. (Later) [V1 · #467] Scheduling + revenue/earnings engine (4 buckets) #461 verifySession / auto-verify — same fn or twin that also sets tuition_session_id.

Algorithm:

if case_lessons.status != 'done': return
if exists verified_hours_ledger where case_lesson_id = lesson.id: return  -- idempotent
resolve learner_profile_id from tutoring_cases (parent → default/selected child link; must not be null)
duration_hours = round(duration_min/60.0, 2)
INSERT ledger:
  source_type = ONE_ON_ONE_LESSON
  status = VERIFIED_COMPLETED
  tutor_profile_id = case_lessons.tutor_id
  learner_profile_id = …
  duration_hours, occurred_at = coalesce(done_at, starts_at)
  verified_at = now(), case_lesson_id = lesson.id
REFRESH caches: profiles.verified_teaching_hours for tutor user; learner_profiles.verified_learning_hours
IF teaching cache crosses 25 → set rising_badge_at = coalesce(rising_badge_at, now())
IF crosses 100 → set azure_badge_at = coalesce(azure_badge_at, now())

Staff path (same primary): desk already has “mark done” — that is enough; no separate “staff seed hours” UI required for V1.

SECONDARY (optional, same PR or follow-up migration) — backfill

-- Pseudocode job: for each case_lessons where status='done' and no ledger row, call credit fn

Idempotent via UNIQUE(case_lesson_id).

Explicit non-trigger

• confirmLessonTutor / confirmLessonParent alone (planned→confirmed) do not write ledger.
• cancelled / no_show / late_cancel never credit hours.

15.5 Implementation checklist (order)

• Migration: columns on profiles + tables learner_profiles, parent_learner_links, verified_hours_ledger, learner_claim_tokens + indexes
• Migration data: §15.2 steps 1–4 idempotent backfill
• RPCs: rewrite complete_onboarding + switch_role / add set_active_view_role + createLearnerClaimToken + claimLearnerProfile + recomputeHoursCaches(userId|learnerId) + adminReconcileHours
• Backend bridge: creditVerifiedHoursFromCaseLesson hooked from markLessonDoneTutor + lessonAdminMark
• UI: OnboardingGate Name+Handle+roles; Account switcher non-destructive; child selector; /account/claim; public Rising/Azure display on tutor card; admin reconcile view
• QA fixtures: §14 table + RLS matrix §15.6 + claim expired/used + double mark-done idempotent

15.6 Empty states + RLS test matrix

Empty / error (expand §9)

RLS / authz test matrix (actor × resource × allow)

SQL sketch: service-role server fns remain primary (current SoR pattern: RLS-deny tables + asAdminDb + authz in handler). If enabling direct client read later, policies must encode the matrix above. QA must include automated tests: tutor A cannot credit/read B; parent sees only own links.

15.7 i18n keys (EN + 繁中 placeholders)

Add under src/features/i18n/locales/{en,zh-HK}.json (names locked):

account.onboarding.title
account.onboarding.name_label
account.onboarding.handle_label
account.onboarding.handle_taken
account.onboarding.role_parent
account.onboarding.role_tutor
account.onboarding.submit
account.switcher.label
account.switcher.parent
account.switcher.tutor
account.switcher.learner
account.children.add
account.children.empty
account.children.invite_claim
account.children.claim_channel_email
account.children.claim_channel_whatsapp
account.claim.title
account.claim.confirm
account.claim.expired
account.claim.used
account.claim.reject_parent_self
account.hours.empty
account.badge.rising          // "Rising · 25h+"
account.badge.azure          // "Azure · 100h+"
admin.reconcile.title
admin.reconcile.drift
admin.reconcile.fix

繁中 values may be draft in PR; keys must exist in both locale files before merge.

15.8 Phase 0 badge ownership reminder (AC)

• When verified_teaching_hours cache crosses 25.0, show Rising on public tutor card + account; set rising_badge_at once.
• When crosses 100.0, show Azure; set azure_badge_at once.
• Public hours number always equals SUM(duration_hours) WHERE status=VERIFIED_COMPLETED for that tutor — staff reconcile view proves it.
• No MaxPoints / vault / streak work in this issue ([Parked · Full Epic] Gamified Retention Engine — Streaks · MaxPoints · Trophy Ladder · Rewards Vault · Fee Rebate #468 On Hold).

Consolidated sub-issues (eng map)

Parked under this track (not V1): #460 profile overhaul.

Consolidated for eng — sub-issues listed.