使用 PostgreSQL、PDF.co 和邮件投递自动生成月度能源报告

• •Google 账号和 Gmail API 凭证
• •PostgreSQL 数据库连接信息
• •可能需要目标 API 的认证凭证

{
  "id": "lFJOVIgGwLDmQa4o",
  "meta": {
    "instanceId": "14e4c77104722ab186539dfea5182e419aecc83d85963fe13f6de862c875ebfa",
    "templateCredsSetupCompleted": true
  },
  "name": "Monthly Energy Generation Report (Postgres → PDF → Email)",
  "tags": [],
  "nodes": [
    {
      "id": "8a74f8aa-d3bf-4a08-9950-e51077071a5e",
      "name": "Transform data",
      "type": "n8n-nodes-base.code",
      "position": [
        360,
        0
      ],
      "parameters": {
        "jsCode": "return {\n  json: {\n    json_string: JSON.stringify({\n      date_range: \"2025-07-01 to 2025-07-03\",\n      records: $input.all().map(record => ({\n        id: record.json.id,\n        site_name: record.json.site_name,\n        generation_date: record.json.generation_date,\n        energy_generated_kwh: record.json.energy_generated_kwh,\n        peak_power_kw: record.json.peak_power_kw,\n        remarks: record.json.remarks\n      })),\n      note: \"TEST\"\n    })\n  }\n};\n"
      },
      "typeVersion": 2
    },
    {
      "id": "7e78ea66-5a79-4904-a514-f5916602b571",
      "name": "Convert data to pdf",
      "type": "n8n-nodes-base.httpRequest",
      "position": [
        580,
        0
      ],
      "parameters": {
        "url": "https://api.pdf.co/v1/pdf/convert/from/html",
        "options": {},
        "sendBody": true,
        "sendQuery": true,
        "sendHeaders": true,
        "bodyParameters": {
          "parameters": [
            {}
          ]
        },
        "queryParameters": {
          "parameters": [
            {}
          ]
        },
        "headerParameters": {
          "parameters": [
            {}
          ]
        }
      },
      "executeOnce": true,
      "typeVersion": 4.2
    },
    {
      "id": "3f834d5f-2239-4842-a14e-9a1c15dbb771",
      "name": "Send Report",
      "type": "n8n-nodes-base.gmail",
      "position": [
        800,
        0
      ],
      "webhookId": "",
      "parameters": {
        "sendTo": "",
        "message": "=Your monthly energy report is as follows  {{ $json.url }}",
        "options": {
          "appendAttribution": true
        },
        "subject": "Energy Report",
        "emailType": "text"
      },
      "credentials": {
        "gmailOAuth2": {}
      },
      "typeVersion": 2.1
    },
    {
      "id": "e8dd1777-6e62-4d14-89dc-e3217b9f7318",
      "name": "Get energy data",
      "type": "n8n-nodes-base.postgres",
      "position": [
        140,
        0
      ],
      "parameters": {
        "table": {
          "__rl": true,
          "mode": "list",
          "value": "energy_data",
          "cachedResultName": "energy_data"
        },
        "schema": {
          "__rl": true,
          "mode": "list",
          "value": "public",
          "cachedResultName": "public"
        },
        "options": {},
        "operation": "select"
      },
      "typeVersion": 2.6
    },
    {
      "id": "18bb8b25-1532-4b31-9b53-6599c6ee5624",
      "name": "Monthly Trigger",
      "type": "n8n-nodes-base.scheduleTrigger",
      "position": [
        -80,
        0
      ],
      "parameters": {
        "rule": {
          "interval": [
            {
              "field": "months",
              "triggerAtMinute": 1
            }
          ]
        }
      },
      "typeVersion": 1.2
    },
    {
      "id": "72b04ac2-68f3-4e90-9b02-e250f83aeb42",
      "name": "Sticky Note",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -140,
        -160
      ],
      "parameters": {
        "color": 4,
        "width": 1140,
        "height": 340,
        "content": "## Monthly Energy Generation Report (Postgres → PDF → Email)"
      },
      "typeVersion": 1
    },
    {
      "id": "04a42081-91d1-4a4b-b21c-57e26c7343da",
      "name": "Sticky Note1",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -140,
        220
      ],
      "parameters": {
        "color": 2,
        "width": 1140,
        "height": 400,
        "content": "##  **Purpose**\nAutomatically generate and send a monthly energy generation report. It collects energy data from a PostgreSQL database, formats it into a PDF, and emails it to a recipient.\n\n##  **Core Logic**\n1. **Trigger**: The workflow is scheduled to run monthly via a `Schedule Trigger` node.\n2. **Data Collection**: Connects to a PostgreSQL database and fetches energy data from the `energy_data` table.\n3. **Transformation**: Uses a `Code` node to transform raw database rows into a structured JSON object with metadata like `date_range`, `note`, and `records`.\n4. **PDF Generation**: Sends the transformed JSON to the PDF.co API (`Convert data to pdf` node), which returns a downloadable report URL.\n5. **Email Delivery**: Uses the `Gmail` node to send the report link via email to a configured recipient.\n\n##  **Outcome**\n* An energy performance PDF report is automatically generated and emailed monthly.\n* Enables proactive reporting for energy production across multiple plants (solar, wind, hydro).\n* Removes manual intervention in creating recurring performance summaries."
      },
      "typeVersion": 1
    }
  ],
  "active": false,
  "pinData": {
    "Transform data": [
      {
        "json": {
          "json_string": "{\"date_range\":\"2025-07-01 to 2025-07-03\",\"records\":[{\"id\":1,\"site_name\":\"Solar Plant A\",\"generation_date\":\"2025-06-30T18:30:00.000Z\",\"energy_generated_kwh\":\"1250.50\",\"peak_power_kw\":\"250.75\",\"remarks\":\"Sunny day, optimal generation\"},{\"id\":2,\"site_name\":\"Solar Plant A\",\"generation_date\":\"2025-07-01T18:30:00.000Z\",\"energy_generated_kwh\":\"1150.30\",\"peak_power_kw\":\"240.60\",\"remarks\":\"Partly cloudy\"},{\"id\":3,\"site_name\":\"Solar Plant A\",\"generation_date\":\"2025-07-02T18:30:00.000Z\",\"energy_generated_kwh\":\"980.75\",\"peak_power_kw\":\"220.10\",\"remarks\":\"Cloudy day\"},{\"id\":4,\"site_name\":\"Wind Farm B\",\"generation_date\":\"2025-06-30T18:30:00.000Z\",\"energy_generated_kwh\":\"1800.00\",\"peak_power_kw\":\"350.00\",\"remarks\":\"Strong winds, high generation\"},{\"id\":5,\"site_name\":\"Wind Farm B\",\"generation_date\":\"2025-07-01T18:30:00.000Z\",\"energy_generated_kwh\":\"1700.25\",\"peak_power_kw\":\"340.20\",\"remarks\":\"Normal winds\"},{\"id\":6,\"site_name\":\"Hydro Plant C\",\"generation_date\":\"2025-06-30T18:30:00.000Z\",\"energy_generated_kwh\":\"2200.40\",\"peak_power_kw\":\"400.50\",\"remarks\":\"Stable water flow\"},{\"id\":7,\"site_name\":\"Hydro Plant C\",\"generation_date\":\"2025-07-01T18:30:00.000Z\",\"energy_generated_kwh\":\"2150.10\",\"peak_power_kw\":\"395.75\",\"remarks\":\"Slight drop in flow\"}],\"note\":\"TEST\"}"
        }
      }
    ],
    "Monthly Trigger": [
      {
        "json": {
          "code": 1,
          "name": "First item"
        }
      },
      {
        "json": {
          "code": 2,
          "name": "Second item"
        }
      }
    ],
    "Convert data to pdf": [
      {
        "json": {
          "url": "",
          "name": "",
          "error": false,
          "status": 200,
          "credits": 9,
          "duration": 1915,
          "pageCount": 1,
          "remainingCredits": 9888,
          "outputLinkValidTill": ""
        }
      }
    ]
  },
  "settings": {
    "executionOrder": "v1"
  },
  "versionId": "a6822d15-47ba-4c21-8f81-355f80d534a9",
  "connections": {
    "3f834d5f-2239-4842-a14e-9a1c15dbb771": {
      "main": [
        []
      ]
    },
    "8a74f8aa-d3bf-4a08-9950-e51077071a5e": {
      "main": [
        [
          {
            "node": "7e78ea66-5a79-4904-a514-f5916602b571",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "e8dd1777-6e62-4d14-89dc-e3217b9f7318": {
      "main": [
        [
          {
            "node": "8a74f8aa-d3bf-4a08-9950-e51077071a5e",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "18bb8b25-1532-4b31-9b53-6599c6ee5624": {
      "main": [
        [
          {
            "node": "e8dd1777-6e62-4d14-89dc-e3217b9f7318",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "7e78ea66-5a79-4904-a514-f5916602b571": {
      "main": [
        [
          {
            "node": "3f834d5f-2239-4842-a14e-9a1c15dbb771",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}