31 files changed, 139 insertions, 57 deletions

diff --git a/db/rest/util/_api_get_user_id.sql b/db/rest/util/_api_get_user_id.sql
deleted file mode 100644
index 23eb160..0000000
--- a/db/rest/util/_api_get_user_id.sql
+++ /dev/null
@@ -1,11 +0,0 @@
-CREATE FUNCTION _api.get_user_id()
-RETURNS INTEGER
-LANGUAGE plpgsql VOLATILE
-AS $BODY$
-BEGIN
-	RETURN CURRENT_SETTING(
-		'request.jwt.claims',
-		TRUE
-	)::JSON->>'user_id';
-END
-$BODY$;
diff --git a/db/ext.sql b/src/db/ext.sql
index 2f3376f..2f3376f 100644
--- a/db/ext.sql
+++ b/src/db/ext.sql
diff --git a/db/migrations/0000.sql b/src/db/migrations/0000.sql
index f3577d4..b60c55b 100644
--- a/db/migrations/0000.sql
+++ b/src/db/migrations/0000.sql
@@ -50,11 +50,12 @@ CREATE TABLE admin.user (
 	middle_name		TEXT DEFAULT ''::text NOT NULL,
 	email			TEXT DEFAULT ''::text NOT NULL,
 	gender			TEXT DEFAULT ''::text NOT NULL,
-	join_date		TIMESTAMP WITH TIME ZONE DEFAULT clock_timestamp() NOT NULL,
 	birth_date		TIMESTAMP WITH TIME ZONE NOT NULL,
-	profile_avatar	BYTEA,
-	profile_banner	BYTEA,
-	profile_bio		TEXT DEFAULT ''::text NOT NULL
+	profile_bio		TEXT DEFAULT ''::text NOT NULL,
+	created			TIMESTAMP WITH TIME ZONE DEFAULT clock_timestamp() NOT NULL,
+	modified		TIMESTAMP WITH TIME ZONE DEFAULT clock_timestamp() NOT NULL,
+	seen			TIMESTAMP WITH TIME ZONE DEFAULT clock_timestamp() NOT NULL,
+	deleted			BOOLEAN DEFAULT FALSE NOT NULL
 );
 
 ALTER TABLE admin.user OWNER TO xssbook;
@@ -75,10 +76,12 @@ CREATE SEQUENCE IF NOT EXISTS sys.post_id_seq
 ALTER TABLE sys.post_id_seq OWNER TO xssbook;
 
 CREATE TABLE admin.post (
-	id		INTEGER DEFAULT nextval('sys.post_id_seq'::regclass) NOT NULL,
-	user_id	INTEGER NOT NULL,
-	content	TEXT DEFAULT ''::text NOT NULL,
-	date	TIMESTAMP WITH TIME ZONE DEFAULT clock_timestamp() NOT NULL
+	id			INTEGER DEFAULT nextval('sys.post_id_seq'::regclass) NOT NULL,
+	user_id		INTEGER NOT NULL,
+	content		TEXT DEFAULT ''::text NOT NULL,
+	created		TIMESTAMP WITH TIME ZONE DEFAULT clock_timestamp() NOT NULL,
+	modified	TIMESTAMP WITH TIME ZONE DEFAULT clock_timestamp() NOT NULL,
+	deleted		BOOLEAN DEFAULT FALSE NOT NULL
 );
 
 ALTER TABLE admin.post OWNER TO xssbook;
@@ -97,11 +100,13 @@ CREATE SEQUENCE IF NOT EXISTS sys.comment_id_seq
 	CACHE 1;
 
 CREATE TABLE admin.comment (
-	id		INTEGER DEFAULT nextval('sys.comment_id_seq'::regclass) NOT NULL,
-	user_id	INTEGER NOT NULL,
-	post_id INTEGER NOT NULL,
-	content TEXT DEFAULT ''::text NOT NULL,
-	date	TIMESTAMP WITH TIME ZONE DEFAULT clock_timestamp() NOT NULL
+	id			INTEGER DEFAULT nextval('sys.comment_id_seq'::regclass) NOT NULL,
+	user_id		INTEGER NOT NULL,
+	post_id		INTEGER NOT NULL,
+	content		TEXT DEFAULT ''::text NOT NULL,
+	created		TIMESTAMP WITH TIME ZONE DEFAULT clock_timestamp() NOT NULL,
+	modified	TIMESTAMP WITH TIME ZONE DEFAULT clock_timestamp() NOT NULL,
+	deleted		BOOLEAN DEFAULT FALSE NOT NULL
 );
 
 ALTER TABLE admin.comment OWNER TO xssbook;
@@ -115,16 +120,29 @@ ALTER TABLE ONLY admin.comment
 ALTER TABLE ONLY admin.comment
 	ADD CONSTRAINT comment_post_id_fkey FOREIGN KEY (post_id) REFERENCES admin.post (id) ON DELETE CASCADE;
 
+CREATE SEQUENCE IF NOT EXISTS sys.like_id_seq
+	START WITH 1
+	INCREMENT BY 1
+	NO MINVALUE
+	NO MAXVALUE
+	CACHE 1;
+
 CREATE TABLE admin.like (
+	id			INTEGER DEFAULT nextval('sys.like_id_seq'::regclass) NOT NULL,
 	user_id		INTEGER NOT NULL,
 	post_id		INTEGER,
 	comment_id  INTEGER,
-	date		TIMESTAMP WITH TIME ZONE DEFAULT clock_timestamp() NOT NULL
+	value		BOOLEAN NOT NULL DEFAULT TRUE,
+	created		TIMESTAMP WITH TIME ZONE DEFAULT clock_timestamp() NOT NULL,
+	modified	TIMESTAMP WITH TIME ZONE DEFAULT clock_timestamp() NOT NULL
 );
 
 ALTER TABLE admin.like OWNER TO xssbook;
 
 ALTER TABLE ONLY admin.like
+	ADD CONSTRAINT like_pkey PRIMARY KEY (id);
+
+ALTER TABLE ONLY admin.like
 	ADD CONSTRAINT like_user_id_fkey FOREIGN KEY (user_id) REFERENCES admin.user (id) ON DELETE CASCADE;
 
 ALTER TABLE ONLY admin.like
@@ -133,16 +151,32 @@ ALTER TABLE ONLY admin.like
 ALTER TABLE ONLY admin.like
 	ADD CONSTRAINT like_comment_id_fkey FOREIGN KEY (comment_id) REFERENCES admin.comment (id) ON DELETE CASCADE;
 
+ALTER TABLE ONLY admin.like
+	ADD CONSTRAINT like_post_id_unique UNIQUE (user_id, post_id);
+
+ALTER TABLE ONLY admin.like
+	ADD CONSTRAINT like_comment_id_unique UNIQUE (user_id, comment_id);
+
+CREATE SEQUENCE IF NOT EXISTS sys.follow_id_seq
+	START WITH 1
+	INCREMENT BY 1
+	NO MINVALUE
+	NO MAXVALUE
+	CACHE 1;
+
 CREATE TABLE admin.follow (
+	id			INTEGER DEFAULT nextval('sys.follow_id_seq'::regclass) NOT NULL,
 	follower_id	INTEGER NOT NULL,
 	followee_id	INTEGER NOT NULL,
-	date		TIMESTAMP WITH TIME ZONE DEFAULT clock_timestamp() NOT NULL
+	value		BOOLEAN NOT NULL DEFAULT TRUE,
+	created		TIMESTAMP WITH TIME ZONE DEFAULT clock_timestamp() NOT NULL,
+	modified	TIMESTAMP WITH TIME ZONE DEFAULT clock_timestamp() NOT NULL
 );
 
 ALTER TABLE admin.follow OWNER TO xssbook;
 
 ALTER TABLE ONLY admin.follow
-	ADD CONSTRAINT follow_pkey PRIMARY KEY (follower_id, followee_id);
+	ADD CONSTRAINT follow_pkey PRIMARY KEY (id);
 
 ALTER TABLE ONLY admin.follow
 	ADD CONSTRAINT follow_follower_id FOREIGN KEY (follower_id) REFERENCES admin.user (id) ON DELETE CASCADE;
@@ -150,6 +184,9 @@ ALTER TABLE ONLY admin.follow
 ALTER TABLE ONLY admin.follow
 	ADD CONSTRAINT follow_followee_id FOREIGN KEY (followee_id) REFERENCES admin.user (id) ON DELETE CASCADE;
 
+ALTER TABLE ONLY admin.follow
+	ADD CONSTRAINT follow_follower_unique UNIQUE (follower_id, followee_id);
+
 CREATE TABLE admin.media (
 	name			TEXT NOT NULL,
 	content			BYTEA NOT NULL,
diff --git a/db/migrations/0001.sql b/src/db/migrations/0001.sql
index d20d895..d20d895 100644
--- a/db/migrations/0001.sql
+++ b/src/db/migrations/0001.sql
diff --git a/db/migrations/0002.sql b/src/db/migrations/0002.sql
index 47c2e24..47c2e24 100644
--- a/db/migrations/0002.sql
+++ b/src/db/migrations/0002.sql
diff --git a/db/rest/comment/api_comment.sql b/src/db/rest/comment/api_comment.sql
index e50ca2f..c8a0e19 100644
--- a/db/rest/comment/api_comment.sql
+++ b/src/db/rest/comment/api_comment.sql
@@ -4,10 +4,24 @@ CREATE VIEW api.comment AS
 		c.user_id,
 		c.post_id,
 		c.content,
-		c.date
+		c.created,
+		c.modified
 	FROM
 		admin.comment c
-	ORDER BY id ASC;
+	LEFT JOIN
+		admin.post p
+	ON
+		p.id = c.post_id
+	LEFT JOIN
+		admin.user u
+	ON
+		u.id = c.user_id
+	WHERE
+		c.deleted <> TRUE AND
+		p.deleted <> TRUE AND
+		u.deleted <> TRUE
+	ORDER BY
+		id ASC;
 
 GRANT SELECT ON TABLE api.comment
 	TO rest_anon, rest_user;
diff --git a/db/rest/comment/api_comment_delete.sql b/src/db/rest/comment/api_comment_delete.sql
index d7db8a4..262b2ed 100644
--- a/db/rest/comment/api_comment_delete.sql
+++ b/src/db/rest/comment/api_comment_delete.sql
@@ -11,9 +11,10 @@ BEGIN
 		PERFORM _api.raise_deny();
 	END IF;
 
-	DELETE FROM admin.comment
-	WHERE user_id = _user_id
-	AND id = OLD.id;
+	UPDATE admin.comment SET
+		deleted = TRUE,
+		modified = clock_timestamp()
+		WHERE id = OLD.id;
 END
 $BODY$;
 
@@ -21,7 +22,7 @@ GRANT EXECUTE ON FUNCTION _api.comment_delete()
 	TO rest_user;
 GRANT DELETE ON TABLE api.comment
 	TO rest_user;
-GRANT DELETE ON TABLE admin.comment
+GRANT UPDATE ON TABLE admin.comment
 	TO rest_user;
 
 CREATE TRIGGER api_comment_delete_trgr
diff --git a/db/rest/comment/api_comment_insert.sql b/src/db/rest/comment/api_comment_insert.sql
index 878e194..990beef 100644
--- a/db/rest/comment/api_comment_insert.sql
+++ b/src/db/rest/comment/api_comment_insert.sql
@@ -34,7 +34,9 @@ BEGIN
 		_user_id,
 		NEW.post_id,
 		NEW.content
-	);
+	)
+	RETURNING id
+	INTO NEW.id;
 
 	RETURN NEW;
 END
diff --git a/db/rest/comment/api_comment_update.sql b/src/db/rest/comment/api_comment_update.sql
index d6b4aca..b8fc16d 100644
--- a/db/rest/comment/api_comment_update.sql
+++ b/src/db/rest/comment/api_comment_update.sql
@@ -27,8 +27,9 @@ BEGIN
 	END IF;
 
 	IF _changed THEN
-		UPDATE admin.comment
-			SET content = NEW.content
+		UPDATE admin.comment SET
+			content = NEW.content,
+			modified = clock_timestamp()
 			WHERE id = OLD.id;
 	END IF;
 
diff --git a/db/rest/login/_api_sign_jwt.sql b/src/db/rest/login/_api_sign_jwt.sql
index dc8e920..dc8e920 100644
--- a/db/rest/login/_api_sign_jwt.sql
+++ b/src/db/rest/login/_api_sign_jwt.sql
diff --git a/db/rest/login/_api_validate_role.sql b/src/db/rest/login/_api_validate_role.sql
index 9f1e54f..9f1e54f 100644
--- a/db/rest/login/_api_validate_role.sql
+++ b/src/db/rest/login/_api_validate_role.sql
diff --git a/db/rest/login/_api_verify_jwt.sql b/src/db/rest/login/_api_verify_jwt.sql
index f5a6daf..9e63cc9 100644
--- a/db/rest/login/_api_verify_jwt.sql
+++ b/src/db/rest/login/_api_verify_jwt.sql
@@ -8,6 +8,7 @@ DECLARE
 	_payload JSON;
 	_valid BOOLEAN;
 	_jwt_secret TEXT;
+	_user_id INTEGER;
 BEGIN
 	SELECT jwt_secret INTO _jwt_secret
 	FROM sys.database_info
@@ -28,7 +29,13 @@ BEGIN
 		RETURN NULL;
 	END IF;
 
-	RETURN _payload->>'user_id';
+	_user_id = _payload->>'user_id';
+
+	UPDATE admin.user
+	SET seen = clock_timestamp()
+	WHERE id = _user_id;
+
+	RETURN _user_id;
 END
 $BODY$;
 
@@ -36,3 +43,5 @@ GRANT EXECUTE ON FUNCTION _api.verify_jwt(TEXT)
 	TO rest_anon, rest_user;
 GRANT SELECT ON TABLE sys.database_info
 	TO rest_anon, rest_user;
+GRANT UPDATE ON TABLE admin.user
+	TO rest_anon, rest_user;
diff --git a/db/rest/login/api_login.sql b/src/db/rest/login/api_login.sql
index 0cf0535..0cf0535 100644
--- a/db/rest/login/api_login.sql
+++ b/src/db/rest/login/api_login.sql
diff --git a/db/rest/post/api_post.sql b/src/db/rest/post/api_post.sql
index 375f292..0d60473 100644
--- a/db/rest/post/api_post.sql
+++ b/src/db/rest/post/api_post.sql
@@ -3,7 +3,8 @@ CREATE VIEW api.post AS
 		p.id,
 		p.user_id,
 		p.content,
-		p.date,
+		p.created,
+		p.modified,
 		COALESCE(c.cc, 0)
 			AS comment_count
 	FROM
@@ -16,8 +17,19 @@ CREATE VIEW api.post AS
 			admin.comment c
 		GROUP BY
 			c.post_id
-	) c ON p.id = c.post_id
-	ORDER BY p.id DESC;
+	) c
+	ON
+		p.id = c.post_id
+	LEFT JOIN
+		admin.user u
+	ON
+		u.id = p.user_id
+	WHERE
+		p.deleted <> TRUE
+	AND
+		u.deleted <> TRUE
+	ORDER BY
+		p.id DESC;
 
 GRANT SELECT ON TABLE api.post
 	TO rest_anon, rest_user;
diff --git a/db/rest/post/api_post_delete.sql b/src/db/rest/post/api_post_delete.sql
index e3dec55..8f26b40 100644
--- a/db/rest/post/api_post_delete.sql
+++ b/src/db/rest/post/api_post_delete.sql
@@ -11,9 +11,10 @@ BEGIN
 		PERFORM _api.raise_deny();
 	END IF;
 
-	DELETE FROM admin.post
-	WHERE user_id = _user_id
-	AND id = OLD.id;
+	UPDATE admin.post SET
+		deleted = TRUE,
+		modified = clock_timestamp()
+		WHERE id = OLD.id;
 END
 $BODY$;
 
@@ -21,7 +22,7 @@ GRANT EXECUTE ON FUNCTION _api.post_delete()
 	TO rest_user;
 GRANT DELETE ON TABLE api.post
 	TO rest_user;
-GRANT DELETE ON TABLE admin.post
+GRANT UPDATE ON TABLE admin.post
 	TO rest_user;
 
 CREATE TRIGGER api_post_delete_trgr
diff --git a/db/rest/post/api_post_insert.sql b/src/db/rest/post/api_post_insert.sql
index 8b2eb48..e0594dc 100644
--- a/db/rest/post/api_post_insert.sql
+++ b/src/db/rest/post/api_post_insert.sql
@@ -22,7 +22,9 @@ BEGIN
 	) VALUES (
 		_user_id,
 		NEW.content
-	);
+	)
+	RETURNING id
+	INTO NEW.id;
 
 	RETURN NEW;
 END
diff --git a/db/rest/post/api_post_update.sql b/src/db/rest/post/api_post_update.sql
index 70230d0..7b4360d 100644
--- a/db/rest/post/api_post_update.sql
+++ b/src/db/rest/post/api_post_update.sql
@@ -27,8 +27,9 @@ BEGIN
 	END IF;
 
 	IF _changed THEN
-		UPDATE admin.post
-			SET content = NEW.content
+		UPDATE admin.post SET
+			content = NEW.content,
+			modified = clock_timestamp()
 			WHERE id = OLD.id;
 	END IF;
 
diff --git a/db/rest/rest.sql b/src/db/rest/rest.sql
index 54f5118..3e6737c 100644
--- a/db/rest/rest.sql
+++ b/src/db/rest/rest.sql
@@ -41,6 +41,12 @@ GRANT USAGE ON SCHEMA _api TO rest_anon, rest_user;
 \i /db/rest/comment/api_comment_update.sql;
 \i /db/rest/comment/api_comment_delete.sql;
 
+-- like
+\i /db/rest/like/api_like.sql;
+\i /db/rest/like/api_like_insert.sql;
+\i /db/rest/like/api_like_update.sql;
+\i /db/rest/like/api_like_delete.sql;
+
 -- login
 \i /db/rest/login/_api_sign_jwt.sql;
 \i /db/rest/login/_api_verify_jwt.sql;
diff --git a/db/rest/user/api_avatar.sql b/src/db/rest/user/api_avatar.sql
index 981409f..981409f 100644
--- a/db/rest/user/api_avatar.sql
+++ b/src/db/rest/user/api_avatar.sql
diff --git a/db/rest/user/api_user.sql b/src/db/rest/user/api_user.sql
index e45768a..6735775 100644
--- a/db/rest/user/api_user.sql
+++ b/src/db/rest/user/api_user.sql
@@ -9,13 +9,15 @@ CREATE VIEW api.user AS
 		u.middle_name,
 		u.email,
 		u.gender,
-		u.join_date,
 		u.birth_date,
-		u.profile_avatar,
-		u.profile_banner,
-		u.profile_bio
+		u.profile_bio,
+		u.created,
+		u.modified,
+		u.seen
 	FROM
-		admin.user u;
+		admin.user u
+	WHERE
+		u.deleted <> TRUE;
 
 GRANT SELECT ON TABLE api.user
 	TO rest_anon, rest_user;
diff --git a/db/rest/user/api_user_delete.sql b/src/db/rest/user/api_user_delete.sql
index 8d7d52f..4389fa0 100644
--- a/db/rest/user/api_user_delete.sql
+++ b/src/db/rest/user/api_user_delete.sql
@@ -11,8 +11,10 @@ BEGIN
 		PERFORM _api.raise_deny();
 	END IF;
 
-	DELETE FROM admin.user
-	WHERE id = _user_id;
+	UPDATE admin.user SET
+		deleted = TRUE,
+		modified = clock_timestamp()
+		WHERE id = _user_id;
 END
 $BODY$;
 
@@ -20,7 +22,7 @@ GRANT EXECUTE ON FUNCTION _api.user_delete()
 	TO rest_user;
 GRANT DELETE ON TABLE api.user
 	TO rest_user;
-GRANT DELETE ON TABLE admin.user
+GRANT UPDATE ON TABLE admin.user
 	TO rest_user;
 
 CREATE TRIGGER api_user_delete_trgr
diff --git a/db/rest/user/api_user_insert.sql b/src/db/rest/user/api_user_insert.sql
index 2297ecd..1a6ef7c 100644
--- a/db/rest/user/api_user_insert.sql
+++ b/src/db/rest/user/api_user_insert.sql
@@ -104,7 +104,9 @@ BEGIN
 		NEW.gender,
 		NEW.birth_date,
 		NEW.profile_bio
-	);
+	)
+	RETURNING id
+	INTO NEW.id;
 
 	NEW.password := NULL;
 
diff --git a/db/rest/user/api_user_update.sql b/src/db/rest/user/api_user_update.sql
index 28e4368..2e7cd50 100644
--- a/db/rest/user/api_user_update.sql
+++ b/src/db/rest/user/api_user_update.sql
@@ -145,7 +145,8 @@ BEGIN
 			email = NEW.email,
 			gender = NEW.gender,
 			birth_date = NEW.birth_date,
-			profile_bio = NEW.profile_bio
+			profile_bio = NEW.profile_bio,
+			modified = clock_timestamp()
 			WHERE id = OLD.id;
 	END IF;
 
diff --git a/db/rest/util/_api_raise.sql b/src/db/rest/util/_api_raise.sql
index 5c740c6..5c740c6 100644
--- a/db/rest/util/_api_raise.sql
+++ b/src/db/rest/util/_api_raise.sql
diff --git a/db/rest/util/_api_raise_deny.sql b/src/db/rest/util/_api_raise_deny.sql
index 17406b7..17406b7 100644
--- a/db/rest/util/_api_raise_deny.sql
+++ b/src/db/rest/util/_api_raise_deny.sql
diff --git a/db/rest/util/_api_raise_null.sql b/src/db/rest/util/_api_raise_null.sql
index be6ee29..be6ee29 100644
--- a/db/rest/util/_api_raise_null.sql
+++ b/src/db/rest/util/_api_raise_null.sql
diff --git a/db/rest/util/_api_raise_unique.sql b/src/db/rest/util/_api_raise_unique.sql
index a18d960..a18d960 100644
--- a/db/rest/util/_api_raise_unique.sql
+++ b/src/db/rest/util/_api_raise_unique.sql
diff --git a/db/rest/util/_api_serve_media.sql b/src/db/rest/util/_api_serve_media.sql
index 8b0f0b8..8b0f0b8 100644
--- a/db/rest/util/_api_serve_media.sql
+++ b/src/db/rest/util/_api_serve_media.sql
diff --git a/db/rest/util/_api_trim.sql b/src/db/rest/util/_api_trim.sql
index c972282..c972282 100644
--- a/db/rest/util/_api_trim.sql
+++ b/src/db/rest/util/_api_trim.sql
diff --git a/db/rest/util/_api_validate_text.sql b/src/db/rest/util/_api_validate_text.sql
index ff3a227..ff3a227 100644
--- a/db/rest/util/_api_validate_text.sql
+++ b/src/db/rest/util/_api_validate_text.sql
diff --git a/db/rev.sql b/src/db/rev.sql
index dff1cfe..dff1cfe 100644
--- a/db/rev.sql
+++ b/src/db/rev.sql