Context Navigation

← Previous Version
View Latest Version
Next Version →

Version 1 (modified by 221296, 2 weeks ago) ( diff )
--

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();$$);

Note: See TracWiki for help on using the wiki.

Download in other formats:

Plain Text