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.

Core Tables

ApplicationUser

Extends ASP.NET Core Identity's IdentityUser with additional properties.

Column	Type	Description
Id	string (PK)	User identifier (from Identity)
UserName	string	Username (from Identity)
Email	string	Email address (from Identity)
EmailConfirmed	bool	Email confirmation status (from Identity)
DisplayName	string	User's display name
PreferredSports	Sports[]	Array of preferred sports (stored as JSON)
BaseLocation	Location?	User's base location (stored as JSON)
Bio	string?	User biography
Rating	float	Average player rating
Status	AccountStatus	Account status (Active, Suspended, etc.)
ProfileImage	FileStore?	Profile image information (stored as JSON)
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)
• One-to-Many: PlayerRatings (as RatedUser)
• One-to-Many: PlayerRatings (as Rater)
• One-to-Many: Friends (as User or Friend)

Match

Represents a sports match/game.

Column	Type	Description
Id	string (PK)	Match identifier
Location	Location	Match location (stored as JSON)
Address	string	Physical address
MatchDateTimeUtc	DateTime	Scheduled match date and time
Description	string	Match description
minPlayers	int	Minimum number of players required
maxPlayers	int	Maximum number of players allowed
Sport	Sports	Sport type (enum)
Status	MatchStatus	Match status (Pending, Confirmed, Completed, etc.)
Privacy	MatchPrivacy	Privacy level (Public, Private, etc.)
CreatorId	string (FK)	User who created the match
ConversationId	string? (FK)	Associated conversation/chat
CreatedAtUtc	DateTime	Match creation timestamp
UpdatedAtUtc	DateTime?	Last update timestamp
DeletedAtUtc	DateTime?	Soft delete timestamp

Relationships:

• Many-to-One: ApplicationUser (Creator)
• Many-to-Many: ApplicationUser (Participants)
• One-to-One: Conversation (optional)
• One-to-Many: MatchInvite

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
Sport	Sports	Primary sport (enum)
CreatorId	string (FK)	User who created the team
CreatedAtUtc	DateTime	Team creation timestamp
UpdatedAtUtc	DateTime?	Last update timestamp
DeletedAtUtc	DateTime?	Soft delete timestamp

Relationships:

• Many-to-One: ApplicationUser (Creator)
• Many-to-Many: ApplicationUser (Members)
• One-to-Many: TeamInvite

Conversation

Represents a chat conversation (typically associated with a match).

Column	Type	Description
Id	string (PK)	Conversation identifier
MatchId	string? (FK)	Associated match (if any)
CreatedAtUtc	DateTime	Conversation creation timestamp
UpdatedAtUtc	DateTime?	Last update timestamp

Relationships:

• One-to-One: Match (optional)
• One-to-Many: ChatMessage
• Many-to-Many: ApplicationUser (Participants)

ChatMessage

Represents a message in a conversation.

Column	Type	Description
Id	string (PK)	Message identifier
ConversationId	string (FK)	Parent conversation
SenderId	string (FK)	User who sent the message
Content	string	Message content
MessageType	MessageType	Message type (Text, Location, etc.)
Status	MessageStatus	Message status (Sent, Delivered, Read)
Location	Location?	Location data (if MessageType is Location)
CreatedAtUtc	DateTime	Message creation timestamp
UpdatedAtUtc	DateTime?	Last update timestamp
DeletedAtUtc	DateTime?	Soft delete timestamp

Relationships:

• Many-to-One: Conversation
• Many-to-One: ApplicationUser (Sender)

Friend

Represents a friendship relationship between users.

Column	Type	Description
Id	string (PK)	Friendship identifier
UserId	string (FK)	First user in the friendship
FriendId	string (FK)	Second user in the friendship
Status	FriendStatus	Friendship status (Pending, Accepted, etc.)
CreatedAtUtc	DateTime	Friendship creation timestamp
UpdatedAtUtc	DateTime?	Last update timestamp

Relationships:

• Many-to-One: ApplicationUser (User)
• Many-to-One: ApplicationUser (Friend)

PlayerRating

Represents a rating given by one user to another.

Column	Type	Description
Id	string (PK)	Rating identifier
RaterId	string (FK)	User who gave the rating
RatedUserId	string (FK)	User who received the rating
MatchId	string? (FK)	Associated match (if any)
Rating	int	Rating value (typically 1-5)
Comment	string?	Optional comment
CreatedAtUtc	DateTime	Rating creation timestamp

Relationships:

• Many-to-One: ApplicationUser (Rater)
• Many-to-One: ApplicationUser (RatedUser)
• Many-to-One: Match (optional)

MatchInvite

Represents an invitation to join a match.

Column	Type	Description
Id	string (PK)	Invitation identifier
MatchId	string (FK)	Target match
InviterId	string (FK)	User who sent the invitation
InviteeId	string (FK)	User who received the invitation
Status	InviteStatus	Invitation status (Pending, Accepted, Declined)
CreatedAtUtc	DateTime	Invitation creation timestamp
UpdatedAtUtc	DateTime?	Last update timestamp

Relationships:

• Many-to-One: Match
• Many-to-One: ApplicationUser (Inviter)
• Many-to-One: ApplicationUser (Invitee)

TeamInvite

Represents an invitation to join a team.

Column	Type	Description
Id	string (PK)	Invitation identifier
TeamId	string (FK)	Target team
InviterId	string (FK)	User who sent the invitation
InviteeId	string (FK)	User who received the invitation
Status	InviteStatus	Invitation status (Pending, Accepted, Declined)
CreatedAtUtc	DateTime	Invitation creation timestamp
UpdatedAtUtc	DateTime?	Last update timestamp

Relationships:

• Many-to-One: Team
• Many-to-One: ApplicationUser (Inviter)
• Many-to-One: ApplicationUser (Invitee)

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"
}

Indexes

The following indexes are created automatically by Entity Framework Core:

• Primary keys on all tables
• Foreign key indexes
• Unique constraints (e.g., Email in ApplicationUser)

Additional indexes may be added for:

• Match.MatchDateTimeUtc (for date range queries)
• Match.Sport (for sport filtering)
• Match.Status (for status filtering)
• ChatMessage.ConversationId (for conversation queries)
• ChatMessage.CreatedAtUtc (for message ordering)

Soft Deletes

Several tables implement soft deletes using DeletedAtUtc:

• ApplicationUser
• Match
• Team
• ChatMessage

Soft-deleted records are not physically removed but marked with a deletion timestamp.

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