Changes between Initial Version and Version 1 of AdvancedDatabaseDevelopment

Timestamp:

08/30/26 00:26:27 (12 days ago)

Author:

236021

Comment:

--

AdvancedDatabaseDevelopment

@@ +1 @@
+= 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 insert outright, at the database level before it can happen: a new **prescription_medical_records** row is rejected if the prescription is linked, via **allergy_prescription_restrictions** → **prescription_restriction** to any allergy already on that patient's medical record.
+
+=== Custom domains ===
+
+{{{
+CREATE DOMAIN non_negative_currency AS DECIMAL(12,2)
+    CHECK (VALUE >= 0);
+}}}
+
+=== Triggers ===
+
+{{{
+CREATE TABLE IF NOT EXISTS prescription_allergy_conflicts_log (
+    log_id BIGSERIAL PRIMARY KEY,
+    record_id BIGINT NOT NULL REFERENCES medical_records(record_id),
+    prescription_id BIGINT NOT NULL REFERENCES prescriptions(prescription_id),
+    allergy_id BIGINT NOT NULL REFERENCES allergies(allergy_id),
+    conflict_type TEXT CHECK (conflict_type IN ('ACTIVE', 'RESOLVED')),
+    detected_date TIMESTAMP DEFAULT NOW(),
+    resolution_notes TEXT,
+    resolved_date TIMESTAMP
+);
+
+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 apr.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 apr.allergy_id = a.allergy_id
+    JOIN medical_record_allergies mra ON a.allergy_id = mra.allergy_id
+    WHERE pr_rest.prescription_id = NEW.prescription_id
+      AND mra.record_id = NEW.record_id
+    LIMIT 1;
+
+    IF v_conflict_allergy_id IS NOT NULL THEN
+        RAISE EXCEPTION 'PRESCRIPTION_ALLERGY_CONFLICT: Prescription % conflicts with allergy % (%) in patient''s record %',
+            NEW.prescription_id, v_conflict_allergy_id, v_allergy_name, NEW.record_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
+    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,
+    CASE
+        WHEN mra.record_id IS NOT NULL THEN 'ACTIVE_CONFLICT'
+        ELSE 'ARCHIVED'
+    END AS conflict_status
+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
+LEFT JOIN medical_record_allergies mra ON mr.record_id = mra.record_id
+    AND a.allergy_id = mra.allergy_id
+WHERE a.allergy_severity IN ('HIGH', 'CRITICAL')
+ORDER BY p.patient_id, a.allergy_severity DESC;
+}}}
+
+----
+
+== Appointment scheduling integrity ==
+
+Appointments must obey real scheduling constraints so no scheduling in the past, no doublebooking 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`, `IN_PROGRESS → COMPLETED/CANCELLED`, nothing further out of `COMPLETED`/`CANCELLED`/`NO_SHOW`). A background job autotransitions appointments that remain `SCHEDULED` if they are 45+ minutes past their time to `NO_SHOW`.
+
+=== 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') 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 NOT is_valid_appointment_transition(OLD.status, NEW.status) THEN
+            RAISE EXCEPTION 'Appointment status cannot transition from % to %',
+                OLD.status, NEW.status;
+        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;
+    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 overlapping appointment at %', NEW.doctor_id, NEW.appointment_date;
+    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 overlapping appointment at %', NEW.patient_id, NEW.appointment_date;
+    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 as a mismatch like a diagnosis for one patient attached to a different patient's record, a doctor referring a patient to themselves, is both an **integrity problem** and a clinical **safety risk**. 
+
+=== 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 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 NOT EXISTS (SELECT 1 FROM medical_records WHERE record_id = NEW.record_id) THEN
+        RAISE EXCEPTION 'Medical record % not found', NEW.record_id;
+    END IF;
+
+    IF NEW.from_doctor_id = NEW.to_doctor_id THEN
+        RAISE EXCEPTION 'Doctor % cannot refer 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 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 IS NOT NULL AND 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 mr.record_id = ref.record_id
+    LEFT JOIN medical_record_allergies mra ON mr.record_id = mra.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 automatically recalculated whenever a procedure or lab test is added to or removed from a bill, rather than relying on the application to keep it in sync manually. **payment_status** may only move `PENDING → PAID` or `PENDING → CANCELLED` but never backwards once finalized and every status change is written to an audit log.  Since **billing** originally had no "issued" date , a **created_at** column was added so outstanding `PENDING` bills can be tracked for follow-up.
+
+=== Schema change ===
+
+{{{
+
+ALTER TABLE billing ADD COLUMN IF NOT EXISTS created_at TIMESTAMP DEFAULT NOW();
+
+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_procedure_total DECIMAL;
+    v_lab_total DECIMAL;
+    v_new_total DECIMAL;
+BEGIN
+    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, new_amount, change_type)
+    VALUES (p_bill_id, 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 WHERE days_outstanding > 30;
+    RAISE NOTICE 'Found % overdue billing records requiring follow-up', v_overdue_count;
+END;
+$$;
+}}}
+
+=== Triggers ===
+
+{{{
+-- one shared trigger function, reused for both billing_procedures and billing_lab_tests
+-- (both tables have a bill_id column, so NEW.bill_id / OLD.bill_id works either way)
+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 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'
+        WHEN CURRENT_DATE - b.created_at::DATE > 30 THEN 'OVERDUE'
+        ELSE 'PENDING'
+    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. **embg_format, email_format, and phone_number_format** are new or stricter than before; **non_negative_cost** consolidates a rule already repeated on three tables. non_negative_cost can be applied immediately, since it matches an existing rule. The other three need existing data checked first, since a stricter domain fails if any row doesn't already conform.
+
+=== 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}$');
+
+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;
+
+-- run these BEFORE altering embg / email / phone columns — every one of these
+-- should return ZERO rows, otherwise the corresponding ALTER will fail:
+--   SELECT patient_id, embg FROM patients WHERE embg !~ '^\d{13}$';
+--   SELECT doctor_id, email_address FROM doctors WHERE email_address !~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$';
+--   SELECT patient_id, email_address FROM patients WHERE email_address IS NOT NULL AND email_address !~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$';
+--   SELECT patient_id, phone_number FROM patients WHERE phone_number IS NOT NULL AND phone_number !~ '^\+?[\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, split by procedure vs lab test. 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 to the clinic only.The refresh procedure was verified by manual invocation.
+
+=== Views ===
+
+{{{
+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'
+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'
+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;
+$$;
+}}}