English
Configurations
Step 1. What the conventions cannot know
Why. EF Core maps most of the model by convention, but some decisions only we can make:
- Lengths. A
stringbecomestextof unlimited length. A display name of a megabyte is a bug or an attack; the database should refuse it.HasMaxLength(64)makes the columnvarchar(64). - Indexes. Every query that filters or sorts by a column needs an index on it, or PostgreSQL reads the whole table. EF Core creates indexes for foreign keys by convention; all others are ours to declare, including the unique ones that enforce rules such as "one direct chat per pair".
- Keys that do not follow the
Idconvention: the composite key ofChatMember, the keyUserIdofUserProfile. - What happens on delete. When a user is deleted, should their messages be deleted too, or should the delete be refused?
- Enumerations as strings and check constraints.
Each entity gets an IEntityTypeConfiguration<T> class in the folder Configurations. The classes are internal sealed: nothing outside the data layer uses them, and ApplyConfigurationsFromAssembly finds them by reflection anyway.
Step 2. Users, profiles and tokens
Do. The user configuration declares the one-to-one relationship with the profile:
cs
using Chatex.Data.Entities;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata.Builders;
namespace Chatex.Data.Configurations;
internal sealed class UserConfiguration : IEntityTypeConfiguration<User>
{
public void Configure(EntityTypeBuilder<User> builder)
{
builder.HasOne(u => u.Profile)
.WithOne(p => p.User)
.HasForeignKey<UserProfile>(p => p.UserId)
.OnDelete(DeleteBehavior.Cascade);
}
}A one-to-one relationship has no "many" side, so EF Core cannot guess which of the two tables holds the foreign key. HasForeignKey<UserProfile>(p => p.UserId) says: the profile holds it. Because the profile's key is the same column, every user has at most one profile.
cs
using Chatex.Data.Entities;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata.Builders;
namespace Chatex.Data.Configurations;
internal sealed class UserProfileConfiguration : IEntityTypeConfiguration<UserProfile>
{
public void Configure(EntityTypeBuilder<UserProfile> builder)
{
builder.HasKey(p => p.UserId);
builder.Property(p => p.DisplayName).HasMaxLength(64);
builder.Property(p => p.Bio).HasMaxLength(500);
builder.Property(p => p.AvatarPath).HasMaxLength(260);
}
}HasKey(p => p.UserId) is needed because the convention looks for a property named Id or UserProfileId. The lengths match the validation rules of topic 9: the database is the last line of defence, the validators give the user a readable message first.
cs
using Chatex.Data.Entities;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata.Builders;
namespace Chatex.Data.Configurations;
internal sealed class RefreshTokenConfiguration : IEntityTypeConfiguration<RefreshToken>
{
public void Configure(EntityTypeBuilder<RefreshToken> builder)
{
builder.Property(t => t.TokenHash).HasMaxLength(128);
builder.Property(t => t.UserAgent).HasMaxLength(512);
builder.Property(t => t.CreatedByIp).HasMaxLength(45);
builder.HasIndex(t => t.TokenHash).IsUnique();
builder.HasIndex(t => t.UserId);
builder.HasOne(t => t.User)
.WithMany(u => u.RefreshTokens)
.HasForeignKey(t => t.UserId)
.OnDelete(DeleteBehavior.Cascade);
}
}The token is looked up by its hash on every refresh, so the hash gets a unique index: the lookup is fast, and two tokens can never have the same hash. 45 characters is the longest text form of an IPv6 address.
Step 3. Chats and members
Do.
cs
using Chatex.Data.Entities;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata.Builders;
namespace Chatex.Data.Configurations;
internal sealed class ChatConfiguration : IEntityTypeConfiguration<Chat>
{
public void Configure(EntityTypeBuilder<Chat> builder)
{
builder.ToTable(t => t.HasCheckConstraint(
"ck_chats_direct_key",
"(type = 'Direct') = (direct_key IS NOT NULL)"));
builder.Property(c => c.Type).HasConversion<string>().HasMaxLength(16);
builder.Property(c => c.Title).HasMaxLength(128);
builder.Property(c => c.Description).HasMaxLength(1000);
builder.Property(c => c.AvatarPath).HasMaxLength(260);
builder.Property(c => c.PublicHandle).HasMaxLength(32);
builder.Property(c => c.DirectKey).HasMaxLength(65);
builder.HasIndex(c => c.PublicHandle).IsUnique();
builder.HasIndex(c => c.DirectKey).IsUnique();
builder.HasOne(c => c.CreatedBy)
.WithMany()
.HasForeignKey(c => c.CreatedById)
.OnDelete(DeleteBehavior.Restrict);
}
}Three new tools appear here:
- A check constraint is a condition the database checks for every row.
(type = 'Direct') = (direct_key IS NOT NULL)compares two booleans: the type isDirectexactly whendirect_keyis set. A group with a direct key, or a direct chat without one, is rejected by PostgreSQL itself, whatever the C# code does. The SQL uses the column names of the database, already in snake_case. HasConversion<string>()stores the enumeration by name ('Direct') instead of by number. The check constraint relies on it: it comparestypewith the text'Direct'.- Unique indexes on nullable columns.
public_handleanddirect_keyare unique, but most chats haveNULLthere. PostgreSQL treatsNULLs as distinct in a unique index, so any number of private chats can exist, while two equal handles cannot.
DirectKey has 65 characters: two ids of 32 hexadecimal digits and the colon between them.
cs
using Chatex.Data.Entities;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata.Builders;
namespace Chatex.Data.Configurations;
internal sealed class ChatMemberConfiguration : IEntityTypeConfiguration<ChatMember>
{
public void Configure(EntityTypeBuilder<ChatMember> builder)
{
builder.HasKey(m => new { m.ChatId, m.UserId });
builder.Property(m => m.Role).HasConversion<string>().HasMaxLength(16);
// Lists the chats of a user; the primary key already covers lookups by chat.
builder.HasIndex(m => m.UserId);
builder.HasOne(m => m.Chat)
.WithMany(c => c.Members)
.HasForeignKey(m => m.ChatId)
.OnDelete(DeleteBehavior.Cascade);
builder.HasOne(m => m.User)
.WithMany(u => u.Memberships)
.HasForeignKey(m => m.UserId)
.OnDelete(DeleteBehavior.Cascade);
}
}HasKey(m => new { m.ChatId, m.UserId }) declares the composite primary key with an anonymous type. A primary key comes with an index whose first column is chat_id, so "the members of a chat" is fast; "the chats of a user" needs the second index on user_id, as the comment says.
Step 4. Messages and attachments
Do.
cs
using Chatex.Data.Entities;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata.Builders;
namespace Chatex.Data.Configurations;
internal sealed class MessageConfiguration : IEntityTypeConfiguration<Message>
{
public const int MaxBodyLength = 4000;
public void Configure(EntityTypeBuilder<Message> builder)
{
builder.Property(m => m.Body).HasMaxLength(MaxBodyLength);
// Keyset pagination: WHERE chat_id = @c AND id < @cursor ORDER BY id DESC.
builder.HasIndex(m => new { m.ChatId, m.Id }).IsDescending(false, true);
builder.HasIndex(m => m.SenderId);
builder.HasOne(m => m.Chat)
.WithMany(c => c.Messages)
.HasForeignKey(m => m.ChatId)
.OnDelete(DeleteBehavior.Cascade);
builder.HasOne(m => m.Sender)
.WithMany()
.HasForeignKey(m => m.SenderId)
.OnDelete(DeleteBehavior.Restrict);
builder.HasOne(m => m.ReplyTo)
.WithMany()
.HasForeignKey(m => m.ReplyToMessageId)
.OnDelete(DeleteBehavior.SetNull);
}
}The most important line of the data layer is the index (chat_id, id DESC). Loading a page of history is the most frequent query of a messenger:
sql
SELECT ... FROM messages
WHERE chat_id = @chat AND id < @cursor
ORDER BY id DESC
LIMIT 51With this index PostgreSQL jumps to the chat, then to the cursor, and reads the next 51 entries in order — the same speed for the first page and for a page from three years ago. IsDescending(false, true) makes the first column ascending and the second descending. The constant MaxBodyLength gives the limit of 4000 characters a name; the message validator of topic 11 checks the same number (MessageLimits.MaxBody in Chatex.Core) before a request ever reaches the database.
cs
using Chatex.Data.Entities;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata.Builders;
namespace Chatex.Data.Configurations;
internal sealed class AttachmentConfiguration : IEntityTypeConfiguration<Attachment>
{
public void Configure(EntityTypeBuilder<Attachment> builder)
{
builder.Property(a => a.FileName).HasMaxLength(255);
builder.Property(a => a.ContentType).HasMaxLength(127);
builder.Property(a => a.StoragePath).HasMaxLength(260);
builder.HasIndex(a => a.MessageId);
builder.HasIndex(a => a.UploaderId);
builder.HasOne(a => a.Message)
.WithMany(m => m.Attachments)
.HasForeignKey(a => a.MessageId)
.OnDelete(DeleteBehavior.SetNull);
builder.HasOne(a => a.Uploader)
.WithMany()
.HasForeignKey(a => a.UploaderId)
.OnDelete(DeleteBehavior.Restrict);
}
}Step 5. Delete rules
Why. Every relationship says what happens to the dependent rows when the principal row is deleted. The choices of Chatex (Table 4.1) follow one question: is the dependent row meaningless without its principal, or is it a record we must not lose silently?
Table 4.1. Delete rules of the relationships
| When this is deleted | these rows | rule | why |
|---|---|---|---|
| user | profile, refresh tokens, memberships | Cascade | they have no meaning without the user |
| user | chats they created, messages they sent, files they uploaded | Restrict | deleting a user must not erase other people's conversations; the delete fails until the data is dealt with on purpose |
| chat | members, messages | Cascade | a chat is deleted as a whole (in practice chats are soft-deleted, so this happens only on cleanup) |
| message | replies to it | SetNull | a reply survives; it just no longer points to the original |
| message | its attachments | SetNull | the file becomes an unlinked upload that the cleanup job removes |
OnDelete is written explicitly everywhere, even where it repeats the convention, so the decision is visible in the code.
Multiple cascade paths
Not every combination is allowed. If deleting a user cascaded to their messages and to their chats, which also cascade to messages, the database would have two delete paths to the same rows. SQL Server refuses such a model outright; PostgreSQL accepts it, but the result depends on the order of the deletes. Restrict on the "created by" and "sender" relationships avoids the problem.
Check
powershell
dotnet buildThe build succeeds, and Source/Chatex.Data/Configurations contains seven files, one per entity of Chatex (Identity's own entities are configured by IdentityDbContext).