Database Schema

Database Schema

This document describes the database schema for the MatchBy application.

Overview

The database uses PostgreSQL with Entity Framework Core as the ORM. The schema follows a relational model with proper foreign key relationships and indexes.

ASP.NET Identity Tables

AspNetUsers (ApplicationUser)

Main user table that extends ASP.NET Core Identity's IdentityUser with additional properties.

Column	Type	Description
Id	string (PK)	User identifier
UserName	string	Username
NormalizedUserName	string	Normalized username (for case-insensitive lookups)
Email	string	Email address
NormalizedEmail	string	Normalized email (for case-insensitive lookups)
EmailConfirmed	bool	Email confirmation status
PasswordHash	string	Hashed password
SecurityStamp	string	Security stamp for token validation
ConcurrencyStamp	string	Concurrency token for optimistic locking
PhoneNumber	string?	Phone number
PhoneNumberConfirmed	bool	Phone number confirmation status
TwoFactorEnabled	bool	Two-factor authentication enabled
LockoutEnd	DateTime?	Lockout end timestamp
LockoutEnabled	bool	Lockout enabled flag
AccessFailedCount	int	Failed login attempt count
DisplayName	string	User's display name
PreferredSports	integer[]	Array of preferred sports (stored as PostgreSQL array)
BaseLocation_Latitude	double?	Base location latitude
BaseLocation_Longitude	double?	Base location longitude
BaseLocation_City	string?	Base location city
BaseLocation_Country	string?	Base location country
Bio	string?	User biography
Rating	float	Average player rating
Status	integer	Account status (enum: AccountStatus)
ProfileImage_Url	string?	Profile image URL
ProfileImage_ExpireDateTimeUtc	DateTime?	Profile image expiration
ProfileImage_Key	string?	S3 storage key
ProfileImage_FileCategory	integer?	File category (enum)
ProfileImage_FileType	integer?	File type (enum)
ProfileImage_CreatedAtUtc	DateTime?	Profile image creation timestamp
CreatedAtUtc	DateTime	Account creation timestamp
UpdatedAtUtc	DateTime?	Last update timestamp
DeletedAtUtc	DateTime?	Soft delete timestamp

Relationships:

• One-to-Many: Matches (as Creator)
• Many-to-Many: Matches (as Participant via MatchParticipants)
• One-to-Many: PlayerRatings (as Sender/SentById)
• One-to-Many: PlayerRatings (as Receiver/ReceivedById)
• One-to-Many: Friends (as Sender)
• One-to-Many: Friends (as Receiver)
• One-to-Many: AspNetUserClaims
• One-to-Many: AspNetUserLogins
• Many-to-Many: AspNetRoles (via AspNetUserRoles)
• One-to-Many: AspNetUserTokens

AspNetRoles

Role management table for ASP.NET Identity.

Column	Type	Description
Id	string (PK)	Role identifier
Name	string	Role name
NormalizedName	string	Normalized role name (for case-insensitive lookups)
ConcurrencyStamp	string	Concurrency token for optimistic locking

Relationships:

• One-to-Many: AspNetRoleClaims
• Many-to-Many: AspNetUsers (via AspNetUserRoles)

AspNetRoleClaims

Claims associated with roles.

Column	Type	Description
Id	integer (PK)	Claim identifier (auto-increment)
RoleId	string (FK)	Role identifier
ClaimType	string?	Claim type
ClaimValue	string?	Claim value

Relationships:

• Many-to-One: AspNetRoles

AspNetUserClaims

Claims associated with users.

Column	Type	Description
Id	integer (PK)	Claim identifier (auto-increment)
UserId	string (FK)	User identifier
ClaimType	string?	Claim type
ClaimValue	string?	Claim value

Relationships:

• Many-to-One: AspNetUsers

AspNetUserLogins

External login providers for users (e.g., Google, Facebook).

Column	Type	Description
LoginProvider	string (PK)	Login provider name
ProviderKey	string (PK)	Provider-specific key
ProviderDisplayName	string?	Display name for the provider
UserId	string (FK)	User identifier

Relationships:

• Many-to-One: AspNetUsers

AspNetUserRoles

Junction table for user-role relationships (many-to-many).

Column	Type	Description
UserId	string (PK, FK)	User identifier
RoleId	string (PK, FK)	Role identifier

Relationships:

• Many-to-One: AspNetUsers
• Many-to-One: AspNetRoles

AspNetUserTokens

Authentication tokens for users (e.g., password reset tokens, 2FA tokens).

Column	Type	Description
UserId	string (PK, FK)	User identifier
LoginProvider	string (PK)	Login provider name
Name	string (PK)	Token name
Value	string?	Token value

Relationships:

• Many-to-One: AspNetUsers

---

Application Tables

Match

Represents a sports match/game.

Column	Type	Description
Id	string (PK)	Match identifier
Location_Latitude	double	Match location latitude
Location_Longitude	double	Match location longitude
Location_City	string	Match location city
Location_Country	string	Match location country
Address	string	Physical address
MatchDateTimeUtc	DateTime	Scheduled match date and time
Description	string	Match description
minPlayers	integer	Minimum number of players required
maxPlayers	integer	Maximum number of players allowed
Sport	integer	Sport type (enum: Sports)
Status	integer	Match status (enum: MatchStatus)
Privacy	integer	Privacy level (enum: MatchPrivacy)
CreatorId	string (FK)	User who created the match
ConversationId	string?	Associated conversation/chat
CreatedAtUtc	DateTime	Match creation timestamp
UpdatedAtUtc	DateTime?	Last update timestamp
DeletedAtUtc	DateTime?	Soft delete timestamp

Relationships:

• Many-to-One: AspNetUsers (Creator)
• Many-to-Many: AspNetUsers (Participants via MatchParticipants)
• One-to-One: Conversation (optional)
• One-to-Many: MatchInvite
• One-to-Many: PlayerRatings

Team

Represents a team that can participate in matches.

Column	Type	Description
Id	string (PK)	Team identifier
Name	string	Team name
Description	string	Team description
OwnerId	string (FK)	User who owns/created the team
ConversationId	string?	Associated conversation/chat
CreatedAtUtc	DateTime	Team creation timestamp
UpdatedAtUtc	DateTime?	Last update timestamp
DeletedAtUtc	DateTime?	Soft delete timestamp

Relationships:

• Many-to-One: AspNetUsers (Owner)
• Many-to-Many: AspNetUsers (Members via TeamMembers)
• One-to-One: Conversation (optional)
• One-to-Many: TeamInvite

Conversation

Represents a chat conversation (can be associated with a match, team, or be private).

Column	Type	Description
Id	string (PK)	Conversation identifier
Type	integer	Conversation type (enum: ConversationType - Private, Team, Match)
Title	string?	Conversation title
Image_Url	string?	Conversation image URL
Image_ExpireDateTimeUtc	DateTime?	Image expiration
Image_Key	string?	S3 storage key
Image_FileCategory	integer?	File category (enum)
Image_FileType	integer?	File type (enum)
Image_CreatedAtUtc	DateTime?	Image creation timestamp
CreatorId	string (FK)	User who created the conversation
TeamId	string? (FK)	Associated team (if any)
MatchId	string? (FK)	Associated match (if any)
LastMessageContent	string?	Content of the last message
LastMessageAtUtc	DateTime?	Timestamp of the last message
CreatedAtUtc	DateTime	Conversation creation timestamp
UpdatedAtUtc	DateTime?	Last update timestamp
DeletedAtUtc	DateTime?	Soft delete timestamp

Relationships:

• Many-to-One: AspNetUsers (Creator)
• One-to-One: Match (optional)
• One-to-One: Team (optional)
• One-to-Many: ChatMessage
• Many-to-Many: AspNetUsers (Participants via ConversationParticipants)

ChatMessage

Represents a message in a conversation.

Column	Type	Description
Id	string (PK)	Message identifier
Content	string?	Message content (nullable for location messages)
SenderId	string (FK)	User who sent the message
Location_Latitude	double?	Location latitude (if message type is Location)
Location_Longitude	double?	Location longitude (if message type is Location)
Location_City	string?	Location city
Location_Country	string?	Location country
InviteUrl	string?	Invitation URL (if message contains invite)
ReplyToMessageId	string? (FK)	ID of message being replied to
ConversationId	string (FK)	Parent conversation
CreatedAtUtc	DateTime	Message creation timestamp
UpdatedAtUtc	DateTime?	Last update timestamp
DeletedAtUtc	DateTime?	Soft delete timestamp

Relationships:

• Many-to-One: Conversation
• Many-to-One: AspNetUsers (Sender)
• Many-to-One: ChatMessage (ReplyToMessage - self-referencing)

Friend

Represents a friendship relationship between users.

Column	Type	Description
Id	string (PK)	Friendship identifier
SenderId	string (FK)	User who sent the friend request
ReceiverId	string (FK)	User who received the friend request
CreatedAtUtc	DateTime	Friendship creation timestamp
UpdatedAtUtc	DateTime?	Last update timestamp
DeletedAtUtc	DateTime?	Soft delete timestamp

Relationships:

• Many-to-One: AspNetUsers (Sender)
• Many-to-One: AspNetUsers (Receiver)

PlayerRating

Represents a rating given by one user to another.

Column	Type	Description
Id	string (PK)	Rating identifier
SentById	string (FK)	User who gave the rating
ReceivedById	string (FK)	User who received the rating
MatchId	string (FK)	Associated match
Rating	float	Rating value (typically 1-5)
CreatedAtUtc	DateTime	Rating creation timestamp
UpdatedAtUtc	DateTime?	Last update timestamp
DeletedAtUtc	DateTime?	Soft delete timestamp

Relationships:

• Many-to-One: AspNetUsers (SentBy)
• Many-to-One: AspNetUsers (ReceivedBy)
• Many-to-One: Match

MatchInvite

Represents an invitation to join a match.

Column	Type	Description
Id	string (PK)	Invitation identifier
MatchId	string (FK)	Target match
Content	string	Invitation message content
SenderId	string (FK)	User who sent the invitation
ReceiverId	string (FK)	User who received the invitation
CreatedAtUtc	DateTime	Invitation creation timestamp
UpdatedAtUtc	DateTime?	Last update timestamp
DeletedAtUtc	DateTime?	Soft delete timestamp

Relationships:

• Many-to-One: Match
• Many-to-One: AspNetUsers (Sender)
• Many-to-One: AspNetUsers (Receiver)

TeamInvite

Represents an invitation to join a team.

Column	Type	Description
Id	string (PK)	Invitation identifier
TeamId	string (FK)	Target team
Content	string	Invitation message content
SenderId	string (FK)	User who sent the invitation
ReceiverId	string (FK)	User who received the invitation
CreatedAtUtc	DateTime	Invitation creation timestamp
UpdatedAtUtc	DateTime?	Last update timestamp
DeletedAtUtc	DateTime?	Soft delete timestamp

Relationships:

• Many-to-One: Team
• Many-to-One: AspNetUsers (Sender)
• Many-to-One: AspNetUsers (Receiver)

Enumerations

Sports

• Football
• Basketball
• Tennis
• Volleyball
• (Additional sports as needed)

MatchStatus

• Pending
• Confirmed
• InProgress
• Completed
• Cancelled

MatchPrivacy

• Public
• Private
• FriendsOnly

AccountStatus

• Active
• Suspended
• Deleted

MessageType

• Text
• Location
• Image
• (Additional types as needed)

MessageStatus

• Sent
• Delivered
• Read

FriendStatus

• Pending
• Accepted
• Declined
• Blocked

InviteStatus

• Pending
• Accepted
• Declined
• Expired

Complex Types (JSON)

Location

{
  "Latitude": 59.3293,
  "Longitude": 18.0686,
  "City": "Stockholm",
  "Country": "Sweden"
}

FileStore

{
  "Url": "https://...",
  "ExpireDateTimeUtc": "2025-12-31T23:59:59Z",
  "Key": "s3-key",
  "FileCategory": "ProfileImage",
  "FileType": "Image",
  "CreatedAtUtc": "2025-01-01T00:00:00Z"
}

Junction Tables

MatchParticipants

Junction table for many-to-many relationship between Matches and Users (participants).

Column	Type	Description
MatchId	string (PK, FK)	Match identifier
ParticipantsId	string (PK, FK)	User identifier

TeamMembers

Junction table for many-to-many relationship between Teams and Users (members).

Column	Type	Description
MembersId	string (PK, FK)	User identifier
TeamId	string (PK, FK)	Team identifier

ConversationParticipants

Junction table for many-to-many relationship between Conversations and Users (participants).

Column	Type	Description
ConversationId	string (PK, FK)	Conversation identifier
ParticipantsId	string (PK, FK)	User identifier

---

Indexes

The following indexes are created automatically by Entity Framework Core:

Primary Keys

• All tables have primary key indexes

Foreign Key Indexes

• IX_AspNetRoleClaims_RoleId on AspNetRoleClaims
• IX_AspNetUserClaims_UserId on AspNetUserClaims
• IX_AspNetUserLogins_UserId on AspNetUserLogins
• IX_AspNetUserRoles_RoleId on AspNetUserRoles
• IX_Matches_CreatorId on Matches
• IX_MatchInvites_MatchId, IX_MatchInvites_SenderId, IX_MatchInvites_ReceiverId on MatchInvites
• IX_MatchParticipants_ParticipantsId on MatchParticipants
• IX_PlayerRatings_MatchId, IX_PlayerRatings_SentById, IX_PlayerRatings_ReceivedById on PlayerRatings
• IX_Teams_OwnerId on Teams
• IX_TeamInvites_TeamId, IX_TeamInvites_SenderId, IX_TeamInvites_ReceiverId on TeamInvites
• IX_TeamMembers_TeamId on TeamMembers
• IX_Friends_SenderId_ReceiverId, IX_Friends_ReceiverId on Friends
• IX_Conversations_CreatorId, IX_Conversations_MatchId, IX_Conversations_TeamId on Conversations
• IX_ConversationParticipants_ParticipantsId on ConversationParticipants
• IX_ChatMessages_ConversationId, IX_ChatMessages_SenderId, IX_ChatMessages_ReplyToMessageId on ChatMessages

Unique Indexes

• RoleNameIndex on AspNetRoles (NormalizedName)
• UserNameIndex on AspNetUsers (NormalizedUserName)
• IX_AspNetUsers_Email on AspNetUsers (Email)
• IX_AspNetUsers_UserName on AspNetUsers (UserName)
• IX_Conversations_MatchId (unique) on Conversations
• IX_Conversations_TeamId (unique) on Conversations
• IX_Friends_SenderId_ReceiverId (unique) on Friends

Soft Deletes

Several tables implement soft deletes using DeletedAtUtc:

• AspNetUsers (ApplicationUser)
• Matches
• Teams
• ChatMessages
• Friends
• MatchInvites
• TeamInvites
• PlayerRatings
• Conversations

Soft-deleted records are not physically removed but marked with a deletion timestamp. Queries should filter out soft-deleted records using WHERE DeletedAtUtc IS NULL.

Migrations

Database migrations are managed using Entity Framework Core Migrations:

# Create a new migration
dotnet ef migrations add <MigrationName>

# Apply migrations
dotnet ef database update

# Remove last migration
dotnet ef migrations remove

Migrations are located in MatchBy/MatchBy/Data/Migrations/.

Seeding

Database seeding is handled by seeders in MatchBy/MatchBy/Data/Seeders/:

• UserSeeder - Creates test users
• MatchSeeder - Creates sample matches
• TeamSeeder - Creates sample teams
• ConversationSeeder - Creates sample conversations
• ChatMessageSeeder - Creates sample messages
• Additional seeders as needed

Seeders run automatically in Development environment.

Related Documentation

• Home - Main documentation index
• Architecture - System architecture