Database Schema

CareLog uses a JSON-based database stored in data/data.json. This document describes the data structure and relationships.

Database Structure

The database is a single JSON file with collections as top-level keys:

json

{
  "users": {},
  "healthLogs": {},
  "appointments": {},
  "medicalRecords": {},
  "diagnoses": {},
  "prescriptions": {},
  "emergencyCalls": {},
  "notifications": {},
  "visitRequests": {},
  "auditLogs": {},
  "feedback": {}
}

Collections

users

Stores all user accounts across all roles.

Structure:

json

{
  "user_id": {
    "id": "string",
    "name": "string",
    "firstName": "string",
    "lastName": "string",
    "email": "string (unique)",
    "password": "string (hashed)",
    "role": "patient|doctor|nurse|hospitaladmin|family",
    "isDisabled": "boolean",
    "deletionRequested": "boolean",
    "linkedPatients": ["array of patient IDs (for family role)"]
  }
}

Relationships:

linkedPatients: References users collection (patient role IDs)

Indexes:

Primary Key: id
Unique: email
Index: role

healthLogs

Daily health status logs created by patients.

Structure:

json

{
  "log_id": {
    "logID": "string",
    "patientID": "string",
    "timestamp": "ISO 8601 datetime",
    "physicalStatus": "array or string",
    "emotionalStatus": "array or string",
    "symptoms": "string",
    "isSensitive": "boolean"
  }
}

Relationships:

patientID: References users.id (role: patient)

Indexes:

Primary Key: logID
Foreign Key: patientID
Index: timestamp, isSensitive

appointments

Medical appointment requests and schedules.

Structure:

json

{
  "appointment_id": {
    "appointmentID": "string",
    "patientID": "string",
    "doctorID": "string (optional)",
    "reason": "string",
    "requestedDate": "ISO 8601 datetime",
    "scheduledDate": "ISO 8601 datetime (optional)",
    "status": "Pending|Scheduled|Completed|Cancelled",
    "notes": "string (optional)",
    "createdAt": "ISO 8601 datetime"
  }
}

Relationships:

patientID: References users.id (role: patient)
doctorID: References users.id (role: doctor)

Indexes:

Primary Key: appointmentID
Foreign Keys: patientID, doctorID
Index: status, scheduledDate

medicalRecords

Comprehensive medical records for each patient.

Structure:

json

{
  "record_id": {
    "recordID": "string",
    "patientID": "string",
    "diagnoses": ["array of diagnosis IDs"],
    "prescriptions": ["array of prescription IDs"],
    "appointments": ["array of appointment IDs"],
    "healthLogs": ["array of health log IDs"],
    "lastUpdated": "ISO 8601 datetime"
  }
}

Relationships:

patientID: References users.id (role: patient)
diagnoses[]: References diagnoses.diagnosisID
prescriptions[]: References prescriptions.prescriptionID
appointments[]: References appointments.appointmentID
healthLogs[]: References healthLogs.logID

Indexes:

Primary Key: recordID
Foreign Key: patientID
Unique: One record per patient

diagnoses

Medical diagnoses made by doctors.

Structure:

json

{
  "diagnosis_id": {
    "diagnosisID": "string",
    "doctorID": "string",
    "patientID": "string",
    "condition": "string",
    "notes": "string",
    "dateDiagnosed": "ISO 8601 date",
    "treatmentPlan": "string (optional)",
    "followUp": "string (optional)"
  }
}

Relationships:

doctorID: References users.id (role: doctor)
patientID: References users.id (role: patient)

Indexes:

Primary Key: diagnosisID
Foreign Keys: doctorID, patientID
Index: dateDiagnosed

prescriptions

Medication prescriptions written by doctors.

Structure:

json

{
  "prescription_id": {
    "prescriptionID": "string",
    "doctorID": "string",
    "patientID": "string",
    "medication": "string",
    "dosage": "string",
    "instruction": "string",
    "datePrescribed": "ISO 8601 date",
    "duration": "string (optional)",
    "warnings": "string (optional)",
    "isActive": "boolean (optional)"
  }
}

Relationships:

doctorID: References users.id (role: doctor)
patientID: References users.id (role: patient)

Indexes:

Primary Key: prescriptionID
Foreign Keys: doctorID, patientID
Index: datePrescribed, isActive

emergencyCalls

Urgent care requests from patients.

Structure:

json

{
  "call_id": {
    "callID": "string",
    "patientID": "string",
    "timestamp": "ISO 8601 datetime",
    "urgencyLevel": "CRITICAL|HIGH|MEDIUM",
    "description": "string (optional)",
    "isResolved": "boolean",
    "isEscalated": "boolean",
    "respondedBy": "string (optional, nurse/doctor ID)",
    "responseTime": "ISO 8601 datetime (optional)",
    "notes": "string (optional)"
  }
}

Relationships:

patientID: References users.id (role: patient)
respondedBy: References users.id (role: nurse or doctor)

Indexes:

Primary Key: callID
Foreign Keys: patientID, respondedBy
Index: isResolved, urgencyLevel, timestamp

notifications

System notifications for users.

Structure:

json

{
  "notification_id": {
    "notificationID": "string",
    "userID": "string",
    "message": "string",
    "timestamp": "ISO 8601 datetime",
    "isRead": "boolean",
    "type": "string (optional)",
    "relatedID": "string (optional)"
  }
}

Relationships:

userID: References users.id
relatedID: May reference any entity depending on type

Indexes:

Primary Key: notificationID
Foreign Key: userID
Index: isRead, timestamp

visitRequests

Family visit requests to patients.

Structure:

json

{
  "request_id": {
    "requestID": "string",
    "patientID": "string",
    "familyMemberID": "string",
    "requestedDate": "ISO 8601 datetime",
    "status": "Submitted|Approved|Denied|Completed",
    "purpose": "string (optional)",
    "createdAt": "ISO 8601 datetime",
    "approvedAt": "ISO 8601 datetime (optional)"
  }
}

Relationships:

patientID: References users.id (role: patient)
familyMemberID: References users.id (role: family)

Indexes:

Primary Key: requestID
Foreign Keys: patientID, familyMemberID
Index: status, requestedDate

auditLogs

System-wide audit trail for compliance.

Structure:

json

{
  "log_id": {
    "logID": "string",
    "userID": "string",
    "action": "string",
    "timestamp": "ISO 8601 datetime",
    "details": "string",
    "ipAddress": "string (optional)",
    "resourceType": "string (optional)",
    "resourceID": "string (optional)"
  }
}

Relationships:

userID: References users.id
resourceID: May reference any entity depending on resourceType

Indexes:

Primary Key: logID
Foreign Key: userID
Index: timestamp, action

Note: Audit logs are immutable and cannot be deleted.

feedback

User feedback submissions.

Structure:

json

{
  "feedback_id": {
    "feedbackID": "string",
    "userID": "string",
    "category": "string",
    "message": "string",
    "timestamp": "ISO 8601 datetime",
    "status": "Pending|Reviewed|Resolved (optional)",
    "response": "string (optional)"
  }
}

Relationships:

userID: References users.id

Indexes:

Primary Key: feedbackID
Foreign Key: userID
Index: timestamp, category

Data Relationships

Entity Relationship Diagram (Conceptual)

User (Patient) ──┬─── HealthLog (1:N)
                 ├─── Appointment (1:N)
                 ├─── MedicalRecord (1:1)
                 ├─── EmergencyCall (1:N)
                 ├─── VisitRequest (1:N, as patient)
                 └─── Notification (1:N)

User (Doctor) ───┬─── Appointment (1:N)
                 ├─── Diagnosis (1:N)
                 └─── Prescription (1:N)

User (Nurse) ────└─── EmergencyCall (N:1, respondedBy)

User (Family) ───└─── VisitRequest (1:N, as family member)
                      └──> Linked to User (Patient) (N:N)

MedicalRecord ───┬─── Diagnosis (1:N)
                 ├─── Prescription (1:N)
                 ├─── Appointment (1:N)
                 └─── HealthLog (1:N)

AuditLog ────────└─── User (N:1)
Feedback ────────└─── User (N:1)

Data Integrity Rules

Referential Integrity

Users: Cannot delete if referenced by other entities
Patients: Cannot delete if have active appointments or medical records
Doctors: Cannot delete if have active appointments
Family Members: Remove patient links before deletion

Business Rules

Health Logs
- Must have valid patientID
- Timestamp auto-generated if not provided
- Sensitive flag defaults to False
Appointments
- Must have valid patientID
- doctorID optional until assigned
- Status transitions: Pending → Scheduled → Completed/Cancelled
Medical Records
- One record per patient (1:1 relationship)
- Auto-created when patient created
- References must exist in respective collections
Diagnoses & Prescriptions
- Must have valid doctorID and patientID
- Date cannot be in the future
- Automatically added to patient's medical record
Emergency Calls
- Must have valid patientID
- urgencyLevel required
- Cannot be deleted, only resolved
- Escalation creates audit log entry
Visit Requests
- Must have valid patientID and familyMemberID
- Family member must be linked to patient
- Status transitions: Submitted → Approved/Denied → Completed
Audit Logs
- Immutable (no updates or deletes)
- Automatically created for sensitive operations
- Timestamp auto-generated
Notifications
- Must have valid userID
- Auto-generated for significant events
- isRead defaults to False

Data Access Patterns

Common Queries

Get user by email (login)
```
Filter users where email = ?
```

Get patient health logs

Filter healthLogs where patientID = ?
Order by timestamp DESC

Get non-sensitive health logs (for family)

Filter healthLogs where patientID = ? AND isSensitive = false
Order by timestamp DESC

Get pending appointments

Filter appointments where status = "Pending"
Order by requestedDate ASC

Get unresolved emergencies

Filter emergencyCalls where isResolved = false
Order by urgencyLevel DESC, timestamp ASC

Get patient medical record

Filter medicalRecords where patientID = ?
Then fetch all related diagnoses, prescriptions, appointments

Get user notifications

Filter notifications where userID = ?
Order by timestamp DESC

Get audit logs by user

Filter auditLogs where userID = ?
Order by timestamp DESC

ID Generation

All IDs are generated using UUID or a combination of:

Entity prefix (e.g., "user", "appt", "diag")
Timestamp
Random suffix

Example: user_20251026_abc123

Data Migration

When schema changes are needed:

Create backup
Write migration script
Test on copy of database
Apply migration
Verify data integrity
Update application code

Performance Considerations

Indexing Strategy

While JSON doesn't have traditional indexes, access patterns should:

Use ID lookups when possible (O(1))
Cache frequently accessed data
Minimize full collection scans
Consider pagination for large datasets

Optimization Tips

Lazy Loading: Load related entities only when needed
Caching: Cache user sessions and frequently accessed records
Batch Operations: Group multiple updates
Pagination: Limit results for large collections

Backup and Recovery

Backup Strategy

Automatic daily backups
Manual backup before major changes
Stored in data/backups/
Named with timestamp: backup_YYYYMMDD_HHMMSS.json

Recovery

python

from core.services.database_service import DatabaseService

db = DatabaseService()
db.restore_from_backup("data/backups/backup_20251026_120000.json")

Security Considerations

Passwords: Always hashed using Argon2
Sensitive Data: Marked with isSensitive flag
Access Control: Enforced at service layer
Audit Trail: All sensitive operations logged
Data Export: Restrict to authorized roles

Database Schema ​

Database Structure ​

Collections ​

users ​

healthLogs ​

appointments ​

medicalRecords ​

diagnoses ​

prescriptions ​

emergencyCalls ​

notifications ​

visitRequests ​

auditLogs ​

feedback ​

Data Relationships ​

Entity Relationship Diagram (Conceptual) ​

Data Integrity Rules ​

Referential Integrity ​

Business Rules ​

Data Access Patterns ​

Common Queries ​

ID Generation ​

Data Migration ​

Performance Considerations ​

Indexing Strategy ​

Optimization Tips ​

Backup and Recovery ​

Backup Strategy ​

Recovery ​

Security Considerations ​

Next Steps ​

Database Schema

Database Structure

Collections

users

healthLogs

appointments

medicalRecords

diagnoses

prescriptions

emergencyCalls

notifications

visitRequests

auditLogs

feedback

Data Relationships

Entity Relationship Diagram (Conceptual)

Data Integrity Rules

Referential Integrity

Business Rules

Data Access Patterns

Common Queries

ID Generation

Data Migration

Performance Considerations

Indexing Strategy

Optimization Tips

Backup and Recovery

Backup Strategy

Recovery

Security Considerations

Next Steps