Advanced Database Development (SQL DDL)

1. Data constraints requirements: Booking Overlapping and Sitter Availability

Data requirements description

Bookings must adhere to real world time and scheduling:

• A Pet Sitter cannot have overlapping confirmed bookings - they cant be in two places at once.
• A Pet Owner cannot book the same pet twice on overlapping dates/times.
• A booking cannot be created if the date_from is in the past.

Implementation

Triggers

CREATE OR REPLACE FUNCTION petsitter_trg_booking_validation()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  IF NEW.date_from < now() THEN
    RAISE EXCEPTION 'Cannot create a booking in the past (date_from=%)', NEW.date_from;
  END IF;

  IF NEW.date_to <= NEW.date_from THEN
    RAISE EXCEPTION 'Booking end date (%) must be after start date (%)', NEW.date_to, NEW.date_from;
  END IF;

  IF NEW.status = 'Confirmed' THEN
    IF EXISTS (
      SELECT 1 FROM bookings b
      WHERE b.sitter_id = NEW.sitter_id
        AND b.status = 'Confirmed'
        AND b.date_from < NEW.date_to 
        AND b.date_to > NEW.date_from
        AND (TG_OP <> 'UPDATE' OR b.booking_id <> NEW.booking_id)
    ) THEN
      RAISE EXCEPTION 'Sitter % is already booked for these dates.', NEW.sitter_id;
    END IF;
  END IF;

  RETURN NEW;
END;
$$;

DROP TRIGGER IF EXISTS trg_booking_validation ON bookings;
CREATE TRIGGER trg_booking_validation
BEFORE INSERT OR UPDATE ON bookings
FOR EACH ROW
EXECUTE FUNCTION petsitter_trg_booking_validation();

---

2. Data constraints requirements: Review/Transaction Integrity

Data requirements description

Reviews are associated with the completion of a booking:

• A review can only be inserted if the relevant booking has the status 'Completed'.
• A booking can have a maximum of ONE (1) review.
• Once a review is created, the rating cannot be edited.

Implementation

Triggers

CREATE OR REPLACE FUNCTION petsitter_trg_review_integrity()
RETURNS trigger
LANGUAGE plpgsql
AS $$
DECLARE
  v_booking_status varchar;
BEGIN
  -- Get booking status
  SELECT status INTO v_booking_status
  FROM bookings
  WHERE booking_id = NEW.booking_id;

  IF v_booking_status IS NULL THEN
    RAISE EXCEPTION 'Booking % not found.', NEW.booking_id;
  END IF;

  -- Block reviews for bookings that arent completed
  IF v_booking_status <> 'Completed' THEN
    RAISE EXCEPTION 'Cannot leave a review for a booking that is % (Must be Completed)', v_booking_status;
  END IF;

  RETURN NEW;
END;
$$;

DROP TRIGGER IF EXISTS trg_review_integrity ON reviews;
CREATE TRIGGER trg_review_integrity
BEFORE INSERT ON reviews
FOR EACH ROW
EXECUTE FUNCTION petsitter_trg_review_integrity();

-- Make reviews non-updateable
CREATE OR REPLACE FUNCTION petsitter_trg_reviews_no_update()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  RAISE EXCEPTION 'Reviews are permanent. Updates are not allowed for 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 petsitter_trg_reviews_no_update();

---

3. Data constraints requirements: Platform Analytics and Reporting

Data requirements description

The system can create reports of long term business operations:

• Monthly reports: Calculates total revenue and booking counts per sitter, per month.
• Top Rated Sitters: Calculates average ratings and total completed jobs.

Implementation

Views

CREATE OR REPLACE VIEW v_monthly_sitter_reports AS
SELECT 
  u.user_id AS sitter_id,
  u.first_name,
  u.last_name,
  date_trunc('month', b.date_to) AS month,
  COUNT(b.booking_id) AS total_completed_jobs,
  SUM(p.amount) AS total_revenue
FROM users u
JOIN bookings b ON u.user_id = b.sitter_id
JOIN payments p ON b.booking_id = p.booking_id
WHERE b.status = 'Completed'
GROUP BY u.user_id, u.first_name, u.last_name, date_trunc('month', b.date_to)
ORDER BY month DESC, total_revenue DESC;

CREATE OR REPLACE VIEW v_top_rated_sitters AS
SELECT 
  u.user_id AS sitter_id,
  u.first_name,
  u.last_name,
  COUNT(r.review_id) AS total_reviews,
  ROUND(AVG(r.rating)::numeric, 2) AS average_rating
FROM users u
JOIN bookings b ON u.user_id = b.sitter_id
JOIN reviews r ON b.booking_id = r.booking_id
GROUP BY u.user_id, u.first_name, u.last_name
HAVING COUNT(r.review_id) > 0
ORDER BY average_rating DESC, total_reviews DESC;

---

4. Data constraints requirements: Background Jobs (Automated System Cleanup)

Data requirements description

Rules for pending bookings that must be enforced asynchronously by the database server to keep the platform clean:

• If a booking request remains 'Pending' and the date_from passes, it should automatically be marked as 'Expired'.
• Runs automatically every hour

Implementation

Stored procedures

-- Enable pg cronjob
CREATE EXTENSION IF NOT EXISTS pg_cron;

-- Stored procedure for clean up expired bookings
CREATE OR REPLACE PROCEDURE petsitter_job_expire_pending_bookings()
LANGUAGE plpgsql
AS $$
BEGIN
  UPDATE bookings
  SET status = 'Expired'
  WHERE status = 'Pending'
    AND date_from < now();
END;
$$;

Views

CREATE OR REPLACE VIEW v_expired_pending_bookings AS
SELECT *
FROM bookings
WHERE status = 'Expired';

Cronjob

-- Schedule the job to run every hour
SELECT cron.schedule('petsitter_expire_bookings', '0 * * * *', $$CALL petsitter_job_expire_pending_bookings();$$);