PostgreSQL API Documentation

User Schema

User Schema will contain all tables, functions and views related to user accounts and information.

All timestamp are aligned to GMT+8 timezone.

Tables

accounts

Table where each row represents a user account.

Data Type	Column Name	Description
int	id	Primary Key
varchar(64)	username	Unique
varchar(64)	display_name	Default to username column through a trigger function
varchar	email	Unique
BYTEA	hashed_password	Encrypted with bcrypt on NodeJS
timestamp	created_at	Default to current timestamp

account_sessions

Table where each row stores a session token tied to a user's account.

Data Type	Column Name	Description
int	id	Primary Key
int	account_id	Foreign Key (References accounts)
varchar	session_token	Unique
text	browser_info
timestamp	expiry_time	Defaults to 30 days after row insertion
timestamp	created_at	Defaults to current timestamp
timestamp	last_used	Defaults to current timestamp

Functions

check_user_availability -> TEXT

Parameter Index	Parameter Name	Parameter Type	Description
1	username	TEXT
2	email	TEXT

Use this function to check if a username or email is already taken in the database.

Useful for registration

Returns: A message based on the availability of the username/email:

• USERNAME, if the username is taken.
• EMAIL, if the email is already in use.
• OK, when the username and email is not taken.

create_account_session -> VARCHAR

Parameter Index	Parameter Name	Parameter Type	Description
1	username	text
2	browser_info	TEXT	Information of the user's current browser

Use this function to create a new account session token, useful for persistent login.

Returns: The session token itself.
Throws Exception when the corresponding account doesn't exist.

check_session_token -> (VARCHAR, INT, VARCHAR, VARCHAR, timestamp)

Parameter Index	Parameter Name	Parameter Type	Description
1	session_token	varchar
2	browser_info	TEXT	Information of the user's current browser

Use this function to fetch a user's account information based on a given session token and browser information.

Returns:

Name	Type	Description
message	VARCHAR	Message about the status of the function
account_id	INT
username	VARCHAR	Not to be confused with display_name
email	VARCHAR
created_at	timestamp	The date of when the account was created

The message status can be one of these:

• INVALID - The session token is invalid, it doesn't exists.
• EXPIRED - The session token is expired, the token is immediately dropped from the database!
• BROWSER - The browser doesn't match with the initial browser used to log in, the token is immediately dropped from the database!
• OK - All matches, the rest of the column data should be valid as well.

The BROWSER is there as a security layer.
If a user's session token got compromised and is attempted to be used in another browser, this security layer will stop the attacker from using the token.