Finance analytics and AI workflow automation for CFO teams

-- Postgres example: target table and upsert from a staging table
-- Assume staging.qb_invoices is loaded from QuickBooks API by an ETL job

CREATE TABLE IF NOT EXISTS qb_invoices (
  id TEXT PRIMARY KEY,
  doc_number TEXT,
  customer_name TEXT,
  txn_date DATE,
  due_date DATE,
  total NUMERIC(12,2),
  balance NUMERIC(12,2),
  created_at TIMESTAMPTZ DEFAULT now()
);

INSERT INTO qb_invoices (id, doc_number, customer_name, txn_date, due_date, total, balance)
SELECT id, doc_number, customer_name, txn_date, due_date, total, balance
FROM staging.qb_invoices
ON CONFLICT (id) DO UPDATE
SET doc_number = EXCLUDED.doc_number,
    customer_name = EXCLUDED.customer_name,
    txn_date = EXCLUDED.txn_date,
    due_date = EXCLUDED.due_date,
    total = EXCLUDED.total,
    balance = EXCLUDED.balance;

# Fetch Xero invoices and upsert into Postgres
# env: XERO_TOKEN, XERO_TENANT_ID, DATABASE_URL
import os, requests, psycopg

token = os.getenv("XERO_TOKEN")
tenant = os.getenv("XERO_TENANT_ID")
resp = requests.get(
    "https://api.xero.com/api.xro/2.0/Invoices",
    headers={
        "Authorization": "Bearer " + token,
        "Xero-tenant-id": tenant,
        "Accept": "application/json",
    },
)
resp.raise_for_status()
inv = resp.json().get("Invoices", [])

with psycopg.connect(os.getenv("DATABASE_URL")) as conn:
    with conn.cursor() as cur:
        for i in inv:
            cur.execute(
                """
                INSERT INTO xero_invoices (id, number, contact, date, due, total, amount_due)
                VALUES (%s,%s,%s,%s,%s,%s,%s)
                ON CONFLICT (id) DO UPDATE
                SET number = EXCLUDED.number,
                    total = EXCLUDED.total,
                    amount_due = EXCLUDED.amount_due
                """,
                (
                    i.get("InvoiceID"),
                    i.get("InvoiceNumber"),
                    (i.get("Contact") or {}).get("Name"),
                    (i.get("DateString") or "")[:10],
                    (i.get("DueDateString") or "")[:10],
                    i.get("Total"),
                    i.get("AmountDue"),
                ),
            )
    conn.commit()

// Node: QuickBooks Online query -> Postgres
// env: QBO_TOKEN, QBO_COMPANY_ID, DATABASE_URL
import fetch from "node-fetch";
import pg from "pg";

const token = process.env.QBO_TOKEN;
const companyId = process.env.QBO_COMPANY_ID;
const qbQuery = "SELECT Id, DocNumber, TxnDate, DueDate, TotalAmt, Balance, CustomerRef FROM Invoice STARTPOSITION 1 MAXRESULTS 50";
const url = "https://quickbooks.api.intuit.com/v3/company/" + companyId + "/query?query=" + encodeURIComponent(qbQuery);

const res = await fetch(url, {
  headers: {
    Authorization: "Bearer " + token,
    Accept: "application/json",
  },
});
const data = await res.json();
const invoices = (data.QueryResponse && data.QueryResponse.Invoice) || [];

const { Client } = pg;
const db = new Client({ connectionString: process.env.DATABASE_URL });
await db.connect();

for (const inv of invoices) {
  await db.query(
    "INSERT INTO qb_invoices (id, doc_number, txn_date, due_date, total, balance, customer_name) VALUES ($1,$2,$3,$4,$5,$6,$7) ON CONFLICT (id) DO UPDATE SET doc_number=EXCLUDED.doc_number, total=EXCLUDED.total, balance=EXCLUDED.balance;",
    [
      inv.Id,
      inv.DocNumber,
      inv.TxnDate,
      inv.DueDate,
      inv.TotalAmt,
      inv.Balance,
      inv.CustomerRef && inv.CustomerRef.name,
    ]
  );
}

await db.end();

// C++: call Xero API with libcurl and upsert to Postgres with libpqxx
// compile with -lcurl -lpqxx -lpq
#include <cstdlib>
#include <string>
#include <iostream>
#include <curl/curl.h>
#include <nlohmann/json.hpp>
#include <pqxx/pqxx>

static size_t write_cb(void* contents, size_t size, size_t nmemb, void* userp) {
  size_t total = size * nmemb;
  std::string* s = reinterpret_cast<std::string*>(userp);
  s->append(reinterpret_cast<char*>(contents), total);
  return total;
}

int main() {
  std::string token = std::getenv("XERO_TOKEN");
  std::string tenant = std::getenv("XERO_TENANT_ID");

  CURL* curl = curl_easy_init();
  std::string body;
  struct curl_slist* headers = NULL;
  headers = curl_slist_append(headers, ("Authorization: Bearer " + token).c_str());
  headers = curl_slist_append(headers, ("Xero-tenant-id: " + tenant).c_str());
  headers = curl_slist_append(headers, "Accept: application/json");

  curl_easy_setopt(curl, CURLOPT_URL, "https://api.xero.com/api.xro/2.0/Contacts");
  curl_easy_setopt(curl, CURLOPT_HTTPHEADER, headers);
  curl_easy_setopt(curl, CURLOPT_WRITEFUNCTION, write_cb);
  curl_easy_setopt(curl, CURLOPT_WRITEDATA, &body);
  curl_easy_perform(curl);
  curl_easy_cleanup(curl);

  auto j = nlohmann::json::parse(body);
  pqxx::connection c(std::getenv("DATABASE_URL"));
  pqxx::work tx(c);
  for (auto& contact : j["Contacts"]) {
    tx.exec_params(
      "INSERT INTO xero_contacts (id, name) VALUES ($1,$2) ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name;",
      contact["ContactID"].get<std::string>(),
      contact["Name"].get<std::string>()
    );
  }
  tx.commit();
  return 0;
}

# R: Xero invoices -> Postgres
# env: XERO_TOKEN, XERO_TENANT_ID, DATABASE_URL
library(httr2)
library(jsonlite)
library(DBI)
library(RPostgres)

token <- Sys.getenv("XERO_TOKEN")
tenant <- Sys.getenv("XERO_TENANT_ID")

resp <- request("https://api.xero.com/api.xro/2.0/Invoices") |>
  req_headers(Authorization = paste("Bearer", token), `Xero-tenant-id` = tenant, Accept = "application/json") |>
  req_perform()

invoices <- fromJSON(resp_body_string(resp))$Invoices

con <- dbConnect(RPostgres::Postgres(), url = Sys.getenv("DATABASE_URL"))
cols <- c("InvoiceID","InvoiceNumber","DateString","DueDateString","Total","AmountDue")
dbWriteTable(con, "xero_invoices", invoices[cols], append = TRUE, row.names = FALSE)
dbDisconnect(con)

-- Power BI DAX measures on QuickBooks/Xero model
AR Balance := SUM('Invoices'[AmountDue])

AR Days Outstanding :=
VAR todayDate = TODAY()
RETURN
  AVERAGEX(
    FILTER('Invoices', 'Invoices'[AmountDue] > 0),
    todayDate - 'Invoices'[DueDate]
  )

Gross Margin % :=
DIVIDE(
  SUM('GL'[Revenue]) - SUM('GL'[COGS]),
  SUM('GL'[Revenue])
)