# Database Schema Design
## Database: PostgreSQL 14+
### Core Tables
#### Users Table
```sql
CREATE TABLE users (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
username VARCHAR(50) UNIQUE NOT NULL,
email VARCHAR(255) UNIQUE NOT NULL,
password_hash VARCHAR(255) NOT NULL,
email_verified BOOLEAN DEFAULT FALSE,
is_active BOOLEAN DEFAULT TRUE,
role VARCHAR(20) DEFAULT 'user' CHECK (role IN ('user', 'admin', 'moderator')),
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
last_login TIMESTAMP WITH TIME ZONE
);
-- Indexes for performance
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_username ON users(username);
CREATE INDEX idx_users_active ON users(is_active) WHERE is_active = true;
```
#### User Sessions Table
```sql
CREATE TABLE user_sessions (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
token_hash VARCHAR(255) NOT NULL,
refresh_token_hash VARCHAR(255),
ip_address INET,
user_agent TEXT,
expires_at TIMESTAMP WITH TIME ZONE NOT NULL,
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
last_accessed TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
CREATE INDEX idx_sessions_user_id ON user_sessions(user_id);
CREATE INDEX idx_sessions_expires ON user_sessions(expires_at);
CREATE INDEX idx_sessions_token_hash ON user_sessions(token_hash);
```
#### Login Attempts Table (Rate Limiting)
```sql
CREATE TABLE login_attempts (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
email_or_username VARCHAR(255) NOT NULL,
ip_address INET NOT NULL,
success BOOLEAN NOT NULL,
attempted_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
user_agent TEXT
);
CREATE INDEX idx_login_attempts_email_time ON login_attempts(email_or_username, attempted_at);
CREATE INDEX idx_login_attempts_ip_time ON login_attempts(ip_address, attempted_at);
```