nick-doc/02 - Data Models/User.md

title, tags, aliases

title	tags	aliases
User	data-model postgres drizzle	User Model IUser Account

User

Last updated: 2026-06-06 — MongoDB fully removed; PostgreSQL + Drizzle is the only database layer (backend v2.9.12). Previous update: 2026-06-03 (dual-write status, guard role).

The core identity document for every actor in the marketplace: buyers, sellers, and admins. Stores credentials (password + WebAuthn passkeys), profile/preference data, referral bookkeeping, point balances, and a soft-delete status flag. Almost every other model carries a uuid (Postgres) reference back to User, so this table is the relational hub of the system.

[!info] Migration status: COMPLETE MongoDB and Mongoose have been fully removed from the backend runtime. PostgreSQL + Drizzle ORM is the sole database layer (19 migrations landed, 0000–0019, 32 tables). Repository: DrizzleUserRepo (returned exclusively by the repository factory) Postgres table: users — backend/src/db/schema/users.ts

---

ID Duality

Field	Storage	Purpose
id (PG column) / pgId (domain object)	uuid, PG primary key	Used for all marketplace foreign keys: offer.sellerId, purchaseRequest.buyerId, payment.buyerId/sellerId, etc.
legacy_object_id (PG column) / _id (domain/auth tokens)	text, 24-hex ObjectId string	Kept for backward compatibility with socket rooms (rooms keyed by legacy id) and auth tokens issued before migration. Partial-unique index WHERE NOT NULL.

[!warning] Always match marketplace FKs on pgId (UUID), not on legacy_object_id. Notifications and socket rooms use the legacy id string.

---

PostgreSQL Schema (Drizzle): users

[!note] Source backend/src/db/schema/users.ts

Columns

Column	PG Type	Nullable	Default	Notes
id	uuid	no	gen_random_uuid()	Primary key (pgId in domain objects)
legacy_object_id	text	yes	—	24-hex ObjectId string; partial-unique index WHERE NOT NULL; kept for socket rooms and legacy auth token compatibility
email	varchar(255)	yes	—	Partial-unique index WHERE NOT NULL
password	varchar(255)	yes	—	Hashed
first_name	text	yes	—	—
last_name	text	yes	—	—
role	user_role enum	no	buyer	Values: admin, buyer, seller, resolver, guard (added migration 0017)
is_email_verified	bool	yes	false	—
auth_provider	auth_provider enum	no	email	Values: email, google, telegram
telegram_verified	bool	yes	false	—
email_verification_token	text	yes	—	Legacy token flow
email_verification_code	text	yes	—	OTP code
email_verification_code_expires	timestamptz	yes	—	—
password_reset_token	text	yes	—	—
password_reset_expires	timestamptz	yes	—	—
password_reset_code	text	yes	—	—
password_reset_code_expires	timestamptz	yes	—	—
profile	jsonb	yes	—	Stores avatar, photoURL, phone, address, bio, website, walletAddress, walletType, walletProvider, walletProofVerified, walletProofTimestamp, isPublic
preferences	jsonb	yes	—	Stores language, currency, notifications.{email,sms,push}
status	user_status enum	yes	active	Values: active, suspended, deleted
last_login_at	timestamptz	yes	—	—
referral_code	varchar(255)	yes	—	Partial-unique index
referred_by_id	uuid	yes	—	Self-FK → users(id); index
points_total	int	yes	0	—
points_available	int	yes	0	—
points_used	int	yes	0	—
points_level	int	yes	1	Indexed
referral_stats_total	int	yes	0	—
referral_stats_active	int	yes	0	—
referral_stats_total_earned	int	yes	0	—
created_at	timestamptz	no	now()	—
updated_at	timestamptz	no	now()	—

Child Tables

user_passkeys — WebAuthn credentials:

Column	Type	Notes
id	text (PK)	WebAuthn credential ID
user_id	uuid FK→users CASCADE	Owner
public_key	text	Stored public key
counter	int	Signature counter
device_type	passkey_device_type enum	platform / cross-platform
device_name	text	Optional human label
created_at	timestamptz	—

user_refresh_tokens — Active JWT refresh tokens:

Column	Type	Notes
token	text (PK)	The refresh token string
user_id	uuid FK→users CASCADE	Owner

Indexes

Index	Type	Condition
users_email_unique	partial-unique	WHERE email IS NOT NULL
users_referral_code_unique	partial-unique	WHERE referral_code IS NOT NULL
users_legacy_object_id_unique	partial-unique	WHERE legacy_object_id IS NOT NULL
users_role_idx	btree	—
users_status_idx	btree	—
users_auth_provider_idx	btree	—
users_referral_code_idx	btree	—
users_referred_by_id_idx	btree	—
users_points_level_idx	btree	—

Relations

• Self-referential: referred_by_id → users.id (parent/children for referral tree)
• One-to-many: user_passkeys.user_id, user_refresh_tokens.user_id

---

Field Reference

[!note] Email change re-verification When a profile update (PUT /api/user/profile, userController.updateUserProfile) changes email to a new value, the controller sets isEmailVerified = false, generates a 6-digit emailVerificationCode (valid 15 minutes), stores it on emailVerificationCode / emailVerificationCodeExpires, and emails the code to the new address. The user must then confirm via POST /api/user/profile/email/verify (or request a new code with POST /api/user/profile/email/resend-verification).

[!note] Wallet ownership proof PATCH /api/user/wallet-address accepts both EVM and TON wallets. EVM addresses require an EIP-191 signature (ethers.verifyMessage); TON addresses are format-validated and may include an optional TonProof. A successful proof sets profile.walletProofVerified = true and profile.walletProofTimestamp.

Field (domain / camelCase)	PG Column	Notes
id / pgId	id (uuid PK)	Used for all marketplace FKs
_id / legacyObjectId	legacy_object_id	24-hex string; socket rooms + legacy auth tokens
email	email	Primary email login; nullable for Telegram-only accounts
password	password	Hashed; optional for passkey/Google/Telegram accounts
firstName	first_name	Persian default "کاربر"
lastName	last_name	Persian default "جدید"
role	role	enum: admin / buyer / seller / resolver / guard
isEmailVerified	is_email_verified	Reset to false on email change
authProvider	auth_provider	enum: email / google / telegram
telegramVerified	telegram_verified	Set after Telegram signature-verify + link
emailVerificationToken	email_verification_token	Legacy token flow
emailVerificationCode	email_verification_code	OTP code
emailVerificationCodeExpires	email_verification_code_expires	—
passwordResetToken	password_reset_token	Token for reset link flow
passwordResetExpires	password_reset_expires	—
passwordResetCode	password_reset_code	OTP reset code
passwordResetCodeExpires	password_reset_code_expires	—
passkeys[]	user_passkeys child table	WebAuthn credentials
passkeys[].id	user_passkeys.id	Credential ID (PK)
passkeys[].publicKey	user_passkeys.public_key	Stored public key
passkeys[].counter	user_passkeys.counter	Signature counter
passkeys[].deviceType	user_passkeys.device_type	enum: platform / cross-platform
passkeys[].deviceName	user_passkeys.device_name	Optional human label
passkeys[].createdAt	user_passkeys.created_at	Registration timestamp
profile.avatar	profile jsonb	Avatar URL
profile.photoURL	profile jsonb	Alternative photo URL
profile.phone	profile jsonb	Contact phone
profile.address.*	profile jsonb	street, city, state, zipCode, country
profile.bio	profile jsonb	Free-form bio
profile.website	profile jsonb	Personal website URL
profile.walletAddress	profile jsonb	EVM 0x… or TON address; set via PATCH /api/user/wallet-address
profile.walletType	profile jsonb	enum: evm / ton
profile.walletProvider	profile jsonb	e.g. evm, telegram-wallet
profile.walletProofVerified	profile jsonb	True when ownership proven (EIP-191 or TonProof)
profile.walletProofTimestamp	profile jsonb	Last verified timestamp
profile.isPublic	profile jsonb	Whether profile is publicly visible
preferences.language	preferences jsonb	UI language; default "en"
preferences.currency	preferences jsonb	Display currency; default "USD"
preferences.notifications.email	preferences jsonb	Opt-in email notifications; default true
preferences.notifications.sms	preferences jsonb	Opt-in SMS notifications; default false
preferences.notifications.push	preferences jsonb	Opt-in push notifications; default true
status	status	enum: active / suspended / deleted
lastLoginAt	last_login_at	Updated by auth middleware
refreshTokens[]	user_refresh_tokens child table	Active JWT refresh tokens; reset on password change/reset
referralCode	referral_code	Planned referral programme
referredBy	referred_by_id (uuid FK)	Planned referral programme
points.total	points_total	Planned loyalty system
points.available	points_available	Planned loyalty system
points.used	points_used	Planned loyalty system
points.level	points_level	Planned LevelConfig lookup
referralStats.totalReferrals	referral_stats_total	Planned
referralStats.activeReferrals	referral_stats_active	Planned
referralStats.totalEarned	referral_stats_total_earned	Planned
createdAt	created_at	Drizzle timestamp
updatedAt	updated_at	Drizzle timestamp

Computed / Virtual

Virtual	Returns	Notes
fullName	${firstName} ${lastName}	Computed in domain layer (was Mongoose virtual)

Serialisation

toJSON() strips password, refreshTokens, all emailVerification* and passwordReset* fields before serialisation.

---

Roles

Role	Added	Capabilities
admin	original	Full platform access
buyer	original	Place purchase requests, confirm delivery
seller	original	Submit offers, manage shop
resolver	commit fce8a19	View/resolve disputes; bypass chat membership checks; no other admin privileges
guard	migration 0017	Defined in user_role PG enum; purpose TBD

---

Relationships

• References: User (self, via referred_by_id).
• Referenced by: PurchaseRequest (buyerId, preferredSellerIds, deliveryInfo.deliveryCodeUsedBy, deliveryInfo.deliveryAttempts[].sellerId), SellerOffer (sellerId), Payment (buyerId, sellerId), Chat (participants[].userId, messages[].senderId, metadata.createdBy), Notification (userId as string), RequestTemplate (sellerId), Dispute (buyerId, sellerId, adminId), BlogPost (author.id), Address (userId), Review (sellerId, reviewerId), PointTransaction (user, referredUser), ShopSettings (sellerId).

State Transitions

stateDiagram-v2
    [*] --> active : signup verified
    active --> suspended : admin action
    suspended --> active : admin restore
    active --> deleted : self-delete
    suspended --> deleted : admin purge
    deleted --> [*]

Common Queries

-- Find by email (login)
SELECT * FROM users WHERE email = lower($1) AND email IS NOT NULL;

-- Active sellers
SELECT * FROM users WHERE role = 'seller' AND status = 'active';

-- Validate referral code
SELECT * FROM users WHERE referral_code = $1 AND referral_code IS NOT NULL;

-- Leaderboard by points
SELECT * FROM users WHERE status = 'active' ORDER BY points_total DESC LIMIT 10;

-- Promote level
UPDATE users SET points_level = $1, updated_at = now() WHERE id = $2;

-- Lookup by legacy ObjectId (socket rooms / auth token migration)
SELECT * FROM users WHERE legacy_object_id = $1;

Related: TempVerification, LevelConfig, PointTransaction, ShopSettings.