pwa/docs/novya-database-architecture.md

Novya Database Architecture

This document implements the approved scalable database architecture for Novya using a Prisma-ready MySQL model.

Scope

• Backend target: Node.js + Hono
• Database target: MySQL (InnoDB, utf8mb4, UTC timestamps)
• ORM target: Prisma
• AI provider target: GapGPT via OpenAI-compatible runtime

Implemented Domains

• Identity and access
• Assessment engine (PCQ-24 ready, versioned)
• Program, lessons, and exercises
• User journey and progress tracking
• AI analysis and lesson-scoped chat
• Notifications, behavior telemetry, and admin snapshots

The concrete model is implemented in prisma/schema.prisma.

Domain Glossary and Lifecycle States

Assessment

• AttemptPhase: pre, post
• AttemptStatus: in_progress, submitted, scored, canceled

Learning Journey

• EnrollmentStatus: active, paused, completed, dropped
• UnlockReason: schedule, manual, prerequisite_completed, retry
• LessonProgressStatus: not_started, in_progress, completed, skipped
• SubmissionStatus: draft, submitted, reviewed

AI Runtime

• AiRunType: assessment_analysis, final_report, exercise_feedback, lesson_chat
• AiRunStatus: queued, running, succeeded, failed, canceled
• ChatRole: system, user, assistant, tool

Tables by Domain

Identity and Access

• organizations
• users
• auth_identities
• auth_sessions
• roles
• user_roles
• user_profiles
• consents
• user_consents

Assessment Domain

• assessment_scales
• assessment_scale_versions
• assessment_questions
• assessment_options
• assessment_attempts
• assessment_answers
• assessment_scores
• assessment_comparisons

Program Content

• programs
• program_versions
• lessons
• lesson_contents
• exercises
• exercise_prompts

User Journey and Progress

• user_program_enrollments
• user_lesson_unlocks
• user_lesson_progress
• user_exercise_submissions
• user_exercise_answers
• user_journey_events
• daily_activity_summaries

AI Runtime

• ai_providers
• ai_models
• ai_prompts
• ai_analysis_runs
• ai_analysis_outputs
• chat_sessions
• chat_messages
• ai_feedback

Notifications and Operations

• notification_templates
• notifications
• behavior_logs
• audit_logs
• admin_report_snapshots

Relationship Map

• users 1-1 user_profiles
• users 1-N auth_sessions
• users N-N roles via user_roles
• assessment_scales 1-N assessment_scale_versions 1-N assessment_questions 1-N assessment_options
• users 1-N assessment_attempts 1-N assessment_answers
• assessment_attempts 1-N assessment_scores
• assessment_comparisons references both pre and post attempts
• programs 1-N program_versions 1-N lessons 1-N exercises 1-N exercise_prompts
• users 1-N user_program_enrollments
• user_program_enrollments 1-N user_lesson_unlocks and 1-N user_lesson_progress
• chat_sessions references (user, enrollment, lesson) and chat_messages N-1 chat_sessions
• ai_analysis_runs references (user, enrollment, provider, model, prompt) and ai_analysis_outputs N-1 ai_analysis_runs
• ai_feedback references user and either lesson progress or exercise submission

ER Diagram (Text)

erDiagram
  users ||--|| user_profiles : has
  users ||--o{ auth_identities : links
  users ||--o{ auth_sessions : owns
  users ||--o{ user_roles : assigned
  roles ||--o{ user_roles : grants

  assessment_scales ||--o{ assessment_scale_versions : versions
  assessment_scale_versions ||--o{ assessment_questions : contains
  assessment_questions ||--o{ assessment_options : offers
  users ||--o{ assessment_attempts : submits
  assessment_attempts ||--o{ assessment_answers : has
  assessment_attempts ||--o{ assessment_scores : produces
  assessment_attempts ||--o| assessment_comparisons : preAttempt
  assessment_attempts ||--o| assessment_comparisons : postAttempt

  programs ||--o{ program_versions : versions
  program_versions ||--o{ lessons : contains
  lessons ||--o{ lesson_contents : renders
  lessons ||--o{ exercises : includes
  exercises ||--o{ exercise_prompts : asks

  users ||--o{ user_program_enrollments : enrolls
  user_program_enrollments ||--o{ user_lesson_unlocks : unlocks
  user_program_enrollments ||--o{ user_lesson_progress : tracks
  user_lesson_progress ||--o{ chat_sessions : scopes
  chat_sessions ||--o{ chat_messages : stores

  user_program_enrollments ||--o{ user_exercise_submissions : submits
  user_exercise_submissions ||--o{ user_exercise_answers : contains

  ai_providers ||--o{ ai_models : exposes
  ai_prompts ||--o{ ai_analysis_runs : usedBy
  ai_models ||--o{ ai_analysis_runs : runsOn
  users ||--o{ ai_analysis_runs : requestedFor
  ai_analysis_runs ||--o{ ai_analysis_outputs : emits
  user_exercise_submissions ||--o{ ai_feedback : receives

  users ||--o{ notifications : receives
  users ||--o{ behavior_logs : generates
  users ||--o{ audit_logs : affects

Implementation Notes

• High-write tables include indexed temporal access paths:
  • chat_messages(session_id, created_at)
  • behavior_logs(user_id, created_at)
  • assessment_attempts(user_id, phase, created_at)
• Content and assessments are versioned to preserve research reproducibility.
• AI output storage is append-only (ai_analysis_runs, ai_analysis_outputs) to support auditable research workflows.
• The schema keeps tenant-readiness via optional organization_id.