CMS Users & Authentication

Overview

This domain has three tables: user holds CMS accounts (scoped per organization), password_history stores hashed passwords with their expiry dates (the user table has no password column), and forgot_password stores the reset-link codes with their expiry.

Table user

Column	Type	Nullable	Default	Description
id	INTEGER	NOT NULL	nextval (serial)	Primary key of the user
organization_id	INTEGER	NOT NULL	–	Organization the user belongs to (FK to organization.id)
email	TEXT	NOT NULL	–	Login email address
avatar	TEXT	NULL	–	URL/path of the profile picture
role_id	INTEGER	NOT NULL	0	User role (FK to system_role.id)
firstname	TEXT	NOT NULL	–	First name
lastname	TEXT	NOT NULL	–	Last name
type	UserType (enum)	NOT NULL	–	User type: onemoby (platform team) or customer
status	CommonStatus (enum)	NOT NULL	–	Account status (active, inactive, delete, etc.)
created_date	TIMESTAMPTZ(3)	NOT NULL	CURRENT_TIMESTAMP	Account creation timestamp
created_by	INTEGER	NOT NULL	0	User id of the creator
updated_date	TIMESTAMPTZ(3)	NULL	–	Last update timestamp (Prisma @updatedAt)
updated_by	INTEGER	NULL	0	User id of the last editor
deleted_date	TIMESTAMPTZ(3)	NULL	–	Soft-delete timestamp (NULL = not deleted)
verification_token	VARCHAR(64)	NULL	–	Token for email verification / first password setup (present in the live DB but not yet tracked in Prisma)

Table password_history

Column	Type	Nullable	Default	Description
id	INTEGER	NOT NULL	nextval (serial)	Sequence number
user_id	INTEGER	NOT NULL	0	User id — references user.id
password	VARCHAR(255)	NOT NULL	–	Password (stored hashed/encrypted)
created_date	TIMESTAMPTZ(3)	NOT NULL	CURRENT_TIMESTAMP	Creation timestamp
expired_date	TIMESTAMPTZ(3)	NOT NULL	–	Date the password expires

Table forgot_password

Column	Type	Nullable	Default	Description
id	INTEGER	NOT NULL	nextval (serial)	Sequence number
code	VARCHAR(50)	NOT NULL	–	Code used to validate that a password-reset URL is legitimate
user_id	INTEGER	NOT NULL	0	User id — references user.id
created_date	TIMESTAMPTZ(3)	NOT NULL	CURRENT_TIMESTAMP	Creation timestamp
expired_date	TIMESTAMPTZ(3)	NOT NULL	–	Date the code expires
status	CommonStatus (enum)	NOT NULL	active	Status of the code (used/cancelled codes are no longer active)
deleted_date	TIMESTAMPTZ(3)	NULL	–	Soft-delete timestamp

Notes

• Foreign keys: user.organization_id -> organization.id, user.role_id -> system_role.id, password_history.user_id -> user.id, forgot_password.user_id -> user.id
• The user table has no password column. The active password is the most recent password_history row for that user, so this table serves as both current credential store and history (used to block password reuse) with expiry enforced via expired_date.
• user has an index on deleted_date.
• user is a PostgreSQL reserved word, so raw SQL must reference it as public."user".
• There is no user_line_oa table. The similarly named object is line_user_line_oa_id_idx, an index on the line_oa_id column of the line_user table — not a separate table.