PostgreSQL vs MongoDB: When to Use What?

-- Users
CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email VARCHAR(255) UNIQUE NOT NULL,
  phone VARCHAR(20) UNIQUE NOT NULL,
  role VARCHAR(20) NOT NULL CHECK (role IN ('patient', 'doctor', 'admin')),
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Doctors
CREATE TABLE doctors (
  id UUID PRIMARY KEY REFERENCES users(id),
  specialty VARCHAR(100) NOT NULL,
  license_number VARCHAR(50) UNIQUE NOT NULL,
  consultation_fee INTEGER NOT NULL,
  available BOOLEAN DEFAULT true
);

-- Consultations
CREATE TABLE consultations (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  patient_id UUID NOT NULL REFERENCES users(id),
  doctor_id UUID NOT NULL REFERENCES doctors(id),
  scheduled_at TIMESTAMPTZ NOT NULL,
  status VARCHAR(20) NOT NULL,
  amount_paid INTEGER,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_consultations_patient ON consultations(patient_id);
CREATE INDEX idx_consultations_doctor ON consultations(doctor_id);
CREATE INDEX idx_consultations_scheduled ON consultations(scheduled_at);

-- Top 10 doctors by consultation count
SELECT
  u.id,
  u.email,
  d.specialty,
  COUNT(c.id) as total_consultations,
  AVG(c.amount_paid) as avg_revenue
FROM users u
JOIN doctors d ON u.id = d.id
LEFT JOIN consultations c ON d.id = c.doctor_id
WHERE c.status = 'completed'
GROUP BY u.id, u.email, d.specialty
ORDER BY total_consultations DESC
LIMIT 10;

// MongoDB Schema
interface MedicalRecord {
  _id: ObjectId
  patientId: string  // Reference to PostgreSQL
  consultations: Array<{
    consultationId: string
    doctorId: string
    date: Date
    diagnosis: string
    symptoms: string[]
    prescriptions: Array<{
      medication: string
      dosage: string
      duration: string
      instructions: string
    }>
    examResults: Array<{
      type: string
      result: any
      attachments: string[]
    }>
    notes: string
  }>
  allergies: string[]
  chronicDiseases: string[]
  vaccinations: Array<{
    name: string
    date: Date
    nextDose?: Date
  }>
  vitalSigns: Array<{
    date: Date
    weight: number
    height: number
    bloodPressure: string
    heartRate: number
    temperature: number
  }>
  createdAt: Date
  updatedAt: Date
}

// Add consultation to medical record
await MedicalRecordModel.updateOne(
  { patientId },
  {
    $push: {
      consultations: {
        consultationId,
        doctorId,
        date: new Date(),
        diagnosis: 'Seasonal flu',
        symptoms: ['Fever', 'Cough', 'Fatigue'],
        prescriptions: [{
          medication: 'Paracetamol',
          dosage: '1g',
          duration: '5 days',
          instructions: '3 times a day after meals'
        }],
        notes: 'Rest advised'
      }
    },
    $set: { updatedAt: new Date() }
  }
)

// Retrieve complete history
const record = await MedicalRecordModel.findOne({ patientId })
console.log(record.consultations)  // All consultations

// consultation.service.ts
async completeConsultation(consultationId: string, details: ConsultationDetails) {
  // 1. Update PostgreSQL
  await this.pgRepo.update(consultationId, {
    status: 'completed',
    completedAt: new Date()
  })

  // 2. Add to MongoDB medical record
  await this.mongoMedicalRecord.addConsultation({
    consultationId,
    ...details
  })

  // If one fails, rollback
  // Distributed transaction with Saga pattern
}