Spaces:
Sleeping
Sleeping
| -- ============================================================= | |
| -- MODULE: Auth / Users | |
| -- File: docs/schema-auth.sql | |
| -- Managed by: TypeORM (synchronize: true in development) | |
| -- ============================================================= | |
| -- ----------------------------------------------------------- | |
| -- ENUMS | |
| -- ----------------------------------------------------------- | |
| -- No dedicated enums in auth module | |
| -- (Enums live in the onboarding module and are referenced here via FK) | |
| -- ----------------------------------------------------------- | |
| -- TABLE: user | |
| -- Entity: src/modules/users/user.entity.ts | |
| -- Description: Core identity record for every registered user. | |
| -- ----------------------------------------------------------- | |
| CREATE TABLE "user" ( | |
| -- Primary key | |
| id UUID PRIMARY KEY DEFAULT gen_random_uuid(), | |
| -- Identity | |
| email VARCHAR NOT NULL UNIQUE, | |
| password VARCHAR, -- NULL for Google OAuth users | |
| google_id VARCHAR UNIQUE, -- NULL for email/password users | |
| name VARCHAR NOT NULL, | |
| -- Email verification | |
| is_verified BOOLEAN NOT NULL DEFAULT FALSE, | |
| otp_hash VARCHAR, -- bcrypt hash of the 6-digit OTP | |
| otp_expiry TIMESTAMPTZ, | |
| -- Session / refresh tokens | |
| refresh_token_hash TEXT, -- bcrypt hash of refresh JWT | |
| refresh_token_issued_at TIMESTAMP, | |
| is_loggedin BOOLEAN NOT NULL DEFAULT FALSE, | |
| -- Password reset | |
| reset_token_hash TEXT, | |
| reset_token_expiry TIMESTAMP | |
| ); | |
| -- Indexes | |
| CREATE INDEX idx_user_email ON "user" (email); | |
| CREATE INDEX idx_user_google ON "user" (google_id); | |
| -- ----------------------------------------------------------- | |
| -- VARIABLE REFERENCE | |
| -- ----------------------------------------------------------- | |
| -- id → UUID, primary key, used in all FK relations | |
| -- email → Used for login, OTP sending, uniqueness check | |
| -- password → Bcrypt hashed; NULL when registered via Google | |
| -- google_id → OAuth2 Google sub claim; NULL for email users | |
| -- name → Display name shown in UI and auth/me response | |
| -- is_verified → Must be TRUE before JWT tokens are issued | |
| -- otp_hash → Hashed 6-digit code; checked at POST /auth/verify-otp | |
| -- otp_expiry → 10-minute TTL; checked at verify-otp endpoint | |
| -- refresh_token_hash → Stored so refresh tokens can be invalidated on logout | |
| -- refresh_token_issued_at → Used to detect refresh token reuse attacks | |
| -- is_loggedin → Set to FALSE on POST /auth/logout | |
| -- reset_token_hash → Hashed token sent via email for password reset | |
| -- reset_token_expiry → 1-hour TTL for password reset links | |