SCHEMA-001: Database Schema Alignment
ID: SCHEMA-001
Status: Complete
Created: 2026-03-03
Updated: 2026-03-03
Priority: High
Implementation Progress
| Phase |
Description |
Status |
Commit |
| 1 |
Stale Entity Cleanup |
Complete |
d4df960 |
| 2 |
New Parameter Groups |
Complete |
d2c2171 |
| 3 |
Expanded EGP |
Complete |
d2c2171 |
| 4 |
db_systemparameter Full Stack |
Complete |
d2c2171 |
| 5 |
Repos + DI |
Complete |
fa8d646 |
| 6 |
Changelogs Gap |
Complete |
fa8d646 |
| 7 |
API + Tests |
Complete |
9a7c356 |
Summary
A live database schema comparison revealed significant gaps between the Essert.MF backend codebase and the actual MariaDB schema. This requirement covers adding 4 new parameter groups, expanding 1 existing group, integrating a completely new database (db_systemparameter), removing stale entities, and closing the changelogs gap — as a full vertical slice across all architecture layers.
Findings
| Database |
Documented |
Actual |
Gap |
| db_productparameter |
40 tables |
63 tables |
+23 new tables |
| db_statistics |
24 tables |
23 tables |
-1 removed (tbl_ignoremessages) |
| db_essert |
5 tables |
3 tables |
-2 removed (tbl_battery, tbl_downtime) |
| db_changelogs |
49 entities |
71 tables |
+22 missing entities, 1 stale |
| db_systemparameter |
not implemented |
8 tables |
entirely new database |
| db_wpc |
4 tables |
4 tables |
no change |
| db_robots |
5 tables |
5 tables |
no change |
| db_process |
1 table |
1 table |
no change |
Phase 1: Stale Entity Cleanup (db_essert) — COMPLETE
Goal: Remove all Battery and Downtime code — tables no longer exist in the database.
Status: Complete | Commit: d4df960
Actual impact: 43 files changed (27 deleted, 16 modified), 2,228 lines removed
1.1 Infrastructure — Deleted (6 files)
| File |
Action |
Essert.MF.Infrastructure/Data/Essert/Entities/Battery.cs |
Deleted |
Essert.MF.Infrastructure/Data/Essert/Entities/Downtime.cs |
Deleted |
Essert.MF.Infrastructure/Data/Essert/Configurations/BatteryConfiguration.cs |
Deleted |
Essert.MF.Infrastructure/Data/Essert/Configurations/DowntimeConfiguration.cs |
Deleted |
Essert.MF.Infrastructure/Data/Changelogs/Entities/WpcGroup.cs |
Deleted |
Essert.MF.Infrastructure/Data/Changelogs/Configurations/WpcGroupConfiguration.cs |
Deleted |
1.2 Infrastructure — Modified (3 files)
| File |
Change |
Essert.MF.Infrastructure/Data/Essert/Context/EssertDbContext.cs |
Removed DbSet<Battery> and DbSet<Downtime> |
Essert.MF.Infrastructure/Data/Changelogs/Context/ChangelogsDbContext.cs |
Removed DbSet<WpcGroup> |
Essert.MF.Infrastructure/Repositories/SystemConfigurationRepository.cs |
Removed Battery/Downtime methods, simplified IsSystemHealthyAsync and GetSystemSummaryAsync |
1.3 Application — Deleted (12 files) + Modified (2 files)
| File |
Action |
Essert.MF.Application/Ports/ISystemConfigurationRepository.cs |
Modified — removed BatteryStatusDto, SystemDowntimeDto, and all Battery/Downtime method contracts |
Essert.MF.Application/DependencyInjection/ServiceCollectionExtensions.cs |
Modified — removed 6 handler registrations |
Essert.MF.Application/UseCases/System/UpdateBatteryStatusCommand.cs |
Deleted |
Essert.MF.Application/UseCases/System/UpdateBatteryStatusHandler.cs |
Deleted |
Essert.MF.Application/UseCases/System/GetAllBatteryStatusesQuery.cs |
Deleted |
Essert.MF.Application/UseCases/System/GetAllBatteryStatusesHandler.cs |
Deleted |
Essert.MF.Application/UseCases/System/GetBatteryStatusQuery.cs |
Deleted |
Essert.MF.Application/UseCases/System/GetBatteryStatusHandler.cs |
Deleted |
Essert.MF.Application/UseCases/System/RecordDowntimeCommand.cs |
Deleted |
Essert.MF.Application/UseCases/System/RecordDowntimeHandler.cs |
Deleted |
Essert.MF.Application/UseCases/System/GetDowntimeRecordsQuery.cs |
Deleted |
Essert.MF.Application/UseCases/System/GetDowntimeRecordsHandler.cs |
Deleted |
Essert.MF.Application/UseCases/System/GetRecentDowntimeRecordsQuery.cs |
Deleted |
Essert.MF.Application/UseCases/System/GetRecentDowntimeRecordsHandler.cs |
Deleted |
1.4 API — Modified (4 files) + Deleted (3 files)
| File |
Action |
Essert.MF.API.Rest/Controllers/SystemController.cs |
Modified — removed Battery/Downtime endpoints |
Essert.MF.API.Rest/Controllers/RecordDowntimeRequest.cs |
Deleted |
Essert.MF.API.Rest/Controllers/RecordDowntimeResponse.cs |
Deleted |
Essert.MF.API.Rest/Controllers/UpdateBatteryStatusRequest.cs |
Deleted |
Essert.MF.API.GraphQL/Queries/SystemQueries.cs |
Modified — removed Battery/Downtime queries |
Essert.MF.API.GraphQL/Mutations/SystemMutations.cs |
Modified — removed Downtime/Battery mutations and payload types |
1.5 Tests — Deleted (6 files) + Modified (7 files)
| File |
Action |
Essert.MF.Application.Tests/UseCases/System/GetAllBatteryStatusesHandlerTests.cs |
Deleted |
Essert.MF.Application.Tests/UseCases/System/GetBatteryStatusHandlerTests.cs |
Deleted |
Essert.MF.Application.Tests/UseCases/System/UpdateBatteryStatusHandlerTests.cs |
Deleted |
Essert.MF.Application.Tests/UseCases/System/RecordDowntimeHandlerTests.cs |
Deleted |
Essert.MF.Application.Tests/UseCases/System/GetDowntimeRecordsHandlerTests.cs |
Deleted |
Essert.MF.Application.Tests/UseCases/System/GetRecentDowntimeRecordsHandlerTests.cs |
Deleted |
Essert.MF.Application.Tests/UseCases/System/GetSystemSummaryHandlerTests.cs |
Modified — updated mock data to match simplified summary |
Essert.MF.Infrastructure.Tests/.../EssertDbContextTests.cs |
Modified — removed Battery/Downtime tests |
Essert.MF.Infrastructure.Tests/.../EssertDataTests.cs |
Modified — removed Battery/Downtime data test regions |
Essert.MF.Infrastructure.Tests/.../ChangelogsDbContextTests.cs |
Modified — removed WpcGroup test and entity type reference |
Essert.MF.Infrastructure.Tests/.../ChangelogsDataTests.cs |
Modified — removed WpcGroup query from WPC data test |
Essert.MF.API.Rest.Tests/Controllers/SystemControllerTests.cs |
Modified — removed Battery/Downtime test methods |
Essert.MF.API.GraphQL.Tests/Queries/SystemQueriesTests.cs |
Modified — removed Battery query test |
Essert.MF.API.GraphQL.Tests/Mutations/SystemMutationsTests.cs |
Modified — removed Downtime/Battery mutation tests |
1.6 Implementation Notes
- Scope was larger than estimated: Original estimate was ~15 files; actual was 43 files. The plan missed GraphQL adapter layer, REST API request/response models, EssertDataTests, ChangelogsDataTests, ChangelogsDbContextTests, and additional Application test files.
- SystemConfigurationRepository simplified:
IsSystemHealthyAsync() now checks variable count >= 0. GetSystemSummaryAsync() now returns only CurrentVersion, VersionDate, SystemVariables, IsHealthy (Battery/Downtime stats removed).
- Infrastructure DI was not affected:
Essert.MF.Infrastructure/DependencyInjection/ServiceCollectionExtensions.cs had no Battery/Downtime registrations to remove (only Application-layer DI was impacted).
Phase 2: New Parameter Groups — Infrastructure Layer
Goal: Add entities and EF configurations for 4 new parameter groups in db_productparameter.
2.1 EGK (Schunk EGK Gripper) — 4-table pattern
Reference pattern: KannSetup.cs, KannMovement.cs, KannPositionname.cs
| New File |
Maps To |
Key Columns |
Entities/EgkSetup.cs |
tbl_egk_setup |
Setupname, Mapping, Creator(254) |
Entities/EgkMapping.cs |
tbl_egk_mapping |
VersionsUID(FK), SetupUID(FK) |
Entities/EgkMovement.cs |
tbl_egk_movements |
SetupUID(FK), PositionNumber, Position(float), MovementType(bit), GrippingForce, GrippingDirection(bit), MoveDirection(bit), Tolerance+/-(float), Creator(254) |
Entities/EgkPositionname.cs |
tbl_egk_positionname |
PositionName(50), PositionNumber, InUse(bit), Creator(254) |
Configurations/EgkSetupConfiguration.cs |
|
|
Configurations/EgkMappingConfiguration.cs |
|
|
Configurations/EgkMovementConfiguration.cs |
|
|
Configurations/EgkPositionnameConfiguration.cs |
|
|
2.2 DistanceSensor — 4-table pattern
Reference pattern: Similar to Kann but with Distance/DistanceName instead of Position/PositionName
| New File |
Maps To |
Key Columns |
Entities/DistancesensorSetup.cs |
tbl_distancesensor_setup |
Setupname, Mapping, Creator(30) |
Entities/DistancesensorMapping.cs |
tbl_distancesensor_mapping |
VersionsUID(FK), SetupUID(FK) |
Entities/DistancesensorDistance.cs |
tbl_distancesensor_distances |
SetupUID(FK), DistanceNumber, Distance(float), TolerancePlus/Minus(float), Creator(30) |
Entities/DistancesensorDistancename.cs |
tbl_distancesensor_distancename |
DistanceName(50), DistanceNumber, InUse(bit), Creator(30) |
Configurations/DistancesensorSetupConfiguration.cs |
|
|
Configurations/DistancesensorMappingConfiguration.cs |
|
|
Configurations/DistancesensorDistanceConfiguration.cs |
|
|
Configurations/DistancesensorDistancenameConfiguration.cs |
|
|
2.3 Pickit3D (3D Vision) — 2-table pattern
Reference pattern: XgxSetup.cs, XgxMapping.cs
| New File |
Maps To |
Key Columns |
Entities/Pickit3dSetup.cs |
tbl_pickit3d_setup |
Setupname, SetupID(bigint), ProductID(bigint), DetectionRetries(bigint), Creator(254) |
Entities/Pickit3dMapping.cs |
tbl_pickit3d_mapping |
VersionsUID(FK), SetupUID(FK) |
Configurations/Pickit3dSetupConfiguration.cs |
|
|
Configurations/Pickit3dMappingConfiguration.cs |
|
|
2.4 SchmalzSCPSi (Vacuum) — 2-table pattern
Reference pattern: XgxSetup.cs, XgxMapping.cs
| New File |
Maps To |
Key Columns |
Entities/SchmalzscpsiSetup.cs |
tbl_schmalzscpsi_setup |
Setupname, AirSavingControl(bit), AirSavingLevel(smallint), AirSavingHysteresis, VacuumLevel, VacuumHysteresis, BlowOffTime, BlowOffDelay, AirSavingReachedDelay, VacuumReachedDelay, RuntimeVacuumReached (all smallint), Creator(254) |
Entities/SchmalzscpsiMapping.cs |
tbl_schmalzscpsi_mapping |
VersionsUID(FK), SetupUID(FK) |
Configurations/SchmalzscpsiSetupConfiguration.cs |
|
|
Configurations/SchmalzscpsiMappingConfiguration.cs |
|
|
2.5 DbContext Update
| File |
Change |
ProductParameterDbContext.cs |
Add 14 new DbSet properties |
Version.cs (entity) |
Add 4 new mapping navigation collections |
Estimated: 28 new files + 2 modifications
Phase 3: Expanded Parameter Groups — Infrastructure Layer
Goal: Add missing Movement and Positionname tables for the existing EGP group.
3.1 EGP — 2 new tables
Reference pattern: KannMovement.cs, KannPositionname.cs
| New File |
Maps To |
Key Columns |
Entities/EgpMovement.cs |
tbl_egp_movements |
SetupUID(FK), PositionNumber, Position(float), MovementType(bit), GrippingForce, SoftGrip(bit), GrippingDirection(bit), MoveDirection(bit), Tolerance+/-(float), Creator(254) |
Entities/EgpPositionname.cs |
tbl_egp_positionname |
PositionName(50), PositionNumber, InUse(bit), Creator(254) |
Configurations/EgpMovementConfiguration.cs |
|
|
Configurations/EgpPositionnameConfiguration.cs |
|
|
Note: EGP movements have an extra SoftGrip(bit) field compared to EGK movements.
| File |
Change |
EgpSetup.cs |
Add ICollection<EgpMovement> navigation property |
ProductParameterDbContext.cs |
Add DbSet<EgpMovement>, DbSet<EgpPositionname> |
Note: tbl_mfconfig_setup / MfconfigSetup.cs already exists in code — no action needed.
Estimated: 4 new files + 2 modifications
Phase 4: New Database db_systemparameter — Full Stack
Goal: Implement the completely new db_systemparameter database (8 tables) across all architecture layers. These are standalone configuration tables — NOT parameter mapping pattern (no VersionsUID/SetupUID FKs).
4.1 Infrastructure — DbContext & Entities
Directory: Essert.MF.Infrastructure/Data/SystemParameter/
| New File |
Maps To |
Key Columns |
Context/SystemParameterDbContext.cs |
db_systemparameter |
8 DbSets |
Entities/Assemblyunit.cs |
tbl_assemblyunit |
Referencedirection(bit), Port(smallint), Endpos(float), Speed_Max(float), Force_Max(float) |
Entities/SpCmmt.cs |
tbl_cmmt |
Scaling(float), Positionlimits_Active(bit), Positionlimits_Min/Max(double), Speed_Fast/Slow/Hand(float), Offset_Referenceposition(float), Positiontolerance(float) |
Entities/Posaxis.cs |
tbl_posaxis |
Same schema as SpCmmt |
Entities/Positioningunit.cs |
tbl_positioningunit |
Position_Open/Stop/Index(float), Velocity_Index/Open_Stop(float), Holdingtorque_Index/Stop(tinyint unsigned) |
Entities/SpRobots.cs |
tbl_robots |
Speed_Fast/Slow(tinyint unsigned) |
Entities/Traystacker.cs |
tbl_traystacker |
Limit_StackheightTop/Cart(float), CountOfTray_Min(tinyint unsigned), CartLevelCorrection_Offset(float) |
Entities/SpUnspecified.cs |
tbl_unspecified |
(only UID, Timestamp, Creator, CRC) |
Entities/Wpcopener.cs |
tbl_wpcopener |
Position_Open/Release/PrePos/Tolerance(float) |
Configurations/AssemblyunitConfiguration.cs |
|
|
Configurations/SpCmmtConfiguration.cs |
|
|
Configurations/PosaxisConfiguration.cs |
|
|
Configurations/PositioningunitConfiguration.cs |
|
|
Configurations/SpRobotsConfiguration.cs |
|
|
Configurations/TraystackerConfiguration.cs |
|
|
Configurations/SpUnspecifiedConfiguration.cs |
|
|
Configurations/WpcopenerConfiguration.cs |
|
|
Naming convention: Sp prefix for entities that conflict with other databases (SpCmmt vs CmmtSetup, SpRobots vs robot entities, SpUnspecified vs Unspecified).
All entities share: UID(auto_increment), Mapping(tinyint), Timestamp(datetime, default 2025-01-01), Creator(varchar 254), CRC(bigint)
4.2 Infrastructure — Repository
| New File |
Description |
Repositories/SystemParameterRepository.cs |
CRUD for all 8 entity types + DTOs |
Mapping/SystemParameterMappingProfile.cs |
AutoMapper profile for system parameter DTOs |
4.3 Application — Port & Use Cases
| New File |
Description |
Ports/ISystemParameterRepository.cs |
CRUD interface for all 8 entity types |
UseCases/SystemParameter/ |
CQRS commands/queries/handlers (~8-16 files) |
4.4 API
| New File |
Description |
Controllers/SystemParameterController.cs |
REST endpoints for all 8 entity types |
4.5 Configuration
| File |
Change |
appsettings.json |
Add "SystemParameter" connection string |
appsettings.test.json (API.Rest) |
Add test connection string |
appsettings.test.json (Infrastructure.Tests) |
Add test connection string |
ServiceCollectionExtensions.cs |
Add AddDbContext<SystemParameterDbContext>(), register repo + handlers |
SchemaAnalyzerTool/Program.cs |
Add db_systemparameter connection string |
Estimated: ~30 new files + 5 modifications
Phase 5: Repositories + DI for All New/Missing Groups
Goal: Add repositories, DTOs, AutoMapper, DI, and CQRS handlers for new and missing parameter groups.
5.1 New Repositories
| New File |
Pattern |
Reference |
Repositories/EgkMappingRepository.cs |
4-table |
KannMappingRepository.cs |
Repositories/DistancesensorMappingRepository.cs |
4-table |
KannMappingRepository.cs |
Repositories/Pickit3dMappingRepository.cs |
2-table |
XgxMappingRepository.cs |
Repositories/SchmalzscpsiMappingRepository.cs |
2-table |
XgxMappingRepository.cs |
Repositories/LightingMappingRepository.cs |
2-table |
ElMappingRepository.cs |
Repositories/VsMappingRepository.cs |
3-table (clips) |
EsMappingRepository.cs |
5.2 Existing Repository Update
| File |
Change |
Repositories/EgpMappingRepository.cs |
Add Movement/Positionname CRUD methods |
5.3 AutoMapper
| File |
Change |
Mapping/ParameterMappingProfile.cs |
Add 6 new mapping groups (EGK, DistanceSensor, Pickit3D, SchmalzSCPSi, Lighting, VS) + update EGP |
5.4 DI Registration
| File |
Change |
ServiceCollectionExtensions.cs |
Register 6 new repos + CQRS handlers |
Estimated: ~8 new files + 3 modifications
Phase 6: Changelogs Gap (db_changelogs)
Goal: Add 23 missing changelog entities (49 mapped → 71 actual tables in database). Remove 1 stale entity (WpcGroup, handled in Phase 1).
6.1 Missing PP Changelogs — New Parameter Groups
| Entity |
Table |
Source Group |
PpDistancesensorSetup |
tbl_pp_distancesensor_setup |
DistanceSensor |
PpDistancesensorMapping |
tbl_pp_distancesensor_mapping |
DistanceSensor |
PpDistancesensorDistances |
tbl_pp_distancesensor_distances |
DistanceSensor |
PpEgkSetup |
tbl_pp_egk_setup |
EGK |
PpEgkMapping |
tbl_pp_egk_mapping |
EGK |
PpEgkMovement |
tbl_pp_egk_movements |
EGK |
PpEgkPositionname |
tbl_pp_egk_positionname |
EGK |
PpPickit3dSetup |
tbl_pp_pickit3d_setup |
Pickit3D |
PpPickit3dMapping |
tbl_pp_pickit3d_mapping |
Pickit3D |
PpSchmalzscpsiSetup |
tbl_pp_schmalzscpsi_setup |
SchmalzSCPSi |
PpSchmalzscpsiMapping |
tbl_pp_schmalzscpsi_mapping |
SchmalzSCPSi |
6.2 Missing PP Changelogs — Expanded/Existing Groups
| Entity |
Table |
Source Group |
PpEgpMovement |
tbl_pp_egp_movements |
EGP (expanded) |
PpEgpPositionname |
tbl_pp_egp_positionname |
EGP (expanded) |
PpElSetup |
tbl_pp_el_setup |
EL (existing, changelog missing) |
PpElMapping |
tbl_pp_el_mapping |
EL (existing, changelog missing) |
6.3 Missing SP Changelogs — System Parameters
| Entity |
Table |
SpAssemblyunit |
tbl_sp_assemblyunit |
SpCmmt |
tbl_sp_cmmt |
SpPosaxis |
tbl_sp_posaxis |
SpPositioningunit |
tbl_sp_positioningunit |
SpRobots |
tbl_sp_robots |
SpTraystacker |
tbl_sp_traystacker |
SpUnspecified |
tbl_sp_unspecified |
SpWpcopener |
tbl_sp_wpcopener |
6.4 DbContext Update
| File |
Change |
ChangelogsDbContext.cs |
Add 23 new DbSet properties |
Each changelog entity needs an entity file + configuration file (IEntityTypeConfiguration).
Estimated: 46 new files + 1 modification
6.5 Implementation Notes
Status: Complete
Actual impact: 46 new files + 1 modification (as estimated)
- 23 entity files created in
Data/Changelogs/Entities/ (11 PP new groups + 4 PP expanded/existing + 8 SP)
- 23 configuration files created in
Data/Changelogs/Configurations/
- ChangelogsDbContext.cs updated with 23 new DbSet properties
- Schema-accurate column mappings: All entity properties verified against live
db_changelogs INFORMATION_SCHEMA
- Column name casing: EGK tables use lowercase
Timestamp_log (matching actual DB schema), others use Timestamp_Log
- PpEgkMovement: No
Timestamp_Log column (matches DB — this table has no log timestamp)
- SP default datetimes: Use
'2025-01-01 12:00:00' (vs '2022-01-01 12:00:00' for PP tables)
- SpWpcopener.Position_Tolerance: Nullable
float? (only nullable column in SP changelogs)
- SpCmmt/SpPosaxis: Use
float for Positionlimits_Min/Max (matching changelog DB, vs double in db_systemparameter)
- SpPositioningunit: Holdingtorque fields are
float in changelogs (vs tinyint unsigned in db_systemparameter)
- Naming conflicts:
SpCmmt, SpRobots, SpUnspecified exist in both Changelogs.Entities and SystemParameter.Entities namespaces — no compile conflict due to separate namespaces
- Build:
dotnet build Essert.MF.Infrastructure — 0 errors, 0 warnings
Phase 7: API Controllers + Tests — COMPLETE
Goal: Add REST API controllers and comprehensive test coverage for all new functionality.
Status: Complete
Actual impact: 16 new files + 1 modified file, ~50 new tests
7.1 New API Controllers (7 files)
| New File |
Pattern |
Route |
Controllers/EgkParametersController.cs |
4-table (Setup+Mapping+Movement+Positionname) |
/api/v1/parameters/egk |
Controllers/DistancesensorParametersController.cs |
4-table (Setup+Mapping+Distance+Distancename) |
/api/v1/parameters/distancesensor |
Controllers/Pickit3dParametersController.cs |
2-table (Setup+Mapping) |
/api/v1/parameters/pickit3d |
Controllers/SchmalzscpsiParametersController.cs |
2-table (Setup+Mapping) |
/api/v1/parameters/schmalzscpsi |
Controllers/LightingParametersController.cs |
2-table (Setup+Mapping) |
/api/v1/parameters/lighting |
Controllers/VsParametersController.cs |
3-table (Sequence+Mapping+Clips) |
/api/v1/parameters/vs |
Controllers/SystemParameterController.cs |
Standalone CRUD (8 entity types) |
/api/v1/systemparameters |
7.2 Infrastructure Tests (2 files)
| New File |
Type |
Tests |
SystemParameterDbContextTests.cs |
DbContext connectivity |
10 tests (connectivity + all 8 table access) |
SystemParameterDataTests.cs |
Data query operations |
9 tests (data query for all 8 tables) |
7.3 REST API Controller Tests (7 files)
| New File |
Tests |
EgkParametersControllerTests.cs |
3 tests (GetSetup, CreateMapping, DeleteMapping) |
DistancesensorParametersControllerTests.cs |
3 tests |
Pickit3dParametersControllerTests.cs |
3 tests |
SchmalzscpsiParametersControllerTests.cs |
3 tests |
LightingParametersControllerTests.cs |
3 tests |
VsParametersControllerTests.cs |
3 tests |
SystemParameterControllerTests.cs |
10 tests (GET list for all 8 types, NotFound, filter) |
7.4 Tests — Updated Existing (1 file)
| File |
Change |
ProductParameterDbContextTests.cs |
Added 6 new tests for EGK, DistanceSensor, Pickit3D, SchmalzSCPSi, Lighting, and VS table accessibility |
7.5 Implementation Notes
- SystemParameterController follows a standalone CRUD pattern (not parameter-mapping), with separate endpoint groups for each of the 8 entity types: assemblyunits, cmmt, posaxis, positioningunits, robots, traystackers, unspecified, wpcopeners
- SystemParameterController injects
ISystemParameterRepository and casts to concrete SystemParameterRepository for typed convenience methods (matching existing pattern where controllers cast to concrete repository types)
- All parameter controllers follow the established CQRS pattern with
IQueryHandler/ICommandHandler for setup/mapping CRUD, plus direct repository calls for specialized operations (movements, distances, clips)
- 4-table controllers (EGK, DistanceSensor) include extra endpoint groups for sub-entities (movements/distances) using concrete repository casts
- REST API tests use
IntegrationTestBase with try/finally cleanup pattern (NOT TransactionalTestBase, as HTTP requests need committed data)
- Build:
dotnet build — 0 errors, 0 warnings across entire solution
Dependency Graph
Phase 1 (Cleanup) ─── independent, do first
│
Phase 2 (New PP Entities) ──┐
│ │
Phase 3 (Expanded EGP) ─────┤── can run in parallel
│ │
Phase 4 (SystemParameter) ──┘
│
▼
Phase 5 (Repos + DI) ── depends on Phase 2 + 3 + 4
│
▼
Phase 6 (Changelogs) ── depends on Phase 2 + 3 + 4 for entity schemas
│
▼
Phase 7 (API + Tests) ── depends on Phase 5 + 6
Scope Summary
| Phase |
Description |
Status |
New |
Modified |
Deleted |
Total |
| 1 |
Stale Cleanup |
Done |
0 |
16 |
27 |
43 |
| 2+3+4 |
PP Groups + EGP + SystemParam |
Done |
60 |
5 |
0 |
65 |
| 5+6 |
Repos + DI + Changelogs |
Done |
61 |
4 |
0 |
65 |
| 7 |
API + Tests |
Done |
16 |
1 |
0 |
17 |
| Total |
|
Complete |
137 |
26 |
27 |
190 |
Critical Reference Files
| File |
Used For |
Infrastructure/Repositories/ParameterMappingRepository.cs |
Generic base class for all parameter repos |
Infrastructure/Repositories/KannMappingRepository.cs |
4-table repository pattern (setup + mapping + movements + positionname) |
Infrastructure/Repositories/XgxMappingRepository.cs |
2-table repository pattern (setup + mapping) |
Infrastructure/Repositories/EsMappingRepository.cs |
3-table pattern with clips |
Infrastructure/DependencyInjection/ServiceCollectionExtensions.cs |
DI registration pattern |
Infrastructure/Data/ProductParameter/Context/ProductParameterDbContext.cs |
DbContext pattern |
Infrastructure/Mapping/ParameterMappingProfile.cs |
AutoMapper profile pattern |
Risks & Considerations
- Creator field length varies: EGK/Pickit3D/SchmalzSCPSi/SystemParameter use
varchar(254), DistanceSensor uses varchar(30). ~~Must match exactly per entity configuration.~~ Resolved — all configurations verified against live INFORMATION_SCHEMA.
- Entity naming conflicts: ~~db_systemparameter has
tbl_cmmt, tbl_robots, tbl_unspecified — use Sp prefix.~~ Resolved — Sp prefix applied (SpCmmt, SpRobots, SpUnspecified) in both SystemParameter and Changelogs namespaces.
- db_systemparameter is NOT parameter-mapping pattern: ~~Requires a new repository interface.~~ Resolved —
ISystemParameterRepository with generic CRUD + SystemParameterRepository with typed convenience methods. Controller casts to concrete type (matching existing pattern).
- Battery/Downtime removal scope: ~~Must grep for ALL references.~~ Resolved in Phase 1 — all references found and removed across REST, GraphQL, Application, Infrastructure, and test layers.
- AutoMapper profile growth:
ParameterMappingProfile.cs grew to ~880 lines. Consider splitting in future if readability degrades.
- Test database availability: All integration tests require running MariaDB server with
db_systemparameter database created.
Verification Plan
dotnet build — solution compiles without errorsdotnet test Essert.MF.Domain.Tests — domain tests passdotnet test Essert.MF.Application.Tests — application tests passdotnet test --filter "FullyQualifiedName~Infrastructure.Tests.Unit" — unit tests passdotnet test --filter "FullyQualifiedName~Infrastructure.Tests.Integration" — integration tests pass (requires DB)dotnet test Essert.MF.API.Rest.Tests — API tests pass (requires DB)- Run SchemaAnalyzerTool — validate all entities match DB schema
- MariaDB MCP — spot-check new entity counts match table row counts
Completion Summary
All 7 phases complete. The database schema gap has been fully closed:
| Database |
Before |
After |
Change |
| db_productparameter |
40 entities |
63 entities |
+23 new parameter tables |
| db_systemparameter |
0 entities |
8 entities |
Entirely new database |
| db_changelogs |
49 entities |
72 entities |
+23 new changelog entities |
| db_essert |
5 entities |
3 entities |
-2 stale removed (Battery, Downtime) |
Architecture layers touched: Domain, Application, Infrastructure, REST API, GraphQL API, Tests
Key deliverables:
- 7 new REST API controllers with full CRUD endpoints
- 7 new repository implementations (6 parameter-mapping + 1 standalone)
- 23 new changelog entities with EF configurations
- ~50 new integration/API tests
- ~190 total files created/modified/deleted across all phases