🏗️ LEONI Quality Management System

-- Consolidated indexing strategy for optimized performance -- ANALYSIS table indexes CREATE INDEX idx_analysis_date ON analysis(analysis_date); CREATE INDEX idx_qrqc_form_gin ON analysis USING GIN (qrqc_form); CREATE INDEX idx_qrqc_status ON analysis USING GIN ((qrqc_form->'metadata'->>'status')); CREATE INDEX idx_qrqc_tri_decision ON analysis USING GIN ((qrqc_form->'tri_data'->>'decisionRetouche')); CREATE INDEX idx_qrqc_actions_status ON analysis USING GIN ((qrqc_form->'actions_securisation'->>'overall_status')); -- VALUES table JSONB indexes CREATE INDEX idx_values_quality_data ON values USING GIN (quality_data); CREATE INDEX idx_values_scrap_rate ON values USING BTREE ((quality_data->'scrap'->'rate')::DECIMAL); CREATE INDEX idx_values_rework_rate ON values USING BTREE ((quality_data->'rework'->'rate')::DECIMAL); CREATE INDEX idx_values_defect_rate_ppm ON values USING BTREE ((quality_data->'defects'->'rate_ppm')::DECIMAL);

// QRQC Analysis - Update Consolidated Form Section PUT /api/analysis/:id/form { "section": "tri_data", "data": { "magasinProduitFini": {"bon": 15, "mauvais": 2}, "paletteFinLigne": {"bon": 12, "mauvais": 5}, "totals": {"totalBon": 27, "totalMauvais": 7}, "decisionRetouche": false } } // Updates: UPDATE analysis SET qrqc_form = jsonb_set(qrqc_form, '{tri_data}', $1) // QRQC Analysis - Update Pre-Analyse Section PUT /api/analysis/:id/form { "section": "pre_analyse", "data": { "operatorId": "OP123", "postMachine": "Station 5 - Assembly Line", "defectDate": "2025-06-18", "defectHour": "14:30" } } // QRQC Analysis - Complete Form Retrieval (Perfect for Archives) GET /api/analysis/:id Response: { "id": 123, "analysis_date": "2025-06-18", "defect_id": 456, "production_line_id": 1, "inspector_id": 789, "qrqc_form": { "document_info": { "qrqc_reference_id": "DEF-1703123456789" }, "caracterisation": { "defect_description": "Detailed description" }, "personnel_notification": { "informed_personnel": ["operateur", "team-speaker"] }, "tri_data": { "totals": {"totalBon": 27, "totalMauvais": 7} }, "pre_analyse": { "operatorId": "OP123" }, "actions_securisation": { "action1": {"responsable": "Quality Supervisor"} }, "analysis_data": { "why1": {"field1": "Root cause analysis"} }, "corrective_actions": { "occurrence": {"pilote": "Production Manager"} }, "verification_tracking": { "teams": [{"team": "Team 1", "occurrence": "No"}] }, "metadata": { "status": "OPEN", "priority": "MEDIUM" } }, "is_archived": false, "created_at": "2025-06-18T10:30:00Z" } "responsable": "Maintenance Team", "date": "2025-06-25", "status": "PLANNED" } } } // Response { "success": true, "message": "Section updated successfully", "analysisId": 123, "section": "actions_securisation", "autoCalculated": { "overall_status": "PLANNED", "next_due_date": "2025-06-20" } }

// Quality Metrics Entry - JSONB Structure POST /api/values/quality-metrics { "production_line_id": 1, "shift": "Morning", "date": "2025-06-18", "quality_data": { "scrap": { "total_scrap": 15, "total_production": 1000 }, "rework": { "total_rework": 8, "total_production": 1000 }, "defects": { "total_defects": 3, "total_production": 1000 } }, "notes": "Morning shift quality metrics" } // Database Operation: INSERT INTO values with automatic rate calculation // Trigger calculates: scrap_rate=1.5%, rework_rate=0.8%, defect_rate_ppm=3000 // Get Current Quality Metrics for Production Line GET /api/values/production-line/1/current Response: { "success": true, "data": { "id": 123, "production_line_id": 1, "shift": "Morning", "date": "2025-06-18", "quality_data": { "scrap": {"total_scrap": 15, "total_production": 1000, "rate": 1.5}, "rework": {"total_rework": 8, "total_production": 1000, "rate": 0.8}, "defects": {"total_defects": 3, "total_production": 1000, "rate_ppm": 3000.0} }, "notes": "Morning shift quality metrics" } } // Quality Trends Analysis GET /api/values/trends?production_line_id=1&days=7 Response: { "success": true, "data": { "scrap_trend": [1.2, 1.5, 1.3, 1.8, 1.4, 1.6, 1.5], "rework_trend": [0.8, 0.9, 0.7, 1.1, 0.8, 0.9, 0.8], "defect_ppm_trend": [2500, 3000, 2800, 3200, 2900, 3100, 3000], "trend_analysis": { "scrap_direction": "stable", "rework_direction": "stable", "defects_direction": "increasing" } } }

class AnalysisRepository { async create(analysisData) { const query = ` INSERT INTO analysis ( analysis_date, defect_id, production_line_id, inspector_id, qrqc_form, is_archived ) VALUES ($1, $2, $3, $4, $5, $6) RETURNING * `; return await this.db.query(query, [ analysisData.date, analysisData.defectId, analysisData.productionLineId, analysisData.inspectorId, JSON.stringify(analysisData.qrqcForm), // Consolidated form data false ]); } async updateFormSection(id, section, data) { const query = ` UPDATE analysis SET qrqc_form = jsonb_set(qrqc_form, $2, $3) WHERE id = $1 RETURNING * `; return await this.db.query(query, [ id, `{${section}}`, JSON.stringify(data) ]); } async getCompleteForm(id) { const query = ` SELECT id, analysis_date, defect_id, production_line_id, inspector_id, qrqc_form, is_archived, created_at FROM analysis WHERE id = $1 `; return await this.db.query(query, [id]); } }

class QRQCValidatorFactory { static createValidator(section) { const validators = { 'document_info': DocumentInfoValidator, 'caracterisation': CaracterisationValidator, 'tri_data': TriDataValidator, 'analysis_data': AnalysisDataValidator, 'corrective_actions': CorrectiveActionValidator, 'verification_tracking': VerificationValidator, 'metadata': MetadataValidator }; const ValidatorClass = validators[section]; if (!ValidatorClass) { throw new Error(`Unknown section: ${section}`); } return new ValidatorClass(); } } class TriDataValidator { validate(data) { const required = ['magasinProduitFini', 'paletteFinLigne', 'ligneAssemblage']; const errors = []; for (const stage of required) { if (!data[stage] || typeof data[stage] !== 'object') { errors.push(`Missing or invalid ${stage} data`); } } return { isValid: errors.length === 0, errors }; } }

class ErrorHandler { static async handleDatabaseError(error, context) { const errorMap = { '23505': 'Duplicate entry detected', '23503': 'Referenced record not found', '23514': 'Data validation failed', '42P01': 'Table does not exist' }; const message = errorMap[error.code] || 'Database operation failed'; logger.error('Database Error', { code: error.code, message: error.message, context: context, timestamp: new Date().toISOString() }); return { success: false, error: message, code: error.code, timestamp: new Date().toISOString() }; } }

class CacheService { constructor() { this.redis = new Redis(process.env.REDIS_URL); this.defaultTTL = 300; // 5 minutes } async get(key) { try { const cached = await this.redis.get(key); return cached ? JSON.parse(cached) : null; } catch (error) { console.error('Cache get error:', error); return null; } } async set(key, data, ttl = this.defaultTTL) { try { await this.redis.setex(key, ttl, JSON.stringify(data)); } catch (error) { console.error('Cache set error:', error); } } async getProductionLines() { const cacheKey = 'production_lines_active'; let lines = await this.get(cacheKey); if (!lines) { lines = await db.query( 'SELECT * FROM production_lines WHERE is_active = true' ); await this.set(cacheKey, lines, 86400); // 24 hours } return lines; } }

-- Automated permission management CREATE OR REPLACE FUNCTION update_user_permissions() RETURNS TRIGGER AS $$ BEGIN DELETE FROM user_permissions WHERE user_id = NEW.id; CASE NEW.role WHEN 'AQL_AGENT' THEN INSERT INTO user_permissions (user_id, can_add_defect, can_view_qrqc) VALUES (NEW.id, TRUE, TRUE); WHEN 'QUALITY_TECHNICIAN' THEN INSERT INTO user_permissions (user_id, can_add_defect, can_view_qrqc, can_add_qrqc, can_edit_qrqc, can_perform_5s_audit) VALUES (NEW.id, TRUE, TRUE, TRUE, TRUE, TRUE); WHEN 'QUALITY_MANAGER' THEN -- Full permissions for quality manager INSERT INTO user_permissions (user_id, can_system_admin, can_user_management) VALUES (NEW.id, TRUE, TRUE); END CASE; RETURN NEW; END; $$ LANGUAGE plpgsql;

// Application Health Check app.get('/health', async (req, res) => { const health = { status: 'OK', timestamp: new Date().toISOString(), uptime: process.uptime(), environment: process.env.NODE_ENV, version: process.env.APP_VERSION, checks: { database: await checkDatabase(), redis: await checkRedis(), diskSpace: await checkDiskSpace(), memory: process.memoryUsage() } }; const hasFailures = Object.values(health.checks) .some(check => check.status === 'ERROR'); res.status(hasFailures ? 503 : 200).json(health); }); // Performance Monitoring const performanceMonitor = { trackApiResponse: (req, res, next) => { const start = Date.now(); res.on('finish', () => { const duration = Date.now() - start; console.log(`${req.method} ${req.path} - ${res.statusCode} - ${duration}ms`); // Send metrics to monitoring service metrics.timing('api.response_time', duration, { method: req.method, path: req.path, status: res.statusCode }); }); next(); } };