Advanced Database Development (SQL DDL)

Custom Domains

Automatically updates the story timestamp on every update

CREATE DOMAIN valid_email AS VARCHAR(255)
CHECK (VALUE ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');

Check rating range from 0 to 5

CREATE DOMAIN story_rating AS DECIMAL(3,2)
CHECK (VALUE >= 0.00 AND VALUE <= 5.00);

Status of accepted values

CREATE DOMAIN story_status AS VARCHAR(50)
CHECK (VALUE IN ('draft', 'published', 'archived'));

Permission level from 1 to 5

CREATE DOMAIN permission_lvl AS INTEGER
CHECK (VALUE >= 1 AND VALUE <= 5);

URL / image path

CREATE DOMAIN valid_url AS VARCHAR(2048)
CHECK (VALUE ~* '^https?://.+');

Views

Detailed view for each story with statistic

CREATE OR REPLACE VIEW story_details_view AS
SELECT
    s.story_id,
    s.short_description,
    s.mature_content,
    s.image,
    s.story_created_at,
    u.username AS writer,
    u.user_name,
    u.surname,
    st.status,
    COUNT(DISTINCT ch.chapter_id) AS total_chapters,
    COALESCE(SUM(ch.view_count), 0) AS total_views,
    ROUND(AVG(ch.rating), 2) AS avg_rating,
    COUNT(DISTINCT l.user_id) AS total_likes,
    COUNT(DISTINCT c.comment_id) AS total_comments,
    COUNT(DISTINCT hg.genre_id) AS total_genres,
    COUNT(DISTINCT col.user_id) AS total_collaborators
FROM story s
JOIN writer w   ON s.user_id    = w.user_id
JOIN users u   ON w.user_id    = u.user_id
JOIN "status" st  ON s.story_id   = st.story_id
LEFT JOIN chapter ch  ON s.story_id   = ch.story_id
LEFT JOIN likes l   ON s.story_id   = l.story_id
LEFT JOIN "comment" c   ON s.story_id   = c.story_id
LEFT JOIN has_genre hg  ON s.story_id   = hg.story_id
LEFT JOIN collaboration col ON s.story_id   = col.story_id
GROUP BY
    s.story_id, s.short_description, s.mature_content,
    s.image, s.story_created_at,
    u.username, u.user_name, u.surname, st.status;

Top 10 most popular stories by likes count

CREATE MATERIALIZED VIEW top_stories_by_likes AS
SELECT
    s.story_id,
    s.short_description,
    u.username AS writer,
    st.status,
    COUNT(DISTINCT l.user_id)  AS total_likes,
    COUNT(DISTINCT c.comment_id) AS total_comments,
    ROUND(AVG(ch.rating), 2) AS avg_rating
FROM story s
JOIN writer w   ON s.user_id    = w.user_id
JOIN users u   ON w.user_id    = u.user_id
JOIN "status" st  ON s.story_id   = st.story_id
AND st.status   = 'published'
LEFT JOIN likes l   ON s.story_id   = l.story_id
LEFT JOIN "comment" c   ON s.story_id   = c.story_id
LEFT JOIN chapter  ch  ON s.story_id   = ch.story_id
GROUP BY
    s.story_id, s.short_description,
    u.username, st.status
ORDER BY total_likes DESC
LIMIT 10;

View for user profile with all his stories

CREATE OR REPLACE VIEW writer_profile_view AS
SELECT
    u.user_id,
    u.username,
    u.user_name,
    u.surname,
    u.user_created_at,
    COUNT(DISTINCT s.story_id)AS total_stories,
    COUNT(DISTINCT ch.chapter_id) AS total_chapters,
    COALESCE(SUM(ch.view_count), 0) AS total_views,
    COUNT(DISTINCT l.user_id) AS total_likes,
    COUNT(DISTINCT c.comment_id) AS total_comments,
    ROUND(AVG(ch.rating), 2) AS avg_rating
FROM users u
JOIN writer w   ON u.user_id    = w.user_id
LEFT JOIN story s   ON w.user_id    = s.user_id
LEFT JOIN chapter ch  ON s.story_id   = ch.story_id
LEFT JOIN likes l   ON s.story_id   = l.story_id
LEFT JOIN comment  c   ON s.story_id   = c.story_id
GROUP BY
    u.user_id, u.username, u.user_name,
    u.surname, u.user_created_at;

Stored Procedures and Functions

Table for daily top story

CREATE TABLE daily_top_stories (
    day         DATE PRIMARY KEY,
    story_id    INTEGER NULL REFERENCES story(story_id),
    total_likes BIGINT
);

Saves the most liked story of the previous day

CREATE OR REPLACE PROCEDURE get_daily_top_story()
LANGUAGE plpgsql
AS $$
DECLARE
    yesterday TIMESTAMP;
BEGIN
    yesterday := CURRENT_DATE - 1;

    IF EXISTS (
        SELECT 1 FROM likes
        WHERE like_created_at BETWEEN yesterday AND CURRENT_DATE
    ) THEN
        INSERT INTO daily_top_stories (day, story_id, total_likes)
        SELECT
            CURRENT_DATE - 1,
            s.story_id,
            COUNT(l.user_id) AS total_likes
        FROM story          s
        JOIN likes          l ON s.story_id = l.story_id
        WHERE l.like_created_at BETWEEN yesterday AND CURRENT_DATE
        GROUP BY s.story_id
        ORDER BY total_likes DESC
        LIMIT 1;
    ELSE
        INSERT INTO daily_top_stories (day, story_id, total_likes)
        VALUES (CURRENT_DATE - 1, NULL, 0);
    END IF;
END;
$$;

Sends a notification to all collaborators of a story

CREATE OR REPLACE PROCEDURE notify_collaborators(
    p_story_id  INTEGER,
    p_message   TEXT,
    p_type      VARCHAR(50),
    p_link      VARCHAR(500)
)
LANGUAGE plpgsql
AS $$
DECLARE
    collab RECORD;
BEGIN
    FOR collab IN
        SELECT c.user_id
        FROM collaboration c
        WHERE c.story_id = p_story_id
    LOOP
        INSERT INTO notification (
            notification_content,
            is_read,
            recipient_user_id,
            type,
            link,
            notification_created_at
        )
        VALUES (
            p_message,
            FALSE,
            collab.user_id,
            p_type,
            p_link,
            CURRENT_TIMESTAMP
        );
    END LOOP;
END;
$$;

Function

Calculates the engagement score for a given story

CREATE OR REPLACE FUNCTION calculate_engagement_score(p_story_id INTEGER)
RETURNS DECIMAL
LANGUAGE sql
AS $$
    SELECT ROUND(
        (COUNT(DISTINCT l.user_id) + COUNT(DISTINCT c.comment_id))::DECIMAL
        / NULLIF(COALESCE(SUM(ch.view_count), 0), 0) * 100, 2
    )
    FROM story s
    LEFT JOIN likes l   ON s.story_id = l.story_id
    LEFT JOIN "comment" c   ON s.story_id = c.story_id
    LEFT JOIN chapter ch  ON s.story_id = ch.story_id
    WHERE s.story_id = p_story_id;
$$;

Triggers

Automatically updates the story timestamp on every update

CREATE OR REPLACE FUNCTION update_timestamp()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    NEW.story_updated_at := CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$;

CREATE OR REPLACE TRIGGER story_update_timestamp
    BEFORE UPDATE ON story
    FOR EACH ROW
EXECUTE FUNCTION update_timestamp();

Sends a notification to the writer when someone likes their story

CREATE OR REPLACE FUNCTION notify_writer_on_like()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
    v_writer_id      INTEGER;
    v_liker_username VARCHAR;
    v_story_desc     VARCHAR;
BEGIN
    SELECT s.user_id, s.short_description
    INTO v_writer_id, v_story_desc
    FROM story s
    WHERE s.story_id = NEW.story_id;

    SELECT username INTO v_liker_username
    FROM users
    WHERE user_id = NEW.user_id;

    -- Do not notify if the writer likes their own story
    IF v_writer_id != NEW.user_id THEN
        INSERT INTO notification (
            notification_content,
            is_read,
            recipient_user_id,
            type,
            link,
            notification_created_at
        )
        VALUES (
            v_liker_username || ' liked your story "' || v_story_desc || '"',
            FALSE,
            v_writer_id,
            'like',
            '/story/' || NEW.story_id::VARCHAR,
            CURRENT_TIMESTAMP
        );
    END IF;

    RETURN NEW;
END;
$$;

CREATE OR REPLACE TRIGGER on_story_liked
    AFTER INSERT ON likes
    FOR EACH ROW
EXECUTE FUNCTION notify_writer_on_like();

Prevents adding a story to a reading list if it is not published

CREATE OR REPLACE FUNCTION check_story_published_before_adding()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM "status"
        WHERE story_id = NEW.story_id
          AND status   = 'published'
    ) THEN
        RAISE EXCEPTION
            'story_id=% is not published and cannot be added to a reading list.',
            NEW.story_id;
    END IF;
    RETURN NEW;
END;
$$;

CREATE OR REPLACE TRIGGER enforce_published_before_list_add
    BEFORE INSERT ON reading_list_items
    FOR EACH ROW
EXECUTE FUNCTION check_story_published_before_adding();

Trigger: prevents a writer from collaborating on their own story

CREATE OR REPLACE FUNCTION check_not_own_story()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    IF EXISTS (
        SELECT 1 FROM story
        WHERE story_id = NEW.story_id
          AND user_id  = NEW.user_id
    ) THEN
        RAISE EXCEPTION
            'user_id=% is the owner of story_id=% and cannot be a collaborator.',
            NEW.user_id,
            NEW.story_id;
    END IF;
    RETURN NEW;
END;
$$;

CREATE OR REPLACE TRIGGER prevent_self_collaboration
    BEFORE INSERT ON collaboration
    FOR EACH ROW
EXECUTE FUNCTION check_not_own_story();