login and register, liking on homepage

30 files changed, 1306 insertions, 0 deletions

diff --git a/src/db/rest/comment/api_comment.sql b/src/db/rest/comment/api_comment.sql
new file mode 100644
index 0000000..c8a0e19
--- /dev/null
+++ b/src/db/rest/comment/api_comment.sql
@@ -0,0 +1,29 @@
+CREATE VIEW api.comment AS
+	SELECT
+		c.id,
+		c.user_id,
+		c.post_id,
+		c.content,
+		c.created,
+		c.modified
+	FROM
+		admin.comment c
+	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;
+GRANT SELECT ON TABLE admin.comment
+	TO rest_anon, rest_user;
diff --git a/src/db/rest/comment/api_comment_delete.sql b/src/db/rest/comment/api_comment_delete.sql
new file mode 100644
index 0000000..262b2ed
--- /dev/null
+++ b/src/db/rest/comment/api_comment_delete.sql
@@ -0,0 +1,32 @@
+CREATE FUNCTION _api.comment_delete()
+RETURNS TRIGGER
+LANGUAGE plpgsql VOLATILE
+AS $BODY$
+DECLARE
+	_user_id INTEGER;
+BEGIN
+	_user_id = _api.get_user_id();
+
+	IF OLD.user_id <> _user_id THEN
+		PERFORM _api.raise_deny();
+	END IF;
+
+	UPDATE admin.comment SET
+		deleted = TRUE,
+		modified = clock_timestamp()
+		WHERE id = OLD.id;
+END
+$BODY$;
+
+GRANT EXECUTE ON FUNCTION _api.comment_delete()
+	TO rest_user;
+GRANT DELETE ON TABLE api.comment
+	TO rest_user;
+GRANT UPDATE ON TABLE admin.comment
+	TO rest_user;
+
+CREATE TRIGGER api_comment_delete_trgr
+	INSTEAD OF DELETE
+			ON api.comment
+			FOR EACH ROW
+				EXECUTE PROCEDURE _api.comment_delete();
diff --git a/src/db/rest/comment/api_comment_insert.sql b/src/db/rest/comment/api_comment_insert.sql
new file mode 100644
index 0000000..990beef
--- /dev/null
+++ b/src/db/rest/comment/api_comment_insert.sql
@@ -0,0 +1,58 @@
+CREATE FUNCTION _api.comment_insert()
+RETURNS TRIGGER
+LANGUAGE plpgsql VOLATILE
+AS $BODY$
+DECLARE
+	_user_id INTEGER;
+BEGIN
+	_user_id = _api.get_user_id();
+
+	NEW.content	:= _api.trim(NEW.content);
+	PERFORM _api.validate_text(
+		_text => NEW.content,
+		_column => 'content',
+		_min => 1,
+		_max => 1024
+	);
+
+	PERFORM TRUE
+	FROM admin.post
+	WHERE id = NEW.post_id;
+
+	IF NOT FOUND THEN
+		PERFORM _api.raise(
+			_msg => 'api_null_post',
+			_err => 400
+		);
+	END IF;
+
+	INSERT INTO admin.comment (
+		user_id,
+		post_id,
+		content
+	) VALUES (
+		_user_id,
+		NEW.post_id,
+		NEW.content
+	)
+	RETURNING id
+	INTO NEW.id;
+
+	RETURN NEW;
+END
+$BODY$;
+
+GRANT EXECUTE ON FUNCTION _api.comment_insert()
+	TO rest_user;
+GRANT INSERT ON TABLE api.comment
+	TO rest_user;
+GRANT INSERT ON TABLE admin.comment
+	TO rest_user;
+GRANT UPDATE ON TABLE sys.comment_id_seq
+	TO rest_user;
+
+CREATE TRIGGER api_comment_insert_trgr
+	INSTEAD OF INSERT
+			ON api.comment
+			FOR EACH ROW
+				EXECUTE PROCEDURE _api.comment_insert();
diff --git a/src/db/rest/comment/api_comment_update.sql b/src/db/rest/comment/api_comment_update.sql
new file mode 100644
index 0000000..b8fc16d
--- /dev/null
+++ b/src/db/rest/comment/api_comment_update.sql
@@ -0,0 +1,51 @@
+CREATE FUNCTION _api.comment_update()
+RETURNS TRIGGER
+LANGUAGE plpgsql VOLATILE
+AS $BODY$
+DECLARE
+	_user_id INTEGER;
+	_changed BOOLEAN;
+BEGIN
+	_user_id = _api.get_user_id();
+	_changed = FALSE;
+
+	IF OLD.user_id <> _user_id THEN
+		PERFORM _api.raise_deny();
+	END IF;
+
+	NEW.content = COALESCE(NEW.content, OLD.content);
+	NEW.content := _api.trim(NEW.content);
+	PERFORM _api.validate_text(
+		_text => NEW.content,
+		_column => 'content',
+		_min => 1,
+		_max => 1024
+	);
+
+	IF NEW.content IS DISTINCT FROM OLD.content THEN
+		_changed = TRUE;
+	END IF;
+
+	IF _changed THEN
+		UPDATE admin.comment SET
+			content = NEW.content,
+			modified = clock_timestamp()
+			WHERE id = OLD.id;
+	END IF;
+
+	RETURN NEW;
+END
+$BODY$;
+
+GRANT EXECUTE ON FUNCTION _api.comment_update()
+	TO rest_user;
+GRANT UPDATE ON TABLE api.comment
+	TO rest_user;
+GRANT UPDATE ON TABLE admin.comment
+	TO rest_user;
+
+CREATE TRIGGER api_comment_update_trgr
+	INSTEAD OF UPDATE
+			ON api.comment
+			FOR EACH ROW
+				EXECUTE PROCEDURE _api.comment_update();
diff --git a/src/db/rest/like/api_like.sql b/src/db/rest/like/api_like.sql
new file mode 100644
index 0000000..6588b43
--- /dev/null
+++ b/src/db/rest/like/api_like.sql
@@ -0,0 +1,16 @@
+CREATE VIEW api.like AS
+	SELECT
+		l.id,
+		l.user_id,
+		l.post_id,
+		l.comment_id,
+		l.value,
+		l.created,
+		l.modified
+	FROM
+		admin.like l;
+
+GRANT SELECT ON TABLE api.like
+	TO rest_anon, rest_user;
+GRANT SELECT ON TABLE admin.like
+	TO rest_anon, rest_user;
diff --git a/src/db/rest/like/api_like_delete.sql b/src/db/rest/like/api_like_delete.sql
new file mode 100644
index 0000000..7209a40
--- /dev/null
+++ b/src/db/rest/like/api_like_delete.sql
@@ -0,0 +1,32 @@
+CREATE FUNCTION _api.like_delete()
+RETURNS TRIGGER
+LANGUAGE plpgsql VOLATILE
+AS $BODY$
+DECLARE
+	_user_id INTEGER;
+BEGIN
+	_user_id = _api.get_user_id();
+
+	IF OLD.user_id <> _user_id THEN
+		PERFORM _api.raise_deny();
+	END IF;
+
+	UPDATE admin.like SET
+		value = FALSE,
+		modified = clock_timestamp()
+		WHERE id = OLD.id;
+END
+$BODY$;
+
+GRANT EXECUTE ON FUNCTION _api.like_delete()
+	TO rest_user;
+GRANT DELETE ON TABLE api.like
+	TO rest_user;
+GRANT UPDATE ON TABLE admin.like
+	TO rest_user;
+
+CREATE TRIGGER api_like_delete_trgr
+	INSTEAD OF DELETE
+			ON api.like
+			FOR EACH ROW
+				EXECUTE PROCEDURE _api.like_delete();
diff --git a/src/db/rest/like/api_like_insert.sql b/src/db/rest/like/api_like_insert.sql
new file mode 100644
index 0000000..a02ad4e
--- /dev/null
+++ b/src/db/rest/like/api_like_insert.sql
@@ -0,0 +1,51 @@
+CREATE FUNCTION _api.like_insert()
+RETURNS TRIGGER
+LANGUAGE plpgsql VOLATILE
+AS $BODY$
+DECLARE
+	_user_id INTEGER;
+BEGIN
+	_user_id = _api.get_user_id();
+
+	IF
+		NEW.post_id IS NULL AND
+		NEW.comment_id IS NULL
+	THEN
+		-- for now
+		PERFORM _api.raise_deny();
+	END IF;
+
+	NEW.value := COALESCE(NEW.value, TRUE);
+
+	INSERT INTO admin.like (
+		user_id,
+		post_id,
+		comment_id,
+		value
+	) VALUES (
+		_user_id,
+		NEW.post_id,
+		NEW.comment_id,
+		NEW.value
+	)
+	RETURNING id
+	INTO NEW.id;
+
+	RETURN NEW;
+END
+$BODY$;
+
+GRANT EXECUTE ON FUNCTION _api.like_insert()
+	TO rest_user;
+GRANT INSERT ON TABLE api.like
+	TO rest_user;
+GRANT INSERT ON TABLE admin.like
+	TO rest_user;
+GRANT UPDATE ON TABLE sys.like_id_seq
+	TO rest_user;
+
+CREATE TRIGGER api_like_insert_trgr
+	INSTEAD OF INSERT
+			ON api.like
+			FOR EACH ROW
+				EXECUTE PROCEDURE _api.like_insert();
diff --git a/src/db/rest/like/api_like_update.sql b/src/db/rest/like/api_like_update.sql
new file mode 100644
index 0000000..76db73a
--- /dev/null
+++ b/src/db/rest/like/api_like_update.sql
@@ -0,0 +1,44 @@
+CREATE FUNCTION _api.like_update()
+RETURNS TRIGGER
+LANGUAGE plpgsql VOLATILE
+AS $BODY$
+DECLARE
+	_user_id INTEGER;
+	_changed BOOLEAN;
+BEGIN
+	_user_id = _api.get_user_id();
+	_changed = FALSE;
+
+	IF OLD.user_id <> _user_id THEN
+		PERFORM _api.raise_deny();
+	END IF;
+
+	NEW.value = COALESCE(NEW.value, OLD.value);
+
+	IF NEW.value IS DISTINCT FROM OLD.value THEN
+		_changed = TRUE;
+	END IF;
+
+	IF _changed THEN
+		UPDATE admin.like SET
+			value = NEW.value,
+			modified = clock_timestamp()
+			WHERE id = OLD.id;
+	END IF;
+
+	RETURN NEW;
+END
+$BODY$;
+
+GRANT EXECUTE ON FUNCTION _api.like_update()
+	TO rest_user;
+GRANT UPDATE ON TABLE api.like
+	TO rest_user;
+GRANT UPDATE ON TABLE admin.like
+	TO rest_user;
+
+CREATE TRIGGER api_like_update_trgr
+	INSTEAD OF UPDATE
+			ON api.like
+			FOR EACH ROW
+				EXECUTE PROCEDURE _api.like_update();
diff --git a/src/db/rest/login/_api_sign_jwt.sql b/src/db/rest/login/_api_sign_jwt.sql
new file mode 100644
index 0000000..dc8e920
--- /dev/null
+++ b/src/db/rest/login/_api_sign_jwt.sql
@@ -0,0 +1,33 @@
+CREATE FUNCTION _api.sign_jwt(
+	_role TEXT,
+	_user_id INTEGER
+)
+RETURNS sys.JWT
+LANGUAGE plpgsql VOLATILE
+AS $BODY$
+DECLARE
+	_jwt_secret TEXT;
+	_token sys.JWT;
+BEGIN
+	SELECT jwt_secret INTO _jwt_secret
+	FROM sys.database_info
+	WHERE name = current_database();
+
+	SELECT public.sign(
+		row_to_json(r), _jwt_secret
+	) INTO _token
+	FROM (
+		SELECT
+			_role AS role,
+			_user_id AS user_id,
+			extract(epoch FROM now())::integer + (60 * 60 * 24) AS exp
+	) r;
+
+	RETURN _token;
+END
+$BODY$;
+
+GRANT EXECUTE ON FUNCTION _api.sign_jwt(TEXT, INTEGER)
+	TO rest_anon, rest_user;
+GRANT SELECT ON TABLE sys.database_info
+	TO rest_anon, rest_user;
diff --git a/src/db/rest/login/_api_validate_role.sql b/src/db/rest/login/_api_validate_role.sql
new file mode 100644
index 0000000..9f1e54f
--- /dev/null
+++ b/src/db/rest/login/_api_validate_role.sql
@@ -0,0 +1,30 @@
+CREATE FUNCTION _api.validate_role()
+RETURNS TRIGGER
+LANGUAGE plpgsql VOLATILE
+AS $BODY$
+BEGIN
+	IF NOT EXISTS (
+		SELECT TRUE
+		FROM pg_catalog.pg_roles AS r
+		WHERE r.rolname = NEW.role
+	) THEN
+		PERFORM _api.raise(
+			_err => 500
+		);
+		RETURN NULL;
+	END IF;
+
+	RETURN NEW;
+END
+$BODY$;
+
+CREATE CONSTRAINT TRIGGER api_validate_role_trgr
+	AFTER INSERT OR UPDATE
+		ON admin.user
+			FOR EACH ROW
+				EXECUTE PROCEDURE _api.validate_role();
+
+GRANT EXECUTE ON FUNCTION _api.validate_role()
+	TO rest_anon, rest_user;
+GRANT SELECT ON TABLE pg_catalog.pg_roles
+	TO rest_anon, rest_user;
diff --git a/src/db/rest/login/_api_verify_jwt.sql b/src/db/rest/login/_api_verify_jwt.sql
new file mode 100644
index 0000000..9e63cc9
--- /dev/null
+++ b/src/db/rest/login/_api_verify_jwt.sql
@@ -0,0 +1,47 @@
+CREATE FUNCTION _api.verify_jwt(
+	_token TEXT
+)
+RETURNS INTEGER
+LANGUAGE plpgsql VOLATILE
+AS $BODY$
+DECLARE
+	_payload JSON;
+	_valid BOOLEAN;
+	_jwt_secret TEXT;
+	_user_id INTEGER;
+BEGIN
+	SELECT jwt_secret INTO _jwt_secret
+	FROM sys.database_info
+	WHERE name = current_database();
+
+	SELECT payload, valid
+	INTO _payload, _valid
+	FROM public.verify(
+		_token,
+		_jwt_secret
+	);
+
+	IF NOT FOUND THEN
+		RETURN NULL;
+	END IF;
+
+	IF _valid <> TRUE THEN
+		RETURN NULL;
+	END IF;
+
+	_user_id = _payload->>'user_id';
+
+	UPDATE admin.user
+	SET seen = clock_timestamp()
+	WHERE id = _user_id;
+
+	RETURN _user_id;
+END
+$BODY$;
+
+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/src/db/rest/login/api_login.sql b/src/db/rest/login/api_login.sql
new file mode 100644
index 0000000..0cf0535
--- /dev/null
+++ b/src/db/rest/login/api_login.sql
@@ -0,0 +1,41 @@
+CREATE FUNCTION api.login(
+	username TEXT,
+	password TEXT
+)
+RETURNS sys.JWT
+LANGUAGE plpgsql VOLATILE
+AS $BODY$
+DECLARE
+	_role NAME;
+	_user_id INTEGER;
+	_token sys.JWT;
+BEGIN
+	SELECT role INTO _role
+		FROM admin.user u
+		WHERE u.username = login.username
+		AND u.password = login.password;
+
+	IF _role IS NULL THEN
+		PERFORM _api.raise(
+			_msg => 'api_invalid_login'
+		);
+		RETURN NULL;
+	END IF;
+
+	SELECT id INTO _user_id
+		FROM admin.user u
+		WHERE u.username = login.username;
+
+	_token = _api.sign_jwt(
+		_role,
+		_user_id
+	);
+
+	RETURN _token;
+END
+$BODY$;
+
+GRANT EXECUTE ON FUNCTION api.login(TEXT, TEXT)
+	TO rest_anon, rest_user;
+GRANT SELECT ON TABLE admin.user
+	TO rest_anon, rest_user;
diff --git a/src/db/rest/post/api_post.sql b/src/db/rest/post/api_post.sql
new file mode 100644
index 0000000..0d60473
--- /dev/null
+++ b/src/db/rest/post/api_post.sql
@@ -0,0 +1,37 @@
+CREATE VIEW api.post AS
+	SELECT
+		p.id,
+		p.user_id,
+		p.content,
+		p.created,
+		p.modified,
+		COALESCE(c.cc, 0)
+			AS comment_count
+	FROM
+		admin.post p
+	LEFT JOIN (
+		SELECT
+			COUNT(c.id) as cc,
+			c.post_id
+		FROM
+			admin.comment c
+		GROUP BY
+			c.post_id
+	) 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;
+GRANT SELECT ON TABLE admin.post
+	TO rest_anon, rest_user;
diff --git a/src/db/rest/post/api_post_delete.sql b/src/db/rest/post/api_post_delete.sql
new file mode 100644
index 0000000..8f26b40
--- /dev/null
+++ b/src/db/rest/post/api_post_delete.sql
@@ -0,0 +1,32 @@
+CREATE FUNCTION _api.post_delete()
+RETURNS TRIGGER
+LANGUAGE plpgsql VOLATILE
+AS $BODY$
+DECLARE
+	_user_id INTEGER;
+BEGIN
+	_user_id = _api.get_user_id();
+
+	IF OLD.user_id <> _user_id THEN
+		PERFORM _api.raise_deny();
+	END IF;
+
+	UPDATE admin.post SET
+		deleted = TRUE,
+		modified = clock_timestamp()
+		WHERE id = OLD.id;
+END
+$BODY$;
+
+GRANT EXECUTE ON FUNCTION _api.post_delete()
+	TO rest_user;
+GRANT DELETE ON TABLE api.post
+	TO rest_user;
+GRANT UPDATE ON TABLE admin.post
+	TO rest_user;
+
+CREATE TRIGGER api_post_delete_trgr
+	INSTEAD OF DELETE
+			ON api.post
+			FOR EACH ROW
+				EXECUTE PROCEDURE _api.post_delete();
diff --git a/src/db/rest/post/api_post_insert.sql b/src/db/rest/post/api_post_insert.sql
new file mode 100644
index 0000000..e0594dc
--- /dev/null
+++ b/src/db/rest/post/api_post_insert.sql
@@ -0,0 +1,46 @@
+CREATE FUNCTION _api.post_insert()
+RETURNS TRIGGER
+LANGUAGE plpgsql VOLATILE
+AS $BODY$
+DECLARE
+	_user_id INTEGER;
+BEGIN
+	_user_id = _api.get_user_id();
+
+	NEW.content	:= _api.trim(NEW.content);
+
+	PERFORM _api.validate_text(
+		_text => NEW.content,
+		_column => 'content',
+		_min => 1,
+		_max => 4096
+	);
+
+	INSERT INTO admin.post (
+		user_id,
+		content
+	) VALUES (
+		_user_id,
+		NEW.content
+	)
+	RETURNING id
+	INTO NEW.id;
+
+	RETURN NEW;
+END
+$BODY$;
+
+GRANT EXECUTE ON FUNCTION _api.post_insert()
+	TO rest_user;
+GRANT INSERT ON TABLE api.post
+	TO rest_user;
+GRANT INSERT ON TABLE admin.post
+	TO rest_user;
+GRANT UPDATE ON TABLE sys.post_id_seq
+	TO rest_user;
+
+CREATE TRIGGER api_post_insert_trgr
+	INSTEAD OF INSERT
+			ON api.post
+			FOR EACH ROW
+				EXECUTE PROCEDURE _api.post_insert();
diff --git a/src/db/rest/post/api_post_update.sql b/src/db/rest/post/api_post_update.sql
new file mode 100644
index 0000000..7b4360d
--- /dev/null
+++ b/src/db/rest/post/api_post_update.sql
@@ -0,0 +1,51 @@
+CREATE FUNCTION _api.post_update()
+RETURNS TRIGGER
+LANGUAGE plpgsql VOLATILE
+AS $BODY$
+DECLARE
+	_user_id INTEGER;
+	_changed BOOLEAN;
+BEGIN
+	_user_id = _api.get_user_id();
+	_changed = FALSE;
+
+	IF OLD.user_id <> _user_id THEN
+		PERFORM _api.raise_deny();
+	END IF;
+
+	NEW.content = COALESCE(NEW.content, OLD.content);
+	NEW.content := _api.trim(NEW.content);
+	PERFORM _api.validate_text(
+		_text => NEW.content,
+		_column => 'content',
+		_min => 1,
+		_max => 4096
+	);
+
+	IF NEW.content IS DISTINCT FROM OLD.content THEN
+		_changed = TRUE;
+	END IF;
+
+	IF _changed THEN
+		UPDATE admin.post SET
+			content = NEW.content,
+			modified = clock_timestamp()
+			WHERE id = OLD.id;
+	END IF;
+
+	RETURN NEW;
+END
+$BODY$;
+
+GRANT EXECUTE ON FUNCTION _api.post_update()
+	TO rest_user;
+GRANT UPDATE ON TABLE api.post
+	TO rest_user;
+GRANT UPDATE ON TABLE admin.post
+	TO rest_user;
+
+CREATE TRIGGER api_post_update_trgr
+	INSTEAD OF UPDATE
+			ON api.post
+			FOR EACH ROW
+				EXECUTE PROCEDURE _api.post_update();
diff --git a/src/db/rest/rest.sql b/src/db/rest/rest.sql
new file mode 100644
index 0000000..3e6737c
--- /dev/null
+++ b/src/db/rest/rest.sql
@@ -0,0 +1,56 @@
+BEGIN TRANSACTION;
+SET search_path = public;
+
+DROP SCHEMA IF EXISTS api CASCADE;
+CREATE SCHEMA api;
+
+DROP SCHEMA IF EXISTS _api CASCADE;
+CREATE SCHEMA _api;
+
+GRANT USAGE ON SCHEMA admin TO rest_anon, rest_user;
+GRANT USAGE ON SCHEMA sys TO rest_anon, rest_user;
+
+GRANT USAGE ON SCHEMA api TO rest_anon, rest_user;
+GRANT USAGE ON SCHEMA _api TO rest_anon, rest_user;
+
+-- util
+\i /db/rest/util/_api_trim.sql;
+\i /db/rest/util/_api_serve_media.sql;
+\i /db/rest/util/_api_raise.sql;
+\i /db/rest/util/_api_raise_null.sql;
+\i /db/rest/util/_api_raise_unique.sql;
+\i /db/rest/util/_api_validate_text.sql;
+\i /db/rest/util/_api_get_user_id.sql;
+
+-- user
+\i /db/rest/user/api_user.sql;
+\i /db/rest/user/api_user_insert.sql;
+\i /db/rest/user/api_user_update.sql;
+\i /db/rest/user/api_user_delete.sql;
+\i /db/rest/user/api_avatar.sql;
+
+-- post
+\i /db/rest/post/api_post.sql;
+\i /db/rest/post/api_post_insert.sql;
+\i /db/rest/post/api_post_update.sql;
+\i /db/rest/post/api_post_delete.sql;
+
+-- comment
+\i /db/rest/comment/api_comment.sql;
+\i /db/rest/comment/api_comment_insert.sql;
+\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;
+\i /db/rest/login/_api_validate_role.sql;
+\i /db/rest/login/api_login.sql;
+
+COMMIT TRANSACTION;
diff --git a/src/db/rest/user/api_avatar.sql b/src/db/rest/user/api_avatar.sql
new file mode 100644
index 0000000..981409f
--- /dev/null
+++ b/src/db/rest/user/api_avatar.sql
@@ -0,0 +1,22 @@
+CREATE FUNCTION api.avatar(
+	user_id INTEGER DEFAULT 0
+)
+RETURNS sys."*/*"
+LANGUAGE plpgsql VOLATILE
+AS $BODY$
+DECLARE
+	_mod INTEGER;
+	_name TEXT;
+BEGIN
+	_mod = MOD(user_id, 24);
+	_name = 'default_avatar_' || _mod || '.png';
+	RETURN _api.serve_media(_name);
+END
+$BODY$;
+
+GRANT EXECUTE ON FUNCTION api.avatar(INTEGER)
+	TO rest_anon, rest_user;
+GRANT SELECT ON TABLE admin.user
+	TO rest_anon, rest_user;
+GRANT SELECT ON TABLE admin.media
+	TO rest_anon, rest_user;
diff --git a/src/db/rest/user/api_user.sql b/src/db/rest/user/api_user.sql
new file mode 100644
index 0000000..6735775
--- /dev/null
+++ b/src/db/rest/user/api_user.sql
@@ -0,0 +1,25 @@
+CREATE VIEW api.user AS
+	SELECT
+		u.id,
+		u.username,
+		NULL AS password,
+		u.role,
+		u.first_name,
+		u.last_name,
+		u.middle_name,
+		u.email,
+		u.gender,
+		u.birth_date,
+		u.profile_bio,
+		u.created,
+		u.modified,
+		u.seen
+	FROM
+		admin.user u
+	WHERE
+		u.deleted <> TRUE;
+
+GRANT SELECT ON TABLE api.user
+	TO rest_anon, rest_user;
+GRANT SELECT ON TABLE admin.user
+	TO rest_anon, rest_user;
diff --git a/src/db/rest/user/api_user_delete.sql b/src/db/rest/user/api_user_delete.sql
new file mode 100644
index 0000000..4389fa0
--- /dev/null
+++ b/src/db/rest/user/api_user_delete.sql
@@ -0,0 +1,32 @@
+CREATE FUNCTION _api.user_delete()
+RETURNS TRIGGER
+LANGUAGE plpgsql VOLATILE
+AS $BODY$
+DECLARE
+	_user_id INTEGER;
+BEGIN
+	_user_id = _api.get_user_id();
+
+	IF OLD.id <> _user_id THEN
+		PERFORM _api.raise_deny();
+	END IF;
+
+	UPDATE admin.user SET
+		deleted = TRUE,
+		modified = clock_timestamp()
+		WHERE id = _user_id;
+END
+$BODY$;
+
+GRANT EXECUTE ON FUNCTION _api.user_delete()
+	TO rest_user;
+GRANT DELETE ON TABLE api.user
+	TO rest_user;
+GRANT UPDATE ON TABLE admin.user
+	TO rest_user;
+
+CREATE TRIGGER api_user_delete_trgr
+	INSTEAD OF DELETE
+			ON api.user
+			FOR EACH ROW
+				EXECUTE PROCEDURE _api.user_delete();
diff --git a/src/db/rest/user/api_user_insert.sql b/src/db/rest/user/api_user_insert.sql
new file mode 100644
index 0000000..1a6ef7c
--- /dev/null
+++ b/src/db/rest/user/api_user_insert.sql
@@ -0,0 +1,130 @@
+CREATE FUNCTION _api.user_insert()
+RETURNS TRIGGER
+LANGUAGE plpgsql VOLATILE
+AS $BODY$
+DECLARE
+	_length INTEGER;
+BEGIN
+
+	NEW.username	:= _api.trim(NEW.username);
+	NEW.password	:= _api.trim(NEW.password);
+	NEW.first_name	:= _api.trim(NEW.first_name);
+	NEW.last_name	:= _api.trim(NEW.last_name);
+	NEW.middle_name	:= _api.trim(NEW.middle_name);
+	NEW.email		:= _api.trim(NEW.email);
+	NEW.gender		:= _api.trim(NEW.gender);
+	NEW.profile_bio	:= _api.trim(NEW.profile_bio);
+
+	PERFORM _api.validate_text(
+		_text => NEW.username,
+		_column => 'username',
+		_min => 1,
+		_max => 24
+	);
+
+	PERFORM TRUE FROM admin.user
+		WHERE username = NEW.username;
+
+	IF FOUND THEN
+		PERFORM _api.raise_unique('username');
+	END IF;
+
+	PERFORM _api.validate_text(
+		_text => NEW.password,
+		_column => 'password',
+		_min => 1,
+		_max => 256
+	);
+
+	PERFORM _api.validate_text(
+		_text => NEW.first_name,
+		_nullable => TRUE,
+		_column => 'first_name',
+		_max => 256
+	);
+	NEW.first_name = COALESCE(NEW.first_name, ''::text);
+
+	PERFORM _api.validate_text(
+		_text => NEW.last_name,
+		_nullable => TRUE,
+		_column => 'last_name',
+		_max => 256
+	);
+	NEW.last_name = COALESCE(NEW.last_name, ''::text);
+
+	PERFORM _api.validate_text(
+		_text => NEW.middle_name,
+		_nullable => TRUE,
+		_column => 'middle_name',
+		_max => 256
+	);
+	NEW.middle_name = COALESCE(NEW.middle_name, ''::text);
+
+	PERFORM _api.validate_text(
+		_text => NEW.email,
+		_column => 'email',
+		_max => 256
+	);
+
+	PERFORM _api.validate_text(
+		_text => NEW.gender,
+		_column => 'gender',
+		_max => 256
+	);
+
+	IF NEW.birth_date IS NULL THEN
+		PERFORM _api.raise_null('birth_date');
+	END IF;
+
+	PERFORM _api.validate_text(
+		_text => NEW.profile_bio,
+		_nullable => TRUE,
+		_column => 'profile_bio',
+		_max => 2048
+	);
+	NEW.profile_bio = COALESCE(NEW.profile_bio, ''::text);
+
+	INSERT INTO admin.user (
+		username,
+		password,
+		first_name,
+		last_name,
+		middle_name,
+		email,
+		gender,
+		birth_date,
+		profile_bio
+	) VALUES (
+		NEW.username,
+		NEW.password,
+		NEW.first_name,
+		NEW.last_name,
+		NEW.middle_name,
+		NEW.email,
+		NEW.gender,
+		NEW.birth_date,
+		NEW.profile_bio
+	)
+	RETURNING id
+	INTO NEW.id;
+
+	NEW.password := NULL;
+
+	RETURN NEW;
+END
+$BODY$;
+
+GRANT EXECUTE ON FUNCTION _api.user_insert()
+	TO rest_anon, rest_user;
+GRANT INSERT ON TABLE api.user
+	TO rest_anon, rest_user;
+GRANT INSERT ON TABLE admin.user
+	TO rest_anon, rest_user;
+GRANT UPDATE ON TABLE sys.user_id_seq
+	TO rest_anon, rest_user;
+
+CREATE TRIGGER api_user_insert_trgr
+	INSTEAD OF INSERT
+			ON api.user
+			FOR EACH ROW
+				EXECUTE PROCEDURE _api.user_insert();
diff --git a/src/db/rest/user/api_user_update.sql b/src/db/rest/user/api_user_update.sql
new file mode 100644
index 0000000..2e7cd50
--- /dev/null
+++ b/src/db/rest/user/api_user_update.sql
@@ -0,0 +1,168 @@
+CREATE FUNCTION _api.user_update()
+RETURNS TRIGGER
+LANGUAGE plpgsql VOLATILE
+AS $BODY$
+DECLARE
+	_user_id INTEGER;
+	_changed BOOLEAN;
+BEGIN
+	_user_id = _api.get_user_id();
+	_changed = FALSE;
+
+	IF OLD.id <> _user_id THEN
+		PERFORM _api.raise_deny();
+	END IF;
+
+	-- username
+	NEW.username = COALESCE(NEW.username, OLD.username);
+	NEW.username := _api.trim(NEW.username);
+	PERFORM _api.validate_text(
+		_text => NEW.username,
+		_column => 'username',
+		_min => 1,
+		_max => 24
+	);
+
+	IF NEW.username IS DISTINCT FROM OLD.username THEN
+		PERFORM TRUE FROM admin.user
+			WHERE username = NEW.username;
+		IF FOUND THEN
+			PERFORM _api.raise_unique('username');
+		END IF;
+		_changed = TRUE;
+	END IF;
+
+	-- password
+	SELECT password
+		INTO OLD.password
+		FROM admin.user
+		WHERE id = OLD.id;
+
+	NEW.password = COALESCE(NEW.password, OLD.password);
+	NEW.password := _api.trim(NEW.password);
+	PERFORM _api.validate_text(
+		_text => NEW.password,
+		_column => 'password',
+		_min => 1,
+		_max => 256
+	);
+
+	IF NEW.password IS DISTINCT FROM OLD.password THEN
+		_changed = TRUE;
+	END IF;
+
+	-- first name
+	NEW.first_name = COALESCE(NEW.first_name, OLD.first_name);
+	NEW.first_name := _api.trim(NEW.first_name);
+	PERFORM _api.validate_text(
+		_text => NEW.first_name,
+		_column => 'first_name',
+		_max => 256
+	);
+
+	IF NEW.first_name IS DISTINCT FROM OLD.first_name THEN
+		_changed = TRUE;
+	END IF;
+
+	-- last name
+	NEW.last_name = COALESCE(NEW.last_name, OLD.last_name);
+	NEW.last_name := _api.trim(NEW.last_name);
+	PERFORM _api.validate_text(
+		_text => NEW.last_name,
+		_column => 'last_name',
+		_max => 256
+	);
+
+	IF NEW.last_name IS DISTINCT FROM OLD.last_name THEN
+		_changed = TRUE;
+	END IF;
+
+	-- middle name
+	NEW.middle_name = COALESCE(NEW.middle_name, OLD.middle_name);
+	NEW.middle_name := _api.trim(NEW.middle_name);
+	PERFORM _api.validate_text(
+		_text => NEW.middle_name,
+		_column => 'middle_name',
+		_max => 256
+	);
+
+	IF NEW.middle_name IS DISTINCT FROM OLD.middle_name THEN
+		_changed = TRUE;
+	END IF;
+
+	-- email
+	NEW.email = COALESCE(NEW.email, OLD.email);
+	NEW.email := _api.trim(NEW.email);
+	PERFORM _api.validate_text(
+		_text => NEW.email,
+		_column => 'email',
+		_max => 256
+	);
+
+	IF NEW.email IS DISTINCT FROM OLD.email THEN
+		_changed = TRUE;
+	END IF;
+
+	-- gender
+	NEW.gender = COALESCE(NEW.gender, OLD.gender);
+	NEW.gender := _api.trim(NEW.gender);
+	PERFORM _api.validate_text(
+		_text => NEW.gender,
+		_column => 'gender',
+		_max => 256
+	);
+
+	IF NEW.gender IS DISTINCT FROM OLD.gender THEN
+		_changed = TRUE;
+	END IF;
+
+	-- birth date
+	NEW.birth_date = COALESCE(NEW.birth_date, OLD.birth_date);
+	IF NEW.birth_date IS DISTINCT FROM OLD.birth_date THEN
+		_changed = TRUE;
+	END IF;
+
+	-- profile bio
+	NEW.profile_bio = COALESCE(NEW.profile_bio, OLD.profile_bio);
+	NEW.profile_bio := _api.trim(NEW.profile_bio);
+	PERFORM _api.validate_text(
+		_text => NEW.profile_bio,
+		_column => 'profile_bio',
+		_max => 2048
+	);
+
+	IF NEW.profile_bio IS DISTINCT FROM OLD.profile_bio THEN
+		_changed = TRUE;
+	END IF;
+
+	IF _changed THEN
+		UPDATE admin.user SET
+			username = NEW.username,
+			password = NEW.password,
+			first_name = NEW.first_name,
+			last_name = NEW.last_name,
+			middle_name = NEW.middle_name,
+			email = NEW.email,
+			gender = NEW.gender,
+			birth_date = NEW.birth_date,
+			profile_bio = NEW.profile_bio,
+			modified = clock_timestamp()
+			WHERE id = OLD.id;
+	END IF;
+
+	RETURN NEW;
+END
+$BODY$;
+
+GRANT EXECUTE ON FUNCTION _api.user_update()
+	TO rest_user;
+GRANT UPDATE ON TABLE api.user
+	TO rest_user;
+GRANT UPDATE ON TABLE admin.user
+	TO rest_user;
+
+CREATE TRIGGER api_user_update_trgr
+	INSTEAD OF UPDATE
+			ON api.user
+			FOR EACH ROW
+				EXECUTE PROCEDURE _api.user_update();
diff --git a/src/db/rest/util/_api_get_user_id.sql b/src/db/rest/util/_api_get_user_id.sql
new file mode 100644
index 0000000..e86afc3
--- /dev/null
+++ b/src/db/rest/util/_api_get_user_id.sql
@@ -0,0 +1,22 @@
+CREATE FUNCTION _api.get_user_id()
+RETURNS INTEGER
+LANGUAGE plpgsql VOLATILE
+AS $BODY$
+DECLARE
+	_user_id INTEGER;
+BEGIN
+	_user_id = CURRENT_SETTING(
+		'request.jwt.claims',
+		TRUE
+	)::JSON->>'user_id';
+
+	UPDATE admin.user
+	SET seen = clock_timestamp()
+	WHERE id = _user_id;
+
+	RETURN _user_id;
+END
+$BODY$;
+
+GRANT UPDATE ON TABLE admin.user
+	TO rest_anon, rest_user;
diff --git a/src/db/rest/util/_api_raise.sql b/src/db/rest/util/_api_raise.sql
new file mode 100644
index 0000000..5c740c6
--- /dev/null
+++ b/src/db/rest/util/_api_raise.sql
@@ -0,0 +1,50 @@
+CREATE TABLE _api.err_map (
+	err		INTEGER,
+	pg_err	TEXT
+);
+
+ALTER TABLE _api.err_map OWNER TO xssbook;
+
+ALTER TABLE ONLY _api.err_map
+	ADD CONSTRAINT err_map_pkey PRIMARY KEY (err);
+
+INSERT INTO _api.err_map (err, pg_err)
+VALUES
+	(400, 'P0001'),
+	(401, '42501'),
+	(403, '42501'),
+	(404, '42883'),
+	(409, '23505'),
+	(500, 'XX001');
+
+CREATE FUNCTION _api.raise(
+	_msg TEXT DEFAULT '',
+	_detail TEXT DEFAULT '',
+	_hint TEXT DEFAULT '',
+	_err INTEGER DEFAULT 400
+)
+RETURNS BOOLEAN
+LANGUAGE plpgsql VOLATILE
+AS $BODY$
+DECLARE
+	_pg_err TEXT;
+BEGIN
+
+	SELECT pg_err INTO _pg_err
+		FROM _api.err_map
+		WHERE err = _err;
+
+	RAISE EXCEPTION USING
+		MESSAGE := _msg,
+		DETAIL := _detail,
+		HINT := _hint,
+		ERRCODE := _pg_err;
+
+	RETURN FALSE;
+END
+$BODY$;
+
+GRANT SELECT ON TABLE _api.err_map
+	TO rest_anon, rest_user;
+GRANT EXECUTE ON FUNCTION _api.raise(TEXT, TEXT, TEXT, INTEGER)
+	TO rest_anon, rest_user;
diff --git a/src/db/rest/util/_api_raise_deny.sql b/src/db/rest/util/_api_raise_deny.sql
new file mode 100644
index 0000000..17406b7
--- /dev/null
+++ b/src/db/rest/util/_api_raise_deny.sql
@@ -0,0 +1,16 @@
+CREATE FUNCTION _api.raise_deny()
+RETURNS BOOLEAN
+LANGUAGE plpgsql VOLATILE
+AS $BODY$
+BEGIN
+	PERFORM _api.raise(
+		_msg => 'api_denied',
+		_err => 403
+	);
+
+	RETURN TRUE;
+END
+$BODY$;
+
+GRANT EXECUTE ON FUNCTION _api.raise_null(TEXT)
+	TO rest_anon, rest_user;
diff --git a/src/db/rest/util/_api_raise_null.sql b/src/db/rest/util/_api_raise_null.sql
new file mode 100644
index 0000000..be6ee29
--- /dev/null
+++ b/src/db/rest/util/_api_raise_null.sql
@@ -0,0 +1,18 @@
+CREATE FUNCTION _api.raise_null(
+	_column TEXT DEFAULT ''
+)
+RETURNS BOOLEAN
+LANGUAGE plpgsql VOLATILE
+AS $BODY$
+BEGIN
+	PERFORM _api.raise(
+		_msg => 'api_null_value',
+		_detail => _column
+	);
+
+	RETURN TRUE;
+END
+$BODY$;
+
+GRANT EXECUTE ON FUNCTION _api.raise_null(TEXT)
+	TO rest_anon, rest_user;
diff --git a/src/db/rest/util/_api_raise_unique.sql b/src/db/rest/util/_api_raise_unique.sql
new file mode 100644
index 0000000..a18d960
--- /dev/null
+++ b/src/db/rest/util/_api_raise_unique.sql
@@ -0,0 +1,18 @@
+CREATE FUNCTION _api.raise_unique(
+	_column TEXT DEFAULT ''
+)
+RETURNS BOOLEAN
+LANGUAGE plpgsql VOLATILE
+AS $BODY$
+BEGIN
+	PERFORM _api.raise(
+		_msg => 'api_unique_value',
+		_detail => _column
+	);
+
+	RETURN TRUE;
+END
+$BODY$;
+
+GRANT EXECUTE ON FUNCTION _api.raise_unique(TEXT)
+	TO rest_anon, rest_user;
diff --git a/src/db/rest/util/_api_serve_media.sql b/src/db/rest/util/_api_serve_media.sql
new file mode 100644
index 0000000..8b0f0b8
--- /dev/null
+++ b/src/db/rest/util/_api_serve_media.sql
@@ -0,0 +1,41 @@
+CREATE FUNCTION _api.serve_media(
+	_name TEXT
+)
+RETURNS sys."*/*"
+LANGUAGE plpgsql VOLATILE
+AS $BODY$
+DECLARE
+	_headers TEXT;
+	_data BYTEA;
+BEGIN
+
+	SELECT FORMAT(
+      '[{"Content-Type": "%s"},'
+       '{"Content-Disposition": "inline; filename=\"%s\""},'
+       '{"Cache-Control": "max-age=259200"}]'
+      ,	m.type, m.name)
+	FROM admin.media m
+	WHERE m.name = _name INTO _headers;
+
+	PERFORM SET_CONFIG('response.headers', _headers, true);
+
+	SELECT m.content
+	FROM admin.media m
+	WHERE m.name = _name
+	INTO _data;
+
+	IF FOUND THEN
+		RETURN(_data);
+	ELSE
+		PERFORM _api.raise(
+			_msg => 'api_not_found',
+			_err => 404
+		);
+	END IF;
+END
+$BODY$;
+
+GRANT EXECUTE ON FUNCTION _api.serve_media(TEXT)
+	TO rest_anon, rest_user;
+GRANT SELECT ON TABLE admin.media
+	TO rest_anon, rest_user;
diff --git a/src/db/rest/util/_api_trim.sql b/src/db/rest/util/_api_trim.sql
new file mode 100644
index 0000000..c972282
--- /dev/null
+++ b/src/db/rest/util/_api_trim.sql
@@ -0,0 +1,25 @@
+CREATE FUNCTION _api.trim(
+	_text TEXT
+)
+RETURNS TEXT
+LANGUAGE plpgsql VOLATILE
+AS $BODY$
+DECLARE
+	_new TEXT;
+BEGIN
+
+	IF _text IS NULL THEN
+		RETURN NULL;
+	END IF;
+
+	_new = _text;
+	_new = TRIM(_new);
+	_new = REGEXP_REPLACE(_new, '^(?: |\r|\n)*', '');
+	_new = REGEXP_REPLACE(_new, '(?: |\r|\n)*$', '');
+
+	RETURN _new;
+END
+$BODY$;
+
+GRANT EXECUTE ON FUNCTION _api.trim(TEXT)
+	TO rest_anon, rest_user;
diff --git a/src/db/rest/util/_api_validate_text.sql b/src/db/rest/util/_api_validate_text.sql
new file mode 100644
index 0000000..ff3a227
--- /dev/null
+++ b/src/db/rest/util/_api_validate_text.sql
@@ -0,0 +1,53 @@
+CREATE FUNCTION _api.validate_text(
+	_column TEXT DEFAULT '',
+	_text TEXT DEFAULT NULL,
+	_min INTEGER DEFAULT NULL,
+	_max INTEGER DEFAULT NULL,
+	_nullable BOOLEAN DEFAULT FALSE
+)
+RETURNS BOOLEAN
+LANGUAGE plpgsql VOLATILE
+AS $BODY$
+DECLARE
+	_length INTEGER;
+BEGIN
+
+	-- make sure that text can only be null
+	-- when we allow it
+	IF _text IS NULL AND NOT _nullable THEN
+		PERFORM _api.raise(
+			_msg => 'api_null_value',
+			_detail => _column
+		);
+	END IF;
+
+	IF _text IS NULL THEN
+		RETURN TRUE;
+	END IF;
+
+	_length = LENGTH(_text);
+
+	IF _min IS NOT NULL AND _length < _min THEN
+		PERFORM _api.raise(
+			_msg => 'api_min_value',
+			_detail => _column,
+			_hint => _min || ''
+		);
+		RETURN FALSE;
+	END IF;
+
+	IF _max IS NOT NULL AND _length > _max THEN
+		PERFORM _api.raise(
+			_msg => 'api_max_value',
+			_detail => _column,
+			_hint => _max || ''
+		);
+		RETURN FALSE;
+	END IF;
+
+	RETURN TRUE;
+END
+$BODY$;
+
+GRANT EXECUTE ON FUNCTION _api.validate_text(TEXT, TEXT, INTEGER, INTEGER, BOOLEAN)
+	TO rest_anon, rest_user;