Read a schema from entities and configuration
In Indotalent’s Blazor CRM Source Code, the database schema is defined by C# entity classes in Data/Entities/ and refined by IEntityTypeConfiguration files in Infrastructure/Database/MsSQL/Configuration/. This article describes the resulting columns for SQL Server conventions: a string maps to nvarchar, a DateTime to datetime, a DateTimeOffset to datetimeoffset, and an enum to an int because the inspected configuration applies no enum-to-string conversion. Decimal properties use the provider default precision of 18,2 on SQL Server.
Nullability follows the C# types directly: nullable reference properties become nullable columns. Application rules such as “Title is required” are enforced by validators and the UI, not by database NOT NULL constraints.
Shared columns across every entity
All CRM entities inherit from BaseEntity. OnModelCreating applies two global conventions: the Id property is configured as nvarchar(36) with fixed length (a char(36)-style key that stores the sequential GUID string), and audit string columns are limited to 500 characters.
| Column | C# type | Nullable | Notes |
|---|---|---|---|
| Id | string | No | Primary key; 36-character fixed-length value |
| CreatedAt / UpdatedAt | DateTimeOffset? | Yes | Set by the audit logic in SaveChanges |
| CreatedBy / UpdatedBy | string? | Yes | nvarchar(500); user id from the request context |
| IsDeleted | bool | No | Soft-delete flag with a global query filter |
Save changes intercept delete operations: a deleted row is changed to IsDeleted = true instead of issuing a SQL DELETE, and the query filter excludes those rows from normal reads.
Lead columns
The Lead table holds the opportunity plus its references. Length limits come from LeadConfiguration: text fields use 500 characters and the two longer description fields use 1000.
| Column | C# type | Nullable | Notes |
|---|---|---|---|
| AutoNumber | string? | Yes | Document number; unique index |
| Title | string? | Yes | nvarchar(500); required by validator |
| Description / CompanyDescription | string? | Yes | nvarchar(1000) |
| CompanyName | string? | Yes | nvarchar(500); required by validator |
| CompanyAddressStreet / City / State / ZipCode / Country | string? | Yes | Each nvarchar(500) |
| CompanyPhoneNumber / FaxNumber / Email / Website / WhatsApp / LinkedIn / Facebook / Instagram / Twitter | string? | Yes | Each nvarchar(500) |
| DateProspecting / DateClosingEstimation / DateClosingActual | DateTime? | Yes | DateProspecting indexed |
| AmountTargeted / AmountClosed | decimal? | Yes | Deal size pursued and closed |
| BudgetScore / AuthorityScore / NeedScore / TimelineScore | decimal? | Yes | Qualification scores |
| PipelineStage | PipelineStage | No | Stored as int; seven stages |
| ClosingStatus | ClosingStatus | No | Stored as int; ClosedLost / ClosedWon / OnProgress |
| ClosingNote | string? | Yes | nvarchar(500) |
| CampaignId | string? | Yes | Foreign key to Campaign; indexed |
| SalesTeamId | string? | Yes | Foreign key to SalesTeam; indexed |
LeadContact and LeadActivity columns
LeadContact stores one person per lead. Every string field is nvarchar(500) except Description, which is 1000. The important columns are LeadId, FullName (indexed), Email, MobileNumber, the social profiles, and AvatarName, which records the uploaded avatar file for the avatar change flow.
| Entity | Key columns | Notes |
|---|---|---|
| LeadContact | LeadId, FullName, Description, Email, PhoneNumber, MobileNumber, AvatarName | LeadId FK indexed; AutoNumber unique; FullName indexed |
| LeadActivity | LeadId, Summary, Description, FromDate, ToDate, Type, AttachmentName | LeadId FK indexed; FromDate indexed; Type stored as LeadActivityType int |
LeadActivity.Type is the LeadActivityType enum with values such as Phone, Email, Social Media, Meeting, Event, and Other. AttachmentName is a plain string; the inspected module does not upload file content for activities.
Campaign, Budget, and Expense columns
Campaign stores title, description, TargetRevenueAmount, start and finish dates, a non-nullable Status enum, and the SalesTeamId foreign key. Budget and Expense share a similar shape: title and description, a date, an amount, a status enum, a CampaignId foreign key, and an auto-numbered document code.
| Entity | Key columns | Notes |
|---|---|---|
| Campaign | Title, Description, TargetRevenueAmount, CampaignDateStart, CampaignDateFinish, Status, SalesTeamId | SalesTeamId FK indexed; start and finish dates indexed |
| Budget | Title, Description, BudgetDate, Amount, Status, CampaignId | CampaignId FK indexed; BudgetDate indexed |
| Expense | Title, Description, ExpenseDate, Amount, Status, CampaignId | CampaignId FK indexed; ExpenseDate indexed |
SalesTeam and SalesRepresentative columns
SalesTeam is minimal: name, description, and no auto-number because it does not implement the auto-number interface. SalesRepresentative adds AutoNumber, job title, employee number, phone, email, and the SalesTeamId foreign key.
| Entity | Key columns | Notes |
|---|---|---|
| SalesTeam | Name, Description | Name indexed; duplicate names rejected by handler |
| SalesRepresentative | Name, AutoNumber, JobTitle, EmployeeNumber, PhoneNumber, EmailAddress, Description, SalesTeamId | AutoNumber unique; Name indexed; SalesTeamId FK indexed |
Foreign keys and delete behavior
The relationships inspected in the CRM configuration files are mapped with DeleteBehavior.NoAction: Lead to Campaign, Lead to SalesTeam, LeadContact to Lead, LeadActivity to Lead, and SalesTeam to its representatives. Budget and Expense map to Campaign from the child configuration with NoAction as well. In practice the soft-delete flow is the active protection for referential integrity, because removing a row sets IsDeleted instead of deleting it.
Indexes and unique constraints
| Entity | Unique index | Supporting indexes |
|---|---|---|
| Lead | AutoNumber | CampaignId, SalesTeamId, DateProspecting |
| LeadContact | AutoNumber | LeadId, FullName |
| LeadActivity | AutoNumber | LeadId, FromDate |
| Campaign | AutoNumber | SalesTeamId, CampaignDateStart, CampaignDateFinish |
| Budget | AutoNumber | CampaignId, BudgetDate |
| Expense | AutoNumber | CampaignId, ExpenseDate |
| SalesTeam | None | Name |
| SalesRepresentative | AutoNumber | Name, SalesTeamId |
The unique document number is a natural look-up key for the UI, and the foreign-key and date indexes support the list queries and stage-based dashboard grouping.
Verify the schema for your queries
Check the CRM database design for the reasoning behind the relationships and the REST API article for how the schema is exposed through DTOs. If you need indexes or required columns for your own workflow, the configuration file for each entity is the place to start. See this architecture implemented in a complete Blazor CRM application.