Database Schema Documentation

This document provides a comprehensive overview of the EI Mastery App database schema. The application uses PostgreSQL with Drizzle ORM for type-safe database operations.

Entity Relationship Diagram

Core Entities Overview

User Management

• users: Core user accounts with EI scoring and profile information
• user_sessions: Active session tracking with device and location info
• login_attempts: Security logging for authentication attempts

Learning Content

• modules: Learning modules organized by EI scales and categories
• lessons: Individual lessons within modules with interactive content
• user_progress: Tracks completion and scores for lessons/modules

Assessment System

• assessments: EI assessment definitions with questions and scoring
• user_assessments: Completed assessments with scores per EI scale
• assessment_progress: Temporary storage for in-progress assessments

Personal Development Tools

• journal_entries: Reflective journaling linked to EI scales
• goal_handprints: Goal-setting framework with finger-based goals
• mood_entries: Daily mood tracking with EI factors
• backward_glance_responses: Values assessment exercise

Gamification & Resources

• achievements: Achievement definitions with requirements
• user_achievements: Earned achievements per user
• resources: Educational content and materials
• podcasts: YouTube-based podcast episodes linked to EI scales

Key Design Patterns

JSONB Usage

The schema leverages PostgreSQL's JSONB type for flexible data structures:

• scale_scores: Dynamic EI scale scoring (Record<string, number>)
• questions: Assessment and lesson questions with options
• finger_goals: Complex goal structures with progress tracking
• responses: User assessment answers

EI Scale Integration

Multiple tables reference the 16 Jo Maddocks EI scales:

• modules.eq_scales[]: Which scales each module addresses
• journal_entries.eq_scale: Scale focus for reflective entries
• podcasts.eq_scales[]: Scales covered in podcast episodes

Temporal Tracking

Comprehensive timestamp tracking across entities:

• Creation timestamps (created_at)
• Update timestamps (updated_at)
• Completion timestamps (completed_at)
• Activity timestamps (last_activity, login_time)

Security & Auditing

• Session management with device/location tracking
• Login attempt logging with failure reasons
• User activity monitoring

---

This schema supports a comprehensive emotional intelligence learning platform with assessment, personalized content, progress tracking, and personal development tools.