Changes between Initial Version and Version 1 of AdvancedDB

Timestamp:

03/28/26 12:41:42 (2 weeks ago)

Author:

221296

Comment:

--

AdvancedDB

@@ +1 @@
+= Advanced Database Development
+== Data constraints requirements: Enrollment-Subscription consistency + single enrollment per course
+=== Data requirements description
+Course enrollments must remain consistent with the subscription and participation rules of the platform:
+
+# A user must have an ACTIVE subscription in order to enroll in a course.
+# A user cannot enroll in the same course more than once.
+# enrollment status changes must follow a valid transition model.
+# Each enrollment must reference a valid user and a valid course.
+=== Implementation
+{{{Triggers}}}
+{{{
+-- status transition helper
+CREATE OR REPLACE FUNCTION olpms_is_valid_enrollment_transition(p_old text, p_new text)
+RETURNS boolean
+LANGUAGE sql
+AS $$
+    SELECT CASE
+        WHEN p_old IS NULL AND p_new IN ('IN_PROGRESS', 'COMPLETED', 'CANCELLED') THEN true
+        WHEN p_old = p_new THEN true
+        WHEN p_old = 'IN_PROGRESS' AND p_new IN ('COMPLETED', 'CANCELLED') THEN true
+        WHEN p_old = 'COMPLETED' AND p_new = 'COMPLETED' THEN true
+        WHEN p_old = 'CANCELLED' AND p_new = 'CANCELLED' THEN true
+        ELSE false
+    END;
+$$;
+
+-- main enrollment consistency trigger function
+CREATE OR REPLACE FUNCTION olpms_trg_enrollment_enforce()
+RETURNS trigger
+LANGUAGE plpgsql
+AS $$
+DECLARE
+    v_active_subscription_count integer;
+BEGIN
+    -- user must have an active subscription when inserting enrollment
+    IF TG_OP = 'INSERT' THEN
+        SELECT COUNT(*)
+        INTO v_active_subscription_count
+        FROM user_subscription us
+        WHERE us.user_id = NEW.user_id
+          AND us.status = 'ACTIVE'
+          AND CURRENT_DATE BETWEEN us.start_date AND us.end_date;
+
+        IF v_active_subscription_count = 0 THEN
+            RAISE EXCEPTION 'User % does not have an active subscription', NEW.user_id;
+        END IF;
+    END IF;
+
+    -- prevent duplicate enrollment for the same user and the same course
+    IF EXISTS (
+        SELECT 1
+        FROM enrollment e
+        WHERE e.user_id = NEW.user_id
+          AND e.course_id = NEW.course_id
+          AND (TG_OP <> 'UPDATE' OR e.enrollment_id <> NEW.enrollment_id)
+    ) THEN
+        RAISE EXCEPTION 'User % is already enrolled in course %', NEW.user_id, NEW.course_id;
+    END IF;
+
+    -- validate completion_status transition on UPDATE
+    IF TG_OP = 'UPDATE' THEN
+        IF NOT olpms_is_valid_enrollment_transition(OLD.completion_status, NEW.completion_status) THEN
+            RAISE EXCEPTION
+                'Invalid enrollment status transition: % -> %',
+                OLD.completion_status,
+                NEW.completion_status;
+        END IF;
+    END IF;
+
+    RETURN NEW;
+END;
+$$;
+
+DROP TRIGGER IF EXISTS trg_enrollment_enforce ON enrollment;
+
+CREATE TRIGGER trg_enrollment_enforce
+BEFORE INSERT OR UPDATE
+ON enrollment
+FOR EACH ROW
+EXECUTE FUNCTION olpms_trg_enrollment_enforce();
+
+}}}
+{{{Stored procedures / functions}}}
+{{{
+CREATE OR REPLACE FUNCTION olpms_is_valid_enrollment_transition(p_old text, p_new text)
+RETURNS boolean
+LANGUAGE sql
+AS $$
+    SELECT CASE
+        WHEN p_old IS NULL AND p_new IN ('IN_PROGRESS', 'COMPLETED', 'CANCELLED') THEN true
+        WHEN p_old = p_new THEN true
+        WHEN p_old = 'IN_PROGRESS' AND p_new IN ('COMPLETED', 'CANCELLED') THEN true
+        WHEN p_old = 'COMPLETED' AND p_new = 'COMPLETED' THEN true
+        WHEN p_old = 'CANCELLED' AND p_new = 'CANCELLED' THEN true
+        ELSE false
+    END;
+$$;
+
+CREATE OR REPLACE FUNCTION olpms_trg_enrollment_enforce()
+RETURNS trigger
+LANGUAGE plpgsql
+AS $$
+DECLARE
+    v_active_subscription_count integer;
+BEGIN
+    IF TG_OP = 'INSERT' THEN
+        SELECT COUNT(*)
+        INTO v_active_subscription_count
+        FROM user_subscription us
+        WHERE us.user_id = NEW.user_id
+          AND us.status = 'ACTIVE'
+          AND CURRENT_DATE BETWEEN us.start_date AND us.end_date;
+
+        IF v_active_subscription_count = 0 THEN
+            RAISE EXCEPTION 'User % does not have an active subscription', NEW.user_id;
+        END IF;
+    END IF;
+
+    IF EXISTS (
+        SELECT 1
+        FROM enrollment e
+        WHERE e.user_id = NEW.user_id
+          AND e.course_id = NEW.course_id
+          AND (TG_OP <> 'UPDATE' OR e.enrollment_id <> NEW.enrollment_id)
+    ) THEN
+        RAISE EXCEPTION 'User % is already enrolled in course %', NEW.user_id, NEW.course_id;
+    END IF;
+
+    IF TG_OP = 'UPDATE' THEN
+        IF NOT olpms_is_valid_enrollment_transition(OLD.completion_status, NEW.completion_status) THEN
+            RAISE EXCEPTION
+                'Invalid enrollment status transition: % -> %',
+                OLD.completion_status,
+                NEW.completion_status;
+        END IF;
+    END IF;
+
+    RETURN NEW;
+END;
+$$;
+}}}
+{{{Views}}}
+{{{
+CREATE OR REPLACE VIEW v_enrollments_enriched AS
+SELECT
+    e.enrollment_id,
+    e.user_id,
+    ue.first_name,
+    ue.last_name,
+    ue.email,
+    e.course_id,
+    c.name AS course_name,
+    e.enroll_date,
+    e.completion_status,
+    e.progress_percentage,
+    us.subscription_id,
+    us.status AS subscription_status,
+    us.start_date AS subscription_start_date,
+    us.end_date AS subscription_end_date
+FROM enrollment e
+JOIN users u
+    ON u.id = e.user_id
+JOIN user_entity ue
+    ON ue.id = u.id
+JOIN course c
+    ON c.course_id = e.course_id
+LEFT JOIN user_subscription us
+    ON us.user_id = e.user_id
+   AND us.status = 'ACTIVE'
+   AND CURRENT_DATE BETWEEN us.start_date AND us.end_date;
+}}}
+== Data constraints requirements: Appointment owner consistency, no overlap scheduling
+=== Data requirements description
+Appointments must obey real scheduling constraints:
+* The responsible_owner_id for an appointment must match the owner of the animal.
+* The same owner cannot have overlapping appointments.
+* The same animal cannot have overlapping appointments.
+* Confirming appointments in the past is blocked.
+=== Implementation
+{{{Triggers}}}
+{{{
+CREATE OR REPLACE FUNCTION petify_trg_appointments_enforce()
+RETURNS trigger
+LANGUAGE plpgsql
+AS $$
+DECLARE
+  v_animal_owner bigint;
+BEGIN
+  SELECT owner_id INTO v_animal_owner
+  FROM animals
+  WHERE animal_id = NEW.animal_id;
+
+  IF v_animal_owner IS NULL THEN
+    RAISE EXCEPTION 'Animal % not found for appointment', NEW.animal_id;
+  END IF;
+
+  IF NEW.responsible_owner_id <> v_animal_owner THEN
+    RAISE EXCEPTION
+      'Appointment responsible_owner_id (%) must match animals.owner_id (%) for animal %',
+      NEW.responsible_owner_id, v_animal_owner, NEW.animal_id;
+  END IF;
+
+  IF NEW.status = 'CONFIRMED' AND NEW.date_time < now() THEN
+    RAISE EXCEPTION 'Cannot CONFIRM an appointment in the past (date_time=%)', NEW.date_time;
+  END IF;
+
+  IF NEW.status = 'DONE' AND NEW.date_time > now() THEN
+    RAISE EXCEPTION 'Cannot mark DONE for an appointment that is in the future (date_time=%)', NEW.date_time;
+  END IF;
+
+  RETURN NEW;
+END;
+$$;
+
+DO $$
+BEGIN
+  IF NOT EXISTS (
+    SELECT 1
+    FROM information_schema.columns
+    WHERE table_name = 'appointments'
+      AND column_name = 'slot'
+  ) THEN
+    ALTER TABLE appointments
+      ADD COLUMN slot tstzrange
+      GENERATED ALWAYS AS (
+        tstzrange(date_time, date_time + interval '30 minutes', '[)')
+      ) STORED;
+  END IF;
+END $$;
+
+CREATE OR REPLACE FUNCTION petify_trg_appointments_no_overlap()
+RETURNS trigger
+LANGUAGE plpgsql
+AS $$
+BEGIN
+  
+  IF NEW.status NOT IN ('CONFIRMED','DONE') THEN
+    RETURN NEW;
+  END IF;
+
+
+  IF EXISTS (
+    SELECT 1
+    FROM appointments a
+    WHERE a.responsible_owner_id = NEW.responsible_owner_id
+      AND a.status IN ('CONFIRMED','DONE')
+      AND a.slot && NEW.slot
+      AND (TG_OP <> 'UPDATE' OR a.appointment_id <> NEW.appointment_id)
+  ) THEN
+    RAISE EXCEPTION
+      'Overlapping appointment for owner % at %',
+      NEW.responsible_owner_id, NEW.date_time;
+  END IF;
+
+ 
+  IF EXISTS (
+    SELECT 1
+    FROM appointments a
+    WHERE a.animal_id = NEW.animal_id
+      AND a.status IN ('CONFIRMED','DONE')
+      AND a.slot && NEW.slot
+      AND (TG_OP <> 'UPDATE' OR a.appointment_id <> NEW.appointment_id)
+  ) THEN
+    RAISE EXCEPTION
+      'Overlapping appointment for animal % at %',
+      NEW.animal_id, NEW.date_time;
+  END IF;
+
+  RETURN NEW;
+END;
+$$;
+
+
+DROP TRIGGER IF EXISTS trg_appointments_enforce ON appointments;
+CREATE TRIGGER trg_appointments_enforce
+BEFORE INSERT OR UPDATE
+ON appointments
+FOR EACH ROW
+EXECUTE FUNCTION petify_trg_appointments_enforce();
+
+DROP TRIGGER IF EXISTS trg_appointments_no_overlap ON appointments;
+CREATE TRIGGER trg_appointments_no_overlap
+BEFORE INSERT OR UPDATE
+ON appointments
+FOR EACH ROW
+EXECUTE FUNCTION petify_trg_appointments_no_overlap();
+
+}}}
+
+{{{Views}}}
+{{{
+CREATE OR REPLACE VIEW v_clinic_appointments_monthly AS
+SELECT
+  clinic_id,
+  date_trunc('month', date_time) AS month,
+  COUNT(*) FILTER (WHERE status='CONFIRMED') AS confirmed_cnt,
+  COUNT(*) FILTER (WHERE status='DONE')      AS done_cnt,
+  COUNT(*) FILTER (WHERE status='NO_SHOW')   AS no_show_cnt,
+  COUNT(*) FILTER (WHERE status='CANCELLED') AS cancelled_cnt,
+  COUNT(*) AS total_cnt
+FROM appointments
+GROUP BY clinic_id, date_trunc('month', date_time)
+ORDER BY month DESC;
+
+}}}
+== Data constraints requirements: Health Record integrity 
+=== Data requirements description
+Health records must be consistent:
+* A health record must be tied to an appointment that is DONE.
+* The animal in health_records must match the animal of the referenced appointment.
+* The record date must match the appointment date.
+=== Implementation
+{{{Triggers}}}
+{{{
+CREATE OR REPLACE FUNCTION petify_trg_health_records_enforce()
+RETURNS trigger
+LANGUAGE plpgsql
+AS $$
+DECLARE
+  v_appt_animal bigint;
+  v_appt_status text;
+  v_appt_date date;
+BEGIN
+  SELECT a.animal_id, a.status, a.date_time::date
+  INTO v_appt_animal, v_appt_status, v_appt_date
+  FROM appointments a
+  WHERE a.appointment_id = NEW.appointment_id;
+
+  IF v_appt_animal IS NULL THEN
+    RAISE EXCEPTION 'Appointment % not found for health record', NEW.appointment_id;
+  END IF;
+
+  IF NEW.animal_id <> v_appt_animal THEN
+    RAISE EXCEPTION 'Health record animal_id (%) must match appointment animal_id (%) for appointment %',
+      NEW.animal_id, v_appt_animal, NEW.appointment_id;
+  END IF;
+
+  IF v_appt_status <> 'DONE' THEN
+    RAISE EXCEPTION 'Cannot insert health record unless appointment % is DONE (current status=%)',
+      NEW.appointment_id, v_appt_status;
+  END IF;
+
+  IF NEW.date <> v_appt_date THEN
+    RAISE EXCEPTION 'Health record date (%) must equal appointment date (%) for appointment %',
+      NEW.date, v_appt_date, NEW.appointment_id;
+  END IF;
+
+  RETURN NEW;
+END;
+$$;
+
+DROP TRIGGER IF EXISTS trg_health_records_enforce ON health_records;
+CREATE TRIGGER trg_health_records_enforce
+BEFORE INSERT OR UPDATE
+ON health_records
+FOR EACH ROW
+EXECUTE FUNCTION petify_trg_health_records_enforce();
+}}}
+{{{Views}}}
+{{{
+CREATE OR REPLACE VIEW v_health_records_with_context AS
+SELECT
+  hr.healthrecord_id,
+  hr.animal_id,
+  a.name AS animal_name,
+  hr.appointment_id,
+  ap.clinic_id,
+  ap.date_time,
+  ap.status AS appointment_status,
+  hr.type,
+  hr.description,
+  hr.date
+FROM health_records hr
+JOIN animals a      ON a.animal_id = hr.animal_id
+JOIN appointments ap ON ap.appointment_id = hr.appointment_id;
+}}}
+== Data constraints requirements: Review Consistency
+=== Data requirements description
+Reviews are split across multiple tables and require consistency checks:
+* A base reviews row can represent either a user_review or a clinic_review, never both.
+* A reviewer cannot repeatedly review the same target too frequently (30-day cooldown).
+* Prevent self-review.
+=== Implementation
+{{{Triggers}}}
+{{{
+CREATE OR REPLACE FUNCTION petify_trg_reviews_no_update()
+RETURNS trigger
+LANGUAGE plpgsql
+AS $$
+BEGIN
+  RAISE EXCEPTION 'Reviews are immutable. Updates are not allowed (review_id=%).', OLD.review_id;
+END;
+$$;
+
+DROP TRIGGER IF EXISTS trg_reviews_no_update ON reviews;
+CREATE TRIGGER trg_reviews_no_update
+BEFORE UPDATE
+ON reviews
+FOR EACH ROW
+EXECUTE FUNCTION petify_trg_reviews_no_update();
+
+DROP TRIGGER IF EXISTS trg_clinic_reviews_no_update ON clinic_reviews;
+CREATE TRIGGER trg_clinic_reviews_no_update
+BEFORE UPDATE
+ON clinic_reviews
+FOR EACH ROW
+EXECUTE FUNCTION petify_trg_no_update_generic();
+
+CREATE OR REPLACE FUNCTION petify_trg_user_review_exclusive()
+RETURNS trigger
+LANGUAGE plpgsql
+AS $$
+BEGIN
+  IF EXISTS (SELECT 1 FROM clinic_reviews cr WHERE cr.review_id = NEW.review_id) THEN
+    RAISE EXCEPTION 'review_id % already used as clinic review (cannot also be user review)', NEW.review_id;
+  END IF;
+  RETURN NEW;
+END;
+$$;
+
+DROP TRIGGER IF EXISTS trg_user_review_exclusive ON user_reviews;
+CREATE TRIGGER trg_user_review_exclusive
+BEFORE INSERT
+ON user_reviews
+FOR EACH ROW
+EXECUTE FUNCTION petify_trg_user_review_exclusive();
+
+
+CREATE OR REPLACE FUNCTION petify_trg_clinic_review_exclusive()
+RETURNS trigger
+LANGUAGE plpgsql
+AS $$
+BEGIN
+  IF EXISTS (SELECT 1 FROM user_reviews ur WHERE ur.review_id = NEW.review_id) THEN
+    RAISE EXCEPTION 'review_id % already used as user review (cannot also be clinic review)', NEW.review_id;
+  END IF;
+  RETURN NEW;
+END;
+$$;
+
+DROP TRIGGER IF EXISTS trg_clinic_review_exclusive ON clinic_reviews;
+CREATE TRIGGER trg_clinic_review_exclusive
+BEFORE INSERT
+ON clinic_reviews
+FOR EACH ROW
+EXECUTE FUNCTION petify_trg_clinic_review_exclusive();
+
+CREATE OR REPLACE FUNCTION petify_trg_user_reviews_cooldown()
+RETURNS trigger
+LANGUAGE plpgsql
+AS $$
+DECLARE
+  v_reviewer bigint;
+  v_created  timestamp;
+BEGIN
+  SELECT reviewer_id, created_at INTO v_reviewer, v_created
+  FROM reviews
+  WHERE review_id = NEW.review_id;
+
+  IF v_reviewer IS NULL THEN
+    RAISE EXCEPTION 'Base review % not found', NEW.review_id;
+  END IF;
+
+  IF v_reviewer = NEW.target_user_id THEN
+    RAISE EXCEPTION 'User cannot review themselves (user_id=%)', v_reviewer;
+  END IF;
+
+  IF EXISTS (
+    SELECT 1
+    FROM user_reviews ur
+    JOIN reviews r ON r.review_id = ur.review_id
+    WHERE r.reviewer_id = v_reviewer
+      AND ur.target_user_id = NEW.target_user_id
+      AND r.is_deleted = false
+      AND r.created_at >= v_created - interval '30 days'
+  ) THEN
+    RAISE EXCEPTION 'Cooldown: reviewer % already reviewed user % within last 30 days',
+      v_reviewer, NEW.target_user_id;
+  END IF;
+
+  RETURN NEW;
+END;
+$$;
+
+DROP TRIGGER IF EXISTS trg_user_reviews_cooldown ON user_reviews;
+CREATE TRIGGER trg_user_reviews_cooldown
+BEFORE INSERT
+ON user_reviews
+FOR EACH ROW
+EXECUTE FUNCTION petify_trg_user_reviews_cooldown();
+
+CREATE OR REPLACE FUNCTION petify_trg_clinic_reviews_cooldown()
+RETURNS trigger
+LANGUAGE plpgsql
+AS $$
+DECLARE
+  v_reviewer bigint;
+  v_created  timestamp;
+BEGIN
+  SELECT reviewer_id, created_at INTO v_reviewer, v_created
+  FROM reviews
+  WHERE review_id = NEW.review_id;
+
+  IF v_reviewer IS NULL THEN
+    RAISE EXCEPTION 'Base review % not found', NEW.review_id;
+  END IF;
+
+  IF EXISTS (
+    SELECT 1
+    FROM clinic_reviews cr
+    JOIN reviews r ON r.review_id = cr.review_id
+    WHERE r.reviewer_id = v_reviewer
+      AND cr.target_clinic_id = NEW.target_clinic_id
+      AND r.is_deleted = false
+      AND r.created_at >= v_created - interval '30 days'
+  ) THEN
+    RAISE EXCEPTION 'Cooldown: reviewer % already reviewed clinic % within last 30 days',
+      v_reviewer, NEW.target_clinic_id;
+  END IF;
+
+  RETURN NEW;
+END;
+$$;
+
+DROP TRIGGER IF EXISTS trg_clinic_reviews_cooldown ON clinic_reviews;
+CREATE TRIGGER trg_clinic_reviews_cooldown
+BEFORE INSERT
+ON clinic_reviews
+FOR EACH ROW
+EXECUTE FUNCTION petify_trg_clinic_reviews_cooldown();
+
+}}}
+{{{Views}}}
+{{{
+CREATE OR REPLACE VIEW v_user_ratings AS
+SELECT
+  ur.target_user_id,
+  COUNT(*) FILTER (WHERE r.is_deleted = false) AS review_count,
+  ROUND(AVG(r.rating)::numeric, 2) FILTER (WHERE r.is_deleted = false) AS avg_rating
+FROM user_reviews ur
+JOIN reviews r ON r.review_id = ur.review_id
+GROUP BY ur.target_user_id;
+
+CREATE OR REPLACE VIEW v_clinic_ratings AS
+SELECT
+  cr.target_clinic_id,
+  COUNT(*) FILTER (WHERE r.is_deleted = false) AS review_count,
+  ROUND(AVG(r.rating)::numeric, 2) FILTER (WHERE r.is_deleted = false) AS avg_rating
+FROM clinic_reviews cr
+JOIN reviews r ON r.review_id = cr.review_id
+GROUP BY cr.target_clinic_id;
+}}}
+{{{Custom domains}}}
+{{{
+CREATE DOMAIN rating_1_5 AS int CHECK (VALUE BETWEEN 1 AND 5);
+}}}
+== Data constraints requirements: Background Jobs
+=== Data requirements description
+These are time-based business rules that must be enforced asynchronously:
+* If an appointment is still CONFIRMED long after its scheduled time, mark it as NO_SHOW.
+* If an Archive listing is older than 30days its status is draft.
+=== Implementation
+{{{Stored procedures}}}
+{{{
+CREATE EXTENSION IF NOT EXISTS pg_cron;
+
+CREATE OR REPLACE PROCEDURE job_mark_no_show()
+LANGUAGE plpgsql
+AS $$
+BEGIN
+  UPDATE appointments
+  SET status = 'NO_SHOW'
+  WHERE status = 'CONFIRMED'
+    AND date_time < now() - interval '45 minutes';
+END;
+$$;
+
+CREATE OR REPLACE PROCEDURE job_archive_stale_drafts()
+LANGUAGE plpgsql
+AS $$
+BEGIN
+  UPDATE listings
+  SET status = 'ARCHIVED'
+  WHERE status = 'DRAFT'
+    AND created_at < now() - interval '30 days';
+END;
+$$;
+}}}
+{{{Views}}}
+{{{
+CREATE OR REPLACE VIEW v_overdue_confirmed_appointments AS
+SELECT *
+FROM appointments
+WHERE status='CONFIRMED'
+  AND date_time < now() - interval '45 minutes';
+
+CREATE OR REPLACE VIEW v_stale_draft_listings AS
+SELECT *
+FROM listings
+WHERE status='DRAFT'
+  AND created_at < now() - interval '30 days';
+}}}
+{{{Scheduling}}}
+{{{
+SELECT cron.schedule('petify_mark_no_show', '*/10 * * * *', $$CALL job_mark_no_show();$$);
+SELECT cron.schedule('petify_archive_stale_drafts_daily', '10 2 * * *', $$CALL job_archive_stale_drafts();$$);
+}}}