Data Model Reference

This page documents the core data entities used in DoCurious, their TypeScript interfaces, relationships, and the mapping between frontend types and the backend SQL schema.

Backend Prisma Schema

The backend has its own Prisma schema with both legacy SQL models and modern UUID-based models. See Database & Prisma for the server-side schema, ER diagrams, and the dual model data flow.

Entity Relationship Diagram

Core Entities

User

The central entity representing all platform users. Users are identified by UUID and carry a role that determines their access level. See Accounts guide for the full feature description and Role guides for per-role behavior.

// src/types/user.types.ts
type UserRole =
  | 'user'            // General user (personal challenge doer)
  | 'vendor'          // Marketplace vendor
  | 'platform_admin'  // Full platform access
  | 'head_school_admin' // Can create other admins
  | 'school_admin'    // School administrator
  | 'teacher'         // Teacher within a school
  | 'student'         // Student within a school
  | 'parent'          // Parent/guardian
  | 'staff'           // DoCurious internal staff

type AccountTier = 'tier_1_school_only' | 'tier_2_full' | 'standard'
type ConsentStatus = 'not_required' | 'pending' | 'granted' | 'revoked'

interface User extends BaseEntity {
  email: string
  displayName: string
  dateOfBirth: string
  role: UserRole
  accountTier: AccountTier
  accountStatus: AccountStatus
  isUnder13: boolean
  parentalConsentStatus: ConsentStatus
  profile?: UserProfile
  avatarUrl?: string
  interests: string[]
  schoolId?: UUID
  vendorProfileId?: UUID
  linkedChildIds?: UUID[]
  balance?: number
}

FE vs SQL mapping:

Concept	FE Field	SQL Column
ID	id (UUID string)	uuid (backend promotes uuid to FE id)
Display name	displayName	username
Full name	profile.firstName / profile.lastName	first_name / last_name
Avatar	avatarUrl	avatar
Role	role (9 values)	role (USER, ADMIN, CHILD, VENDOR)
Account tier	accountTier	Not in SQL
Under 13 flag	isUnder13	Not in SQL
Parental consent	parentalConsentStatus	Not in SQL
XP / Level / Streak	Via gamification store	Not in SQL

Challenge

The primary content entity. Represents an activity a user can discover, attempt, and document. See Challenges guide for the full feature description.

// src/types/challenge.types.ts
type ChallengeStatus = 'draft' | 'pending_review' | 'approved' | 'rejected' | 'archived'
type DifficultyLevel = 'beginner' | 'intermediate' | 'advanced' | 'expert'
type FulfillmentType = 'kit' | 'hosted' | 'digitally_guided'

interface Challenge extends BaseEntity {
  title: string
  specificMission?: string
  description: string
  shortDescription?: string
  source: ChallengeSource
  vendorId?: UUID
  categoryIds: UUID[]
  difficulty: DifficultyLevel
  estimatedDuration: string
  ageMin?: number
  ageMax?: number
  soloGroup: SoloGroup
  isFree: boolean
  costAmount?: number
  fulfillmentType?: FulfillmentType
  coverImageUrl: string
  hasMilestones: boolean
  milestones?: ChallengeMilestone[]
  verificationRequirements?: VerificationRequirement[]
  baseXp: number
  status: ChallengeStatus
  completionCount: number
  trackRecordCount: number
  isFeatured: boolean
  instances?: ChallengeInstance[]
}

FE vs SQL mapping:

Concept	FE Field	SQL Column
Status	draft/pending_review/approved/archived	INACTIVE/PENDING/ACTIVE/INACTIVE
Mission	specificMission	mission
Price	costAmount (number)	price (varchar)
Duration	estimatedDuration (string)	completion_time (double)
Age range	ageMin/ageMax	lower_bound/upper_bound
Difficulty	beginner/intermediate/advanced/expert	0/1/2/3 (integers)
Type	fulfillmentType	type (HOSTED/ONLINE/KIT/AUTONOMOUS/AFFILIATE)
Media	Embedded URL fields	Separate media table

Two-tier architecture

The SQL schema uses challenges (template) and challenge_instances (specific session with schedule, location, pricing). The FE uses a single flat Challenge interface with an optional instances array populated on the detail view.

Challenge Instance

A specific offering of a challenge template, with its own schedule, location, capacity, and difficulty.

// src/types/challenge.types.ts
interface ChallengeInstance extends BaseEntity {
  challengeId: UUID
  difficulty: DifficultyLevel
  duration?: number
  status: ChallengeInstanceStatus
  totalSeats?: number
  schedule?: InstanceSchedule[]
  availableDates?: string[]
  streetAddress?: string
  city?: string
  state?: string
  lat?: number
  lng?: number
  disabilityFriendly: boolean
  consentRequired: boolean
}

Track Record

Documentation of a user's challenge completion. Contains ordered entries with text and media. See Track Records guide for the full feature description.

// src/types/challenge.types.ts
type TrackRecordStatus = 'draft' | 'submitted' | 'verified' | 'rejected' | 'unverified'

interface TrackRecord extends BaseEntity {
  userChallengeId: UUID
  userId: UUID
  challengeId: UUID
  status: TrackRecordStatus
  storageUsedBytes: number
  sharingScope: 'private' | 'communities' | 'individuals' | 'public'
  verificationAttempts: number  // Max 3
  escalated: boolean
  entries?: TrackRecordEntry[]
}

interface TrackRecordEntry extends BaseEntity {
  trackRecordId: UUID
  textContent?: string
  milestoneId?: UUID
  displayOrder: number
  media?: TrackRecordEntryMedia[]
  thumbsUpCount: number
  commentCount: number
}

Community

Social spaces where users share challenges and track records. Six community types with three feed types. See Communities guide for the full feature description.

// src/types/community.types.ts
type CommunityType =
  | 'family'
  | 'trusted_friends'
  | 'casual_friends'
  | 'interest_hobby'
  | 'other'
  | 'institutional'

type FeedType = 'bucket_list' | 'track_record' | 'discussion'

interface Community extends BaseEntity {
  name: string
  description: string
  type: CommunityType
  visibility: 'public' | 'private' | 'secret'
  memberCount: number
  creator?: { id: UUID; displayName: string }
}

Gift

Challenge gifting between users. See Gifting guide for the full feature description.

// src/types/gift.types.ts
interface Gift extends BaseEntity {
  senderId: UUID
  recipientId?: UUID
  recipientEmail?: string
  challengeId: UUID
  message?: string
  status: 'pending' | 'accepted' | 'declined' | 'expired' | 'cancelled'
}

FE vs SQL: FE declined maps to SQL rejected; FE cancelled has no SQL equivalent.

Gamification

XP, badges, levels, and streaks. See Gamification guide for the full feature description.

// src/types/gamification.types.ts
interface Badge extends BaseEntity {
  name: string
  description: string
  iconUrl: string
  category: BadgeCategory  // milestone, category, breadth, streak, dc, etc.
  rarity: 'common' | 'uncommon' | 'rare' | 'epic' | 'legendary'
  criteria: Record<string, unknown>
}

FE-only domain

Gamification (XP, badges, levels, streaks, learning paths) has no backend tables. The mock API is the specification for the future backend implementation.

School

School administration hierarchy: School -> Classes -> Students/Teachers, with assignments and grade tracking. See School guide for the full feature description.

// src/types/school.types.ts
interface School extends BaseEntity {
  name: string
  organizationType: 'school' | 'district'
  status: 'active' | 'pilot' | 'suspended'
  settings: SchoolSettings
}

FE-Only Domains

These domains exist only in the frontend (mock API). No backend tables exist:

• COPPA compliance -- accountTier, isUnder13, parentalConsentStatus
• Gamification -- XP, badges, levels, streaks, learning paths, journey map
• Track Records (full domain) -- entries, media, reflections, finalization
• Communities -- all 6 types, feeds, goals, moderation
• School hierarchy -- schools, classes, teachers, assignments, grades
• Milestones -- structured milestone tracking
• Notifications -- categories, preferences, quiet hours
• Portfolio/Scrapbook -- templates, PDF export

SQL-Only Domains

These exist in the backend but have no FE representation:

• Wallet/balance system -- wallets table, users.balance
• Affiliate infrastructure -- tracking URLs, commission policies
• Payment processing -- payments/payouts tables
• Challenge instances -- per-session scheduling (partially modeled)
• Series ordering/pricing -- series_orders table

Common Patterns

BaseEntity

All entities extend BaseEntity, which provides id, createdAt, and updatedAt:

// src/types/common.types.ts
type UUID = string

interface BaseEntity {
  id: UUID
  createdAt: string  // ISO date string
  updatedAt: string  // ISO date string
}

API Response Types

All API calls return a consistent response structure:

interface ApiResponse<T> {
  success: boolean
  data: T
  error?: string
  message?: string
}

interface PaginatedResponse<T> {
  success: boolean
  data: T[]
  error?: string
  pagination: {
    page: number
    limit: number
    total: number
    totalPages: number
    hasMore: boolean
  }
}

Database Table Count

The full backend schema includes approximately 74 tables across these domains. Several of these now have modern Prisma models in server/prisma/schema.prisma -- see Database & Prisma for the full schema:

Domain	Tables	Notes
Core Platform	8	Users, orgs, notifications, audit
Challenges & TRs	4	Challenges, milestones, track records, verification
Gifting	2	Gifts, invitations
Explore & Discovery	7	Curated views, saved lists, events, search log
Communities	4	Communities, memberships, posts, share batches
School Admin	7	Classes, assignments, feedback, surveys
Reflection & SEL	5	Prompts, responses, settings, flags
Gamification	13	XP, badges, streaks, levels, paths, leaderboards
Admin & Analytics	8	Admin users, flags, moderation, dashboards
Security & Privacy	4	Consent, data requests, security events
Notifications	4	Email sends, push tokens, suppressions
Onboarding	3	Progress, feature education, empty states
Reporting & Flagging	4	Reports, blocks, mutes, appeals
Vendors	1	Vendor profiles