Top questions/answers (sorted by a weird score):
SELECT 'question', id, (((select count(*) from question_rating where question_id = question.id and rate = 1) + 1.9208) / ((select count(*) from question_rating where question_id = question.id)) - 1.96 * SQRT(((select count(*) from question_rating where question_id = question.id and rate = 1) * (select count(*) from question_rating where question_id = question.id and rate = -1)) / ((select count(*) from question_rating where question_id = question.id)) + 0.9604) / ((select count(*) from question_rating where question_id = question.id))) / (1 + 3.8416 / ((select count(*) from question_rating where question_id = question.id))) as score FROM question WHERE (select count(*) from question_rating where question_id = question.id) > 0 union SELECT 'answer', id, (((select count(*) from answer_rating where answer_id = answer.id and rate = 1) + 1.9208) / ((select count(*) from answer_rating where answer_id = answer.id)) - 1.96 * SQRT(((select count(*) from answer_rating where answer_id = answer.id and rate = 1) * (select count(*) from answer_rating where answer_id = answer.id and rate = -1)) / ((select count(*) from answer_rating where answer_id = answer.id)) + 0.9604) / ((select count(*) from answer_rating where answer_id = answer.id))) / (1 + 3.8416 / ((select count(*) from answer_rating where answer_id = answer.id))) as score from answer where (select count(*) from answer_rating where answer_id = answer.id) > 0 ORDER BY score DESC;

New questions/answers (sorted by date):
select 'question', id, "date" from question union select 'answer', id, "date" from answer order by "date" desc;

Recommended questions/answers (sorted by the weird score, filter results to only show posts from people the member follows):
SELECT 'question', id, (((select count(*) from question_rating where question_id = question.id and rate = 1) + 1.9208) / ((select count(*) from question_rating where question_id = question.id)) - 1.96 * SQRT(((select count(*) from question_rating where question_id = question.id and rate = 1) * (select count(*) from question_rating where question_id = question.id and rate = -1)) / ((select count(*) from question_rating where question_id = question.id)) + 0.9604) / ((select count(*) from question_rating where question_id = question.id))) / (1 + 3.8416 / ((select count(*) from question_rating where question_id = question.id))) as score FROM question WHERE (select count(*) from question_rating where question_id = question.id) > 0 and question.author_id in (select following_id from follow_member where follower_id = $USER_ID) union SELECT 'answer', id, (((select count(*) from answer_rating where answer_id = answer.id and rate = 1) + 1.9208) / ((select count(*) from answer_rating where answer_id = answer.id)) - 1.96 * SQRT(((select count(*) from answer_rating where answer_id = answer.id and rate = 1) * (select count(*) from answer_rating where answer_id = answer.id and rate = -1)) / ((select count(*) from answer_rating where answer_id = answer.id)) + 0.9604) / ((select count(*) from answer_rating where answer_id = answer.id))) / (1 + 3.8416 / ((select count(*) from answer_rating where answer_id = answer.id))) as score from answer where (select count(*) from answer_rating where answer_id = answer.id) > 0 and answer.author_id in (select following_id from follow_member where follower_id = $USER_ID) ORDER BY score DESC;

Currently only have planned db tables clustering. Need anything else? Ask JCL

insert into ... values ...
no

INSERT INTO ... VALUES ...
yes

Checkout AWS S3 Buckets

In cases where report for a given (user, content) already exists, do something -> return a pretty error? If the error is in the regular Laravel format, front end is already showing it

Explore the possibility of not rendering content as HTML in question (but mostly answer) cards, but instead as pure text, as it doesn't look good. Example:

There is a quick easy way to do it, just pointing it out here so it's not forgotten.

Member

• New column: is_certified (not null, default false)
• Unique index on lower(username)

Topic

• Unique constraint on name

Needs to be built from scratch

Admin interface is done, only need to implement option for moderator (small popup when clicking in order to provide a reason and then send?)

Stuff to do

High priority - stuff reused in most pages

• Finish navbar (add links to user dropdown, add search bar, add notifications preview example)
• Finish questions/answers cards
• Fix footer positioning and design - read here
• Finish 'add question' popup

Medium priority

• Dedicated sign up page
• Dedicated login page
• Dedicated about page
• Admin page - manage users and moderators
• Finish user page
• Finish search page(s)

Low priority

• Logged in version of feed
• Define primary and secondary color

Can't think of anything else right now. Feel free to add more stuff, and to create separate issues for any of these topics so we can discuss them

• check issue when the end of input is a mention
• check a notifications error when adding a comment -> might be on my end

Add option to close a question -> user chooses a question?

On pages with multiple requests made, make loader dissapear only when all requests are complete

Read https://github.com/axios/axios for info

Important pages:

• home
• profile (easier to test)
• question page (?)

Main ones:
Username - /^[a-zA-Z][\w-]{1,18}(?![-_])\w$/ (2-20 chars, must start with letter, can contain - or _ but not end with them)
Name = /^[a-z\s]+$/i
Password - /^(?=.*\d)(?=.*[a-zA-Z])(?=.*["-_?!@#+*$%&/()=])["\w\-?!@#+*$%&/()=]{8,32}$/ (8-32 chars, must contain at least one of each: number, letter, special char - ";-_?!@#+*$%&/()=)
Email - Usage of input type=email should be enough

Adding as necessary

Report is sent as notification to all moderators (pop up when clicking to report so as to be able to provide a reason for the report)

Similar to the follow/unfollow mechanism

VERY IMPORTANT: Confirmation pop up before deleting so user can confirm his action!