Skip to main content

​
Schema Overview

Evaly uses Convex’s built-in database with TypeScript schema validation. All schemas are defined in convex/schemas/ and exported from convex/schema.ts.

​
Core Entities

​
Multi-Tenancy

Organizations are the top-level multi-tenancy container.Fields:
  • name - Organization name
  • image - Optional organization logo URL
  • type - Organization type identifier
  • deletedAt - Soft delete timestamp
Relationships:
  • Has many organizer (members)
  • Has many test
  • Has one organizationPlan
  • Has one organizationUsage
Organizers are members of an organization with test management permissions.Fields:
  • userId - Reference to users table
  • organizationId - Reference to organization
  • role - Member role (owner, admin, member)
  • deletedAt - Soft delete timestamp
Indexes:
  • by_user_id
  • by_organization_id
Token-based invitation system for adding members.Fields:
  • organizationId - Reference to organization
  • email - Invitee email address
  • token - Unique invitation token
  • role - Invited role
  • invitedBy - Reference to inviter’s organizer ID
  • expiresAt - Token expiration (7 days from creation)
  • acceptedAt - Optional acceptance timestamp
  • deletedAt - Soft delete timestamp
Indexes:
  • by_token
  • by_organization_id
  • by_email

​
Subscription & Usage

Subscription plan configuration for each organization.Fields:
  • organizationId - Reference to organization
  • plan - Plan type: "free" | "pro" | "max"
  • customLimits - Optional custom limit overrides
    • aiQuestionGeneration - Monthly AI question generation limit
    • aiTranslation - Monthly AI translation limit
    • aiOptions - Monthly AI options generation limit
    • aiAnalysis - Monthly AI analysis limit
    • results - Monthly test results limit
    • members - Team members limit
    • activeTests - Concurrent active tests limit
    • concurrentParticipants - Concurrent participants limit
  • createdAt - Plan creation timestamp
  • updatedAt - Plan update timestamp
Indexes:
  • by_organization
Tracks monthly usage against plan limits.Fields:
  • organizationId - Reference to organization
  • month - Usage month (YYYY-MM format)
  • aiQuestionGeneration - AI questions generated this month
  • aiTranslation - AI translations this month
  • aiOptions - AI options generated this month
  • aiAnalysis - AI analyses this month
  • results - Test results generated this month
  • updatedAt - Last update timestamp
Indexes:
  • by_organization_month

​
Test Structure

Core test/exam configuration.Fields:
  • title - Test title
  • description - Optional test description
  • organizationId - Reference to organization
  • createdByOrganizerId - Reference to creator’s organizer ID
  • access - Access control: "public" | "private"
  • isPublished - Publication status
  • heldAt - Optional test date/location
  • deletedAt - Soft delete timestamp
Scheduling:
  • scheduledStartAt - Optional start timestamp
  • scheduledEndAt - Optional end timestamp
  • activationJobId - Scheduler job ID for activation
  • finishJobId - Scheduler job ID for finish
Time Management:
  • pausedAt - Pause timestamp (if paused)
  • totalPausedDuration - Total time paused (milliseconds)
  • useSectionDurations - Whether to use section-based durations
  • finishedAt - Test completion timestamp
  • stoppedReason - Optional stop/cancel reason
Access Control:
  • password - Optional password protection
  • allowedEmailDomains - Optional email domain whitelist
  • allowedIpAddresses - Optional IP address whitelist
Settings:
  • randomizeQuestions - Randomize question order
  • randomizeAnswers - Randomize answer options
  • resultsReleased - Whether participants can view results
Deprecated:
  • type - Old type field (use access instead)
  • showResultImmediately - Old results field (use resultsReleased)
Indexes:
  • by_organization_id
Sections organize questions within a test.Fields:
  • testId - Reference to test
  • title - Section title
  • description - Optional section description
  • order - Display order
  • duration - Optional duration in minutes (for timed sections)
  • deletedAt - Soft delete timestamp
Indexes:
  • by_test_id
Individual questions within sections or libraries.Fields:
  • question - Question text (rich text HTML)
  • referenceId - Section or library ID (string, not typed ID)
  • originalReferenceId - Original library ID (for duplicated questions)
  • organizationId - Reference to organization
  • order - Display order
  • type - Question type:
    • "multiple-choice"
    • "yes-or-no"
    • "image-choice"
    • "audio-choice"
    • "text-field"
    • "file-upload"
    • "fill-the-blank"
    • "audio-response"
    • "video-response"
    • "matching-pairs"
    • "slider-scale"
    • "likert-scale"
    • "ranking"
  • pointValue - Optional point value for scoring
  • allowMultipleAnswers - Whether multiple answers are allowed
  • options - Answer options array (for choice questions)
    • id - Option ID
    • text - Option text
    • isCorrect - Correct answer flag
    • mediaUrl - Optional media URL
    • mediaType - Optional media type
    • pointValue - Optional partial credit
  • settings - Type-specific settings (see below)
  • deletedAt - Soft delete timestamp
Type-Specific Settings:
  • Text Field: placeholderText, minCharacterLimit, maxCharacterLimit
  • Audio/Video: maxDurationSeconds
  • Likert Scale: likertScalePoints, likertLabels
  • Slider Scale: sliderMin, sliderMax, sliderStep, sliderMinLabel, sliderMaxLabel, sliderShowValue
  • Ranking: rankingScoringType ("exact" | "per-position")
Indexes:
  • by_reference_id
Reusable question banks for organizing questions.Fields:
  • name - Library name
  • description - Optional library description
  • organizationId - Reference to organization
  • deletedAt - Soft delete timestamp
Indexes:
  • by_organization_id
Questions reference libraries via referenceId field (string format, not typed ID).

​
Participant Data

Individual participants allowed to take a test.Fields:
  • testId - Reference to test
  • email - Participant email
  • addedAt - Timestamp when added
  • deletedAt - Soft delete timestamp
Indexes:
  • by_test_id
  • by_email
  • by_test_id_email
User groups allowed to take a test.Fields:
  • testId - Reference to test
  • userGroupId - Reference to userGroup
  • addedAt - Timestamp when added
  • deletedAt - Soft delete timestamp
Indexes:
  • by_test_id
  • by_user_group_id
Groups for organizing participants.Fields:
  • name - Group name
  • organizationId - Reference to organization
  • deletedAt - Soft delete timestamp
Indexes:
  • by_organization_id
Members of user groups.Fields:
  • userGroupId - Reference to userGroup
  • email - Member email
  • addedAt - Timestamp when added
  • deletedAt - Soft delete timestamp
Indexes:
  • by_user_group_id
  • by_email
Participant attempts at test sections.Fields:
  • testId - Reference to test
  • testSectionId - Reference to testSection
  • participantId - Reference to users
  • startedAt - Attempt start timestamp
  • finishedAt - Optional completion timestamp
  • deletedAt - Soft delete timestamp
Indexes:
  • by_participant_test_section
  • by_participant_test
  • by_test
Each section of a test creates a separate attempt record.
Individual answers to questions within attempts.Fields:
  • testAttemptId - Reference to testAttempt
  • testId - Reference to test
  • testSectionId - Reference to testSection
  • questionId - Reference to question
  • answerText - Optional text answer
  • answerOptions - Optional selected option IDs
  • answerMediaUrl - Optional uploaded media URL
  • answerMediaMetadata - Optional media metadata
    • extension - File extension
    • size - File size in bytes
    • name - Original filename
    • durationMs - Optional duration for audio/video
  • isFlagged - Whether answer is flagged for review
  • deletedAt - Soft delete timestamp
Indexes:
  • by_attempt_and_question
  • by_attempt
  • by_section
  • by_test
Session tracking for participants.Fields:
  • testId - Reference to test
  • participantId - Reference to users
  • sessionToken - Unique session token
  • startedAt - Session start timestamp
  • lastActivityAt - Last activity timestamp
  • deletedAt - Soft delete timestamp
Indexes:
  • by_test_id
  • by_participant_id
  • by_session_token

​
Grading & Results

Manual grading records for subjective questions.Fields:
  • testAttemptId - Reference to testAttempt
  • questionId - Reference to question
  • participantId - Reference to users
  • testId - Reference to test
  • pointsAwarded - Points awarded for this answer
  • feedback - Optional grader feedback
  • gradedBy - Reference to grader’s organizer ID
  • gradedAt - Grading timestamp
  • deletedAt - Soft delete timestamp
Indexes:
  • by_test_attempt
  • by_question
  • by_participant
  • by_test
Used for manual grading of text fields, file uploads, and other subjective question types.

​
Activity & Notifications

Audit log for test events.Fields:
  • testId - Reference to test
  • organizationId - Reference to organization
  • eventType - Event type (e.g., "test_started", "test_paused", "participant_joined")
  • participantId - Optional participant ID
  • organizerId - Optional organizer ID
  • metadata - Optional event-specific data
  • timestamp - Event timestamp
Indexes:
  • by_test_id
  • by_organization_id
  • by_event_type
Individual notification events (batched for display).Fields:
  • organizationId - Reference to organization
  • type - Notification type
  • batchKey - Batching key for grouping
  • testId - Optional test ID
  • testName - Optional test name
  • participantId - Optional participant ID
  • participantName - Optional participant name
  • participantEmail - Optional participant email
  • metadata - Optional additional data
    • sectionName
    • reason
    • limit
    • current
    • memberName
    • memberEmail
  • createdAt - Notification timestamp
Indexes:
  • by_organization
  • by_batch_key
  • by_organization_created
Grouped notifications for display.Fields:
  • organizationId - Reference to organization
  • batchKey - Unique batch key
  • type - Notification type
  • count - Number of events in batch
  • latestEventAt - Latest event timestamp
  • firstEventAt - First event timestamp
Indexes:
  • by_organization
  • by_batch_key
Per-organizer read status tracking.Fields:
  • organizerId - Reference to organizer
  • batchKey - Reference to notification batch
  • readAt - Read timestamp
Indexes:
  • by_organizer
  • by_batch_key
  • by_organizer_batch
Per-organizer notification preferences.Fields:
  • organizerId - Reference to organizer
  • Individual preference flags for each notification type
Indexes:
  • by_organizer

​
AI Features

AI conversation threads for question generation.Fields:
  • organizationId - Reference to organization
  • organizerId - Reference to organizer
  • referenceId - Section or library ID
  • createdAt - Thread creation timestamp
  • deletedAt - Soft delete timestamp
Indexes:
  • by_organization_id
  • by_reference_id
Messages within AI threads.Fields:
  • threadId - Reference to aiThreads
  • role - Message role: "user" | "assistant" | "system"
  • content - Message content
  • createdAt - Message timestamp
Indexes:
  • by_thread_id

​
Authentication

User accounts (managed by Convex Auth).Fields:
  • Standard Convex Auth fields
  • selectedOrganizationId - Current organization context
  • selectedOrganizerId - Current organizer context
Extended from Convex Auth’s base user schema.
Password reset token management.Fields:
  • userId - Reference to users
  • token - Unique reset token
  • expiresAt - Token expiration timestamp
  • usedAt - Optional usage timestamp
Indexes:
  • by_token
  • by_user_id
Email verification token management.Fields:
  • userId - Reference to users
  • token - Unique verification token
  • expiresAt - Token expiration timestamp
  • verifiedAt - Optional verification timestamp
Indexes:
  • by_token
  • by_user_id

​
Schema Patterns

​
Soft Deletion

Most tables include a deletedAt field for soft deletion: 
// Querying (exclude deleted)
.filter((q) => q.lte(q.field("deletedAt"), 0))
// or
.filter((q) => q.eq(q.field("deletedAt"), undefined))

​
Ordering

Many collections have an order field for manual sorting:
  • testSection.order
  • question.order
  • User-controlled ordering via drag-and-drop

​
Reference IDs

Questions use flexible string-based references:
  • referenceId - Points to section ID or library ID
  • originalReferenceId - Tracks source when duplicated from library

​
Timestamps

  • _creationTime - Automatic Convex timestamp
  • Custom timestamps: startedAt, finishedAt, createdAt, updatedAt

​
Indexes

Indexes optimize query performance:
  • Single field: by_organization_id, by_test_id
  • Compound: by_organization_created, by_participant_test_section
  • Unique lookups: by_token, by_session_token
Always use indexes when querying large tables. Queries without indexes scan all records.

Build docs developers (and LLMs) love

Get started for freeTalk to us