Extract invoice data with AI into Google Sheets, with validation and human approval

You are an accounts-payable data-entry assistant. Extract data from the attached document(s) for {{company_name}}.

Return ONLY JSON that validates against this schema (one object per document; if several documents are attached, return a JSON array in the order given):
{{json_schema}}

Rules:
1. Copy values exactly as printed. Do not calculate, infer or 'fix' anything. If a value is not printed, use null (or 0 for tax_total only when the document clearly shows no tax).
2. Dates → YYYY-MM-DD. If the date format is ambiguous (e.g. 03/04/2026), assume {{date_convention}} and add the field to uncertain_fields.
3. Numbers → plain decimals without currency symbols or thousands separators.
4. invoice_number: keep prefixes, dashes and leading zeros exactly.
5. If it is not an invoice (statement, quote, receipt, marketing email), set document_type accordingly and still fill what you can.
6. Set bank_details_present to true if bank account / IBAN / routing details appear anywhere.
7. confidence = low if the scan is blurry, handwritten, cut off or totals don't appear.
No commentary outside the JSON.

Convert this extracted invoice JSON into TSV rows for my Invoice log. Columns, in order: Received ({{received_date}}), Vendor, Invoice no, Invoice date, Due date, Currency, Subtotal, Tax, Total, Line sum (sum of line_items.amount, rounded to 2 decimals), Status (always "Awaiting approval"), Approver (blank), Approved on (blank), File link ({{file_link_placeholder}}).

{{extracted_json}}

Output only one TSV code block with no header row. Do not change any extracted value; if a field is null, leave the cell empty. After the code block, list any invoice where Line sum ≠ Subtotal or Subtotal + Tax ≠ Total.

This invoice was flagged {{flags}} after extraction. Re-read the attached PDF carefully and compare it against the extracted JSON below, field by field.

{{extracted_json}}

Return a table: field | extracted value | value printed on the document (quote it exactly) | match (yes/no). Then say which flag is explained by an extraction error and which reflects a real problem on the invoice itself (e.g. the vendor's own arithmetic is wrong, or it's a duplicate). Do not recommend paying or not paying; just report what the document says.

{
  "$schema": "https://json-schema.org/draft/2020-12/schema",
  "title": "ExtractedInvoice",
  "type": "object",
  "additionalProperties": false,
  "required": ["document_type", "vendor_name", "invoice_number", "invoice_date", "currency",
               "line_items", "subtotal", "tax_total", "total", "confidence", "uncertain_fields"],
  "properties": {
    "document_type":   { "enum": ["invoice", "credit_note", "receipt", "statement", "quote", "not_an_invoice"] },
    "vendor_name":     { "type": "string", "description": "Legal name as printed on the document" },
    "vendor_tax_id":   { "type": ["string", "null"], "description": "VAT/GST/EIN etc. exactly as printed, else null" },
    "vendor_address":  { "type": ["string", "null"] },
    "bill_to_name":    { "type": ["string", "null"] },
    "invoice_number":  { "type": "string", "description": "Exactly as printed, including prefixes and leading zeros" },
    "po_number":       { "type": ["string", "null"] },
    "invoice_date":    { "type": "string", "format": "date", "description": "YYYY-MM-DD" },
    "due_date":        { "type": ["string", "null"], "format": "date" },
    "payment_terms":   { "type": ["string", "null"], "description": "e.g. 'Net 30' as printed" },
    "currency":        { "type": "string", "pattern": "^[A-Z]{3}$", "description": "ISO 4217, e.g. USD" },
    "line_items": {
      "type": "array",
      "items": {
        "type": "object",
        "additionalProperties": false,
        "required": ["description", "quantity", "unit_price", "amount"],
        "properties": {
          "description": { "type": "string" },
          "quantity":    { "type": ["number", "null"] },
          "unit_price":  { "type": ["number", "null"] },
          "amount":      { "type": "number", "description": "Line total before tax, as printed" },
          "tax_rate":    { "type": ["number", "null"], "description": "Percent, e.g. 20 for 20%" }
        }
      }
    },
    "subtotal":        { "type": "number", "description": "Before tax, after line-level discounts" },
    "discount_total":  { "type": ["number", "null"], "description": "Document-level discount as a positive number" },
    "shipping_total":  { "type": ["number", "null"] },
    "tax_total":       { "type": "number", "description": "0 if no tax shown" },
    "total":           { "type": "number", "description": "Amount due as printed" },
    "amount_paid":     { "type": ["number", "null"] },
    "bank_details_present": { "type": "boolean", "description": "true if the document shows bank/payment account details" },
    "confidence":      { "enum": ["high", "medium", "low"] },
    "uncertain_fields": { "type": "array", "items": { "type": "string" },
                          "description": "Names of fields that were hard to read, inferred or missing" }
  }
}

Conventions: numbers are plain decimals (1234.5, not "1,234.50"); credit notes use positive numbers with
document_type "credit_note"; never compute a value that isn't printed — put the field in uncertain_fields instead.

VALIDATION RULES — apply to every extracted invoice before it can be approved

Tolerance: 0.01 per amount (0.02 if more than 10 line items, to allow per-line rounding).

R1  Line sum        | SUM(line_items.amount) = subtotal                                   → else "LINE_SUM_MISMATCH"
R2  Total           | subtotal − discount_total + shipping_total + tax_total = total       → else "TOTAL_MISMATCH"
R3  Tax sanity      | if line tax_rate present: tax_total ≈ Σ(amount × rate/100)          → else "TAX_CHECK" (warning)
R4  Duplicate       | same normalised vendor + same normalised invoice_number already in the log
                    | (normalise: upper-case, keep only letters/digits, drop zeros that lead a number)  → "DUPLICATE" (block)
R5  Near-duplicate  | same vendor + same total + invoice_date within 7 days, different number → "POSSIBLE_DUPLICATE"
R6  Dates           | invoice_date not in the future and not > 365 days old; due_date ≥ invoice_date → "DATE_CHECK"
R7  Required        | vendor_name, invoice_number, invoice_date, total present             → else "MISSING_FIELD"
R8  Doc type        | document_type = "invoice" (credit notes routed separately)           → else "NOT_INVOICE"
R9  Confidence      | confidence = "low" or uncertain_fields not empty                     → "HUMAN_CHECK"
R10 Bank details    | bank_details_present = true AND vendor is new, or bank details differ from the vendor master
                    | → "VERIFY_BANK_DETAILS_BY_PHONE" (classic invoice-fraud pattern: call a known number, never the one on the invoice)

Google Sheets versions (Invoice log tab; columns: A Received, B Vendor, C Invoice no, D Invoice date,
E Due date, F Currency, G Subtotal, H Tax, I Total, J Line sum, K Status, L Approver, M Approved on, N File link,
O Norm key, P Flags):
  O2  =UPPER(REGEXREPLACE(B2,"[^A-Za-z0-9]",""))&"|"&REGEXREPLACE(UPPER(REGEXREPLACE(C2,"[^A-Za-z0-9]","")),"(^|[A-Z])0+([0-9])","$1$2")
  P2  =TEXTJOIN(", ",TRUE,
        IF(ABS(J2-G2)>0.01,"LINE_SUM_MISMATCH",""),
        IF(ABS(G2+H2-I2)>0.01,"TOTAL_MISMATCH",""),
        IF(COUNTIF($O$2:$O,O2)>1,"DUPLICATE",""),
        IF(D2>TODAY(),"DATE_CHECK",""))
  (Adjust P2's TOTAL check if you track discount/shipping columns.)
  Conditional formatting on A2:P: custom formula =$P2<>"" → red fill.

APPROVAL RULE: Status may only change to "Approved" when P is empty (or each flag is resolved with a note),
and the approver has opened the original PDF. Payment happens in your bank/accounting system, never from this sheet.

// n8n Code node — "Validate invoice" (mode: Run Once for Each Item)
// Input: the Information Extractor's structured result (n8n puts it under `output`; we also accept a top-level object)
// plus `existingKeys` (array of normalised "VENDOR|INVOICENO" keys) merged in from a Google Sheets lookup step.
const inv = $json.output ?? $json;
const existingKeys = $json.existingKeys ?? [];
const flags = [];
const tol = (inv.line_items?.length ?? 0) > 10 ? 0.02 : 0.01;
const num = (v) => (typeof v === 'number' && isFinite(v) ? v : 0);
const close = (a, b) => Math.abs(a - b) <= tol + 1e-9;

for (const f of ['vendor_name', 'invoice_number', 'invoice_date', 'total']) {
  if (inv[f] === null || inv[f] === undefined || inv[f] === '') flags.push(`MISSING_FIELD:${f}`);
}
if (inv.document_type !== 'invoice') flags.push(`NOT_INVOICE:${inv.document_type}`);

const lineSum = (inv.line_items ?? []).reduce((s, l) => s + num(l.amount), 0);
if ((inv.line_items ?? []).length && !close(lineSum, num(inv.subtotal))) flags.push('LINE_SUM_MISMATCH');

const expectedTotal = num(inv.subtotal) - num(inv.discount_total) + num(inv.shipping_total) + num(inv.tax_total);
if (!close(expectedTotal, num(inv.total))) flags.push('TOTAL_MISMATCH');

const today = new Date(); today.setHours(0, 0, 0, 0);
const invDate = new Date(inv.invoice_date);
if (isNaN(invDate) || invDate > today || (today - invDate) / 86400000 > 365) flags.push('DATE_CHECK');
if (inv.due_date && new Date(inv.due_date) < invDate) flags.push('DATE_CHECK:due_before_invoice');

const norm = (s) => String(s ?? '').toUpperCase().replace(/[^A-Z0-9]/g, '');
const key = `${norm(inv.vendor_name)}|${norm(inv.invoice_number).replace(/(^|[A-Z])0+(?=\d)/g, '$1')}`; // INV-00123 -> INV123
if (existingKeys.includes(key)) flags.push('DUPLICATE');

if (inv.confidence === 'low' || (inv.uncertain_fields ?? []).length) flags.push('HUMAN_CHECK');
if (inv.bank_details_present) flags.push('VERIFY_BANK_DETAILS_BY_PHONE_IF_NEW_OR_CHANGED');

return {
  json: {
    ...inv,
    line_sum: Math.round(lineSum * 100) / 100,
    norm_key: key,
    flags: flags.join(', '),
    status: flags.includes('DUPLICATE') ? 'Blocked – duplicate' : 'Awaiting approval',
  },
};