Changes between Version 1 and Version 2 of AdvancedApplicationDevelopment


Ignore:
Timestamp:
09/23/26 23:25:56 (5 hours ago)
Author:
236021
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • AdvancedApplicationDevelopment

    v1 v2  
    33== Transactions ==
    44
    5 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:
    6 
    7 === Scenario 1: Patient creation with medical record ===
    8 
    9 '''File:''' `src/main/java/medora/service/PatientService.java`
    10 
    11 Creates a patient and automatically creates their medical record in the same transaction.
     5Medora'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.
     6
     7The 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.
     8
     9=== Scenario 1: Patient creation with user account and medical record ===
     10
     11'''File:''' `backend/src/main/java/medora/service/PatientService.java`
     12
     13Patient creation creates a login account, a patient profile, and a medical record in one transaction.
    1214
    1315{{{
    1416@Transactional
    15 public Patient createPatient(Patient patient) {
     17public Patient createPatient(Patient patient, String rawPassword) {
    1618    if (patient == null || patient.getEmbg() == null || patient.getEmbg().isBlank()) {
    1719        throw new IllegalArgumentException("Patient EMBG is required");
    … …  
    2325        throw new IllegalArgumentException("Patient last name is required");
    2426    }
     27    if (rawPassword == null || rawPassword.isBlank()) {
     28        throw new IllegalArgumentException("Password is required");
     29    }
     30
     31    if (userRepository.existsByUsername(patient.getEmbg())) {
     32        throw new RuntimeException("A user account for this EMBG already exists");
     33    }
     34
     35    User user = new User();
     36    user.setUsername(patient.getEmbg());
     37    user.setPassword(passwordEncoder.encode(rawPassword));
     38    user.setRole("PATIENT");
     39    user.setFirstName(patient.getFirstName());
     40    user.setLastName(patient.getLastName());
     41    user.setIsActive(true);
     42
     43    User savedUser = userRepository.save(user);
     44
     45    patient.setUser(savedUser);
     46    patient.setPatientId(patientRepository.findMaxPatientId() + 1);
    2547
    2648    logger.info("Creating new patient with EMBG: {}", patient.getEmbg());
    2749    Patient savedPatient = patientRepository.save(patient);
    2850
    29    
    3051    try {
    3152        MedicalRecord medicalRecord = new MedicalRecord();
     53        medicalRecord.setRecordId(medicalRecordRepository.findMaxRecordId() + 1);
    3254        medicalRecord.setPatient(savedPatient);
    3355        medicalRecordRepository.save(medicalRecord);
    34         logger.info("Created medical record for patient ID: {}", savedPatient.getPatientId());
     56
     57        logger.info("Created medical record for patient ID: {}",
     58                savedPatient.getPatientId());
    3559    } catch (Exception e) {
    36         logger.error("Failed to create medical record for patient: {}", e.getMessage());
     60        logger.error("Failed to create medical record for patient {}",
     61                savedPatient.getPatientId(), e);
     62        throw e;
    3763    }
    3864
    … …  
    4167}}}
    4268
    43 '''Transaction behavior:''' both `patientRepository.save()` and `medicalRecordRepository.save()` execute within the same transaction. If either fails, both are rolled back.
     69'''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.
     70
     71The `patients.user_id` foreign key is mandatory, so the user account must be saved before the patient profile.
    4472
    4573=== Scenario 2: Auto generate billing from procedures & lab tests ===
    4674
    47 '''File:''' `src/main/java/medora/service/BillingService.java`
    48 
    49 Creates a billing record and links every performed procedure and lab test for a patient on a given date.
     75'''File:''' `backend/src/main/java/medora/service/BillingService.java`
     76
     77This service creates or updates a billing record and links performed procedures and laboratory tests for a patient on a particular date.
    5078
    5179{{{
    5280@Transactional
    53 public void autoGenerateBillingForPatientService(Long patientId, LocalDate serviceDate) {
     81public void autoGenerateBillingForPatientService(Long patientId,
     82                                                   LocalDate serviceDate) {
    5483    try {
    5584        if (patientId == null || patientId <= 0) {
    5685            throw new IllegalArgumentException("Patient ID must be valid");
    5786        }
     87
    5888        if (serviceDate == null) {
    5989            throw new IllegalArgumentException("Service date must be valid");
    6090        }
    6191
    62         logger.info("Starting auto-billing for patient {} on date {}", patientId, serviceDate);
    63 
    64         MedicalRecord medicalRecord = medicalRecordRepository.findByPatientPatientId(patientId)
    65                 .orElseGet(() -> {
    66                     logger.info("Creating new medical record for patient {}", patientId);
    67                     MedicalRecord newRecord = new MedicalRecord();
    68                     newRecord.setPatient(patientRepository.findById(patientId)
    69                             .orElseThrow(() -> new RuntimeException("Patient not found with ID: " + patientId)));
    70                     return medicalRecordRepository.save(newRecord);
    71                 });
    72 
    73         logger.info("Using medical record {} for patient {}", medicalRecord.getRecordId(), patientId);
    74 
    75         List<PerformedProcedures> procedures = performedProcedureRepository.findByPatientAndDate(patientId, serviceDate);
    76         List<PerformedLabTests> labTests = performedLabTestRepository.findByPatientAndDate(patientId, serviceDate);
     92        logger.info("Starting auto-billing for patient {} on date {}",
     93                patientId, serviceDate);
     94
     95        MedicalRecord medicalRecord =
     96                medicalRecordRepository.findByPatientPatientId(patientId)
     97                        .orElseGet(() -> {
     98                            logger.info("Creating new medical record for patient {}",
     99                                    patientId);
     100
     101                            MedicalRecord newRecord = new MedicalRecord();
     102                            newRecord.setRecordId(
     103                                    medicalRecordRepository.findMaxRecordId() + 1);
     104
     105                            newRecord.setPatient(
     106                                    patientRepository.findById(patientId)
     107                                            .orElseThrow(() ->
     108                                                    new RuntimeException(
     109                                                            "Patient not found with ID: "
     110                                                                    + patientId)));
     111
     112                            return medicalRecordRepository.save(newRecord);
     113                        });
     114
     115        logger.info("Using medical record {} for patient {}",
     116                medicalRecord.getRecordId(), patientId);
     117
     118        List<PerformedProcedures> procedures =
     119                performedProcedureRepository.findByPatientAndDate(
     120                        patientId, serviceDate);
     121
     122        List<PerformedLabTests> labTests =
     123                performedLabTestRepository.findByPatientAndDate(
     124                        patientId, serviceDate);
    77125
    78126        logger.info("Found {} procedures and {} lab tests for patient {} on {}",
    … …  
    80128
    81129        if (procedures.isEmpty() && labTests.isEmpty()) {
    82             logger.info("No procedures or lab tests found for patient {} on {}", patientId, serviceDate);
     130            logger.info("No procedures or lab tests found for patient {} on {}",
     131                    patientId, serviceDate);
    83132            return;
    84133        }
    … …  
    87136                .map(p -> {
    88137                    BigDecimal cost = p.getProcedure().getCost();
    89                     logger.debug("Procedure {} cost: {}", p.getProcedure().getProcedureId(), cost);
     138
     139                    logger.debug("Procedure {} cost: {}",
     140                            p.getProcedure().getProcedureId(), cost);
     141
    90142                    return cost;
    91143                })
    … …  
    95147                .map(lt -> {
    96148                    BigDecimal cost = lt.getLabTest().getCost();
    97                     logger.debug("Lab test {} cost: {}", lt.getLabTest().getTestId(), cost);
     149
     150                    logger.debug("Lab test {} cost: {}",
     151                            lt.getLabTest().getTestId(), cost);
     152
    98153                    return cost;
    99154                })
    … …  
    101156
    102157        BigDecimal totalCost = procedureCost.add(labTestCost);
    103         logger.info("Total cost calculation: procedures={}, labTests={}, total={}", procedureCost, labTestCost, totalCost);
    104 
    105         // Get default admin (first admin in system) - skip billing if none found
     158
     159        logger.info("Total cost calculation: procedures={}, labTests={}, total={}",
     160                procedureCost, labTestCost, totalCost);
     161
    106162        Optional<Admin> adminOptional = adminRepository.findAll()
    107163                .stream()
    … …  
    109165
    110166        if (adminOptional.isEmpty()) {
    111             logger.warn("No admin found in system - skipping automatic billing generation for patient {} on {}", patientId, serviceDate);
     167            logger.warn("No admin found in system - skipping automatic billing "
     168                    + "generation for patient {} on {}",
     169                    patientId, serviceDate);
    112170            return;
    113171        }
    … …  
    115173        Admin admin = adminOptional.get();
    116174
    117         Billing billing = billingRepository.findBillingForPatientOnDate(patientId, serviceDate);
     175        Billing billing = billingRepository.findBillingForPatientOnDate(
     176                patientId, serviceDate);
    118177
    119178        if (billing != null) {
    120             logger.info("Billing record {} already exists for patient {} on {}, updating with new total",
     179            logger.info("Billing record {} already exists for patient {} on {}, "
     180                    + "updating with new total",
    121181                    billing.getBillId(), patientId, serviceDate);
     182
    122183            billing.setTotalCost(totalCost);
    123184        } else {
    124185            billing = new Billing();
     186            billing.setBillId(billingRepository.findMaxBillId() + 1);
    125187            billing.setMedicalRecord(medicalRecord);
    126188            billing.setAdmin(admin);
    … …  
    129191            billing.setPaymentDate(serviceDate);
    130192
    131             logger.info("Creating new billing record for patient {} on {}", patientId, serviceDate);
     193            logger.info("Creating new billing record for patient {} on {}",
     194                    patientId, serviceDate);
    132195        }
    133196
    134197        Billing savedBilling = billingRepository.save(billing);
     198
    135199        logger.info("Billing record {} for patient {} on {} with total cost: {}",
    136200                savedBilling.getBillId(), patientId, serviceDate, totalCost);
    137201
    138202        for (PerformedProcedures procedure : procedures) {
    139             try {
    140                 BillingProcedures billingProcedure = new BillingProcedures(savedBilling, procedure.getProcedure());
     203            if (!billingProceduresRepository
     204                    .existsByBillingBillIdAndProcedureProcedureId(
     205                            savedBilling.getBillId(),
     206                            procedure.getProcedure().getProcedureId())) {
     207
     208                BillingProcedures billingProcedure =
     209                        new BillingProcedures(
     210                                savedBilling,
     211                                procedure.getProcedure());
     212
    141213                billingProceduresRepository.save(billingProcedure);
    142                 logger.debug("Linked procedure {} to billing {}", procedure.getProcedure().getProcedureId(), savedBilling.getBillId());
    143             } catch (Exception e) {
    144                 logger.debug("Procedure {} already linked to billing {}", procedure.getProcedure().getProcedureId(), savedBilling.getBillId());
     214
     215                logger.debug("Linked procedure {} to billing {}",
     216                        procedure.getProcedure().getProcedureId(),
     217                        savedBilling.getBillId());
    145218            }
    146219        }
    147220
    148221        for (PerformedLabTests labTest : labTests) {
    149             try {
    150                 BillingLabTests billingLabTest = new BillingLabTests(savedBilling, labTest.getLabTest());
     222            if (!billingLabTestsRepository
     223                    .existsByBillingBillIdAndLabTestTestId(
     224                            savedBilling.getBillId(),
     225                            labTest.getLabTest().getTestId())) {
     226
     227                BillingLabTests billingLabTest =
     228                        new BillingLabTests(
     229                                savedBilling,
     230                                labTest.getLabTest());
     231
    151232                billingLabTestsRepository.save(billingLabTest);
    152                 logger.debug("Linked lab test {} to billing {}", labTest.getLabTest().getTestId(), savedBilling.getBillId());
    153             } catch (Exception e) {
    154                 logger.debug("Lab test {} already linked to billing {}", labTest.getLabTest().getTestId(), savedBilling.getBillId());
     233
     234                logger.debug("Linked lab test {} to billing {}",
     235                        labTest.getLabTest().getTestId(),
     236                        savedBilling.getBillId());
    155237            }
    156238        }
    157239
    158         logger.info("Successfully processed {} procedures and {} lab tests for billing record {}",
    159                 procedures.size(), labTests.size(), savedBilling.getBillId());
     240        logger.info("Successfully processed {} procedures and {} lab tests "
     241                        + "for billing record {}",
     242                procedures.size(),
     243                labTests.size(),
     244                savedBilling.getBillId());
    160245
    161246    } catch (Exception e) {
    162         logger.error("Error in auto-billing for patient {} on date {}: {}", patientId, serviceDate, e.getMessage(), e);
     247        logger.error("Error in auto-billing for patient {} on date {}: {}",
     248                patientId, serviceDate, e.getMessage(), e);
    163249        throw e;
    164250    }
    … …  
    166252}}}
    167253
    168 **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.
    169 
     254'''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.
     255
     256The 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.
    170257
    171258=== Scenario 3: Request a lab test with automated billing ===
    172259
    173 '''File:''' `src/main/java/medora/service/LabService.java`
    174 
    175 A doctor requests a lab test for a patient, which automatically triggers billing generation.
     260'''File:''' `backend/src/main/java/medora/service/LabService.java`
     261
     262A 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.
    176263
    177264{{{
    178265@Transactional
    179266public PerformedLabTests requestLabTestForPatient(Long patientId,
    180                                                   Long doctorId,
    181                                                   Long testId,
    182                                                   LocalDate testDate,
    183                                                   String notes) {
    184     if (patientId == null || patientId <= 0)
     267                                                   Long doctorId,
     268                                                   Long testId,
     269                                                   Long technicianId,
     270                                                   LocalDate testDate,
     271                                                   String notes) {
     272    if (patientId == null || patientId <= 0) {
    185273        throw new IllegalArgumentException("Invalid patient ID");
    186 
    187     if (doctorId == null || doctorId <= 0)
     274    }
     275
     276    if (doctorId == null || doctorId <= 0) {
    188277        throw new IllegalArgumentException("Invalid doctor ID");
    189 
    190     if (testId == null || testId <= 0)
     278    }
     279
     280    if (testId == null || testId <= 0) {
    191281        throw new IllegalArgumentException("Invalid test ID");
     282    }
     283
     284    if (technicianId == null || technicianId <= 0) {
     285        throw new IllegalArgumentException("Invalid lab technician ID");
     286    }
    192287
    193288    Patient patient = patientRepository.findById(patientId)
    … …  
    200295            .orElseThrow(() -> new RuntimeException("Lab test not found"));
    201296
     297    LabTechnician technician = labTechnicianRepository.findById(technicianId)
     298            .orElseThrow(() ->
     299                    new RuntimeException("Lab technician not found"));
     300
    202301    PerformedLabTests performedTest = new PerformedLabTests();
     302
     303    performedTest.setPerformedTestId(
     304            performedLabTestRepository.findMaxPerformedTestId() + 1);
     305
    203306    performedTest.setPatient(patient);
    204307    performedTest.setDoctor(doctor);
    205308    performedTest.setLabTest(test);
    206     LocalDate finalTestDate = testDate != null ? testDate : LocalDate.now();
     309    performedTest.setTechnician(technician);
     310
     311    LocalDate finalTestDate =
     312            testDate != null ? testDate : LocalDate.now();
     313
    207314    performedTest.setTestDate(finalTestDate);
    208315    performedTest.setNotes(notes);
    209316
    210     logger.info("Lab test {} requested for patient {} by doctor {}", testId, patientId, doctorId);
    211     PerformedLabTests saved = performedLabTestRepository.save(performedTest);
    212 
    213     // Auto generate billing for the patient on this date
    214     billingService.autoGenerateBillingForPatientService(patientId, finalTestDate);
     317    logger.info("Lab test {} requested for patient {} by doctor {}",
     318            testId, patientId, doctorId);
     319
     320    PerformedLabTests saved =
     321            performedLabTestRepository.save(performedTest);
     322
     323    billingService.autoGenerateBillingForPatientService(
     324            patientId, finalTestDate);
    215325
    216326    return saved;
    … …  
    218328}}}
    219329
    220 '''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.
    221 
    222 === Scenario 4: Store lab result and link to medical record ===
    223 
    224 '''File:''' `src/main/java/medora/service/LabService.java`
    225 
    226 Stores a lab result and links it to the patient's medical record.
     330'''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.
     331
     332The `performed_lab_tests.technician_id` column is mandatory in the database, and the Java entity reflects this:
     333
     334{{{
     335@ManyToOne(optional = false, fetch = FetchType.LAZY)
     336@JoinColumn(name = "technician_id", nullable = false)
     337private LabTechnician technician;
     338}}}
     339
     340=== Scenario 4:  Store lab result and link to medical record ===
     341
     342'''File:''' `backend/src/main/java/medora/service/LabService.java`
     343
     344The method creates a laboratory result and links it to a medical record through the `medical_record_lab_results` join table.
    227345
    228346{{{
    229347@Transactional
    230348public MedicalRecordLabResults storeLabResult(Long medicalRecordId,
    231                                               Long testId,
    232                                               String results,
    233                                               LocalDate resultDate) {
    234 
    235     if (medicalRecordId == null || medicalRecordId <= 0)
    236         throw new IllegalArgumentException("Invalid medical record ID");
    237 
    238     if (testId == null || testId <= 0)
     349                                               Long testId,
     350                                               String results,
     351                                               LocalDate resultDate) {
     352    if (medicalRecordId == null || medicalRecordId <= 0) {
     353        throw new IllegalArgumentException(
     354                "Invalid medical record ID");
     355    }
     356
     357    if (testId == null || testId <= 0) {
    239358        throw new IllegalArgumentException("Invalid test ID");
    240 
    241     if (results == null || results.isBlank())
     359    }
     360
     361    if (results == null || results.isBlank()) {
    242362        throw new IllegalArgumentException("Results required");
    243 
    244     MedicalRecord medicalRecord = medicalRecordRepository.findById(medicalRecordId)
    245             .orElseThrow(() -> new RuntimeException("Medical record not found"));
     363    }
     364
     365    LocalDate finalResultDate =
     366            resultDate != null ? resultDate : LocalDate.now();
     367
     368    if (finalResultDate.isAfter(LocalDate.now())) {
     369        throw new IllegalArgumentException(
     370                "Result date cannot be in the future");
     371    }
     372
     373    MedicalRecord medicalRecord =
     374            medicalRecordRepository.findById(medicalRecordId)
     375                    .orElseThrow(() ->
     376                            new RuntimeException(
     377                                    "Medical record not found"));
    246378
    247379    LabTests labTest = labTestRepository.findById(testId)
    248             .orElseThrow(() -> new RuntimeException("Lab test not found"));
     380            .orElseThrow(() ->
     381                    new RuntimeException("Lab test not found"));
    249382
    250383    LabResults labResult = new LabResults();
     384    labResult.setResultId(labResultsRepository.findMaxResultId() + 1);
    251385    labResult.setResults(results);
    252     labResult.setResultDate(resultDate);
     386    labResult.setResultDate(finalResultDate);
    253387    labResult.setLabTest(labTest);
    254388
    255389    LabResults saved = labResultsRepository.save(labResult);
    256 
    257     // Use the join repository to safely link to avoid deleting existing links
    258     // Create and save the join entity explicitly
    259390
    260391    MedicalRecordLabResults link = new MedicalRecordLabResults();
    … …  
    262393    link.setLabResult(saved);
    263394
    264     logger.info("Stored lab result {} for medical record {}", saved.getResultId(), medicalRecordId);
     395    logger.info("Stored lab result {} for medical record {}",
     396            saved.getResultId(), medicalRecordId);
     397
    265398    return medicalRecordLabResultRepository.save(link);
    266399}
    267400}}}
    268401
    269 '''Transaction behavior:''' Both the lab result creation and the link to the medical record happen atomically. If either fails, both are rolled back.
     402'''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.
     403
     404The service supplies today's date when no result date is provided and rejects future result dates. This corresponds to the database rule:
     405
     406{{{
     407result_date DATE NOT NULL
     408CHECK (result_date <= CURRENT_DATE)
     409}}}
    270410
    271411=== Scenario 5: Appointment creation ===
    272412
    273 '''File:''' `src/main/java/medora/service/AppointmentService.java`
    274 
    275 Creates an appointment with validation for future dates, no double-booking, and no duplicate appointments.
     413'''File:''' `backend/src/main/java/medora/service/AppointmentService.java`
     414
     415Appointment creation validates the patient, doctor, date, time, and availability before saving the appointment.
    276416
    277417{{{
    … …  
    281421                                     LocalDate appointmentDate,
    282422                                     LocalTime appointmentTime) {
    283 
    284423    if (patientId == null || patientId <= 0) {
    285424        throw new IllegalArgumentException("Patient ID must be valid");
    … …  
    291430
    292431    if (appointmentDate == null) {
    293         throw new IllegalArgumentException("Appointment date is required");
     432        throw new IllegalArgumentException(
     433                "Appointment date is required");
    294434    }
    295435
    296436    if (appointmentTime == null) {
    297         throw new IllegalArgumentException("Appointment time is required");
     437        throw new IllegalArgumentException(
     438                "Appointment time is required");
    298439    }
    299440
    300441    Patient patient = patientRepository.findById(patientId)
    301442            .orElseThrow(() ->
    302                     new RuntimeException("Patient not found with ID: " + patientId));
     443                    new RuntimeException(
     444                            "Patient not found with ID: " + patientId));
    303445
    304446    Doctors doctor = doctorRepository.findById(doctorId)
    305447            .orElseThrow(() ->
    306                     new RuntimeException("Doctor not found with ID: " + doctorId));
     448                    new RuntimeException(
     449                            "Doctor not found with ID: " + doctorId));
    307450
    308451    LocalDateTime appointmentDateTime =
    309452            LocalDateTime.of(appointmentDate, appointmentTime);
    310453
    311     // Future validation
    312 
    313454    if (!appointmentDateTime.isAfter(LocalDateTime.now())) {
    314455        throw new RuntimeException(
    315                 "Appointment must be scheduled for a future date and time"
    316         );
    317     }
    318 
    319     // Doctor slot validation
     456                "Appointment must be scheduled for a future date and time");
     457    }
     458
    320459    boolean doctorBusy =
    321460            appointmentRepository
    … …  
    324463                            appointmentDate,
    325464                            appointmentTime,
    326                             AppointmentStatus.CANCELLED
    327                     );
     465                            AppointmentStatus.CANCELLED);
    328466
    329467    if (doctorBusy) {
    330         throw new RuntimeException("This appointment slot is already booked");
    331     }
    332 
    333     // Duplicate patient validation
     468        throw new RuntimeException(
     469                "This appointment slot is already booked");
     470    }
     471
    334472    boolean duplicateAppointment =
    335473            appointmentRepository
    … …  
    339477                            appointmentDate,
    340478                            appointmentTime,
    341                             AppointmentStatus.CANCELLED
    342                     );
     479                            AppointmentStatus.CANCELLED);
    343480
    344481    if (duplicateAppointment) {
    345482        throw new RuntimeException(
    346                 "Patient already has this appointment scheduled"
    347         );
     483                "Patient already has this appointment scheduled");
    348484    }
    349485
    350486    Appointment appointment = new Appointment();
    351487
     488    appointment.setAppointmentId(
     489            appointmentRepository.findMaxAppointmentId() + 1);
    352490    appointment.setPatient(patient);
    353491    appointment.setDoctor(doctor);
    … …  
    359497            "Creating appointment for patient ID: {} with doctor ID: {}",
    360498            patientId,
    361             doctorId
    362     );
     499            doctorId);
    363500
    364501    return appointmentRepository.save(appointment);
    … …  
    366503}}}
    367504
    368 '''Transaction behavior:''' Validation queries and appointment creation all happen within a single transaction.
     505The application performs basic validation for future dates and duplicate appointment slots. The database triggers provide additional protection by enforcing:
     506
     507* no appointments in the past
     508* no overlapping 30-minute appointments for the same doctor
     509* no overlapping 30-minute appointments for the same patient
     510* valid appointment-status transitions
     511* no completion before the scheduled time
     512
     513The Java enum matches the database-supported statuses:
     514
     515{{{
     516public enum AppointmentStatus {
     517    SCHEDULED,
     518    COMPLETED,
     519    CANCELLED,
     520    IN_PROGRESS,
     521    NO_SHOW
     522}
     523}}}
     524
     525The database procedure `job_mark_no_show()` changes appointments that remain `SCHEDULED` more than 45 minutes after their scheduled time to `NO_SHOW`.
     526
     527'''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.
    369528
    370529----
    371 
    372 == Database Connection Pooling ==
    373 
    374530=== Current configuration ===
    375531
    … …  
    377533
    378534{{{
    379 spring.datasource.url=jdbc:postgresql://localhost:5432/medora
    380 spring.datasource.username=postgres
    381 spring.datasource.password=${DB_PASSWORD}
    382 spring.datasource.driver-class-name=org.postgresql.Driver
     535spring.application.name=medora
     536server.port=8081
     537spring.profiles.active=remote
     538spring.config.import=optional:file:.env.properties
     539
     540spring.servlet.multipart.max-file-size=5MB
     541spring.servlet.multipart.max-request-size=6MB
     542
     543# Spring Security - Development credentials
     544spring.security.user.name=admin
     545spring.security.user.password=${DB_PASSWORD}
     546
     547# JWT Configuration
     548jwt.secret=${JWT_SECRET}
     549jwt.expiration=86400000
    383550
    384551# JPA / Hibernate settings
    385 spring.jpa.hibernate.ddl-auto=update
     552spring.jpa.hibernate.ddl-auto=none
    386553spring.jpa.show-sql=true
     554spring.jpa.open-in-view=false
    387555spring.jpa.properties.hibernate.format_sql=true
    388556spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect
    389557
    390 # HikariCP connection pooling configuration
     558# HikariCP connection pool settings
    391559spring.datasource.hikari.maximum-pool-size=20
    392560spring.datasource.hikari.minimum-idle=5
    393 spring.datasource.hikari.connection-timeout=30000
    394 spring.datasource.hikari.idle-timeout=600000
    395 spring.datasource.hikari.max-lifetime=1800000
     561spring.datasource.hikari.connection-timeout=20000
     562spring.datasource.hikari.idle-timeout=300000
     563spring.datasource.hikari.max-lifetime=1200000
     564spring.datasource.hikari.auto-commit=true
    396565spring.datasource.hikari.leak-detection-threshold=60000
     566
     567# Jackson serialization settings
     568spring.jackson.serialization.fail-on-empty-beans=false
     569spring.jackson.default-property-inclusion=non_null
     570
    397571}}}
    398572
    … …  
    404578
    405579'''Startup:'''
    406 {{{
    407 2026-08-30T13:22:28.551+02:00  INFO 37848 --- [medora] [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Starting...
    408 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
    409 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
    410 2026-08-30T13:22:28.675+02:00  INFO 37848 --- [medora] [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Start completed.
     580
     581{{{
     5822026-08-30T13:22:28.551+02:00 INFO 37848 --- [medora] [           main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Starting...
     5832026-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
     5842026-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
     5852026-08-30T13:22:28.675+02:00 INFO 37848 --- [medora] [           main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Start completed.
    411586}}}
    412587
    413588'''Shutdown:'''
    414 {{{
    415 2026-08-30T13:22:31.457+02:00  INFO 37848 --- [medora] [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Shutdown initiated...
    416 2026-08-30T13:22:31.461+02:00  INFO 37848 --- [medora] [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Shutdown completed.
    417 }}}
    418 
    419 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.
    420 
     589
     590{{{
     5912026-08-30T13:22:31.457+02:00 INFO 37848 --- [medora] [           main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Shutdown initiated...
     5922026-08-30T13:22:31.461+02:00 INFO 37848 --- [medora] [           main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Shutdown completed.
     593}}}
     594
     595This 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.
     596
     597This confirms that:
     598
     599* HikariCP initializes during application startup.
     600* PostgreSQL connections are created and added to the pool.
     601* Service methods annotated with `@Transactional` obtain connections from the pool while their transactions execute.
     602* Connections are returned to the pool after the transaction completes.
     603* The pool shuts down gracefully when the application stops.