Database Schema

This document provides a comprehensive overview of the MedFeed database schema, including collections, relationships, and data structures.

Database Architecture

MedFeed uses MongoDB as its primary database with the following architectural principles:

[Screenshot placeholder: Database architecture diagram]

• Multi-tenant Architecture: Separate databases per hospital/organization
• Document-based Storage: Flexible schema for healthcare data
• Replica Sets: High availability and data redundancy
• Sharding: Horizontal scaling for large deployments

Core Collections

Users Collection

[Screenshot placeholder: Users collection schema diagram]

{
  _id: ObjectId,
  primaryEmail: String, // Unique email address
  name: String, // Full name
  isActive: Boolean, // Account status
  role: String, // User role (doctor, nurse, admin, etc.)
  department: ObjectId, // Reference to departments collection
  media: {
    profilePicture: String, // S3 URL
    thumbnails: [String] // Various sizes
  },
  permissions: [String], // Array of permission strings
  lastLogin: Date,
  createdAt: Date,
  updatedAt: Date,
  // Authentication
  password: String, // Hashed password
  refreshTokens: [String], // Active refresh tokens
  // Profile information
  phoneNumber: String,
  employeeId: String,
  licenseNumber: String,
  // Preferences
  preferences: {
    theme: String, // light, dark, auto
    language: String, // en, es, fr, etc.
    notifications: {
      email: Boolean,
      push: Boolean,
      sms: Boolean
    },
    dashboard: {
      layout: String,
      widgets: [Object]
    }
  }
}

Patients Collection

[Screenshot placeholder: Patients collection schema diagram]

{
  _id: ObjectId,
  // Demographics
  firstName: String,
  lastName: String,
  dateOfBirth: Date,
  gender: String, // M, F, O
  // Identifiers
  medicalRecordNumber: String, // Unique MRN
  nationalId: String, // SSN, National ID, etc.
  // Contact Information
  address: {
    street: String,
    city: String,
    state: String,
    zipCode: String,
    country: String
  },
  phoneNumber: String,
  email: String,
  emergencyContact: {
    name: String,
    relationship: String,
    phoneNumber: String
  },
  // Medical Information
  allergies: [{
    allergen: String,
    reaction: String,
    severity: String, // mild, moderate, severe
    verifiedDate: Date
  }],
  medications: [{
    name: String,
    dosage: String,
    frequency: String,
    startDate: Date,
    endDate: Date,
    prescribedBy: ObjectId // Reference to users
  }],
  // System fields
  isActive: Boolean,
  createdBy: ObjectId, // Reference to users
  createdAt: Date,
  updatedAt: Date
}

Patient Cases Collection

[Screenshot placeholder: Patient cases schema diagram]

{
  _id: ObjectId,
  patientId: ObjectId, // Reference to patients
  caseNumber: String, // Unique case identifier
  // Case Details
  admissionDate: Date,
  dischargeDate: Date,
  status: String, // active, discharged, transferred
  priority: String, // low, medium, high, critical
  // Clinical Information
  chiefComplaint: String,
  diagnosis: [{
    code: String, // ICD-10 code
    description: String,
    type: String, // primary, secondary
    confirmedDate: Date
  }],
  // Care Team
  attendingPhysician: ObjectId, // Reference to users
  residents: [ObjectId], // References to users
  nurses: [ObjectId], // References to users
  consultants: [ObjectId], // References to users
  // Location
  department: ObjectId, // Reference to departments
  room: String,
  bed: String,
  // System fields
  createdAt: Date,
  updatedAt: Date,
  archivedAt: Date // For archived cases
}

Clinical Notes Collection

[Screenshot placeholder: Clinical notes schema diagram]

{
  _id: ObjectId,
  // References
  patientId: ObjectId, // Reference to patients
  caseId: ObjectId, // Reference to patient cases
  authorId: ObjectId, // Reference to users (clinician)
  // Note Details
  noteType: String, // progress, admission, discharge, consultation
  template: String, // Note template used
  // Content
  content: {
    structured: Object, // Structured data fields
    narrative: String, // Free-text narrative
    markdown: String // Formatted markdown content
  },
  // Voice Data
  audioFile: {
    s3Key: String, // S3 object key
    duration: Number, // Duration in seconds
    transcription: {
      text: String,
      confidence: Number,
      language: String,
      timestamps: [Object] // Word-level timestamps
    }
  },
  // Clinical Data
  vitalSigns: {
    temperature: Number,
    bloodPressure: {
      systolic: Number,
      diastolic: Number
    },
    heartRate: Number,
    respiratoryRate: Number,
    oxygenSaturation: Number,
    recordedAt: Date
  },
  // Workflow
  status: String, // draft, pending_review, approved, signed
  reviewedBy: ObjectId, // Reference to users
  signedAt: Date,
  // System fields
  createdAt: Date,
  updatedAt: Date,
  version: Number // For version control
}

Feedback Collection

[Screenshot placeholder: Feedback collection schema diagram]

{
  _id: ObjectId,
  // References
  patientId: ObjectId, // Reference to patients
  caseId: ObjectId, // Reference to patient cases
  collectedBy: ObjectId, // Reference to users
  // Feedback Details
  type: String, // satisfaction, complaint, suggestion
  category: String, // care_quality, staff_behavior, facilities
  // Content
  audioFile: {
    s3Key: String,
    duration: Number,
    transcription: {
      text: String,
      confidence: Number,
      language: String,
      sentiment: {
        score: Number, // -1 to 1
        magnitude: Number,
        emotions: [String]
      }
    }
  },
  textFeedback: String, // Manual text entry
  // Ratings
  ratings: {
    overall: Number, // 1-10 scale
    careQuality: Number,
    staffBehavior: Number,
    facilities: Number,
    communication: Number,
    npsScore: Number // Net Promoter Score
  },
  // Analysis
  kpis: {
    nps: Number,
    csat: Number,
    ces: Number, // Customer Effort Score
    sentiment: String // positive, negative, neutral
  },
  // Workflow
  status: String, // new, reviewed, resolved, escalated
  assignedTo: ObjectId, // Reference to users
  priority: String, // low, medium, high, urgent
  // System fields
  createdAt: Date,
  updatedAt: Date,
  resolvedAt: Date
}

Tasks Collection

[Screenshot placeholder: Tasks collection schema diagram]

{
  _id: ObjectId,
  // Task Details
  title: String,
  description: String,
  type: String, // maintenance, patient_care, administrative
  category: String, // cleaning, medication, documentation
  // Assignment
  assignedTo: ObjectId, // Reference to users
  assignedBy: ObjectId, // Reference to users
  department: ObjectId, // Reference to departments
  // Scheduling
  dueDate: Date,
  estimatedDuration: Number, // Minutes
  priority: String, // low, medium, high, urgent
  // Location
  location: {
    department: String,
    room: String,
    floor: String,
    building: String
  },
  // Workflow
  status: String, // pending, in_progress, completed, cancelled
  progress: Number, // 0-100 percentage
  // Completion
  completedAt: Date,
  completedBy: ObjectId, // Reference to users
  completionNotes: String,
  // Attachments
  attachments: [{
    type: String, // image, document, audio
    s3Key: String,
    filename: String,
    uploadedBy: ObjectId,
    uploadedAt: Date
  }],
  // System fields
  createdAt: Date,
  updatedAt: Date
}

Supporting Collections

Departments Collection

[Screenshot placeholder: Departments schema diagram]

{
  _id: ObjectId,
  name: String, // Department name
  code: String, // Short code (e.g., "ER", "ICU")
  description: String,
  // Hierarchy
  parentDepartment: ObjectId, // Reference to parent department
  // Management
  headOfDepartment: ObjectId, // Reference to users
  managers: [ObjectId], // References to users
  // Contact Information
  phoneNumber: String,
  email: String,
  location: {
    building: String,
    floor: String,
    wing: String
  },
  // Configuration
  settings: {
    allowTaskCreation: Boolean,
    requireApproval: Boolean,
    defaultTaskPriority: String
  },
  // System fields
  isActive: Boolean,
  createdAt: Date,
  updatedAt: Date
}

Organizations Collection

[Screenshot placeholder: Organizations schema diagram]

{
  _id: ObjectId,
  // Basic Information
  name: String, // Hospital/Organization name
  type: String, // hospital, clinic, health_system
  // Contact Information
  address: {
    street: String,
    city: String,
    state: String,
    zipCode: String,
    country: String
  },
  phoneNumber: String,
  email: String,
  website: String,
  // Configuration
  settings: {
    timezone: String,
    language: String,
    currency: String,
    dateFormat: String,
    // Feature flags
    features: {
      voiceFeedback: Boolean,
      scribeDM: Boolean,
      taskOrchestration: Boolean,
      analytics: Boolean
    }
  },
  // Branding
  branding: {
    logo: String, // S3 URL
    primaryColor: String,
    secondaryColor: String,
    theme: String
  },
  // System fields
  isActive: Boolean,
  createdAt: Date,
  updatedAt: Date
}

Activity Logs Collection

[Screenshot placeholder: Activity logs schema diagram]

{
  _id: ObjectId,
  // Actor Information
  userId: ObjectId, // Reference to users
  userEmail: String,
  userName: String,
  // Action Details
  action: String, // login, create_note, update_patient, etc.
  resource: String, // patient, note, task, etc.
  resourceId: ObjectId, // ID of affected resource
  // Context
  ipAddress: String,
  userAgent: String,
  location: {
    country: String,
    region: String,
    city: String
  },
  // Request Details
  method: String, // GET, POST, PUT, DELETE
  endpoint: String, // API endpoint
  statusCode: Number, // HTTP status code
  // Additional Data
  metadata: Object, // Additional context data
  changes: Object, // Before/after values for updates
  // System fields
  timestamp: Date,
  sessionId: String
}

Indexes and Performance

Primary Indexes

[Screenshot placeholder: Database indexes visualization]

Users Collection:

// Unique indexes
{ primaryEmail: 1 } // Unique
{ employeeId: 1 } // Unique, sparse

// Compound indexes
{ department: 1, role: 1 }
{ isActive: 1, lastLogin: -1 }

Patients Collection:

// Unique indexes
{ medicalRecordNumber: 1 } // Unique

// Search indexes
{ firstName: 1, lastName: 1 }
{ dateOfBirth: 1 }
{ phoneNumber: 1 }

// Compound indexes
{ isActive: 1, createdAt: -1 }

Clinical Notes Collection:

// Foreign key indexes
{ patientId: 1, createdAt: -1 }
{ caseId: 1, createdAt: -1 }
{ authorId: 1, createdAt: -1 }

// Status and workflow indexes
{ status: 1, createdAt: -1 }
{ noteType: 1, createdAt: -1 }

Tasks Collection:

// Assignment indexes
{ assignedTo: 1, status: 1, dueDate: 1 }
{ department: 1, status: 1, priority: -1 }

// Status and priority indexes
{ status: 1, createdAt: -1 }
{ priority: -1, dueDate: 1 }

Text Search Indexes

// Full-text search capabilities
db.patients.createIndex({
  firstName: "text",
  lastName: "text",
  medicalRecordNumber: "text"
});

db.clinicalNotes.createIndex({
  "content.narrative": "text",
  "audioFile.transcription.text": "text"
});

db.feedback.createIndex({
  "audioFile.transcription.text": "text",
  textFeedback: "text"
});

Data Relationships

Entity Relationship Diagram

[Screenshot placeholder: Complete ERD showing all relationships]

Key Relationships:

• Users ↔ Departments (Many-to-One)
• Patients ↔ Patient Cases (One-to-Many)
• Patient Cases ↔ Clinical Notes (One-to-Many)
• Patient Cases ↔ Feedback (One-to-Many)
• Users ↔ Tasks (Many-to-Many via assignment)
• Departments ↔ Tasks (One-to-Many)

Data Validation Rules

Schema Validation

[Screenshot placeholder: Validation rules configuration]

// Example validation for Users collection
{
  $jsonSchema: {
    bsonType: "object",
    required: ["primaryEmail", "name", "role"],
    properties: {
      primaryEmail: {
        bsonType: "string",
        pattern: "^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\\.[a-zA-Z]{2,}$"
      },
      name: {
        bsonType: "string",
        minLength: 2,
        maxLength: 100
      },
      role: {
        enum: ["doctor", "nurse", "admin", "quality_manager", "technician"]
      },
      phoneNumber: {
        bsonType: "string",
        pattern: "^\\+?[1-9]\\d{1,14}$"
      }
    }
  }
}

Backup and Recovery

Backup Strategy

[Screenshot placeholder: Backup configuration interface]

Backup Schedule:

• Continuous: Replica set with oplog
• Daily: Full database backup
• Weekly: Compressed archive backup
• Monthly: Long-term storage backup

Recovery Procedures:

• Point-in-time Recovery: Using oplog replay
• Full Restore: From daily backups
• Selective Restore: Individual collections
• Cross-region Restore: Disaster recovery

Security Considerations

Data Encryption

[Screenshot placeholder: Encryption configuration]

Encryption at Rest:

• MongoDB encrypted storage engine
• Field-level encryption for sensitive data
• Key management through AWS KMS

Encryption in Transit:

• TLS 1.3 for all connections
• Certificate-based authentication
• VPN tunnels for cross-site replication

Access Control

// Role-based access control example
{
  role: "clinician",
  privileges: [
    {
      resource: { db: "medfeed", collection: "patients" },
      actions: ["find", "update"]
    },
    {
      resource: { db: "medfeed", collection: "clinicalNotes" },
      actions: ["find", "insert", "update"]
    }
  ]
}

---

For API integration details, see the API Reference. For deployment configuration, refer to the Installation Guide.