Projects-plastic/IMPLEMENTATION_GUIDE.md

Data Persistence Implementation Guide

Overview

This guide will help you implement crash recovery and data persistence for your KPI tracking system. After implementation, the system will:

• Automatically save timing data to the database every 5 seconds
• Survive crashes and reboots
• Prompt for session restoration on startup
• Maintain accurate production metrics across restarts

Files Created

1. Database Schema

• create_session_state_table.sql - SQL to create the session_state table

2. Modified Function Nodes

• modified_machine_cycles.js - Updated Machine cycles function with DB sync
• modified_work_order_buttons.js - Updated Work Order buttons function with DB sync

3. New Recovery Functions

• startup_recovery_query.js - Queries for previous session on startup
• process_recovery_response.js - Processes recovery data and prompts user
• restore_session.js - Restores a previous session
• start_fresh.js - Starts fresh and deactivates old session

---

Implementation Steps

Step 1: Create Database Table

1. Connect to your MySQL/MariaDB database
2. Execute the SQL from create_session_state_table.sql:

mysql -u your_username -p your_database < create_session_state_table.sql

Or manually run the SQL in your database client.

Verify table creation:

SHOW TABLES LIKE 'session_state';
DESCRIBE session_state;

---

Step 2: Update Machine Cycles Function

1. Open Node-RED editor

2. Find the "Machine cycles" function node

3. Replace its code with contents from modified_machine_cycles.js

4. Important: Add a third output to this function node:

   • Double-click the function node
   • In the "Outputs" field, change from 2 to 3
   • Click "Done"

5. Wire the new output (output 3):

   • Connect it to a new MySQL node (call it "Session State DB")
   • Configure it to use the same database as your work_orders table

---

Step 3: Update Work Order Buttons Function

1. Find the "Work Order buttons" function node

2. Replace its code with contents from modified_work_order_buttons.js

3. Important: Add a fifth output to this function node:

   • Double-click the function node
   • In the "Outputs" field, change from 4 to 5
   • Click "Done"

4. Wire the new output (output 5):

   • Connect it to the same "Session State DB" MySQL node from Step 2

---

Step 4: Add Startup Recovery Flow

Create a new flow called "Session Recovery" with the following nodes:

A. Startup Query

1. Inject node (triggers on startup)

   • Set to "Inject once after 0.1 seconds"
   • Name: "Startup Trigger"

2. Function node - "Query Previous Session"

   • Code: Copy from startup_recovery_query.js
   • Outputs: 1

3. MySQL node - "Session State DB"

   • Use same database config
   • Connects to function output

B. Process Response

4. Function node - "Process Recovery Response"

   • Code: Copy from process_recovery_response.js
   • Outputs: 2
   • Output 1: Database commands (deactivate stale sessions)
   • Output 2: User prompts

5. Connect Output 1 to another MySQL node

6. Connect Output 2 to a UI notification node or Dashboard notification

C. User Actions

7. Function node - "Restore Session"

   • Code: Copy from restore_session.js
   • Outputs: 2
   • Triggered when user clicks "Restore Session" button

8. Function node - "Start Fresh"

   • Code: Copy from start_fresh.js
   • Outputs: 2
   • Triggered when user clicks "Start Fresh" button

9. Connect both outputs from these functions:

   • Output 1: MySQL node (database updates)
   • Output 2: Dashboard notifications

---

Step 5: Update Dashboard for Recovery Prompts

You'll need to add UI elements to handle recovery prompts. Two options:

Option A: Simple Notification (Quick)

Use Node-RED dashboard notification nodes to display recovery messages.

Option B: Modal Dialog (Recommended)

Create a dashboard modal with:

• Session details display
• "Restore Session" button → triggers restore_session.js
• "Start Fresh" button → triggers start_fresh.js

---

Flow Diagram

[Startup Inject] → [Query Previous Session] → [MySQL]
                                                  ↓
                    [Process Recovery Response] ←┘
                            ↓                ↓
                    [MySQL: Deactivate]  [UI Notification]
                                                  ↓
                                         [User Decision]
                                         ↙            ↘
                            [Restore Session]  [Start Fresh]
                                    ↓                   ↓
                            [MySQL + UI]        [MySQL + UI]

---

Testing the Implementation

Test 1: Normal Operation

1. Deploy the updated flows
2. Start a work order
3. Click START button
4. Run some machine cycles
5. Check the session_state table:

   SELECT * FROM session_state ORDER BY updated_at DESC LIMIT 1;
   

6. Verify data is being saved every ~5 seconds

Test 2: Crash Recovery (Simulated)

1. Start a work order and run several cycles
2. Stop Node-RED: sudo systemctl stop nodered
3. Verify session state in database is preserved
4. Restart Node-RED: sudo systemctl start nodered
5. You should see recovery prompt with previous session details
6. Click "Restore Session"
7. Verify:
   • Cycle count is correct
   • Work order is active
   • Operating time continues from where it left off

Test 3: Stale Session

1. Insert an old session record (24+ hours old):

   UPDATE session_state
   SET updated_at = updated_at - (25 * 60 * 60 * 1000)
   WHERE is_active = 1;
   

2. Restart Node-RED
3. Verify it detects stale session and starts fresh

Test 4: Fresh Start

1. Start a work order and run some cycles
2. Restart Node-RED
3. When recovery prompt appears, click "Start Fresh"
4. Verify:
   • Old session is deactivated in database
   • All counters reset to 0
   • Ready for new work order

---

Monitoring and Troubleshooting

Check Session State

-- View active sessions
SELECT * FROM session_state WHERE is_active = 1;

-- View session history
SELECT session_id, active_work_order_id, cycle_count,
       FROM_UNIXTIME(created_at/1000) as created,
       FROM_UNIXTIME(updated_at/1000) as updated
FROM session_state
ORDER BY updated_at DESC
LIMIT 10;

Debug Mode

Add debug nodes after each function to monitor:

• Session sync messages
• Recovery prompt data
• Restoration status

Common Issues

Issue: Database sync not working

• Check MySQL node is connected correctly
• Verify database permissions (INSERT, UPDATE)
• Check Node-RED debug log for SQL errors

Issue: Recovery prompt not appearing

• Check inject node is set to trigger on startup
• Verify session_state table has active records
• Check debug output from "Process Recovery Response"

Issue: Restored data incorrect

• Verify JSON serialization in active_work_order_data
• Check for single quote escaping in SQL queries
• Verify timestamp formats (should be milliseconds)

---

Performance Notes

• Database writes are throttled to every 5 seconds during normal operation

• Forced immediate writes occur on:

  • Work order start/complete
  • Production START/STOP buttons
  • Target reached (scrap prompt)
  • State changes

• Expected database load: ~12 writes per minute during active production

---

Next Steps

After implementing data persistence, you can proceed with:

• Issue 4: Intelligent downtime categorization (planned vs unplanned stops)
• Issue 5: Session management and pattern tracking
• Issue 2: Cycle count capping
• Issue 3: Hardware irregularity tracking

---

Support

If you encounter issues:

1. Check Node-RED debug log: journalctl -u nodered -f
2. Check database logs for SQL errors
3. Verify all wiring connections in the flow
4. Test SQL queries manually in MySQL client

For questions about implementation, refer to:

• /home/mdares/.node-red/optimization_prompt.txt
• This implementation guide