users_schema_1.sql (15095B)
1 -- ============================================================ 2 -- USERS SCHEMA - USERS & AUTHENTICATION SERVICE 3 -- ============================================================ 4 -- Handles: User management, authentication, sessions, addresses 5 -- Schema: users 6 -- Port: 3004 7 8 -- Set search path to users schema 9 SET search_path TO users, public; 10 11 -- ============================================================ 12 -- USERS TABLE 13 -- ============================================================ 14 CREATE TABLE users ( 15 id SERIAL PRIMARY KEY, 16 uuid UUID NOT NULL DEFAULT uuid_generate_v4() UNIQUE, 17 18 -- Authentication 19 email VARCHAR(255) NOT NULL UNIQUE, 20 password_hash VARCHAR(255) NOT NULL, -- bcrypt hash 21 22 -- Personal info 23 first_name VARCHAR(100), 24 last_name VARCHAR(100), 25 phone VARCHAR(50), 26 27 -- Account status 28 is_active BOOLEAN DEFAULT true, 29 is_verified BOOLEAN DEFAULT false, 30 31 -- Verification 32 email_verification_token VARCHAR(255), 33 email_verified_at TIMESTAMP WITH TIME ZONE, 34 35 -- Password reset 36 password_reset_token VARCHAR(255), 37 password_reset_expires_at TIMESTAMP WITH TIME ZONE, 38 39 -- Timestamps 40 last_login_at TIMESTAMP WITH TIME ZONE, 41 created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP, 42 updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP 43 ); 44 45 CREATE INDEX idx_users_uuid ON users(uuid); 46 CREATE INDEX idx_users_email ON users(email); 47 CREATE INDEX idx_users_is_active ON users(is_active); 48 CREATE INDEX idx_users_email_verification_token ON users(email_verification_token); 49 CREATE INDEX idx_users_password_reset_token ON users(password_reset_token); 50 51 COMMENT ON TABLE users IS 'Registered user accounts'; 52 COMMENT ON COLUMN users.uuid IS 'External UUID (exposed as eid in API)'; 53 COMMENT ON COLUMN users.password_hash IS 'Hashed password using bcrypt'; 54 55 -- ============================================================ 56 -- USER_SESSIONS TABLE 57 -- ============================================================ 58 CREATE TABLE user_sessions ( 59 id SERIAL PRIMARY KEY, 60 uuid UUID NOT NULL DEFAULT uuid_generate_v4() UNIQUE, 61 62 -- User reference 63 user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE, 64 65 -- Session data 66 session_token VARCHAR(255) NOT NULL UNIQUE, 67 refresh_token VARCHAR(255) UNIQUE, 68 69 -- Session lifecycle 70 expires_at TIMESTAMP WITH TIME ZONE NOT NULL, 71 last_activity_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP, 72 73 -- Status 74 is_active BOOLEAN DEFAULT true, 75 76 -- Timestamps 77 created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP, 78 updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP 79 ); 80 81 CREATE INDEX idx_user_sessions_uuid ON user_sessions(uuid); 82 CREATE INDEX idx_user_sessions_user_id ON user_sessions(user_id); 83 CREATE INDEX idx_user_sessions_session_token ON user_sessions(session_token); 84 CREATE INDEX idx_user_sessions_is_active ON user_sessions(is_active); 85 CREATE INDEX idx_user_sessions_expires_at ON user_sessions(expires_at); 86 87 COMMENT ON TABLE user_sessions IS 'Active user sessions for authentication'; 88 COMMENT ON COLUMN user_sessions.session_token IS 'JWT or session token'; 89 COMMENT ON COLUMN user_sessions.refresh_token IS 'Token for refresh'; 90 91 -- ============================================================ 92 -- USER_ADDRESSES TABLE 93 -- ============================================================ 94 CREATE TABLE user_addresses ( 95 id SERIAL PRIMARY KEY, 96 uuid UUID NOT NULL DEFAULT uuid_generate_v4() UNIQUE, 97 98 -- User reference 99 user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE, 100 101 -- Address type 102 address_type VARCHAR(50) DEFAULT 'shipping', -- 'shipping', 'billing', 'both' 103 address_label VARCHAR(100), -- 'Home', 'Work', etc. 104 105 -- Recipient 106 first_name VARCHAR(100) NOT NULL, 107 last_name VARCHAR(100) NOT NULL, 108 109 -- Address 110 address_line1 VARCHAR(255) NOT NULL, 111 address_line2 VARCHAR(255), 112 city VARCHAR(100) NOT NULL, 113 state VARCHAR(100) NOT NULL, 114 postal_code VARCHAR(20) NOT NULL, 115 country VARCHAR(100) NOT NULL DEFAULT 'US', 116 117 -- Contact 118 phone VARCHAR(50), 119 120 -- Flags 121 is_default BOOLEAN DEFAULT false, 122 is_active BOOLEAN DEFAULT true, 123 124 -- Timestamps 125 created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP, 126 updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP 127 ); 128 129 CREATE INDEX idx_user_addresses_uuid ON user_addresses(uuid); 130 CREATE INDEX idx_user_addresses_user_id ON user_addresses(user_id); 131 CREATE INDEX idx_user_addresses_address_type ON user_addresses(address_type); 132 CREATE INDEX idx_user_addresses_is_default ON user_addresses(is_default); 133 134 COMMENT ON TABLE user_addresses IS 'User saved addresses'; 135 COMMENT ON COLUMN user_addresses.is_default IS 'Default address for this type'; 136 137 -- ============================================================ 138 -- USER_PROFILES TABLE 139 -- ============================================================ 140 CREATE TABLE user_profiles ( 141 id SERIAL PRIMARY KEY, 142 uuid UUID NOT NULL DEFAULT uuid_generate_v4() UNIQUE, 143 144 -- User reference (one-to-one) 145 user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE UNIQUE, 146 147 -- Profile information 148 avatar_url TEXT, 149 date_of_birth DATE, 150 151 -- Preferences 152 email_notifications BOOLEAN DEFAULT true, 153 marketing_emails BOOLEAN DEFAULT false, 154 155 -- Timestamps 156 created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP, 157 updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP 158 ); 159 160 CREATE INDEX idx_user_profiles_uuid ON user_profiles(uuid); 161 CREATE INDEX idx_user_profiles_user_id ON user_profiles(user_id); 162 163 COMMENT ON TABLE user_profiles IS 'User profile information and preferences'; 164 165 -- ============================================================ 166 -- GUEST_USERS TABLE 167 -- ============================================================ 168 CREATE TABLE guest_users ( 169 id SERIAL PRIMARY KEY, 170 uuid UUID NOT NULL DEFAULT uuid_generate_v4() UNIQUE, 171 172 -- Guest identification 173 email VARCHAR(255) NOT NULL, 174 phone VARCHAR(50), 175 176 -- Conversion tracking 177 converted_to_user_id INTEGER REFERENCES users(id) ON DELETE SET NULL, 178 converted_at TIMESTAMP WITH TIME ZONE, 179 180 -- Timestamps 181 created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP, 182 updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP 183 ); 184 185 CREATE INDEX idx_guest_users_uuid ON guest_users(uuid); 186 CREATE INDEX idx_guest_users_email ON guest_users(email); 187 CREATE INDEX idx_guest_users_converted_to_user_id ON guest_users(converted_to_user_id); 188 189 COMMENT ON TABLE guest_users IS 'Guest users who can later register'; 190 COMMENT ON COLUMN guest_users.converted_to_user_id IS 'User ID if guest registered'; 191 192 -- ============================================================ 193 -- TRIGGERS FOR UPDATED_AT 194 -- ============================================================ 195 196 CREATE OR REPLACE FUNCTION update_updated_at_column() 197 RETURNS TRIGGER AS $$ 198 BEGIN 199 NEW.updated_at = CURRENT_TIMESTAMP; 200 RETURN NEW; 201 END; 202 $$ language 'plpgsql'; 203 204 CREATE TRIGGER update_users_updated_at BEFORE UPDATE ON users 205 FOR EACH ROW EXECUTE FUNCTION update_updated_at_column(); 206 207 CREATE TRIGGER update_user_sessions_updated_at BEFORE UPDATE ON user_sessions 208 FOR EACH ROW EXECUTE FUNCTION update_updated_at_column(); 209 210 CREATE TRIGGER update_user_addresses_updated_at BEFORE UPDATE ON user_addresses 211 FOR EACH ROW EXECUTE FUNCTION update_updated_at_column(); 212 213 CREATE TRIGGER update_user_profiles_updated_at BEFORE UPDATE ON user_profiles 214 FOR EACH ROW EXECUTE FUNCTION update_updated_at_column(); 215 216 CREATE TRIGGER update_guest_users_updated_at BEFORE UPDATE ON guest_users 217 FOR EACH ROW EXECUTE FUNCTION update_updated_at_column(); 218 219 -- ============================================================ 220 -- TRIGGER: Ensure only one default address per user per type 221 -- ============================================================ 222 223 CREATE OR REPLACE FUNCTION ensure_single_default_address() 224 RETURNS TRIGGER AS $$ 225 BEGIN 226 IF NEW.is_default = true THEN 227 -- Unset other default addresses of the same type for this user 228 UPDATE user_addresses 229 SET is_default = false 230 WHERE user_id = NEW.user_id 231 AND address_type = NEW.address_type 232 AND id != NEW.id 233 AND is_default = true; 234 END IF; 235 RETURN NEW; 236 END; 237 $$ language 'plpgsql'; 238 239 CREATE TRIGGER ensure_single_default_address_trigger 240 BEFORE INSERT OR UPDATE ON user_addresses 241 FOR EACH ROW 242 WHEN (NEW.is_default = true) 243 EXECUTE FUNCTION ensure_single_default_address(); 244 245 -- ============================================================ 246 -- FUNCTION: Generate session token 247 -- ============================================================ 248 249 CREATE OR REPLACE FUNCTION generate_session_token() 250 RETURNS TEXT AS $$ 251 BEGIN 252 RETURN 'SES-' || 253 EXTRACT(EPOCH FROM CURRENT_TIMESTAMP)::BIGINT::TEXT || '-' || 254 SUBSTRING(MD5(RANDOM()::TEXT), 1, 32); 255 END; 256 $$ LANGUAGE plpgsql; 257 258 COMMENT ON FUNCTION generate_session_token IS 'Generate unique session token'; 259 260 -- ============================================================ 261 -- FUNCTION: Revoke user session 262 -- ============================================================ 263 264 CREATE OR REPLACE FUNCTION revoke_user_session(p_session_token VARCHAR) 265 RETURNS BOOLEAN AS $$ 266 DECLARE 267 v_updated INTEGER; 268 BEGIN 269 UPDATE user_sessions 270 SET is_active = false 271 WHERE session_token = p_session_token 272 AND is_active = true; 273 274 GET DIAGNOSTICS v_updated = ROW_COUNT; 275 RETURN v_updated > 0; 276 END; 277 $$ LANGUAGE plpgsql; 278 279 COMMENT ON FUNCTION revoke_user_session IS 'Revoke a session (logout)'; 280 281 -- ============================================================ 282 -- FUNCTION: Convert guest to registered user 283 -- ============================================================ 284 285 CREATE OR REPLACE FUNCTION convert_guest_to_user( 286 p_guest_uuid UUID, 287 p_user_id INTEGER 288 ) 289 RETURNS BOOLEAN AS $$ 290 DECLARE 291 v_updated INTEGER; 292 BEGIN 293 UPDATE guest_users 294 SET converted_to_user_id = p_user_id, 295 converted_at = CURRENT_TIMESTAMP 296 WHERE uuid = p_guest_uuid 297 AND converted_to_user_id IS NULL; 298 299 GET DIAGNOSTICS v_updated = ROW_COUNT; 300 RETURN v_updated > 0; 301 END; 302 $$ LANGUAGE plpgsql; 303 304 COMMENT ON FUNCTION convert_guest_to_user IS 'Mark guest as converted to registered user'; 305 306 -- ============================================================ 307 -- VIEWS 308 -- ============================================================ 309 310 -- Users with default addresses 311 CREATE VIEW v_users_with_addresses AS 312 SELECT 313 u.id, 314 u.uuid as eid, 315 u.email, 316 u.first_name, 317 u.last_name, 318 u.phone, 319 u.is_verified, 320 u.last_login_at, 321 322 -- Default shipping address (as JSON) 323 ( 324 SELECT jsonb_build_object( 325 'eid', ua.uuid, 326 'address_label', ua.address_label, 327 'first_name', ua.first_name, 328 'last_name', ua.last_name, 329 'address_line1', ua.address_line1, 330 'address_line2', ua.address_line2, 331 'city', ua.city, 332 'state', ua.state, 333 'postal_code', ua.postal_code, 334 'country', ua.country, 335 'phone', ua.phone 336 ) 337 FROM user_addresses ua 338 WHERE ua.user_id = u.id 339 AND ua.address_type IN ('shipping', 'both') 340 AND ua.is_default = true 341 AND ua.is_active = true 342 LIMIT 1 343 ) as default_shipping_address, 344 345 -- Default billing address (as JSON) 346 ( 347 SELECT jsonb_build_object( 348 'eid', ua.uuid, 349 'address_label', ua.address_label, 350 'first_name', ua.first_name, 351 'last_name', ua.last_name, 352 'address_line1', ua.address_line1, 353 'address_line2', ua.address_line2, 354 'city', ua.city, 355 'state', ua.state, 356 'postal_code', ua.postal_code, 357 'country', ua.country, 358 'phone', ua.phone 359 ) 360 FROM user_addresses ua 361 WHERE ua.user_id = u.id 362 AND ua.address_type IN ('billing', 'both') 363 AND ua.is_default = true 364 AND ua.is_active = true 365 LIMIT 1 366 ) as default_billing_address, 367 368 u.created_at 369 FROM users u 370 WHERE u.is_active = true; 371 372 -- Guest users with conversion status 373 CREATE VIEW v_guest_users AS 374 SELECT 375 g.uuid as eid, 376 g.email, 377 g.phone, 378 g.converted_to_user_id IS NOT NULL as is_converted, 379 u.email as registered_email, 380 g.converted_at, 381 g.created_at 382 FROM guest_users g 383 LEFT JOIN users u ON g.converted_to_user_id = u.id; 384 385 COMMENT ON VIEW v_users_with_addresses IS 'Users with their default addresses'; 386 COMMENT ON VIEW v_guest_users IS 'Guest users with conversion status'; 387 388 -- ============================================================ 389 -- CONSTRAINTS & CHECKS 390 -- ============================================================ 391 392 -- Email format validation 393 ALTER TABLE users ADD CONSTRAINT check_email_format 394 CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'); 395 396 -- Password hash must not be empty 397 ALTER TABLE users ADD CONSTRAINT check_password_hash_not_empty 398 CHECK (LENGTH(password_hash) > 0); 399 400 -- Session token must not be empty 401 ALTER TABLE user_sessions ADD CONSTRAINT check_session_token_not_empty 402 CHECK (LENGTH(session_token) > 0); 403 404 -- ============================================================ 405 -- SAMPLE DATA (for testing) 406 -- ============================================================ 407 408 /* 409 -- Example: Create a registered user with profile and address 410 411 -- 1. Create user 412 INSERT INTO users ( 413 email, 414 password_hash, 415 first_name, 416 last_name, 417 phone, 418 is_verified 419 ) VALUES ( 420 'john.doe@example.com', 421 '$2b$12$LQv3c1yqBWVHxkd0LHAkCOYz6TtxMQJqhN8/LewY5GyYzpLaEiL4e', -- bcrypt hash 422 'John', 423 'Doe', 424 '+1-555-0100', 425 true 426 ) RETURNING id, uuid; 427 428 -- 2. Create profile 429 INSERT INTO user_profiles ( 430 user_id, 431 email_notifications 432 ) VALUES (1, true); 433 434 -- 3. Add shipping address 435 INSERT INTO user_addresses ( 436 user_id, 437 address_type, 438 address_label, 439 first_name, 440 last_name, 441 address_line1, 442 city, 443 state, 444 postal_code, 445 country, 446 phone, 447 is_default 448 ) VALUES ( 449 1, 450 'shipping', 451 'Home', 452 'John', 453 'Doe', 454 '123 Main St', 455 'New York', 456 'NY', 457 '10001', 458 'US', 459 '+1-555-0100', 460 true 461 ); 462 463 -- 4. Create session (login) 464 INSERT INTO user_sessions ( 465 user_id, 466 session_token, 467 expires_at 468 ) VALUES ( 469 1, 470 generate_session_token(), 471 CURRENT_TIMESTAMP + INTERVAL '7 days' 472 ); 473 474 -- 5. Logout (revoke session) 475 SELECT revoke_user_session('SES-...'); 476 477 -- Example: Guest user registration 478 479 -- 1. Create guest user (during guest checkout) 480 INSERT INTO guest_users (email, phone) 481 VALUES ('guest@example.com', '+1-555-0200') 482 RETURNING uuid; 483 484 -- 2. Later, guest decides to register 485 INSERT INTO users (email, password_hash, first_name, last_name, phone) 486 VALUES ('guest@example.com', 'hash...', 'Jane', 'Smith', '+1-555-0200') 487 RETURNING id; 488 489 -- 3. Link guest to registered user 490 SELECT convert_guest_to_user('guest-uuid', 2); 491 */