login and register, liking on homepage

26 files changed, 0 insertions, 1096 deletions

diff --git a/db/rest/comment/api_comment.sql b/db/rest/comment/api_comment.sql
deleted file mode 100644
index e50ca2f..0000000
--- a/db/rest/comment/api_comment.sql
+++ /dev/null
@@ -1,15 +0,0 @@
-CREATE VIEW api.comment AS
-	SELECT
-		c.id,
-		c.user_id,
-		c.post_id,
-		c.content,
-		c.date
-	FROM
-		admin.comment c
-	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/db/rest/comment/api_comment_delete.sql b/db/rest/comment/api_comment_delete.sql
deleted file mode 100644
index d7db8a4..0000000
--- a/db/rest/comment/api_comment_delete.sql
+++ /dev/null
@@ -1,31 +0,0 @@
-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;
-
-	DELETE FROM admin.comment
-	WHERE user_id = _user_id
-	AND 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 DELETE 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/db/rest/comment/api_comment_insert.sql b/db/rest/comment/api_comment_insert.sql
deleted file mode 100644
index 878e194..0000000
--- a/db/rest/comment/api_comment_insert.sql
+++ /dev/null
@@ -1,56 +0,0 @@
-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
-	);
-
-	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/db/rest/comment/api_comment_update.sql b/db/rest/comment/api_comment_update.sql
deleted file mode 100644
index d6b4aca..0000000
--- a/db/rest/comment/api_comment_update.sql
+++ /dev/null
@@ -1,50 +0,0 @@
-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
-			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/db/rest/login/_api_sign_jwt.sql b/db/rest/login/_api_sign_jwt.sql
deleted file mode 100644
index dc8e920..0000000
--- a/db/rest/login/_api_sign_jwt.sql
+++ /dev/null
@@ -1,33 +0,0 @@
-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/db/rest/login/_api_validate_role.sql b/db/rest/login/_api_validate_role.sql
deleted file mode 100644
index 9f1e54f..0000000
--- a/db/rest/login/_api_validate_role.sql
+++ /dev/null
@@ -1,30 +0,0 @@
-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/db/rest/login/_api_verify_jwt.sql b/db/rest/login/_api_verify_jwt.sql
deleted file mode 100644
index f5a6daf..0000000
--- a/db/rest/login/_api_verify_jwt.sql
+++ /dev/null
@@ -1,38 +0,0 @@
-CREATE FUNCTION _api.verify_jwt(
-	_token TEXT
-)
-RETURNS INTEGER
-LANGUAGE plpgsql VOLATILE
-AS $BODY$
-DECLARE
-	_payload JSON;
-	_valid BOOLEAN;
-	_jwt_secret TEXT;
-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;
-
-	RETURN _payload->>'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;
diff --git a/db/rest/login/api_login.sql b/db/rest/login/api_login.sql
deleted file mode 100644
index 0cf0535..0000000
--- a/db/rest/login/api_login.sql
+++ /dev/null
@@ -1,41 +0,0 @@
-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/db/rest/post/api_post.sql b/db/rest/post/api_post.sql
deleted file mode 100644
index 375f292..0000000
--- a/db/rest/post/api_post.sql
+++ /dev/null
@@ -1,25 +0,0 @@
-CREATE VIEW api.post AS
-	SELECT
-		p.id,
-		p.user_id,
-		p.content,
-		p.date,
-		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
-	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/db/rest/post/api_post_delete.sql b/db/rest/post/api_post_delete.sql
deleted file mode 100644
index e3dec55..0000000
--- a/db/rest/post/api_post_delete.sql
+++ /dev/null
@@ -1,31 +0,0 @@
-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;
-
-	DELETE FROM admin.post
-	WHERE user_id = _user_id
-	AND 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 DELETE 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/db/rest/post/api_post_insert.sql b/db/rest/post/api_post_insert.sql
deleted file mode 100644
index 8b2eb48..0000000
--- a/db/rest/post/api_post_insert.sql
+++ /dev/null
@@ -1,44 +0,0 @@
-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
-	);
-
-	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/db/rest/post/api_post_update.sql b/db/rest/post/api_post_update.sql
deleted file mode 100644
index 70230d0..0000000
--- a/db/rest/post/api_post_update.sql
+++ /dev/null
@@ -1,50 +0,0 @@
-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
-			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/db/rest/rest.sql b/db/rest/rest.sql
deleted file mode 100644
index 54f5118..0000000
--- a/db/rest/rest.sql
+++ /dev/null
@@ -1,50 +0,0 @@
-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;
-
--- 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/db/rest/user/api_avatar.sql b/db/rest/user/api_avatar.sql
deleted file mode 100644
index 981409f..0000000
--- a/db/rest/user/api_avatar.sql
+++ /dev/null
@@ -1,22 +0,0 @@
-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/db/rest/user/api_user.sql b/db/rest/user/api_user.sql
deleted file mode 100644
index e45768a..0000000
--- a/db/rest/user/api_user.sql
+++ /dev/null
@@ -1,23 +0,0 @@
-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.join_date,
-		u.birth_date,
-		u.profile_avatar,
-		u.profile_banner,
-		u.profile_bio
-	FROM
-		admin.user u;
-
-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/db/rest/user/api_user_delete.sql b/db/rest/user/api_user_delete.sql
deleted file mode 100644
index 8d7d52f..0000000
--- a/db/rest/user/api_user_delete.sql
+++ /dev/null
@@ -1,30 +0,0 @@
-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;
-
-	DELETE FROM admin.user
-	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 DELETE 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/db/rest/user/api_user_insert.sql b/db/rest/user/api_user_insert.sql
deleted file mode 100644
index 2297ecd..0000000
--- a/db/rest/user/api_user_insert.sql
+++ /dev/null
@@ -1,128 +0,0 @@
-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
-	);
-
-	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/db/rest/user/api_user_update.sql b/db/rest/user/api_user_update.sql
deleted file mode 100644
index 28e4368..0000000
--- a/db/rest/user/api_user_update.sql
+++ /dev/null
@@ -1,167 +0,0 @@
-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
-			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/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/rest/util/_api_raise.sql b/db/rest/util/_api_raise.sql
deleted file mode 100644
index 5c740c6..0000000
--- a/db/rest/util/_api_raise.sql
+++ /dev/null
@@ -1,50 +0,0 @@
-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/db/rest/util/_api_raise_deny.sql b/db/rest/util/_api_raise_deny.sql
deleted file mode 100644
index 17406b7..0000000
--- a/db/rest/util/_api_raise_deny.sql
+++ /dev/null
@@ -1,16 +0,0 @@
-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/db/rest/util/_api_raise_null.sql b/db/rest/util/_api_raise_null.sql
deleted file mode 100644
index be6ee29..0000000
--- a/db/rest/util/_api_raise_null.sql
+++ /dev/null
@@ -1,18 +0,0 @@
-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/db/rest/util/_api_raise_unique.sql b/db/rest/util/_api_raise_unique.sql
deleted file mode 100644
index a18d960..0000000
--- a/db/rest/util/_api_raise_unique.sql
+++ /dev/null
@@ -1,18 +0,0 @@
-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/db/rest/util/_api_serve_media.sql b/db/rest/util/_api_serve_media.sql
deleted file mode 100644
index 8b0f0b8..0000000
--- a/db/rest/util/_api_serve_media.sql
+++ /dev/null
@@ -1,41 +0,0 @@
-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/db/rest/util/_api_trim.sql b/db/rest/util/_api_trim.sql
deleted file mode 100644
index c972282..0000000
--- a/db/rest/util/_api_trim.sql
+++ /dev/null
@@ -1,25 +0,0 @@
-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/db/rest/util/_api_validate_text.sql b/db/rest/util/_api_validate_text.sql
deleted file mode 100644
index ff3a227..0000000
--- a/db/rest/util/_api_validate_text.sql
+++ /dev/null
@@ -1,53 +0,0 @@
-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;