0007 — Account and Family-Group identity + approval workflow

Status: Accepted (schema changes implemented in Phase 2)

Date: 2026-06-17

ADO work item: AB#3073

Deciders: Kristopher Turner (platform owner)

---

Context

Heritage Community Hub is a closed, member-only community — "a family, not just a church." Every access request is verified and approved by a minister. The account model has several requirements that differ from standard open-registration platforms:

• Adults (Member, Spouse) authenticate via Apple/Google social login (Clerk — ADR 0003).
• Children have no email address and no social login. They use a parent-managed credential (username + PIN/password) scoped to the homeschool portal and sections the parent approves.
• COPPA compliance is required for child sub-accounts.
• All membership changes go through a minister approval step — joining, adding a spouse, adding a child, and publishing content.
• Messaging is one-way broadcast only — no user-to-user reply threads. This collapses content moderation into the existing approval workflow.
• Family Group is the primary organizational unit — every approved member belongs to exactly one Family Group.

database/schema.sql exists as a design document but requires updates to reflect this model.

Decision

We will use a single Users table with nullable email and a credential_type discriminator to represent both adult (social login) and child (parent-managed) accounts. Family Groups are first-class entities. The ApprovalWorkflow table is extended to gate all membership and content events. An Announcements table (no reply FK) replaces any general Messages concept.

Account model

Account type	Auth	Email	Created by
Member (adult)	Apple/Google via Clerk	Required	Self-request → minister approval
Spouse (adult)	Apple/Google via Clerk	Required	Member adds → minister approval
Child (sub-account)	Parent-managed (username + PIN/password)	NULL	Parent adds → automatic (no minister step required for children of approved members)
Minister / Admin	Apple/Google via Clerk	Required	Direct admin creation

Approval workflow (all gated events)

New member:  Request → Minister verifies identity → Approves → Creates User(role=member) + FamilyGroup
Spouse add:  Member submits → Minister approves → Creates User(role=member, family_group_id=existing)
Child add:   Parent submits → Auto-approved (parent already vetted) → Creates User(credential_type='parent-managed', family_group_id=parent)
Content:     Author submits → Approver reviews → Approves/rejects → Publishes

Schema changes required (Phase 2)

• Users.email → nullable (children have no email).
• Users.credential_type → enum social | parent-managed.
• Users.parent_user_id → nullable FK (child → parent; NULL for adults).
• Users.username → nullable (used by parent-managed accounts in place of email).
• ApprovalWorkflow.workflow_type → enum extended: member-join | spouse-add | content-publish.
• Announcements table → new. Columns: id, author_id, title, body, published_at, audience (enum: all | ministry | group). No reply FK — one-way broadcast only.
• FamilyGroups table → confirm or create. Columns: id, name, primary_member_id.
• FamilyGroupMembers join table → family_group_id, user_id, relationship (enum: primary | spouse | child).

Alternatives considered

Option	Pros	Cons	Why not chosen
Single Users table, nullable email + credential_type (chosen)	Simplest; one table; minimal join complexity	Nullable fields require careful application-layer validation	— chosen
Separate ChildAccount table	Clean type separation; no nullable email in main table	Join complexity; two codepaths for every "get user" query	Complexity not justified; discriminator column is simpler
Separate AdultAccount + ChildAccount tables (polymorphic)	Very clean types	Significant query complexity; ORM friction	Over-engineered for this domain
External IdP handles all accounts (Clerk for children too)	Unified auth layer	Clerk does not support parent-managed child credentials (no email, no social); COPPA boundary unclear	Hard constraint — children cannot have social login

Consequences

Positive

• The Users table is the single source of truth for all community members regardless of account type.
• Clerk handles adult auth (ADR 0003); parent-managed child credentials are platform-owned — the COPPA boundary is clean (no PII sent to Clerk for minors).
• The approval workflow is a single, auditable table covering all gated events — joining, spouse, content.
• One-way Announcements (no replies) eliminates user-generated content moderation as a separate concern.

Negative / trade-offs

• Nullable email means application-layer code must handle the null case everywhere email is referenced. Add a DB check constraint: email IS NOT NULL OR credential_type = 'parent-managed'.
• ApprovalWorkflow becomes a central bottleneck — all gated events queue through it. Design with status + assignee fields so ministers can see pending items in a dashboard.

Risks

• COPPA compliance — child accounts must satisfy verifiable parental consent. The parent adding the child (already a vetted, minister-approved member) provides that consent. Document this flow explicitly in the compliance design. No child data (email, photo) is collected; only a username and PIN.
• Parent-managed credential security — PIN/passwords set by parents must be hashed (bcrypt/argon2) and stored in Users, not in plaintext. The auth endpoint for parent-managed accounts is separate from the Clerk social-login flow.
• Orphaned child accounts — if a parent member is removed, their child sub-accounts must be deactivated. Cascade or trigger required in Phase 2 schema.

Amendment (2026-06-18) — required phone number

Adult member profiles (AccountType IN Member, Spouse, Leadership) now require a phone number in addition to email. This supports multi-channel delivery (SMS + in-app push + email) per ADR 0013 (notification channels). Children remain a no-contact account: the managing parent is the contact of record, so Users.Phone is NULL allowed for Child and enforced via a CHECK constraint on AccountType.

Schema impact:

• Users.Phone NVARCHAR(32) is added (supersedes the prior optional PhoneNumber field).
• A CHECK constraint requires Phone IS NOT NULL for adult account types.
• Per-user notification-channel preferences (NotifyByEmail, NotifyBySms, NotifyByPush) are added to Users, all defaulting to enabled. Adults may opt channels off; children inherit their parent's notification routing.

This amendment does not change the identity / approval / family-group decisions above; it only extends the contact + delivery surface required by ADR 0013.

References

• Platform strategy — Account & Family-Group model
• ADR 0003 — Authentication: Clerk — adult auth; Clerk never touches child accounts
• ADR 0006 — Two-plane RBAC — role model; child scope
• ADR 0013 — Notification channels — multi-channel delivery (SMS + push + email) requiring phone on adult profiles
• database/schema.sql — current schema (updated in Phase 2 per changes above)