n8n for SmartBuilding/PropClimateTech SaaS Vendors: 5 Automations for NYC LL97, EU EPBD, and ENERGY STAR Compliance

{
  "customer_tiers": [
    {
      "tier": "ENTERPRISE_BMS_SAAS",
      "description": "Large BMS platform serving Fortune 500 multi-site portfolios",
      "primary_regulations": [
        "NYC_LL97",
        "EU_EPBD",
        "ENERGY_STAR",
        "ISO_50001",
        "EU_TAXONOMY_ART10"
      ],
      "key_risk": "Portfolio-level carbon liability ($268/tonne) across 50+ buildings \u2014 one missed filing = seven-figure penalty"
    },
    {
      "tier": "COMMERCIAL_RE_ENERGY_SAAS",
      "description": "Energy management for multi-tenant commercial buildings",
      "primary_regulations": [
        "NYC_LL97",
        "ENERGY_STAR",
        "ASHRAE_90_1"
      ],
      "key_risk": "Tenant-level consumption attribution errors cause incorrect LL97 cap allocation and penalty disputes"
    },
    {
      "tier": "SMART_BUILDING_IOTANALYTICS_SAAS",
      "description": "IoT sensor analytics and building intelligence platform",
      "primary_regulations": [
        "ENERGY_STAR",
        "ASHRAE_90_1",
        "ISO_50001",
        "GDPR_ART5"
      ],
      "key_risk": "Sensor data gaps during critical consumption periods create unverifiable audit trails"
    },
    {
      "tier": "PROPTECH_ENERGY_MGMT_SAAS",
      "description": "PropTech energy optimization for real estate investment portfolios",
      "primary_regulations": [
        "EU_EPBD",
        "EU_TAXONOMY_ART10",
        "ENERGY_STAR",
        "NYC_LL97"
      ],
      "key_risk": "EU Taxonomy Art.10 misclassification on green bond prospectus \u2014 investor liability"
    },
    {
      "tier": "GREEN_BUILDING_CERTIFICATION_SAAS",
      "description": "LEED/BREEAM/WELL certification management platform",
      "primary_regulations": [
        "ENERGY_STAR",
        "WELL_BUILDING_V2",
        "ASHRAE_90_1",
        "EU_TAXONOMY_ART10"
      ],
      "key_risk": "Certification lapse mid-lease = tenant covenant breach = legal exposure"
    },
    {
      "tier": "HVAC_CONTROLS_SAAS",
      "description": "HVAC optimization and controls SaaS",
      "primary_regulations": [
        "ASHRAE_90_1",
        "CA_TITLE_24",
        "NYC_LL97",
        "ISO_50001"
      ],
      "key_risk": "ASHRAE 90.1 commissioning documentation gaps found during code inspection = project permit risk"
    },
    {
      "tier": "SMARTBUILDING_STARTUP_SAAS",
      "description": "Early-stage smart building tech startup (<$5M ARR)",
      "primary_regulations": [
        "ENERGY_STAR",
        "NYC_LL97"
      ],
      "key_risk": "First enterprise deal with NYC portfolio requires LL97 integration \u2014 missed = lost deal"
    }
  ]
}

{
  "name": "NYC LL97 Carbon Emissions Monitor & Penalty Calculator",
  "nodes": [
    {
      "id": "n1",
      "name": "Daily Trigger 6AM",
      "type": "n8n-nodes-base.scheduleTrigger",
      "parameters": {
        "rule": {
          "interval": [
            {
              "field": "hours",
              "hoursInterval": 24,
              "triggerAtHour": 6
            }
          ]
        }
      }
    },
    {
      "id": "n2",
      "name": "Query Building Portfolio",
      "type": "n8n-nodes-base.postgres",
      "parameters": {
        "operation": "executeQuery",
        "query": "SELECT b.building_id, b.name, b.address, b.gross_sq_ft, b.ll97_cap_2024_tonnes, b.ll97_cap_2030_tonnes, SUM(e.co2e_tonnes) AS ytd_co2e_tonnes, COUNT(e.id) AS meter_readings, MAX(e.reading_date) AS last_reading_date, b.may1_filing_status, b.energy_star_score FROM buildings b LEFT JOIN energy_readings e ON b.building_id = e.building_id AND EXTRACT(YEAR FROM e.reading_date) = EXTRACT(YEAR FROM CURRENT_DATE) WHERE b.nyc_ll97_subject = TRUE AND b.active = TRUE GROUP BY b.building_id, b.name, b.address, b.gross_sq_ft, b.ll97_cap_2024_tonnes, b.ll97_cap_2030_tonnes, b.may1_filing_status, b.energy_star_score"
      }
    },
    {
      "id": "n3",
      "name": "Calculate LL97 Exposure",
      "type": "n8n-nodes-base.code",
      "parameters": {
        "jsCode": "\nconst buildings = $input.all();\nconst results = [];\nconst today = new Date();\nconst yearEnd = new Date(today.getFullYear(), 11, 31);\nconst may1 = new Date(today.getFullYear() + 1, 4, 1);\nconst dayOfYear = Math.floor((today - new Date(today.getFullYear(), 0, 0)) / 86400000);\nconst daysInYear = 365;\nconst yearFraction = dayOfYear / daysInYear;\nfor (const b of buildings) {\n  const d = b.json;\n  const cap = parseFloat(d.ll97_cap_2024_tonnes) || 0;\n  const ytd = parseFloat(d.ytd_co2e_tonnes) || 0;\n  const projected = yearFraction > 0 ? ytd / yearFraction : 0;\n  const overage = Math.max(0, projected - cap);\n  const penaltyUSD = Math.round(overage * 268);\n  const daysToMay1 = Math.floor((may1 - today) / 86400000);\n  const daysSinceLastReading = d.last_reading_date ?\n    Math.floor((today - new Date(d.last_reading_date)) / 86400000) : 999;\n  let severity = 'NOTICE';\n  let alert = false;\n  if (projected > cap) { severity = 'CRITICAL'; alert = true; }\n  else if (projected > cap * 0.9) { severity = 'WARNING'; alert = true; }\n  else if (projected > cap * 0.75) { severity = 'WATCH'; alert = true; }\n  if (daysSinceLastReading > 3) { severity = 'DATA_GAP'; alert = true; }\n  results.push({\n    json: {\n      ...d, projected_annual_co2e: projected.toFixed(2),\n      overage_tonnes: overage.toFixed(2), penalty_usd: penaltyUSD,\n      severity, alert, days_to_may1: daysToMay1,\n      days_since_last_reading: daysSinceLastReading,\n      cap_utilization_pct: cap > 0 ? ((projected / cap) * 100).toFixed(1) : '0'\n    }\n  });\n}\nreturn results.filter(r => r.json.alert);\n"
      }
    },
    {
      "id": "n4",
      "name": "Route by Severity",
      "type": "n8n-nodes-base.if",
      "parameters": {
        "conditions": {
          "string": [
            {
              "value1": "={{$json.severity}}",
              "operation": "equal",
              "value2": "CRITICAL"
            }
          ]
        }
      }
    },
    {
      "id": "n5",
      "name": "Slack Critical Alert",
      "type": "n8n-nodes-base.slack",
      "parameters": {
        "channel": "#nyc-ll97-critical",
        "text": "CRITICAL: {{ $json.name }} \u2014 {{ $json.address }}\nProjected: {{ $json.projected_annual_co2e }} tonnes vs cap {{ $json.ll97_cap_2024_tonnes }} tonnes\nOverage: {{ $json.overage_tonnes }} tonnes | Penalty: ${{ $json.penalty_usd }} | Days to May 1: {{ $json.days_to_may1 }}"
      }
    },
    {
      "id": "n6",
      "name": "Slack Warning Alert",
      "type": "n8n-nodes-base.slack",
      "parameters": {
        "channel": "#nyc-ll97-monitoring",
        "text": "{{ $json.severity }}: {{ $json.name }} \u2014 Cap utilization {{ $json.cap_utilization_pct }}% | Days to May 1: {{ $json.days_to_may1 }}"
      }
    }
  ],
  "connections": {
    "Daily Trigger 6AM": {
      "main": [
        [
          {
            "node": "Query Building Portfolio",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Query Building Portfolio": {
      "main": [
        [
          {
            "node": "Calculate LL97 Exposure",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Calculate LL97 Exposure": {
      "main": [
        [
          {
            "node": "Route by Severity",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Route by Severity": {
      "main": [
        [
          {
            "node": "Slack Critical Alert",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "Slack Warning Alert",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}

{
  "name": "EU EPBD EPC Deadline Tracker & Transaction Alert",
  "nodes": [
    {
      "id": "n1",
      "name": "Webhook: Property Event",
      "type": "n8n-nodes-base.webhook",
      "parameters": {
        "path": "epbd-property-event",
        "httpMethod": "POST"
      }
    },
    {
      "id": "n2",
      "name": "Classify Event Type",
      "type": "n8n-nodes-base.code",
      "parameters": {
        "jsCode": "\nconst evt = $json;\nconst eventType = evt.event_type;\nconst epcClass = evt.epc_class || 'UNKNOWN';\nconst epcExpiry = evt.epc_expiry_date ? new Date(evt.epc_expiry_date) : null;\nconst today = new Date();\nconst epcExpired = epcExpiry ? epcExpiry < today : true;\nconst epcValid = !epcExpired;\nconst mepsDeadlines = {\n  G: { deadline: '2030-01-01', description: 'EPC class G must reach F by Jan 2030 (non-residential)' },\n  F: { deadline: '2033-01-01', description: 'EPC class F must reach E by Jan 2033 (non-residential)' },\n  E: { deadline: '2040-01-01', description: 'EPC class E must reach D by Jan 2040 (non-residential)' }\n};\nconst mepsDeadline = mepsDeadlines[epcClass];\nconst daysToMeps = mepsDeadline ?\n  Math.floor((new Date(mepsDeadline.deadline) - today) / 86400000) : null;\nconst requiresEpc = ['PROPERTY_SALE', 'NEW_RENTAL', 'PROPERTY_LISTED', 'LEASE_RENEWAL'].includes(eventType);\nconst alerts = [];\nif (requiresEpc && !epcValid) alerts.push({ type: 'EPC_REQUIRED_MISSING', severity: 'CRITICAL', message: `${eventType} event \u2014 EPC required but expired or missing` });\nif (requiresEpc && epcValid && epcExpiry) {\n  const daysToExpiry = Math.floor((epcExpiry - today) / 86400000);\n  if (daysToExpiry < 30) alerts.push({ type: 'EPC_EXPIRING_SOON', severity: 'WARNING', message: `EPC expires in ${daysToExpiry} days \u2014 renew before next transaction` });\n}\nif (mepsDeadline && daysToMeps !== null && daysToMeps < 365 * 3) alerts.push({ type: 'MEPS_UPGRADE_DUE', severity: daysToMeps < 365 ? 'CRITICAL' : 'WARNING', message: mepsDeadline.description, days_remaining: daysToMeps });\nreturn [{ json: { ...evt, epc_valid: epcValid, epc_class: epcClass, requires_epc: requiresEpc, meps_deadline: mepsDeadline, days_to_meps: daysToMeps, alerts } }];\n"
      }
    },
    {
      "id": "n3",
      "name": "Has Alerts?",
      "type": "n8n-nodes-base.if",
      "parameters": {
        "conditions": {
          "number": [
            {
              "value1": "={{$json.alerts.length}}",
              "operation": "larger",
              "value2": 0
            }
          ]
        }
      }
    },
    {
      "id": "n4",
      "name": "Notify Compliance Team",
      "type": "n8n-nodes-base.gmail",
      "parameters": {
        "toList": "compliance@yourplatform.com",
        "subject": "EU EPBD Alert: {{ $json.property_id }} \u2014 {{ $json.alerts[0].type }}",
        "message": "Property: {{ $json.property_address }}\nEvent: {{ $json.event_type }}\nEPC Class: {{ $json.epc_class }} | Valid: {{ $json.epc_valid }}\nAlerts: {{ $json.alerts | json }}\n\nRef: EU EPBD 2024 (Directive 2024/1275/EU) Art.19 \u2014 EPC required at point of sale/rental"
      }
    },
    {
      "id": "n5",
      "name": "Log to Postgres",
      "type": "n8n-nodes-base.postgres",
      "parameters": {
        "operation": "insert",
        "table": "epbd_events",
        "columns": "property_id,event_type,epc_class,epc_valid,alerts_json,created_at",
        "values": "={{ $json.property_id }},={{ $json.event_type }},={{ $json.epc_class }},={{ $json.epc_valid }},={{ JSON.stringify($json.alerts) }},NOW()"
      }
    }
  ],
  "connections": {
    "Webhook: Property Event": {
      "main": [
        [
          {
            "node": "Classify Event Type",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Classify Event Type": {
      "main": [
        [
          {
            "node": "Has Alerts?",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Has Alerts?": {
      "main": [
        [
          {
            "node": "Notify Compliance Team",
            "type": "main",
            "index": 0
          },
          {
            "node": "Log to Postgres",
            "type": "main",
            "index": 0
          }
        ],
        []
      ]
    }
  }
}

{
  "name": "ENERGY STAR Portfolio Manager Benchmarking Pipeline",
  "nodes": [
    {
      "id": "n1",
      "name": "Weekly Trigger Monday 7AM",
      "type": "n8n-nodes-base.scheduleTrigger",
      "parameters": {
        "rule": {
          "interval": [
            {
              "field": "weeks",
              "weeksInterval": 1,
              "triggerAtDay": [
                1
              ],
              "triggerAtHour": 7
            }
          ]
        }
      }
    },
    {
      "id": "n2",
      "name": "Fetch Building Scores",
      "type": "n8n-nodes-base.postgres",
      "parameters": {
        "operation": "executeQuery",
        "query": "SELECT building_id, name, energy_star_score, energy_star_score_prev, energy_star_certified, certification_expiry_date, benchmarking_jurisdiction, benchmarking_deadline, last_submission_date, submission_status FROM buildings WHERE energy_star_benchmarking_required = TRUE AND active = TRUE"
      }
    },
    {
      "id": "n3",
      "name": "Analyze Score Changes",
      "type": "n8n-nodes-base.code",
      "parameters": {
        "jsCode": "\nconst buildings = $input.all();\nconst alerts = [];\nfor (const b of buildings) {\n  const d = b.json;\n  const score = parseFloat(d.energy_star_score);\n  const prevScore = parseFloat(d.energy_star_score_prev);\n  const certified = d.energy_star_certified;\n  const expiryDate = d.certification_expiry_date ? new Date(d.certification_expiry_date) : null;\n  const today = new Date();\n  const daysToExpiry = expiryDate ? Math.floor((expiryDate - today) / 86400000) : null;\n  const deadlineDate = d.benchmarking_deadline ? new Date(d.benchmarking_deadline) : null;\n  const daysToDeadline = deadlineDate ? Math.floor((deadlineDate - today) / 86400000) : null;\n  if (certified && score < 75) {\n    alerts.push({ ...d, alert_type: 'CERTIFICATION_LOST', severity: 'CRITICAL',\n      message: `Score dropped to ${score} \u2014 ENERGY STAR certification lost (was ${prevScore})` });\n  } else if (!certified && score >= 75) {\n    alerts.push({ ...d, alert_type: 'CERTIFICATION_ELIGIBLE', severity: 'INFO',\n      message: `Score ${score} \u2014 now eligible for ENERGY STAR certification` });\n  } else if (prevScore && Math.abs(score - prevScore) > 5) {\n    alerts.push({ ...d, alert_type: 'SCORE_SIGNIFICANT_CHANGE', severity: 'WARNING',\n      message: `Score changed ${prevScore} \u2192 ${score} (${score > prevScore ? '+' : ''}${(score - prevScore).toFixed(1)} pts)` });\n  }\n  if (daysToExpiry !== null && daysToExpiry < 60) {\n    alerts.push({ ...d, alert_type: 'CERTIFICATION_EXPIRING', severity: daysToExpiry < 30 ? 'CRITICAL' : 'WARNING',\n      message: `ENERGY STAR certification expires in ${daysToExpiry} days` });\n  }\n  if (daysToDeadline !== null && daysToDeadline < 45 && d.submission_status !== 'SUBMITTED') {\n    alerts.push({ ...d, alert_type: 'BENCHMARKING_DEADLINE_APPROACHING', severity: daysToDeadline < 14 ? 'CRITICAL' : 'WARNING',\n      message: `${d.benchmarking_jurisdiction} benchmarking deadline in ${daysToDeadline} days \u2014 status: ${d.submission_status}` });\n  }\n}\nreturn alerts.map(a => ({ json: a }));\n"
      }
    },
    {
      "id": "n4",
      "name": "Slack Energy Team",
      "type": "n8n-nodes-base.slack",
      "parameters": {
        "channel": "#energy-star-alerts",
        "text": "{{ $json.severity }}: {{ $json.name }}\n{{ $json.alert_type }}: {{ $json.message }}\nJurisdiction: {{ $json.benchmarking_jurisdiction }} | Deadline: {{ $json.benchmarking_deadline }}"
      }
    },
    {
      "id": "n5",
      "name": "Log Alert",
      "type": "n8n-nodes-base.postgres",
      "parameters": {
        "operation": "insert",
        "table": "energy_star_alerts",
        "columns": "building_id,alert_type,severity,message,energy_star_score,created_at",
        "values": "={{ $json.building_id }},={{ $json.alert_type }},={{ $json.severity }},={{ $json.message }},={{ $json.energy_star_score }},NOW()"
      }
    }
  ],
  "connections": {
    "Weekly Trigger Monday 7AM": {
      "main": [
        [
          {
            "node": "Fetch Building Scores",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Fetch Building Scores": {
      "main": [
        [
          {
            "node": "Analyze Score Changes",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Analyze Score Changes": {
      "main": [
        [
          {
            "node": "Slack Energy Team",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Slack Energy Team": {
      "main": [
        [
          {
            "node": "Log Alert",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}

{
  "name": "ISO 50001 / EU Taxonomy Compliance Incident Pipeline",
  "nodes": [
    {
      "id": "n1",
      "name": "Webhook: Compliance Event",
      "type": "n8n-nodes-base.webhook",
      "parameters": {
        "path": "iso50001-incident",
        "httpMethod": "POST"
      }
    },
    {
      "id": "n2",
      "name": "Route Incident Type",
      "type": "n8n-nodes-base.switch",
      "parameters": {
        "dataPropertyName": "incident_type",
        "rules": {
          "rules": [
            {
              "value": "ISO50001_INTERNAL_AUDIT_MISSED"
            },
            {
              "value": "ISO50001_SEU_CORRECTIVE_ACTION_OVERDUE"
            },
            {
              "value": "ISO50001_ENERGY_BASELINE_DEVIATION"
            },
            {
              "value": "EU_TAXONOMY_ART10_ASSESSMENT_OVERDUE"
            },
            {
              "value": "EU_TAXONOMY_NZEB_THRESHOLD_BREACH"
            },
            {
              "value": "WELL_CERTIFICATION_EXPIRING"
            },
            {
              "value": "ASHRAE_COMMISSIONING_DOC_MISSING"
            },
            {
              "value": "CA_TITLE24_PERMIT_COMPLIANCE_GAP"
            }
          ]
        }
      }
    },
    {
      "id": "n3",
      "name": "ISO Audit: Escalate",
      "type": "n8n-nodes-base.gmail",
      "parameters": {
        "toList": "iso-coordinator@yourplatform.com",
        "subject": "ISO 50001 AUDIT MISSED \u2014 {{ $json.facility_name }} \u2014 Certification Risk",
        "message": "Facility: {{ $json.facility_name }}\nAudit Due: {{ $json.audit_due_date }}\nDays Overdue: {{ $json.days_overdue }}\n\nISO 50001:2018 Clause 9.2 \u2014 Internal audit must be conducted at planned intervals.\nRisk: Certifying body annual surveillance visit will find gap \u2192 suspension of EnMS certificate.\nEU Taxonomy Art.10: If ISO 50001 is the basis for climate mitigation classification, suspension triggers covenant review.\n\nRequired action: Schedule audit within 5 business days. Document corrective action."
      }
    },
    {
      "id": "n4",
      "name": "EU Taxonomy: Flag Investment",
      "type": "n8n-nodes-base.slack",
      "parameters": {
        "channel": "#eu-taxonomy-compliance",
        "text": "EU TAXONOMY BREACH: {{ $json.building_name }}\nType: {{ $json.incident_type }}\nIssue: {{ $json.description }}\nInvestors affected: {{ $json.investor_count }}\n\nRef: EU Taxonomy Regulation 2020/852 Art.10 \u2014 Do No Significant Harm criteria. Notify ESG reporting team immediately."
      }
    },
    {
      "id": "n5",
      "name": "Log All Incidents",
      "type": "n8n-nodes-base.postgres",
      "parameters": {
        "operation": "insert",
        "table": "compliance_incidents",
        "columns": "facility_id,incident_type,severity,description,regulation_ref,reported_at,status",
        "values": "={{ $json.facility_id }},={{ $json.incident_type }},={{ $json.severity }},={{ $json.description }},={{ $json.regulation_ref }},NOW(),'OPEN'"
      }
    }
  ],
  "connections": {
    "Webhook: Compliance Event": {
      "main": [
        [
          {
            "node": "Route Incident Type",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Route Incident Type": {
      "main": [
        [
          {
            "node": "ISO Audit: Escalate",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "ISO Audit: Escalate",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "Log All Incidents",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "EU Taxonomy: Flag Investment",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "EU Taxonomy: Flag Investment",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "Log All Incidents",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "Log All Incidents",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "Log All Incidents",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "ISO Audit: Escalate": {
      "main": [
        [
          {
            "node": "Log All Incidents",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "EU Taxonomy: Flag Investment": {
      "main": [
        [
          {
            "node": "Log All Incidents",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}

{
  "name": "Weekly SmartBuilding Platform KPI Dashboard",
  "nodes": [
    {
      "id": "n1",
      "name": "Monday 7AM",
      "type": "n8n-nodes-base.scheduleTrigger",
      "parameters": {
        "rule": {
          "interval": [
            {
              "field": "weeks",
              "weeksInterval": 1,
              "triggerAtDay": [
                1
              ],
              "triggerAtHour": 7
            }
          ]
        }
      }
    },
    {
      "id": "n2",
      "name": "Query Platform KPIs",
      "type": "n8n-nodes-base.postgres",
      "parameters": {
        "operation": "executeQuery",
        "query": "\nSELECT\n  COUNT(DISTINCT customer_id) FILTER (WHERE tier = 'ENTERPRISE_BMS_SAAS') AS enterprise_customers,\n  COUNT(DISTINCT customer_id) FILTER (WHERE tier = 'COMMERCIAL_RE_ENERGY_SAAS') AS commercial_re_customers,\n  COUNT(DISTINCT customer_id) FILTER (WHERE tier = 'SMARTBUILDING_STARTUP_SAAS') AS startup_customers,\n  COUNT(DISTINCT customer_id) FILTER (WHERE created_at >= NOW() - INTERVAL '7 days') AS new_customers_7d,\n  COUNT(DISTINCT customer_id) FILTER (WHERE created_at >= NOW() - INTERVAL '14 days' AND created_at < NOW() - INTERVAL '7 days') AS new_customers_prior_7d,\n  SUM(mrr_usd) AS total_mrr,\n  SUM(mrr_usd) FILTER (WHERE created_at >= NOW() - INTERVAL '7 days') AS new_mrr_7d,\n  COUNT(DISTINCT b.building_id) AS total_buildings_monitored,\n  AVG(b.energy_star_score) AS avg_energy_star_score,\n  COUNT(*) FILTER (WHERE b.energy_star_score < 75 AND b.energy_star_certified = TRUE) AS certification_at_risk,\n  SUM(b.ll97_projected_penalty_usd) AS total_ll97_exposure_usd,\n  COUNT(*) FILTER (WHERE b.nyc_ll97_subject = TRUE AND b.ll97_projected_penalty_usd > 0) AS buildings_over_cap,\n  COUNT(*) FILTER (WHERE b.epc_class IN ('G','F','E') AND b.eu_epbd_subject = TRUE) AS buildings_needing_meps_upgrade,\n  COUNT(*) FILTER (WHERE b.iso50001_certified = TRUE) AS iso50001_certified_buildings,\n  SUM(b.gross_sq_ft) AS total_portfolio_sqft\nFROM customers c\nJOIN buildings b ON c.customer_id = b.customer_id\nWHERE c.active = TRUE\n"
      }
    },
    {
      "id": "n3",
      "name": "Build KPI Email",
      "type": "n8n-nodes-base.code",
      "parameters": {
        "jsCode": "\nconst d = $json;\nconst wowNew = d.new_customers_prior_7d > 0 ?\n  (((d.new_customers_7d - d.new_customers_prior_7d) / d.new_customers_prior_7d) * 100).toFixed(1) : 'N/A';\nconst flags = [];\nif (d.certification_at_risk > 0) flags.push(`\u26a0 ${d.certification_at_risk} buildings: ENERGY STAR certification at risk (score <75)`);\nif (d.buildings_over_cap > 0) flags.push(`\u26a0 ${d.buildings_over_cap} buildings over NYC LL97 cap \u2014 total exposure $${Number(d.total_ll97_exposure_usd || 0).toLocaleString()}`);\nif (d.buildings_needing_meps_upgrade > 0) flags.push(`\u26a0 ${d.buildings_needing_meps_upgrade} EU buildings need MEPS upgrade before 2030`);\nconst html = [\n  '<h2>SmartBuilding Platform \u2014 Weekly KPI</h2>',\n  '<table border=\"1\" cellpadding=\"6\" style=\"border-collapse:collapse;font-family:monospace\">',\n  '<tr><th>Metric</th><th>This Week</th></tr>',\n  `<tr><td>Enterprise BMS customers</td><td>${d.enterprise_customers}</td></tr>`,\n  `<tr><td>Commercial RE customers</td><td>${d.commercial_re_customers}</td></tr>`,\n  `<tr><td>Startup customers</td><td>${d.startup_customers}</td></tr>`,\n  `<tr><td>New customers (7d)</td><td>${d.new_customers_7d} (${wowNew}% WoW)</td></tr>`,\n  `<tr><td>Total MRR</td><td>$${Number(d.total_mrr || 0).toLocaleString()}</td></tr>`,\n  `<tr><td>New MRR (7d)</td><td>$${Number(d.new_mrr_7d || 0).toLocaleString()}</td></tr>`,\n  `<tr><td>Buildings monitored</td><td>${Number(d.total_buildings_monitored || 0).toLocaleString()}</td></tr>`,\n  `<tr><td>Portfolio sq ft</td><td>${Number(d.total_portfolio_sqft || 0).toLocaleString()}</td></tr>`,\n  `<tr><td>Avg ENERGY STAR score</td><td>${parseFloat(d.avg_energy_star_score || 0).toFixed(1)}</td></tr>`,\n  `<tr><td>ISO 50001 certified buildings</td><td>${d.iso50001_certified_buildings}</td></tr>`,\n  `<tr><td>NYC LL97 exposure</td><td>$${Number(d.total_ll97_exposure_usd || 0).toLocaleString()}</td></tr>`,\n  `<tr><td>EU MEPS upgrades needed</td><td>${d.buildings_needing_meps_upgrade}</td></tr>`,\n  '</table>',\n  flags.length ? '<h3>Flags</h3><ul>' + flags.map(f => `<li>${f}</li>`).join('') + '</ul>' : '',\n  '<p style=\"font-size:11px;color:#666\">SmartBuilding Platform KPI \u2014 generated by n8n. Ref: NYC LL97 (Local Law 97 of 2019), EU EPBD 2024 (Directive 2024/1275/EU), ENERGY STAR Portfolio Manager, ISO 50001:2018, EU Taxonomy Regulation 2020/852.</p>'\n].join('\\n');\nreturn [{ json: { html, flags_count: flags.length, ...d } }];\n"
      }
    },
    {
      "id": "n4",
      "name": "Email CEO",
      "type": "n8n-nodes-base.gmail",
      "parameters": {
        "toList": "ceo@yourplatform.com",
        "bccList": "energy-director@yourplatform.com",
        "subject": "SmartBuilding KPI \u2014 Week of {{ $now.format('MMM DD') }} | MRR ${{ $json.total_mrr }} | LL97 Exposure ${{ $json.total_ll97_exposure_usd }}",
        "message": "={{ $json.html }}",
        "options": {
          "bodyType": "html"
        }
      }
    }
  ],
  "connections": {
    "Monday 7AM": {
      "main": [
        [
          {
            "node": "Query Platform KPIs",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Query Platform KPIs": {
      "main": [
        [
          {
            "node": "Build KPI Email",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Build KPI Email": {
      "main": [
        [
          {
            "node": "Email CEO",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}