np-dms/lcbp3
0
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
main
lcbp3/specs/03-Data-and-Storage/0.md
admin b79a3ff68b
All checks were successful
Build and Deploy / deploy (push) Successful in 1m33s
Details
260304:1716 20260304:1700 update n8n
2026-03-04 17:16:42 +07:00

45 KiB
Raw Permalink Blame History

ผมจะสร้างไฟล์ JSON สำหรับ n8n Free Plan (ไม่ใช้ Environment Variables) และปรับปรุงเอกสารคู่มือให้สอดคล้อง โดยใช้ Static Parameters และ Set Node แทนการอ่านจาก Environment

📝 ไฟล์ n8n.workflow (Free Plan Compatible)

{
  "name": "LCBP3 Migration Workflow v1.8.0",
  "meta": {
    "instanceId": "lcbp3-migration-free"
  },
  "settings": {
    "executionOrder": "v1"
  },
  "nodes": [
    {
      "parameters": {
        "rule": {
          "interval": [
            {
              "field": "cronExpression",
              "expression": "0 22 * * *"
            }
          ]
        }
      },
      "id": "trigger-schedule",
      "name": "Schedule Trigger (22:00)",
      "type": "n8n-nodes-base.scheduleTrigger",
      "typeVersion": 1.1,
      "position": [0, 0],
      "notes": "เริ่มรันอัตโนมัติทุกวันเวลา 22:00"
    },
    {
      "parameters": {
        "jsCode": "// ============================================\n// CONFIGURATION - แก้ไขค่าที่นี่\n// ============================================\nconst CONFIG = {\n  // Ollama Settings\n  OLLAMA_HOST: 'http://192.168.20.100:11434',\n  OLLAMA_MODEL_PRIMARY: 'llama3.2:3b',\n  OLLAMA_MODEL_FALLBACK: 'mistral:7b-instruct-q4_K_M',\n  \n  // Backend Settings\n  BACKEND_URL: 'https://api.np-dms.work',\n  MIGRATION_TOKEN: 'Bearer YOUR_MIGRATION_TOKEN_HERE',\n  \n  // Batch Settings\n  BATCH_SIZE: 10,\n  BATCH_ID: 'migration_20260226',\n  DELAY_MS: 2000,\n  \n  // Thresholds\n  CONFIDENCE_HIGH: 0.85,\n  CONFIDENCE_LOW: 0.60,\n  MAX_RETRY: 3,\n  FALLBACK_THRESHOLD: 5,\n  \n  // Paths\n  STAGING_PATH: '/share/np-dms/staging_ai',\n  LOG_PATH: '/share/np-dms/n8n/migration_logs',\n  \n  // Database\n  DB_HOST: '192.168.1.100',\n  DB_PORT: 3306,\n  DB_NAME: 'lcbp3_production',\n  DB_USER: 'migration_bot',\n  DB_PASSWORD: 'YOUR_DB_PASSWORD_HERE'\n};\n\n// Store in global workflow data\n$workflow.staticData = $workflow.staticData || {};\n$workflow.staticData.config = CONFIG;\n\nreturn [{ json: { config_loaded: true, timestamp: new Date().toISOString(), config: CONFIG } }];"
      },
      "id": "config-setter",
      "name": "Set Configuration",
      "type": "n8n-nodes-base.code",
      "typeVersion": 2,
      "position": [200, 0],
      "notes": "กำหนดค่า Configuration ทั้งหมด - แก้ไขที่นี่ก่อนรัน"
    },
    {
      "parameters": {
        "method": "GET",
        "url": "={{$workflow.staticData.config.BACKEND_URL}}/api/meta/categories",
        "sendHeaders": true,
        "headerParameters": {
          "parameters": [
            {
              "name": "Authorization",
              "value": "={{$workflow.staticData.config.MIGRATION_TOKEN}}"
            }
          ]
        },
        "options": {
          "timeout": 10000
        }
      },
      "id": "preflight-categories",
      "name": "Fetch Categories",
      "type": "n8n-nodes-base.httpRequest",
      "typeVersion": 4.1,
      "position": [400, 0],
      "notes": "ดึง Categories จาก Backend"
    },
    {
      "parameters": {
        "method": "GET",
        "url": "={{$workflow.staticData.config.BACKEND_URL}}/api/health",
        "options": {
          "timeout": 5000
        }
      },
      "id": "preflight-health",
      "name": "Check Backend Health",
      "type": "n8n-nodes-base.httpRequest",
      "typeVersion": 4.1,
      "position": [400, 200],
      "notes": "ตรวจสอบ Backend พร้อมใช้งาน",
      "onError": "continueErrorOutput"
    },
    {
      "parameters": {
        "jsCode": "const fs = require('fs');\nconst config = $workflow.staticData.config;\n\n// Check file mount\ntry {\n  const files = fs.readdirSync(config.STAGING_PATH);\n  if (files.length === 0) throw new Error('staging_ai is empty');\n  \n  // Check write permission to log path\n  fs.writeFileSync(`${config.LOG_PATH}/.preflight_ok`, new Date().toISOString());\n  \n  // Store categories\n  const categories = $input.first().json.categories || \n    ['Correspondence','RFA','Drawing','Transmittal','Report','Other'];\n  $workflow.staticData.systemCategories = categories;\n  \n  return [{ json: { \n    preflight_ok: true, \n    file_count: files.length,\n    system_categories: categories,\n    timestamp: new Date().toISOString()\n  }}];\n} catch (err) {\n  throw new Error(`Pre-flight check failed: ${err.message}`);\n}"
      },
      "id": "preflight-check",
      "name": "File Mount Check",
      "type": "n8n-nodes-base.code",
      "typeVersion": 2,
      "position": [600, 0],
      "notes": "ตรวจสอบ File System และเก็บ Categories"
    },
    {
      "parameters": {
        "operation": "executeQuery",
n        "host": "={{$workflow.staticData.config.DB_HOST}}",
        "port": "={{$workflow.staticData.config.DB_PORT}}",
        "database": "={{$workflow.staticData.config.DB_NAME}}",
        "user": "={{$workflow.staticData.config.DB_USER}}",
        "password": "={{$workflow.staticData.config.DB_PASSWORD}}",
        "query": "SELECT last_processed_index, status FROM migration_progress WHERE batch_id = '{{$workflow.staticData.config.BATCH_ID}}' LIMIT 1",
        "options": {}
      },
      "id": "checkpoint-read",
      "name": "Read Checkpoint",
      "type": "n8n-nodes-base.mySql",
      "typeVersion": 2.4,
      "position": [800, 0],
      "notes": "อ่านตำแหน่งล่าสุดที่ประมวลผล",
      "onError": "continueErrorOutput"
    },
    {
      "parameters": {
        "operation": "toData",
        "binaryProperty": "data",
        "options": {
          "sheetName": "Sheet1"
        }
      },
      "id": "excel-reader",
      "name": "Read Excel",
      "type": "n8n-nodes-base.spreadsheetFile",
      "typeVersion": 2,
      "position": [800, 200],
      "notes": "อ่านไฟล์ Excel รายการเอกสาร"
    },
    {
      "parameters": {
        "jsCode": "const checkpoint = $input.first().json[0] || { last_processed_index: 0, status: 'NEW' };\nconst startIndex = checkpoint.last_processed_index || 0;\nconst config = $workflow.staticData.config;\n\nconst allItems = $('Read Excel').all()[0].json.data || [];\nconst remaining = allItems.slice(startIndex);\nconst currentBatch = remaining.slice(0, config.BATCH_SIZE);\n\n// Encoding Normalization\nconst normalize = (str) => {\n  if (!str) return '';\n  return String(str).normalize('NFC').trim();\n};\n\nreturn currentBatch.map((item, i) => ({\n  json: {\n    document_number: normalize(item.document_number || item['Document Number']),\n    title: normalize(item.title || item.Title || item['Subject']),\n    legacy_number: normalize(item.legacy_number || item['Legacy Number']),\n    excel_revision: item.revision || item.Revision || 1,\n    original_index: startIndex + i,\n    batch_id: config.BATCH_ID,\n    file_name: `${normalize(item.document_number)}.pdf`\n  }\n}));"
      },
      "id": "batch-processor",
      "name": "Process Batch + Encoding",
      "type": "n8n-nodes-base.code",
      "typeVersion": 2,
      "position": [1000, 0],
      "notes": "ตัด Batch + Normalize UTF-8"
    },
    {
      "parameters": {
        "jsCode": "const fs = require('fs');\nconst path = require('path');\nconst config = $workflow.staticData.config;\n\nconst items = $input.all();\nconst validated = [];\nconst errors = [];\n\nfor (const item of items) {\n  const docNum = item.json.document_number;\n  \n  // Sanitize filename\n  const safeName = path.basename(String(docNum).replace(/[^a-zA-Z0-9\\-_.]/g, '_')).normalize('NFC');\n  const filePath = path.resolve(config.STAGING_PATH, `${safeName}.pdf`);\n  \n  // Path traversal check\n  if (!filePath.startsWith(config.STAGING_PATH)) {\n    errors.push({\n      ...item,\n      json: { ...item.json, error: 'Path traversal detected', error_type: 'SECURITY', file_exists: false }\n    });\n    continue;\n  }\n  \n  try {\n    if (fs.existsSync(filePath)) {\n      const stats = fs.statSync(filePath);\n      validated.push({\n        ...item,\n        json: { ...item.json, file_exists: true, file_size: stats.size, file_path: filePath }\n      });\n    } else {\n      errors.push({\n        ...item,\n        json: { ...item.json, error: `File not found: ${safeName}.pdf`, error_type: 'FILE_NOT_FOUND', file_exists: false }\n      });\n    }\n  } catch (err) {\n    errors.push({\n      ...item,\n      json: { ...item.json, error: err.message, error_type: 'FILE_ERROR', file_exists: false }\n    });\n  }\n}\n\n// Output 0: Validated, Output 1: Errors\nreturn [validated, errors];"
      },
      "id": "file-validator",
      "name": "File Validator",
      "type": "n8n-nodes-base.code",
      "typeVersion": 2,
      "position": [1200, 0],
      "notes": "ตรวจสอบไฟล์ PDF มีอยู่จริง + Sanitize path"
    },
    {
      "parameters": {
        "operation": "executeQuery",
        "host": "={{$workflow.staticData.config.DB_HOST}}",
        "port": "={{$workflow.staticData.config.DB_PORT}}",
        "database": "={{$workflow.staticData.config.DB_NAME}}",
        "user": "={{$workflow.staticData.config.DB_USER}}",
        "password": "={{$workflow.staticData.config.DB_PASSWORD}}",
        "query": "SELECT is_fallback_active, recent_error_count FROM migration_fallback_state WHERE batch_id = '{{$workflow.staticData.config.BATCH_ID}}' LIMIT 1",
        "options": {}
      },
      "id": "fallback-check",
      "name": "Check Fallback State",
      "type": "n8n-nodes-base.mySql",
      "typeVersion": 2.4,
      "position": [1400, -200],
      "notes": "ตรวจสอบว่าต้องใช้ Fallback Model หรือไม่",
      "onError": "continueErrorOutput"
    },
    {
      "parameters": {
        "jsCode": "const config = $workflow.staticData.config;\nconst fallbackState = $input.first().json[0] || { is_fallback_active: false, recent_error_count: 0 };\n\nconst isFallback = fallbackState.is_fallback_active || false;\nconst model = isFallback ? config.OLLAMA_MODEL_FALLBACK : config.OLLAMA_MODEL_PRIMARY;\n\nconst systemCategories = $workflow.staticData.systemCategories || \n  ['Correspondence','RFA','Drawing','Transmittal','Report','Other'];\n\nconst items = $('File Validator').all();\n\nreturn items.map(item => {\n  const systemPrompt = `You are a Document Controller for a large construction project.\nYour task is to validate document metadata.\nYou MUST respond ONLY with valid JSON. No explanation, no markdown, no extra text.\nIf there are no issues, \"detected_issues\" must be an empty array [].`;\n\n  const userPrompt = `Validate this document metadata and respond in JSON:\n\nDocument Number: ${item.json.document_number}\nTitle: ${item.json.title}\nExpected Pattern: [ORG]-[TYPE]-[SEQ] e.g. \"TCC-COR-0001\"\nCategory List (MUST match system enum exactly): ${JSON.stringify(systemCategories)}\n\nRespond ONLY with this exact JSON structure:\n{\n  \"is_valid\": true | false,\n  \"confidence\": 0.0 to 1.0,\n  \"suggested_category\": \"<one from Category List>\",\n  \"detected_issues\": [\"<issue1>\"],\n  \"suggested_title\": \"<corrected title or null>\"\n}`;\n\n  return {\n    json: {\n      ...item.json,\n      active_model: model,\n      is_fallback: isFallback,\n      system_categories: systemCategories,\n      ollama_payload: {\n        model: model,\n        prompt: `${systemPrompt}\\n\\n${userPrompt}`,\n        stream: false,\n        format: 'json'\n      }\n    }\n  };\n});"
      },
      "id": "prompt-builder",
      "name": "Build AI Prompt",
      "type": "n8n-nodes-base.code",
      "typeVersion": 2,
      "position": [1400, 0],
      "notes": "สร้าง Prompt โดยใช้ Categories จาก System"
    },
    {
      "parameters": {
        "method": "POST",
        "url": "={{$workflow.staticData.config.OLLAMA_HOST}}/api/generate",
        "sendBody": true,
        "specifyBody": "json",
        "jsonBody": "={{ $json.ollama_payload }}",
        "options": {
          "timeout": 30000
        }
      },
      "id": "ollama-call",
      "name": "Ollama AI Analysis",
      "type": "n8n-nodes-base.httpRequest",
      "typeVersion": 4.1,
      "position": [1600, 0],
      "notes": "เรียก Ollama วิเคราะห์เอกสาร"
    },
    {
      "parameters": {
        "jsCode": "const items = $input.all();\nconst parsed = [];\nconst parseErrors = [];\n\nfor (const item of items) {\n  try {\n    let raw = item.json.response || '';\n    \n    // Clean markdown\n    raw = raw.replace(/```json/gi, '').replace(/```/g, '').trim();\n    const result = JSON.parse(raw);\n    \n    // Schema Validation\n    if (typeof result.is_valid !== 'boolean') throw new Error('is_valid must be boolean');\n    if (typeof result.confidence !== 'number' || result.confidence < 0 || result.confidence > 1) {\n      throw new Error('confidence must be float 0.0-1.0');\n    }\n    if (!Array.isArray(result.detected_issues)) throw new Error('detected_issues must be array');\n    \n    // Enum Validation\n    const systemCategories = item.json.system_categories || [];\n    if (!systemCategories.includes(result.suggested_category)) {\n      throw new Error(`Category \"${result.suggested_category}\" not in system enum`);\n    }\n    \n    parsed.push({\n      ...item,\n      json: { ...item.json, ai_result: result, parse_error: null }\n    });\n  } catch (err) {\n    parseErrors.push({\n      ...item,\n      json: {\n        ...item.json,\n        ai_result: null,\n        parse_error: err.message,\n        raw_ai_response: item.json.response,\n        error_type: 'AI_PARSE_ERROR'\n      }\n    });\n  }\n}\n\nreturn [parsed, parseErrors];"
      },
      "id": "json-parser",
      "name": "Parse & Validate AI Response",
      "type": "n8n-nodes-base.code",
      "typeVersion": 2,
      "position": [1800, 0],
      "notes": "Parse JSON + Validate Schema + Enum Check"
    },
    {
      "parameters": {
        "operation": "executeQuery",
        "host": "={{$workflow.staticData.config.DB_HOST}}",
        "port": "={{$workflow.staticData.config.DB_PORT}}",
        "database": "={{$workflow.staticData.config.DB_NAME}}",
        "user": "={{$workflow.staticData.config.DB_USER}}",
        "password": "={{$workflow.staticData.config.DB_PASSWORD}}",
        "query": "INSERT INTO migration_fallback_state (batch_id, recent_error_count, is_fallback_active) VALUES ('{{$workflow.staticData.config.BATCH_ID}}', 1, FALSE) ON DUPLICATE KEY UPDATE recent_error_count = recent_error_count + 1, is_fallback_active = CASE WHEN recent_error_count + 1 >= {{$workflow.staticData.config.FALLBACK_THRESHOLD}} THEN TRUE ELSE is_fallback_active END, updated_at = NOW()",
        "options": {}
n      },
      "id": "fallback-update",
      "name": "Update Fallback State",
      "type": "n8n-nodes-base.mySql",
      "typeVersion": 2.4,
      "position": [2000, 200],
      "notes": "เพิ่ม Error count และตรวจสอบ Fallback threshold"
    },
    {
      "parameters": {
        "jsCode": "const config = $workflow.staticData.config;\nconst items = $('Parse & Validate AI Response').all();\n\nconst autoIngest = [];\nconst reviewQueue = [];\nconst rejectLog = [];\nconst errorLog = [];\n\nfor (const item of items) {\n  if (item.json.parse_error || !item.json.ai_result) {\n    errorLog.push(item);\n    continue;\n  }\n  \n  const ai = item.json.ai_result;\n  \n  // Revision Drift Protection (ถ้ามีข้อมูลจาก DB)\n  if (item.json.current_db_revision !== undefined) {\n    const expectedRev = item.json.current_db_revision + 1;\n    if (parseInt(item.json.excel_revision) !== expectedRev) {\n      reviewQueue.push({\n        ...item,\n        json: { ...item.json, review_reason: `Revision drift: Excel=${item.json.excel_revision}, Expected=${expectedRev}` }\n      });\n      continue;\n    }\n  }\n  \n  // Confidence Routing\n  if (ai.confidence >= config.CONFIDENCE_HIGH && ai.is_valid === true) {\n    autoIngest.push(item);\n  } else if (ai.confidence >= config.CONFIDENCE_LOW) {\n    reviewQueue.push({\n      ...item,\n      json: { ...item.json, review_reason: `Confidence ${ai.confidence.toFixed(2)} < ${config.CONFIDENCE_HIGH}` }\n    });\n  } else {\n    rejectLog.push({\n      ...item,\n      json: { ...item.json, reject_reason: ai.is_valid === false ? 'AI marked invalid' : `Confidence ${ai.confidence.toFixed(2)} < ${config.CONFIDENCE_LOW}` }\n    });\n  }\n}\n\n// Output 0: Auto, 1: Review, 2: Reject, 3: Error\nreturn [autoIngest, reviewQueue, rejectLog, errorLog];"
      },
      "id": "confidence-router",
      "name": "Confidence Router",
      "type": "n8n-nodes-base.code",
      "typeVersion": 2,
      "position": [2000, 0],
      "notes": "แยกตาม Confidence: Auto(≥0.85) / Review(≥0.60) / Reject(<0.60)"
    },
    {
      "parameters": {
        "method": "POST",
        "url": "={{$workflow.staticData.config.BACKEND_URL}}/api/correspondences/import",
        "sendHeaders": true,
        "headerParameters": {
          "parameters": [
            {
              "name": "Authorization",
              "value": "={{$workflow.staticData.config.MIGRATION_TOKEN}}"
            },
            {
              "name": "Idempotency-Key",
              "value": "={{$json.document_number}}:{{$workflow.staticData.config.BATCH_ID}}"
            }
          ]
        },
        "sendBody": true,
        "specifyBody": "json",
        "jsonBody": "={\n  \"document_number\": \"{{$json.document_number}}\",\n  \"title\": \"{{$json.ai_result.suggested_title || $json.title}}\",\n  \"category\": \"{{$json.ai_result.suggested_category}}\",\n  \"source_file_path\": \"{{$json.file_path}}\",\n  \"ai_confidence\": {{$json.ai_result.confidence}},\n  \"ai_issues\": {{JSON.stringify($json.ai_result.detected_issues)}},\n  \"migrated_by\": \"SYSTEM_IMPORT\",\n  \"batch_id\": \"{{$workflow.staticData.config.BATCH_ID}}\",\n  \"details\": {\n    \"legacy_number\": \"{{$json.legacy_number}}\"\n  }\n}",
        "options": {
          "timeout": 30000
        }
      },
      "id": "backend-import",
      "name": "Import to Backend",
      "type": "n8n-nodes-base.httpRequest",
      "typeVersion": 4.1,
      "position": [2200, -200],
      "notes": "ส่งข้อมูลเข้า LCBP3 Backend พร้อม Idempotency-Key"
    },
    {
      "parameters": {
        "jsCode": "const item = $input.first();\nconst shouldCheckpoint = item.json.original_index % 10 === 0;\n\nreturn [{\n  json: {\n    ...item.json,\n    should_update_checkpoint: shouldCheckpoint,\n    checkpoint_index: item.json.original_index,\n    import_status: 'success',\n    timestamp: new Date().toISOString()\n  }\n}];"
      },
      "id": "checkpoint-flag",
      "name": "Flag Checkpoint",
      "type": "n8n-nodes-base.code",
      "typeVersion": 2,
      "position": [2400, -200],
      "notes": "กำหนดว่าจะบันทึก Checkpoint หรือไม่ (ทุก 10 records)"
    },
    {
      "parameters": {
        "operation": "executeQuery",
        "host": "={{$workflow.staticData.config.DB_HOST}}",
        "port": "={{$workflow.staticData.config.DB_PORT}}",
        "database": "={{$workflow.staticData.config.DB_NAME}}",
        "user": "={{$workflow.staticData.config.DB_USER}}",
        "password": "={{$workflow.staticData.config.DB_PASSWORD}}",
        "query": "INSERT INTO migration_progress (batch_id, last_processed_index, status) VALUES ('{{$workflow.staticData.config.BATCH_ID}}', {{$json.checkpoint_index}}, 'RUNNING') ON DUPLICATE KEY UPDATE last_processed_index = {{$json.checkpoint_index}}, updated_at = NOW()",
        "options": {}
      },
      "id": "checkpoint-save",
      "name": "Save Checkpoint",
      "type": "n8n-nodes-base.mySql",
      "typeVersion": 2.4,
      "position": [2600, -200],
      "notes": "บันทึกความคืบหน้าลง Database"
    },
    {
      "parameters": {
        "operation": "executeQuery",
        "host": "={{$workflow.staticData.config.DB_HOST}}",
        "port": "={{$workflow.staticData.config.DB_PORT}}",
        "database": "={{$workflow.staticData.config.DB_NAME}}",
        "user": "={{$workflow.staticData.config.DB_USER}}",
        "password": "={{$workflow.staticData.config.DB_PASSWORD}}",
        "query": "INSERT INTO migration_review_queue (document_number, title, original_title, ai_suggested_category, ai_confidence, ai_issues, review_reason, status, created_at) VALUES ('{{$json.document_number}}', '{{$json.ai_result.suggested_title || $json.title}}', '{{$json.title}}', '{{$json.ai_result.suggested_category}}', {{$json.ai_result.confidence}}, '{{JSON.stringify($json.ai_result.detected_issues)}}', '{{$json.review_reason}}', 'PENDING', NOW()) ON DUPLICATE KEY UPDATE status = 'PENDING', review_reason = '{{$json.review_reason}}', created_at = NOW()",
        "options": {}
      },
      "id": "review-queue-insert",
      "name": "Insert Review Queue",
      "type": "n8n-nodes-base.mySql",
      "typeVersion": 2.4",
      "position": [2200, 0],
      "notes": "บันทึกรายการที่ต้องตรวจสอบโดยคน (ไม่สร้าง Correspondence)"
    },
    {
      "parameters": {
        "jsCode": "const fs = require('fs');\nconst item = $input.first();\nconst config = $workflow.staticData.config;\n\nconst csvPath = `${config.LOG_PATH}/reject_log.csv`;\nconst header = 'timestamp,document_number,title,reject_reason,ai_confidence,ai_issues\\n';\nconst esc = (s) => `\"${String(s || '').replace(/\"/g, '\"\"')}\"`;\n\nif (!fs.existsSync(csvPath)) {\n  fs.writeFileSync(csvPath, header, 'utf8');\n}\n\nconst line = [\n  new Date().toISOString(),\n  esc(item.json.document_number),\n  esc(item.json.title),\n  esc(item.json.reject_reason),\n  item.json.ai_result?.confidence ?? 'N/A',\n  esc(JSON.stringify(item.json.ai_result?.detected_issues || []))\n].join(',') + '\\n';\n\nfs.appendFileSync(csvPath, line, 'utf8');\n\nreturn [$input.first()];"
      },
      "id": "reject-logger",
      "name": "Log Reject to CSV",
      "type": "n8n-nodes-base.code",
      "typeVersion": 2,
      "position": [2200, 200],
      "notes": "บันทึกรายการที่ถูกปฏิเสธลง CSV"
    },
    {
      "parameters": {
        "jsCode": "const fs = require('fs');\nconst items = $input.all();\nconst config = $workflow.staticData.config;\n\nconst csvPath = `${config.LOG_PATH}/error_log.csv`;\nconst header = 'timestamp,document_number,error_type,error_message,raw_ai_response\\n';\nconst esc = (s) => `\"${String(s || '').replace(/\"/g, '\"\"')}\"`;\n\nif (!fs.existsSync(csvPath)) {\n  fs.writeFileSync(csvPath, header, 'utf8');\n}\n\nfor (const item of items) {\n  const line = [\n    new Date().toISOString(),\n    esc(item.json.document_number),\n    esc(item.json.error_type || 'UNKNOWN'),\n    esc(item.json.error || item.json.parse_error),\n    esc(item.json.raw_ai_response || '')\n  ].join(',') + '\\n';\n  \n  fs.appendFileSync(csvPath, line, 'utf8');\n}\n\nreturn items;"
      },
      "id": "error-logger-csv",
      "name": "Log Error to CSV",
      "type": "n8n-nodes-base.code",
      "typeVersion": 2,
      "position": [1400, 400],
      "notes": "บันทึก Error ลง CSV (จาก File Validator)"
    },
    {
      "parameters": {
        "operation": "executeQuery",
        "host": "={{$workflow.staticData.config.DB_HOST}}",
        "port": "={{$workflow.staticData.config.DB_PORT}}",
        "database": "={{$workflow.staticData.config.DB_NAME}}",
        "user": "={{$workflow.staticData.config.DB_USER}}",
        "password": "={{$workflow.staticData.config.DB_PASSWORD}}",
        "query": "INSERT INTO migration_errors (batch_id, document_number, error_type, error_message, raw_ai_response, created_at) VALUES ('{{$workflow.staticData.config.BATCH_ID}}', '{{$json.document_number}}', '{{$json.error_type || \"UNKNOWN\"}}', '{{$json.error || $json.parse_error}}', '{{$json.raw_ai_response || \"\"}}', NOW())",
        "options": {}
      },
      "id": "error-logger-db",
      "name": "Log Error to DB",
      "type": "n8n-nodes-base.mySql",
      "typeVersion": 2.4",
      "position": [2000, 400],
      "notes": "บันทึก Error ลง MariaDB"
    },
    {
      "parameters": {
        "amount": "={{$workflow.staticData.config.DELAY_MS}}",
        "unit": "milliseconds"
      },
      "id": "delay-node",
      "name": "Delay",
      "type": "n8n-nodes-base.wait",
      "typeVersion": 1,
      "position": [2800, 0],
      "notes": "หน่วงเวลาระหว่าง Batches"
    }
  ],
  "connections": {
    "Schedule Trigger (22:00)": {
      "main": [
        [
          {
            "node": "Set Configuration",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Set Configuration": {
      "main": [
        [
          {
            "node": "Fetch Categories",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Fetch Categories": {
      "main": [
        [
          {
            "node": "File Mount Check",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "File Mount Check": {
      "main": [
        [
          {
            "node": "Read Checkpoint",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Read Checkpoint": {
      "main": [
        [
          {
            "node": "Process Batch + Encoding",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Process Batch + Encoding": {
      "main": [
        [
          {
            "node": "File Validator",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "File Validator": {
      "main": [
        [
          {
            "node": "Build AI Prompt",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "Log Error to CSV",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Build AI Prompt": {
      "main": [
        [
          {
            "node": "Ollama AI Analysis",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Ollama AI Analysis": {
      "main": [
        [
          {
            "node": "Parse & Validate AI Response",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Parse & Validate AI Response": {
      "main": [
        [
          {
            "node": "Confidence Router",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "Update Fallback State",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Confidence Router": {
      "main": [
        [
          {
            "node": "Import to Backend",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "Insert Review Queue",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "Log Reject to CSV",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "Log Error to DB",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Import to Backend": {
      "main": [
        [
          {
            "node": "Flag Checkpoint",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Flag Checkpoint": {
      "main": [
        [
          {
            "node": "Save Checkpoint",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Log Error to CSV": {
      "main": [
        [
          {
            "node": "Log Error to DB",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}

📄 เอกสารปรับปรุง 03-05-n8n-migration-setup-guide.md (Free Plan Edition)

# 📋 คู่มือการตั้งค่า n8n สำหรับ Legacy Data Migration (Free Plan Edition)

> **สำหรับ n8n Free Plan (Self-hosted)** - ไม่ใช้ Environment Variables
> **Version:** 1.8.0-free | **Last Updated:** 2026-03-03

---

## ⚠️ ความแตกต่างจากเวอร์ชัน Enterprise

| ฟีเจอร์ | Enterprise | Free Plan (นี้) |
|---------|-----------|----------------|
| Environment Variables | ✅ ใช้ `$env` | ❌ ใช้ `Set Node` + `staticData` |
| External Secrets | ✅ Vault/Secrets Manager | ❌ Hardcode ใน Set Node |
| Multiple Workflows | ✅ Unlimited | ⚠️ รวมเป็น Workflow เดียว |
| Error Handling | ✅ Advanced | ⚠️ Manual Retry |
| Webhook Triggers | ✅ | ✅ ใช้ได้ |

---

## 🏗️ สถาปัตยกรรมใหม่สำหรับ Free Plan

┌─────────────────────────────────────────────────────────────┐ │ MIGRATION WORKFLOW v1.8.0-FREE │ ├─────────────────────────────────────────────────────────────┤ │ │ │ [Schedule Trigger 22:00] │ │ │ │ │ ▼ │ │ ┌─────────────┐ ค่า Config ทั้งหมดอยู่ที่นี่ │ │ │ Set Config │ (แก้ไขใน Code Node นี้เท่านั้น) │ │ │ (Node 0) │ │ │ └──────┬──────┘ │ │ │ │ │ ┌──────▼──────┐ ┌──────────────┐ ┌──────────────┐ │ │ │Pre-flight │───▶│Fetch Categories│──▶│File Validator│ │ │ │Checks │ │from Backend │ │+ Sanitize │ │ │ └─────────────┘ └──────────────┘ └──────┬───────┘ │ │ │ │ │ ┌────────────────────────────┤ │ │ │ │ │ │ Valid │ Error │ │ │ ▼ ▼ │ │ ┌─────────────────┐ ┌─────────────────┐ │ │ │ AI Analysis │ │ Error Logger │ │ │ │ (Ollama) │ │ (CSV + DB) │ │ │ └────────┬────────┘ └─────────────────┘ │ │ │ │ │ ┌────────▼────────┐ │ │ │ Confidence │ │ │ │ Router │ │ │ │ (4 outputs) │ │ │ └────┬───┬───┬────┘ │ │ │ │ │ │ │ ┌─────────┘ │ └─────────┐ │ │ ▼ ▼ ▼ │ │ ┌──────┐ ┌──────────┐ ┌────────┐ │ │ │Auto │ │ Review │ │Reject │ │ │ │Ingest│ │ Queue │ │Log │ │ │ │+Chkpt│ │(DB only) │ │(CSV) │ │ │ └──────┘ └──────────┘ └────────┘ │ │ │ └─────────────────────────────────────────────────────────────┘


---

## 📝 การตั้งค่า Configuration (สำคัญมาก)

### ขั้นตอนที่ 1: แก้ไข Node "Set Configuration"

**เปิด Workflow → คลิก Node "Set Configuration" → แก้ไข Code:**

```javascript
// ============================================
// CONFIGURATION - แก้ไขค่าที่นี่เท่านั้น
// ============================================
const CONFIG = {
  // 🔴 สำคัญ: เปลี่ยนทุกค่าที่มี <...>

  // Ollama Settings
  OLLAMA_HOST: 'http://192.168.20.100:11434',
  OLLAMA_MODEL_PRIMARY: 'llama3.2:3b',
  OLLAMA_MODEL_FALLBACK: 'mistral:7b-instruct-q4_K_M',

  // Backend Settings
  BACKEND_URL: 'https://api.np-dms.work',
  MIGRATION_TOKEN: 'Bearer YOUR_MIGRATION_TOKEN_HERE', // 🔴 เปลี่ยน

  // Batch Settings
  BATCH_SIZE: 10,
  BATCH_ID: 'migration_20260226',
  DELAY_MS: 2000,

  // Thresholds
  CONFIDENCE_HIGH: 0.85,
  CONFIDENCE_LOW: 0.60,
  MAX_RETRY: 3,
  FALLBACK_THRESHOLD: 5,

  // Paths (QNAP NAS)
  STAGING_PATH: '/share/np-dms/staging_ai',
  LOG_PATH: '/share/np-dms/n8n/migration_logs',

  // Database (MariaDB)
  DB_HOST: '192.168.1.100',
  DB_PORT: 3306,
  DB_NAME: 'lcbp3_production',
  DB_USER: 'migration_bot',
  DB_PASSWORD: 'YOUR_DB_PASSWORD_HERE' // 🔴 เปลี่ยน
};

// อย่าแก้โค้ดด้านล่างนี้
$workflow.staticData = $workflow.staticData || {};
$workflow.staticData.config = CONFIG;

return [{ json: { config_loaded: true, timestamp: new Date().toISOString() }}];

ขั้นตอนที่ 2: ตั้งค่า Credentials ใน n8n UI

เนื่องจาก Free Plan ไม่สามารถซ่อน Sensitive Data ได้ทั้งหมด แนะนำให้:

  1. สร้าง Dedicated User สำหรับ Migration เท่านั้น
  2. ใช้ Token ที่มีสิทธิ์จำกัด (เฉพาะ API ที่จำเป็น)
  3. Rotate Token ทันทีหลัง Migration เสร็จ

การตั้งค่า Credentials (ถ้าใช้):

Credential Type ใช้ใน Node
Ollama API HTTP Request Ollama AI Analysis
LCBP3 Backend HTTP Request Import to Backend, Fetch Categories
MariaDB MySQL ทุก Database Node

🗄️ การเตรียม Database (เหมือนเดิม)

รัน SQL นี้บน MariaDB ก่อน เริ่มใช้งาน:

-- Checkpoint
CREATE TABLE IF NOT EXISTS migration_progress (
    batch_id VARCHAR(50) PRIMARY KEY,
    last_processed_index INT DEFAULT 0,
    status ENUM('RUNNING','COMPLETED','FAILED') DEFAULT 'RUNNING',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Review Queue
CREATE TABLE IF NOT EXISTS migration_review_queue (
    id INT AUTO_INCREMENT PRIMARY KEY,
    document_number VARCHAR(100) NOT NULL,
    title TEXT,
    original_title TEXT,
    ai_suggested_category VARCHAR(50),
    ai_confidence DECIMAL(4,3),
    ai_issues JSON,
    review_reason VARCHAR(255),
    status ENUM('PENDING','APPROVED','REJECTED') DEFAULT 'PENDING',
    reviewed_by VARCHAR(100),
    reviewed_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_doc_number (document_number)
);

-- Error Log
CREATE TABLE IF NOT EXISTS migration_errors (
    id INT AUTO_INCREMENT PRIMARY KEY,
    batch_id VARCHAR(50),
    document_number VARCHAR(100),
    error_type ENUM('FILE_NOT_FOUND','AI_PARSE_ERROR','API_ERROR','DB_ERROR','SECURITY','UNKNOWN'),
    error_message TEXT,
    raw_ai_response TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_batch_id (batch_id),
    INDEX idx_error_type (error_type)
);

-- Fallback State
CREATE TABLE IF NOT EXISTS migration_fallback_state (
    id INT AUTO_INCREMENT PRIMARY KEY,
    batch_id VARCHAR(50) UNIQUE,
    recent_error_count INT DEFAULT 0,
    is_fallback_active BOOLEAN DEFAULT FALSE,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Idempotency
CREATE TABLE IF NOT EXISTS import_transactions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    idempotency_key VARCHAR(255) UNIQUE NOT NULL,
    document_number VARCHAR(100),
    batch_id VARCHAR(100),
    status_code INT DEFAULT 201,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_idem_key (idempotency_key)
);

🐳 Docker Compose สำหรับ QNAP (Free Plan)

version: '3.8'

services:
  n8n:
    image: n8nio/n8n:1.78.0
    container_name: n8n-lcbp3
    restart: unless-stopped
    ports:
      - "5678:5678"
    environment:
      - TZ=Asia/Bangkok
      - NODE_ENV=production
      - N8N_BASIC_AUTH_ACTIVE=true
      - N8N_BASIC_AUTH_USER=admin
      - N8N_BASIC_AUTH_PASSWORD=YOUR_N8N_PASSWORD_HERE
      - N8N_ENCRYPTION_KEY=YOUR_ENCRYPTION_KEY_HERE
    volumes:
      - /share/np-dms/n8n:/home/node/.n8n
      - /share/np-dms/n8n/cache:/home/node/.cache
      # อ่านอย่างเดียว: ไฟล์ต้นฉบับ
      - /share/np-dms/staging_ai:/share/np-dms/staging_ai:ro
      # เขียนได้: Logs และ CSV
      - /share/np-dms/n8n/migration_logs:/share/np-dms/n8n/migration_logs:rw
    networks:
      - lcbp3-network

networks:
  lcbp3-network:
    external: true

หมายเหตุ: Free Plan ไม่ต้องใช้ PostgreSQL สำหรับ n8n (ใช้ SQLite ได้)

🔄 การทำงานของแต่ละ Node

Node 0: Set Configuration

  • เก็บค่า Config ทั้งหมดใน $workflow.staticData.config
  • อ่านผ่าน $workflow.staticData.config.KEY ใน Node อื่น

Node 1-2: Pre-flight Checks

  • ตรวจสอบ Backend Health
  • ดึง Categories จาก /api/meta/categories
  • ตรวจ File Mount (Read-only)
  • เก็บ Categories ใน $workflow.staticData.systemCategories

Node 3: Read Checkpoint

  • อ่าน last_processed_index จาก migration_progress
  • ถ้าไม่มี เริ่มจาก 0

Node 4: Process Batch

  • อ่าน Excel
  • Normalize UTF-8 (NFC)
  • ตัด Batch ตาม BATCH_SIZE

Node 5: File Validator

  • Sanitize filename (replace special chars)
  • Path traversal check
  • ตรวจสอบไฟล์มีอยู่จริง
  • Output 2 ทาง: Valid → AI, Error → Log

Node 6: Build AI Prompt

  • ดึง Categories จาก staticData (ไม่ hardcode)
  • เลือก Model ตาม Fallback State
  • สร้าง Prompt ตาม Template

Node 7: Ollama AI Analysis

  • เรียก POST /api/generate
  • Timeout 30 วินาที
  • Retry 3 ครั้ง (n8n built-in)

Node 8: Parse & Validate

  • Parse JSON Response
  • Schema Validation (is_valid, confidence, detected_issues)
  • Enum Validation (ตรวจ Category ว่าอยู่ใน List หรือไม่)
  • Output 2 ทาง: Success → Router, Error → Fallback

Node 9: Confidence Router

  • 4 Outputs:
    1. Auto Ingest (confidence ≥ 0.85 && is_valid)
    2. Review Queue (0.60 ≤ confidence < 0.85)
    3. Reject Log (confidence < 0.60 หรือ is_valid = false)
    4. Error Log (parse error)

Node 10A: Auto Ingest

  • POST /api/correspondences/import
  • Header: Idempotency-Key: {doc_num}:{batch_id}
  • บันทึก Checkpoint ทุก 10 records

Node 10B: Review Queue

  • INSERT เข้า migration_review_queue เท่านั้น
  • ยังไม่สร้าง Correspondence

Node 10C: Reject Log

  • เขียน CSV ที่ /share/np-dms/n8n/migration_logs/reject_log.csv

Node 10D: Error Log

  • เขียน CSV + INSERT เข้า migration_errors

🚨 ข้อควรระวังสำหรับ Free Plan

1. Security

  • อย่า Commit ไฟล์นี้เข้า Git ถ้ามี Password/Token
  • ใช้ .gitignore สำหรับไฟล์ JSON ที่มี Config
  • Rotate Token ทันทีหลังใช้งาน

2. Limitations

  • Execution Timeout: ตรวจสอบ n8n execution timeout (default 5 นาที)
  • Memory: จำกัดที่ 2GB (ตาม Docker Compose)
  • Concurrent: รัน Batch ต่อเนื่อง ไม่ parallel

3. Backup

  • สำรอง SQLite database ของ n8n ที่ /home/node/.n8n
  • สำรอง Logs ที่ /share/np-dms/n8n/migration_logs

✅ Pre-Production Checklist (Free Plan)

ลำดับ รายการ วิธีตรวจสอบ
1 Config ถูกต้อง รัน Test Execution ดูผลลัพธ์ Node 0
2 Database Connect ได้ Test Step ใน Node Read Checkpoint
3 Ollama พร้อม curl http://<OLLAMA_HOST>/api/tags
4 Backend Token ใช้ได้ Test Step ใน Node Fetch Categories
5 File Mount RO ถูกต้อง docker exec n8n ls /share/np-dms/staging_ai
6 Log Mount RW ถูกต้อง docker exec n8n touch /share/np-dms/n8n/migration_logs/test
7 Categories ไม่ hardcode ดูผลลัพธ์ Node Fetch Categories
8 Idempotency Key ถูกต้อง ตรวจ Header ใน Node Import
9 Checkpoint บันทึก ตรวจสอบ migration_progress หลังรัน
10 Error Log สร้างไฟล์ ตรวจสอบ error_log.csv

🔧 การแก้ไขปัญหาเฉพาะหน้า

ปัญหา: Config ไม่ถูกต้อง

แก้ไข: แก้ที่ Node "Set Configuration" แล้ว Save → Execute Workflow ใหม่

ปัญหา: Database Connection Error

ตรวจสอบ:

// ใส่ใน Code Node ชั่วคราวเพื่อ Debug
const config = $workflow.staticData.config;
return [{ json: {
  host: config.DB_HOST,
  port: config.DB_PORT,
  // อย่าแสดง password ใน Production!
  test: 'Config loaded: ' + (config ? 'YES' : 'NO')
}}];

ปัญหา: Ollama Timeout

แก้ไข:

  • เพิ่ม DELAY_MS เป็น 3000 หรือ 5000
  • ลด BATCH_SIZE เหลือ 5
  • ตรวจสอบ GPU/CPU ของ Ollama Server

📊 การ Monitor (Manual)

เนื่องจาก Free Plan ไม่มี Advanced Monitoring:

# ดู Progress ล่าสุด
docker exec n8n-lcbp3 sh -c "tail -5 /share/np-dms/n8n/migration_logs/reject_log.csv"

# ดู Error ล่าสุด
docker exec n8n-lcbp3 sh -c "tail -10 /share/np-dms/n8n/migration_logs/error_log.csv"

# ดู Checkpoint ใน DB
mysql -h <DB_HOST> -u migration_bot -p -e "SELECT * FROM migration_progress WHERE batch_id = 'migration_20260226'"

เอกสารฉบับนี้จัดทำขึ้นสำหรับ n8n Free Plan (Self-hosted) Version: 1.8.0-free | Last Updated: 2026-03-03


---

## 📥 วิธี Import Workflow

1. บันทึก JSON ด้านบนเป็นไฟล์ `lcbp3-migration-free.json`
2. เข้า n8n UI → **Workflows** → **Import from File**
3. เลือกไฟล์ `lcbp3-migration-free.json`
4. เปิด Workflow → แก้ไข Node **"Set Configuration"** ตามข้อมูลจริง
5. ตั้งค่า **Schedule Trigger** หรือเปลี่ยนเป็น **Manual Trigger** สำหรับทดสอบ
6. **Save** → **Execute Workflow** เพื่อทดสอบ

ต้องการให้ช่วยปรับแต่งเพิ่มเติมหรือไม่ครับ? เช่น:
- เพิ่ม Node สำหรับส่ง Email แจ้งเตือน
- เพิ่ม Rollback Workflow
- ปรับ Batch Size อัตโนมัติตาม Error Rate
Reference in New Issue View Git Blame Copy Permalink