Parental Consent Lifecycle

§1 Why this diagram is different

The common doc framing is a tidy five-state lifecycle. The database disagrees. There is no consent_state column anywhere. The lifecycle is expressed by the combination of five different enums (and one boolean) across five different tables. Each transition updates some of them, not all.

§2 The lifecycle stages (as users and auditors think of them)

This view is how the COPPA docs and a human auditor would describe the lifecycle. It's useful — but it's not how the code sees state. The code sees §3.

§3 How each stage maps to the database

Rows are the actual columns / booleans across five tables. Columns are the stages from §2. A cell shows what each column holds during that stage.

§4 Who writes what

Each transition is owned by a specific handler. A bug in any one of these leaves the database in a partial state where the matrix above no longer lines up cleanly.

§5 Query cookbook

The correct way to query each conceptual stage. Copy these rather than inferring.

-- parent has verified (household active + has parent_account_verification)
-- AND child has private-use consent recorded
-- AND child row is active AND child auth account is active
SELECT c.*
FROM children c
JOIN households h        ON h.id = c.household_id
JOIN child_auth_accounts ca ON ca.child_id = c.id
JOIN parental_consents pc   ON pc.child_id = c.id
WHERE c.child_status   = 'active'
  AND h.household_status = 'active'
  AND ca.login_state  = 'active'
  AND pc.scope        = 'specific_child_private_use'
  AND pc.consent_status = 'recorded';

SELECT c.id, pp.pending_parental_consent_expires_at
FROM children c
JOIN private_profiles pp ON pp.child_id = c.id
WHERE c.child_status = 'pending_private_use'
  AND pp.pending_parental_consent = true
  AND pp.parental_consent_date IS NULL
  AND pp.pending_parental_consent_expires_at < now() + interval '48 hours';

-- both "revoked by parent" and "restricted by billing lapse" surface here
-- because both manifest as parental_consents.consent_status='revoked' OR
-- child_status='restricted' — see source handlers in §4
SELECT c.*
FROM children c
WHERE c.child_status IN ('restricted', 'archived')
  AND (c.archived_at IS NULL OR c.archived_at > now() - interval '90 days');

§6 Gotchas (from the verification sweep)

§7 Verification pointers

• parental_consents table + CHECKsupabase/migrations_after_baseline/20260330190000_add_household_child_foundation.sql:113-152
• households enum20260330190000_add_household_child_foundation.sql:8-28
• children enum20260330190000_add_household_child_foundation.sql:55-87
• child_auth_accounts enum20260330190000_add_household_child_foundation.sql:235-275
• pending_parental_consent flag20260323_000000_add_pending_parental_consent_flow.sql:9
• Pending writerFuzzyCode/quart_server/services/coppa_consent.py:699+
• Pending cleanupFuzzyCode/scripts/cleanup_pending_parental_consent_accounts.py
• Stripe webhook → verifiedsupabase/functions/stripe-webhook/index.ts:862
• Archive sweepFuzzyCode/quart_server/services/system_jobs.py:33
• Parent rights: revoke / archiveservices/parent_rights.py:1346-1421, 1572-1578
• Private-use consent writeFuzzyCode/quart_server/services/child_private_use.py:265-319
• Parent-rights verification state20260407183000_add_parent_rights_structured_verification_fields.sql