199 lines
7.4 KiB
Plaintext
199 lines
7.4 KiB
Plaintext
# Backend Optimization Requirements for KPI Tracking System
|
|
|
|
## Context
|
|
This Node-RED application tracks manufacturing KPIs (OEE, Availability, Performance, Quality) for a production line. The system monitors machine cycles, work orders, and production metrics. These optimizations address data persistence, session management, and accurate downtime tracking.
|
|
|
|
## Critical Issues to Address
|
|
|
|
### Issue 1: Data Persistence (Option A - Database Storage)
|
|
**Problem:** Timing data and context variables are lost on crash or reboot, making it impossible to restore sessions.
|
|
|
|
**Solution:** Implement database persistence for timing and context data
|
|
- Create a new table in the database called `session_state` or `global_context`
|
|
- Store the following timing data:
|
|
- `productionStartTime`
|
|
- `operatingTime`
|
|
- `lastUpdateTime`
|
|
- `trackingEnabled` status
|
|
- `cycleCount`
|
|
- `activeWorkOrder` details
|
|
- Any other critical global/flow context variables
|
|
|
|
**Implementation Requirements:**
|
|
1. On system startup, check if there's existing session data in the database
|
|
2. Restore session state from database if found
|
|
3. Continuously sync critical timing variables to database (implement throttled writes to avoid database overload - e.g., update every 5-10 seconds or on state changes)
|
|
4. On clean shutdown or work order completion, ensure final state is written
|
|
5. On crash recovery, prompt user if they want to restore the previous session or start fresh
|
|
|
|
**Database Schema Example:**
|
|
```sql
|
|
CREATE TABLE session_state (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
session_id TEXT UNIQUE,
|
|
production_start_time INTEGER,
|
|
operating_time REAL,
|
|
last_update_time INTEGER,
|
|
tracking_enabled INTEGER,
|
|
cycle_count INTEGER,
|
|
active_work_order_id TEXT,
|
|
created_at INTEGER,
|
|
updated_at INTEGER,
|
|
is_active INTEGER DEFAULT 1
|
|
);
|
|
```
|
|
|
|
### Issue 2: Cycle Count Capping
|
|
**Problem:** Need to prevent cycle counts from exceeding reasonable limits.
|
|
|
|
**Solution:** Implement a cap at 100 cycles
|
|
- When cycle count reaches 100, either:
|
|
- Stop accepting new cycles and alert the operator
|
|
- OR automatically prompt for work order completion
|
|
- Add validation in the cycle counting logic
|
|
- Display warning in UI when approaching the cap (e.g., at 90+ cycles)
|
|
|
|
### Issue 3: Hardware Irregularity Handling
|
|
**Problem:** Real hardware will be used and may have irregular behavior/timing.
|
|
|
|
**Solution:** Design system to handle and track irregularities
|
|
- Log irregular cycle times (cycles that deviate significantly from average)
|
|
- Track unexpected state changes
|
|
- Store metadata about irregular events for later analysis
|
|
- This data will help diagnose:
|
|
- Unplanned production stops
|
|
- Slower than normal cycles
|
|
- Patterns in machine behavior
|
|
|
|
**Implementation Requirements:**
|
|
1. Calculate rolling average cycle time
|
|
2. Flag cycles that are >20% slower/faster than average
|
|
3. Store flagged cycles with timestamps in database
|
|
4. Create a separate table for anomaly tracking:
|
|
```sql
|
|
CREATE TABLE cycle_anomalies (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
work_order_id TEXT,
|
|
cycle_number INTEGER,
|
|
expected_time REAL,
|
|
actual_time REAL,
|
|
deviation_percent REAL,
|
|
timestamp INTEGER,
|
|
notes TEXT
|
|
);
|
|
```
|
|
|
|
### Issue 4: Intelligent Downtime Categorization
|
|
**Problem:** Not all stops should count as downtime (e.g., lunch breaks vs machine issues).
|
|
|
|
**Solution:** Implement stop reason prompt and categorization
|
|
- When STOP button is clicked, immediately show a prompt asking "Why are you stopping?"
|
|
- Provide categorized options:
|
|
- **Planned Stops** (don't affect downtime):
|
|
- Lunch break
|
|
- Scheduled break
|
|
- Shift change
|
|
- Planned maintenance
|
|
- **Unplanned Stops** (count as downtime, affect Availability):
|
|
- Machine malfunction
|
|
- Material shortage
|
|
- Quality issue
|
|
- Operator error
|
|
- Other (with text input)
|
|
|
|
**Implementation Requirements:**
|
|
1. Modify "Work Order buttons" function to intercept "stop" command
|
|
2. Display modal/prompt with categorized options
|
|
3. Store stop reason in database with timestamp
|
|
4. Only accumulate downtime for unplanned stops
|
|
5. Create stop_events table:
|
|
```sql
|
|
CREATE TABLE stop_events (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
work_order_id TEXT,
|
|
stop_time INTEGER,
|
|
resume_time INTEGER,
|
|
duration INTEGER,
|
|
reason_category TEXT, -- 'planned' or 'unplanned'
|
|
reason_detail TEXT,
|
|
affects_availability INTEGER,
|
|
notes TEXT
|
|
);
|
|
```
|
|
|
|
### Issue 5: Session Management and Pattern Tracking
|
|
**Problem:** Need to track production patterns and identify when sessions start/stop.
|
|
|
|
**Solution:** Create new session each time RESUME is clicked
|
|
- Each START/RESUME creates a new production session
|
|
- Track session metadata for pattern analysis
|
|
- Add time tracking columns to identify:
|
|
- When production occurs
|
|
- Unexpected breaks
|
|
- Patterns (e.g., breaks at certain hours)
|
|
- Session duration trends
|
|
|
|
**Implementation Requirements:**
|
|
1. Generate unique session_id on each START/RESUME
|
|
2. Store session metadata in new table:
|
|
```sql
|
|
CREATE TABLE production_sessions (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
session_id TEXT UNIQUE,
|
|
work_order_id TEXT,
|
|
start_time INTEGER,
|
|
end_time INTEGER,
|
|
duration INTEGER,
|
|
cycles_completed INTEGER,
|
|
reason_for_start TEXT, -- 'initial_start', 'resume_after_planned', 'resume_after_unplanned'
|
|
reason_for_end TEXT, -- 'planned_stop', 'unplanned_stop', 'work_order_complete'
|
|
operating_time REAL,
|
|
downtime REAL,
|
|
created_at INTEGER
|
|
);
|
|
```
|
|
|
|
3. Add time tracking columns to work_orders table:
|
|
```sql
|
|
ALTER TABLE work_orders ADD COLUMN total_sessions INTEGER DEFAULT 0;
|
|
ALTER TABLE work_orders ADD COLUMN total_operating_time REAL DEFAULT 0;
|
|
ALTER TABLE work_orders ADD COLUMN total_downtime REAL DEFAULT 0;
|
|
ALTER TABLE work_orders ADD COLUMN avg_session_duration REAL DEFAULT 0;
|
|
```
|
|
|
|
4. On RESUME, log the previous session and start a new one
|
|
5. Track cumulative metrics across all sessions for the work order
|
|
6. Build analytics to identify:
|
|
- Peak production times
|
|
- Frequent break times
|
|
- Session duration patterns
|
|
- Correlation between session length and performance
|
|
|
|
## Implementation Priority
|
|
Since these are backend fixes affecting ~1% of Availability KPI accuracy, and UI presentation is the immediate priority:
|
|
|
|
**Phase 1 (Later):** Implement Issues 1, 4, 5 (data persistence, stop categorization, session tracking)
|
|
**Phase 2 (Later):** Implement Issues 2, 3 (cycle capping, irregularity tracking)
|
|
**Phase 3 (Later):** Build analytics dashboard for pattern analysis
|
|
|
|
## Key Functions to Modify
|
|
1. **Machine cycles function** - Add database sync for timing data
|
|
2. **Work Order buttons function** - Add session management, stop reason prompt
|
|
3. **Database initialization** - Add new tables
|
|
4. **Startup routine** - Add session recovery logic
|
|
5. **Availability calculation** - Exclude planned stops from downtime calculation
|
|
|
|
## Expected Outcome
|
|
- Data persists through crashes/reboots
|
|
- Accurate downtime vs planned stop tracking
|
|
- Session-based production history for pattern analysis
|
|
- Foundation for predictive maintenance and efficiency optimization
|
|
- Ability to identify and mitigate unexpected production issues
|
|
|
|
## Notes
|
|
- All timing should use Unix timestamps (milliseconds) for consistency
|
|
- Implement database connection pooling for performance
|
|
- Add error handling for database operations
|
|
- Consider adding a "notes" field to most tables for operator comments
|
|
- Build admin interface to review historical sessions and patterns
|