Data model design

Last updated: 2026-06-18 Status: Current — reconciled with ADRs 0003, 0006, 0007, 0012, 0013, and 0023.

---

Overview

Heritage Community Hub is a closed, minister-approved community platform. Every person in the system is represented by a single Users table row. Adults and children are distinguished by a CredentialType discriminator column rather than separate tables. Family Group is the primary organizational unit: every approved adult belongs to exactly one FamilyGroups record, and their spouse and children belong to the same group.

Application role-based access control (RBAC) is Plane 2 of a two-plane authorization model (ADR 0006). Plane 1 (Azure infrastructure access via Microsoft Entra) is entirely separate and never intersects with community member data.

All gated events — member join, spouse add, child add, and content publish — flow through a single ApprovalWorkflow table so that ministers have one dashboard for all pending decisions.

The schema design is in database/schema.sql. That file is a design document, not a migration script; versioned migrations are a Phase 2 deliverable.

---

Core entities

Users and family groups

Adults vs children

A single Users table covers all community members. The CredentialType column distinguishes the two account shapes:

Field	Adult (social)	Child (parent-managed)
CredentialType	'social'	'parent-managed'
ExternalUserID	Clerk subject ID (required)	NULL
Email	Required	NULL — children have no email address
Username	NULL	Required — used for login in place of email
PasswordHash	NULL	Argon2id hash of the parent-assigned PIN or password
Phone	Required	NULL — parent is the contact of record
ParentUserID	NULL	FK to the managing parent's UserID

Three database constraints enforce this shape:

• CK_Users_EmailOrParentManaged — Email is NOT NULL or CredentialType = 'parent-managed'.
• CK_Users_SocialShape — social accounts must have both ExternalUserID and Email.
• CK_Users_ParentManagedShape — parent-managed accounts must have Username, PasswordHash, and ParentUserID.
• CK_Users_PhoneRequiredForAdults — Phone is NOT NULL except for AccountType = 'Child' (added per ADR 0013, 2026-06-18).

Adults authenticate through Clerk (Apple/Google social login — ADR 0003). Clerk never touches child accounts; child credential verification happens server-side inside the platform API. This keeps the Children's Online Privacy Protection Act (COPPA) boundary clean: no child personally identifiable information (PII) is sent to any external identity provider.

AccountType classifies adults further: 'Member', 'Spouse', or 'Leadership' (all social login). Children have AccountType = 'Child'.

Lifecycle states

Every Users row carries a Status column that drives access:

Status	Meaning
pending_approval	Account exists; awaiting minister review
active	Approved; full access per role
suspended	Temporarily restricted by admin
deactivated	Permanently removed from the community

New social-login accounts are created with status = 'pending_approval' and role = 'visitor' on first Clerk sign-in. A minister transitions them to active after identity verification.

Notification channel preferences

Adults carry three BIT columns (NotifyByEmail, NotifyBySms, NotifyByPush), all defaulting to enabled. Members may opt channels off in their profile. Children have no independent notification preferences; the managing parent's settings govern delivery routing for their household.

FamilyGroups

FamilyGroups is a first-class table, not a tag on Users. Every approved adult member belongs to exactly one family group; their spouse and children belong to the same group.

FamilyGroups
  FamilyGroupID (PK)
  FamilyName
  PrimaryMemberID → Users (the minister-approved anchor adult)
  CreatedBy       → Users (the minister who approved)

The deferred foreign key Users.FamilyGroupID → FamilyGroups.FamilyGroupID is wired after the FamilyGroups table is created to avoid a circular dependency at DDL time.

FamilyGroupMembers

FamilyGroupMembers is an explicit join table that records each person's relationship to their family group:

FamilyGroupMembers
  FamilyGroupID → FamilyGroups
  UserID        → Users
  Relationship  -- 'primary' | 'spouse' | 'child'

A user appears in this table exactly once. The UNIQUE(FamilyGroupID, UserID) constraint enforces this.

---

Roles

The six canonical Plane-2 application roles

Role slugs are stored in the Roles table and assigned to users via UserRoles. The API reads Users.role on every authorization check; the client never sends a trusted role claim (ADR 0006).

Role slug	Display name	Permission level	What this role can do
admin	Community leader	5	Full system access; approve members; all configuration
ministry_leader	Ministry leader	4	Manages their ministry area; approves announcements; limited admin
group_leader	Small group leader	3	Manages their assigned small group
comms_author	Communications author	—	Drafts Announcements for assigned audiences; submits to approval queue; cannot approve, reject, or publish anything, including their own drafts (ADR 0023)
member	Member	2	Standard access; family management with approval
visitor	Visitor / prospective	1	Approval-gated; minimal read access only

comms_author is the sixth role added by ADR 0023 (2026-06-18). It extends the original five canonical roles defined in ADR 0006. Its permission scope is strictly additive and narrowly bounded to the Announcements feature; it carries no permissions in any other feature area.

Children are not a separate role. They inherit a restricted scope of the member role as defined by their parent's approval settings, enforced at the application layer on top of the role check.

How a social-login subject maps to an app role

1. Clerk validates the Apple or Google token and issues a session JSON Web Token (JWT) containing the provider sub claim.
2. On first sign-in, the API creates a Users row: CredentialType = 'social', role = 'visitor', status = 'pending_approval'.
3. A minister reviews and approves the user; the API updates Users.role and Users.status. Role updates are admin-only API operations — no endpoint allows a member to self-modify their role.
4. On all subsequent API calls, the server reads Users.role from the database. The JWT's sub is the only trusted claim from the client.

---

Content entities

Announcements

Announcements are the platform's primary communication mechanism. They are one-way broadcast only — there is no reply or comment table linked to Announcements, no reply API endpoint, and no reply affordance in any client surface. This constraint is architectural, not a configuration toggle (ADR 0007, ADR 0012).

Key columns:

Column	Purpose
AuthorID	Who wrote the draft
ApprovedBy	Who approved for publication (must differ from AuthorID when drafter holds comms_author role — enforced server-side)
ApprovalWorkflowID	Link to the gating ApprovalWorkflow row
AudienceScope	'all' | 'role' | 'group' | 'ministry'
AudienceRoleID	Populated when AudienceScope = 'role'
AudienceGroupID	Populated when AudienceScope = 'group'
Priority	'low' | 'normal' | 'high' | 'urgent'
PublishAt	Optional scheduled publish time; NULL = publish immediately on approval
ExpiresAt	Optional expiry; after this timestamp the announcement is hidden from feeds
Status	Workflow state — see approval workflows below

An AnnouncementReceipts companion table (defined in ADR 0012) records per-member delivery and read timestamps. This gives ministry leadership visibility into who has seen a time-sensitive communication.

Calendar events

The Events table stores community, small-group, ministry, and leadership calendar events. Visibility is controlled by VisibilityLevel (1 = all, 2 = ministry, 3 = small group, 4 = leadership) and an optional GroupID for group-scoped events. EventAttendees tracks RSVP status and check-in state per member.

Media files

Media assets (sermon recordings, music, attachments) are represented by a MediaFiles table (to be added in Phase 2). The Sermons feature (ADR 0010) and Music Hub share this table through feature-specific join tables. The schema design document does not yet include MediaFiles; it is a Phase 2 addition.

---

Approval workflows

All gated events in the platform flow through a single ApprovalWorkflow table. This gives ministers one queue for all pending decisions rather than feature-specific queues.

Workflow types

WorkflowType	What is being approved
member-join	An adult requesting community membership
spouse-add	An existing member adding their spouse
child-add	A member adding a parent-managed child sub-account
content-publish	An announcement submitted for publication

Child-add workflows are typically auto-approved (Status = 'AutoApproved') because the parent is already a vetted, minister-approved member.

Status values and transitions

member-join / spouse-add / content-publish:

  Pending  →  Approved
           →  Rejected

child-add:

  Pending  →  AutoApproved  (no minister step required)

For announcements, the Announcements.Status column tracks the content lifecycle in more detail (see below); ApprovalWorkflow.Status tracks the gating approval event itself.

Announcement state machine (ADR 0012 and ADR 0023)

  draft  ──[author submits]──►  pending_approval
    ▲                                 │
    │  [approver rejects]             │  [approver approves]
    └────────────────────             ▼
                               approved / scheduled ──► published ──► expired

A comms_author may move an announcement from draft to pending_approval only. Only ministry_leader and admin roles may transition from pending_approval to approved or rejected. The self-approve prohibition (approved_by_id ≠ author_user_id) is enforced by the API at the approval endpoint.

On approval (or when PublishAt arrives), the API calls the platform notifications transport (ADR 0013), which fans out through the enabled channels — email, SMS, and in-app push — according to each recipient's preferences.

Audit trail

Every state transition writes an AuditLog row: action, actor, target entity type and ID, old values, and new values stored as JSON. Approver identity is also recorded directly on the ApprovalWorkflow row in the MinisterID / AdminID columns, providing a second, query-friendly approval trail.

---

Key design decisions

Nullable email for children

Users.Email is nullable. Adults must have an email (enforced by CK_Users_SocialShape and CK_Users_EmailOrParentManaged). Children have no email address. A filtered unique index (WHERE Email IS NOT NULL) enforces uniqueness across adult accounts while allowing multiple NULL values (one per child).

The same pattern applies to Username: nullable for adults, required for children, enforced by a filtered unique index (WHERE Username IS NOT NULL).

Argon2id for child credentials

Parent-managed child accounts authenticate with a username and PIN or password set by the parent. These credentials are hashed with Argon2id and stored in Users.PasswordHash. This column is never populated for social-login adults. The child auth endpoint is a separate code path from the Clerk social-login flow.

Phone required for adults, null allowed for children

Users.Phone is required for all adult account types (Member, Spouse, Leadership). The CK_Users_PhoneRequiredForAdults constraint enforces this at the database layer. Children have no phone requirement; the managing parent's Phone is used for any household-level SMS or push delivery. This requirement was added in the ADR 0007 amendment of 2026-06-18 to support multi-channel notification delivery (ADR 0013).

A legacy PhoneNumber column is retained for backward compatibility during the Phase 2 migration; new code uses Phone.

Filtered unique indexes on nullable columns

Standard UNIQUE constraints in SQL Server (T-SQL dialect) treat NULL as a distinct value, which would prevent more than one NULL per column. The schema uses filtered unique indexes to enforce uniqueness only on non-null values:

-- UNVALIDATED
CREATE UNIQUE INDEX UX_Users_Email
    ON Users(Email)
    WHERE Email IS NOT NULL;

CREATE UNIQUE INDEX UX_Users_Username
    ON Users(Username)
    WHERE Username IS NOT NULL;

Single ApprovalWorkflow table

A single table covers all gated events rather than per-feature approval tables. This reduces minister dashboard complexity: one query with a WorkflowType filter surfaces all pending decisions. The SubjectEntityType and SubjectEntityID columns handle the polymorphic reference to the approved entity when the subject is not a Users row (for example, a content-publish workflow points to an Announcements row).

comms_author audience scope

The comms_author role requires a companion UserCommunicationsScope join table (one row per author per permitted audience scope). This stores what audiences a given author is authorized to write for — community-wide, a specific ministry, or a specific small group. The API rejects draft creation that exceeds the author's assigned scope. This table is a Phase 2 addition; its design is specified in ADR 0023.

---

Schema reference

The full table definitions, constraints, indexes, and sample seed data are in:

database/schema.sql

That file is a design document in T-SQL (SQL Server) dialect. Phase 2 will translate it into versioned migration scripts targeting PostgreSQL (Azure Database for PostgreSQL Flexible Server — ADR locked 2026-06-18).

---

Community groups

GroupResource

Stores files and links shared within a specific group (AB#3314, AB#3442).

Field	Type	Note
id	String	CUID primary key
groupId	String	FK to Group
uploadedById	String	FK to User
title	String	Display name of the resource
description	String?	Optional summary
externalUrl	String?	Link-type resources; mutually exclusive with storageKey
storageKey	String?	Blob storage key for uploaded files
isFile	Boolean	true when a file was uploaded rather than a URL linked
mimeType	String?	MIME type of uploaded file
fileSize	Int?	File size in bytes
isActive	Boolean	Soft-delete flag
createdAt	DateTime
updatedAt	DateTime

Relations: belongs to one Group; uploaded by one User.

GroupThread

A discussion thread anchored to a group (AB#3439). Threads hold an ordered set of GroupPost replies.

Field	Type	Note
id	String	CUID primary key
groupId	String	FK to Group
authorId	String	FK to User (thread creator)
title	String	Thread subject
body	String	Opening message body
isPinned	Boolean	Pinned threads sort to top
isActive	Boolean	Soft-delete flag
createdAt	DateTime
updatedAt	DateTime

Relations: belongs to one Group; authored by one User; has many GroupPost rows.

GroupPost

A reply within a GroupThread (AB#3439). Cascades delete with its parent thread.

Field	Type	Note
id	String	CUID primary key
threadId	String	FK to GroupThread (cascade delete)
authorId	String	FK to User
body	String	Post body text
isActive	Boolean	Soft-delete flag
createdAt	DateTime

Relations: belongs to one GroupThread; authored by one User.

---

Events

EventAttendee

Tracks per-member RSVP state and check-in status for a calendar event.

Field	Type	Note
id	String	CUID primary key
eventId	String	FK to Event
userId	String	FK to User
rsvpStatus	RsvpStatus	PENDING | ATTENDING | NOT_ATTENDING | MAYBE
rsvpAt	DateTime	When the RSVP was recorded
checkedIn	Boolean	Whether the member checked in on the day
checkInAt	DateTime?	Timestamp of check-in

Relations: belongs to one Event and one User. Unique constraint on (eventId, userId).

---

Announcements and communications

AnnouncementReceipt

Per-member delivery and read receipt for an announcement (ADR 0012). Provides ministry leadership visibility into who has seen a time-sensitive broadcast.

Field	Type	Note
id	String	CUID primary key
announcementId	String	FK to Announcement
userId	String	FK to User
channel	NotificationChannel	EMAIL | SMS | PUSH | IN_APP
deliveredAt	DateTime?	When the message was dispatched
readAt	DateTime?	When the member opened it
createdAt	DateTime

Relations: belongs to one Announcement and one User. Unique constraint on (announcementId, userId, channel).

UserCommunicationsScope

Defines which audiences a comms_author is authorized to write for (ADR 0023). One row per author per permitted scope. The API rejects draft creation that exceeds the author's assigned scope.

Field	Type	Note
id	String	CUID primary key
userId	String	FK to User (the comms_author)
scopeType	CommsScopeType	COMMUNITY | MINISTRY | GROUP
groupId	String?	FK to Group; populated when scopeType is GROUP or MINISTRY
createdAt	DateTime

Relations: belongs to one User; optionally scoped to one Group. Unique constraint on (userId, scopeType, groupId).

---

Identity and roles

UserRole

Auditable history of role assignments. User.role is the fast-path auth check; this table is the complete grant ledger (AB#3357).

Field	Type	Note
id	String	CUID primary key
userId	String	FK to User receiving the role
roleId	String	FK to Role definition
assignedBy	String	FK to User (admin who granted the role)
isActive	Boolean	Whether this grant is currently active
createdAt	DateTime

Relations: belongs to one User (recipient) and one Role; references the assigning User. Unique constraint on (userId, roleId).

Invitation

Invitation codes issued by ministers to prospective members (AB#3357). Supports max-use counts and expiry.

Field	Type	Note
id	String	CUID primary key
code	String	Unique invitation token
email	String?	Optional pre-assigned email address
createdById	String	FK to User (minister who issued the code)
expiresAt	DateTime?	Expiry; null = no expiry
usedById	String?	FK to User who redeemed the code
usedAt	DateTime?	Redemption timestamp
isActive	Boolean	Soft-delete / revoke flag
maxUses	Int	Maximum number of uses; defaults to 1
currentUses	Int	Running use count
createdAt	DateTime

Relations: created by one User; optionally used by one User.

MemberGroupLeaderAssignment

Maps a member to their assigned group leader(s) (AB#4530). Editable by ministry_leader or group_leader roles.

Field	Type	Note
id	String	CUID primary key
memberId	String	FK to User being assigned (cascade delete)
groupLeaderId	String	FK to User who is the group leader (cascade delete)
assignedById	String	FK to User who made the assignment
assignedAt	DateTime	Timestamp of assignment

Relations: references three distinct User rows. Unique constraint on (memberId, groupLeaderId).

---

Push notifications

PushToken

Expo push token for delivering notifications to native iOS and Android clients.

Field	Type	Note
id	String	CUID primary key
userId	String	FK to User
token	String	Expo push token (unique)
platform	String	'ios' or 'android'
isActive	Boolean	Soft-delete flag; set false on logout or token expiry
createdAt	DateTime
updatedAt	DateTime

Relations: belongs to one User.

PushSubscription

Web Push (VAPID) subscription for PWA and browser-based push delivery (AB#4432). One row per browser/device/PWA install. Stale subscriptions (410 Gone from push service) are removed by the notifier.

Field	Type	Note
id	String	CUID primary key
userId	String	FK to User (cascade delete on logout)
endpoint	String	Browser push service URL (globally unique)
p256dh	String	Diffie-Hellman public key
auth	String	Auth secret
userAgent	String?	Browser/device identifier
createdAt	DateTime

Relations: belongs to one User. Maps to table push_subscriptions.

---

Audit

AuditLog

Immutable append-only record of every state transition in the platform. Actors, entities, old values, and new values are all captured.

Field	Type	Note
id	String	CUID primary key
actorId	String?	FK to User performing the action; null for system-initiated events
action	String	Verb describing what happened (e.g., 'ApproveUser', 'PublishAnnouncement')
entityType	String?	Name of the affected model (e.g., 'User', 'Announcement')
entityId	String?	ID of the affected row
oldValues	Json?	Snapshot of relevant fields before the change
newValues	Json?	Snapshot of relevant fields after the change
ipAddress	String?	Requester IP
userAgent	String?	Requester user-agent string
createdAt	DateTime

Relations: optionally references one User as actor.

---

Media and sermons

MediaSeries

A named collection of media items (sermon series, music album, video playlist). Access is gated by AccessTier (AB#3137).

Field	Type	Note
id	String	CUID primary key
name	String	Series display name
type	MediaType	MESSAGES | MUSIC | VIDEOS
accessTier	AccessTier	MEMBERS or BAPTIZED_ONLY
description	String?
artworkKey	String?	Storage key for series artwork; never returned raw to client
createdById	String	FK to User
createdAt	DateTime
updatedAt	DateTime

Relations: created by one User; has many MediaItem rows. Maps to table media_series.

MediaItem

A single recorded sermon, song, or video within a series. Tracks upload and publish actors separately (AB#3137).

Field	Type	Note
id	String	CUID primary key
title	String
speaker	String?	Free text; not a FK
seriesId	String	FK to MediaSeries
itemType	MediaType	MESSAGES | MUSIC | VIDEOS
mediaType	String	Technical format: 'audio' or 'video'
date	DateTime	Recording or release date
tags	String?	Comma-separated controlled vocabulary
durationSec	Int?	Playback duration in seconds
storageKey	String	Provider-agnostic object key; never returned to clients
status	MediaStatus	DRAFT | PROCESSING | PUBLISHED | UNPUBLISHED | ARCHIVED
uploadedById	String	FK to User
publishedById	String?	FK to User
publishedAt	DateTime?
createdAt	DateTime
updatedAt	DateTime

Relations: belongs to one MediaSeries; has many MediaChapter and MediaReceipt rows; optionally referenced by one LiveEvent as its archived recording. Maps to table media_items.

MediaChapter

A named timestamp within a MediaItem for navigation (e.g., sermon outline points).

Field	Type	Note
id	String	CUID primary key
itemId	String	FK to MediaItem (cascade delete)
title	String	Chapter label
offsetSecs	Int	Start offset in seconds from the beginning of the item

Relations: belongs to one MediaItem. Maps to table media_chapters.

MediaReceipt

Per-member playback progress and completion state for a media item.

Field	Type	Note
id	String	CUID primary key
itemId	String	FK to MediaItem (cascade delete)
memberId	String	FK to User
progressSecs	Int	Furthest playback position in seconds
completed	Boolean	Whether the member reached the end
updatedAt	DateTime

Relations: belongs to one MediaItem and one User. Unique constraint on (itemId, memberId). Maps to table media_receipts.

LiveEvent

An active or scheduled Cloudflare Stream live broadcast. Cloudflare input and playback IDs are server-side only and never returned to API clients.

Field	Type	Note
id	String	CUID primary key
title	String
cloudflareInputId	String	Cloudflare Stream RTMP input ID; server-side only
cloudflarePlaybackId	String	Cloudflare Stream playback ID; server-side only
status	LiveEventStatus	SCHEDULED | LIVE | ENDED | ARCHIVED
accessTier	AccessTier	MEMBERS or BAPTIZED_ONLY
scheduledAt	DateTime?
startedAt	DateTime?
endedAt	DateTime?
archivedItemId	String?	FK to MediaItem created from the recording after the stream ends
createdById	String	FK to User
createdAt	DateTime

Relations: created by one User; optionally linked to one archived MediaItem. Maps to table live_events.

---

Marketplace

Listing

A member-posted item or service for sale, trade, or giveaway within the community marketplace.

Field	Type	Note
id	String	CUID primary key
sellerId	String	FK to User
title	String
description	String
price	Decimal?	Null when isFree is true
isFree	Boolean	Marks a no-cost listing
category	ListingCategory	GOODS | SERVICES | HOUSING | VEHICLES | JOBS | FREE | WANTED | OTHER
status	ListingStatus	ACTIVE | SOLD | WITHDRAWN | PENDING_REVIEW
expiresAt	DateTime?	Optional expiry
createdAt	DateTime
updatedAt	DateTime

Relations: posted by one User (seller); has many ListingImage and ListingReview rows. Maps to table listings.

ListingImage

An image attached to a marketplace listing. Multiple images per listing are ordered by sortOrder.

Field	Type	Note
id	String	CUID primary key
listingId	String	FK to Listing (cascade delete)
storageKey	String	Blob storage key
sortOrder	Int	Display order; lower values appear first

Relations: belongs to one Listing. Maps to table listing_images.

ListingReview

A member review of a marketplace listing. One review per member per listing.

Field	Type	Note
id	String	CUID primary key
listingId	String	FK to Listing (cascade delete)
reviewerId	String	FK to User
rating	Int	Numeric rating
body	String?	Optional review text
createdAt	DateTime

Relations: belongs to one Listing; written by one User. Unique constraint on (listingId, reviewerId). Maps to table listing_reviews.

---

Homeschool

HomeschoolEnrollment

Links a student to their guardian and tracks enrollment status in the homeschool program.

Field	Type	Note
id	String	CUID primary key
studentId	String	FK to User (student)
guardianId	String	FK to User (guardian)
grade	String?	Current grade level
status	EnrollmentStatus	PENDING | ACTIVE | WITHDRAWN | GRADUATED
enrolledAt	DateTime?
withdrawnAt	DateTime?
createdAt	DateTime
updatedAt	DateTime

Relations: references two distinct User rows; has many AcademicRecord rows. Maps to table homeschool_enrollments.

AcademicRecord

A subject-level grade or progress record tied to a homeschool enrollment.

Field	Type	Note
id	String	CUID primary key
enrollmentId	String	FK to HomeschoolEnrollment (cascade delete)
subject	String	Subject name
grade	String?	Letter or numeric grade
notes	String?	Instructor notes
termYear	String	Academic term identifier (e.g., '2025-Fall')
createdAt	DateTime

Relations: belongs to one HomeschoolEnrollment. Maps to table academic_records.

EducationResource

A shareable teaching resource (file or link) available to the homeschool community.

Field	Type	Note
id	String	CUID primary key
title	String
description	String?
storageKey	String?	Blob storage key for uploaded files
externalUrl	String?	Link for external resources
subject	String?	Subject area
gradeLevel	String?	Intended grade level
uploadedById	String	FK to User who contributed the resource
downloadCount	Int	Running download counter
createdAt	DateTime
updatedAt	DateTime

Relations: uploaded by one User. Maps to table education_resources.

---

Ride share

Trip

A driver-offered trip with origin, destination, departure time, and available seats.

Field	Type	Note
id	String	CUID primary key
driverId	String	FK to User
origin	String	Departure location
destination	String	Arrival location
departureAt	DateTime	Scheduled departure
seats	Int	Total passenger capacity offered
notes	String?	Driver notes (route, stops, etc.)
status	TripStatus	OPEN | FULL | DEPARTED | COMPLETED | CANCELLED
createdAt	DateTime
updatedAt	DateTime

Relations: driven by one User; has many TripPassenger rows. Maps to table trips.

TripPassenger

A passenger request or confirmation on a specific trip.

Field	Type	Note
id	String	CUID primary key
tripId	String	FK to Trip (cascade delete)
userId	String	FK to User
status	PassengerStatus	PENDING | CONFIRMED | DECLINED | CANCELLED
requestedAt	DateTime	When the passenger requested the seat

Relations: belongs to one Trip and one User. Unique constraint on (tripId, userId). Maps to table trip_passengers.

---

Pony Express (community courier)

DeliveryRequest

A member request for a community courier to pick up and deliver an item.

Field	Type	Note
id	String	CUID primary key
requesterId	String	FK to User
description	String	What needs to be delivered
pickupAddress	String	Where to collect the item
dropoffAddress	String	Where to deliver the item
neededBy	DateTime?	Optional deadline
status	DeliveryStatus	REQUESTED | CLAIMED | IN_TRANSIT | DELIVERED | CANCELLED
createdAt	DateTime
updatedAt	DateTime

Relations: requested by one User; has many DeliveryOffer and DeliveryTracking rows. Maps to table delivery_requests.

DeliveryOffer

A courier's offer to fulfill a DeliveryRequest. Only one offer may be accepted.

Field	Type	Note
id	String	CUID primary key
requestId	String	FK to DeliveryRequest (cascade delete)
courierId	String	FK to User
notes	String?	Courier notes
accepted	Boolean	Whether the requester accepted this offer
createdAt	DateTime

Relations: belongs to one DeliveryRequest; offered by one User. Maps to table delivery_offers.

DeliveryTracking

An ordered status log for a delivery request, recording each stage transition.

Field	Type	Note
id	String	CUID primary key
requestId	String	FK to DeliveryRequest (cascade delete)
status	DeliveryStatus	The status recorded at this point
notes	String?	Optional status note
createdAt	DateTime

Relations: belongs to one DeliveryRequest. Maps to table delivery_tracking.

---

Scripture

BibleTranslation

A Bible translation available for use with the verse-of-the-day feature.

Field	Type	Note
id	String	CUID primary key
name	String	Full translation name (e.g., 'King James Version')
abbreviation	String	Short code (e.g., 'KJV'); unique
isDefault	Boolean	Whether this is the platform default translation
isActive	Boolean
createdAt	DateTime
updatedAt	DateTime

Relations: has many VerseOfDay rows. Maps to table bible_translations.

VerseOfDay

A scripture verse for display on the home screen. Verses with a null displayDate form a rotating pool; a non-null displayDate pins the verse to that specific calendar day.

Field	Type	Note
id	String	CUID primary key
reference	String	Scripture reference (e.g., 'John 3:16')
text	String	Full verse text
translationId	String?	FK to BibleTranslation
displayDate	DateTime?	Null = pool; non-null = pinned date
status	VerseStatus	SCHEDULED | ACTIVE | PAST
createdById	String?	FK to User
createdAt	DateTime
updatedAt	DateTime

Relations: optionally linked to one BibleTranslation and one User. Maps to table verses_of_day.

---

Sister communities

SisterCommunity

A record of an affiliated or prospective partner community in the network.

Field	Type	Note
id	String	CUID primary key
name	String	Community name
location	String	City or general location
state	String?	US state or region
website	String?	Community website URL
contactName	String?	Primary contact person
contactEmail	String?	Contact email address
notes	String?	Internal notes
status	SisterCommunityStatus	ACTIVE | INACTIVE | PROSPECTIVE
createdAt	DateTime
updatedAt	DateTime

Maps to table sister_communities. No FK relations to other models.

---

Related ADRs

ADR	Title	Summary
ADR 0003	Authentication: Clerk + Apple/Google social login	Clerk handles adult auth via social login; child credentials are platform-owned and Argon2id-hashed; Entra is infrastructure-only
ADR 0006	Two-plane RBAC with reconciled role model	Defines the six canonical Plane-2 application roles; server-side enforcement only; client role claims are never trusted
ADR 0007	Account and family-group identity + approval workflow	Single Users table with nullable email; FamilyGroups as first-class entity; ApprovalWorkflow gates all membership and content events
ADR 0012	Announcements (one-way broadcast)	Announcements table schema, audience scoping, priority, status lifecycle, AnnouncementReceipts, and the one-way constraint
ADR 0013	Messaging and notifications	Channel adapters (email, SMS, in-app push), fan-out, retry logic, and per-member delivery preferences
ADR 0023	Communications authoring and approval workflow	comms_author sixth role; two-actor draft/approve separation; self-approve prohibition; UserCommunicationsScope table