Nexus — Use Case: EDR v6 Data Consolidation

Section 01 -- Overview [M]

Field	Value
Use Case ID	UC-D1
Client Reference [M]	PMG -- Industrial data consolidation platform
System Name [M]	Enhanced Data Repository v6 (EDR v6)
Domain [M]	Industrial Engineering / Data Consolidation / Equipment Registry
One-Line Description [M]	Multi-source data ingestion, rule-based entity consolidation with confidence scoring, and FACT table generation for industrial equipment registries
Status [M]	In Development -- 85% complete, MVP target achieved. PostgreSQL migration done. Test coverage 93.17%.
Target Delivery [M]	MVP completed June 2025. Production deployment pending AWS infrastructure (Milestone 2).

Summary [M]

EDR v6 is a complete Python rewrite of a legacy data consolidation system for managing industrial equipment and process data. The system ingests data from multiple heterogeneous sources (Excel, CSV, Access databases, SQL Server, MySQL, PostgreSQL), normalizes it through source adapters, performs rule-based entity consolidation with exact/fuzzy/pattern matching, generates confidence scores, and outputs consolidated FACT tables (FACT000 raw consolidated, FACT001 processed output).

The business targets are: 35% improvement in consolidation accuracy over the legacy system, 60% reduction in processing time, and multi-database backend support. The system exposes consolidated data through a Flask REST API and a React dashboard ("Wormwood EDR Command Center") with real-time WebSocket metrics.

Nexus orchestrates the consolidation pipeline: source adapter selection, validation rule execution, entity matching, confidence scoring, and FACT table generation. The pipeline is fully configurable -- new data sources are added via adapter configuration without code changes to the core engine.

Section 02 -- Actors [M]

Role [M]	Description [M]	Access Level [D]	Frequency [D]
Super Admin	Full system access including configuration, user management, and system-level operations.	all_access, system_config	As needed -- system configuration
Admin	User management, role assignment, data deletion authority. Inherits from Super Admin (restricted).	manage_users, manage_roles, delete	Weekly -- user admin
Manager	Data Operations Manager. Approves consolidation runs, assigns work, monitors dashboards. Inherits from Admin (restricted).	approve, assign	Daily -- pipeline oversight
Analyst	Quality Assurance Specialist. Reviews consolidation results, investigates anomalies, writes and tests rules.	write, analyze	Daily -- primary data operator
Viewer	Read-only access to consolidated data, dashboards, and reports.	read	Daily -- data consumers

Section 03 -- User Stories [M/D]

Backlog contains 168 story points across 4 milestones. Sourced from E:/repos/PMG_workbench/PMG/EDRv6/MASTER_PROJECT_BACKLOG.md.

Actor: Analyst

P1 [M]: Configure and execute data source ingestion from Excel, CSV, Access, and SQL databases
P1 [M]: Define and test consolidation rules (exact match, fuzzy match, pattern match)
P1 [M]: Review entity consolidation results with confidence scores
P1 [M]: Generate FACT tables from consolidated entities (FACT000 to FACT001 pipeline)
P2 [D]: Investigate anomalies detected by the monitoring system
P2 [D]: Export consolidated data in multiple formats

Actor: Manager

P1 [M]: Monitor consolidation pipeline health via real-time dashboard
P1 [M]: Approve consolidation runs before FACT table generation
P2 [D]: View metrics: processing time, accuracy scores, source health
P2 [D]: Assign data quality tasks to analysts

Actor: Admin

P1 [M]: Manage user accounts with 5-role RBAC hierarchy
P1 [M]: Configure data source connections (connection strings, auth, adapters)
P2 [D]: Monitor system health (API, database, WebSocket connections)
P2 [D]: Access audit logs for all data modifications

Milestone Status

Milestone	Points	Status
M1: Frontend + API	168	85% -- auth integration blocked
M2: AWS Cloud Infrastructure	80	Planned
M3: Environment & Testing	60	Planned
M4: Data Processing Completion	40	In Progress

Section 04 -- System Flows [M/D]

Flow: Data Ingestion Pipeline [M]

Trigger [M]: Scheduled batch run or manual trigger by Analyst via dashboard.

#	Step [M]	Actor [M]	Input [M]	Output [D]	Decision [M]
1	Select source adapter	System	Source configuration (type, connection string)	Adapter instance (CSV/Excel/MDB/SQL)	Adapter type validation
2	Extract raw data	System	Source connection + query/file path	Raw records with provenance metadata	Connection success/failure
3	Normalize and validate	System	Raw records + validation rules	Standardized entity objects with typed properties	Validation pass/fail per record
4	Incremental load with conflict detection	System	Normalized entities vs existing store	New/updated/conflicted entity sets	Conflict resolution rules apply
5	Store with provenance	System	Resolved entities	Persisted entities with source_id, timestamps, confidence	--

Result [M]: Source data ingested, normalized, validated, and stored with full provenance tracking.

Volume [M]: Up to 10,000 records per batch. Multiple sources can be ingested in parallel.

Flow: Entity Consolidation Pipeline [M]

Trigger [M]: After ingestion completes, or manual trigger by Manager/Analyst.

#	Step [M]	Actor [M]	Input [M]	Output [D]	Decision [M]
1	Load consolidation rules	System	Rule definitions (exact/fuzzy/pattern)	Active rule set	--
2	Entity matching	System	Source entities + rule set	Match candidates with scores (0.0-1.0)	Match confidence threshold
3	Merge/link entities	System	Confirmed matches above threshold	Consolidated entity with merged properties	Property conflict resolution (highest confidence wins)
4	Generate FACT000 (raw consolidated)	System	All consolidated entities	FACT000 table output	--
5	Process FACT001 (final output)	System	FACT000 + processing rules	FACT001 table -- production-ready output	Quality gate (minimum confidence threshold)

Result [M]: Multi-source entities consolidated into authoritative FACT tables with confidence scoring and full lineage.

SLA [D]: 60% faster than legacy system. Full consolidation run completes within operational window.

Flow: Real-Time Monitoring [D]

Trigger [D]: Continuous -- WebSocket connections stream metrics to dashboard.

Dashboard displays: pipeline status (running/idle/error), entity counts by source, consolidation accuracy metrics, anomaly detection alerts, system resource utilization. Manager receives alerts on pipeline failures or accuracy drops below threshold.

Section 05 -- Data Model [M/D]

Entities

Entity [M]	Description [M]	Key Fields [M]	States [D]	Relationships [D]
Source	Data source definition (Excel, CSV, MDB, SQL connection)	id, name, type, connection_config, status, last_ingestion	Active, Inactive, Error	has_many Entities (via source_id)
Entity (BaseEntity)	A record from any source, normalized to standard schema	id (UUID), entity_type, source_id (FK), properties{}, created_at, updated_at	Raw, Validated, Consolidated, Published	belongs_to Source, has_many PropertyValues, has_many Relationships
PropertyValue	A typed property attached to an entity with provenance	name, value, source_id, confidence (0.0-1.0), created_at, updated_at	Current, Historical	belongs_to Entity
Rule	Consolidation/matching rule definition	id, name, type (exact/fuzzy/pattern), config{}, priority, active	Active, Disabled, Draft	applied_to Entity matching
Workflow	Processing pipeline definition (ingestion or consolidation)	id, name, steps[], schedule, last_run, status	Idle, Running, Error, Complete	references Sources, produces FACT tables
Class	Entity type classification (equipment categories)	id, name, code, parent_class_id, property_schema{}	Active, Deprecated	classifies Entities, hierarchical (self-ref)
Action	Operational action/task record	id, type, target_entity_id, performed_by, result, timestamp	Pending, Complete, Failed	references Entity, performed_by User

FACT Tables [M]

Table	Purpose	Source
FACT000	Raw consolidated output -- all matched entities merged, no filtering	Entity consolidation pipeline output
FACT001	Processed production output -- quality-gated, ready for downstream consumption	FACT000 + processing rules + quality filters

Business Rules [M/D]

ID	Rule [M]	Trigger [D]	On Violation [D]
BR-01	Entity matching requires minimum confidence score before merge	During consolidation step 2-3	Low-confidence matches flagged for analyst review
BR-02	Property conflicts resolved by highest-confidence source value	During entity merge	Conflict logged; lower-confidence value preserved as historical
BR-03	FACT001 output must meet minimum quality threshold	During FACT001 generation	Records below threshold excluded; logged in quality report
BR-04	All data modifications require audit trail entry	On any CRUD operation	Operation blocked if audit write fails
BR-05	Source adapters must validate data types before ingestion	During normalization (ingestion step 3)	Invalid records rejected with error details; ingestion continues

Section 06 -- Screens [D]

Screen [D]	Actor [M]	Purpose [D]	Key Elements [D]
Command Center Dashboard	Manager, Analyst	Real-time pipeline monitoring and KPIs	Pipeline status indicators, entity counts by source, accuracy metrics, anomaly alerts, WebSocket-driven live updates
Data Source Management	Admin, Analyst	Configure and monitor data sources	Source list with status, connection editor, ingestion history, error logs, manual trigger button
Rule Management	Analyst	Define, test, and manage consolidation rules	Rule editor (exact/fuzzy/pattern), test runner with sample data, rule priority ordering, activation toggle
FACT Table Viewer	Manager, Analyst, Viewer	Browse and export consolidated FACT table data	Tabular view with search/filter, confidence score column, source lineage drill-down, export (CSV/JSON)
Entity Detail	Analyst	Inspect individual entity with full provenance	Property grid with confidence per value, source attribution, match history, relationship graph
Single Action Execution	Admin, Analyst	Execute one-off operations on entities	Action type selector, target entity picker, execution result panel
System Monitoring	Admin	API health, database stats, WebSocket connections	Service health indicators, response time graphs, connection counts, error rate
User Administration	Admin	Manage users and RBAC roles	User list, role assignment (5-tier hierarchy), permission matrix, audit log viewer

Section 07 -- Integrations [M]

System [M]	Direction [M]	Data [M]	Format [D]	Frequency [M]	Fallback [D]
Excel Files	In	Equipment data sheets, property spreadsheets	Source adapter (openpyxl/pandas)	On-demand (file upload or path reference)	Reject file with validation error report
CSV Files	In	Bulk data exports from legacy systems	Source adapter (pandas)	Batch scheduled or on-demand	Partial ingest; log rejected rows
Access Databases (MDB)	In	Legacy equipment registries	Source adapter (PyODBC)	Batch scheduled	Connection retry; alert on repeated failure
SQL Server / MySQL / PostgreSQL	In	Live operational databases	Source adapter (pymssql/pymysql/psycopg2)	Scheduled incremental sync	Queue pending changes; retry on connection restore
AER (Augmented Equipment Register)	In (EDR receives)	Equipment records, tags, properties, classification codes	REST API (JSON), OAuth 2.0	Real-time (<5s critical), Batch (15 min), On-demand	Queue in Redis; retry with backoff; alert on 3+ failures
Downstream Consumers	Out	FACT001 consolidated data, reports	REST API (JSON), CSV/Excel export	On-demand via API	Cache last-known-good; serve stale with warning

Section 08 -- Infrastructure [D]

Layer	Choice [D]	Reason [D]
Language	Python 3.9+	Complete rewrite from legacy; strong data processing ecosystem (pandas, numpy)
API Framework	Flask 2.x with SQLAlchemy 1.4.x ORM	Lightweight, proven for REST APIs; SQLAlchemy handles multi-DB support
Database (Primary)	PostgreSQL	Migrated from MSSQL May 2025 -- 12% query performance improvement, $7,400/yr savings
Database (Supported)	SQLite (dev), Access, MySQL, MSSQL	Multi-DB support for source ingestion; SQLite for local development
Frontend	React 18 + Bootstrap ("Wormwood EDR Command Center")	Real-time dashboard with WebSocket support; cyberpunk design system
Real-time	WebSocket	Live metric streaming to dashboard; anomaly detection alerts
Auth	JWT tokens with 5-role RBAC hierarchy	Stateless API auth; role inheritance model
Hosting (Planned)	AWS: ECS/Fargate, RDS PostgreSQL, ALB, CodePipeline	Scalable container orchestration; managed database; CI/CD automation
Local Dev	Flask dev server on port 30080	Standard development workflow

Constraints [M]

Legacy compatibility: Must ingest from Access databases (MDB via PyODBC) -- client has extensive legacy data in this format
Performance: 60% reduction in processing time vs legacy system (measurable KPI)
Accuracy: 35% improvement in consolidation accuracy (measurable KPI)
Browser support: [RISK] Client environments may include older IE versions

Environments [D]

Environment	Purpose
Development	Local Flask + SQLite, hot reload
Staging	AWS ECS with RDS PostgreSQL (planned)
Production	AWS ECS/Fargate + RDS + ALB (planned)

Section 09 -- Critical Path [D]

#	Item [D]	Depends On	Duration [D]	Blocker Risk [D]
1	Authentication system integration (JWT + Security Team approval)	Security Team sign-off	2 weeks	HIGH -- currently blocked (RAID-API-2025-0510)
2	Testing remediation (P0 critical -- 75 story points)	#1 (auth needed for integration tests)	3 weeks	HIGH -- frontend completions rejected without tests
3	AWS Terraform infrastructure (VPC, RDS, ECS, ALB)	--	3 weeks	MEDIUM -- standard AWS patterns
4	CI/CD pipeline (CodePipeline, blue/green deploy)	#3	2 weeks	Low
5	Production deployment + load testing	#2, #3, #4	2 weeks	MEDIUM -- first production run

Milestones [D]

Milestone	Reached When	Target Date
M1: MVP API + Dashboard	Core CRUD + consolidation pipeline + dashboard working	Achieved June 2025
M2: AWS Infrastructure	Terraform deployed, ECS running, RDS provisioned	Pending -- blocked on auth
M3: Production Ready	Full test coverage, CI/CD operational, load tested	M2 + 4 weeks
M4: Data Processing Complete	All source adapters tested with real data, FACT pipeline validated	M3 + 2 weeks

Section 10 -- What Is Needed [M]

#	Item [M]	From [M]	Blocking [M]	Status [M]
1	Security Team sign-off on authentication implementation	Client Security Team	Yes -- blocks auth integration, which blocks testing, which blocks deployment	Open (RAID-API-2025-0510)
2	AWS account access with permissions for Terraform provisioning	Client IT / Cloud Team	Yes -- blocks Milestone 2 entirely	Open
3	Production data sample for load testing and validation	Client Data Operations	No -- only blocks final validation phase	Open
4	Git commit evidence for frontend work items	Development team coordination	Yes -- multiple work items rejected without evidence	Open (process failure)

Section 11 -- Acceptance Criteria [M/D]

Definition of Done [D]

Criterion [D]	Verified By [D]
Consolidation accuracy >= 35% improvement over legacy	A/B comparison against legacy output on identical input data
Processing time >= 60% faster than legacy	Timed comparison on production-scale dataset
All source adapters operational (Excel, CSV, MDB, SQL)	E2E ingestion test per adapter type with real data
FACT000 to FACT001 pipeline produces valid output	FACT table validation against known-good reference
93%+ test coverage maintained	pytest coverage report
Dashboard renders real-time metrics via WebSocket	Manual verification in supported browsers
5-role RBAC enforced at API level	Security test per role boundary

Success Metrics (30/60/90 days) [M]

Metric [M]	Target [M]	Measured By [D]
Consolidation accuracy	35% improvement over legacy baseline	Entity match precision/recall on test dataset
Processing throughput	60% reduction in end-to-end time	Pipeline completion time logs
System uptime	99.5% during operational hours	CloudWatch availability metrics
Data quality	FACT001 records with confidence >= 0.8	Confidence score distribution in FACT001

Section 12 -- Risks [D]

#	Risk [D]	Likelihood [D]	Impact [D]	Mitigation [D]
R1	Authentication implementation blocked by Security Team -- cascading delay to testing and deployment	High (currently blocked)	High -- blocks production	Escalate; implement local auth fallback for testing; decouple auth from pipeline tests
R2	Frontend work rejected due to lack of git commit evidence (coordination failure)	High (already occurring)	Medium -- rework cost	Enforce commit-per-work-item policy; automated commit verification in CI
R3	Browser compatibility -- older IE in client environments	Medium	Medium -- dashboard unusable for some users	React polyfills; progressive enhancement; document minimum browser versions
R4	Data inconsistency in staging tables (RAID-DATA-2025-0509)	Medium (investigating)	Medium -- incorrect consolidation output	Root cause analysis; add data integrity checks to ingestion pipeline
R5	Server capacity insufficient for production load	Low (assumption, validating)	High -- production failure	Load test before go-live; auto-scaling in ECS/Fargate configuration

Open Questions [M]

#	Question [M]	Impacts [D]	Resolution [M]
Q1	When will Security Team approve authentication implementation?	Blocks testing, CI/CD, and production deployment	Open -- escalation required
Q2	What is the minimum browser version for client environments?	Dashboard technology choices (polyfills vs modern-only)	Open -- needs client IT confirmation
Q3	What is the production data volume for capacity planning?	AWS instance sizing, auto-scaling thresholds	Open -- needs client data ops input

Section 13 -- Cooperator Brief [D]

Field	Value
Scope [D]	Multi-source data consolidation engine with Flask API, React dashboard, PostgreSQL primary DB, 5 source adapters, FACT table pipeline, 5-role RBAC. AWS production deployment pending.
Stack [D]	Python 3.9+ / Flask 2.x / SQLAlchemy 1.4.x / PostgreSQL / React 18 / WebSocket / JWT auth / AWS (ECS, RDS, ALB, CodePipeline)
Timeline [D]	MVP achieved. Remaining: auth unblock + 2w testing + 3w AWS infra + 2w deployment = ~7 weeks from auth blocker resolution.
Dependencies [D]	Security Team auth approval (critical blocker), AWS account access, production data sample
Interfaces [D]	Receives: data from Excel/CSV/MDB/SQL sources + AER equipment sync. Delivers: FACT tables via REST API, real-time metrics via WebSocket, CSV/JSON exports.
Quality Bar [D]	93%+ test coverage (pytest). 35% accuracy improvement. 60% speed improvement. RBAC enforced. Full audit trail. Load tested.
Handover [D]	Deployed AWS infrastructure + API docs (OpenAPI) + source adapter guide + rule authoring guide + dashboard user manual + deployment runbook

Source Authority

Document Sources

This use case was compiled from the EDR v6 repository at E:/repos/PMG_workbench/PMG/EDRv6. Key source files:

README.md -- Project overview and objectives (Section 01)
MASTER_PROJECT_BACKLOG.md -- 4-milestone backlog with story points (Section 03, 09)
authorization.py -- 5-role RBAC hierarchy (Section 02)
app/models/ -- 6 core entity types (Section 05)
src/adapters/ -- Source adapter framework (Section 07)
src/consolidation/fact_pipeline.py -- FACT000/001 pipeline (Section 04)
docs/RAID_LOG.md -- Risks, decisions, dependencies (Section 10, 12)
docs/architecture/ -- Technical architecture decisions (Section 08)

EDR v6 implementation status: 85% complete. MVP achieved. PostgreSQL migration done (May 2025). Test coverage at 93.17%. Production deployment blocked on Security Team auth approval.