DB

This is the database package, it is intended to have the schema definitions and handle the database migrations, we are using Drizzle ORM to handle all of these, please take a look at the documentations and current project files to check what we are doing currenttly.

Note: Please make sure that all schemas have thei respective relationship definitions

Lookup Table: UserTypes

Field	Type	Description	SQL Indicators
id	serial	Auto-incrementing primary key	Primary Key, Not Null
name	varchar(256)	Name of the user type	Not Null
description	text	Description of the user type	Not Null
isActive	boolean	If the user type is active	Not Null, Default True

Lookup Table: PaymentMethods

Field	Type	Description	SQL Indicators
id	serial	Auto-incrementing primary key	Primary Key, Not Null
name	varchar(256)	Name of the payment method	Not Null
description	text	Description of the payment method	Not Null
isActive	boolean	If the payment method is active	Not Null, Default True

Table: Users

Field	Type	Description	SQL Indicators
id	varchar(256)	Auto-incrementing primary key	Primary Key, Not Null
username	varchar(256)	User’s unique username	Not Null
email	varchar(256)	User’s email address
password	varchar(256)	User’s password	Not Null
name	varchar(256)	User’s name	Not Null
paternalLastName	varchar(256)	User’s paternal last name	Not Null
maternalLastName	varchar(256)	User’s maternal last name	Not Null
profilePhoto	varchar(256)	URL to user’s profile photo	Not Null
phone	varchar(256)	User’s phone number	Not Null
mobilePhone	varchar(256)	User’s mobile phone number	Not Null
phoneExtension	varchar(256)	Extension for the user’s phone number	Not Null
birthDate	timestamp	User’s date of birth	Not Null
address	varchar(256)	User’s address	Not Null
city	varchar(256)	City where the user lives	Not Null
state	varchar(256)	State where the user lives	Not Null
country	varchar(256)	Country where the user lives	Not Null
postalCode	varchar(256)	User’s postal code	Not Null
startDate	date	User’s start date	Not Null
userType	integer	User’s type	Not Null, Foreign Key
imssNumber	varchar(256)	User’s IMSS number	Not Null
imssDate	date	User’s IMSS date	Not Null
rfc	varchar(256)	User’s RFC	Not Null
emergencyContactName	varchar(256)	User’s emergency contact name	Not Null
emergencyContactPhone	varchar(256)	User’s emergency contact phone number	Not Null
emergencyContactRelationship	varchar(256)	User’s relationship with emergency contact	Not Null
createdAt	timestamp	Record creation date	Not Null, Default Now
updatedAt	timestamp	Record last update date	Not Null
createdBy	text	Record creator	Not Null
updatedBy	text	Record last updater
isActive	boolean	If the user is active	Not Null, Default True

Table: PaymentInfo

Field	Type	Description	SQL Indicators
id	serial	Auto-incrementing primary key	Primary Key, Not Null
userId	integer	ID of the user	Not Null, Foreign Key
weeklyWage	double precision	User’s weekly wage	Not Null
paymentMethod	integer	ID of the payment method	Not Null, Foreign Key
startDate	date	Start date of the payment	Not Null
nomipaqId	varchar(256)	Nomipaq ID	Not Null
deducibleAmount	double precision	Deducible amount	Not Null
ficticial	boolean	If the payment is ficticial	Not Null, Default False

Lookup Table: DiscountTypes

Field	Type	Description	SQL Indicators
id	serial	Auto-incrementing primary key	Primary Key, Not Null
name	varchar(256)	Name of the discount type	Not Null
description	text	Description of the discount type	Not Null
isActive	boolean	If the discount type is active	Not Null, Default True

Table: Discounts

Field	Type	Description	SQL Indicators
id	serial	Auto-incrementing primary key	Primary Key, Not Null
paymentInfoId	integer	ID of the payment info	Not Null, Foreign Key
discountType	integer	ID of the discount type	Not Null, Foreign Key
amount	double precision	Discount amount	Not Null
description	text	Description of the discount	Not Null
createdAt	timestamp	Record creation date	Not Null, Default Now
updatedAt	timestamp	Record last update date	Not Null
createdBy	text	Record creator	Not Null
updatedBy	text	Record last updater
isActive	boolean	If the discount is active	Not Null, Default True

Table: BankAccounts

Field	Type	Description	SQL Indicators
id	serial	Auto-incrementing primary key	Primary Key, Not Null
userId	integer	ID of the user	Not Null, Foreign Key
bankName	varchar(256)	Name of the bank	Not Null
accountNumber	varchar(256)	Bank account number	Not Null
clabe	varchar(256)	CLABE of the bank account	Not Null
createdAt	timestamp	Record creation date	Not Null, Default Now
updatedAt	timestamp	Record last update date	Not Null
createdBy	text	Record creator	Not Null
updatedBy	text	Record last updater
isActive	boolean	If the bank account is active	Not Null, Default True

Table: BonusCategories

Field	Type	Description	SQL Indicators
id	varchar(256)	Unique identifier of the bonus category	PK, Not Null
name	text	Name of the bonus category	Not Null
description	text	Description of the bonus category	Not Null
createdAt	timestamp	Record creation date	Default: Now, Not Null
updatedAt	timestamp	Record last update date	Not Null
isActive	boolean	Indicates if the bonus category is active	Default: true, Not Null
createdBy	text	User who created the record	Not Null
updatedBy	text	User who last updated the record

Table: Bonus

Column	Data Type	Description	SQL Indicators
id	varchar(256)	Unique identifier for the bonus.	PK, Not Null, Unique
name	text	Name of the bonus.	Not Null
description	text	Description of the bonus.	Not Null
amount	double precision	Amount of the bonus.	Not Null
categoryId	text	Identifier of the category that the bonus belongs to. It’s a foreign key referencing `bonusCategories.id`.	FK `bonusCategories.id`, Not Null, On Delete Restrict, On Update Cascade
createdAt	timestamp	Date and time when the bonus was created. It’s automatically set when creating the bonus.	Not Null, Default Now
updatedAt	timestamp	Date and time when the bonus was last updated.	Not Null
isActive	boolean	Indicates whether the bonus is active. Defaults to true.	Not Null, Default True
createdBy	text	User who created the bonus.	Not Null
updatedBy	text	User who last updated the bonus.

Lookup Table: LicenseTypes

Field	Type	Description	SQL Indicators
id	serial	Auto-incrementing primary key	Primary Key, Not Null
name	text	Name of the license type	Not Null
description	text	Description of the license type	Not Null
createdAt	timestamp	Record creation date	Not Null, Default Now
updatedAt	timestamp	Record last update date	Not Null
isActive	boolean	If the license type is active	Not Null, Default True
createdBy	integer	Record creator	Not Null
updatedBy	integer	Record last updater

Table: Licenses

Field	Type	Description	SQL Indicators
id	serial	Auto-incrementing primary key	Primary Key, Not Null
userId	integer	ID of the user	Not Null, Foreign Key
description	text	Description of the license	Not Null
expediture	timestamp	Expediture date of the license	Not Null
expiration	timestamp	Expiration date of the license	Not Null
licenseNumber	text	License number	Not Null
typeId	integer	ID of the license type	Not Null, Foreign Key
createdAt	timestamp	Record creation date	Not Null, Default Now
updatedAt	timestamp	Record last update date	Not Null
isActive	boolean	If the license is active	Not Null, Default True
createdBy	integer	Record creator	Not Null
updatedBy	integer	Record last updater

Table: AllowedConnexionTimes

Field	Type	Description	SQL Indicators
id	serial	Auto-incrementing primary key	Primary Key, Not Null
userId	integer	ID of the user	Not Null, Foreign Key
weekDay	integer	Week day	Not Null
startHour	integer	Start hour of the allowed connection	Not Null
endHour	integer	End hour of the allowed connection	Not Null
createdAt	timestamp	Record creation date	Not Null, Default Now
updatedAt	timestamp	Record last update date	Not Null
isActive	boolean	If the allowed connection time is active	Not Null, Default True
createdBy	integer	Record creator	Not Null
updatedBy	integer	Record last updater

Table: UserLogBook

Field	Type	Description	SQL Indicators
id	serial	Auto-incrementing primary key	Primary Key, Not Null
userId	integer	ID of the user	Not Null, Foreign Key
action	varchar(256)	Action performed by the user	Not Null
createdAt	timestamp	Record creation date	Not Null, Default Now

DB