SQL Database Documentation

Service: Auth (PostgreSQL + SQLAlchemy)

Database Connection

• Database Type: PostgreSQL
• ORM: SQLAlchemy with Alembic for migrations
• Connection: Via SQLAlchemy engine with session management

---

Table: users

Field	Type	Required	Default	Unique	Description
id	Integer	Yes	Auto-increment	Yes	Primary key
username	String	Yes	-	Yes	Unique username for login
firstname	String	No	-	No	User's first name
lastname	String	No	-	No	User's last name
avatar	String	No	-	No	Profile avatar URL
email	String	No	-	No	User email address
phone_number	String	Yes	-	Yes	User phone number (unique)
password_hash	String	Yes	-	No	Hashed password
role	Enum	Yes	END_USER	No	User role (END_USER, OWNER, AGENT, BROKER, TC_ADMIN, OPT_USER)
country	String	No	-	No	User's country
city	String	No	-	No	User's city
zipcode	String	No	-	No	User's postal code
created_at	DateTime	Auto	now()	No	Creation timestamp with timezone
updated_at	DateTime	Auto	now()	No	Update timestamp with timezone

Primary Key: id

Indexes:

• ix_users_id (id)
• ix_users_username (username) - Unique
• ix_users_email (email) - Unique
• ix_users_phone_number (phone_number) - Unique

Enum Values - UserRole:

• END_USER: Regular end user
• OWNER: Property owner
• AGENT: Real estate agent
• BROKER: Real estate broker
• TC_ADMIN: Terms & Conditions admin
• OPT_USER: Operations user

---

Table: user_follows

Field	Type	Required	Default	Unique	Description
id	Integer	Yes	Auto-increment	Yes	Primary key
follower_id	String	Yes	-	No	Cognito user ID of follower
following_id	String	Yes	-	No	Cognito user ID of user being followed
created_at	DateTime	Auto	now()	No	Timestamp when follow relationship was created

Primary Key: id

Relations:

• References external Cognito users via follower_id and following_id
• Used to track follow relationships between users

Indexes:

• idx_follower_id (follower_id) - Find all users that a user is following
• idx_following_id (following_id) - Find all followers of a user
• idx_follower_following (follower_id, following_id) - Unique constraint, prevent duplicate follows

---

Table: user_follow_stats

Field	Type	Required	Default	Unique	Description
user_id	String	Yes	-	Yes	Cognito user ID (Primary Key)
followers_count	Integer	Yes	0	No	Number of users following this user
followings_count	Integer	Yes	0	No	Number of users this user is following
updated_at	DateTime	Auto	now()	No	Last update timestamp with timezone

Primary Key: user_id

Relations:

• References external Cognito users via user_id
• Aggregated statistics calculated from user_follows table

Note: This table stores denormalized follower/following counts for performance optimization

---

Migration History

Migration: 130a2475f44f (2025-05-06)

• Description: Add fullname and avatar columns to users
• Changes:
  • Added fullname column (String, nullable, indexed)
  • Added avatar column (LargeBinary, nullable)

Migration: b4c6717d0af8 (2025-05-27)

• Description: Refactor user model
• Changes:
  • Created UserRole enum (END_USER, OWNER, AGENT, BROKER, TC_ADMIN, OPT_USER)
  • Replaced fullname with firstname + lastname
  • Added phone_number (String, required, unique)
  • Added role (Enum, required, default: END_USER)
  • Added country, city, zipcode (String, nullable)
  • Added created_at, updated_at timestamps
  • Changed email to nullable
  • Updated indexes

Migration: 8d2c20ede788 (2025-05-27)

• Description: Update user table structure
• Additional user model refinements

Migration: 94a653acee01 (2025-06-20)

• Description: Create user follows system
• Changes:
  • Created user_follows table
  • Added follower_id, following_id relationship
  • Created indexes for follow queries
  • Added unique constraint to prevent duplicate follows

Migration: e67a8cedc427 (2025-06-20)

• Description: Create follow statistics table
• Changes:
  • Created user_follow_stats table
  • Added followers_count, followings_count counters
  • Added auto-updating timestamp

---

Database Features

Authentication & Authorization

• Password Storage: Bcrypt hashed passwords
• User Roles: Enum-based role system with 6 predefined roles
• External Integration: Cognito user ID references for follow system

Social Features

• Follow System: Users can follow/unfollow each other
• Statistics Tracking: Denormalized follower counts for performance
• Relationship Queries: Optimized indexes for social graph queries

Performance Optimizations

• Denormalized Stats: Separate stats table to avoid expensive aggregations
• Strategic Indexes: Covering indexes for common query patterns
• Timezone Awareness: All timestamps store timezone information

Data Integrity

• Unique Constraints: Username, email, phone_number uniqueness
• Foreign Key Logic: Relationship validation in application layer
• Enum Validation: Strict role validation at database level