Database Schema
The friend system uses a simple relational database schema with tables for users, friendships, friend requests, blocked users, rooms, and invitations.
Entity Relationships
users 1────N friendships N────1 users
users 1────N friend_requests N────1 users
users 1────N blocked_users N────1 users
users 1────N rooms
users 1────N invitations N────1 users
Table Definitions
users
Stores user profiles and online status. Note: The users table is optional if using external user management.
| Column | Type | Constraints | Description |
|---|---|---|---|
| id | TEXT | PRIMARY KEY | Unique user identifier |
| username | TEXT | NOT NULL | Display name |
| status | TEXT | DEFAULT 'offline' | online, offline, away, in_room |
| last_seen | INTEGER | Unix timestamp of last activity | |
| created_at | INTEGER | NOT NULL | Unix timestamp |
friendships
Stores accepted friend relationships between users.
| Column | Type | Constraints | Description |
|---|---|---|---|
| id | INTEGER | PRIMARY KEY AUTOINCREMENT | |
| user_id | TEXT | NOT NULL, FK | First user |
| friend_id | TEXT | NOT NULL, FK | Second user (friend) |
| status | TEXT | DEFAULT 'active' | active, blocked |
| created_at | INTEGER | NOT NULL | Unix timestamp when friendship created |
friend_requests
Stores pending friend requests.
| Column | Type | Constraints | Description |
|---|---|---|---|
| id | TEXT | PRIMARY KEY | Unique request ID |
| sender_id | TEXT | NOT NULL, FK | User who sent the request |
| receiver_id | TEXT | NOT NULL, FK | User who received the request |
| status | TEXT | DEFAULT 'pending' | pending, accepted, rejected, cancelled |
| created_at | INTEGER | NOT NULL | Unix timestamp |
| updated_at | INTEGER | Unix timestamp of last change |
blocked_users
Stores user block entries for moderation.
| Column | Type | Constraints | Description |
|---|---|---|---|
| id | INTEGER | PRIMARY KEY AUTOINCREMENT | |
| user_id | TEXT | NOT NULL, FK | User who blocked |
| blocked_user_id | TEXT | NOT NULL, FK | User who was blocked |
| created_at | INTEGER | NOT NULL | Unix timestamp |
rooms
Stores game room/session information.
| Column | Type | Constraints | Description |
|---|---|---|---|
| id | TEXT | PRIMARY KEY | Unique room identifier |
| host_id | TEXT | NOT NULL, FK | User ID of room host |
| name | TEXT | Display name for room | |
| max_players | INTEGER | DEFAULT 0 | Maximum player capacity |
| current_players | INTEGER | DEFAULT 0 | Current player count |
| is_private | INTEGER | DEFAULT 0 | 1 if password protected |
| custom_properties | TEXT | JSON string of game-specific data | |
| created_at | INTEGER | NOT NULL | Unix timestamp |
invitations
Stores game invitations between friends.
| Column | Type | Constraints | Description |
|---|---|---|---|
| id | TEXT | PRIMARY KEY | Unique invitation ID |
| sender_id | TEXT | NOT NULL, FK | User who sent invitation |
| receiver_id | TEXT | NOT NULL, FK | User who received invitation |
| room_id | TEXT | FK | Room being invited to |
| message | TEXT | Personal message from sender | |
| status | TEXT | DEFAULT 'pending' | pending, accepted, declined, expired |
| created_at | INTEGER | NOT NULL | Unix timestamp |
| updated_at | INTEGER | Unix timestamp of last change |
SQL Schema File
The complete SQL schema is available at .Server/websocket/database/external_schema.sql. You can use this to:
- Set up the database manually
- Extend the schema with additional tables
- Create indexes for performance optimization