Changes between Initial Version and Version 1 of Advanced Database Development

Timestamp:

02/08/26 19:48:20 (18 hours ago)

Author:

231035

Comment:

--

Advanced Database Development

@@ +1 @@
+= 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();$$);
+}}}