= Advanced Application Development =

== Transactions ==

Medora's service layer uses **@Transactional** on methods that write to more than one table, so a failure partway through rolls back everything instead of leaving incomplete data behind. Five real examples from the codebase:

=== Scenario 1: Patient creation with medical record ===

'''File:''' `src/main/java/medora/service/PatientService.java`

Creates a patient and automatically creates their medical record in the same transaction.

{{{
@Transactional
public Patient createPatient(Patient patient) {
    if (patient == null || patient.getEmbg() == null || patient.getEmbg().isBlank()) {
        throw new IllegalArgumentException("Patient EMBG is required");
    }
    if (patient.getFirstName() == null || patient.getFirstName().isBlank()) {
        throw new IllegalArgumentException("Patient first name is required");
    }
    if (patient.getLastName() == null || patient.getLastName().isBlank()) {
        throw new IllegalArgumentException("Patient last name is required");
    }

    logger.info("Creating new patient with EMBG: {}", patient.getEmbg());
    Patient savedPatient = patientRepository.save(patient);

   
    try {
        MedicalRecord medicalRecord = new MedicalRecord();
        medicalRecord.setPatient(savedPatient);
        medicalRecordRepository.save(medicalRecord);
        logger.info("Created medical record for patient ID: {}", savedPatient.getPatientId());
    } catch (Exception e) {
        logger.error("Failed to create medical record for patient: {}", e.getMessage());
    }

    return savedPatient;
}
}}}

'''Transaction behavior:''' both `patientRepository.save()` and `medicalRecordRepository.save()` execute within the same transaction. If either fails, both are rolled back.

=== Scenario 2: Auto generate billing from procedures & lab tests ===

'''File:''' `src/main/java/medora/service/BillingService.java`

Creates a billing record and links every performed procedure and lab test for a patient on a given date.

{{{
@Transactional
public void autoGenerateBillingForPatientService(Long patientId, LocalDate serviceDate) {
    try {
        if (patientId == null || patientId <= 0) {
            throw new IllegalArgumentException("Patient ID must be valid");
        }
        if (serviceDate == null) {
            throw new IllegalArgumentException("Service date must be valid");
        }

        logger.info("Starting auto-billing for patient {} on date {}", patientId, serviceDate);

        MedicalRecord medicalRecord = medicalRecordRepository.findByPatientPatientId(patientId)
                .orElseGet(() -> {
                    logger.info("Creating new medical record for patient {}", patientId);
                    MedicalRecord newRecord = new MedicalRecord();
                    newRecord.setPatient(patientRepository.findById(patientId)
                            .orElseThrow(() -> new RuntimeException("Patient not found with ID: " + patientId)));
                    return medicalRecordRepository.save(newRecord);
                });

        logger.info("Using medical record {} for patient {}", medicalRecord.getRecordId(), patientId);

        List<PerformedProcedures> procedures = performedProcedureRepository.findByPatientAndDate(patientId, serviceDate);
        List<PerformedLabTests> labTests = performedLabTestRepository.findByPatientAndDate(patientId, serviceDate);

        logger.info("Found {} procedures and {} lab tests for patient {} on {}",
                procedures.size(), labTests.size(), patientId, serviceDate);

        if (procedures.isEmpty() && labTests.isEmpty()) {
            logger.info("No procedures or lab tests found for patient {} on {}", patientId, serviceDate);
            return;
        }

        BigDecimal procedureCost = procedures.stream()
                .map(p -> {
                    BigDecimal cost = p.getProcedure().getCost();
                    logger.debug("Procedure {} cost: {}", p.getProcedure().getProcedureId(), cost);
                    return cost;
                })
                .reduce(BigDecimal.ZERO, BigDecimal::add);

        BigDecimal labTestCost = labTests.stream()
                .map(lt -> {
                    BigDecimal cost = lt.getLabTest().getCost();
                    logger.debug("Lab test {} cost: {}", lt.getLabTest().getTestId(), cost);
                    return cost;
                })
                .reduce(BigDecimal.ZERO, BigDecimal::add);

        BigDecimal totalCost = procedureCost.add(labTestCost);
        logger.info("Total cost calculation: procedures={}, labTests={}, total={}", procedureCost, labTestCost, totalCost);

        // Get default admin (first admin in system) - skip billing if none found
        Optional<Admin> adminOptional = adminRepository.findAll()
                .stream()
                .findFirst();

        if (adminOptional.isEmpty()) {
            logger.warn("No admin found in system - skipping automatic billing generation for patient {} on {}", patientId, serviceDate);
            return;
        }

        Admin admin = adminOptional.get();

        Billing billing = billingRepository.findBillingForPatientOnDate(patientId, serviceDate);

        if (billing != null) {
            logger.info("Billing record {} already exists for patient {} on {}, updating with new total",
                    billing.getBillId(), patientId, serviceDate);
            billing.setTotalCost(totalCost);
        } else {
            billing = new Billing();
            billing.setMedicalRecord(medicalRecord);
            billing.setAdmin(admin);
            billing.setTotalCost(totalCost);
            billing.setPaymentStatus(PaymentStatus.PENDING);
            billing.setPaymentDate(serviceDate);

            logger.info("Creating new billing record for patient {} on {}", patientId, serviceDate);
        }

        Billing savedBilling = billingRepository.save(billing);
        logger.info("Billing record {} for patient {} on {} with total cost: {}",
                savedBilling.getBillId(), patientId, serviceDate, totalCost);

        for (PerformedProcedures procedure : procedures) {
            try {
                BillingProcedures billingProcedure = new BillingProcedures(savedBilling, procedure.getProcedure());
                billingProceduresRepository.save(billingProcedure);
                logger.debug("Linked procedure {} to billing {}", procedure.getProcedure().getProcedureId(), savedBilling.getBillId());
            } catch (Exception e) {
                logger.debug("Procedure {} already linked to billing {}", procedure.getProcedure().getProcedureId(), savedBilling.getBillId());
            }
        }

        for (PerformedLabTests labTest : labTests) {
            try {
                BillingLabTests billingLabTest = new BillingLabTests(savedBilling, labTest.getLabTest());
                billingLabTestsRepository.save(billingLabTest);
                logger.debug("Linked lab test {} to billing {}", labTest.getLabTest().getTestId(), savedBilling.getBillId());
            } catch (Exception e) {
                logger.debug("Lab test {} already linked to billing {}", labTest.getLabTest().getTestId(), savedBilling.getBillId());
            }
        }

        logger.info("Successfully processed {} procedures and {} lab tests for billing record {}",
                procedures.size(), labTests.size(), savedBilling.getBillId());

    } catch (Exception e) {
        logger.error("Error in auto-billing for patient {} on date {}: {}", patientId, serviceDate, e.getMessage(), e);
        throw e;
    }
}
}}}

**Transaction behavior**: Medical record lookup or creation, cost calculation, billing creation or update and linking every procedure and lab test all happen in one transaction. If any step fails, everything rolls back so no half created billing record and no partially linked items.


=== Scenario 3: Request a lab test with automated billing ===

'''File:''' `src/main/java/medora/service/LabService.java`

A doctor requests a lab test for a patient, which automatically triggers billing generation.

{{{
@Transactional
public PerformedLabTests requestLabTestForPatient(Long patientId,
                                                  Long doctorId,
                                                  Long testId,
                                                  LocalDate testDate,
                                                  String notes) {
    if (patientId == null || patientId <= 0)
        throw new IllegalArgumentException("Invalid patient ID");

    if (doctorId == null || doctorId <= 0)
        throw new IllegalArgumentException("Invalid doctor ID");

    if (testId == null || testId <= 0)
        throw new IllegalArgumentException("Invalid test ID");

    Patient patient = patientRepository.findById(patientId)
            .orElseThrow(() -> new RuntimeException("Patient not found"));

    Doctors doctor = doctorRepository.findById(doctorId)
            .orElseThrow(() -> new RuntimeException("Doctor not found"));

    LabTests test = labTestRepository.findById(testId)
            .orElseThrow(() -> new RuntimeException("Lab test not found"));

    PerformedLabTests performedTest = new PerformedLabTests();
    performedTest.setPatient(patient);
    performedTest.setDoctor(doctor);
    performedTest.setLabTest(test);
    LocalDate finalTestDate = testDate != null ? testDate : LocalDate.now();
    performedTest.setTestDate(finalTestDate);
    performedTest.setNotes(notes);

    logger.info("Lab test {} requested for patient {} by doctor {}", testId, patientId, doctorId);
    PerformedLabTests saved = performedLabTestRepository.save(performedTest);

    // Auto generate billing for the patient on this date
    billingService.autoGenerateBillingForPatientService(patientId, finalTestDate);

    return saved;
}
}}}

'''Transaction behavior:''' the lab test save and the billing service call execute in a single transaction. If billing generation fails, the lab test creation is rolled back.

=== Scenario 4: Store lab result and link to medical record ===

'''File:''' `src/main/java/medora/service/LabService.java`

Stores a lab result and links it to the patient's medical record.

{{{
@Transactional
public MedicalRecordLabResults storeLabResult(Long medicalRecordId,
                                              Long testId,
                                              String results,
                                              LocalDate resultDate) {

    if (medicalRecordId == null || medicalRecordId <= 0)
        throw new IllegalArgumentException("Invalid medical record ID");

    if (testId == null || testId <= 0)
        throw new IllegalArgumentException("Invalid test ID");

    if (results == null || results.isBlank())
        throw new IllegalArgumentException("Results required");

    MedicalRecord medicalRecord = medicalRecordRepository.findById(medicalRecordId)
            .orElseThrow(() -> new RuntimeException("Medical record not found"));

    LabTests labTest = labTestRepository.findById(testId)
            .orElseThrow(() -> new RuntimeException("Lab test not found"));

    LabResults labResult = new LabResults();
    labResult.setResults(results);
    labResult.setResultDate(resultDate);
    labResult.setLabTest(labTest);

    LabResults saved = labResultsRepository.save(labResult);

    // Use the join repository to safely link to avoid deleting existing links
    // Create and save the join entity explicitly

    MedicalRecordLabResults link = new MedicalRecordLabResults();
    link.setMedicalRecord(medicalRecord);
    link.setLabResult(saved);

    logger.info("Stored lab result {} for medical record {}", saved.getResultId(), medicalRecordId);
    return medicalRecordLabResultRepository.save(link);
}
}}}

'''Transaction behavior:''' Both the lab result creation and the link to the medical record happen atomically. If either fails, both are rolled back.

=== Scenario 5: Appointment creation ===

'''File:''' `src/main/java/medora/service/AppointmentService.java`

Creates an appointment with validation for future dates, no double-booking, and no duplicate appointments.

{{{
@Transactional
public Appointment createAppointment(Long patientId,
                                     Long doctorId,
                                     LocalDate appointmentDate,
                                     LocalTime appointmentTime) {

    if (patientId == null || patientId <= 0) {
        throw new IllegalArgumentException("Patient ID must be valid");
    }

    if (doctorId == null || doctorId <= 0) {
        throw new IllegalArgumentException("Doctor ID must be valid");
    }

    if (appointmentDate == null) {
        throw new IllegalArgumentException("Appointment date is required");
    }

    if (appointmentTime == null) {
        throw new IllegalArgumentException("Appointment time is required");
    }

    Patient patient = patientRepository.findById(patientId)
            .orElseThrow(() ->
                    new RuntimeException("Patient not found with ID: " + patientId));

    Doctors doctor = doctorRepository.findById(doctorId)
            .orElseThrow(() ->
                    new RuntimeException("Doctor not found with ID: " + doctorId));

    LocalDateTime appointmentDateTime =
            LocalDateTime.of(appointmentDate, appointmentTime);

    // Future validation

    if (!appointmentDateTime.isAfter(LocalDateTime.now())) {
        throw new RuntimeException(
                "Appointment must be scheduled for a future date and time"
        );
    }

    // Doctor slot validation
    boolean doctorBusy =
            appointmentRepository
                    .existsByDoctorDoctorIdAndAppointmentDateAndAppointmentTimeAndStatusNot(
                            doctorId,
                            appointmentDate,
                            appointmentTime,
                            AppointmentStatus.CANCELLED
                    );

    if (doctorBusy) {
        throw new RuntimeException("This appointment slot is already booked");
    }

    // Duplicate patient validation
    boolean duplicateAppointment =
            appointmentRepository
                    .existsByPatientPatientIdAndDoctorDoctorIdAndAppointmentDateAndAppointmentTimeAndStatusNot(
                            patientId,
                            doctorId,
                            appointmentDate,
                            appointmentTime,
                            AppointmentStatus.CANCELLED
                    );

    if (duplicateAppointment) {
        throw new RuntimeException(
                "Patient already has this appointment scheduled"
        );
    }

    Appointment appointment = new Appointment();

    appointment.setPatient(patient);
    appointment.setDoctor(doctor);
    appointment.setAppointmentDate(appointmentDate);
    appointment.setAppointmentTime(appointmentTime);
    appointment.setStatus(AppointmentStatus.SCHEDULED);

    logger.info(
            "Creating appointment for patient ID: {} with doctor ID: {}",
            patientId,
            doctorId
    );

    return appointmentRepository.save(appointment);
}
}}}

'''Transaction behavior:''' Validation queries and appointment creation all happen within a single transaction.

----

== Database Connection Pooling ==

=== Current configuration ===

`src/main/resources/application.properties` defines the datasource, plus the explicit HikariCP pool settings:

{{{
spring.datasource.url=jdbc:postgresql://localhost:5432/medora
spring.datasource.username=postgres
spring.datasource.password=${DB_PASSWORD}
spring.datasource.driver-class-name=org.postgresql.Driver

# JPA / Hibernate settings
spring.jpa.hibernate.ddl-auto=update
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.format_sql=true
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect

# HikariCP connection pooling configuration
spring.datasource.hikari.maximum-pool-size=20
spring.datasource.hikari.minimum-idle=5
spring.datasource.hikari.connection-timeout=30000
spring.datasource.hikari.idle-timeout=600000
spring.datasource.hikari.max-lifetime=1800000
spring.datasource.hikari.leak-detection-threshold=60000
}}}

Spring Boot automatically configures HikariCP as the connection pool once a datasource is present; the block above makes pool sizing and timeout behavior explicit rather than relying on defaults.

=== Connection pool in use ===

Real startup and shutdown log output from the running application:

'''Startup:'''
{{{
2026-08-30T13:22:28.551+02:00  INFO 37848 --- [medora] [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Starting...
2026-08-30T13:22:28.673+02:00  INFO 37848 --- [medora] [           main] com.zaxxer.hikari.pool.HikariPool        : HikariPool-1 - Added connection org.postgresql.jdbc.PgConnection@6ac756b
2026-08-30T13:22:28.673+02:00  INFO 37848 --- [medora] [           main] com.zaxxer.hikari.pool.HikariPool        : HikariPool-1 - Added connection org.postgresql.jdbc.PgConnection@3f8c92e1
2026-08-30T13:22:28.675+02:00  INFO 37848 --- [medora] [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Start completed.
}}}

'''Shutdown:'''
{{{
2026-08-30T13:22:31.457+02:00  INFO 37848 --- [medora] [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Shutdown initiated...
2026-08-30T13:22:31.461+02:00  INFO 37848 --- [medora] [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Shutdown completed.
}}}

This shows the full pool lifecycle. The pool initializes on startup  and then connections are created and added, as shown with real PostgreSQL connection object references, the pool signals it's ready, and on shutdown it closes gracefully and releases its connections. With **minimum-idle=5**, additional connections are created up to that threshold during startup. Each service method annotated **@Transactional** obtains a connection from this pool for the duration of its transaction, then returns it for reuse.