MongoDB Database Schema

Overview

MongoDB schema design for ImpactPulse, optimized for flexibility, performance, and scalability.

Collections

users

{
  _id: ObjectId("..."),
  email: "user@example.com", // unique index
  password_hash: "bcrypt_hash",
  full_name: "John Doe",

  // Email & Auth
  email_verified: false,
  email_verification_token: "token",
  email_preferences: {
    reminders: true,
    updates: true,
    reports: false
  },

  // Activity
  created_at: ISODate("2024-01-15T10:00:00Z"),
  last_login_at: ISODate("2024-01-15T10:00:00Z"),

  // Organizations (denormalized for performance)
  organization_memberships: [
    ObjectId("org_id_1"),
    ObjectId("org_id_2")
  ],

  // Stats
  total_surveys_taken: 5,
  last_survey_at: ISODate("2024-01-15T10:00:00Z"),

  // Metadata
  timezone: "America/New_York",
  locale: "en_US",
  metadata: {}
}

organizations

{
  _id: ObjectId("..."),
  name: "Acme Corporation",
  slug: "acme-corp", // unique index
  description: "Leading innovation company",

  // Ownership
  created_by_id: ObjectId("user_id"),
  created_at: ISODate("2024-01-15T10:00:00Z"),

  // Subscription
  subscription_tier: "pro", // free, pro, enterprise
  subscription_expires_at: ISODate("2025-01-15T10:00:00Z"),

  // Settings
  settings: {
    allow_anonymous: false,
    max_surveys: 100,
    max_responses_per_survey: 1000,
    features: ["advanced_analytics", "api_access"],
    branding: {
      logo_url: "https://...",
      primary_color: "#0066CC"
    }
  },

  // Stats
  member_count: 25,
  survey_count: 10,
  total_responses: 250,

  // Metadata
  industry: "Technology",
  size: "50-100",
  country: "US",
  metadata: {}
}

organization_members

{
  _id: ObjectId("..."),
  organization_id: ObjectId("org_id"), // compound index with user_id
  user_id: ObjectId("user_id"),

  // Role & Permissions
  role: "admin", // owner, admin, member
  permissions: ["view_analytics", "manage_surveys"],

  // Activity
  joined_at: ISODate("2024-01-15T10:00:00Z"),
  invited_by_id: ObjectId("inviter_id"),
  last_active_at: ISODate("2024-01-15T10:00:00Z"),

  // Status
  is_active: true,
  deactivated_at: null,

  // Stats
  surveys_taken: 5,
  surveys_assigned: 8,
  completion_rate: 0.625
}

organization_invitations

{
  _id: ObjectId("..."),
  organization_id: ObjectId("org_id"),

  // Invitation Details
  email: "invitee@example.com", // index
  token: "secure_random_token", // unique index
  role: "member",

  // Tracking
  invited_by_id: ObjectId("admin_id"),
  created_at: ISODate("2024-01-15T10:00:00Z"),
  expires_at: ISODate("2024-01-22T10:00:00Z"), // 7 days

  // Status
  status: "pending", // pending, accepted, expired, cancelled
  accepted_at: null,
  accepted_by_user_id: null,

  // Custom Message
  custom_message: "Welcome to our team!",

  // Metadata
  invitation_source: "dashboard",
  metadata: {}
}

surveys

{
  _id: ObjectId("..."),
  title: "Q1 2024 Maslow Assessment",
  description: "Quarterly team wellbeing check",
  type: "maslow", // maslow, custom, feedback

  // Ownership
  created_by_id: ObjectId("user_id"),
  organization_id: ObjectId("org_id"), // index

  // Access Control
  public_id: "surv_abc123", // unique index for public access
  is_active: true,
  is_published: true,
  is_organization_only: true,

  // Multi-attempt Settings
  allow_multiple_attempts: true,
  max_attempts: null, // null = unlimited
  attempts_time_restriction: {
    enabled: true,
    minimum_days_between: 30
  },

  // Reminder Settings
  reminder_settings: {
    enabled: true,
    schedule: [24, 48, 168], // hours after start
    custom_message: "Don't forget to complete your assessment"
  },

  // Access Control
  access_control: {
    require_login: true,
    allowed_domains: ["acme.com"],
    invitation_only: false,
    assigned_users: [] // if invitation_only
  },

  // Timestamps
  created_at: ISODate("2024-01-15T10:00:00Z"),
  updated_at: ISODate("2024-01-15T10:00:00Z"),
  published_at: ISODate("2024-01-15T10:00:00Z"),

  // Stats
  total_responses: 150,
  completion_rate: 0.75,
  average_time_seconds: 420,

  // Scoring Summary (precomputed from question scoring_config values)
  scoring_summary: {
    max_possible_score: 40,       // sum of max_points for all scored questions
    scored_question_count: 4,     // number of questions with scoring (excludes qualitative)
    qualitative_question_count: 1 // number of qualitative (unscored) questions
  },

  // Metadata
  tags: ["quarterly", "wellbeing"],
  category: "hr",
  metadata: {}
}

questions

{
  _id: ObjectId("..."),
  survey_id: ObjectId("survey_id"), // index

  // Question Details
  text: "How satisfied are you with your work-life balance?",
  type: "rating", // rating, text, boolean, multiple_choice, checkbox

  // Display
  order: 1, // compound index with survey_id
  section: "Work Environment",

  // Validation
  required: true,
  validation: {
    min: 1,
    max: 10,
    pattern: null
  },

  // Options (for multiple choice/checkbox)
  options: [
    {
      value: "very_satisfied",
      label: "Very Satisfied",
      order: 1
    }
  ],

  // Maslow Categorization
  category: "safety", // physiological, safety, love_belonging, esteem, self_actualization
  weight: 1.0, // for scoring

  // Conditional Logic
  conditional_logic: {
    show_if: {
      question_id: ObjectId("..."),
      operator: "equals",
      value: "yes"
    }
  },

  // Scoring Configuration (see scoring-system.md for details)
  scoring_config: {
    scoring_type: "direct_scale", // direct_scale, binary, weighted_options, multi_select_sum, qualitative
    max_points: 10,               // maximum achievable points for this question (default: 10)
    scale_min: 0,                 // for direct_scale: minimum scale value (default: 0)
    scale_max: 5,                 // for direct_scale: maximum scale value (default: 5)
    positive_answer: "yes",       // for binary: which answer scores full points (default: "yes")
    option_weights: {             // for weighted_options and multi_select_sum (custom_weight mode)
      "Weekly": 10,
      "Monthly": 6,
      "Never": 0
    },
    mode: "equal_weight",         // for multi_select_sum: "equal_weight" or "custom_weight"
    weight_multiplier: 1.0        // optional multiplier for question importance (default: 1.0)
  },

  // Metadata
  help_text: "Consider your typical work week",
  metadata: {}
}

Scoring Configuration

The scoring_config field on each question defines how responses convert to numeric scores. There are five scoring types:

For full scoring specification, formulas, and examples, see Scoring System.

survey_sessions

{
  _id: ObjectId("..."),
  user_id: ObjectId("user_id"), // compound index with survey_id, attempt_number
  survey_id: ObjectId("survey_id"),
  organization_id: ObjectId("org_id"), // index

  // Attempt Tracking
  attempt_number: 1, // incremental per user per survey

  // Status
  status: "in_progress", // not_started, in_progress, completed, abandoned

  // Progress
  current_question_index: 5,
  total_questions: 20,
  questions_answered: 5,
  required_questions_remaining: 10,

  // Partial Responses
  partial_responses: {
    "question_id_1": "answer",
    "question_id_2": 7,
    "question_id_3": ["option1", "option2"]
  },

  // Timestamps
  started_at: ISODate("2024-01-15T10:00:00Z"),
  last_activity_at: ISODate("2024-01-15T10:15:00Z"),
  completed_at: null,
  abandoned_at: null,

  // Time Tracking
  time_spent_seconds: 300,
  time_per_question: [30, 45, 20, 15, 50],

  // Context
  browser_info: {
    user_agent: "Mozilla/5.0...",
    browser: "Chrome",
    version: "120.0",
    os: "Windows 10",
    device_type: "desktop"
  },
  ip_address: "192.168.1.1",
  ip_country: "US",

  // Reminders
  reminders_sent: ["24h"],
  next_reminder: "48h",

  // Metadata
  referrer: "email",
  utm_params: {},
  metadata: {}
}

survey_responses

{
  _id: ObjectId("..."),
  session_id: ObjectId("session_id"), // index
  user_id: ObjectId("user_id"), // compound index with survey_id
  survey_id: ObjectId("survey_id"),
  organization_id: ObjectId("org_id"), // index

  // Attempt Info
  attempt_number: 1,

  // Full Responses
  responses: {
    "question_id_1": "Satisfied",
    "question_id_2": 8,
    "question_id_3": true,
    "question_id_4": ["option1", "option3"],
    "question_id_5": "Open text response..."
  },

  // Scoring (see scoring-system.md for full specification)
  scores: {
    total: 85,
    by_category: {
      "section_1": 90,
      "section_2": 80
    },
    percentile: 75 // compared to others
  },

  // Detailed Score Breakdown (points-based scoring system)
  score: {
    total_points: 34,            // sum of points earned across scored questions
    max_possible_points: 40,     // sum of max_points for all scored questions
    percentage: 85,              // (total_points / max_possible_points) * 100
    question_scores: [
      {
        question_id: ObjectId("q1"),
        points_earned: 8.0,
        max_points: 10,
        scoring_type: "direct_scale"
      },
      {
        question_id: ObjectId("q2"),
        points_earned: 10.0,
        max_points: 10,
        scoring_type: "binary"
      },
      {
        question_id: ObjectId("q3"),
        points_earned: 10.0,
        max_points: 10,
        scoring_type: "weighted_options"
      },
      {
        question_id: ObjectId("q4"),
        points_earned: 6.0,
        max_points: 10,
        scoring_type: "multi_select_sum"
      }
      // qualitative questions are excluded from scoring
    ]
  },

  // Timestamps
  submitted_at: ISODate("2024-01-15T10:30:00Z"),
  completed_at: ISODate("2024-01-15T10:30:00Z"),

  // Time Tracking
  time_spent_seconds: 600,

  // Validation
  is_complete: true,
  is_valid: true,
  validation_errors: [],

  // Metadata
  submission_source: "web",
  metadata: {}
}

maslow_scores

{
  _id: ObjectId("..."),
  user_id: ObjectId("user_id"), // index
  session_id: ObjectId("session_id"),
  response_id: ObjectId("response_id"),
  survey_id: ObjectId("survey_id"), // index

  // Attempt Info
  attempt_number: 2,

  // Scores by Category
  scores: {
    physiological: 0.85,      // 0-1 scale
    safety: 0.72,
    love_belonging: 0.68,
    esteem: 0.61,
    self_actualization: 0.55,
    overall: 0.682            // weighted average
  },

  // Raw Scores (before normalization)
  raw_scores: {
    physiological: 42,         // out of 50
    safety: 36,               // out of 50
    love_belonging: 34,       // out of 50
    esteem: 30.5,            // out of 50
    self_actualization: 27.5  // out of 50
  },

  // Comparison with Previous
  comparison_with_previous: {
    attempt_compared: 1,
    physiological_delta: 0.05,
    safety_delta: -0.03,
    love_belonging_delta: 0.08,
    esteem_delta: 0.11,
    self_actualization_delta: 0.15,
    overall_delta: 0.072,
    days_between: 92
  },

  // Percentiles (compared to population)
  percentiles: {
    physiological: 75,
    safety: 60,
    love_belonging: 55,
    esteem: 45,
    self_actualization: 40,
    overall: 55
  },

  // Insights
  insights: {
    strongest_area: "physiological",
    weakest_area: "self_actualization",
    biggest_improvement: "self_actualization",
    biggest_decline: "safety",
    consistency_score: 0.82
  },

  // Timestamps
  calculated_at: ISODate("2024-01-15T10:30:00Z"),

  // Metadata
  calculation_version: "1.0",
  metadata: {}
}

email_reminders

{
  _id: ObjectId("..."),
  user_id: ObjectId("user_id"), // index
  session_id: ObjectId("session_id"),
  survey_id: ObjectId("survey_id"),

  // Reminder Details
  reminder_type: "24h", // 24h, 48h, 7d, custom
  reminder_number: 1, // 1st, 2nd, 3rd reminder

  // Scheduling
  scheduled_at: ISODate("2024-01-16T10:00:00Z"), // compound index with status
  sent_at: null,

  // Email Details
  email_to: "user@example.com",
  email_subject: "Complete your Maslow Assessment",
  email_template: "survey_reminder_24h",

  // Tracking
  email_provider_id: "sendgrid_message_id",
  opened_at: null,
  clicked_at: null,
  unsubscribed_at: null,

  // Status
  status: "pending", // pending, sent, failed, cancelled, bounced

  // Retry Logic
  retry_count: 0,
  max_retries: 3,
  last_retry_at: null,
  error_message: null,

  // Custom Data
  custom_message: null,
  resume_link: "https://app.com/survey/abc/resume",

  // Metadata
  campaign_id: "q1_2024_maslow",
  metadata: {}
}

Indexes

Performance Indexes

// Users
db.users.createIndex({ "email": 1 }, { unique: true })
db.users.createIndex({ "created_at": -1 })
db.users.createIndex({ "organization_memberships": 1 })

// Organizations
db.organizations.createIndex({ "slug": 1 }, { unique: true })
db.organizations.createIndex({ "created_by_id": 1 })

// Organization Members
db.organization_members.createIndex(
  { "organization_id": 1, "user_id": 1 }, { unique: true }
)
db.organization_members.createIndex({ "user_id": 1 })
db.organization_members.createIndex({ "role": 1 })

// Surveys
db.surveys.createIndex({ "organization_id": 1 })
db.surveys.createIndex({ "public_id": 1 }, { unique: true })
db.surveys.createIndex({ "created_by_id": 1 })
db.surveys.createIndex({ "is_active": 1, "is_published": 1 })

// Questions
db.questions.createIndex({ "survey_id": 1, "order": 1 })
db.questions.createIndex({ "survey_id": 1 })

// Survey Sessions
db.survey_sessions.createIndex(
  { "user_id": 1, "survey_id": 1, "attempt_number": 1 }, { unique: true }
)
db.survey_sessions.createIndex({ "status": 1 })
db.survey_sessions.createIndex({ "organization_id": 1 })
db.survey_sessions.createIndex({ "last_activity_at": 1 })

// Survey Responses
db.survey_responses.createIndex({ "user_id": 1, "survey_id": 1 })
db.survey_responses.createIndex({ "organization_id": 1 })
db.survey_responses.createIndex({ "submitted_at": -1 })
db.survey_responses.createIndex({ "session_id": 1 })

// Maslow Scores
db.maslow_scores.createIndex({ "user_id": 1, "attempt_number": -1 })
db.maslow_scores.createIndex({ "survey_id": 1 })
db.maslow_scores.createIndex({ "calculated_at": -1 })

// Email Reminders
db.email_reminders.createIndex({ "scheduled_at": 1, "status": 1 })
db.email_reminders.createIndex({ "user_id": 1 })
db.email_reminders.createIndex({ "session_id": 1 })

TTL Indexes

// Auto-delete expired invitations
db.organization_invitations.createIndex(
  { "expires_at": 1 },
  { expireAfterSeconds: 0 }
)

// Clean up abandoned sessions after 90 days
db.survey_sessions.createIndex(
  { "last_activity_at": 1 },
  {
    expireAfterSeconds: 7776000, // 90 days
    partialFilterExpression: { status: "abandoned" }
  }
)

Query Patterns

Common Queries

// Get user's organizations
db.organization_members.find({ user_id: ObjectId("...") })

// Get organization's active surveys
db.surveys.find({
  organization_id: ObjectId("..."),
  is_active: true,
  is_published: true
})

// Get user's survey attempts
db.survey_sessions.find({
  user_id: ObjectId("..."),
  survey_id: ObjectId("...")
}).sort({ attempt_number: -1 })

// Get pending reminders
db.email_reminders.find({
  scheduled_at: { $lte: new Date() },
  status: "pending"
})

// Calculate organization stats
db.survey_responses.aggregate([
  { $match: { organization_id: ObjectId("...") } },
  { $group: {
    _id: "$survey_id",
    total_responses: { $sum: 1 },
    avg_score: { $avg: "$scores.total" }
  }}
])

Performance Considerations

Denormalization

• User's organization memberships in user document
• Survey stats cached in survey document
• Member count cached in organization document

Aggregation Pipelines

// Maslow score trends
db.maslow_scores.aggregate([
  { $match: { user_id: ObjectId("...") } },
  { $sort: { attempt_number: 1 } },
  { $project: {
    attempt: "$attempt_number",
    date: "$calculated_at",
    scores: "$scores"
  }}
])