ebd

EBD - Database Specification Component

ProGram aims to bring together programmers, developers and other people passionate about informatics, while providing a supportive environment for communication and discussion. Our vision is to create a safe space where like-minded individuals can share ideas, learn new tricks and even start projects together.

Table of Contents

• EBD - Database Specification Component
  • Table of Contents
  • A4 - Conceptual Data Model
    • 1. Class diagram
    • 2. Additional Business Rules
  • A5 - Relational Schema, Validation and Schema Refinement
    • 1. Relational Schema
      • Generalization Justification
    • 2. Domains
    • 3. Schema validation
  • A6 - Indexes, Triggers, Transactions and Database population
    • 1. Database Workload
    • 2. Proposed Indices
      • 2.1. Performance Indices
      • SQL Code
      • SQL Code
      • SQL Code
      • 2.2. Full-text Search Indices
      • SQL Code
      • SQL Code
      • SQL Code
    • 3. Triggers
      • SQL Code
      • SQL Code
      • SQL Code
      • SQL Code
      • SQL Code
      • SQL Code
      • SQL Code
      • SQL Code
      • SQL Code
      • SQL Code
      • SQL Code
      • SQL Code
      • SQL Code
      • SQL Code
      • SQL Code
      • SQL Code
      • SQL Code
    • 4. Transactions
      • SQL Code
      • SQL Code
  • Annex A. SQL Code
    • A.1. Database schema
    • A.2. Database population
  • Usage of Generative AI Tools
  • Revision history

A4 - Conceptual Data Model

The purpose of this artifact is to create a comprehensive conceptual data model that illustrates the relationships among various classes within the system. For this, we provide a class UML diagram for understanding how different entities interact, as well as a table containing the remaining business rules.

1. Class diagram

Fig. 1 Conceptual Model

2. Additional Business Rules

Besides the business rules already specified in the UML Diagram as constraints (such as the self-interaction restrictions), the following table contains additional business rules defined for the project.

Identifier	Name	Description
BR01	Spam Control	Users are limited to creating posts at a rate of 1 post per 30 seconds
BR03	User Deletion Content Handling	When a user is deleted, their content is anonymized, including posts, comments, their names in group and profile
BR04	Group Deletion Handling	When a group is deleted, all of the posts and messages there are also deleted
BR05	Minimum Age	Users must be at least 13 years old to register and use the platform
BR06	Public/Private Profiles	There should be public and privates profiles, private profiles are only available to followers

Table 1: Business Rules

---

A5 - Relational Schema, Validation and Schema Refinement

This artifact contains the Relational Schema obtained by mapping from the Conceptual Data Model. The Relational Schema includes each relation schema, attributes, domains, primary keys, foreign keys and other integrity rules:

• UNIQUE (UK)
• DEFAULT (DF)
• NOT NULL (NN)
• CHECK (CK)

Besides the relational schema, this artifact also contains the validation of each relation according to functional dependencies and normal forms.

1. Relational Schema

Relation reference	Relation Compact Notation
R01	user(id, name, email UK, password, register_timestamp NN DF NOW, handle UK, is_public, description, profile_picture_url, banner_image_url, is_deleted NN DF FALSE, num_followers, num_following)
R02	administrator(id, name NN, email NN UK, password NN, register_timestamp NN DF NOW)
R03	post(id, author_id → user NN, title NN, text, creation_timestamp NN DF NOW, is_announcement NN DF FALSE, is_public NN, likes NN , comments NN)
R04	tag(id, name NN UK)
R05	post_tag(post_id → post, tag_id → tag)
R06	post_attachment(id, post_id → post NN, url NN, type NN CK type IN AttachmentTypes)
R07	post_like(id, liker_id → user NN, post_id → post NN, timestamp NN)
R08	comment(id, post_id → post NN, author_id → user NN, content NN, timestamp NN DF NOW, likes NN)
R09	comment_like(id, comment_id → comment NN, liker_id → user NN, timestamp NN DF NOW)
R10	user_stats(id, user_id → user NN UK, github_url, gitlab_url, linkedin_url)
R11	language(id, name NN UK)
R12	user_stats_language(user_stats_id → user_stats, language_id → language)
R13	technology(id, name NN UK)
R14	user_stats_technology(user_stats_id → user_stats, technology_id → technology)
R15	top_project(id, name NN, url NN, user_stats_id → NN)
R16	follow(id, follower_id → user, followed_id → user CK follower_id != followed_id, timestamp NN DF NOW)
R17	follow_request(id, follower_id → user NN, followed_id → user NN CK follower_id != followed_id, creation_timestamp NN DF NOW, status NN CK status in StatusValues DF 'pending')
R18	ban(id, user_id → user NN, administrator_id → administrator NN, start NN DF NOW, reason NN, duration NN, is_active NN DF TRUE)
R19	token(id, user_id → user UK, administrator_id → post UK CK (user_id IS NULL) XOR (administrator_id IS NULL), value NN, creation_timestamp NN DF NOW, validity_timestamp NN CK validity_timestamp > creation_timestamp)
R20	group(id, owner_id → user NN, name NN UK, description, creation_timestamp NN DF NOW, is_public NN, member_count NN)
R21	group_member(user_id → user, group_id → group, join_timestamp NN DF NOW)
R22	group_post(post_id → post, group_id → group NN)
R23	group_join_request(id, group_id → group NN, requester_id → user NN, creation_timestamp NN DF NOW, status NN CK status in StatusValues DF 'pending')
R24	group_invitation(id, group_id → group NN, invitee_id → user NN, creation_timestamp NN DF NOW, status NN CK status in StatusValues DF 'pending')
R25	notification(id, receiver_id → user NN, timestamp NN DF NOW, is_read NN DF FALSE, type NN CK type in NotificationTypes, follow_id → follow, post_id → post, comment_id → comment, post_like_id → post_like, comment_like_id → comment_like)

Table 2: Relational Model

Generalization Justification

Class	Constraints	Style Used	Justification
Account	Disjoint, Complete	Object-Oriented	Although the base class Account has many attributes, the User and Administrator classes are disjoint and complete and are to be used as two completely different entities, with different login methods, so neither the ER nor the superclass style would be more adequate. This, however, implied that we used two foreign keys for the token relation, one for each subclass' relation.
Notification	Disjoint, Complete	Superclass Approach	Since notifications do not have a strong distinction between each type, having each subclass no attributes of their own, the superclass approach was the best choice. Nonetheless, this has the adversity of having to store a foreign key for each one of the referenced classes and use a check to guarantee the foreign keys have adequate values, according to the notification type.
Post	Incomplete	ER Approach	For this relation, both ER and superclass approaches were viable options, since GroupPost does not have attributes of its own and the generalization is incomplete. We decided on the ER style, not only for a better separation on the posts that are indeed from a group (relevant for search functionalities) but also because we predict most posts will not be from a group, not justifying a foreign key for all of them.
Attachment	Disjoint, Complete	Superclass Approach	Like the Notification class, both attachment types do not have any attributes separately, and are to be used with little distinction between them.

Table 3: Generalization Justification

2. Domains

Domain Name	Domain Specification
AttachmentTypes	ENUM ('image', 'video')
StatusValues	ENUM ('pending', 'accepted', 'rejected')
NotificationTypes	ENUM ('follow', 'comment', 'post_like', 'comment_like', 'post_mention', 'comment_mention')

Table 4: Domains

3. Schema validation

TABLE R01	user
Keys	{id}, {email}, {handle}
Functional Dependencies:
FD0101	{id} → {name, email, password, register_timestamp, handle, is_public, description, profile_picture_url, banner_image_url, is_deleted, num_followers, num_following}
FD0103	{email} → {id, name, password, register_timestamp, handle, is_public, description, profile_picture_url, banner_image_url, is_deleted, num_followers, num_following}
FD0104	{handle} → {id, name, email, password, register_timestamp, is_public, description, profile_picture_url, banner_image_url, is_deleted, num_followers, num_following}
NORMAL FORM	BCNF

TABLE R02	administrator
Keys	{id}, {email}
Functional Dependencies:
FD0201	{id} → {name, email, password, register_timestamp}
FD0202	{email} → {id, name, password, register_timestamp}
NORMAL FORM	BCNF

TABLE R03	post
Keys	{id}
Functional Dependencies:
FD0301	{id} → {author_id, title, text, creation_timestamp, is_announcement, is_public, likes, comments}
NORMAL FORM	BCNF

TABLE R04	tag
Keys	{id}, {name}
Functional Dependencies:
FD0401	{id} → {name}
FD0402	{name} → {id}
NORMAL FORM	BCNF

TABLE R05	post_tag
Keys	{post_id, tag_id}
Functional Dependencies:	None
NORMAL FORM	BCNF

TABLE R06	post_attachment
Keys	{id}
Functional Dependencies:
FD0601	{id} → {post_id, url, type}
NORMAL FORM	BCNF

TABLE R07	post_like
Keys	{id}, {liker_id, post_id}
Functional Dependencies:
FD0701	{id} → {liker_id, post_id, timestamp}
FD0702	{liker_id, post_id} → {id, timestamp}
NORMAL FORM	BCNF

TABLE R08	comment
Keys	{id}
Functional Dependencies:
FD0801	{id} → {post_id, author_id, content, timestamp, likes}
NORMAL FORM	BCNF

TABLE R09	comment_like
Keys	{id}, {comment_id, liker_id}
Functional Dependencies:
FD0901	{id} → {comment_id, liker_id, timestamp}
FD0902	{comment_id, liker_id} → {id, timestamp}
NORMAL FORM	BCNF

TABLE R10	user_stats
Keys	{id}, {user_id}
Functional Dependencies:
FD1001	{id} → {user_id, github_url, gitlab_url, linkedin_url}
FD1002	{user_id} → {id, github_url, gitlab_url, linkedin_url}
NORMAL FORM	BCNF

TABLE R11	language
Keys	{id}, {name}
Functional Dependencies:
FD1101	{id} → {name}
FD1102	{name} → {id}
NORMAL FORM	BCNF

TABLE R12	user_stats_language
Keys	{user_stats_id, language_id}
Functional Dependencies:	None
NORMAL FORM	BCNF

TABLE R13	technology
Keys	{id}, {name}
Functional Dependencies:
FD1301	{id} → {name}
FD1302	{name} → {id}
NORMAL FORM	BCNF

TABLE R14	user_stats_technology
Keys	{user_stats_id, technology_id}
Functional Dependencies:	None
NORMAL FORM	BCNF

TABLE R15	top_project
Keys	{id}
Functional Dependencies:
FD1501	{id} → {name, url, user_stats_id}
NORMAL FORM	BCNF

TABLE R16	follow
Keys	{id}, {follower_id, followed_id}
Functional Dependencies:
FD1601	{id} → {follower_id, followed_id, timestamp}
FD1602	{follower_id, followed_id} → {id, timestamp}
NORMAL FORM	BCNF

TABLE R17	follow_request
Keys	{id}
Functional Dependencies:
FD1701	{id} → {follower_id, followed_id, creation_timestamp, status}
NORMAL FORM	BCNF

TABLE R18	ban
Keys	{id}
Functional Dependencies:
FD1801	{id} → {user_id, administrator_id, start, reason, duration, is_active}
NORMAL FORM	BCNF

TABLE R19	token
Keys	{id}, {user_id}, {administrator_id}
Functional Dependencies:
FD1901	{id} → {user_id, administrator_id, value, creation_timestamp, validity_timestamp}
FD1902	{user_id} → {id, administrator_id, value, creation_timestamp, validity_timestamp}
FD1903	{administrator_id} → {id, user_id, value, creation_timestamp, validity_timestamp}
NORMAL FORM	BCNF

TABLE R20	group
Keys	{id}, {name}
Functional Dependencies:
FD2001	{id} → {owner_id, name, description, creation_timestamp, is_public, member_count}
FD2002	{name} → {id, owner_id, description, creation_timestamp, is_public, member_count}
NORMAL FORM	BCNF

TABLE R21	group_member
Keys	{user_id, group_id}
Functional Dependencies:
FD2101	{user_id, group_id} → {join_timestamp}
NORMAL FORM	BCNF

TABLE R22	group_post
Keys	{post_id}
Functional Dependencies:
FD2201	{post_id} → {group_id}
NORMAL FORM	BCNF

TABLE R23	group_join_request
Keys	{id}
Functional Dependencies:
FD2301	{id} → {group_id, requester_id, creation_timestamp, status}
NORMAL FORM	BCNF

TABLE R24	group_invitation
Keys	{id}
Functional Dependencies:
FD2401	{id} → {group_id, invitee_id, creation_timestamp, status}
NORMAL FORM	BCNF

TABLE R25	notification
Keys	{id}
Functional Dependencies:
FD2501	{id} → {receiver_id, timestamp, is_read, type, follow_id, post_id, comment_id, post_like_id, comment_like_id}
NORMAL FORM	BCNF

In general, all the relations respect the Boyce-Codd Normal Form just after applying the conversion rules from conceptual to relational model, since all the non-trivial functional dependencies of each relation have a super-key on the left side.

---

A6 - Indexes, Triggers, Transactions and Database population

The goal of this artifact is to outline the physical schema of the database, it includes a description of the expected workload, the proposed indices for full text search and performance and triggers to ensure data integrity. In addition, this artifact also clarifies the transactions that we used along with the justification about why they are necessary to maintain data integrity and why did we choose a certain isolation level for them.

1. Database Workload

Relation reference	Relation Name	Order of magnitude	Estimated growth
R01	account	10k	10/day
R02	user	10k	10/day
R03	administrator	10	1/year
R04	post	100k	100/day
R05	tag	100	1/year
R06	post_tag	1M	100/day
R07	post_attachment	1M	100/day
R08	post_like	10M	1k/day
R09	comment	1M	1k/day
R10	comment_like	10M	1k/day
R11	user_stats	10k	10/day
R12	language	10	1/year
R13	user_stats_language	100k	100/day
R14	technology	100	1/year
R15	user_stats_technology	100k	100/day
R16	top_project	10k	10/day
R17	follow	1M	1k/day
R18	follow_request	1M	1k/day
R19	ban	100	10/year
R20	token	1k	10/day
R21	group	100	1/day
R22	group_member	10k	100/day
R23	group_post	100k	100/day
R24	group_join_request	10k	100/day
R25	group_invitation	1k	10/day
R26	notification	10M	10k/day

Table 5: Database Workload

2. Proposed Indices

2.1. Performance Indices

Index	IDX01
Relation	notification
Attributes	receiver_id, timestamp
Type	B-tree
Cardinality	High
Clustering	No
Justification	Since there will be frequent queries to the notifications associated with a specific user, it is useful to have an index on these attributes. Additionally, it is also useful to order the values by their timestamp to get recent notifications first, hence using a B-tree index.

SQL Code

CREATE INDEX notification_receiver_timestamp ON notification USING btree(receiver_id, timestamp DESC);

---

Index	IDX02
Relation	comment
Attributes	post_id, likes
Type	B-tree
Cardinality	High
Clustering	No
Justification	Since we want to perform a query over the comments each time the comments of a post are accessed, it is useful to have an index for this table. Moreover, we can also index on the number of likes of the comment for retrieving the top comments more efficient.

SQL Code

CREATE INDEX comment_post_likes ON comment USING btree(post_id, likes DESC);

---

Index	IDX03
Relation	post
Attributes	author_id, creation_timestamp
Type	B-tree
Cardinality	High
Clustering	No
Justification	Since the posts from a user will be shown on its profile, it is useful an index for these queries. The posts will usually be accessed and sorted by their creation date, so we can add that to the index in order to receive more recent posts first.

SQL Code

CREATE INDEX post_author_creation ON post USING btree(author_id, creation_timestamp DESC);

2.2. Full-text Search Indices

Index	IDX11
Relation	post
Attributes	title, text, users.name, users.handle, comment.content
Type	GiST
Clustering	No
Justification	This index is essential to efficiently provide full-text search on posts, based on their title, content, user and comments' content. Since the index columns span multiple tables, it was necessary to implement more than one trigger, three to be exact, one for each of the relevant tables. A GiST index is used, because it is expected that the table changes frequently.

SQL Code

ALTER TABLE post
ADD COLUMN tsvectors TSVECTOR;

CREATE FUNCTION calculate_post_tsvectors(post_id INTEGER)
RETURNS VOID AS $$
BEGIN
    UPDATE post
    SET tsvectors = (
        setweight(to_tsvector('english', post.title), 'A') ||
        setweight(to_tsvector('english', (
            SELECT users.name
            FROM users
            WHERE users.id = post.author_id
        )), 'A') ||
        setweight(to_tsvector('english', (
            SELECT users.handle
            FROM users
            WHERE users.id = post.author_id
        )), 'A') ||
        setweight(to_tsvector('english', coalesce(post.text, '')), 'B') ||
        setweight(to_tsvector('english', (
            SELECT coalesce(string_agg(comment.content, ' '), '')
            FROM comment
            WHERE comment.post_id = post.id
        )), 'C'))
    WHERE post.id = post_id;
END;
$$ LANGUAGE plpgsql;

CREATE FUNCTION post_search_update()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT'
    OR (TG_OP = 'UPDATE' AND (NEW.title <> OLD.title OR NEW.text <> OLD.text))
    THEN
        PERFORM calculate_post_tsvectors(NEW.id);
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER post_search_update
AFTER INSERT OR UPDATE ON post
FOR EACH ROW
EXECUTE PROCEDURE post_search_update();

CREATE FUNCTION post_comment_search_update()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
        IF TG_OP = 'INSERT' OR NEW.content <> OLD.content THEN
            PERFORM calculate_post_tsvectors(NEW.post_id);
        END IF;

        RETURN NEW;

    ELSIF TG_OP = 'DELETE' THEN
        PERFORM calculate_post_tsvectors(OLD.post_id);

        RETURN OLD;
    END IF;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER post_comment_search_update
AFTER INSERT OR UPDATE OR DELETE ON comment
FOR EACH ROW
EXECUTE PROCEDURE post_comment_search_update();

CREATE FUNCTION post_author_search_update()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'UPDATE' AND (OLD.name <> NEW.name OR OLD.handle <> NEW.handle)
    THEN 
        PERFORM calculate_post_tsvectors(NEW.id);
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER post_author_search_update
AFTER UPDATE ON users
FOR EACH ROW
EXECUTE PROCEDURE post_author_search_update();

CREATE INDEX post_search_idx ON post USING GiST (tsvectors);

---

Index	IDX12
Relation	users
Attributes	name, handle, description
Type	GiST
Clustering	No
Justification	Used to support full-text search over public users, based on their name, handle and description. The index type GiST is used because the table columns are expected to change often.

SQL Code

ALTER TABLE users
ADD COLUMN tsvectors TSVECTOR;

CREATE FUNCTION user_search_update()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT'
    OR (TG_OP = 'UPDATE' AND (NEW.name <> OLD.name OR NEW.handle <> OLD.handle OR NEW.description <> OLD.description))
    THEN
        NEW.tsvectors = (
            setweight(to_tsvector('english', NEW.name), 'A') ||
            setweight(to_tsvector('english', NEW.handle), 'A') ||
            setweight(to_tsvector('english', coalesce(NEW.description, '')), 'B')
        );
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER user_search_update
BEFORE INSERT OR UPDATE ON users 
FOR EACH ROW
EXECUTE PROCEDURE user_search_update();

CREATE INDEX user_search_idx ON users USING GiST (tsvectors);

---

Index	IDX13
Relation	groups
Attributes	name, description
Type	GIN
Clustering	No
Justification	Used for supporting efficient full-text search on the platform's groups, based on their names and descriptions. The index type is GIN, since we do not predict many insertions on the table nor updates of its values.

SQL Code

ALTER TABLE groups
ADD COLUMN tsvectors TSVECTOR;

CREATE FUNCTION group_search_update()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT'
    OR (TG_OP = 'UPDATE' AND (NEW.name <> OLD.name OR NEW.description <> OLD.description))
    THEN
        NEW.tsvectors = (
            setweight(to_tsvector('english', NEW.name), 'A') ||
            setweight(to_tsvector('english', coalesce(NEW.description, '')), 'B')            
        );
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER group_search_update
BEFORE INSERT OR UPDATE ON groups
FOR EACH ROW
EXECUTE PROCEDURE group_search_update();

CREATE INDEX group_search_idx ON groups USING GIN (tsvectors);

---

3. Triggers

Trigger	TRIGGER01
Description	Users should be notified when they receive a comment on their post.
Justification	Ensures users are promptly informed about new comments on their posts, encouraging engagement.

SQL Code

CREATE FUNCTION notify_user_on_comment() 
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO notification (receiver_id, timestamp, is_read, type, comment_id) 
    VALUES (NEW.author_id, CURRENT_TIMESTAMP, FALSE, 'comment', NEW.id);

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER notify_user_on_comment
AFTER INSERT ON comment
FOR EACH ROW
EXECUTE FUNCTION notify_user_on_comment();

---

Trigger	TRIGGER02
Description	Users should be notified when they receive a like on their post.
Justification	Ensures users are promptly informed about new likes on their posts, making them aware about positive reactions to their content and fostering interaction.

SQL Code

CREATE FUNCTION notify_user_on_post_like()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO notification (receiver_id, timestamp, is_read, type, post_like_id) 
    VALUES ((SELECT author_id FROM post WHERE id = NEW.post_id), CURRENT_TIMESTAMP, FALSE, 'post_like', NEW.id);

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER notify_user_on_post_like
AFTER INSERT ON post_like
FOR EACH ROW
EXECUTE FUNCTION notify_user_on_post_like();

---

Trigger	TRIGGER03
Description	Users should be notified when they receive a like on their comment.
Justification	Ensures users are promptly informed about new likes on their comments, making them aware about positive reactions to their content and fostering interaction.

SQL Code

CREATE FUNCTION notify_user_on_comment_like()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO notification (receiver_id, timestamp, is_read, type, comment_like_id) 
    VALUES ((SELECT author_id FROM comment WHERE id = NEW.comment_id), CURRENT_TIMESTAMP, FALSE, 'comment_like', NEW.id);

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER notify_user_on_comment_like
AFTER INSERT ON comment_like
FOR EACH ROW
EXECUTE FUNCTION notify_user_on_comment_like();

---

Trigger	TRIGGER04
Description	Users should be notified when they are followed by another user.
Justification	Ensures that users are promptly notified whenever they are followed by another user, keeping them informed about their followers and encouraging user engagement.

SQL Code

CREATE FUNCTION notify_user_on_follow()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO notification (receiver_id, timestamp, is_read, type, follow_id) 
    VALUES (NEW.followed_id, CURRENT_TIMESTAMP, FALSE, 'follow', NEW.id);

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER notify_user_on_follow
AFTER INSERT ON follow
FOR EACH ROW
EXECUTE FUNCTION notify_user_on_follow();

---

Trigger	TRIGGER05
Description	The owner of a group should be a member of the group as well.
Justification	Ensures that the group creator is automatically added as a member when the group is created. This trigger is used, in opposition to a trigger which checks if the owner is a group member, which would make it impossible to eliminate the group (deleting tuples in group_member would trigger a error, saying that the owner must be a member, before we can eliminate the tuple in groups).

SQL Code

CREATE FUNCTION set_group_owner_as_member()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO group_member (user_id, group_id) 
    VALUES (NEW.owner_id, NEW.id);

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER set_group_owner_as_member
AFTER INSERT ON groups
FOR EACH ROW
EXECUTE FUNCTION set_group_owner_as_member();

---

Trigger	TRIGGER06
Description	When a user accepts a group invitation, the user should be added as a member of the group.
Justification	Ensures that, whenever an invitation is accepted, the user is recorded as a member in the group member table, maintaining accurate group membership records.

SQL Code

CREATE FUNCTION handle_group_invitation_acceptance()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.status = 'accepted' AND OLD.status <> 'accepted' THEN
        INSERT INTO group_member (user_id, group_id, join_timestamp) 
        VALUES (NEW.user_id, NEW.group_id, CURRENT_TIMESTAMP);
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER handle_group_invitation_acceptance
AFTER UPDATE ON group_invitation
FOR EACH ROW
EXECUTE FUNCTION handle_group_invitation_acceptance();

---

Trigger	TRIGGER07
Description	When a user request to join a group is accepted, the user should be added as a member of the group.
Justification	Ensures that when a user’s request to join a group is approved, they are automatically added as a member. This keeps the group member records up-to-date and reflects accurate membership upon acceptance.

SQL Code

CREATE FUNCTION handle_group_join_request_acceptance()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.status = 'accepted' AND OLD.status <> 'accepted' THEN
        INSERT INTO group_member (user_id, group_id, join_timestamp) 
        VALUES (NEW.requester_id, NEW.group_id, CURRENT_TIMESTAMP);
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER handle_group_join_request_acceptance
AFTER UPDATE ON group_join_request
FOR EACH ROW
EXECUTE FUNCTION handle_group_join_request_acceptance();

---

---

Trigger	TRIGGER08
Description	This trigger is used to handle the acceptances of follow requests, ensuring that, when a given request turns into accepted, the follow relationship is inserted.
Justification	Ensures that, when a follow request is accepted, a follow relationship is created in the follow table, accurately reflecting the new connection between the follower and the followed user.

SQL Code

CREATE FUNCTION handle_follow_request_acceptance()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.status = 'accepted' AND OLD.status <> 'accepted' THEN
        INSERT INTO follow (follower_id, followed_id, timestamp) 
        VALUES (NEW.follower_id, NEW.followed_id, CURRENT_TIMESTAMP);
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER handle_follow_request_acceptance
AFTER UPDATE ON follow_request
FOR EACH ROW
EXECUTE FUNCTION handle_follow_request_acceptance();

---

Trigger	TRIGGER09
Description	Users should not be able to like their own posts (BR02).
Justification	Prevents users from liking their own posts, ensuring that the like functionality accurately reflects interactions between different users and upholds the platform's engagement rules.

SQL Code

CREATE FUNCTION enforce_different_post_liker()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.liker_id = (SELECT author_id FROM post WHERE id = NEW.post_id) THEN
        RAISE EXCEPTION 'A user cannot like their own post';
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER enforce_different_post_liker
BEFORE INSERT ON post_like
FOR EACH ROW
EXECUTE FUNCTION enforce_different_post_liker();

---

Trigger	TRIGGER10
Description	Users cannot like their own comments (BR02).
Justification	Ensures that users cannot like their own comments, maintaining the integrity of the liking system and encouraging genuine engagement between users.

SQL Code

CREATE FUNCTION enforce_different_comment_liker()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.liker_id = (SELECT author_id FROM comment WHERE id = NEW.comment_id) THEN
        RAISE EXCEPTION 'A user cannot like their own comment';
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER enforce_different_comment_liker
BEFORE INSERT ON comment_like
FOR EACH ROW
EXECUTE FUNCTION enforce_different_comment_liker();

---

Trigger	TRIGGER11
Description	Authors of group posts must be members of the group.
Justification	Ensures that only members of a group can post in that group, maintaining the integrity of group interactions and ensuring posts are relevant to the group's members.

SQL Code

CREATE FUNCTION enforce_group_post_author_is_member()
RETURNS TRIGGER AS $$
BEGIN
    IF (SELECT author_id FROM post WHERE id = NEW.post_id) NOT IN (SELECT user_id FROM group_member WHERE group_id = NEW.group_id) THEN
        RAISE EXCEPTION 'Post author must be a member of the group';
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER enforce_group_post_author_is_member
BEFORE INSERT OR UPDATE ON group_post
FOR EACH ROW
EXECUTE FUNCTION enforce_group_post_author_is_member();

---

Trigger	TRIGGER12
Description	Each user has a maximum of 10 projects in user statistics.
Justification	Ensures that each user can have no more than 10 top projects, maintaining a limit on the number of projects in user stats and preventing information overload.

SQL Code

CREATE FUNCTION enforce_max_top_projects()
RETURNS TRIGGER AS $$
BEGIN
    IF (SELECT COUNT(*) FROM top_project WHERE user_stats_id = NEW.user_stats_id) >= 10 THEN
        RAISE EXCEPTION 'User cannot have more than 10 top projects';
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER enforce_max_top_projects
BEFORE INSERT ON top_project
FOR EACH ROW
EXECUTE FUNCTION enforce_max_top_projects();

---

Trigger	TRIGGER13
Description	The number of likes a post has should be updated when a like is added or removed.
Justification	Ensures that the number of likes on a post is accurately updated whenever a like is added or removed, maintaining up-to-date like counts for posts.

SQL Code

CREATE FUNCTION update_post_likes()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        UPDATE post
        SET likes = (SELECT count(*) FROM post_like WHERE post_id = NEW.post_id)
        WHERE id = NEW.post_id;

        RETURN NEW;

    ELSIF TG_OP = 'DELETE' THEN
        UPDATE post
        SET likes = (SELECT count(*) FROM post_like WHERE post_id = OLD.post_id)
        WHERE id = OLD.post_id;

        RETURN OLD;
    END IF;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_post_likes
AFTER INSERT OR DELETE ON post_like
FOR EACH ROW
EXECUTE FUNCTION update_post_likes();

---

Trigger	TRIGGER14
Description	The number of likes a comment has should be updated when a like is added or removed.
Justification	Ensures that the number of likes on a comment is correctly updated whenever a like is added or removed, keeping the like count accurate and in sync with changes to the comment_like table.

SQL Code

CREATE FUNCTION update_comment_likes()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        UPDATE comment
        SET likes = (SELECT count(*) FROM comment_like where comment_id = NEW.comment_id)
        WHERE id = NEW.comment_id;

        RETURN NEW;

    ELSIF TG_OP = 'DELETE' THEN
        UPDATE comment
        SET likes = (SELECT count(*) FROM comment_like where comment_id = OLD.comment_id)
        WHERE id = OLD.comment_id;

        RETURN OLD;
    END IF;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_comment_likes
AFTER INSERT OR DELETE ON comment_like
FOR EACH ROW
EXECUTE FUNCTION update_comment_likes();

---

Trigger	TRIGGER15
Description	The number of followers and following of a user should be updated when there is a new follower or the user starts following another account.
Justification	Ensures that the number of followers and following for each user is accurately updated whenever a user gains a new follower or starts following another user, keeping follow values updated.

SQL Code

CREATE function update_follow_counts()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN    
        UPDATE users
        SET num_followers = (SELECT count(*) FROM follow WHERE followed_id = users.id)
        WHERE users.id = NEW.followed_id;

        UPDATE users
        SET num_following = (SELECT count(*) FROM follow WHERE follower_id = users.id)
        WHERE users.id = NEW.follower_id;

        RETURN NEW;

    ELSIF TG_OP = 'DELETE' THEN
        UPDATE users
        SET num_followers = (SELECT count(*) FROM follow WHERE followed_id = users.id)
        WHERE users.id = OLD.followed_id;

        UPDATE users
        SET num_following = (SELECT count(*) FROM follow WHERE follower_id = users.id)
        WHERE users.id = OLD.follower_id;

        RETURN OLD;
    END IF;    
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_follow_counts
BEFORE INSERT OR DELETE ON follow
FOR EACH ROW
EXECUTE FUNCTION update_follow_counts();

---

Trigger	TRIGGER16
Description	The number of comments of a post should be updated when a comment is added or removed.
Justification	Ensures that the number of comments on a post is accurately updated whenever a comment is added or removed, keeping the comment count up-to-date in the post table.

SQL Code

CREATE FUNCTION update_comment_count()
RETURNS TRIGGER AS $$ 
BEGIN 
    IF TG_OP = 'INSERT' THEN 
        UPDATE post
        SET comments = (SELECT COUNT(*) FROM comment WHERE comment.post_id = post.id)
        WHERE id = NEW.post_id;

        RETURN NEW;

    ELSIF TG_OP = 'DELETE' THEN 
        UPDATE post
        SET comments = (SELECT COUNT(*) FROM comment WHERE comment.post_id = post.id)
        WHERE id = OLD.post_id;

        RETURN OLD;
    END IF;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_comment_count
AFTER INSERT OR DELETE ON comment
FOR EACH ROW
EXECUTE FUNCTION update_comment_count();

---

Trigger	TRIGGER17
Description	The number of members in a group should be updated when a user joins or leaves the group.
Justification	Ensures that the member count of a group is always accurate, by updating the count whenever a user joins or leaves the group. This keeps the groups table in sync with the actual number of members.

SQL Code

CREATE FUNCTION update_member_count()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        UPDATE groups 
        SET member_count = (SELECT COUNT (*) from group_member where group_id = id)
        WHERE id = NEW.group_id;

        RETURN NEW;

    ELSIF TG_OP = 'DELETE' THEN 
        UPDATE groups 
        SET member_count = (SELECT COUNT (*) from group_member where group_id = id)
        WHERE id = OLD.group_id;

        RETURN OLD;
    END IF;
    
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_member_count
AFTER INSERT OR DELETE ON group_member
FOR EACH ROW
EXECUTE FUNCTION update_member_count();

4. Transactions

TSX01	Post Creation
Isolation level	REPEATABLE READ
Justification	This transaction ensures that every post is created as a single unit, with the relevant tags and attachments associated. In case an error happens, the whole operation of inserting a post into the table, associating tags and attachments fails, rolling back to a state where none of those operations where started. Since we use post_id_seq, the operations are vulnerable to non-repeatable reads, hence it is necessary to use the REPEATABLE READ isolation level. Phantom reads shouldn't affect the transaction, as it does not directly perform queries.

SQL Code

BEGIN TRANSACTION;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- Create post
INSERT INTO post (author_id, title, text, is_announcement, is_public)
VALUES ($author_id, $title, $text, $is_announcement, $is_public);

-- Associate tag
INSERT INTO post_tag (post_id, tag_id)
VALUES (currval('post_id_seq'), $tag_id);

-- Add attachment
INSERT INTO post_attachment (post_id, url, type)
VALUES (currval('post_id_seq'), $attachment_url, $attachment_type);

END TRANSACTION;

---

TSX02	User Registration
Isolation level	REPEATABLE READ
Justification	This transaction ensures that every user is created as a single unit, with its user stats associated. In case an error happens, the whole operation of creating the user is reverted. It does not suffer from phantom reads, since there are no queries performed, but the transaction uses users_id_seq, requiring a REPEATABLE READ isolation level to ensure its value does not change during its execution.

SQL Code

BEGIN TRANSACTION;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- Insert user info
INSERT INTO users (name, email, password, handle, is_public, description, profile_picture_url, banner_image_url)
VALUES ($name, $email, $password, $is_public, $description, $profile_picture_url, $banner_image_url);

-- Insert user statistics
INSERT INTO user_stats (user_id, github_url, gitlab_url, linkedin_url)
VALUES (currval('users_id_seq'), $github_url, $gitlab_url, $linkedin_url);

END TRANSACTION;

---

TSX03	User account deletion
Isolation level	SERIALIZABLE
Justification	When a user is deleted, we need to erase their personal data, while deleting associated tuples from other tables, such as follow requests and user statistics, and ensuring data consistency, hence the use of a transaction. It is SERIALIZABLE, since an insertion on any tuple that references the user can fail the operation.

BEGIN TRANSACTION;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- Delete user notifications
DELETE FROM notification WHERE receiver_id = $user_id;

-- Delete all associated follow requests, group join requests and group invitations
DELETE FROM follow_request WHERE follower_id = $user_id OR followed_id = $user_id;
DELETE FROM group_join_request WHERE requester_id = $user_id;
DELETE FROM group_invitation WHERE invitee_id = $user_id;

-- Delete all group memberships
DELETE FROM group_member WHERE user_id = $user_id;

-- Delete user bans
DELETE FROM ban WHERE user_id = $user_id;

-- Delete user token
DELETE FROM token WHERE user_id = $user_id;

-- Delete user stats info
DELETE FROM user_stats_language
WHERE user_stats_id = (SELECT id FROM user_stats WHERE user_id = $user_id);
DELETE FROM user_stats_technology
WHERE user_stats_id = (SELECT id FROM user_stats WHERE user_id = $user_id);
DELETE FROM top_project
WHERE user_stats_id = (SELECT id FROM user_stats WHERE user_id = $user_id);

UPDATE user_stats
SET github_url = NULL, gitlab_url = NULL, linkedin_url = NULL
WHERE user_id = $user_id;

-- Delete user info and set as deleted
UPDATE users
SET name = $user_id, email = $user_id, password = $user_id, handle = $user_id, is_public = FALSE, description = NULL,
    profile_picture_url = NULL, banner_image_url = NULL, is_deleted = TRUE
WHERE id = $user_id;

END TRANSACTION;

---

Annex A. SQL Code

In this section we provide a comprehensive script for database creation (taken from the file create.sql) and another script featuring INSERT statements to populate the database with initial data (from the file populate.sql).

The mentioned files can be checked here: create.sql and populate.sql.

A.1. Database schema

-- * ====================================================
-- * Schema creation and search path setup
-- * ====================================================

DROP SCHEMA IF EXISTS lbaw2411 CASCADE;
CREATE SCHEMA lbaw2411;
SET search_path TO lbaw2411;


-- * ====================================================
-- * Enum and type creation
-- * ====================================================

CREATE TYPE attachment_type AS ENUM (
    'image',
    'video'
);

CREATE TYPE status_values AS ENUM (
    'pending',
    'accepted',
    'rejected'
);

CREATE TYPE notification_type AS ENUM (
    'follow',
    'comment',
    'post_like',
    'comment_like',
    'post_mention',
    'comment_mention'
);


-- * ====================================================
-- * Table creation
-- * ====================================================

-- Plural used because "user" is a reserved keyword in PostgreSQL
CREATE TABLE users (
    id SERIAL,
    name TEXT NOT NULL,
    email TEXT NOT NULL UNIQUE,
    password TEXT NOT NULL,
    register_timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    handle TEXT NOT NULL UNIQUE,
    is_public BOOLEAN NOT NULL,
    is_deleted BOOLEAN NOT NULL DEFAULT FALSE,
    description TEXT,
    profile_picture_url TEXT,
    banner_image_url TEXT,
    num_followers INTEGER NOT NULL DEFAULT 0,
    num_following INTEGER NOT NULL DEFAULT 0,
    remember_token TEXT,
    PRIMARY KEY (id)
);

CREATE TABLE administrator (
    id SERIAL,
    name TEXT NOT NULL,
    email TEXT NOT NULL UNIQUE,
    password TEXT NOT NULL,
    remember_token TEXT,
    register_timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
);

CREATE TABLE user_stats (
    id SERIAL,
    user_id INTEGER UNIQUE,
    github_url TEXT,
    gitlab_url TEXT,
    linkedin_url TEXT,
    PRIMARY KEY (id),
    FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE language (
    id SERIAL,
    name TEXT NOT NULL UNIQUE,
    PRIMARY KEY (id)
);

CREATE TABLE user_stats_language (
    user_stats_id INTEGER, 
    language_id INTEGER,
    PRIMARY KEY (user_stats_id, language_id),
    FOREIGN KEY (user_stats_id) REFERENCES user_stats (id) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (language_id) REFERENCES language (id) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE technology (
    id SERIAL,
    name TEXT NOT NULL UNIQUE,
    PRIMARY KEY (id)
);

CREATE TABLE user_stats_technology (
    user_stats_id INTEGER NOT NULL,
    technology_id INTEGER NOT NULL,
    PRIMARY KEY (user_stats_id,technology_id),
    FOREIGN KEY (user_stats_id) REFERENCES user_stats (id) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (technology_id) REFERENCES technology (id) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE top_project (
    id SERIAL,
    user_stats_id INTEGER NOT NULL,
    name TEXT NOT NULL,
    url TEXT NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (user_stats_id) REFERENCES user_stats (id) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE post (
    id SERIAL,
    author_id INTEGER NOT NULL,
    title TEXT NOT NULL,
    text TEXT,
    creation_timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    is_announcement BOOLEAN NOT NULL DEFAULT FALSE,
    is_public BOOLEAN NOT NULL,
    likes INTEGER NOT NULL DEFAULT 0,
    comments INTEGER NOT NULL DEFAULT 0,
    PRIMARY KEY (id),
    FOREIGN KEY (author_id) REFERENCES users (id) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE post_like (
    id SERIAL,
    liker_id INTEGER NOT NULL,
    post_id INTEGER NOT NULL,
    timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE (liker_id, post_id),
    FOREIGN KEY (liker_id) REFERENCES users (id) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (post_id) REFERENCES post (id) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE post_attachment (
    id SERIAL,
    post_id INTEGER NOT NULL,
    url TEXT NOT NULL,
    type attachment_type NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (post_id) REFERENCES post (id) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE tag (
    id SERIAL,
    name TEXT NOT NULL UNIQUE,
    PRIMARY KEY (id)
);

CREATE TABLE post_tag (
    post_id INTEGER NOT NULL,
    tag_id INTEGER NOT NULL,
    PRIMARY KEY (post_id, tag_id),
    FOREIGN KEY (post_id) REFERENCES post (id) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (tag_id) REFERENCES tag (id) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE comment (
    id SERIAL,
    post_id INTEGER NOT NULL,
    author_id INTEGER NOT NULL,
    content TEXT NOT NULL,
    likes INTEGER NOT NULL DEFAULT 0,
    timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    FOREIGN KEY (post_id) REFERENCES post (id) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (author_id) REFERENCES users (id) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE comment_like (
    id SERIAL,
    liker_id INTEGER NOT NULL,
    comment_id INTEGER NOT NULL,
    timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE (liker_id, comment_id),
    FOREIGN KEY (comment_id) REFERENCES comment (id) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (liker_id) REFERENCES users (id) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE follow (
    id SERIAL,
    follower_id INTEGER NOT NULL,
    followed_id INTEGER NOT NULL,
    timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE (follower_id, followed_id),
    FOREIGN KEY (follower_id) REFERENCES users (id) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (followed_id) REFERENCES users (id) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT not_self_follow CHECK (follower_id <> followed_id)
);

CREATE TABLE follow_request (
    id SERIAL,
    follower_id INTEGER NOT NULL,
    followed_id INTEGER NOT NULL,
    creation_timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    status status_values NOT NULL DEFAULT 'pending',
    PRIMARY KEY (id),
    FOREIGN KEY (follower_id) REFERENCES users (id) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (followed_id) REFERENCES users (id) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT not_self_follow CHECK (follower_id <> followed_id)
);

CREATE TABLE ban (
    id SERIAL,
    user_id INTEGER NOT NULL,
    administrator_id INTEGER NOT NULL,
    start TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    reason TEXT NOT NULL,
    duration INTERVAL NOT NULL,
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    PRIMARY KEY (id),
    FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (administrator_id) REFERENCES administrator (id) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE token (
    id SERIAL,
    value TEXT NOT NULL,
    user_id INTEGER,
    administrator_id INTEGER,
    creation_timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    validity_timestamp TIMESTAMP NOT NULL,
    PRIMARY KEY (id),
    UNIQUE (user_id),
    UNIQUE (administrator_id),
    FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (administrator_id) REFERENCES administrator (id) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT account_fk_not_null CHECK ((user_id IS NULL) <> (administrator_id IS NULL)),
    CONSTRAINT validity_after_creation CHECK (validity_timestamp > creation_timestamp)
);

-- Plural used because "group" is a reserved keyword in PostgreSQL
CREATE TABLE groups (
    id SERIAL,
    owner_id INTEGER NOT NULL,
    name TEXT NOT NULL UNIQUE,
    description TEXT,
    creation_timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    is_public BOOLEAN NOT NULL,
    member_count INTEGER NOT NULL DEFAULT 1,
    PRIMARY KEY (id),
    FOREIGN KEY (owner_id) REFERENCES users (id)
);

CREATE TABLE group_member (
    user_id INTEGER NOT NULL,
    group_id INTEGER NOT NULL,
    join_timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (user_id, group_id),
    FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (group_id) REFERENCES groups (id) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE group_post (
    post_id INTEGER,
    group_id INTEGER NOT NULL,
    PRIMARY KEY (post_id),
    FOREIGN KEY (post_id) REFERENCES post (id) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (group_id) REFERENCES groups (id) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE group_join_request (
    id SERIAL,
    group_id INTEGER NOT NULL,
    requester_id INTEGER NOT NULL,
    creation_timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    status status_values NOT NULL DEFAULT 'pending',
    PRIMARY KEY (id),
    FOREIGN KEY (group_id) REFERENCES groups (id) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (requester_id) REFERENCES users (id) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE group_invitation (
    id SERIAL,
    group_id INTEGER NOT NULL,
    invitee_id INTEGER NOT NULL,
    creation_timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    status status_values NOT NULL DEFAULT 'pending',
    PRIMARY KEY (id),
    FOREIGN KEY (group_id) REFERENCES groups (id) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (invitee_id) REFERENCES users (id) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE notification (
    id SERIAL,
    receiver_id INTEGER NOT NULL,
    timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    is_read BOOLEAN DEFAULT FALSE,
    type notification_type NOT NULL,
    follow_id INTEGER,
    post_id INTEGER,
    comment_id INTEGER,
    post_like_id INTEGER,
    comment_like_id INTEGER,
    PRIMARY KEY (id),
    FOREIGN KEY (receiver_id) REFERENCES users (id) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (follow_id) REFERENCES follow (id) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (post_id) REFERENCES post (id) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (comment_id) REFERENCES comment (id) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (post_like_id) REFERENCES post_like (id) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (comment_like_id) REFERENCES comment_like (id) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT notification_type_fk CHECK (
        ((type = 'follow' OR type = 'post_mention') AND follow_id IS NOT NULL AND post_id IS NULL AND comment_id IS NULL AND post_like_id IS NULL AND comment_like_id IS NULL)
        OR ((type = 'comment' OR type = 'comment_mention') AND follow_id IS NULL AND post_id IS NULL AND comment_id IS NOT NULL AND post_like_id IS NULL AND comment_like_id IS NULL)
        OR (type = 'post_like' AND follow_id IS NULL AND post_id IS NULL AND comment_id IS NULL AND post_like_id IS NOT NULL AND comment_like_id IS NULL)
        OR (type = 'comment_like' AND follow_id IS NULL AND post_id IS NULL AND comment_id IS NULL AND post_like_id IS NULL AND comment_like_id IS NOT NULL)
    )
);


-- * ====================================================
-- * Index Creation
-- * ====================================================

CREATE INDEX notification_receiver_timestamp ON notification USING btree(receiver_id, timestamp DESC);

CREATE INDEX comment_post_likes ON comment USING btree(post_id, likes DESC);

CREATE INDEX post_author_creation ON post USING btree(author_id, creation_timestamp DESC);


-- * ====================================================
-- * Full Text Search Index Creation
-- * ====================================================

-- IDX11

ALTER TABLE post
ADD COLUMN tsvectors TSVECTOR;

CREATE FUNCTION calculate_post_tsvectors(post_id INTEGER)
RETURNS VOID AS $$
BEGIN
    UPDATE post
    SET tsvectors = (
        setweight(to_tsvector('english', post.title), 'A') ||
        setweight(to_tsvector('english', (
            SELECT users.name
            FROM users
            WHERE users.id = post.author_id
        )), 'A') ||
        setweight(to_tsvector('english', (
            SELECT users.handle
            FROM users
            WHERE users.id = post.author_id
        )), 'A') ||
        setweight(to_tsvector('english', coalesce(post.text, '')), 'B') ||
        setweight(to_tsvector('english', (
            SELECT coalesce(string_agg(comment.content, ' '), '')
            FROM comment
            WHERE comment.post_id = post.id
        )), 'C'))
    WHERE post.id = post_id;
END;
$$ LANGUAGE plpgsql;

CREATE FUNCTION post_search_update()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT'
    OR (TG_OP = 'UPDATE' AND (NEW.title <> OLD.title OR NEW.text <> OLD.text))
    THEN
        PERFORM calculate_post_tsvectors(NEW.id);
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER post_search_update
AFTER INSERT OR UPDATE ON post
FOR EACH ROW
EXECUTE PROCEDURE post_search_update();

CREATE FUNCTION post_comment_search_update()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
        IF TG_OP = 'INSERT' OR NEW.content <> OLD.content THEN
            PERFORM calculate_post_tsvectors(NEW.post_id);
        END IF;

        RETURN NEW;

    ELSIF TG_OP = 'DELETE' THEN
        PERFORM calculate_post_tsvectors(OLD.post_id);

        RETURN OLD;
    END IF;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER post_comment_search_update
AFTER INSERT OR UPDATE OR DELETE ON comment
FOR EACH ROW
EXECUTE PROCEDURE post_comment_search_update();

CREATE FUNCTION post_author_search_update()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'UPDATE' AND (OLD.name <> NEW.name OR OLD.handle <> NEW.handle)
    THEN 
        PERFORM calculate_post_tsvectors(NEW.id);
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER post_author_search_update
AFTER UPDATE ON users
FOR EACH ROW
EXECUTE PROCEDURE post_author_search_update();

CREATE INDEX post_search_idx ON post USING GiST (tsvectors);


-- IDX12

ALTER TABLE users
ADD COLUMN tsvectors TSVECTOR;

CREATE FUNCTION user_search_update()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT'
    OR (TG_OP = 'UPDATE' AND (NEW.name <> OLD.name OR NEW.handle <> OLD.handle OR NEW.description <> OLD.description))
    THEN
        NEW.tsvectors = (
            setweight(to_tsvector('english', NEW.name), 'A') ||
            setweight(to_tsvector('english', NEW.handle), 'A') ||
            setweight(to_tsvector('english', coalesce(NEW.description, '')), 'B')
        );
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER user_search_update
BEFORE INSERT OR UPDATE ON users 
FOR EACH ROW
EXECUTE PROCEDURE user_search_update();

CREATE INDEX user_search_idx ON users USING GiST (tsvectors);


-- IDX13

ALTER TABLE groups
ADD COLUMN tsvectors TSVECTOR;

CREATE FUNCTION group_search_update()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT'
    OR (TG_OP = 'UPDATE' AND (NEW.name <> OLD.name OR NEW.description <> OLD.description))
    THEN
        NEW.tsvectors = (
            setweight(to_tsvector('english', NEW.name), 'A') ||
            setweight(to_tsvector('english', coalesce(NEW.description, '')), 'B')            
        );
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER group_search_update
BEFORE INSERT OR UPDATE ON groups
FOR EACH ROW
EXECUTE PROCEDURE group_search_update();

CREATE INDEX group_search_idx ON groups USING GIN (tsvectors);


-- * ====================================================
-- * Trigger Creation
-- * ====================================================

-- * ====================================================
-- *     Trigger Creation: Notifications
-- * ====================================================

-- TRIGGER01 
CREATE FUNCTION notify_user_on_comment() 
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO notification (receiver_id, timestamp, is_read, type, comment_id) 
    VALUES (NEW.author_id, CURRENT_TIMESTAMP, FALSE, 'comment', NEW.id);

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER notify_user_on_comment
AFTER INSERT ON comment
FOR EACH ROW
EXECUTE FUNCTION notify_user_on_comment();

-- TRIGGER02
CREATE FUNCTION notify_user_on_post_like()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO notification (receiver_id, timestamp, is_read, type, post_like_id) 
    VALUES ((SELECT author_id FROM post WHERE id = NEW.post_id), CURRENT_TIMESTAMP, FALSE, 'post_like', NEW.id);

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER notify_user_on_post_like
AFTER INSERT ON post_like
FOR EACH ROW
EXECUTE FUNCTION notify_user_on_post_like();

-- TRIGGER03  
CREATE FUNCTION notify_user_on_comment_like()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO notification (receiver_id, timestamp, is_read, type, comment_like_id) 
    VALUES ((SELECT author_id FROM comment WHERE id = NEW.comment_id), CURRENT_TIMESTAMP, FALSE, 'comment_like', NEW.id);

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER notify_user_on_comment_like
AFTER INSERT ON comment_like
FOR EACH ROW
EXECUTE FUNCTION notify_user_on_comment_like();

-- TRIGGER04 
CREATE FUNCTION notify_user_on_follow()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO notification (receiver_id, timestamp, is_read, type, follow_id) 
    VALUES (NEW.followed_id, CURRENT_TIMESTAMP, FALSE, 'follow', NEW.id);

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER notify_user_on_follow
AFTER INSERT ON follow
FOR EACH ROW
EXECUTE FUNCTION notify_user_on_follow();


-- * ====================================================
-- *     Trigger Creation: Group Owner
-- * ====================================================

-- TRIGGER05
CREATE FUNCTION set_group_owner_as_member()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO group_member (user_id, group_id) 
    VALUES (NEW.owner_id, NEW.id);

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER set_group_owner_as_member
AFTER INSERT ON groups
FOR EACH ROW
EXECUTE FUNCTION set_group_owner_as_member();

-- * ====================================================
-- *     Trigger Creation: Requests
-- * ====================================================

-- TRIGGER06
CREATE FUNCTION handle_group_invitation_acceptance()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.status = 'accepted' AND OLD.status <> 'accepted' THEN
        INSERT INTO group_member (user_id, group_id, join_timestamp) 
        VALUES (NEW.user_id, NEW.group_id, CURRENT_TIMESTAMP);
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER handle_group_invitation_acceptance
AFTER UPDATE ON group_invitation
FOR EACH ROW
EXECUTE FUNCTION handle_group_invitation_acceptance();

-- TRIGGER07
CREATE FUNCTION handle_group_join_request_acceptance()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.status = 'accepted' AND OLD.status <> 'accepted' THEN
        INSERT INTO group_member (user_id, group_id, join_timestamp) 
        VALUES (NEW.requester_id, NEW.group_id, CURRENT_TIMESTAMP);
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER handle_group_join_request_acceptance
AFTER UPDATE ON group_join_request
FOR EACH ROW
EXECUTE FUNCTION handle_group_join_request_acceptance();

-- TRIGGER08
CREATE FUNCTION handle_follow_request_acceptance()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.status = 'accepted' AND OLD.status <> 'accepted' THEN
        INSERT INTO follow (follower_id, followed_id, timestamp) 
        VALUES (NEW.follower_id, NEW.followed_id, CURRENT_TIMESTAMP);
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER handle_follow_request_acceptance
AFTER UPDATE ON follow_request
FOR EACH ROW
EXECUTE FUNCTION handle_follow_request_acceptance();


-- * ====================================================
-- *     Trigger creation: Enforcements
-- * ====================================================

-- TRIGGER09 
CREATE FUNCTION enforce_different_post_liker()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.liker_id = (SELECT author_id FROM post WHERE id = NEW.post_id) THEN
        RAISE EXCEPTION 'A user cannot like their own post';
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER enforce_different_post_liker
BEFORE INSERT ON post_like
FOR EACH ROW
EXECUTE FUNCTION enforce_different_post_liker();

-- TRIGGER010
CREATE FUNCTION enforce_different_comment_liker()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.liker_id = (SELECT author_id FROM comment WHERE id = NEW.comment_id) THEN
        RAISE EXCEPTION 'A user cannot like their own comment';
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER enforce_different_comment_liker
BEFORE INSERT ON comment_like
FOR EACH ROW
EXECUTE FUNCTION enforce_different_comment_liker();

-- TRIGGER011  
CREATE FUNCTION enforce_group_post_author_is_member()
RETURNS TRIGGER AS $$
BEGIN
    IF (SELECT author_id FROM post WHERE id = NEW.post_id) NOT IN (SELECT user_id FROM group_member WHERE group_id = NEW.group_id) THEN
        RAISE EXCEPTION 'Post author must be a member of the group';
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER enforce_group_post_author_is_member
BEFORE INSERT OR UPDATE ON group_post
FOR EACH ROW
EXECUTE FUNCTION enforce_group_post_author_is_member();

-- TRIGGER012
CREATE FUNCTION enforce_max_top_projects()
RETURNS TRIGGER AS $$
BEGIN
    IF (SELECT COUNT(*) FROM top_project WHERE user_stats_id = NEW.user_stats_id) >= 10 THEN
        RAISE EXCEPTION 'User cannot have more than 10 top projects';
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER enforce_max_top_projects
BEFORE INSERT ON top_project
FOR EACH ROW
EXECUTE FUNCTION enforce_max_top_projects();


-- * ====================================================
-- *     Trigger creation: Derived Attributes
-- * ====================================================

-- TRIGGER013
CREATE FUNCTION update_post_likes()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        UPDATE post
        SET likes = (SELECT count(*) FROM post_like WHERE post_id = NEW.post_id)
        WHERE id = NEW.post_id;

        RETURN NEW;

    ELSIF TG_OP = 'DELETE' THEN
        UPDATE post
        SET likes = (SELECT count(*) FROM post_like WHERE post_id = OLD.post_id)
        WHERE id = OLD.post_id;

        RETURN OLD;
    END IF;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_post_likes
AFTER INSERT OR DELETE ON post_like
FOR EACH ROW
EXECUTE FUNCTION update_post_likes();

-- TRIGGER014
CREATE FUNCTION update_comment_likes()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        UPDATE comment
        SET likes = (SELECT count(*) FROM comment_like where comment_id = NEW.comment_id)
        WHERE id = NEW.comment_id;

        RETURN NEW;

    ELSIF TG_OP = 'DELETE' THEN
        UPDATE comment
        SET likes = (SELECT count(*) FROM comment_like where comment_id = OLD.comment_id)
        WHERE id = OLD.comment_id;

        RETURN OLD;
    END IF;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_comment_likes
AFTER INSERT OR DELETE ON comment_like
FOR EACH ROW
EXECUTE FUNCTION update_comment_likes();

-- TRIGGER015
CREATE function update_follow_counts()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN    
        UPDATE users
        SET num_followers = (SELECT count(*) FROM follow WHERE followed_id = users.id)
        WHERE users.id = NEW.followed_id;

        UPDATE users
        SET num_following = (SELECT count(*) FROM follow WHERE follower_id = users.id)
        WHERE users.id = NEW.follower_id;

        RETURN NEW;

    ELSIF TG_OP = 'DELETE' THEN
        UPDATE users
        SET num_followers = (SELECT count(*) FROM follow WHERE followed_id = users.id)
        WHERE users.id = OLD.followed_id;

        UPDATE users
        SET num_following = (SELECT count(*) FROM follow WHERE follower_id = users.id)
        WHERE users.id = OLD.follower_id;

        RETURN OLD;
    END IF;    
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_follow_counts
BEFORE INSERT OR DELETE ON follow
FOR EACH ROW
EXECUTE FUNCTION update_follow_counts();

-- TRIGGER016
CREATE FUNCTION update_comment_count()
RETURNS TRIGGER AS $$ 
BEGIN 
    IF TG_OP = 'INSERT' THEN 
        UPDATE post
        SET comments = (SELECT COUNT(*) FROM comment WHERE comment.post_id = post.id)
        WHERE id = NEW.post_id;

        RETURN NEW;

    ELSIF TG_OP = 'DELETE' THEN 
        UPDATE post
        SET comments = (SELECT COUNT(*) FROM comment WHERE comment.post_id = post.id)
        WHERE id = OLD.post_id;

        RETURN OLD;
    END IF;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_comment_count
AFTER INSERT OR DELETE ON comment
FOR EACH ROW
EXECUTE FUNCTION update_comment_count();

-- TRIGGER017
CREATE FUNCTION update_member_count()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        UPDATE groups 
        SET member_count = (SELECT COUNT (*) from group_member where group_id = id)
        WHERE id = NEW.group_id;

        RETURN NEW;

    ELSIF TG_OP = 'DELETE' THEN 
        UPDATE groups 
        SET member_count = (SELECT COUNT (*) from group_member where group_id = id)
        WHERE id = OLD.group_id;

        RETURN OLD;
    END IF;
    
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_member_count
AFTER INSERT OR DELETE ON group_member
FOR EACH ROW
EXECUTE FUNCTION update_member_count();

A.2. Database population

SET search_path TO lbaw2411;

INSERT INTO users (name, email, password, register_timestamp, handle, is_public, description, profile_picture_url, banner_image_url) VALUES
    ('Cindy Burton', 'cindyburton@gmail.com', '$2y$10$RYObU9iEY6BU4HPBMesgDOcA/ZkkK4lPyi1mT8BLYF9tvsNSPuyg6', '2024-02-06 22:45:13', 'cindy_burton193', TRUE, 'Python enthusiast and data scientist. Enjoys working with AI/ML algorithms, Kaggle competitions, and visualizations.', '/images/profile-pictures/user-2.jpg', '/images/banner-images/user-2.jpg'),
    ('Joshua Johnson', 'joshua514@outlook.com', '$2y$10$RYObU9iEY6BU4HPBMesgDOcA/ZkkK4lPyi1mT8BLYF9tvsNSPuyg6', '2024-06-20 06:32:36', 'joshua_johnson514', TRUE, 'Cloud architect with AWS and GCP experience. Always looking for new automation challenges in DevOps.', '/images/profile-pictures/user-3.jpg', '/images/banner-images/user-3.jpg'),
    ('Joshua Davies', 'jasonsmith@yahoo.com', '$2y$10$RYObU9iEY6BU4HPBMesgDOcA/ZkkK4lPyi1mT8BLYF9tvsNSPuyg6', '2024-07-18 03:03:24', 'joshua_davies298', FALSE, 'Mobile app developer specializing in iOS. Swift and Objective-C are my playground!', '/images/profile-pictures/4f11df12-2931-45a5-a41e-24670f84a7d9.jpg', '/images/banner-images/26f272b7-7507-46d7-8840-1a2e0bc37202.jpg'),
    ('Connie Terry', 'lewisalexis@gmail.com', '$2y$10$RYObU9iEY6BU4HPBMesgDOcA/ZkkK4lPyi1mT8BLYF9tvsNSPuyg6', '2024-10-23 14:44:19', 'connie_terry331', FALSE, 'JavaScript junkie and Node.js expert. Backend dev with a knack for REST APIs and microservices.', '/images/profile-pictures/e2920b2c-4227-4283-8f39-6913115f9bff.jpg', '/images/banner-images/c1261bc0-ba69-4426-a5ba-e6508a1308a5.jpg'),
    ('Joshua May', 'johnsonjeffrey@gmail.com', '$2y$10$RYObU9iEY6BU4HPBMesgDOcA/ZkkK4lPyi1mT8BLYF9tvsNSPuyg6', '2024-10-05 05:10:33', 'joshua_may728', FALSE, 'Frontend developer who loves React and Next.js. Big fan of beautiful, user-friendly interfaces.', '/images/profile-pictures/f44c2ab3-87e9-4c42-9dd6-1a382ddc3c19.jpg', '/images/banner-images/dc23d8ca-c511-4066-ac1a-6a3b85c1930d.jpg'),
    ('Paul Thompson', 'cody25@outlook.com', '$2y$10$RYObU9iEY6BU4HPBMesgDOcA/ZkkK4lPyi1mT8BLYF9tvsNSPuyg6', '2024-01-26 01:19:06', 'paul_thompson351', FALSE, 'Embedded systems engineer who codes for hardware. Specializes in C and ARM Cortex MCUs.', '/images/profile-pictures/6b368019-a89f-4c46-8a5c-35ab9b62db6b.jpg', '/images/banner-images/ba391605-4405-44da-a224-d2b52e71a28a.jpg'),
    ('Peter Campbell', 'michael24@yahoo.com', '$2y$10$RYObU9iEY6BU4HPBMesgDOcA/ZkkK4lPyi1mT8BLYF9tvsNSPuyg6', '2024-05-05 03:08:39', 'peter_campbell477', FALSE, 'Rust advocate and systems programmer. Obsessed with performance and memory safety.', '/images/profile-pictures/b455b5f7-2999-4089-82d7-d1a456df8d63.jpg', '/images/banner-images/ca668f71-cb0d-45ba-81a5-817f437d060b.jpg'),
-- Full file can be found on the repository

---

Usage of Generative AI Tools

In this delivery, we have used both GhatGPT and Github Copilot to make most of the text present in the database population data more natural, by rewriting the tuples in the file.

---

Revision history

Changes made to the first submission:

1. Updated SQL file links based on the Laravel Template (26/11/2024)
2. Update database schema code, database population code and INDEX11 code. (26/11/2024)
3. Update user deletion transaction TSX05 (to replace non-nullable attributes with the user id on deletion). (26/11/2024)
4. Update conceptual model (more specifically the "User" class and the association arrow heads). (26/11/2024)
5. Update search indexes to not perform unnecessary null checks (04/12/2024).
6. Add "ON DELETE" on foreign keys (04/12/2024).
7. Remove transactions TSX03 (Comment Deletion) and TSX04 (Post Deletion), and change transaction TSX05 code to TSX03 (05/12/2024).
8. Fix user search index bug introduced in .5 (06/12/2024).
9. Change attribute joined_at to join_timestamp in triggers TRIGGER06 and TRIGGER07 (08/12/2024).

---

GROUP2411, October 28, 2024

• José Carlos Malheiro de Sousa, up202208817@up.pt (Editor)
• Bruno Ricardo Soares Pereira de Sousa Oliveira, up202208700@up.pt
• Henrique Sardo Fernandes, up202204988@up.pt
• Rodrigo Albergaria Coelho e Silva, up202205188@up.pt