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: Certificate generation consistency + one certificate per completed enrollment

Data requirements description

Certificates must obey completion and consistency rules:

• A certificate can only be issued for an enrollment whose completion status is COMPLETED.
• The same enrollment cannot have more than one certificate.
• A certificate must always refer to a valid enrollment.
• Issuing a certificate before course completion is blocked.

Implementation

Triggers

CREATE OR REPLACE FUNCTION olpms_trg_certificate_enforce()
RETURNS trigger
LANGUAGE plpgsql
AS $$
DECLARE
    v_completion_status VARCHAR(30);
BEGIN
    -- find the status of the referenced enrollment
    SELECT e.completion_status
    INTO v_completion_status
    FROM enrollment e
    WHERE e.enrollment_id = NEW.enrollment_id;

    IF v_completion_status IS NULL THEN
        RAISE EXCEPTION 'Enrollment % does not exist or has no completion status', NEW.enrollment_id;
    END IF;

    -- certificate can only be issued for completed enrollments
    IF v_completion_status <> 'COMPLETED' THEN
        RAISE EXCEPTION
            'Certificate cannot be issued because enrollment % is not COMPLETED',
            NEW.enrollment_id;
    END IF;

    -- prevent more than one certificate for the same enrollment
    IF EXISTS (
        SELECT 1
        FROM certificate c
        WHERE c.enrollment_id = NEW.enrollment_id
          AND (TG_OP <> 'UPDATE' OR c.certificate_id <> NEW.certificate_id)
    ) THEN
        RAISE EXCEPTION
            'Enrollment % already has a certificate',
            NEW.enrollment_id;
    END IF;

    RETURN NEW;
END;
$$;

DROP TRIGGER IF EXISTS trg_certificate_enforce ON certificate;

CREATE TRIGGER trg_certificate_enforce
BEFORE INSERT OR UPDATE
ON certificate
FOR EACH ROW
EXECUTE FUNCTION olpms_trg_certificate_enforce();

Views

CREATE OR REPLACE VIEW v_certificates_enriched AS
SELECT
    c.certificate_id,
    c.enrollment_id,
    c.issue_date,
    c.certificate_code,
    c.status AS certificate_status,
    e.user_id,
    ue.first_name,
    ue.last_name,
    ue.email,
    e.course_id,
    cr.name AS course_name,
    e.enroll_date,
    e.completion_status,
    e.progress_percentage
FROM certificate c
JOIN enrollment e
    ON e.enrollment_id = c.enrollment_id
JOIN users u
    ON u.id = e.user_id
JOIN user_entity ue
    ON ue.id = u.id
JOIN course cr
    ON cr.course_id = e.course_id;

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