= Other Topics =

== SQL Performance ==

=== Scenario 1: Appointments Table Filtering ===

This scenario tests a '''single-table range filter''' on a large table with ~50% selectivity, where an index on status and date can significantly improve scan performance over a sequential scan.

Making appointments table have 1 million rows:

{{{
WITH patient_ids AS (
    SELECT array_agg(patient_id) AS ids FROM patients
),
doctor_ids AS (
    SELECT array_agg(doctor_id) AS ids FROM doctors
),
base AS (
    SELECT
        gs,
        CURRENT_DATE + ((random() * 240 - 120)::INT) AS gen_date
    FROM generate_series(1, 1000000) gs
)
INSERT INTO appointments (appointment_id, appointment_date, appointment_time, status, patient_id, doctor_id)
SELECT
    (SELECT COALESCE(MAX(appointment_id), 0) FROM appointments) + gs,
    gen_date,
    make_time((floor(random() * 24))::INT, (floor(random() * 60))::INT, 0)::TIME,
    CASE
        WHEN gen_date < CURRENT_DATE THEN
            (ARRAY['COMPLETED', 'CANCELLED', 'IN_PROGRESS'])[floor(random()*3)+1]
        ELSE
            'SCHEDULED'
        END,
    (SELECT ids[floor(random()*array_length(ids,1))+1] FROM patient_ids),
    (SELECT ids[floor(random()*array_length(ids,1))+1] FROM doctor_ids)
FROM base;
}}}

I disabled the `trigger_appointments_no_overlap` and `trigger_appointments_enforce` triggers for easier bulk testing (the overlap and past-date checks would otherwise reject most of the synthetic rows), and re-enabled both immediately after the insert completed.

Final data was verified to have realistic ~50% selectivity for the target query before benchmarking:

{{{
total = 1,000,014
scheduled = 502,677
future_dated = 502,671
matching filter (status = 'SCHEDULED' AND appointment_date >= CURRENT_DATE) = 502,671
}}}

Executing the query 10 times plus 1 discarded warm-up run to see average execution time:

{{{
EXPLAIN (ANALYZE, BUFFERS)
SELECT appointment_id, appointment_date, appointment_time, status, patient_id, doctor_id
FROM appointments
WHERE status = 'SCHEDULED'
  AND appointment_date >= CURRENT_DATE;
}}}

After the first recorded execution:

{{{
Seq Scan on appointments  (cost=0.00..41790.90 rows=302562 width=46) (actual time=0.012..171.250 rows=502671.00 loops=1)
  Filter: (((status)::text = 'SCHEDULED'::text) AND (appointment_date >= CURRENT_DATE))
  Rows Removed by Filter: 497343
  Buffers: shared hit=11270 read=9349
Planning Time: 0.068 ms
Execution Time: 192.175 ms
}}}

'''Average execution time WITHOUT index''': 177.60ms

Created the index:

{{{
CREATE INDEX idx_appointments_status_date
    ON appointments (status, appointment_date DESC);
}}}

After the first recorded execution with the index present:

{{{
Bitmap Heap Scan on appointments  (cost=3455.86..28451.45 rows=250091 width=46) (actual time=13.507..61.849 rows=502671.00 loops=1)
  Recheck Cond: (((status)::text = 'SCHEDULED'::text) AND (appointment_date >= CURRENT_DATE))
  Heap Blocks: exact=10310
  Buffers: shared hit=10750
  ->  Bitmap Index Scan on idx_appointments_status_date  (cost=0.00..3393.34 rows=250091 width=0) (actual time=12.486..12.487 rows=502671.00 loops=1)
        Index Cond: (((status)::text = 'SCHEDULED'::text) AND (appointment_date >= CURRENT_DATE))
        Index Searches: 1
        Buffers: shared hit=440
Planning Time: 0.093 ms
Execution Time: 78.882 ms
}}}

'''Average execution time WITH index''': 85.23ms

'''Improvement: ~52.0%'''

Because the execution time has nearly halved and the query planner independently switched from a Seq Scan to a Bitmap Heap Scan using the new index (disk buffer reads dropped from ~9,000 to ~440 per execution), we will keep the index.

=== Scenario 2: Performed Lab Tests Date Range Query ===

This scenario tests a '''date-range filter''' on a very large table contaning 2M rows with lower selectivity (~8%), where an index on the date column can reduce I/O significantly despite the planner's initial preference for parallel sequential scans.

Making performed_lab_tests table have 2 million rows:

{{{
WITH test_ids AS (
    SELECT array_agg(test_id) AS ids FROM lab_tests
),
patient_ids AS (
    SELECT array_agg(patient_id) AS ids FROM patients
),
doctor_ids AS (
    SELECT array_agg(doctor_id) AS ids FROM doctors
),
technician_ids AS (
    SELECT array_agg(technician_id) AS ids FROM lab_technician
)
INSERT INTO performed_lab_tests (performed_test_id, test_id, patient_id, doctor_id, technician_id, test_date, notes)
SELECT
    (SELECT COALESCE(MAX(performed_test_id), 0) FROM performed_lab_tests) + gs,
    (SELECT ids[floor(random()*array_length(ids,1))+1] FROM test_ids),
    (SELECT ids[floor(random()*array_length(ids,1))+1] FROM patient_ids),
    (SELECT ids[floor(random()*array_length(ids,1))+1] FROM doctor_ids),
    (SELECT ids[floor(random()*array_length(ids,1))+1] FROM technician_ids),
    CURRENT_DATE - (random() * 90)::INT,
    'Performance test - auto-generated'
FROM generate_series(1, 2000000) gs;
}}}

No triggers exist on `performed_lab_tests`, so no disable/re-enable step was needed for this table.

Final data was verified to have realistic ~8-9% selectivity for the target query before benchmarking (7 days out of a 90-day distribution):

{{{
total = 2,000,032
matching filter (test_date >= CURRENT_DATE - INTERVAL '7 days' AND test_date <= CURRENT_DATE) = 167,547
selectivity ≈ 8.4%
}}}

Executing the query 10 times (plus 1 discarded warm-up run) to see average execution time:

{{{
EXPLAIN (ANALYZE, BUFFERS)
SELECT performed_test_id, test_id, patient_id, doctor_id, test_date
FROM performed_lab_tests
WHERE test_date >= CURRENT_DATE - INTERVAL '7 days'
  AND test_date <= CURRENT_DATE;
}}}

After the first recorded execution:

{{{
Gather  (cost=1000.00..63198.60 rows=167803 width=36) (actual time=0.832..189.052 rows=167547.00 loops=1)
  Workers Planned: 2
  Workers Launched: 2
  Buffers: shared hit=1948 read=24720
  ->  Parallel Seq Scan on performed_lab_tests  (cost=0.00..45418.30 rows=69918 width=36) (actual time=0.357..138.538 rows=55849.00 loops=3)
        Filter: ((test_date <= CURRENT_DATE) AND (test_date >= (CURRENT_DATE - '7 days'::interval)))
        Rows Removed by Filter: 610828
        Buffers: shared hit=1948 read=24720
Planning Time: 0.087 ms
Execution Time: 200.140 ms
}}}

'''Average execution time WITHOUT index''': 203.30ms

Created the index:

{{{
CREATE INDEX idx_performed_lab_tests_date
    ON performed_lab_tests (test_date DESC);
}}}

After the first recorded execution with the index present:

{{{
Bitmap Heap Scan on performed_lab_tests  (cost=2292.42..32735.98 rows=167803 width=36) (actual time=15.679..153.700 rows=167547.00 loops=1)
  Recheck Cond: ((test_date >= (CURRENT_DATE - '7 days'::interval)) AND (test_date <= CURRENT_DATE))
  Heap Blocks: exact=26626
  Buffers: shared read=26770 written=6
  ->  Bitmap Index Scan on idx_performed_lab_tests_date  (cost=0.00..2250.46 rows=167803 width=0) (actual time=12.123..12.124 rows=167547.00 loops=1)
        Index Cond: ((test_date >= (CURRENT_DATE - '7 days'::interval)) AND (test_date <= CURRENT_DATE))
        Index Searches: 1
        Buffers: shared read=144
Planning Time: 0.092 ms
Execution Time: 165.364 ms
}}}

'''Average execution time WITH index''': 152.66ms

'''Improvement: ~24.9%'''

Because the execution time improved by ~25% and the query planner independently abandoned its parallel sequential scan strategy (2 workers) in favor of a Bitmap Heap Scan using the new index, we will keep the index. This is a smaller improvement than Scenario 1, which is expected: this scenario tests a much lower selectivity (~8.4% vs ~50%), and the planner's willingness to abandon a parallel scan strategy — normally one of its strongest non-index options — for the index is itself a strong signal the index is genuinely earning its keep here, not just marginally useful.

=== Scenario 3: Billing Table with JOIN and ORDER BY ===

This scenario tests a JOIN across multiple tables with a filter and a sort. It shows two things: a composite index on the filter column doesn't help if the query wraps that column in a calculation, and even after rewriting the filter to compare the column directly and the index still isn't used because too large a share of the table matches the filter, so a full scan stays cheaper than using the index.

Making billing table have 1 million rows:

{{{
WITH record_ids AS (
    SELECT array_agg(record_id) AS ids FROM medical_records
),
base AS (
    SELECT
        gs,
        (ARRAY['PENDING', 'PAID', 'CANCELLED'])[floor(random()*3)+1] AS status,
        (NOW() - (random() * INTERVAL '180 days'))::TIMESTAMP AS created_date
    FROM generate_series(1, 1000000) gs
)
INSERT INTO billing (bill_id, record_id, total_cost, payment_status, payment_date, created_at)
SELECT
    (SELECT COALESCE(MAX(bill_id), 0) FROM billing) + gs,
    (SELECT ids[floor(random()*array_length(ids,1))+1] FROM record_ids),
    (random() * 2000 + 20)::numeric(12,2),
    status,
    CASE WHEN status = 'PAID' THEN (created_date + (random() * 30 || ' days')::INTERVAL)::DATE ELSE NULL END,
    created_date
FROM base;
}}}

No triggers fire on `INSERT` for the `billing` table (`trg_billing_status_transition` only fires on `UPDATE`), so no disable/re-enable step was needed.

Final data was verified to have ~28% selectivity for the target query before benchmarking:

{{{
total = 1,000,022
pending_count = 333,340
matching filter (payment_status = 'PENDING' AND CURRENT_DATE - created_at::DATE >= 30) = 278,616
selectivity ≈ 27.9%
}}}

Unlike Scenarios 1 and 2, this scenario tests a JOIN across three tables (`billing`, `medical_records`, `patients`) with a filter ''and'' an `ORDER BY` on a value derived from the indexed column, to see whether a composite index can eliminate both the filter scan and the sort step in one pass.

{{{
EXPLAIN (ANALYZE, BUFFERS)
SELECT
    b.bill_id,
    p.patient_id, p.first_name, p.last_name,
    b.total_cost, b.payment_status, b.created_at,
    CURRENT_DATE - b.created_at::DATE AS days_outstanding
FROM billing b
JOIN medical_records mr ON b.record_id = mr.record_id
JOIN patients p ON mr.patient_id = p.patient_id
WHERE b.payment_status = 'PENDING'
  AND CURRENT_DATE - b.created_at::DATE >= 30
ORDER BY days_outstanding DESC;
}}}

After the first recorded execution (without index):

{{{
Nested Loop  (cost=22776.01..39200.16 rows=110425 width=1073) (actual time=117.677..330.399 rows=278616.00 loops=1)
  ->  Gather Merge  (cost=22775.86..35636.67 rows=110425 width=37) (actual time=117.618..201.318 rows=278616.00 loops=1)
        Workers Planned: 2
        Workers Launched: 2
        ->  Sort  (cost=21775.84..21890.86 rows=46010 width=37) (actual time=84.710..95.540 rows=92872.00 loops=3)
              Sort Key: ((CURRENT_DATE - (b.created_at)::date)) DESC
              Sort Method: external merge  Disk: 4816kB
              ->  Hash Join  (cost=1.34..18212.44 rows=46010 width=37) (actual time=0.323..54.310 rows=92872.00 loops=3)
                    Hash Cond: (b.record_id = mr.record_id)
                    ->  Parallel Seq Scan on billing b  (cost=0.00..18056.21 rows=46010 width=37) (actual time=0.249..37.559 rows=92872.00 loops=3)
                          Filter: (((payment_status)::text = 'PENDING'::text) AND ((CURRENT_DATE - (created_at)::date) >= 30))
                          Rows Removed by Filter: 240469
                    ->  Hash  (cost=1.15..1.15 rows=15 width=16)
                          ->  Seq Scan on medical_records mr  (cost=0.00..1.15 rows=15 width=16)
  ->  Memoize (Cache Key: mr.patient_id)
        ->  Index Scan using patients_pk on patients p
Planning Time: 0.174 ms
Execution Time: 345.700 ms
}}}

'''Average execution time WITHOUT index''' (n=3): 354.67ms

Created the index:

{{{
CREATE INDEX idx_billing_status_created
    ON billing (payment_status, created_at DESC);
}}}

Re-ran the identical query with the index present. The plan was unchanged, still a `Parallel Seq Scan` on `billing`, no reference to `idx_billing_status_created` anywhere in the plan:

{{{
Nested Loop  (cost=22776.01..39200.16 rows=110425 width=1073) (actual time=116.693..322.849 rows=278616.00 loops=1)
  ->  Gather Merge  (cost=22775.86..35636.67 rows=110425 width=37) (actual time=116.660..198.802 rows=278616.00 loops=1)
        ...
        ->  Sort  (cost=21775.84..21890.86 rows=46010 width=37) (actual time=85.462..96.482 rows=92872.00 loops=3)
              Sort Key: ((CURRENT_DATE - (b.created_at)::date)) DESC
              Sort Method: external merge  Disk: 4776kB
              ->  Hash Join  (cost=1.34..18212.44 rows=46010 width=37) (actual time=0.331..54.864 rows=92872.00 loops=3)
                    ->  Parallel Seq Scan on billing b  (cost=0.00..18056.21 rows=46010 width=37) (actual time=0.255..38.297 rows=92872.00 loops=3)
                          Filter: (((payment_status)::text = 'PENDING'::text) AND ((CURRENT_DATE - (created_at)::date) >= 30))
                          Rows Removed by Filter: 240469
...
Planning Time: 0.186 ms
Execution Time: 337.457 ms
}}}

'''Average execution time WITH index, same query as written''': 346.13ms so no meaningful change from baseline, and no index usage in the plan.

**Why the index wasn't used:** the filter CURRENT_DATE - b.created_at::DATE >= 30 does math on the indexed column before comparing it. PostgreSQL can't turn a calculation like that into an index lookup, so it evaluates the filter row-by-row with a full scan instead so whether or not a matching index exists.

Scenario 3b - rewriting the filter so the index can be used

To find out whether the index itself was the problem, or just how the filter was written, the query was rewritten to compare created_at directly, with no calculation around it:

{{{
EXPLAIN (ANALYZE, BUFFERS)
SELECT
    b.bill_id,
    p.patient_id, p.first_name, p.last_name,
    b.total_cost, b.payment_status, b.created_at,
    CURRENT_DATE - b.created_at::DATE AS days_outstanding
FROM billing b
JOIN medical_records mr ON b.record_id = mr.record_id
JOIN patients p ON mr.patient_id = p.patient_id
WHERE b.payment_status = 'PENDING'
  AND b.created_at <= (CURRENT_DATE - 30)
ORDER BY days_outstanding DESC;
}}}

{{{
Nested Loop  (cost=31149.76..71980.89 rows=274670 width=1073) (actual time=111.568..296.262 rows=276804.00 loops=1)
  ->  Gather Merge  (cost=31149.61..63139.46 rows=274670 width=37) (actual time=111.517..185.352 rows=276804.00 loops=1)
        ->  Sort  (cost=30149.59..30435.70 rows=114446 width=37) (actual time=81.512..90.942 rows=92268.00 loops=3)
              Sort Key: ((CURRENT_DATE - (b.created_at)::date)) DESC
              Sort Method: external merge  Disk: 4768kB
              ->  Hash Join  (cost=1.34..17401.16 rows=114446 width=37) (actual time=0.127..50.843 rows=92268.00 loops=3)
                    ->  Parallel Seq Scan on billing b  (cost=0.00..17014.52 rows=114446 width=37) (actual time=0.054..34.252 rows=92268.00 loops=3)
                          Filter: (((payment_status)::text = 'PENDING'::text) AND (created_at <= (CURRENT_DATE - 30)))
                          Rows Removed by Filter: 241073
...
Planning Time: 0.213 ms
Execution Time: 310.265 ms
}}}

'''Average execution time WITH index, sargable filter''': 319.74ms

Even with the rewrite, the plan still shows Parallel Seq Scan the index still isn't used. The ~10% speed-up comes from the filter itself being cheaper to check (a plain comparison instead of a date calculation on every row), not from using the index.

'''Overall Scenario 3 conclusion'''

At ~27.9% selectivity, `idx_billing_status_created` was never picked by the planner, in any of the three versions tested — the original filter, the original filter with the index added, or the rewritten sargable filter with the index. This matches what we saw with `idx_performed_lab_tests_date` in Scenario 2: once a large enough share of the table matches the filter, PostgreSQL decides a full scan is cheaper than using the index. Rewriting the filter to be sargable is still worth doing because it gave a real ~10% speed-up, but it didn't make the index get used here.

'''Decision: DROP''' `idx_billing_status_created` for this query as currently written, or as a general index because it adds write overhead to every future `INSERT`/`UPDATE` on `billing` without providing any read benefit for this report.

=== Scenario 4: Appointments Trigger Write-Path Optimization ===

'''This scenario tests whether an index can optimize '''write-path trigger performance''', specifically, can a composite index reduce the cost of overlap-checking subqueries that run on every insert, rather than speeding up a read query.

Unlike Scenarios 1-3 (all SELECT/read-path queries), this scenario tests something structurally different: whether an index reduces the cost of a '''write-path trigger'''. The `trigger_appointments_no_overlap` trigger runs two `EXISTS` subqueries, a doctor-side check and a patient-side check on every `INSERT` into `appointments`, to prevent double-booking. Since `EXPLAIN ANALYZE` on an `INSERT` doesn't break out trigger execution time separately, this was measured by timing individual `INSERT` statements end-to-end (wall-clock, via `clock_timestamp()`), 1 discarded warm-up + 10 recorded inserts per condition, each using a distinct far-future date so no insert collides with existing data or with the other test rows.

{{{
CREATE INDEX idx_appointments_doctor_datetime
    ON appointments (doctor_id, appointment_date, appointment_time);
}}}

'''Baseline (10 single-row inserts, WITHOUT index):'''

{{{
230.834, 215.470, 224.593, 292.890, 259.240, 303.525, 275.669, 281.612, 256.227, 258.670  (ms)
Average: 259.873 ms
}}}

'''Indexed (10 single-row inserts, WITH index):'''

{{{
319.400, 277.986, 273.792, 264.343, 268.986, 234.145, 249.148, 254.938, 246.849, 210.346  (ms)
Average: 259.993 ms
}}}

'''Improvement: -0.05%''' - no meaningful difference; if anything, marginally slower.

'''Why the index made no difference:''' `t1_appointments_no_overlap()` runs two separate overlap checks, one filtered on `doctor_id`, one filtered on `patient_id`. `idx_appointments_doctor_datetime` only supports the doctor-side check. The patient-side check has no supporting index at all and remains a full scan on every insert regardless. Since both checks run on every insert, the unindexed patient-side check dominates the total time, masking any improvement on the doctor side.

'''Decision: DROP''' `idx_appointments_doctor_datetime` as currently designed. A single-sided index cannot meaningfully help a trigger that checks both sides of a relationship — a real fix would require a matching index on `patient_id` as well, which is outside the scope of what was tested here.


=== Scenario 5: Referral Loop Detection via Recursive CTE ===

'''This scenario tests a '''recursive graph traversal''' where the same join is repeated at every recursion level, so a modest per-lookup saving in the index compounds across all levels—the opposite of Scenario 3, where a high-selectivity filter made the planner prefer a full scan.

This scenario is different again: it uses a recursive CTE to follow chains of doctor-to-doctor referrals and find indirect loops (Dr. A refers to Dr. B, who refers to Dr. C, who refers back to Dr. A). This matters because the existing trigger, `trg_referral_consistency`, only catches a doctor referring directly to themselves — it can't catch a multi-hop loop, since each individual INSERT looks fine on its own. Spotting a loop like this means tracing paths across the whole table, which only a query can do — a row-level trigger can't see far enough.

{{{
WITH RECURSIVE referral_chain AS (
    SELECT
        r.referral_id, r.from_doctor_id, r.to_doctor_id, r.referral_date,
        ARRAY[r.from_doctor_id, r.to_doctor_id] AS path, 1 AS depth
    FROM referrals r
    UNION ALL
    SELECT
        r.referral_id, rc.from_doctor_id, r.to_doctor_id, r.referral_date,
        rc.path || r.to_doctor_id, rc.depth + 1
    FROM referrals r
    JOIN referral_chain rc ON r.from_doctor_id = rc.to_doctor_id
    WHERE rc.depth < 5
      AND r.to_doctor_id <> ALL(rc.path[2:])
)
SELECT rc.from_doctor_id, d1.first_name, d1.last_name, rc.depth, rc.path, rc.referral_date
FROM referral_chain rc
JOIN doctors d1 ON d1.doctor_id = rc.from_doctor_id
WHERE rc.depth > 1 AND rc.to_doctor_id = rc.from_doctor_id
ORDER BY rc.depth DESC, rc.referral_date DESC
LIMIT 50;
}}}

**Data setup and correctness check:** the `referrals` table started with only 13 rows — far too small for an index to matter, and with no real loops in it, so the query had never actually caught a positive case. The table was bulk-populated to ~50,000 rows using the same insert pattern as earlier scenarios, then a guaranteed 3-hop loop was seeded by hand (Dr. 1 → Dr. 2 → Dr. 3 → Dr. 1). Re-running the query confirmed it catches the seeded loop, returning it once from each doctor's point of view in the cycle — which is what you'd expect from how the traversal works:

{{{
from_doctor_id=2, to_doctor_id=2, depth=3, path={2,3,1,2}
from_doctor_id=3, to_doctor_id=3, depth=3, path={3,1,2,3}
from_doctor_id=1, to_doctor_id=1, depth=3, path={1,2,3,1}
}}}

'''Baseline (10 runs, WITHOUT index):'''

{{{
Merge Join  (cost=36388.55..1381897.00 ...)
  Merge Cond: (r_1.from_doctor_id = rc_1.to_doctor_id)
  ->  Sort  (cost=4972.93..5097.97 rows=50016 width=28) (actual time=13.909..16.054 rows=40013.00 loops=5)
        Sort Key: r_1.from_doctor_id
        Sort Method: quicksort  Memory: 3710kB
        ->  Seq Scan on referrals r_1  (cost=0.00..1069.16 rows=50016 width=28) ...
}}}

Execution times (ms): 169.772, 192.549, 165.460, 171.478, 172.596, 172.093, 165.839, 150.697, 191.878, 149.140

'''Average execution time WITHOUT index''': 170.15ms

Created the index:

{{{
CREATE INDEX idx_referrals_from_doctor
    ON referrals (from_doctor_id, to_doctor_id, referral_date);
}}}

'''Indexed (10 runs, WITH index):'''

{{{
Limit  (cost=27687563.92..27687564.04 rows=50 width=1080) (actual time=150.504..150.509 rows=3.00 loops=1)
  Buffers: shared hit=159918, temp read=836 written=1457
  CTE referral_chain
    ->  Recursive Union  (cost=0.00..17757623.19 rows=396599576 width=64) (actual time=0.016..137.869 rows=50065.00 loops=1)
          Storage: Disk  Maximum Storage: 4114kB
          Buffers: shared hit=159916, temp read=836 written=956
          ->  Seq Scan on referrals r  (cost=0.00..1069.16 rows=50016 width=64) (actual time=0.015..5.964 rows=50016.00 loops=1)
                Buffers: shared hit=569
          ->  Merge Join  (cost=31415.91..1379055.83 rows=39654956 width=64) (actual time=8.380..24.306 rows=9.80 loops=5)
                Merge Cond: (r_1.from_doctor_id = rc_1.to_doctor_id)
                Join Filter: (r_1.to_doctor_id <> ALL (rc_1.path[2:]))
                Rows Removed by Join Filter: 1
                Buffers: shared hit=159347, temp read=836 written=456
                ->  Index Scan using idx_referrals_from_doctor on referrals r_1  (cost=0.29..2256.80 rows=50016 width=28) (actual time=0.012..12.456 rows=40013.00 loops=5)
                      Index Searches: 5
                      Buffers: shared hit=159347
                ->  Materialize  (cost=31415.62..32249.22 rows=166720 width=52) (actual time=8.335..8.340 rows=15.40 loops=5)
                      Storage: Memory  Maximum Storage: 17kB
                      Buffers: temp read=836 written=456
                      ->  Sort  (cost=31415.62..31832.42 rows=166720 width=52) (actual time=8.121..8.122 rows=11.80 loops=5)
                            Sort Key: rc_1.to_doctor_id
                            Sort Method: quicksort  Memory: 25kB
                            Buffers: temp read=836 written=456
                            ->  WorkTable Scan on referral_chain rc_1  (cost=0.00..11253.60 rows=166720 width=52) (actual time=0.006..2.463 rows=10011.80 loops=5)
                                  Filter: (depth < 5)
                                  Rows Removed by Filter: 1
                                  Buffers: temp read=501 written=1
  ->  Sort  (cost=9929940.72..9930932.22 rows=396599 width=1080) (actual time=150.503..150.505 rows=3.00 loops=1)
        Sort Key: rc.depth DESC, rc.referral_date DESC
        Sort Method: quicksort  Memory: 25kB
        Buffers: shared hit=159918, temp read=836 written=1457
        ->  Hash Join  (cost=4.70..9916765.99 rows=396599 width=1080) (actual time=91.359..150.495 rows=3.00 loops=1)
              Hash Cond: (rc.from_doctor_id = d1.doctor_id)
              Buffers: shared hit=159918, temp read=836 written=1457
              ->  CTE Scan on referral_chain rc  (cost=0.00..9914989.40 rows=660999 width=56) (actual time=91.287..150.418 rows=3.00 loops=1)
                    Filter: ((depth > 1) AND (from_doctor_id = to_doctor_id))
                    Rows Removed by Filter: 50062
                    Storage: Disk  Maximum Storage: 4096kB
                    Buffers: shared hit=159916, temp read=836 written=1457
              ->  Hash  (cost=3.20..3.20 rows=120 width=1040) (actual time=0.060..0.061 rows=121.00 loops=1)
                    Buckets: 1024  Batches: 1  Memory Usage: 15kB
                    Buffers: shared hit=2
                    ->  Seq Scan on doctors d1  (cost=0.00..3.20 rows=120 width=1040) (actual time=0.024..0.036 rows=121.00 loops=1)
                          Buffers: shared hit=2
Planning Time: 0.246 ms
Execution Time: 152.918 ms
}}}

Execution times (ms): 152.918, 171.109, 147.609, 144.191, 157.394, 161.768, 163.216, 145.951, 159.057, 145.346

'''Average execution time WITH index''': 154.86ms

'''Improvement: ~9.0%'''

The percentage improvement looks modest on its own, but the query plan tells a clearer story: the baseline plan needed an explicit `Sort` on `referrals.from_doctor_id` at every recursive step, since the table wasn't already ordered that way. The indexed plan skips that entirely — a single `Index Scan` returns the rows pre-sorted, cutting out a whole step rather than just making it faster.

**Decision: KEEP** `idx_referrals_from_doctor`. Scenarios 1-2 asked "does this index help filter the table once?" This one's different: the saving per lookup is small, but it adds up because the same lookup happens at every level of the recursion. It's also the only scenario where the real question isn't about filtering rows, but about how cheaply the same join can be repeated at scale.

== Security measures ==

=== SQL Injection Prevention ===

The application protects against SQL injection attacks by using parameterized queries through Spring Data JPA. User-provided input is never directly concatenated into SQL or JPQL statements. Instead, input values are passed as parameters and handled as data by the persistence framework.

=== JPQL Queries with Named Parameters ===

{{{
@Query("""
SELECT b FROM Billing b
WHERE b.medicalRecord.patient.patientId = :patientId
ORDER BY b.paymentDate DESC
""")
List<Billing> findBillingHistoryForPatient(
    @Param("patientId") Long patientId
);
}}}

The query uses the named parameter `:patientId` instead of putting user input directly into the JPQL statement. Spring Data JPA and Hibernate bind the value of `patientId` separately from the query structure, so it's treated as a parameter, not part of the JPQL command — meaning an attacker can't use malicious input to alter the query or inject additional conditions or SQL commands through the `patientId` value. This prevents SQL injection in database operations that rely on user-provided identifiers.

=== Native SQL Queries with Parameters ===

{{{
@Query(value = """
SELECT COALESCE(SUM(p.cost), 0) + COALESCE(SUM(l.cost), 0)
FROM medical_records mr
LEFT JOIN medical_record_procedures mrp ON mr.record_id = mrp.record_id
LEFT JOIN procedures p ON mrp.procedure_id = p.procedure_id
LEFT JOIN medical_record_lab_results mrl ON mr.record_id = mrl.record_id
LEFT JOIN lab_results lr ON mrl.result_id = lr.result_id
LEFT JOIN lab_tests l ON lr.test_id = l.test_id
WHERE mr.record_id = :recordId
""", nativeQuery = true)
BigDecimal calculateTotalCostForMedicalRecord(
    @Param("recordId") Long recordId
);
}}}

Native SQL queries also use named parameters such as `:recordId`, with the value bound separately from the SQL statement through `@Param`. This means user input can't change the SQL syntax or append additional SQL commands to the query — even a malicious value is treated as a parameter value rather than executable SQL. This prevents SQL injection in native database queries.

=== Prevention of Unsafe Dynamic SQL ===

The application does not construct SQL queries by concatenating user input with SQL strings.

For example, the following unsafe approach is not used:

{{{
String query = "SELECT * FROM patients WHERE embg = '" + embg + "'";
}}}

Directly concatenating user input into a SQL statement could allow an attacker to manipulate the query and potentially access, modify, or delete unauthorized data.

Instead, the application uses Spring Data JPA parameter binding, where user input is kept separate from the SQL or JPQL command.

In conclusion SQL injection is prevented by keeping user input separate from database commands. The application uses Spring Data JPA method queries, named parameters with `@Param`, and parameterized native SQL queries. As a result, user-provided values are treated as data rather than executable SQL, preventing attackers from modifying the intended database queries through malicious input.

=== Database-Level Security & Access Control ===

Beyond application-layer parameterized queries, data access is protected through:

* '''Service-Layer Ownership Checks''': Resource-level access control is enforced at the service layer. For example, patients can only access their own medical records, appointments, and billing information. Doctors can only modify their own appointment schedules and patient records they have access to. These checks verify that the user's ID or role from the JWT token matches the resource being accessed before any data is returned or modified.

=== CORS ===

The application uses a custom CORS filter to control which frontend clients can communicate with the backend API. In development, requests are allowed only from the trusted origins `http://localhost:3000` and `http://localhost:3001`. The filter also restricts the allowed HTTP methods and headers and rejects preflight requests from untrusted origins.

{{{
package medora.config;

import org.springframework.stereotype.Component;
import jakarta.servlet.Filter;
import jakarta.servlet.FilterChain;
import jakarta.servlet.ServletException;
import jakarta.servlet.ServletRequest;
import jakarta.servlet.ServletResponse;
import jakarta.servlet.http.HttpServletRequest;
import jakarta.servlet.http.HttpServletResponse;

import java.io.IOException;

@Component
public class CorsFilter implements Filter {

    @Override
    public void doFilter(
            ServletRequest request,
            ServletResponse response,
            FilterChain chain)
            throws IOException, ServletException {

        HttpServletRequest httpRequest = (HttpServletRequest) request;
        HttpServletResponse httpResponse = (HttpServletResponse) response;

        String origin = httpRequest.getHeader("Origin");

        // Allow only trusted frontend origins
        boolean allowedOrigin =
                "http://localhost:3000".equals(origin) ||
                "http://localhost:3001".equals(origin);

        if (allowedOrigin) {
            httpResponse.setHeader(
                    "Access-Control-Allow-Origin",
                    origin
            );

            httpResponse.setHeader(
                    "Access-Control-Allow-Methods",
                    "GET, POST, PUT, PATCH, DELETE, OPTIONS"
            );

            httpResponse.setHeader(
                    "Access-Control-Allow-Headers",
                    "Content-Type, Authorization"
            );

            httpResponse.setHeader(
                    "Access-Control-Allow-Credentials",
                    "true"
            );

            httpResponse.setHeader(
                    "Access-Control-Max-Age",
                    "3600"
            );
        }

        // Handle CORS preflight requests
        if ("OPTIONS".equalsIgnoreCase(httpRequest.getMethod())) {
            if (allowedOrigin) {
                httpResponse.setStatus(HttpServletResponse.SC_OK);
            } else {
                httpResponse.setStatus(HttpServletResponse.SC_FORBIDDEN);
            }
            return;
        }

        chain.doFilter(request, response);
    }
}
}}}

=== Password Storage ===

The application uses BCrypt to protect user passwords. Passwords are hashed using `PasswordEncoder` before being stored in the database, while login attempts are verified using BCrypt's password matching functionality.

{{{
String hashedPassword = passwordEncoder.encode(password);
User user = new User(username, hashedPassword, role, firstName, lastName);
userRepository.save(user);
}}}

* Passwords are not stored as plaintext; only the BCrypt hash is saved in the database.
* During login, `passwordEncoder.matches()` compares the provided password with the stored hash without exposing or storing the original password.

{{{
if (!passwordEncoder.matches(password, user.getPassword())) {
    throw new RuntimeException("Invalid username or password");
}
}}}

=== JWT Authentication ===

The application uses JWT tokens to authenticate users. All API endpoints, except authentication and health-check endpoints, require a valid JWT token.
The `AuthorizationInterceptor` validates the token using `SecurityUtil`. Requests with a missing or invalid token are rejected with `HTTP 401 Unauthorized`.

{{{
if (!securityUtil.isValidToken(request)) {
    response.setStatus(HttpServletResponse.SC_UNAUTHORIZED);
    return false;
}
}}}

== Role-Based Authorization ==

The application uses the role stored in the JWT to restrict access to different API endpoints.
* Admin endpoints require the `ADMIN` role
* Doctor endpoints require `DOCTOR` or `ADMIN`
* Patient endpoints require `PATIENT`, `DOCTOR`, or `ADMIN`

Requests from users without the required role are rejected with `HTTP 403 Forbidden`.

{{{
if (path.startsWith("/api/admin/")) {
    if (!securityUtil.hasRole("ADMIN", request)) {
        response.setStatus(HttpServletResponse.SC_FORBIDDEN);
        return false;
    }
}
}}}

* Resource ownership checks, such as ensuring that a patient can access only their own records, are handled separately in the corresponding service methods.