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