Skip to main content

​
Overview

The users table manages user authentication and login credentials for the NominaSoft system. It stores basic user profile information including username, password, email, and name.

​
Table Definition

CREATE TABLE IF NOT EXISTS users
(
    uid                 SERIAL          NOT NULL,
    username            VARCHAR         NOT NULL,
    password            VARCHAR         NOT NULL,
    email               VARCHAR         NOT NULL,
    name                VARCHAR         NOT NULL,
    PRIMARY KEY(uid)
);

​
Column Reference

​
uid
SERIAL
required
Unique identifier for each user (auto-incremented). Primary key.
​
username
VARCHAR
required
User’s login username. Must be unique for authentication.
​
password
VARCHAR
required
User’s password (should be hashed in application layer).
​
email
VARCHAR
required
User’s email address for notifications and recovery.
​
name
VARCHAR
required
User’s full name or display name.

​
Constraints

​
Primary Key

  • uid: Auto-incrementing unique identifier

​
Not Null Constraints

  • All columns are required (NOT NULL)

​
Usage Notes

The password field stores passwords. In production, ensure passwords are properly hashed using bcrypt, Argon2, or similar algorithms before storage.
This table does not include constraints for unique usernames or emails. Consider adding unique constraints in production:
ALTER TABLE users ADD CONSTRAINT unique_username UNIQUE (username);
ALTER TABLE users ADD CONSTRAINT unique_email UNIQUE (email);

​
Security Considerations

  1. Password Hashing: Always hash passwords before storing them
  2. Username Uniqueness: Implement unique constraint on username
  3. Email Validation: Validate email format in application layer
  4. Session Management: Implement secure session handling with uid

​
Example Queries

​
Insert New User

INSERT INTO users (username, password, email, name) 
VALUES ('jdoe', 'hashed_password_here', '[email protected]', 'John Doe');

​
Authenticate User

SELECT uid, name, email 
FROM users 
WHERE username = 'jdoe' AND password = 'hashed_password';

​
List All Users

SELECT uid, username, email, name 
FROM users 
ORDER BY name;

​
Update User Email

UPDATE users 
SET email = '[email protected]' 
WHERE uid = 1;
The users table is independent and does not have foreign key relationships with other tables in the payroll schema. It serves purely for authentication purposes.

​
Best Practices

  • Store hashed passwords only (never plain text)
  • Implement rate limiting for login attempts
  • Add created_at and updated_at timestamps
  • Consider adding role/permission fields for access control
  • Implement account status (active/inactive/locked)

Build docs developers (and LLMs) love

Get started for freeTalk to us