👨‍👩‍👧 Parent Account Creation & Linking Contract

Canonical Principle

Parent account creation is:

• Token-initiated — parent arrives via a secure token link, never a public signup page
• Email-verified — magic link sent to parent email before any child data is shown
• Backend-linked to existing child — the child record already exists; the parent flow only creates the link
• Read-only by default — parent has no write access to child, class, or school data

Flow Overview

Step 0 — Teacher Issues a Token

In the Teacher Portal, under a student's profile, the teacher sees a Parent Access toggle (Off by default). When toggled On, the portal calls the User Center to generate a token. The token URL is displayed as a QR code and a short link the teacher can share with the parent (printed, emailed, or sent via school system).

{
  "issued_by_user_id": "teacher-uuid",
  "expires_in_hours": 72          // default 72h, max 168h (7d)
}

{
  "token": "plt_a3f8...z9",       // full token — shown once, not stored in DB
  "link_url": "https://parents.readingtester.com/link?token=plt_a3f8...z9",
  "expires_at": "2026-04-22T02:00:00Z"
}

INSERT INTO parent_link_tokens (child_id, issued_by_user_id, token_hash, status, expires_at)
VALUES (child_uuid_lookup, teacher_uuid, sha256(token), 'active', expires_at)

{ action: "parent_link_token_created", actor_id: teacher_uuid, child_id: child_uuid }

Step 1 — Parent Opens Link Page

{
  "valid": true,
  "school_name": "Greenwood Primary",   // for branding only
  "school_logo_url": "https://..."      // optional, may be null
}

{
  "valid": false,
  "reason": "expired" | "not_found" | "already_used" | "revoked"
}

token_hash = sha256(link_token)
row = SELECT * FROM parent_link_tokens WHERE token_hash = token_hash

if (!row)                  → { valid: false, reason: "not_found" }
if (row.status != 'active') → { valid: false, reason: row.status }
if (row.expires_at < NOW()) → { valid: false, reason: "expired" }
// do NOT return child_id, child name, or any learner data here
→ { valid: true, school_name: ... }

Step 2 — Parent Submits Email

{
  "link_token": "plt_a3f8...z9",
  "email": "parent@example.com"
}

1. token_hash = sha256(link_token)
2. Re-validate token (same checks as Step 1 — token can expire between steps)
   → if invalid: 400 { error: "LINK_INVALID", reason: "..." }

3. email = normalize(input.email).toLowerCase().trim()

4. user = SELECT * FROM users WHERE email = email AND role = 'parent'
   if (!user):
     INSERT INTO users (uuid, email, name='', role='parent', email_verified_at=NULL, ...)
     user = new row

5. verification_token = crypto.randomBytes(32).hex()
   INSERT INTO email_verifications (
     user_id       = user.id,
     token_hash    = sha256(verification_token),
     link_token_hash = token_hash,       // ← store association
     expires_at    = NOW() + 30 minutes,
     consumed_at   = NULL
   )

6. Send PARENT_LINK_VERIFY email to parent:
   Subject: "Confirm your email to connect to Pickatale"
   Body: magic link → https://parents.readingtester.com/verify?vt={verification_token}
   email_log INSERT

7. audit_log INSERT: { action: "parent_link_started", parent_user_id: user.id,
                        link_token_hash: token_hash }

{
  "message": "Check your email for a confirmation link."
}

400 { "error": "LINK_INVALID", "reason": "expired" | "not_found" | "already_used" | "revoked" }
400 { "error": "INVALID_EMAIL" }
429 { "error": "RATE_LIMITED", "retryAfter": 900 }

Step 3 — Parent Clicks Email Link

1. vt_hash = sha256(verification_token)
2. ev = SELECT * FROM email_verifications WHERE token_hash = vt_hash
   if (!ev)               → 400 { error: "INVALID_LINK" }
   if (ev.consumed_at)    → 400 { error: "LINK_ALREADY_USED" }
   if (ev.expires_at < NOW()) → 400 { error: "LINK_EXPIRED" }

3. plt = SELECT * FROM parent_link_tokens WHERE token_hash = ev.link_token_hash
   if (!plt)                     → 400 { error: "LINK_INVALID", reason: "not_found" }
   if (plt.status != 'active')   → 400 { error: "LINK_INVALID", reason: plt.status }
   if (plt.expires_at < NOW())   → 400 { error: "LINK_INVALID", reason: "expired" }

// BEGIN TRANSACTION
4. UPDATE users SET email_verified_at = NOW() WHERE id = ev.user_id
5. UPDATE email_verifications SET consumed_at = NOW() WHERE id = ev.id
6. UPDATE parent_link_tokens SET status = 'used', used_at = NOW() WHERE id = plt.id

7. INSERT INTO parent_child_links (parent_user_id, child_id, access_level, status)
   VALUES (ev.user_id, plt.child_id, 'read_only', 'active')
   ON DUPLICATE KEY UPDATE status = 'active'   // idempotent if re-linked

8. INSERT INTO sessions (user_id, token, expires_at)
   session_token = crypto.randomBytes(32).hex()
   expires_at = NOW() + 30 days
// COMMIT TRANSACTION

9. audit_log INSERT: { action: "parent_link_completed",
                        parent_user_id: ev.user_id, child_id: plt.child_id }

10. Set-Cookie: parent_session={session_token}; HttpOnly; Secure; SameSite=Lax; Path=/
    Redirect: 302 → /parent/onboarding   // first login always goes to onboarding
    // server checks: parent_notification_preferences row exists? → /parent/home instead

400 { "error": "INVALID_LINK" }           // vt not found in DB
400 { "error": "LINK_ALREADY_USED" }      // email_verifications.consumed_at set
400 { "error": "LINK_EXPIRED" }           // email_verifications.expires_at passed
400 { "error": "LINK_INVALID", "reason": "revoked" | "expired" | ... }  // parent_link_token bad

Step 4 — Onboarding Preferences

Route guard

On every authenticated request to the Parent Portal, the server checks:

prefs = SELECT * FROM parent_notification_preferences WHERE parent_user_id = session.user_id
if (!prefs AND currentPath != '/parent/onboarding'):
  redirect → /parent/onboarding    // enforce the one-time screen
if (prefs AND currentPath == '/parent/onboarding'):
  redirect → /parent/home          // already done, skip

UI

Headline: "How would you like to receive updates about your child's reading?"

Single button: Save & Continue

{
  "weekly_summary_enabled":    boolean,   // required
  "alerts_enabled":            boolean,   // required
  "recommendations_enabled":   boolean    // required
}

INSERT INTO parent_notification_preferences
  (parent_user_id, weekly_summary_enabled, alerts_enabled, recommendations_enabled, updated_at)
VALUES
  (session.user_id, body.weekly_summary_enabled, body.alerts_enabled, body.recommendations_enabled, NOW())
ON DUPLICATE KEY UPDATE
  weekly_summary_enabled  = VALUES(weekly_summary_enabled),
  alerts_enabled          = VALUES(alerts_enabled),
  recommendations_enabled = VALUES(recommendations_enabled),
  updated_at              = NOW()

audit_log INSERT: { action: "parent_preferences_set", parent_user_id: session.user_id }

{ "saved": true }

redirect → /parent/home

{
  "weekly_summary_enabled":    boolean,
  "alerts_enabled":            boolean,
  "recommendations_enabled":   boolean,
  "updated_at":                "ISO-8601"
}

{ "error": "NOT_SET" }   // preferences not yet saved — should not happen after onboarding

Step 5 — Parent Home View

Authenticated via parent_session cookie. Parent can have multiple linked children (one parent_child_links row per child). All data fetched from Reader API and Learner Bot via internal calls using child's learner_id.

What the parent sees

• Last reading activity (last book, last active date)
• Progress summary (books read this week, miles earned, reading streak)
• Latest parent digest (from Learner Bot nightly report)
• Talking points: "Things to ask your child about" (generated by bot)
• Email preference settings (Weekly digest / Nightly digest / Off)

What the parent does NOT see

• Child's username or PIN — never
• FK reading level number (show age-appropriate language: "Reading well for their age")
• Class name, school name, teacher name
• Other children in the class
• Assessment scores or technical reading data

Access permissions — enforced server-side

Required Data Model

The following tables must be created or modified. All tables live in the user_center database.

CREATE TABLE parent_link_tokens (
  id              INT PRIMARY KEY AUTO_INCREMENT,
  child_id        VARCHAR(36) NOT NULL,          -- learner UUID (from users.uuid)
  issued_by_user_id VARCHAR(36) NOT NULL,         -- teacher UUID
  token_hash      VARCHAR(64) NOT NULL UNIQUE,   -- sha256(raw_token), hex
  status          ENUM('active','used','expired','revoked') NOT NULL DEFAULT 'active',
  expires_at      DATETIME NOT NULL,
  created_at      DATETIME NOT NULL DEFAULT NOW(),
  used_at         DATETIME,
  revoked_by      VARCHAR(36),                   -- user UUID of revoker if revoked
  revoked_at      DATETIME,
  INDEX idx_child (child_id),
  INDEX idx_status_expires (status, expires_at)
);

CREATE TABLE email_verifications (
  id              INT PRIMARY KEY AUTO_INCREMENT,
  user_id         INT NOT NULL,                  -- FK → users.id
  token_hash      VARCHAR(64) NOT NULL UNIQUE,   -- sha256(raw_vt), hex
  link_token_hash VARCHAR(64) NOT NULL,          -- FK → parent_link_tokens.token_hash
  expires_at      DATETIME NOT NULL,             -- NOW() + 30 minutes
  consumed_at     DATETIME,                      -- set when used; NULL = not used
  created_at      DATETIME NOT NULL DEFAULT NOW(),
  INDEX idx_user (user_id)
);

CREATE TABLE parent_child_links (
  id              INT PRIMARY KEY AUTO_INCREMENT,
  parent_user_id  INT NOT NULL,                  -- FK → users.id (role='parent')
  child_id        VARCHAR(36) NOT NULL,          -- learner UUID
  access_level    ENUM('read_only') NOT NULL DEFAULT 'read_only',  -- v1 only
  status          ENUM('active','revoked') NOT NULL DEFAULT 'active',
  created_at      DATETIME NOT NULL DEFAULT NOW(),
  revoked_at      DATETIME,
  revoked_by      VARCHAR(36),
  UNIQUE KEY uq_parent_child (parent_user_id, child_id)
);

CREATE TABLE parent_notification_preferences (
  id                       INT PRIMARY KEY AUTO_INCREMENT,
  parent_user_id           INT NOT NULL UNIQUE,   -- FK → users.id (role='parent')
  weekly_summary_enabled   TINYINT(1) NOT NULL DEFAULT 1,
  alerts_enabled           TINYINT(1) NOT NULL DEFAULT 1,
  recommendations_enabled  TINYINT(1) NOT NULL DEFAULT 0,
  updated_at               DATETIME NOT NULL DEFAULT NOW() ON UPDATE NOW()
);

ALTER TABLE users ADD COLUMN email_verified_at DATETIME DEFAULT NULL
  AFTER email;

-- Also ensure role enum includes 'parent':
-- ENUM('teacher', 'parent', 'student', 'admin')  ← already present, confirm

Audit Log Events

All events written to audit_log table. The immutability trigger applies (no DELETEs).

Full API Contracts

{ "valid": true, "school_name": string | null, "school_logo_url": string | null }

{ "valid": false, "reason": "expired" | "not_found" | "already_used" | "revoked" }

{
  "link_token": string,   // required
  "email":      string    // required, must be valid email format
}

{ "message": "Check your email for a confirmation link." }

400 { "error": "LINK_INVALID",  "reason": "expired" | "not_found" | "already_used" | "revoked" }
400 { "error": "INVALID_EMAIL" }
429 { "error": "RATE_LIMITED", "retryAfter": 900 }

302 Redirect → /parent/home
Set-Cookie: parent_session={session_token}; HttpOnly; Secure; SameSite=Lax; Path=/; Max-Age=2592000

400 { "error": "INVALID_LINK" }
400 { "error": "LINK_ALREADY_USED" }
400 { "error": "LINK_EXPIRED" }
400 { "error": "LINK_INVALID", "reason": "revoked" | "expired" | "not_found" }

{
  "child_id":        "learner-uuid",
  "parent_user_id":  123,            // DB id of the parent user
  "reason":          string          // free text, stored in audit_log
}

{
  "link_token_hash": "sha256hex...",  // token hash, from Teacher Portal state
  "reason":          string
}

{ "revoked": true }

403 { "error": "FORBIDDEN" }         // requester is not the issuing teacher or admin
404 { "error": "NOT_FOUND" }          // link or token not found
409 { "error": "ALREADY_REVOKED" }    // idempotent — no error, just 200

{
  "issued_by_user_id": "teacher-uuid",
  "expires_in_hours":  72              // integer, 1–168
}

{
  "token":      "plt_...",
  "link_url":   "https://parents.readingtester.com/link?token=plt_...",
  "expires_at": "ISO-8601"
}

{ "error": "CHILD_NOT_FOUND" }

Teacher Controls

In the Teacher Portal, under a student's profile:

Migration: Replacing Existing Code

The following existing code must be removed or replaced as part of implementing this contract:

Security Rules — Hardened v1.1

GAP 1: Email Enumeration — Fully Closed

"Always 200" is not sufficient if response time differs. The server must spend the same amount of time whether the email exists or not.

// /api/parent-link/start — constant-time enforcement
async function start(link_token, email) {
  await validateToken(link_token);               // always runs
  const normalized = normalizeEmail(email);
  const existing = await db.findParentByEmail(normalized);  // always runs

  // Whether account exists or not, always:
  const user = existing ?? await db.createParentAccount(normalized);
  await db.insertEmailVerification(user.id, link_token);
  await sendVerificationEmail(normalized);       // always sends (idempotent — see GAP 3)
  await sleep(crypto_random_ms(20, 60));         // constant-time padding: 20–60ms random jitter
  return 200 { message: "Check your email for a confirmation link." };
  // Response body IDENTICAL in all cases. No branch in response.
}

GAP 2: Token Reuse Race Condition — DB Constraint + SELECT FOR UPDATE

Step 3 (verify) must use SELECT FOR UPDATE inside the transaction to prevent two simultaneous requests both reading status='active' before either updates it.

// Step 3 verify — exact transaction with row lock
BEGIN TRANSACTION;

  -- Row-level lock: only one concurrent request can proceed
  SELECT id, status, expires_at, child_id FROM parent_link_tokens
  WHERE token_hash = ? FOR UPDATE;

  -- Guard checks inside the lock
  if (status != 'active') → ROLLBACK → 400 LINK_INVALID
  if (expires_at < NOW()) → ROLLBACK → 400 LINK_EXPIRED

  UPDATE parent_link_tokens SET status='used', used_at=NOW() WHERE id=?;
  UPDATE email_verifications SET consumed_at=NOW() WHERE id=?;
  UPDATE users SET email_verified_at=NOW() WHERE id=?;

  -- Unique constraint on (parent_user_id, child_id) handles duplicate-link attempts
  INSERT INTO parent_child_links (parent_user_id, child_id, access_level, status)
  VALUES (?, ?, 'read_only', 'active')
  ON DUPLICATE KEY UPDATE status='active';

  INSERT INTO sessions ...;

COMMIT;

-- DB schema enforcement:
ALTER TABLE parent_link_tokens ADD UNIQUE KEY uq_token_hash (token_hash);
-- The UNIQUE on token_hash means even if two requests pass the application check,
-- only one UPDATE can succeed — the other will see status='used' on retry.

GAP 3: Idempotency — /start and /verify

/api/parent-link/start is idempotent: calling it twice with the same link_token + email before the first magic link expires sends a second email and overwrites the email_verifications row (upsert by user_id + link_token_hash). The parent gets a fresh link; the old link is invalidated.

-- /start idempotency: upsert, not insert
INSERT INTO email_verifications (user_id, token_hash, link_token_hash, expires_at)
VALUES (?, sha256(new_vt), ?, NOW() + INTERVAL 30 MINUTE)
ON DUPLICATE KEY UPDATE
  token_hash = VALUES(token_hash),
  expires_at = VALUES(expires_at),
  consumed_at = NULL;   -- reset if previously consumed (re-link after unlink scenario)

/api/parent-link/verify is idempotent: if called twice (e.g. browser refresh), the SELECT FOR UPDATE + consumed_at check catches it. Second call returns 400 LINK_ALREADY_USED with UI: "You're already connected. Log in to view your child's progress."

GAP 4: Exact Rate Limit Thresholds

All limits use in-process sliding window counter (Redis optional — acceptable at current scale). Counters keyed by the values above, stored with TTL = window duration.

GAP 5: Session Invalidation on Revoke

When a teacher revokes a parent_child_links row, the parent's existing session is NOT immediately terminated — but every data request checks link status:

// Middleware on all /parent/* authenticated routes:
async function requireActiveParentLink(req, res, next) {
  const session = verifySessionCookie(req);
  if (!session) return res.redirect('/parent/login');

  // Check at least one active link exists for this parent
  const links = await db.query(
    `SELECT id FROM parent_child_links
     WHERE parent_user_id = ? AND status = 'active' LIMIT 1`,
    [session.user_id]
  );

  if (links.length === 0) {
    // All links revoked — destroy session, redirect
    clearSessionCookie(res);
    return res.redirect('/parent/link-revoked');
    // /parent/link-revoked page: "Your access has been removed by the school.
    //  Contact your child's teacher if you think this is a mistake."
  }
  next();
}

Result: session invalidation is near-real-time (takes effect on the parent's next request, typically within seconds for an active browser session).

GAP 6: Parent Linked to Multiple Children

Explicitly allowed. One parent email can link to multiple children via separate parent_child_links rows — each from a separate teacher-issued token.

• Allowed: parent@example.com linked to Child A (Class 3B) AND Child B (Class 4A) — siblings in different classes, or cousins with the same guardian
• UI: /parent/home shows a child selector at the top when multiple children are linked. Data displayed is per-selected-child.
• Unique constraint: UNIQUE KEY uq_parent_child (parent_user_id, child_id) — same parent cannot be linked to the same child twice (idempotent via ON DUPLICATE KEY UPDATE status='active')
• Max children per parent: no hard cap in v1. Audit log records every link.
• Revocation scope: revoking one link does not affect other links. If ALL links are revoked, session is terminated (see GAP 5).

GAP 7: Audit Log — Failed Attempts Included

The following events must be logged in addition to the success events already defined:

GAP 8: MFA / Secondary Check — School Context Decision

Magic link via email is the authentication factor. In a school context, this is the correct tradeoff:

• Parents are not verified identity holders — schools do not have parent ID documents
• The token is the primary trust anchor (teacher issued it; teacher knows the parent)
• The email is the secondary factor (proves parent controls that inbox)
• Two-factor combined: something you were given (token) + something you control (email inbox)

v1 decision: no additional MFA. Rationale: adding SMS MFA requires phone number collection (GDPR data minimisation violation — we have no legitimate basis to require it from parents). Adding an app-based TOTP requires parent to install software (disproportionate friction for a read-only view).

If a school requires stronger auth (enterprise tier), the parent_child_links.access_level field can be extended to require re-verification via fresh magic link after 24h. This is a future config flag, not v1.

GAP 9: GDPR / Consent Layer

Parent linking has two GDPR obligations:

1. Parent's own data: By submitting their email and creating an account, the parent consents to Pickatale processing their email for the purpose of receiving reading updates. This consent is captured at onboarding (Step 4) via the notification preferences screen — the act of saving preferences constitutes consent to processing for that specific purpose.
2. Accessing child data: The school's DPA (already signed in Teacher Portal setup) covers the school's right to share child progress data with parents. Pickatale is the processor; the school controls who sees the child's data. The teacher's act of issuing the token is the school's authorisation.

-- Add to parent_child_links table:
ALTER TABLE parent_child_links
  ADD COLUMN consent_captured_at DATETIME NOT NULL DEFAULT NOW(),
  ADD COLUMN consent_basis VARCHAR(100) NOT NULL DEFAULT 'school_dpa_teacher_token';
-- consent_basis values: 'school_dpa_teacher_token' (v1 only)

What to show on the onboarding screen (Step 4): Below the notification preferences form, add a single line in small text:

"By continuing, you agree to Pickatale's Privacy Policy. Your child's school has authorised this connection under their data agreement with Pickatale."

No additional checkbox required — consent is captured via the school DPA chain.

-- Add to parent_notification_preferences:
ALTER TABLE parent_notification_preferences
  ADD COLUMN privacy_policy_version VARCHAR(20) NOT NULL DEFAULT 'v1.0',
  ADD COLUMN consent_captured_at DATETIME NOT NULL DEFAULT NOW();

GAP 10: Teacher Visibility Controls — Exact Field List

"Basic" and "Full" are now defined exactly. Stored as parent_child_links.visibility_level ENUM('basic', 'full') DEFAULT 'basic'.

v1 implements Basic only. Full is available in a future enterprise tier. The visibility_level column is created now so no migration is needed when Full ships.

-- Add to parent_child_links:
ALTER TABLE parent_child_links
  ADD COLUMN visibility_level ENUM('basic', 'full') NOT NULL DEFAULT 'basic';

All API responses serving parent data must check visibility_level before including Full-tier fields. Any Full-tier field requested with visibility_level='basic' returns null silently (not 403 — avoids leaking that the field exists).

Security Hardening, Legal Basis & Incident Rules

Threat Model & Residual Risk Acceptance

The threat model for parent linking is scoped to the realistic adversarial context: schools, not nation-state attackers.

Explicit residual risk acceptance (v1): The school-context threat model does not require MFA, phone verification, or government ID. The combination of teacher-issued token + email inbox ownership is sufficient for the data sensitivity level (read-only child reading activity). This decision is accepted by Pickatale and documented here.

Stronger Parent Auth — When and How

v1 uses magic link (token + email). The following upgrade path is defined for future tiers:

No TOTP, no SMS in roadmap — TOTP requires app install (disproportionate); SMS requires phone collection (GDPR basis problem). SSO is the correct enterprise upgrade path.

-- School-level config (future, create now to avoid migration):
ALTER TABLE schools ADD COLUMN parent_session_max_days INT NOT NULL DEFAULT 30;
ALTER TABLE schools ADD COLUMN parent_strict_session TINYINT(1) NOT NULL DEFAULT 0;
ALTER TABLE schools ADD COLUMN parent_sso_provider VARCHAR(50) DEFAULT NULL;
-- parent_sso_provider: NULL (magic link) | 'google' | 'microsoft'

Explicit Legal Basis by Market

Pickatale operates in multiple jurisdictions. The legal basis for processing parent data differs by market:

-- Market-specific consent flag (implement now):
ALTER TABLE parent_child_links
  ADD COLUMN explicit_consent_required TINYINT(1) NOT NULL DEFAULT 0,
  ADD COLUMN explicit_consent_given_at DATETIME DEFAULT NULL;
-- Set explicit_consent_required=1 for UAE, KSA, Turkey based on school.country_code
-- Onboarding screen shows consent checkbox if explicit_consent_required=1
-- Block /parent/home until explicit_consent_given_at IS NOT NULL when required

Abuse & Dispute Workflow

Three scenarios with exact handling steps:

Scenario A — Parent reports they did not link / did not request a link

1. Parent contacts school (not Pickatale directly in v1 — school is the controller)
2. Teacher revokes the link immediately via Teacher Portal → POST /api/parent-link/revoke
3. Session is terminated on next request (via route guard middleware)
4. Platform admin is notified if 3+ revocations occur from the same school within 24h → audit_alert: suspicious_link_activity
5. If school suspects data breach: escalate to Pickatale DPO → 72h GDPR breach notification window starts

Scenario B — Parent disputes data shown (claims it belongs to wrong child)

1. Parent contacts school
2. Teacher checks connected-parents list in Teacher Portal — confirms which child the parent is linked to
3. If wrong link: teacher revokes + re-issues correct token
4. Audit log provides full history: which teacher issued the token, when, for which child
5. Pickatale support can query: SELECT * FROM audit_log WHERE action IN ('parent_link_token_created','parent_link_completed') AND (actor_id=? OR target_id=?)

Scenario C — Parent is abusing access (e.g. harassing teacher based on child data)

1. Teacher revokes link immediately
2. Teacher can set a school-level block: INSERT INTO parent_access_blocks (school_id, parent_email_hash, blocked_by, reason) — blocked email cannot be linked to any child in that school
3. Block is enforced at /api/parent-link/start: check parent_access_blocks table before creating account

CREATE TABLE parent_access_blocks (
  id              INT PRIMARY KEY AUTO_INCREMENT,
  school_id       INT NOT NULL,
  email_hash      VARCHAR(64) NOT NULL,   -- sha256(normalized_email)
  blocked_by      VARCHAR(36) NOT NULL,  -- teacher/admin UUID
  reason          TEXT,
  created_at      DATETIME NOT NULL DEFAULT NOW(),
  UNIQUE KEY uq_school_email (school_id, email_hash)
);

Alerting & Incident Rules

The following conditions must trigger an automated alert to Pickatale platform admins (write to audit_alerts table + send internal notification):

CREATE TABLE audit_alerts (
  id           INT PRIMARY KEY AUTO_INCREMENT,
  condition    VARCHAR(100) NOT NULL,
  level        ENUM('warning','high','critical') NOT NULL,
  details_json JSON,
  created_at   DATETIME NOT NULL DEFAULT NOW(),
  resolved_at  DATETIME,
  resolved_by  VARCHAR(36)
);

Implementation Checklist for Claude Code

Complete in this order:

1. Create parent_link_tokens table (DDL above) in user_center DB
2. Create email_verifications table in user_center DB
3. Create parent_child_links table in user_center DB
4. Add email_verified_at column to users table; confirm role enum includes parent
5. Implement POST /api/internal/students/:child_uuid/parent-link-token in User Center
6. Implement GET /api/parent-link/validate in Parent Portal server
7. Implement POST /api/parent-link/start in Parent Portal server (with rate limiting + email send)
8. Implement GET /api/parent-link/verify in Parent Portal server (atomic transaction)
9. Implement POST /api/parent-link/revoke in Parent Portal server
10. Build Parent Portal frontend: /parent/link page, /parent/verify page (error states), /parent/onboarding page, /parent/home page, /parent/settings page (preference editing)
11. Implement route guard: unauthenticated → /parent/login; authenticated + no preferences row → /parent/onboarding; authenticated + preferences exist → allow through
12. Implement GET /api/parent/preferences and POST /api/parent/preferences
13. Create parent_notification_preferences table (DDL above) in user_center DB
14. Build Teacher Portal UI: Parent Access toggle, QR display, connected parents list, Unlink button, Regenerate link button
15. Remove old signup procedure, linkChild mutation, and 6-char code public endpoint
16. Migrate existing parents rows → users table; migrate parentChildren rows → parent_child_links
17. Send email_log rows on every PARENT_LINK_VERIFY email attempt
18. Create parent_access_blocks table; enforce block check in /api/parent-link/start
19. Create audit_alerts table; implement the 6 alerting conditions with exact thresholds
20. Add consent_captured_at + privacy_policy_version to parent_notification_preferences
21. Add explicit_consent_required + explicit_consent_given_at to parent_child_links; enforce by school.country_code
22. Add parent_session_max_days, parent_strict_session, parent_sso_provider to schools table (create now, use later)
23. Add visibility_level to parent_child_links; enforce field filtering in all parent data endpoints
24. Verify end-to-end: teacher generates token → parent opens link → submits email → clicks magic link → sees onboarding screen → saves preferences → lands on /parent/home. Re-login goes directly to home (no onboarding repeat). Revoke link → confirm parent session terminates on next request.

Parent Linking Contract v1.3 — 2026-04-19 · Authoritative · All decisions final · Ref: Wiki v1.9