| Version 3 (modified by , 8 hours ago) ( diff ) |
|---|
Advanced Database Development
Allergy–prescription safety enforcement
A patient should never be prescribed a medication that conflicts with one of their documented allergies. This requirement blocks the unsafe prescription outright, at the database level: a new or changed prescription_medical_records row is rejected if the prescription is linked, via prescription_restriction → allergy_prescription_restrictions, to an allergy the patient has on file, either in patient_allergies or in medical_record_allergies on any of the patient's medical records. The trigger only prevents new conflicts; prescriptions that were recorded before it existed are listed by the v_prescription_allergy_conflicts view so they can be reviewed.
Custom domains
CREATE DOMAIN non_negative_cost AS DECIMAL(12,2)
CHECK (VALUE >= 0);
ALTER TABLE procedures ALTER COLUMN cost TYPE non_negative_cost USING cost::non_negative_cost;
ALTER TABLE lab_tests ALTER COLUMN cost TYPE non_negative_cost USING cost::non_negative_cost;
ALTER TABLE billing ALTER COLUMN total_cost TYPE non_negative_cost USING total_cost::non_negative_cost;
ALTER TABLE procedures DROP CONSTRAINT IF EXISTS procedures_cost_chk;
ALTER TABLE lab_tests DROP CONSTRAINT IF EXISTS lab_tests_cost_chk;
ALTER TABLE billing DROP CONSTRAINT IF EXISTS billing_cost_chk;
Triggers
CREATE OR REPLACE FUNCTION t1_prescription_allergy_check()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
v_patient_id BIGINT;
v_conflict_allergy_id BIGINT;
v_allergy_name TEXT;
BEGIN
SELECT patient_id INTO v_patient_id
FROM medical_records
WHERE record_id = NEW.record_id;
IF v_patient_id IS NULL THEN
RAISE EXCEPTION 'Medical record % not found', NEW.record_id;
END IF;
SELECT a.allergy_id, a.name
INTO v_conflict_allergy_id, v_allergy_name
FROM prescription_restriction pr_rest
JOIN allergy_prescription_restrictions apr ON apr.restriction_id = pr_rest.restriction_id
JOIN allergies a ON a.allergy_id = apr.allergy_id
WHERE pr_rest.prescription_id = NEW.prescription_id
AND (
EXISTS (SELECT 1 FROM patient_allergies pa
WHERE pa.patient_id = v_patient_id
AND pa.allergy_id = a.allergy_id)
OR EXISTS (SELECT 1 FROM medical_record_allergies mra
JOIN medical_records mr2 ON mr2.record_id = mra.record_id
WHERE mr2.patient_id = v_patient_id
AND mra.allergy_id = a.allergy_id)
)
LIMIT 1;
IF v_conflict_allergy_id IS NOT NULL THEN
RAISE EXCEPTION 'PRESCRIPTION_ALLERGY_CONFLICT: Prescription % conflicts with allergy % (%) of patient %',
NEW.prescription_id, v_conflict_allergy_id, v_allergy_name, v_patient_id;
END IF;
RETURN NEW;
END;
$$;
DROP TRIGGER IF EXISTS trg_prescription_allergy_check ON prescription_medical_records;
CREATE TRIGGER trg_prescription_allergy_check
BEFORE INSERT OR UPDATE OF prescription_id, record_id
ON prescription_medical_records
FOR EACH ROW
EXECUTE FUNCTION t1_prescription_allergy_check();
Views
CREATE OR REPLACE VIEW v_prescription_allergy_conflicts AS
SELECT
mr.record_id,
p.patient_id,
p.first_name,
p.last_name,
pmr.prescription_id,
pr.medication_name,
a.allergy_id,
a.name AS allergy_name,
a.allergy_severity,
apr.restriction_id,
pr_rest.description AS restriction_description
FROM prescription_medical_records pmr
JOIN medical_records mr ON pmr.record_id = mr.record_id
JOIN patients p ON mr.patient_id = p.patient_id
JOIN prescriptions pr ON pmr.prescription_id = pr.prescription_id
JOIN prescription_restriction pr_rest ON pr.prescription_id = pr_rest.prescription_id
JOIN allergy_prescription_restrictions apr ON pr_rest.restriction_id = apr.restriction_id
JOIN allergies a ON apr.allergy_id = a.allergy_id
WHERE EXISTS (SELECT 1 FROM patient_allergies pa
WHERE pa.patient_id = p.patient_id
AND pa.allergy_id = a.allergy_id)
OR EXISTS (SELECT 1 FROM medical_record_allergies mra
JOIN medical_records mr2 ON mr2.record_id = mra.record_id
WHERE mr2.patient_id = p.patient_id
AND mra.allergy_id = a.allergy_id)
ORDER BY
p.patient_id,
CASE a.allergy_severity
WHEN 'CRITICAL' THEN 1
WHEN 'HIGH' THEN 2
WHEN 'MEDIUM' THEN 3
ELSE 4
END;
Appointment scheduling integrity
Appointments must obey real scheduling constraints: no scheduling in the past (when an appointment is created or moved to a new date or time), no double-booking the same doctor or patient in an overlapping time, no marking an appointment COMPLETED before its scheduled time, and valid status transitions only (SCHEDULED → IN_PROGRESS/COMPLETED/CANCELLED/NO_SHOW, IN_PROGRESS → COMPLETED/CANCELLED, nothing further out of COMPLETED/CANCELLED/NO_SHOW). Each appointment is treated as a 30-minute slot, and only SCHEDULED, IN_PROGRESS and COMPLETED appointments occupy a slot. The job_mark_no_show procedure moves appointments that are still SCHEDULED 45+ minutes past their time to NO_SHOW; it is run with CALL job_mark_no_show();, either manually or from a scheduler such as pg_cron, and v_overdue_appointments shows the appointments it will pick up.
Schema change
ALTER TABLE appointments DROP CONSTRAINT appointments_status_chk;
ALTER TABLE appointments ADD CONSTRAINT appointments_status_chk
CHECK (status IN ('SCHEDULED', 'COMPLETED', 'CANCELLED', 'IN_PROGRESS', 'NO_SHOW'));
Triggers
CREATE OR REPLACE FUNCTION is_valid_appointment_transition(p_old TEXT, p_new TEXT)
RETURNS BOOLEAN
LANGUAGE sql
IMMUTABLE
AS $$
SELECT CASE
WHEN p_old = p_new THEN TRUE
WHEN p_old = 'SCHEDULED' AND p_new IN ('IN_PROGRESS', 'COMPLETED', 'CANCELLED', 'NO_SHOW') THEN TRUE
WHEN p_old = 'IN_PROGRESS' AND p_new IN ('COMPLETED', 'CANCELLED') THEN TRUE
ELSE FALSE
END;
$$;
CREATE OR REPLACE FUNCTION trigger_appointments_enforce()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
v_combined_datetime TIMESTAMP;
BEGIN
v_combined_datetime := NEW.appointment_date::TIMESTAMP + NEW.appointment_time;
IF TG_OP = 'INSERT' AND v_combined_datetime < NOW() THEN
RAISE EXCEPTION 'Cannot schedule appointment in the past (appointment_date=%, appointment_time=%)',
NEW.appointment_date, NEW.appointment_time;
END IF;
IF TG_OP = 'UPDATE' THEN
IF (NEW.appointment_date, NEW.appointment_time) IS DISTINCT FROM (OLD.appointment_date, OLD.appointment_time)
AND v_combined_datetime < NOW() THEN
RAISE EXCEPTION 'Cannot move appointment % into the past (appointment_date=%, appointment_time=%)',
NEW.appointment_id, NEW.appointment_date, NEW.appointment_time;
END IF;
IF NOT is_valid_appointment_transition(OLD.status, NEW.status) THEN
RAISE EXCEPTION 'Appointment status cannot transition from % to %',
OLD.status, NEW.status;
END IF;
END IF;
IF NEW.status = 'COMPLETED' AND v_combined_datetime > NOW() THEN
RAISE EXCEPTION 'Cannot mark appointment COMPLETED before its scheduled time (scheduled for %)',
v_combined_datetime;
END IF;
RETURN NEW;
END;
$$;
DROP TRIGGER IF EXISTS trigger_appointments_enforce ON appointments;
CREATE TRIGGER trigger_appointments_enforce
BEFORE INSERT OR UPDATE
ON appointments
FOR EACH ROW
EXECUTE FUNCTION trigger_appointments_enforce();
CREATE OR REPLACE FUNCTION t1_appointments_no_overlap()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
v_new_start TIMESTAMP;
v_new_end TIMESTAMP;
BEGIN
IF NEW.status NOT IN ('SCHEDULED', 'IN_PROGRESS', 'COMPLETED') THEN
RETURN NEW;
END IF;
v_new_start := NEW.appointment_date::TIMESTAMP + NEW.appointment_time;
v_new_end := v_new_start + INTERVAL '30 minutes';
IF EXISTS (
SELECT 1 FROM appointments a
WHERE a.doctor_id = NEW.doctor_id
AND a.status IN ('SCHEDULED', 'IN_PROGRESS', 'COMPLETED')
AND (a.appointment_date::TIMESTAMP + a.appointment_time) < v_new_end
AND v_new_start < (a.appointment_date::TIMESTAMP + a.appointment_time + INTERVAL '30 minutes')
AND (TG_OP <> 'UPDATE' OR a.appointment_id <> NEW.appointment_id)
) THEN
RAISE EXCEPTION 'Doctor % has an overlapping appointment at % %',
NEW.doctor_id, NEW.appointment_date, NEW.appointment_time;
END IF;
IF EXISTS (
SELECT 1 FROM appointments a
WHERE a.patient_id = NEW.patient_id
AND a.status IN ('SCHEDULED', 'IN_PROGRESS', 'COMPLETED')
AND (a.appointment_date::TIMESTAMP + a.appointment_time) < v_new_end
AND v_new_start < (a.appointment_date::TIMESTAMP + a.appointment_time + INTERVAL '30 minutes')
AND (TG_OP <> 'UPDATE' OR a.appointment_id <> NEW.appointment_id)
) THEN
RAISE EXCEPTION 'Patient % has an overlapping appointment at % %',
NEW.patient_id, NEW.appointment_date, NEW.appointment_time;
END IF;
RETURN NEW;
END;
$$;
DROP TRIGGER IF EXISTS trigger_appointments_no_overlap ON appointments;
CREATE TRIGGER trigger_appointments_no_overlap
BEFORE INSERT OR UPDATE
ON appointments
FOR EACH ROW
EXECUTE FUNCTION t1_appointments_no_overlap();
Stored procedures/functions
CREATE OR REPLACE PROCEDURE job_mark_no_show()
LANGUAGE plpgsql
AS $$
BEGIN
UPDATE appointments
SET status = 'NO_SHOW'
WHERE status = 'SCHEDULED'
AND (appointment_date::TIMESTAMP + appointment_time) < (NOW() - INTERVAL '45 minutes');
END;
$$;
Views
CREATE OR REPLACE VIEW v_overdue_appointments AS
SELECT
a.appointment_id, a.patient_id, p.first_name, p.last_name,
a.doctor_id, d.first_name AS doctor_first_name, d.last_name AS doctor_last_name,
a.appointment_date, a.appointment_time, a.status,
NOW() - (a.appointment_date::TIMESTAMP + a.appointment_time) AS time_overdue
FROM appointments a
JOIN patients p ON a.patient_id = p.patient_id
JOIN doctors d ON a.doctor_id = d.doctor_id
WHERE a.status = 'SCHEDULED'
AND (a.appointment_date::TIMESTAMP + a.appointment_time) < (NOW() - INTERVAL '45 minutes')
ORDER BY time_overdue DESC;
Medical record consistency
Diagnoses, procedures, and referrals can be created independently but must stay consistent with the patient they actually serve. Linking a diagnosis to a different patient's medical record, linking a performed procedure to a diagnosis that belongs to another patient, or a doctor referring a patient to themselves is both an integrity problem and a clinical safety risk, so these are rejected on insert and on update. The v_medical_record_overview view summarises each medical record and flags diagnosis mismatches and self-referrals that were recorded before the triggers existed.
Triggers
CREATE OR REPLACE FUNCTION t1_diagnosis_record_consistency()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
v_record_patient BIGINT;
v_diagnosis_patient BIGINT;
BEGIN
SELECT patient_id INTO v_record_patient FROM medical_records WHERE record_id = NEW.record_id;
SELECT patient_id INTO v_diagnosis_patient FROM diagnosis WHERE diagnosis_id = NEW.diagnosis_id;
IF v_record_patient IS NULL THEN
RAISE EXCEPTION 'Medical record % not found', NEW.record_id;
END IF;
IF v_diagnosis_patient IS NULL THEN
RAISE EXCEPTION 'Diagnosis % not found', NEW.diagnosis_id;
END IF;
IF v_record_patient <> v_diagnosis_patient THEN
RAISE EXCEPTION 'Diagnosis % belongs to patient %, but medical record % belongs to patient %',
NEW.diagnosis_id, v_diagnosis_patient, NEW.record_id, v_record_patient;
END IF;
RETURN NEW;
END;
$$;
DROP TRIGGER IF EXISTS trg_diagnosis_record_consistency ON diagnosis_medical_records;
CREATE TRIGGER trg_diagnosis_record_consistency
BEFORE INSERT OR UPDATE ON diagnosis_medical_records
FOR EACH ROW EXECUTE FUNCTION t1_diagnosis_record_consistency();
CREATE OR REPLACE FUNCTION t2_procedure_diagnosis_consistency()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
v_diagnosis_patient BIGINT;
BEGIN
IF NEW.diagnosis_id IS NOT NULL THEN
SELECT patient_id INTO v_diagnosis_patient FROM diagnosis WHERE diagnosis_id = NEW.diagnosis_id;
IF v_diagnosis_patient IS NULL THEN
RAISE EXCEPTION 'Diagnosis % not found', NEW.diagnosis_id;
END IF;
IF NEW.patient_id <> v_diagnosis_patient THEN
RAISE EXCEPTION 'Procedure belongs to patient %, but diagnosis % belongs to patient %',
NEW.patient_id, NEW.diagnosis_id, v_diagnosis_patient;
END IF;
END IF;
RETURN NEW;
END;
$$;
DROP TRIGGER IF EXISTS trg_procedure_diagnosis_consistency ON performed_procedures;
CREATE TRIGGER trg_procedure_diagnosis_consistency
BEFORE INSERT OR UPDATE ON performed_procedures
FOR EACH ROW EXECUTE FUNCTION t2_procedure_diagnosis_consistency();
CREATE OR REPLACE FUNCTION t3_referral_consistency()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
IF NEW.from_doctor_id = NEW.to_doctor_id THEN
RAISE EXCEPTION 'Doctor % cannot refer a patient to themselves', NEW.from_doctor_id;
END IF;
RETURN NEW;
END;
$$;
DROP TRIGGER IF EXISTS trg_referral_consistency ON referrals;
CREATE TRIGGER trg_referral_consistency
BEFORE INSERT OR UPDATE ON referrals
FOR EACH ROW EXECUTE FUNCTION t3_referral_consistency();
Views
CREATE OR REPLACE VIEW v_medical_record_overview AS
SELECT
mr.record_id,
p.patient_id, p.first_name, p.last_name, p.embg,
COUNT(DISTINCT dmr.diagnosis_id) AS diagnosis_count,
COUNT(DISTINCT CASE WHEN d.patient_id <> p.patient_id THEN dmr.diagnosis_id END) AS diagnosis_mismatches,
COUNT(DISTINCT mrp.procedure_id) AS procedures_count,
COUNT(DISTINCT mrl.result_id) AS lab_results_count,
COUNT(DISTINCT ref.referral_id) AS referrals_count,
COUNT(DISTINCT mra.allergy_id) AS allergies_count,
COUNT(DISTINCT CASE WHEN ref.from_doctor_id = ref.to_doctor_id THEN ref.referral_id END) AS self_referrals_detected
FROM medical_records mr
JOIN patients p ON mr.patient_id = p.patient_id
LEFT JOIN diagnosis_medical_records dmr ON dmr.record_id = mr.record_id
LEFT JOIN diagnosis d ON d.diagnosis_id = dmr.diagnosis_id
LEFT JOIN medical_record_procedures mrp ON mrp.record_id = mr.record_id
LEFT JOIN medical_record_lab_results mrl ON mrl.record_id = mr.record_id
LEFT JOIN referrals ref ON ref.record_id = mr.record_id
LEFT JOIN medical_record_allergies mra ON mra.record_id = mr.record_id
GROUP BY mr.record_id, p.patient_id, p.first_name, p.last_name, p.embg;
Billing integrity
Billing records must stay in sync with their line items, and payment status must follow a valid state machine. billing.total_cost is recalculated automatically whenever a procedure or lab test is added to or removed from a bill, as the sum of the list prices (cost) of its line items, rather than relying on the application to keep it in sync. Line items can only be changed while the bill is PENDING, so a PAID or CANCELLED bill can no longer change its amount. payment_status may only move PENDING → PAID or PENDING → CANCELLED, never backwards once finalized; when a bill becomes PAID without a payment_date, the current date is filled in. Every billing status change and every change to the calculated total caused by adding or removing a billing line item is recorded in billing_audit_log. The existing created_at column is used to track outstanding PENDING bills: v_overdue_billings lists bills that have been pending for more than 30 days (CRITICAL after 60 days), and job_billing_alerts reports how many need follow-up.
Schema change
CREATE TABLE IF NOT EXISTS billing_audit_log (
audit_id BIGSERIAL PRIMARY KEY,
bill_id BIGINT NOT NULL REFERENCES billing(bill_id),
old_amount DECIMAL(12,2),
new_amount DECIMAL(12,2),
old_status TEXT,
new_status TEXT,
change_type TEXT CHECK (change_type IN ('INSERT', 'UPDATE', 'LINE_ITEM_ADD', 'LINE_ITEM_REMOVE')),
changed_at TIMESTAMP DEFAULT NOW()
);
Stored procedures/functions
CREATE OR REPLACE FUNCTION recalculate_billing_total(p_bill_id BIGINT, p_change_type TEXT)
RETURNS VOID
LANGUAGE plpgsql
AS $$
DECLARE
v_old_total DECIMAL;
v_status TEXT;
v_procedure_total DECIMAL;
v_lab_total DECIMAL;
v_new_total DECIMAL;
BEGIN
SELECT total_cost, payment_status
INTO v_old_total, v_status
FROM billing
WHERE bill_id = p_bill_id;
IF v_status IS NULL THEN
RAISE EXCEPTION 'Bill % not found', p_bill_id;
END IF;
IF v_status <> 'PENDING' THEN
RAISE EXCEPTION 'Bill % is % and its line items can no longer be changed', p_bill_id, v_status;
END IF;
SELECT COALESCE(SUM(p.cost), 0) INTO v_procedure_total
FROM billing_procedures bp
JOIN procedures p ON p.procedure_id = bp.procedure_id
WHERE bp.bill_id = p_bill_id;
SELECT COALESCE(SUM(lt.cost), 0) INTO v_lab_total
FROM billing_lab_tests blt
JOIN lab_tests lt ON lt.test_id = blt.test_id
WHERE blt.bill_id = p_bill_id;
v_new_total := v_procedure_total + v_lab_total;
UPDATE billing SET total_cost = v_new_total WHERE bill_id = p_bill_id;
INSERT INTO billing_audit_log (bill_id, old_amount, new_amount, change_type)
VALUES (p_bill_id, v_old_total, v_new_total, p_change_type);
END;
$$;
CREATE OR REPLACE FUNCTION is_valid_billing_transition(p_old TEXT, p_new TEXT)
RETURNS BOOLEAN
LANGUAGE sql
IMMUTABLE
AS $$
SELECT CASE
WHEN p_old = p_new THEN TRUE
WHEN p_old = 'PENDING' AND p_new IN ('PAID', 'CANCELLED') THEN TRUE
ELSE FALSE
END;
$$;
CREATE OR REPLACE PROCEDURE job_billing_alerts()
LANGUAGE plpgsql
AS $$
DECLARE
v_overdue_count INT;
BEGIN
SELECT COUNT(*) INTO v_overdue_count FROM v_overdue_billings;
RAISE NOTICE 'Found % overdue billing records requiring follow-up', v_overdue_count;
END;
$$;
Triggers
CREATE OR REPLACE FUNCTION t1_billing_line_item_changed()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
IF TG_OP = 'DELETE' THEN
PERFORM recalculate_billing_total(OLD.bill_id, 'LINE_ITEM_REMOVE');
RETURN OLD;
ELSE
PERFORM recalculate_billing_total(NEW.bill_id, 'LINE_ITEM_ADD');
RETURN NEW;
END IF;
END;
$$;
DROP TRIGGER IF EXISTS trg_billing_procedures_update_total ON billing_procedures;
CREATE TRIGGER trg_billing_procedures_update_total
AFTER INSERT OR DELETE ON billing_procedures
FOR EACH ROW
EXECUTE FUNCTION t1_billing_line_item_changed();
DROP TRIGGER IF EXISTS trg_billing_lab_tests_update_total ON billing_lab_tests;
CREATE TRIGGER trg_billing_lab_tests_update_total
AFTER INSERT OR DELETE ON billing_lab_tests
FOR EACH ROW
EXECUTE FUNCTION t1_billing_line_item_changed();
CREATE OR REPLACE FUNCTION t2_billing_status_transition()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
IF NOT is_valid_billing_transition(OLD.payment_status, NEW.payment_status) THEN
RAISE EXCEPTION 'Cannot transition billing status from % to %',
OLD.payment_status, NEW.payment_status;
END IF;
IF NEW.payment_status = 'PAID' AND NEW.payment_date IS NULL THEN
NEW.payment_date := CURRENT_DATE;
END IF;
IF OLD.payment_status <> NEW.payment_status THEN
INSERT INTO billing_audit_log (bill_id, old_status, new_status, change_type)
VALUES (NEW.bill_id, OLD.payment_status, NEW.payment_status, 'UPDATE');
END IF;
RETURN NEW;
END;
$$;
DROP TRIGGER IF EXISTS trg_billing_status_transition ON billing;
CREATE TRIGGER trg_billing_status_transition
BEFORE UPDATE ON billing
FOR EACH ROW
EXECUTE FUNCTION t2_billing_status_transition();
Views
CREATE OR REPLACE VIEW v_overdue_billings AS
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,
CASE
WHEN CURRENT_DATE - b.created_at::DATE > 60 THEN 'CRITICAL'
ELSE 'OVERDUE'
END AS urgency
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;
Custom domains for EMBG and Phone number formats
Custom domains replace one-off column checks with reusable types. non_negative_cost consolidates the cost >= 0 rule that was repeated on procedures, lab_tests and billing; since it matches an existing rule it is applied immediately and the three old CHECK constraints are dropped. It is created in the Custom domains block of the first section on this page, because the column types must be changed before any view that uses these columns is created (such as v_overdue_billings and mv_revenue_monthly), and PostgreSQL cannot change the type of a column that a view depends on. embg_format (the same rule as the existing patients_embg_format_chk), email_format and phone_number_format are defined for reuse but not yet applied to any column: email_format is stricter than the existing checks (for example, it requires a domain ending such as .com, which the admin and lab technician checks do not), so existing data has to be checked before it is applied.
Custom domains
CREATE DOMAIN embg_format AS TEXT
CHECK (VALUE ~ '^\d{13}$');
CREATE DOMAIN email_format AS TEXT
CHECK (
VALUE ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'
AND LENGTH(VALUE) <= 254
);
CREATE DOMAIN phone_number_format AS TEXT
CHECK (VALUE ~ '^\+?[\d\s\-().]{7,20}$');
Revenue reporting (materialized view and background refresh job)
Revenue reports need expensive joins across billing, procedures, and lab tests, so computing them fresh on every request is wasteful. This materialized view precomputes monthly revenue from PAID bills, split by procedure vs lab test and grouped by the month of payment_date. Procedure revenue is attributed down to department, since procedures carries a doctor_id directly. Lab revenue can't be: lab_tests has no doctor or department reference, and billing_lab_tests links only to the catalog test_id, not a specific performed instance, so lab rows are reported for the clinic as a whole. The view is refreshed with CALL medora_job_refresh_revenue_view();, either manually or from a scheduler such as pg_cron; the refresh procedure was verified by manual invocation.
Views
DROP MATERIALIZED VIEW IF EXISTS mv_revenue_monthly CASCADE;
CREATE MATERIALIZED VIEW mv_revenue_monthly AS
SELECT
DATE_TRUNC('month', b.payment_date)::DATE AS month,
dept.department_id,
dept.department_name,
'PROCEDURE' AS revenue_type,
SUM(p.cost) AS revenue,
COUNT(DISTINCT b.bill_id) AS transaction_count
FROM billing b
JOIN billing_procedures bp ON bp.bill_id = b.bill_id
JOIN procedures p ON p.procedure_id = bp.procedure_id
JOIN doctors doc ON doc.doctor_id = p.doctor_id
JOIN departments dept ON dept.department_id = doc.department_id
WHERE b.payment_status = 'PAID'
AND b.payment_date IS NOT NULL
GROUP BY DATE_TRUNC('month', b.payment_date), dept.department_id, dept.department_name
UNION ALL
SELECT
DATE_TRUNC('month', b.payment_date)::DATE AS month,
NULL AS department_id,
NULL AS department_name,
'LAB' AS revenue_type,
SUM(lt.cost) AS revenue,
COUNT(DISTINCT b.bill_id) AS transaction_count
FROM billing b
JOIN billing_lab_tests blt ON blt.bill_id = b.bill_id
JOIN lab_tests lt ON lt.test_id = blt.test_id
WHERE b.payment_status = 'PAID'
AND b.payment_date IS NOT NULL
GROUP BY DATE_TRUNC('month', b.payment_date);
CREATE INDEX idx_mv_revenue_monthly_month ON mv_revenue_monthly (month, revenue_type);
CREATE OR REPLACE VIEW v_current_month_revenue AS
SELECT month, department_id, department_name, revenue_type, revenue, transaction_count
FROM mv_revenue_monthly
WHERE month = DATE_TRUNC('month', CURRENT_DATE)::DATE
ORDER BY revenue_type, revenue DESC;
Stored procedures/functions
CREATE OR REPLACE PROCEDURE medora_job_refresh_revenue_view()
LANGUAGE plpgsql
AS $$
BEGIN
REFRESH MATERIALIZED VIEW mv_revenue_monthly;
END;
$$;
