Advanced Database Development

Data constrains requirements: Listing-Animal ownership consistency + single active listing

Data requirements description

Petify listings must stay consistent with the animal they refer to:

• listings.owner_id must always match animals.owner_id for the same animal_id.
• An animal can have at most one ACTIVE listing at a time.
• Listing status changes must follow a valid transition model.

Implementation

Triggers

-- status transition
CREATE OR REPLACE FUNCTION petify_is_valid_listing_transition(p_old text, p_new text)
RETURNS boolean
LANGUAGE sql
AS $$
  SELECT CASE
    WHEN p_old = p_new THEN true
    WHEN p_old = 'DRAFT'    AND p_new IN ('ACTIVE','ARCHIVED') THEN true
    WHEN p_old = 'ACTIVE'   AND p_new IN ('SOLD','ARCHIVED')   THEN true
    WHEN p_old = 'SOLD'     AND p_new IN ('ARCHIVED')          THEN true
    WHEN p_old = 'ARCHIVED' THEN false
    ELSE false
  END;
$$;

-- same owner
CREATE OR REPLACE FUNCTION petify_trg_listings_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', NEW.animal_id;
  END IF;

  IF NEW.owner_id <> v_animal_owner THEN
    RAISE EXCEPTION 'Listing owner_id (%) must match animal.owner_id (%) for animal %',
      NEW.owner_id, v_animal_owner, NEW.animal_id;
  END IF;

  IF TG_OP = 'UPDATE' THEN
    IF NOT petify_is_valid_listing_transition(OLD.status, NEW.status) THEN
      RAISE EXCEPTION 'Invalid listing status transition: % -> %', OLD.status, NEW.status;
    END IF;
  END IF;

  IF NEW.status = 'ACTIVE' THEN
    IF EXISTS (
      SELECT 1
      FROM listings l
      WHERE l.animal_id = NEW.animal_id
        AND l.status = 'ACTIVE'
        AND (TG_OP <> 'UPDATE' OR l.listing_id <> NEW.listing_id)
    ) THEN
      RAISE EXCEPTION 'Animal % already has an ACTIVE listing', NEW.animal_id;
    END IF;
  END IF;

  RETURN NEW;
END;
$$;

DROP TRIGGER IF EXISTS trg_listings_enforce ON listings;
CREATE TRIGGER trg_listings_enforce
BEFORE INSERT OR UPDATE
ON listings
FOR EACH ROW
EXECUTE FUNCTION petify_trg_listings_enforce();

Stored procedures

CREATE OR REPLACE VIEW v_listings_enriched AS
SELECT
  l.listing_id,
  l.status,
  l.price,
  l.created_at,
  l.owner_id AS listing_owner_id,
  a.animal_id,
  a.name AS animal_name,
  a.species,
  a.breed,
  a.located_name,
  a.owner_id AS animal_owner_id,
  (l.owner_id = a.owner_id) AS owner_match
FROM listings l
JOIN animals a ON a.animal_id = l.animal_id;

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

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

Stored procedures

-- 30min timeslots
CREATE EXTENSION IF NOT EXISTS btree_gist;

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

DO $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'appointments_no_overlap_per_owner') THEN
    ALTER TABLE appointments
      ADD CONSTRAINT appointments_no_overlap_per_owner
      EXCLUDE USING gist (responsible_owner_id WITH =, slot WITH &&)
      WHERE (status IN ('CONFIRMED','DONE'));
  END IF;
END $$;

DO $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'appointments_no_overlap_per_animal') THEN
    ALTER TABLE appointments
      ADD CONSTRAINT appointments_no_overlap_per_animal
      EXCLUDE USING gist (animal_id WITH =, slot WITH &&)
      WHERE (status IN ('CONFIRMED','DONE'));
  END IF;
END $$;

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

RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  RAISE EXCEPTION 'Reviews cannot be deleted directly. Use the soft-delete procedure.';
END;
$$;

DROP TRIGGER IF EXISTS trg_reviews_no_delete ON reviews;
CREATE TRIGGER trg_reviews_no_delete
BEFORE DELETE
ON reviews
FOR EACH ROW
EXECUTE FUNCTION petify_trg_reviews_no_delete();

CREATE OR REPLACE FUNCTION petify_trg_no_update_generic()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  RAISE EXCEPTION 'Updates are not allowed on this table.';
END;
$$;

DROP TRIGGER IF EXISTS trg_user_reviews_no_update ON user_reviews;
CREATE TRIGGER trg_user_reviews_no_update
BEFORE UPDATE
ON user_reviews
FOR EACH ROW
EXECUTE FUNCTION petify_trg_no_update_generic();

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

Stored procedures

CREATE OR REPLACE PROCEDURE petify_soft_delete_review(p_review_id bigint)
LANGUAGE plpgsql
AS $$
BEGIN
 
  PERFORM set_config('session_replication_role', 'replica', true);

  UPDATE reviews
  SET is_deleted = true,
      updated_at = now()
  WHERE review_id = p_review_id;

  PERFORM set_config('session_replication_role', 'origin', true);
END;
$$;

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