Context Navigation

← Previous Change
Wiki History
Next Change →

Changes between Version 4 and Version 5 of AdvancedDB

Timestamp:: 03/28/26 13:45:51 (2 weeks ago)
Author:: 221296
Comment:: --

Legend:

: Unmodified
: Added
: Removed
: Modified

AdvancedDB

-              v4
+              v5
     ON c.course_id = m.course_id;
 }}}
+== Data constraints requirements: Review Consistency
+== Data constraints requirements: Support ticket status consistency + validated ticket attributes
 === 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.
+Support tickets must remain consistent and well-structured throughout their lifecycle:
+* Ticket statuses must follow a controlled set of allowed values.
+* Ticket subjects must not be empty.
+* Ticket handling should support valid status transitions.
+* Ticket data should be easy to inspect together with the related user and administrator information.
 === 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();
+{{{Stored procedures / functions}}}
+{{{
+CREATE OR REPLACE FUNCTION olpms_is_valid_ticket_transition(p_old text, p_new text)
+RETURNS boolean
+LANGUAGE sql
+AS $$
+    SELECT CASE
+        WHEN p_old = p_new THEN true
+        WHEN p_old = 'OPEN' AND p_new IN ('IN_PROGRESS', 'RESOLVED') THEN true
+        WHEN p_old = 'IN_PROGRESS' AND p_new IN ('RESOLVED') THEN true
+        WHEN p_old = 'RESOLVED' AND p_new = 'RESOLVED' THEN true
+        ELSE false
+    END;
+$$;
 }}}
 {{{Views}}}
 {{{
 CREATE OR REPLACE VIEW v_user_ratings AS
+CREATE OR REPLACE VIEW v_support_tickets_enriched 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;
+    st.ticket_id,
+    st.subject,
+    st.description,
+    st.status,
+    st.created_at,
+    st.user_id,
+    u.first_name AS user_first_name,
+    u.last_name AS user_last_name,
+    u.email AS user_email,
+    st.admin_id,
+    a.first_name AS admin_first_name,
+    a.last_name AS admin_last_name,
+    a.email AS admin_email
+FROM support_ticket st
+JOIN user_entity u
+    ON u.id = st.user_id
+JOIN user_entity a
+    ON a.id = st.admin_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();$$);
+}}}
+CREATE DOMAIN ticket_status_domain AS VARCHAR(30)
+CHECK (VALUE IN ('OPEN', 'IN_PROGRESS', 'RESOLVED'));
+CREATE DOMAIN non_empty_subject_domain AS VARCHAR(200)
+CHECK (LENGTH(TRIM(VALUE)) > 0);
+}}}