= Advanced Application Development =

== Transactions ==

Medora's service layer uses **@Transactional** on methods that perform multiple related database operations. When an unchecked exception is propagated from a transactional method, Spring rolls back the database changes made during that transaction instead of leaving incomplete data behind.

The PostgreSQL database also enforces important business rules through foreign keys, custom domains, triggers, views, and stored procedures. Therefore, transaction and data-integrity rules are enforced at both the application and database levels.

=== Scenario 1: Patient creation with user account and medical record ===

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

Patient creation creates a login account, a patient profile, and a medical record in one transaction.

{{{
@Transactional
public Patient createPatient(Patient patient, String rawPassword) {
    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");
    }
    if (rawPassword == null || rawPassword.isBlank()) {
        throw new IllegalArgumentException("Password is required");
    }

    if (userRepository.existsByUsername(patient.getEmbg())) {
        throw new RuntimeException("A user account for this EMBG already exists");
    }

    User user = new User();
    user.setUsername(patient.getEmbg());
    user.setPassword(passwordEncoder.encode(rawPassword));
    user.setRole("PATIENT");
    user.setFirstName(patient.getFirstName());
    user.setLastName(patient.getLastName());
    user.setIsActive(true);

    User savedUser = userRepository.save(user);

    patient.setUser(savedUser);
    patient.setPatientId(patientRepository.findMaxPatientId() + 1);

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

    try {
        MedicalRecord medicalRecord = new MedicalRecord();
        medicalRecord.setRecordId(medicalRecordRepository.findMaxRecordId() + 1);
        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 {}",
                savedPatient.getPatientId(), e);
        throw e;
    }

    return savedPatient;
}
}}}

'''Transaction behavior:''' The user, patient, and medical-record inserts execute within the same transaction. If medical-record creation fails, the exception is logged and rethrown, allowing Spring to roll back the transaction.

The `patients.user_id` foreign key is mandatory, so the user account must be saved before the patient profile.

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

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

This service creates or updates a billing record and links performed procedures and laboratory tests for a patient on a particular 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.setRecordId(
                                    medicalRecordRepository.findMaxRecordId() + 1);

                            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);

        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.setBillId(billingRepository.findMaxBillId() + 1);
            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) {
            if (!billingProceduresRepository
                    .existsByBillingBillIdAndProcedureProcedureId(
                            savedBilling.getBillId(),
                            procedure.getProcedure().getProcedureId())) {

                BillingProcedures billingProcedure =
                        new BillingProcedures(
                                savedBilling,
                                procedure.getProcedure());

                billingProceduresRepository.save(billingProcedure);

                logger.debug("Linked procedure {} to billing {}",
                        procedure.getProcedure().getProcedureId(),
                        savedBilling.getBillId());
            }
        }

        for (PerformedLabTests labTest : labTests) {
            if (!billingLabTestsRepository
                    .existsByBillingBillIdAndLabTestTestId(
                            savedBilling.getBillId(),
                            labTest.getLabTest().getTestId())) {

                BillingLabTests billingLabTest =
                        new BillingLabTests(
                                savedBilling,
                                labTest.getLabTest());

                billingLabTestsRepository.save(billingLabTest);

                logger.debug("Linked lab test {} 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, billing creation or update, and billing-link creation all execute within one transaction. If an unchecked exception is propagated, the transaction can be rolled back.

The application calculates and initially assigns the total. However, the database triggers `trg_billing_procedures_update_total` and `trg_billing_lab_tests_update_total` recalculate `billing.total_cost` from the linked line items. Therefore, the database calculation is authoritative after the billing links are inserted.

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

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

A doctor requests a laboratory test for a patient. The request also identifies the laboratory technician and automatically starts billing for the same patient and date.

{{{
@Transactional
public PerformedLabTests requestLabTestForPatient(Long patientId,
                                                   Long doctorId,
                                                   Long testId,
                                                   Long technicianId,
                                                   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");
    }

    if (technicianId == null || technicianId <= 0) {
        throw new IllegalArgumentException("Invalid lab technician 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"));

    LabTechnician technician = labTechnicianRepository.findById(technicianId)
            .orElseThrow(() ->
                    new RuntimeException("Lab technician not found"));

    PerformedLabTests performedTest = new PerformedLabTests();

    performedTest.setPerformedTestId(
            performedLabTestRepository.findMaxPerformedTestId() + 1);

    performedTest.setPatient(patient);
    performedTest.setDoctor(doctor);
    performedTest.setLabTest(test);
    performedTest.setTechnician(technician);

    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);

    billingService.autoGenerateBillingForPatientService(
            patientId, finalTestDate);

    return saved;
}
}}}

'''Transaction behavior:''' The performed laboratory-test record and automatic billing operation execute within the same transaction. If billing generation fails and the exception is propagated, the laboratory-test request is rolled back.

The `performed_lab_tests.technician_id` column is mandatory in the database, and the Java entity reflects this:

{{{
@ManyToOne(optional = false, fetch = FetchType.LAZY)
@JoinColumn(name = "technician_id", nullable = false)
private LabTechnician technician;
}}}

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

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

The method creates a laboratory result and links it to a medical record through the `medical_record_lab_results` join table.

{{{
@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");
    }

    LocalDate finalResultDate =
            resultDate != null ? resultDate : LocalDate.now();

    if (finalResultDate.isAfter(LocalDate.now())) {
        throw new IllegalArgumentException(
                "Result date cannot be in the future");
    }

    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.setResultId(labResultsRepository.findMaxResultId() + 1);
    labResult.setResults(results);
    labResult.setResultDate(finalResultDate);
    labResult.setLabTest(labTest);

    LabResults saved = labResultsRepository.save(labResult);

    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:''' The laboratory-result insert and medical-record link execute within one transaction. If either operation fails, the exception is propagated and the transaction can roll back.

The service supplies today's date when no result date is provided and rejects future result dates. This corresponds to the database rule:

{{{
result_date DATE NOT NULL
CHECK (result_date <= CURRENT_DATE)
}}}

=== Scenario 5: Appointment creation ===

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

Appointment creation validates the patient, doctor, date, time, and availability before saving the appointment.

{{{
@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);

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

    boolean doctorBusy =
            appointmentRepository
                    .existsByDoctorDoctorIdAndAppointmentDateAndAppointmentTimeAndStatusNot(
                            doctorId,
                            appointmentDate,
                            appointmentTime,
                            AppointmentStatus.CANCELLED);

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

    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.setAppointmentId(
            appointmentRepository.findMaxAppointmentId() + 1);
    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);
}
}}}

The application performs basic validation for future dates and duplicate appointment slots. The database triggers provide additional protection by enforcing:

* no appointments in the past
* no overlapping 30-minute appointments for the same doctor
* no overlapping 30-minute appointments for the same patient
* valid appointment-status transitions
* no completion before the scheduled time

The Java enum matches the database-supported statuses:

{{{
public enum AppointmentStatus {
    SCHEDULED,
    COMPLETED,
    CANCELLED,
    IN_PROGRESS,
    NO_SHOW
}
}}}

The database procedure `job_mark_no_show()` changes appointments that remain `SCHEDULED` more than 45 minutes after their scheduled time to `NO_SHOW`.

'''Transaction behavior:''' Appointment validation queries and appointment creation execute within one transaction. Database triggers provide a second layer of protection against invalid data and overlapping appointments.

----
=== Current configuration ===

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

{{{
spring.application.name=medora
server.port=8081
spring.profiles.active=remote
spring.config.import=optional:file:.env.properties

spring.servlet.multipart.max-file-size=5MB
spring.servlet.multipart.max-request-size=6MB

# Spring Security - Development credentials
spring.security.user.name=admin
spring.security.user.password=${DB_PASSWORD}

# JWT Configuration
jwt.secret=${JWT_SECRET}
jwt.expiration=86400000

# JPA / Hibernate settings
spring.jpa.hibernate.ddl-auto=none
spring.jpa.show-sql=true
spring.jpa.open-in-view=false
spring.jpa.properties.hibernate.format_sql=true
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect

# HikariCP connection pool settings
spring.datasource.hikari.maximum-pool-size=20
spring.datasource.hikari.minimum-idle=5
spring.datasource.hikari.connection-timeout=20000
spring.datasource.hikari.idle-timeout=300000
spring.datasource.hikari.max-lifetime=1200000
spring.datasource.hikari.auto-commit=true
spring.datasource.hikari.leak-detection-threshold=60000

# Jackson serialization settings
spring.jackson.serialization.fail-on-empty-beans=false
spring.jackson.default-property-inclusion=non_null

}}}

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.

This confirms that:

* HikariCP initializes during application startup.
* PostgreSQL connections are created and added to the pool.
* Service methods annotated with `@Transactional` obtain connections from the pool while their transactions execute.
* Connections are returned to the pool after the transaction completes.
* The pool shuts down gracefully when the application stops.