Database & Prisma

The backend uses Prisma ORM to interact with a PostgreSQL database. The schema contains two distinct model layers: legacy models mapped from the original production database, and modern models designed for the new application features.

STATUS: PARTIAL

Legacy models (40+) provide read-only access to production data. Modern models (90+) support full CRUD across auth, challenges, track records, gamification, communities, schools, vendors, orders, and more. Some domain services still use in-memory storage and will migrate to Prisma models as development continues.

Overview

• Schema file: server/prisma/schema.prisma
• Database: PostgreSQL 17 (docurious_prod)
• Prisma client: Singleton in server/src/lib/prisma.ts (survives hot-reload in dev)

Legacy Models

These models were introspected from the original MySQL database (converted to PostgreSQL). They map 1:1 to the production SQL tables using @@map() directives.

Characteristics:

• BigInt primary keys (auto-increment)
• snake_case column names
• UPPER_CASE status enums (ACTIVE, INACTIVE, PENDING)
• uuid field as a secondary identifier (not the primary key)
• Soft deletes via deleted_at timestamp

Key legacy models:

Prisma Model	SQL Table	Description
User	users	User accounts (email, password, role, balance)
Challenge	challenges	Challenge templates (title, vendor, pricing)
Vendor	vendors	Vendor organizations (Stripe, commission)
Order	orders	Purchase transactions
Coupon	coupons	Discount codes
categories	categories	Challenge categories
series	series	Challenge series/bundles
wallets	wallets	User wallet transactions
payouts	payouts	Vendor payout records
gift_challenges	gift_challenges	Gift challenge records
challenge_instances	challenge_instances	Specific challenge sessions (location, schedule)
challenge_submissions	challenge_submissions	User submissions
children	children	Parent-child relationships
coppa_verifications	coppa_verifications	COPPA consent records

Used by: prodData.service.ts -- serves read-only production data through the root-level API routes (/challenges, /vendors, /users, /series, /wallet).

Modern Models

These models use UUID string primary keys, camelCase conventions, and @default(now())/@updatedAt timestamps. They power the modern application features.

Characteristics:

• UUID string primary keys (@id @default(uuid()))
• camelCase field names
• lowercase status enums (active, draft, pending_review)
• Cascade deletes via onDelete: Cascade

Entity Relationship Diagram

Key modern models

Model	Table	Description
AppUser	app_users	User accounts (email, role, tier, COPPA status)
AppUserProfile	app_user_profiles	Profile details (name, phone, bio)
AppUserSettings	app_user_settings	Notification preferences
RefreshToken	refresh_tokens	JWT refresh token storage + revocation
AppChallenge	app_challenges	Challenge definitions (difficulty, XP, cost, media)
AppCategory	app_categories	Challenge categories with slugs
ChallengeMilestone	challenge_milestones	Ordered steps within a challenge
ChallengeVerificationReq	challenge_verification_reqs	Proof requirements
ChallengeDiscussion	challenge_discussions	Threaded discussion posts
UserChallenge	user_challenges	User's challenge progress (status, attempts)
TrackRecord	track_records	Track record with verification status
TrackRecordEntry	track_record_entries	Individual entries with text + media
TrackRecordEntryMedia	track_record_entry_media	Attached photos/videos
TrackRecordEntryVote	track_record_entry_votes	Thumbs-up votes
TrackRecordComment	track_record_comments	Threaded comments on entries
XPEvent	xp_events	XP earning events with metadata
Badge	badges	Badge definitions (category, rarity, criteria)
UserBadge	user_badges	Badges earned by users

Dual Model Data Flow

Legacy and modern models coexist in the same database and Prisma client. The data flows differently depending on which path is used:

Legacy path: Legacy tables → prodData.service.ts transforms bigint IDs to strings, snake_case to camelCase, and UPPER_CASE enums to lowercase → Frontend adapters apply final mapping.

Modern path: Modern tables → Domain services query with Prisma → Response is already in the shape the frontend expects → No adapter transformation needed.

Prisma CLI Commands

Run these from the server/ directory:

Command	Description
npx prisma generate	Regenerate the Prisma client after schema changes
npx prisma db push	Push schema to database (no migration history)
npx prisma migrate dev	Create a migration and apply it
npx prisma studio	Open browser-based database GUI
npx prisma db seed	Run the seed script (prisma/seed.ts)
npx prisma format	Auto-format the schema file

INFO

During active development, db push is faster for iterating. Switch to migrate dev when you need migration history for production deployments.

See Also

• Backend Architecture -- server layers and patterns
• Adapter Layer -- frontend-side SQL → FE type mapping
• Data Model -- frontend type definitions and entity relationships
• API Endpoints -- which endpoints use which models