Skip to content

Database

The NKO ERP stores everything in two SQL Server databases with no foreign keys between them, reached through two Entity Framework Core contexts. This section is the full data dictionary: every entity, every column, its type, nullability and relationships, generated from the EF model.

Core Warehouse
Database NKO_DB NKO_DB_Warehouse
Context NKO_DBEntities1 WarehouseDBContext
Connection string HrConnection WareConnection
Project NKO.Lib/NKO.DataAccess/ NKO.Lib/NKO.Warehouse.DataAccess/
Entities 140 21
Migrations 18 5
Covers HR, payroll, procurement, finance, recruitment, training, administration, identity Stock, assets, maintenance, destruction, waybills

A third connection string, HangfireConnection, points at the Hangfire job store, which Hangfire creates and owns (PrepareSchemaIfNecessary = true). It is not part of the application model.

Six more tables in NKO_DB that are not in this dictionary

NKO_DBEntities1 derives from IdentityDbContext<ApplicationUser>, so ASP.NET Core Identity adds its own tables alongside the 140 application entities: roles, role claims, user claims, external logins, user-role links and user tokens (IdentityRole, IdentityRoleClaim, IdentityUserClaim, IdentityUserLogin, IdentityUserRole, IdentityUserToken). They belong to the framework, have no DbSet on the context, and are not documented here. Permissions come from AppUserGroup / AppPermission instead (see Security & Permissions) — yet the code still calls User.IsInRole(...) in 56 places, and nothing in the application ever assigns an Identity role. See R3-8. ApplicationUser itself is documented, under Core — HR; it is mapped to ApplicationUsers rather than Identity's default AspNetUsers.

The dictionary

Page Entities
Context ERD both contexts, relationships and cardinality
Core — HR 21
Core — Procurement 17
Core — Finance 23
Core — Payroll 17
Core — Recruitment 21
Core — Training 1
Core — Administration 40
Warehouse 21
Total 161

Module grouping follows the Modules section. A few entities are cross-cutting and sit under Administration.

Conventions

Names

Entity classes are singular; the DbSet is usually plural.

public class StockDetails { … }                              // the class
public virtual DbSet<StockDetails> StockDetails { get; set; } // the set

The same split applies to AppUserAdvanceDetails / AppUserAdvanceDetails, AppUserOvertimeDates / AppUserOvertimeDates, MasterBudgetDetails / MasterBudgetDetails and MasterBudgetDetailsMonths / MasterBudgetDetailsMonths. Both names are real and neither is wrong — but they refer to different things, and mixing them up is the single most common documentation error in this repository. Each entity page shows both where they differ.

Not every class follows the singular rule. 15 class names are themselves plural, including Announcements, Applicants, ExchangeRates, Materials, RFQs, RFQProviderMats, ProjectDonors, DonorPartners and JobAnswers — so "plural means DbSet" is not a safe rule either. Check the class declaration.

86 of 140 core entities carry an explicit [Table("…")]; the rest map by convention.

Keys

Primary keys are <Entity>Id by convention — AppUserSignId, TrainingRequestId. 18 entities use a plain Id instead, among them AppUserAdvance, AppUserDeduction, AppUserCharge and Bank. The core context declares almost no keys explicitly; the warehouse context declares 17 with HasKey plus ValueGeneratedOnAdd.

Audit columns

BaseEntity (DbModel/Base/BaseEntity.cs) supplies six nullable audit members, and 105 of 140 core entities inherit it:

public DateTime? DateCreated  { get; set; }
public DateTime? DateUpdated  { get; set; }
public DateTime? DateDeleted  { get; set; }
public int? CreatedBy { get; set; }
public int? UpdatedBy { get; set; }
public int? DeletedBy { get; set; }

Warehouse entities do not inherit it; they declare their own audit columns individually. ApplicationUser is a special case — it derives from Identity's IdentityUser and implements IBaseEntity directly, declaring all six members itself.

38 entities redeclare CreatedBy and shadow the base — R2-1

Of the 105 entities inheriting BaseEntity, 38 declare their own public int CreatedBy — non-nullable, and without the new modifier, so each one raises compiler warning CS0108. No entity redeclares any of the other five audit members.

The two members disagree on nullability (int vs int?), so "was this row's creator recorded?" has two different answers depending on which member the reading code binds to.

Workflow columns

Every document that goes through an approval carries the same shape inline — there is no separate workflow or history table:

Column group Purpose
StatusId The state-machine position (see the module's status enum)
NextUserId Who must act next; null once complete
<Role>Id · <Role>Sign · <Role>SignDate · <Role>Note One block per approver role
RefuseId · IsRefused · RefuseDate · RefuseNote The rejection record
<Entity>Guid The public identifier used in URLs — Details/{guid}, not the integer key

Core — Training is the clearest single example: one entity, five approver blocks.

Soft deletes and query filters

Only four entities have a global query filter, all in the core context:

Entity Filter
AppUser !x.IsDeleted && x.Status == 1
AppUserSign !x.Deleted
Project !x.IsDeleted
BudgetLine !x.IsDeleted

Everything else with an IsDeleted column is filtered by hand, per query — so a query that forgets the predicate returns deleted rows. Conversely, code that needs filtered rows (draft or deleted employees) must call IgnoreQueryFilters(), as HumanResourceController.DraftEmployees does.

The warehouse context has no query filters at all.

Delete behaviour

Counted from EF's migration snapshots, which record the whole model — relationships configured in OnModelCreating and the ones EF infers from naming and attributes:

Context Relationships Cascade ClientSetNull NoAction Restrict
Core 325 132 122 69 2
Warehouse 36 6 3 27 0

ClientSetNull is EF's default for an optional foreign key: EF nulls the column on dependents it has loaded, but the database constraint is NO ACTION. Cascade is EF's default for a required one. Only 259 core and 27 warehouse relationships are configured explicitly; the rest take these defaults.

The model also contains three one-to-one relationships: AppUserExtend ↔ AppUser and RFQTechnical ↔ RFQs in the core, and Computer ↔ Asset in the warehouse. RFQTechnical and Computer share their principal's primary key.

Neither context states a convention. In the core, 132 relationships cascade — deleting a principal silently deletes its dependents — while 69 are pinned to NoAction. The warehouse configures all 27 of its explicit relationships as NoAction, which reads like a deliberate no-cascade policy, but 6 inferred relationships still cascade: deleting an Asset deletes its MaintenanceReq, AssetsReturn and Computer rows, deleting a Warehouse deletes its maintenance requests, and deleting a CatL1 deletes its EmpCat1Permission rows. Tracked as R2-3.

Indexes

Exactly one index is configured in either context:

modelBuilder.Entity<Attendance>()
    .HasIndex(p => new { p.UserId, p.Year, p.Month, p.Day })
    .IsUnique(true);

Everything else relies on the primary-key clustered index and whatever SQL Server infers for foreign keys. Tracked as R2-2.

Lazy loading is off

Navigation properties are declared virtual throughout, and Microsoft.EntityFrameworkCore.Proxies is referenced — but UseLazyLoadingProxies() is never called. An un-Included navigation property is therefore null, not a lazy round-trip. Every query must .Include(...) what it needs. See Technology Stack.

Migrations

Applied per context:

Update-Database -Context NKO_DBEntities1
Update-Database -Context WarehouseDBContext

Migrations/ holds 37 files for the core model, but that is 18 migrations × 2 files (<name>.cs + <name>.Designer.cs) plus one ModelSnapshot. The warehouse folder holds 11 files for 5 migrations. See Installation — Database Setup.