Data Model

Purpose: Document the Juniro API database schema and entity relationships Last Updated: February 2026 Source of Truth: juniro-api/prisma/schema.prisma

---

Overview

Juniro uses PostgreSQL with Prisma ORM. The data model supports:

• Multi-region deployment (US and India with separate databases)
• Multi-staff provider accounts (owner, admin, staff roles)
• Flexible pricing (offerings layer between activities and sessions)
• Geography normalization (admin-controlled country/city launch markets)
• Messaging (parent ↔ provider conversations, feature-flagged)
• Feature flags (global toggles for gradual rollout)
• Idempotency (double-submit protection for payments and mutations)

---

Entity Relationship Diagram

---

Domain Groups

Geography (Admin-Controlled)

Controls which markets Juniro operates in. All geography data is admin-seeded.

Table	Purpose
Country	Countries where Juniro operates (US, India)
City	Cities within countries (launch markets)
Locality	Neighborhoods within cities (optional granularity)

Country (US, India)
  └── City (Austin, Bangalore, ...)
       └── Locality (Downtown, Koramangala, ...)

Users & Authentication

Table	Purpose
User	Base user record, linked to Supabase Auth
Parent	Parent profile (PII: name, phone)
Child	Children profiles (PII: name, birthdate)
ParentInterest	Category preferences per parent/child

Key Fields:

• User.authId - Supabase Auth user ID
• User.homeRegion - 'us' or 'in' (immutable after creation)
• User.role - 'parent', 'provider', or 'admin'

Providers (Multi-Staff)

Table	Purpose
ProviderAccount	Business entity (can have multiple staff)
ProviderUser	Join table linking users to provider accounts
Location	Physical locations owned by provider

Provider Roles:

• owner - Full access, can manage staff
• admin - Full access, cannot transfer ownership
• staff - Limited access (view/create, no delete)

Lookup Pattern:

// Find provider for authenticated user
const providerUser = await db.providerUser.findFirst({
  where: { userId: authUser.id },
  include: { providerAccount: true }
})
const provider = providerUser?.providerAccount

Activities & Offerings

Table	Purpose
Category	Activity categories (see Category Structure below)
Activity	What the provider offers (Soccer Camp, Piano Lessons)
Offering	Pricing packages for an activity
Session	Specific time slots that can be booked

Offering Types:

• single_session - One-time class
• multi_session - Package of N sessions
• camp - Multi-day camp
• term - Semester enrollment

Hierarchy:

Activity (Soccer Skills Camp)
  └── Offering (Drop-in: $25, 4-Week Package: $80)
       └── Session (Feb 1 10am, Feb 8 10am, ...)

Category Structure:

Activities
├── Swimming
├── Dance
├── Art
├── Music
├── Sports
├── STEM
├── Martial Arts
└── Language

Camps
├── Day Camps
├── Summer Camps
└── Sports Camps

Childcare & Early Education
├── Daycare Centers
├── Preschools
├── Montessori
├── Pre-K Programs
└── After-School Care

Parties & Events
├── Entertainers
├── Party Places
└── Mobile Services

Wellness (Phase 2)
├── Pediatric Dentists
├── Therapists
└── Specialists

Medical (Phase 3)
├── Pediatricians
├── Clinics
└── Children's Hospitals

Bookings & Payments

Table	Purpose
Booking	Parent books child into a session
Payment	Payment records (Stripe/Razorpay)
Review	Post-activity reviews

Booking Status Flow:

pending_payment → confirmed → completed
                     ↓
                cancelled / no_show / expired

Payment Status Flow:

created → pending → paid
              ↓
           failed → (retry) → paid
              ↓
           refunded

Messaging (Feature-Flagged)

Parent ↔ Provider conversations. Controlled by the MESSAGING_ENABLED feature flag.

Table	Purpose
Conversation	Thread between a parent and a provider (optionally linked to a booking)
Message	Individual message within a conversation

Key Design Decisions:

• One conversation per parent–provider pair (@@unique([parentId, providerId]))
• Conversations can be optionally linked to a specific Booking
• Read tracking is split: parentReadAt and providerReadAt (each side tracks independently)
• Messages support attachments via a JSON array ({ url, type, name })

Conversation Status Flow:

active → archived
   ↓
blocked

Feature Flags

Global boolean toggles for gradual feature rollout. Admin-managed.

Table	Purpose
FeatureFlag	Key-value toggle (e.g., MESSAGING_ENABLED, AI_SEARCH_ENABLED)

Usage Pattern:

const flag = await db.featureFlag.findUnique({ where: { key: 'MESSAGING_ENABLED' } });
if (!flag?.value) throw new ForbiddenError('Messaging is not enabled');

Idempotency

Prevents double-submit on mutations (especially payments). Keys auto-expire.

Table	Purpose
IdempotencyKey	Maps a client-generated key to a cached response

How It Works:

1. Client sends a unique Idempotency-Key header
2. Server checks if key exists → returns cached response if so
3. Otherwise, processes the request and stores the result
4. A cleanup job purges expired keys (indexed on expiresAt)

---

Key Tables Detail

User

Column	Type	Description
id	UUID	Primary key
authId	String	Supabase Auth ID (unique)
email	String	User email
homeRegion	String	'us' or 'in' - immutable
countryId	UUID?	FK to Country
role	String	'parent', 'provider', 'admin'

ProviderAccount

Column	Type	Description
id	UUID	Primary key
businessName	String	Display name
slug	String	URL-safe identifier (unique)
type	String	'business' or 'individual'
status	String	'pending', 'verified', 'suspended', 'rejected'
verifiedAt	DateTime?	When admin verified

Activity

Column	Type	Description
id	UUID	Primary key
providerId	UUID	FK to ProviderAccount
title	String	Activity title
slug	String	URL-safe (unique per provider)
categoryId	UUID	FK to Category
ageMin / ageMax	Int?	Age range
duration	Int?	Minutes
status	String	'draft', 'published', 'archived'

Offering

Column	Type	Description
id	UUID	Primary key
activityId	UUID	FK to Activity
type	String	'single_session', 'multi_session', 'camp', 'term'
name	String?	Display name (e.g., "4-Week Package")
priceCents	Int	Price in cents
currency	String	'USD' or 'INR'
sessionCount	Int?	For multi_session type
status	String	'active' or 'inactive'

Session

Column	Type	Description
id	UUID	Primary key
activityId	UUID	FK to Activity
offeringId	UUID	FK to Offering
locationId	UUID	FK to Location
startsAt	DateTime	Session start time
endsAt	DateTime	Session end time
capacity	Int	Max attendees
enrolled	Int	Current enrollment count
status	String	'scheduled', 'cancelled', 'completed'

Booking

Column	Type	Description
id	UUID	Primary key
sessionId	UUID	FK to Session
offeringId	UUID	FK to Offering
parentId	UUID	FK to Parent
childId	UUID	FK to Child
status	String	See status flow above
expiresAt	DateTime?	For pending_payment timeout

Location

Column	Type	Description
id	UUID	Primary key
providerId	UUID	FK to ProviderAccount
name	String	Location name
address	String	Street address
cityId	UUID	FK to City
localityId	UUID?	FK to Locality (optional)
latitude / longitude	Float?	Coordinates
isDefault	Boolean	Default location for provider

Payment

Column	Type	Description
id	UUID	Primary key
bookingId	UUID	FK to Booking
amountCents	Int	Amount in smallest currency unit (cents/paise)
currency	String	'USD' or 'INR'
processor	String	'stripe' (US) or 'razorpay' (India)
processorPaymentId	String?	External payment ID from processor
processorCheckoutId	String?	External checkout/session ID
status	String	See Payment Status Flow above
statusReason	String?	Human-readable reason for status change
idempotencyKey	String?	Unique key for double-submit protection
paidAt	DateTime?	Timestamp when payment succeeded
failedAt	DateTime?	Timestamp when payment failed
refundedAt	DateTime?	Timestamp when refund completed

Review

Column	Type	Description
id	UUID	Primary key
bookingId	UUID	FK to Booking (unique — one review per booking)
parentId	UUID	FK to Parent
rating	Int	1–5 star rating (CHECK constraint: 1 ≤ rating ≤ 5)
comment	String?	Parent's written review
response	String?	Provider's response to the review
respondedAt	DateTime?	When the provider responded

Conversation

Column	Type	Description
id	UUID	Primary key
parentId	UUID	FK to Parent
providerId	UUID	FK to ProviderAccount
bookingId	UUID?	FK to Booking (optional context)
subject	String?	Conversation subject line
status	String	'active', 'archived', 'blocked'
lastMessageAt	DateTime?	Timestamp of most recent message
parentReadAt	DateTime?	When the parent last read
providerReadAt	DateTime?	When the provider last read

Message

Column	Type	Description
id	UUID	Primary key
conversationId	UUID	FK to Conversation
senderId	UUID	FK to User (parent or provider staff)
senderType	String	'parent' or 'provider'
content	String	Message body text
attachments	Json?	Array of { url, type, name }
readAt	DateTime?	When the recipient read the message

FeatureFlag

Column	Type	Description
id	UUID	Primary key
key	String	Unique flag name (e.g., 'MESSAGING_ENABLED')
value	Boolean	Whether the feature is on or off
description	String?	Human-readable explanation

IdempotencyKey

Column	Type	Description
key	String	Primary key — client-generated unique key
userId	String	User who made the request
response	Json	Cached response body
expiresAt	DateTime	Auto-expiry timestamp (indexed for cleanup)

---

Indexes & Constraints

Unique Constraints

Table	Constraint
User	authId
ProviderAccount	slug
Activity	(providerId, slug)
City	(countryId, slug)
Locality	(cityId, slug)
ProviderUser	(providerAccountId, userId)
Review	bookingId (one review per booking)
ParentInterest	(parentId, categoryId, childId)
Conversation	(parentId, providerId) (one thread per pair)
Payment	idempotencyKey
FeatureFlag	key
Category	name, slug

Important Indexes

Table	Index	Purpose
Payment	processorPaymentId	Webhook lookup
IdempotencyKey	expiresAt	Cleanup job
Conversation	parentId	Parent inbox query
Conversation	providerId	Provider inbox query
Message	conversationId	Thread message listing
Message	senderId	User message history

---

Data Integrity Rules

Business Rules (Enforced in Code)

1. Booking capacity: session.enrolled <= session.capacity
2. Review eligibility: Only completed bookings can be reviewed
3. One review per booking: Unique constraint on Review.bookingId
4. Provider response: One response per review
5. Age validation: activity.ageMin <= activity.ageMax
6. Session timing: session.startsAt < session.endsAt
7. Rating range: 1 <= review.rating <= 5 (CHECK constraint)
8. Messaging gate: Conversations can only be created when MESSAGING_ENABLED feature flag is true
9. One conversation per pair: Unique constraint on (parentId, providerId) — new messages go into existing thread
10. Idempotency expiry: Keys auto-expire; cleanup job deletes rows where expiresAt < now()

Cascade Deletes

Parent	Child	Behavior
User	Parent	CASCADE
Parent	Child	CASCADE
Parent	ParentInterest	CASCADE
ProviderAccount	ProviderUser	CASCADE
ProviderAccount	Location	CASCADE
ProviderAccount	Activity	CASCADE
Activity	Offering	CASCADE
Activity	Session	CASCADE
Conversation	Message	CASCADE

---

Enum & Status Values Reference

All status and type fields are stored as strings in the database (not Prisma enums) for flexibility. This table is the single reference for all allowed values.

Entity Statuses

Table	Field	Allowed Values	Notes
User	role	parent, provider, admin	Set at signup
User	homeRegion	us, in	Immutable after creation
ProviderAccount	status	pending, verified, suspended, rejected	Admin-controlled
ProviderAccount	type	business, individual	Set at signup
ProviderUser	role	owner, admin, staff	Per-staff permissions
Activity	status	draft, published, archived	Provider-controlled
Offering	type	single_session, multi_session, camp, term	Determines pricing model
Offering	status	active, inactive	Provider-controlled
Session	status	scheduled, cancelled, completed	Lifecycle
Booking	status	pending_payment, confirmed, cancelled, completed, no_show, expired	See Booking Status Flow
Payment	status	created, pending, paid, failed, refunded	See Payment Status Flow
Payment	processor	stripe, razorpay	Region-dependent
Payment	currency	USD, INR	Region-dependent
Offering	currency	USD, INR	Region-dependent
Conversation	status	active, archived, blocked	Messaging lifecycle
Message	senderType	parent, provider	Who sent the message
Review	rating	1, 2, 3, 4, 5	Integer, CHECK constraint

Boolean Flags

Table	Field	Default	Purpose
Country	isActive	false	Admin enables launch markets
City	isActive	false	Admin enables launch cities
Locality	isActive	false	Admin enables neighborhoods
Category	isActive	true	Toggle category visibility
Location	isDefault	false	Provider's primary location
FeatureFlag	value	false	Global feature toggle

PII Classification

Fields containing Personally Identifiable Information (PII) — these must never be replicated across regions.

Table	PII Fields	Sensitivity
User	email	Medium
Parent	firstName, lastName, phone	High
Child	firstName, lastName, birthDate	High
Location	address, latitude, longitude	Medium
Payment	processorPaymentId, processorCheckoutId	High
Message	content, attachments	High

---

Multi-Region Considerations

Each region (US, India) has its own database with identical schema.

Never replicate across regions:

• User PII (name, phone, address)
• Child data
• Payment details

Can share (read-only):

• Category definitions
• Non-PII activity metadata

User assignment:

• homeRegion is set at signup based on location
• Users can only write to their home region
• Cross-region reads allowed for public data

---

Schema Evolution

Adding New Tables

1. Add model to prisma/schema.prisma
2. Run npm run db:migrate to create migration
3. Update relevant route files
4. Update this documentation

Migration Best Practices

• Always use migrations (never db push in production)
• Make columns nullable first, then backfill, then make required
• Add indexes in separate migration from data changes
• Test migrations on staging before production

---

References

• Prisma Schema
• API Architecture
• Multi-Region Architecture