{ "edges": [ { "to": "c907f41d-c860-4d1a-8708-d26a62dc4786", "from": "858def70-5a67-4b79-8c2a-e3528082f850", "toPort": "0", "fromPort": "0" }, { "to": "6d08da53-983a-438c-b164-ff86ce7c1b47", "from": "bbb0bbe0-fd8f-46cd-bd2f-c4826efcca19", "toPort": "0", "fromPort": "0" }, { "to": "f11ee402-4b02-49d6-869f-fbcd9a151fd0", "from": "511a0aa7-79b0-4a62-a21c-daee9e7796f3", "toPort": "0", "fromPort": "0" }, { "to": "131da027-2258-4957-a297-c232325e4ea5", "from": "43052c70-6f3f-49b7-aa1a-22f8db32790d", "toPort": "0", "fromPort": "0" }, { "to": "ba8740a8-2dcb-4513-94e0-047e96cc0f26", "from": "ead117d5-1bee-4c3f-bd93-1810f7f9600a", "toPort": "0", "fromPort": "0" }, { "to": "ac495591-815b-4443-a661-be7aa94af167", "from": "8efb034f-82d7-43db-aa89-96ca9bb14f63", "toPort": "0", "fromPort": "0" }, { "to": "a2d97894-1d5d-48b0-a05d-26a2bf3db021", "from": "ac495591-815b-4443-a661-be7aa94af167", "toPort": "0", "fromPort": "0" }, { "to": "858def70-5a67-4b79-8c2a-e3528082f850", "from": "dfdd2e11-c8f1-4504-b13e-d7137c59b2a1", "toPort": "0", "fromPort": "0" }, { "to": "c907f41d-c860-4d1a-8708-d26a62dc4786", "from": "dfdd2e11-c8f1-4504-b13e-d7137c59b2a1", "toPort": "0", "fromPort": "1" }, { "to": "511a0aa7-79b0-4a62-a21c-daee9e7796f3", "from": "c64b6ad1-cc8e-4dc7-bc93-7598b0379e8e", "toPort": "0", "fromPort": "0" }, { "to": "6e01a2e2-2224-441c-983e-91ed044382ee", "from": "131da027-2258-4957-a297-c232325e4ea5", "toPort": "1", "fromPort": "0" }, { "to": "6e01a2e2-2224-441c-983e-91ed044382ee", "from": "ba8740a8-2dcb-4513-94e0-047e96cc0f26", "toPort": "0", "fromPort": "0" }, { "to": "c64b6ad1-cc8e-4dc7-bc93-7598b0379e8e", "from": "c907f41d-c860-4d1a-8708-d26a62dc4786", "toPort": "0", "fromPort": "0" }, { "to": "ead117d5-1bee-4c3f-bd93-1810f7f9600a", "from": "6d08da53-983a-438c-b164-ff86ce7c1b47", "toPort": "0", "fromPort": "0" }, { "to": "43052c70-6f3f-49b7-aa1a-22f8db32790d", "from": "6d08da53-983a-438c-b164-ff86ce7c1b47", "toPort": "0", "fromPort": "0" }, { "to": "8efb034f-82d7-43db-aa89-96ca9bb14f63", "from": "6d08da53-983a-438c-b164-ff86ce7c1b47", "toPort": "0", "fromPort": "0" }, { "to": "a2d97894-1d5d-48b0-a05d-26a2bf3db021", "from": "6e01a2e2-2224-441c-983e-91ed044382ee", "toPort": "0", "fromPort": "0" }, { "to": "dfdd2e11-c8f1-4504-b13e-d7137c59b2a1", "from": "a2d97894-1d5d-48b0-a05d-26a2bf3db021", "toPort": "0", "fromPort": "0" } ], "nodes": [ { "id": "bbb0bbe0-fd8f-46cd-bd2f-c4826efcca19", "name": "Monthly Schedule", "type": "trigger", "params": { "kind": "n8n-nodes-base.scheduleTrigger", "rule": { "interval": [ { "field": "months", "triggerAtHour": 2 } ] } }, "position": [ -448, 192 ] }, { "id": "6d08da53-983a-438c-b164-ff86ce7c1b47", "name": "Workflow Configuration", "type": "set", "params": { "options": {}, "assignments": { "assignments": [ { "id": "id-1", "name": "stripeApiUrl", "type": "string", "value": "<__PLACEHOLDER_VALUE__Stripe API endpoint URL__>" }, { "id": "id-2", "name": "paypalApiUrl", "type": "string", "value": "<__PLACEHOLDER_VALUE__PayPal API endpoint URL__>" }, { "id": "id-3", "name": "bankApiUrl", "type": "string", "value": "<__PLACEHOLDER_VALUE__Bank API endpoint URL__>" }, { "id": "id-4", "name": "archiveFolderId", "type": "string", "value": "<__PLACEHOLDER_VALUE__Google Drive folder ID for archive__>" }, { "id": "id-5", "name": "taxAgentEmail", "type": "string", "value": "<__PLACEHOLDER_VALUE__Tax agent email address__>" }, { "id": "id-6", "name": "reconciliationThreshold", "type": "number", "value": 0.01 } ] }, "includeOtherFields": true }, "position": [ -224, 192 ] }, { "id": "ead117d5-1bee-4c3f-bd93-1810f7f9600a", "name": "Get Stripe Revenue", "type": "http", "params": { "url": "={{ $('Workflow Configuration').first().json.stripeApiUrl }}", "options": {}, "sendQuery": true, "authentication": "predefinedCredentialType", "queryParameters": { "parameters": [ { "name": "created[gte]", "value": "={{ $now.minus({ months: 1 }).startOf('month').toUnixInteger() }}" }, { "name": "created[lt]", "value": "={{ $now.startOf('month').toUnixInteger() }}" } ] } }, "position": [ 0, 0 ] }, { "id": "43052c70-6f3f-49b7-aa1a-22f8db32790d", "name": "Get PayPal Revenue", "type": "http", "params": { "url": "={{ $('Workflow Configuration').first().json.paypalApiUrl }}", "options": {}, "sendQuery": true, "authentication": "predefinedCredentialType", "queryParameters": { "parameters": [ { "name": "start_date", "value": "={{ $now.minus({ months: 1 }).startOf('month').toISO() }}" }, { "name": "end_date", "value": "={{ $now.startOf('month').toISO() }}" } ] } }, "position": [ 0, 192 ] }, { "id": "8efb034f-82d7-43db-aa89-96ca9bb14f63", "name": "Get Bank Statements", "type": "http", "params": { "url": "={{ $('Workflow Configuration').first().json.bankApiUrl }}", "options": {}, "sendQuery": true, "authentication": "predefinedCredentialType", "queryParameters": { "parameters": [ { "name": "from_date", "value": "={{ $now.minus({ months: 1 }).startOf('month').toISODate() }}" }, { "name": "to_date", "value": "={{ $now.startOf('month').toISODate() }}" } ] } }, "position": [ 224, 384 ] }, { "id": "ba8740a8-2dcb-4513-94e0-047e96cc0f26", "name": "Normalize Stripe Data", "type": "set", "params": { "options": {}, "assignments": { "assignments": [ { "id": "id-1", "name": "transactionId", "type": "string", "value": "={{ $json.id }}" }, { "id": "id-2", "name": "amount", "type": "number", "value": "={{ $json.amount / 100 }}" }, { "id": "id-3", "name": "currency", "type": "string", "value": "={{ $json.currency }}" }, { "id": "id-4", "name": "date", "type": "string", "value": "={{ $json.created }}" }, { "id": "id-5", "name": "source", "type": "string", "value": "Stripe" }, { "id": "id-6", "name": "description", "type": "string", "value": "={{ $json.description }}" } ] } }, "position": [ 224, 0 ] }, { "id": "131da027-2258-4957-a297-c232325e4ea5", "name": "Normalize PayPal Data", "type": "set", "params": { "options": {}, "assignments": { "assignments": [ { "id": "id-1", "name": "transactionId", "type": "string", "value": "={{ $json.transaction_info.transaction_id }}" }, { "id": "id-2", "name": "amount", "type": "number", "value": "={{ $json.transaction_info.transaction_amount.value }}" }, { "id": "id-3", "name": "currency", "type": "string", "value": "={{ $json.transaction_info.transaction_amount.currency_code }}" }, { "id": "id-4", "name": "date", "type": "string", "value": "={{ $json.transaction_info.transaction_initiation_date }}" }, { "id": "id-5", "name": "source", "type": "string", "value": "PayPal" }, { "id": "id-6", "name": "description", "type": "string", "value": "={{ $json.transaction_info.transaction_subject }}" } ] } }, "position": [ 224, 192 ] }, { "id": "ac495591-815b-4443-a661-be7aa94af167", "name": "Normalize Bank Data", "type": "set", "params": { "options": {}, "assignments": { "assignments": [ { "id": "id-1", "name": "transactionId", "type": "string", "value": "={{ $json.transaction_id }}" }, { "id": "id-2", "name": "amount", "type": "number", "value": "={{ $json.amount }}" }, { "id": "id-3", "name": "currency", "type": "string", "value": "={{ $json.currency }}" }, { "id": "id-4", "name": "date", "type": "string", "value": "={{ $json.booking_date }}" }, { "id": "id-5", "name": "source", "type": "string", "value": "Bank" }, { "id": "id-6", "name": "description", "type": "string", "value": "={{ $json.remittance_information }}" } ] } }, "position": [ 448, 384 ] }, { "id": "6e01a2e2-2224-441c-983e-91ed044382ee", "name": "Combine Revenue Sources", "type": "merge", "params": {}, "position": [ 448, 96 ] }, { "id": "a2d97894-1d5d-48b0-a05d-26a2bf3db021", "name": "Reconcile Revenue vs Bank", "type": "code", "params": { "jsCode": "// Reconcile revenue sources against bank statements\nconst items = $input.all();\n\n// Separate revenue sources from bank data\nconst revenueItems = [];\nconst bankItems = [];\n\nfor (const item of items) {\n if (item.json.source === 'stripe' || item.json.source === 'paypal') {\n revenueItems.push(item.json);\n } else if (item.json.source === 'bank') {\n bankItems.push(item.json);\n }\n}\n\n// Calculate total revenue from payment processors\nconst totalRevenue = revenueItems.reduce((sum, item) => {\n return sum + (parseFloat(item.amount) || 0);\n}, 0);\n\n// Calculate total deposits from bank\nconst totalBankDeposits = bankItems.reduce((sum, item) => {\n return sum + (parseFloat(item.amount) || 0);\n}, 0);\n\n// Calculate difference\nconst difference = totalRevenue - totalBankDeposits;\nconst differencePercentage = totalBankDeposits > 0 \n ? ((difference / totalBankDeposits) * 100).toFixed(2)\n : 0;\n\n// Create reconciliation report\nconst reconciliation = {\n totalRevenue: totalRevenue.toFixed(2),\n totalBankDeposits: totalBankDeposits.toFixed(2),\n difference: difference.toFixed(2),\n differencePercentage: differencePercentage,\n hasMismatch: Math.abs(difference) > 0.01, // Allow for small rounding differences\n revenueCount: revenueItems.length,\n bankTransactionCount: bankItems.length,\n reconciliationDate: new Date().toISOString(),\n revenueBreakdown: {\n stripe: revenueItems.filter(i => i.source === 'stripe').reduce((sum, i) => sum + parseFloat(i.amount || 0), 0).toFixed(2),\n paypal: revenueItems.filter(i => i.source === 'paypal').reduce((sum, i) => sum + parseFloat(i.amount || 0), 0).toFixed(2)\n },\n allTransactions: [...revenueItems, ...bankItems]\n};\n\nreturn [{ json: reconciliation }];" }, "position": [ 672, 192 ] }, { "id": "dfdd2e11-c8f1-4504-b13e-d7137c59b2a1", "name": "Check for Mismatches", "type": "if", "params": { "options": {}, "conditions": { "options": { "version": 3, "leftValue": "", "caseSensitive": true, "typeValidation": "strict" }, "combinator": "and", "conditions": [ { "id": "id-1", "operator": { "type": "boolean", "operation": "equals" }, "leftValue": "={{ $('Reconcile Revenue vs Bank').item.json.hasMismatches }}", "rightValue": true } ] } }, "position": [ 896, 192 ] }, { "id": "858def70-5a67-4b79-8c2a-e3528082f850", "name": "Flag Mismatches", "type": "set", "params": { "options": {}, "assignments": { "assignments": [ { "id": "id-1", "name": "mismatchFlag", "type": "string", "value": "ATTENTION REQUIRED" }, { "id": "id-2", "name": "reviewRequired", "type": "boolean", "value": true } ] }, "includeOtherFields": true }, "position": [ 1120, 128 ] }, { "id": "c907f41d-c860-4d1a-8708-d26a62dc4786", "name": "Aggregate Monthly Data", "type": "passthrough", "params": { "mode": "aggregate", "options": {}, "aggregate": "aggregateAllItemData" }, "position": [ 1344, 192 ] }, { "id": "c64b6ad1-cc8e-4dc7-bc93-7598b0379e8e", "name": "Generate Audit Report", "type": "code", "params": { "jsCode": "// Generate comprehensive audit-proof monthly revenue report\nconst items = $input.all();\n\n// Extract date range from workflow configuration\nconst currentDate = new Date();\nconst reportMonth = currentDate.toLocaleString('default', { month: 'long', year: 'numeric' });\n\n// Aggregate data from all items\nlet totalStripeRevenue = 0;\nlet totalPayPalRevenue = 0;\nlet totalBankDeposits = 0;\nlet transactions = [];\nlet mismatches = [];\n\nfor (const item of items) {\n const data = item.json;\n \n // Collect transaction details\n if (data.source) {\n transactions.push({\n date: data.date || data.transaction_date,\n source: data.source,\n amount: data.amount,\n description: data.description || data.memo,\n transactionId: data.transaction_id || data.id\n });\n }\n \n // Aggregate revenue by source\n if (data.source === 'Stripe') {\n totalStripeRevenue += parseFloat(data.amount || 0);\n } else if (data.source === 'PayPal') {\n totalPayPalRevenue += parseFloat(data.amount || 0);\n } else if (data.source === 'Bank') {\n totalBankDeposits += parseFloat(data.amount || 0);\n }\n \n // Collect mismatches\n if (data.mismatch_flag || data.reconciliation_status === 'mismatch') {\n mismatches.push({\n date: data.date,\n expectedAmount: data.expected_amount,\n actualAmount: data.actual_amount,\n difference: data.difference,\n notes: data.notes\n });\n }\n}\n\n// Calculate totals and variance\nconst totalRevenue = totalStripeRevenue + totalPayPalRevenue;\nconst variance = totalBankDeposits - totalRevenue;\nconst variancePercentage = totalRevenue > 0 ? ((variance / totalRevenue) * 100).toFixed(2) : 0;\n\n// Generate report structure\nconst report = {\n reportMetadata: {\n title: `Monthly Revenue Reconciliation Report - ${reportMonth}`,\n generatedDate: currentDate.toISOString(),\n reportPeriod: reportMonth,\n reportType: 'Tax Audit Evidence'\n },\n executiveSummary: {\n totalStripeRevenue: totalStripeRevenue.toFixed(2),\n totalPayPalRevenue: totalPayPalRevenue.toFixed(2),\n totalRevenue: totalRevenue.toFixed(2),\n totalBankDeposits: totalBankDeposits.toFixed(2),\n variance: variance.toFixed(2),\n variancePercentage: `${variancePercentage}%`,\n reconciliationStatus: Math.abs(variance) < 1 ? 'RECONCILED' : 'VARIANCE DETECTED',\n totalTransactions: transactions.length,\n mismatchCount: mismatches.length\n },\n revenueBreakdown: {\n stripe: {\n total: totalStripeRevenue.toFixed(2),\n percentage: totalRevenue > 0 ? ((totalStripeRevenue / totalRevenue) * 100).toFixed(2) + '%' : '0%'\n },\n paypal: {\n total: totalPayPalRevenue.toFixed(2),\n percentage: totalRevenue > 0 ? ((totalPayPalRevenue / totalRevenue) * 100).toFixed(2) + '%' : '0%'\n }\n },\n transactions: transactions.sort((a, b) => new Date(a.date) - new Date(b.date)),\n mismatches: mismatches,\n reconciliationNotes: {\n methodology: 'Automated reconciliation comparing payment processor revenue against bank deposits',\n toleranceThreshold: '$1.00',\n dataSource: 'Stripe API, PayPal API, Bank Statements',\n verificationStatus: mismatches.length === 0 ? 'All transactions verified' : `${mismatches.length} discrepancies require review`\n },\n auditTrail: {\n workflowExecutionId: $execution.id,\n executionDate: currentDate.toISOString(),\n dataIntegrity: 'Verified',\n complianceStandard: 'Tax Authority Requirements'\n }\n};\n\n// Generate PDF-ready HTML content\nconst htmlReport = `\n\n\n\n \n Revenue Reconciliation Report - ${reportMonth}\n \n\n\n

${report.reportMetadata.title}

Generated: ${new Date(report.reportMetadata.generatedDate).toLocaleString()}

Report Type: ${report.reportMetadata.reportType}

\n \n

Executive Summary

\n \n \n \n \n \n \n \n \n \n

Total Stripe Revenue:	$${report.executiveSummary.totalStripeRevenue}
Total PayPal Revenue:	$${report.executiveSummary.totalPayPalRevenue}
Total Revenue:	$${report.executiveSummary.totalRevenue}
Total Bank Deposits:	$${report.executiveSummary.totalBankDeposits}
Variance:	$${report.executiveSummary.variance} (${report.executiveSummary.variancePercentage})
Status:	${report.executiveSummary.reconciliationStatus}
Total Transactions:	${report.executiveSummary.totalTransactions}
Mismatches:	${report.executiveSummary.mismatchCount}

\n \n

Revenue Breakdown by Source

\n \n \n \n \n

Source	Amount	Percentage
Stripe	$${report.revenueBreakdown.stripe.total}	${report.revenueBreakdown.stripe.percentage}
PayPal	$${report.revenueBreakdown.paypal.total}	${report.revenueBreakdown.paypal.percentage}

\n \n

Transaction Details

\n \n \n ${transactions.map(t => `\n \n \n \n \n \n \n \n `).join('')}\n

Date	Source	Transaction ID	Description	Amount
${new Date(t.date).toLocaleDateString()}	${t.source}	${t.transactionId}	${t.description}	$${parseFloat(t.amount).toFixed(2)}

\n \n ${mismatches.length > 0 ? `\n

Discrepancies Requiring Review

\n \n \n ${mismatches.map(m => `\n \n \n \n \n \n \n \n `).join('')}\n

Date	Expected	Actual	Difference	Notes
${new Date(m.date).toLocaleDateString()}	$${parseFloat(m.expectedAmount).toFixed(2)}	$${parseFloat(m.actualAmount).toFixed(2)}	$${parseFloat(m.difference).toFixed(2)}	${m.notes}

\n ` : '

No discrepancies detected. All transactions reconciled successfully.

'}\n \n

Reconciliation Methodology

${report.reconciliationNotes.methodology}

Tolerance Threshold: ${report.reconciliationNotes.toleranceThreshold}

Data Sources: ${report.reconciliationNotes.dataSource}

Verification Status: ${report.reconciliationNotes.verificationStatus}

\n \n \n\n\n`;\n\n// Return both JSON and HTML formats\nreturn [\n {\n json: {\n report: report,\n htmlReport: htmlReport,\n fileName: `Revenue_Reconciliation_${reportMonth.replace(' ', '_')}_${currentDate.toISOString().split('T')[0]}.html`,\n pdfReady: true,\n summary: {\n totalRevenue: totalRevenue,\n totalBankDeposits: totalBankDeposits,\n variance: variance,\n status: Math.abs(variance) < 1 ? 'RECONCILED' : 'VARIANCE_DETECTED',\n mismatchCount: mismatches.length\n }\n }\n }\n];" }, "position": [ 1568, 192 ] }, { "id": "511a0aa7-79b0-4a62-a21c-daee9e7796f3", "name": "Upload to Archive", "type": "integration", "params": { "name": "={{ 'Revenue_Report_' + $now.minus({ months: 1 }).toFormat('yyyy-MM') + '.pdf' }}", "driveId": { "__rl": true, "mode": "list", "value": "My Drive" }, "options": {}, "service": "googleDrive", "folderId": { "__rl": true, "mode": "id", "value": "={{ $('Workflow Configuration').first().json.archiveFolderId }}" }, "operation": "read" }, "position": [ 1792, 192 ] }, { "id": "f11ee402-4b02-49d6-869f-fbcd9a151fd0", "name": "Notify Tax Agent", "type": "integration", "params": { "sendTo": "={{ $('Workflow Configuration').first().json.taxAgentEmail }}", "message": "=

Monthly Revenue Report

\n\n

Dear Tax Agent,

\n\n

The automated revenue reconciliation for {{ $now.minus({ months: 1 }).toFormat('MMMM yyyy') }} has been completed.

\n\n

Report Summary:

Total Revenue: {{ $('Aggregate Monthly Data').first().json.totalRevenue }}
Stripe Revenue: {{ $('Aggregate Monthly Data').first().json.stripeRevenue }}
PayPal Revenue: {{ $('Aggregate Monthly Data').first().json.paypalRevenue }}
Bank Deposits: {{ $('Aggregate Monthly Data').first().json.bankDeposits }}

\n\n{{ $('Flag Mismatches').all().length > 0 ? '

⚠️ Mismatches Detected:

There are ' + $('Flag Mismatches').all().length + ' discrepancies that require review. Please check the detailed report.

' : '

✓ All revenue sources reconciled successfully with no discrepancies.

' }}\n\n

Archived Report:

The complete audit report has been uploaded to Google Drive:

View Report

\n\n

Best regards,
Automated Revenue Reconciliation System

", "options": {}, "service": "gmail", "subject": "={{ 'Monthly Revenue Report Ready - ' + $now.minus({ months: 1 }).toFormat('MMMM yyyy') }}", "operation": "read" }, "position": [ 2016, 192 ] } ] }

${report.reportMetadata.title}

Executive Summary

Revenue Breakdown by Source

Transaction Details

Discrepancies Requiring Review

Reconciliation Methodology

Audit Trail

Monthly Revenue Report

Report Summary:

⚠️ Mismatches Detected:

Archived Report:

Multi-platform revenue reconciliation across Stripe, PayPal & bank with tax archive