Schema Evolution: What Changed from the Original SQL

This page explains what we started with, what we built on top of it, and why. If you worked on the original MySQL database, this is your guide to understanding the current state.

STATUS: BUILT

The Prisma schema contains ~130 total models: ~40 legacy tables preserved from the original dump, plus ~90 modern models added for the new application layer. Both coexist in a single schema.

On This Page

Section	What's Covered
What We Started With	The original MySQL dump — 40 tables, bigint IDs, UPPER_CASE enums
Why We Added a Second Layer	What didn't align with the new product design
The Legacy Tables	Full list of original tables, preserved read-only
The Modern Models	All ~90 new models organized by domain
Status Enum Translation	How ACTIVE/INACTIVE/PENDING map to the frontend
Field Renames & Type Changes	Users, vendors, challenges — what got renamed and why
By the Numbers	Quick stats on the full schema
Next Steps: Legacy Cleanup	The plan to drop the dual-layer approach entirely

---

What We Started With

The original database was a MySQL dump (dump-docurious_prod-202602121304.sql) containing about 40 tables. It used bigint AUTO_INCREMENT primary keys, snake_case naming, and UPPER_CASE status enums like ACTIVE, INACTIVE, and PENDING.

We imported it into PostgreSQL 17 and introspected it with Prisma to generate the initial schema. The only modification to the legacy tables was adding a uuid column (via gen_random_uuid()) to the key tables — users, challenges, vendors, orders, etc. — so the frontend adapter layer could bridge between legacy bigint IDs and the UUID-based system the app uses.

Every original table is still in the schema, untouched. We read from them via a prodData service but don't write to them for new features.

Why We Added a Second Layer

The original schema was built for a different application architecture. Several things didn't align with the new product design:

• IDs: The frontend uses UUID strings everywhere, but the legacy tables use bigint auto-increment with uuid as a secondary field
• Roles: The original users table had 6 roles (USER, CHILD, VENDOR, ADMIN, MANAGER, EMPLOYEE). The new product needs 9 user roles plus 7 admin sub-roles
• Challenges: The original had a two-tier architecture — challenges (templates) and challenge_instances (specific sessions with schedule, location, pricing, seats). The new product treats challenges as flat, self-contained entities
• Status enums: Every domain used different conventions. Challenge statuses (ACTIVE/INACTIVE/PENDING) didn't map cleanly to the frontend's lifecycle states (draft/pending_review/approved/rejected/archived)
• Missing domains: Communities, track records, gamification (XP/badges), schools, learning paths, reflections, notifications, and content moderation didn't exist at all

Rather than heavily modify the production tables (which still have real data), we built a parallel set of modern models and an adapter layer that translates between the two worlds.

The Legacy Tables (~40)

These are the original tables from the MySQL dump. They're mapped via @@map() directives in Prisma:

Core entities: users, challenges, vendors, orders, coupons, categories, series

Challenge-related: challenge_instances, challenge_categories, challenge_sub_categories, challenge_tags, challenge_accessories, challenge_resources, challenge_variants, challenge_links, challenge_related_links, challenge_advertisements, challenge_expiry_dates, challenge_instance_prices, challenge_likes_dislikes, challenge_reviews, challenge_submissions, challenge_upload_media_and_documents, challenge_verification

Commerce: order_instances, coupon_usages, payments, payouts, wallets, series_orders

Users & Families: children, child_account_requests, coppa_verifications, favorite_categories, favorite_challenges, user_instances, user_challenge_activities, activity_series

Gifting: gift_challenges, gift_challenge_groups

Content & Structure: sub_categories, tags, sections, landing_page, media, addresses, physical_venues, virtual_venues, seat_types, policies, agreements

Infrastructure: migrations, failed_jobs, jobs, password_reset_tokens, personal_access_tokens, portfolio_without_orders

The Modern Models (~90)

All modern models use UUID string primary keys, camelCase field naming, lowercase status enums, proper foreign key relations with cascade deletes, and automatic createdAt/updatedAt timestamps.

Users & Auth

We replaced the legacy users table (for new features) with a more capable user system:

• AppUser — 9 roles (user, vendor, platform_admin, head_school_admin, school_admin, teacher, student, parent, staff) plus 7 admin sub-roles (super, content, school, vendor, support, analytics, engineering). Adds isUnder13, parentalConsentStatus, 2FA fields (totpSecret, totpEnabled, backupCodes), email verification, interests[] array, and a schoolId link
• AppUserProfile — separated profile data (firstName, lastName, phone, bio)
• AppUserSettings — per-user notification preferences
• RefreshToken — JWT refresh tokens with session metadata (userAgent, ipAddress, deviceType)

Challenges

The biggest structural change was flattening the two-tier challenge architecture into a single model:

• AppChallenge — merges what was challenges + challenge_instances. Adds fields the original didn't have: shortDescription, specificMission, soloGroup, locationType, costAmount/costCurrency/costDisplay, baseXp, completionCount, averageRating, ageMin/ageMax, coverImageUrl, galleryImageUrls[], videoUrl. Also has version control fields: firstStartedAt (triggers structural field locking), hasPendingEdit, supersededById, reviewNotes, approvedAt
• ChallengeEditHistory — version control audit trail tracking every edit with fieldsChanged, previousValues, newValues, editType (cosmetic vs structural), and admin review status
• AppCategory — clean categories with unique slug and displayOrder
• AppChallengeCategoryMapping — many-to-many join with a unique constraint
• ChallengeMilestone — ordered milestones within a challenge
• ChallengeVerificationReq — what proof users need to submit
• ChallengeDiscussion — threaded discussion posts on challenges

Track Records & Verification (entirely new)

This entire domain didn't exist in the original database:

• UserChallenge — links a user to a challenge with a full status lifecycle (in_progress → submitted → verified/abandoned), repeat completions, verification attempts, and a Dealer's Choice multiplier
• TrackRecord — a user's documentation of completing a challenge. Has sharing scope (private/public), a verification workflow with SLA deadlines, escalation, reviewer assignment, and storage tracking
• TrackRecordEntry — individual entries within a track record (text + media) with ordering, thumbs-up counts, and edit-after-verification tracking
• TrackRecordEntryMedia — photos and videos attached to entries
• TrackRecordEntryVote — thumbs-up votes (unique per user+entry)
• TrackRecordComment — threaded comments with edit deadlines, visibility control, and instructor feedback flags

Gamification (entirely new)

• XPEvent — every XP-earning action is logged with type, amount, and JSON metadata
• Badge — badge definitions across 7 categories with rarity, criteria rules, optional XP bonus, hidden badges
• UserBadge — earned badges per user (unique constraint prevents duplicates)

Communities (entirely new)

• Community — 6 community types with visibility, unique slugs, member/post counts
• CommunityMember — per-community roles (owner/admin/moderator/member) with status
• CommunityMembership — extended membership with granular permissions (canPost, canModerate, canInvite)
• CommunityAssignment — challenges assigned to communities with due dates
• CommunityGoal — group goals with targets, progress tracking, and date ranges
• GoalMilestone, GoalParticipation, GoalContribution — goal sub-entities

Feed System (entirely new)

• FeedItem — 3 feed types (discussion, bucket_list, track_record) with pinning and announcements
• FeedComment — threaded comments on feed items
• FeedLike — likes with unique constraint per user+item

School Domain (entirely new)

The original database had children and coppa_verifications tables but no school infrastructure:

• School — schools with district, address, and count fields
• SchoolClass — classes within schools
• StudentRoster / TeacherRoster — enrollment with suspension tracking and department assignment
• ClassAssignment / ClassAssignmentProgress — challenge assignments for classes with per-student progress
• Survey / SurveyCampaign — school surveys
• ChallengeRequest — teacher → admin challenge approval workflow
• GradeLevel — school-specific grade level definitions

Gifts & Invitations (modern replacements)

The original gift_challenges and gift_challenge_groups tables are still in the schema but aren't used for new data:

• Gift — clean model with proper sender/recipient foreign keys, expiry, accept/decline timestamps
• ChallengeInvitation — group invitations with payment approach, participant limits, and RSVP tracking
• InvitationRecipient — individual recipients with status

Learning Paths (entirely new)

• LearningPath — ordered sequences of challenges with enrollment tracking
• LearningPathCheckpoint — check-in points along the path
• UserPathProgress — user's current position (unique per user+path)

Events (entirely new)

• ChallengeEvent — scheduled events with capacity, registration deadlines, pricing, recurrence rules
• EventRegistration — user registrations (unique per user+event)

Reflections & SEL (entirely new)

• ReflectionPrompt — reflection questions tagged with SEL competencies and age groups, can be school-specific
• ReflectionResponse — user answers as JSON, linked to track records

Notifications (entirely new)

• Notification — in-app notifications with category, priority, action URLs, and read tracking
• NotificationPreferences — per-user settings with quiet hours and digest frequency

Content Moderation & Admin Audit (entirely new)

• ContentReport — user reports with auto-flagging, risk scores, and resolver tracking
• ImpersonationLog — admin impersonation audit trail
• AdminAction — general admin action log

Payments & Commerce (modern replacements)

• ModernOrder — clean order model replacing legacy orders for new transactions
• ModernCoupon — coupons with unique codes, usage limits, and challenge-specific targeting
• PaymentMethod, CheckoutSession, Transaction — full payment flow
• Portfolio — user portfolios with customizable JSON sections and templates

Vendor System (modern replacement)

• ModernVendor — replaces legacy vendors table for new data. Uses JSON fields for profile, contact, billing, tax info, and venues (instead of the legacy polymorphic physical_venues/virtual_venues tables). Has approval workflow and vetting checklist
• VendorDocument — uploaded vendor documents
• ModernVendorTeamMember — multi-user vendor teams
• VendorTeamMember — user-to-vendor role mapping (owner/manager/contributor)
• Review — user reviews on challenges with moderation status

Explore & Discovery (entirely new)

• SavedChallenge — bookmarked challenges
• DealersChoiceGame — random challenge card game with cooldown
• DCHistoryEntry — play history

User Data Management (entirely new)

• DeletionRequest — GDPR/account deletion workflow
• DataExportRequest — data export requests

Status Enum Translation

The adapter layer (src/adapters/statusMaps.ts) translates bidirectionally between legacy UPPER_CASE enums and modern lowercase values. Here are the key mappings:

Domain	Legacy SQL	Modern FE
Challenge status	ACTIVE	approved
	INACTIVE	draft
	PENDING	pending_review
	(none)	rejected, archived (new states)
Order status	UPCOMING	pending
	INPROGRESS	confirmed
	COMPLETED	fulfilled
	(none)	cancelled, refunded (new states)
Vendor status	ACTIVE	active
	INACTIVE	inactive
	BLOCKED / FREEZED	suspended (merged)
Difficulty	0 / 1 / 2 / 3	beginner / intermediate / advanced / expert
Gift status	rejected	declined (renamed)
Series status	ACTIVE	published
	INACTIVE	draft
Challenge type	HOSTED	hosted
	ONLINE / AUTONOMOUS	digitally_guided (merged)
	KIT	kit
	AFFILIATE	hosted (merged)

Additional mappings exist for challenge instance status, portfolio status, wallet status/type, payout status, vendor approval status, revenue type, and commission type. See Adapter Layer for the full implementation.

Field Renames & Type Changes

When modern models replaced legacy concepts, several fields were renamed or had their types changed:

Users

Concept	Legacy	Modern
Display name	username (varchar)	displayName (string)
Name	first_name / last_name	firstName / lastName (in AppUserProfile)
Avatar	avatar (varchar, relative path)	avatarUrl (string, full URL)
Verification	is_verified (int 0/1)	emailVerified (boolean)
Points	points (decimal)	Separate XPEvent model
Roles	6 values in role column	9 values + adminSubRole

Vendors

Concept	Legacy	Modern
Company name	title	companyName
Onboarding step	step (int)	onboardingStep (int)
Agreement	agreement_accepted_at	agreementSignedAt
Stripe	stripe_connected (int 0/1) + payment_id (varchar)	Part of vendor JSON profile
Social URLs	5 separate columns	Part of JSON profile field
Venues	Polymorphic physical_venues / virtual_venues tables	JSON fields physicalVenues / virtualVenues

Challenges

Concept	Legacy	Modern
Architecture	Two-tier: challenges → challenge_instances	Flat: single AppChallenge
Mission	mission (text)	specificMission (string)
Price	price (varchar!)	costAmount (float) + costCurrency + costDisplay
Duration	completion_time (decimal)	estimatedDuration (string)
Age range	lower_bound / upper_bound (bigint)	ageMin / ageMax (int)
Creator	user_id (bigint FK)	creatorUserId (UUID string)
Type	type (5 values)	fulfillmentType (3 values, consolidated)
Free flag	isFree (int 0/1)	isFree (boolean)

By the Numbers

Metric	Count
Original legacy tables	~40
New modern models	~90
Total models in schema	~130
API route files	23
Domain services	19
Backend tests	227 (all passing)
Status enum mappings	13 domains
Frontend adapters	10
User roles (legacy → modern)	6 → 9 + 7 sub-roles

Next Steps: Legacy Table Cleanup

PLANNED

The site is not yet live. Since there are no production users, we can safely consolidate to a single model layer with a one-time data migration — no downtime risk, no user impact.

Why consolidate now

The dual-layer approach was the right call when we were still building: it let us preserve the original production data while designing the modern schema alongside it. But now that the modern layer is complete (~90 models, 19 services, 227 tests), the legacy tables have become overhead:

• Developer confusion — two User models, two Order models, two Vendor models. New devs have to learn which one to use (answer: always the modern one)
• The adapter layer only exists to translate between two conventions — idAdapter, statusMaps, and the domain adapters all go away if there's only one convention
• The prodData service manually transforms snake_case to camelCase, bigint to string, and UPPER_CASE to lowercase. That entire service becomes unnecessary
• The schema file is ~2,400 lines when ~40% of it (the legacy models) isn't used for writes
• No production users means zero migration risk — we can rebuild cleanly without worrying about downtime or data loss

What data is worth migrating

Most of the legacy tables contain test data or Laravel infrastructure that we don't need. The real value is concentrated in a few tables:

Legacy Table	Real Data	Migrates To
vendors	Vendor profiles, Stripe account IDs (payment_id), approval history	ModernVendor
challenges + challenge_instances	Challenge content, pricing, locations, schedules	AppChallenge
categories / sub_categories	Category taxonomy (names, icons, hierarchy)	AppCategory
users	Any real user accounts (likely just test/admin accounts)	AppUser
series	Challenge bundles (if worth keeping)	LearningPath or custom model

Everything else — orders, wallets, payouts, gift_challenges, coupon_usages, plus the Laravel plumbing (jobs, failed_jobs, migrations, personal_access_tokens) — is either empty test data or framework infrastructure that PostgreSQL + Prisma doesn't need.

The cleanup plan

Phase 1: Migration script

• Write a one-time TypeScript script that reads from legacy tables and inserts into modern tables
• Map vendor data: vendors.title → ModernVendor.companyName, vendors.payment_id → Stripe integration, social URLs → JSON profile, etc.
• Flatten challenges: merge challenges + challenge_instances rows into AppChallenge records with proper field mapping
• Migrate categories: categories + sub_categories → AppCategory with slugs and display order
• Migrate any real user accounts into AppUser with proper role mapping

Phase 2: Remove legacy models

• Drop all ~40 legacy model definitions from server/prisma/schema.prisma
• Delete prodData.service.ts, its controller, and its routes
• Remove the catch-all route registration
• Run prisma db push to sync the cleaned schema

Phase 3: Remove the adapter layer

• Delete src/adapters/statusMaps.ts (status enum translation)
• Delete src/adapters/idAdapter.ts (uuid → id promotion)
• Delete or simplify domain adapters that exist solely for legacy translation (challenge.adapter.ts, vendor.adapter.ts, user.adapter.ts, order.adapter.ts, portfolio.adapter.ts, wallet.adapter.ts, series.adapter.ts, category.adapter.ts)
• Update any frontend code that imports from the adapter layer

Phase 4: Clean up routes and aliases

• Remove the v2 route aliases (/v2/challenges, /v2/my-challenges, etc.) or make them the primary routes
• Update server/README.md and API endpoint docs
• Update the schema evolution docs (this page) to reflect the completed migration

Result

After cleanup, the schema drops from ~130 models to ~90, the adapter layer is gone, every model follows the same convention (UUID, camelCase, lowercase enums), and there's a single source of truth for every entity. New developers won't need to learn two systems.

See Also

• Database & Prisma — schema overview, Prisma commands, ER diagram
• Adapter Layer — frontend-side SQL → FE type mapping
• Backend Architecture — server layers and patterns
• Roles & Permissions — the full modern role model
• API Endpoints — which endpoints use which models