Employee Database Schema Reference
Summary
The current Employee Service model contains 13 persisted objects in its owned schema, 8 required child-to-root foreign keys, and 17 indexes. This reference inventories logical objects and important storage behavior without reproducing every internal column.
Audience
- Backend developers and QA engineers
- DevOps engineers, support engineers, and solution architects
- Future schema-reference automation
Reference Content
Source-reference keys
| Key | Repository-relative source |
|---|---|
| S1 | microservices/src/employee-service/Infrastructure/EmployeeDbContext.cs |
| S2 | microservices/src/employee-service/Infrastructure/Persistence.cs |
| S3 | microservices/src/employee-service/Domain/Employees/Employee.cs |
| S4 | microservices/src/employee-service/Domain/Organization/Organization.cs |
| S5 | microservices/src/employee-service/Infrastructure/Migrations/20260701173627_InitialEmployeeSchema.cs |
| S6 | microservices/src/employee-service/Infrastructure/Migrations/20260701181407_AddTimelineEventFields.cs |
| S7 | microservices/src/employee-service/Infrastructure/Migrations/EmployeeDbContextModelSnapshot.cs |
| S8 | microservices/src/employee-service/Infrastructure/PostgresEmployeeSearchProvider.cs |
| S9 | Data/AppDbContext.cs |
| S10 | Models/Labor.cs |
| S11 | Models/Department.cs |
| S12 | Models/Designation.cs |
| S13 | Models/EmployeeDocument.cs |
DbContext inventory
| Context | Classification | Responsibility | Model boundary | Source |
|---|---|---|---|---|
EmployeeDbContext | Employee-owned | Employee aggregate, organization, history, audit, outbox | Default Employee schema; code-first migrations | S1 |
AppDbContext | Legacy / Transitional | Monolith Labor, Department, Designation, EmployeeDocument and other monolith domains | Separate Legacy model and migrations | S9 |
EmployeeDbContextFactory is a design-time factory, not a third persisted context. It supplies the Employee context with a null tenant abstraction for EF tooling (S2).
Persisted object inventory
| Category | Logical object | Purpose / owner | Tenant scope | ID | Relationships | Uniqueness / indexes | History and lifecycle | Source |
|---|---|---|---|---|---|---|---|---|
| Employee core | Employee | Workforce master aggregate root; Employee Service | Integer tenant filter | GUID | Owns inline Employment and 8 child collections; logical org/manager refs | Unique tenant/code; tenant/status; tenant/department | Status-based soft deletion; produces timeline/audit/outbox participation | S1, S3, S5 |
| Organization | Department | Workforce organization unit; Employee Service | Integer tenant filter | GUID | Logical parent and head refs | Unique tenant/code | Active/inactive; deactivation restricted when active Employees assigned | S1, S4 |
| Organization | Designation | Workforce designation; Employee Service | Integer tenant filter | GUID | Logical Department ref | Unique tenant/code | Active/inactive deactivation | S1, S4 |
| Profile | Employee education | Education collection child | Stamped integer tenant/filter | GUID | Required Employee FK, cascade | Employee-ID index | Replaced as aggregate collection | S1, S3, S5 |
| Profile | Employee experience | Work-history collection child | Stamped integer tenant/filter | GUID | Required Employee FK, cascade | Employee-ID index | Replaced as aggregate collection | S1, S3, S5 |
| Profile | Emergency contact | Contact collection child | Stamped integer tenant/filter | GUID | Required Employee FK, cascade | Employee-ID index | Replaced as aggregate collection | S1, S3, S5 |
| Profile | Employee skill | Skill collection child | Stamped integer tenant/filter | GUID | Required Employee FK, cascade | Employee-ID index | Replaced as aggregate collection; queried by search | S1, S3, S8 |
| Profile | Employee bank account | Bank-detail collection child | Stamped integer tenant/filter | GUID | Required Employee FK, cascade | Employee-ID index | Replaced as aggregate collection | S1, S3, S5 |
| Profile | Employee document reference | Document metadata/reference child | Stamped integer tenant/filter | GUID | Required Employee FK, cascade | Employee-ID index | Replace or append through aggregate methods | S1, S3, S5 |
| History | Employee note | Employee note child | Stamped integer tenant/filter | GUID | Required Employee FK, cascade | Employee-ID index | Append through aggregate method | S1, S3, S5 |
| History | Employee timeline entry | Workforce history child | Stamped integer tenant/filter | GUID | Required Employee FK, cascade | Employee-ID and tenant/time indexes | Append from aggregate lifecycle or confirmed external-event method | S1, S3, S6 |
| Audit | Employee audit log | Technical action/actor record; infrastructure | Integer tenant filter | GUID | Textual entity reference; no FK | Tenant/time index | Intended append-only; DB enforcement not found | S1, S5 |
| Messaging | Outbox message | Local integration-delivery work item; shared model in Employee schema | Optional shared tenant concept; no user query filter | GUID | No business FK | Unique event identity; status/time index | Pending/failed/processed tracking | S1, S2, S5 |
Employment is an owned value stored inline with Employee and is not a separate persisted object. Personal address and government-identifier concepts are also root values rather than separate objects.
Relationship inventory
| Relationship | Cardinality | Database FK | Delete behavior | Validation | Source |
|---|---|---|---|---|---|
| Employee → Education | 1:N | Required | Cascade | Aggregate stamping | S1, S5 |
| Employee → Experience | 1:N | Required | Cascade | Aggregate stamping | S1, S5 |
| Employee → Emergency Contact | 1:N | Required | Cascade | Aggregate stamping | S1, S5 |
| Employee → Skill | 1:N | Required | Cascade | Aggregate stamping | S1, S5 |
| Employee → Bank Account | 1:N | Required | Cascade | Aggregate stamping | S1, S5 |
| Employee → Document Reference | 1:N | Required | Cascade | Aggregate/document invariants | S1, S5 |
| Employee → Note | 1:N | Required | Cascade | Aggregate note invariant | S1, S5 |
| Employee → Timeline | 1:N | Required | Cascade | Aggregate timeline invariant | S1, S5 |
| Employee → Employment | 1:1 owned | Inline ownership | Root lifecycle | Domain value | S1, S3 |
| Employee → Department/Designation/Manager | Optional references | None configured | None | Application/domain validation | S1, validators |
| Department → Parent/Head | Optional references | None configured | None | Self-parent check only; broader hierarchy requires confirmation | S1, S4 |
| Designation → Department | Optional reference | None configured | None | Application validation in Employee assignment paths | S1, S4 |
| Audit → entity | Textual reference | None | None | Writer-controlled | S1, S2 |
| Outbox → domain change | Message correlation only | None | None | Context event mapping | S1 |
Index and constraint inventory
| Object | Index/constraint | Unique | Purpose | Source |
|---|---|---|---|---|
| Employee | Tenant + Employee code | Yes | Tenant-scoped code uniqueness | S1, S5 |
| Employee | Tenant + status | No | Status filtering | S1, S5 |
| Employee | Tenant + department | No | Department filtering | S1, S5 |
| Department | Tenant + code | Yes | Tenant-scoped code uniqueness | S1, S5 |
| Designation | Tenant + code | Yes | Tenant-scoped code uniqueness | S1, S5 |
| Eight Employee child objects | Employee ID, one each | No | Root-to-child joins | S5 |
| Timeline | Tenant + occurrence time | No | Tenant history ordering | S6 |
| Audit log | Tenant + occurrence time | No | Tenant audit lookup | S1, S5 |
| Outbox | Event identity | Yes | Persisted event idempotency | S1, S5 |
| Outbox | Status + occurrence time | No | Ordered relay work selection | S1, S5 |
This accounts for 17 indexes: 3 Employee, 2 organization, 8 child, 2 history/audit, and 2 outbox. Employee email, designation reference, and reporting-manager reference have no confirmed database index.
Migration inventory
| Order | Migration | Model effect | Source |
|---|---|---|---|
| 1 | Initial Employee schema | Creates all 13 objects, initial indexes, and 8 child FKs | S5 |
| 2 | Timeline event fields | Adds timeline classification/metadata and tenant/time index; constrains two text properties | S6 |
| Current | Model snapshot | Current canonical EF model snapshot | S7 |
Tenant-isolation inventory
| Path | Behavior | Source |
|---|---|---|
| Normal root/organization/audit query | Current-tenant or confirmed super-admin filter | S1 |
| Child query | Shared generic child filter | S1 |
| Code/email existence checks | Ignore filters plus explicit tenant predicate | S2 |
| Command persistence | Tenant required by approved handlers | application validators/handlers |
| Outbox processing | Operational status query without end-user tenant filter | S2 |
Transaction participant inventory
| Participant | Local context commit | Notes | Source |
|---|---|---|---|
| Aggregate/organization changes | Yes | Tracked by Employee context | S1, S2 |
| Timeline children | Yes | Added by aggregate methods | S3 |
| Audit records | Yes | Added before unit-of-work save | S2 |
| New outbox records | Yes | Added by context override before save | S1 |
| Broker delivery | No | Separate relay concern | S2 |
Legacy and compatibility inventory
| Legacy object | Classification | Identifier | Employee Service equivalent | Source |
|---|---|---|---|---|
| Labor | Legacy / Transitional | Numeric | Employee aggregate with GUID | S9, S10 |
| Department | Legacy / Transitional | Numeric | Department aggregate with GUID | S9, S11 |
| Designation | Legacy / Transitional | Numeric | Designation aggregate with GUID | S9, S12 |
| EmployeeDocument | Legacy / Transitional | Numeric associations | Employee document reference metadata; not equivalent binary ownership | S9, S13 |
No Employee Service legacy-ID mapping object or backfill-state object appears in S1, S5, S6, or S7.
Source References
microservices/src/employee-service/Infrastructure/EmployeeDbContext.csmicroservices/src/employee-service/Infrastructure/Persistence.csmicroservices/src/employee-service/Infrastructure/PostgresEmployeeSearchProvider.csmicroservices/src/employee-service/Infrastructure/Migrations/20260701173627_InitialEmployeeSchema.csmicroservices/src/employee-service/Infrastructure/Migrations/20260701181407_AddTimelineEventFields.csmicroservices/src/employee-service/Infrastructure/Migrations/EmployeeDbContextModelSnapshot.csmicroservices/src/employee-service/Domain/Employees/Employee.csmicroservices/src/employee-service/Domain/Organization/Organization.csData/AppDbContext.csModels/Labor.csModels/Department.csModels/Designation.csModels/EmployeeDocument.cs
Related Articles
See Also
Keywords
- Schema inventory
- Persisted objects
- Relationship reference
Revision Information
- Status: Draft
- Last reviewed: 2026-07-15
- Review cycle: Quarterly