Invoices

finance

Track invoices, payments, and clients for billing and accounts receivable.

Collections

Clients

Billing clients and their details

Schema Fields

name:stringemail:stringcompany:stringaddress:stringtax_id:stringpayment_terms_days:integer

Invoices

Invoices issued to clients with embedded line items

Schema Fields

invoice_number:stringcurrency:stringstatus:stringissued_at:stringdue_at:stringpaid_at:stringdescription:stringnotes:stringline_items:array

Payments

Payments received against invoices

Schema Fields

amount:numbercurrency:stringreceived_at:stringmethod:stringreference:stringnotes:string

Relationships

invoices

billed_to

clientsInvoice issued to client

payments

payment_for

invoicesPayment applied to invoice. A single payment can cover multiple invoices — each relationship carries the allocated amount.

Metadata

allocated:number

Example Queries

Query this template's data using the SQL endpoint.

List all invoices with computed totals

Sum line item amounts to compute the invoice total directly from the embedded array.

SELECT
  data.invoice_number.:String as invoice_number,
  data.status.:String as status,
  arraySum(CAST(data.line_items[].amount, 'Array(Float64)')) as total
FROM records FINAL
WHERE workspace_id = {workspace_id:String}
  AND collection = 'invoices'
  AND deleted = false
ORDER BY data.issued_at.:String DESC

Revenue by status

Aggregate invoice totals grouped by status to see how much is paid, overdue, or pending.

SELECT
  data.status.:String as status,
  count() as invoice_count,
  sum(arraySum(CAST(data.line_items[].amount, 'Array(Float64)'))) as total
FROM records FINAL
WHERE workspace_id = {workspace_id:String}
  AND collection = 'invoices'
  AND deleted = false
GROUP BY status
ORDER BY total DESC

Top line items by revenue

Extract individual line items from all invoices and rank them by total revenue across all invoices.

SELECT
  item.description.:String as item_description,
  count() as times_billed,
  sum(CAST(item.amount, 'Float64')) as total_revenue
FROM records FINAL
ARRAY JOIN data.line_items[] as item
WHERE workspace_id = {workspace_id:String}
  AND collection = 'invoices'
  AND deleted = false
GROUP BY item_description
ORDER BY total_revenue DESC

Payment reconciliation

Compare invoice totals against allocated payments to find outstanding balances. Uses CTEs to join records with relationship metadata.

WITH
  inv AS (
    SELECT
      id,
      data.invoice_number.:String as invoice_number,
      data.status.:String as status,
      arraySum(CAST(data.line_items[].amount, 'Array(Float64)')) as invoice_total
    FROM records FINAL
    WHERE workspace_id = {workspace_id:String}
      AND collection = 'invoices'
      AND deleted = false
  ),
  pay AS (
    SELECT
      target_id,
      sum(CAST(data.allocated, 'Float64')) as total_paid
    FROM relationships FINAL
    WHERE workspace_id = {workspace_id:String}
      AND rel_type = 'payment_for'
      AND deleted = false
    GROUP BY target_id
  )
SELECT
  inv.invoice_number,
  inv.status,
  inv.invoice_total,
  coalesce(pay.total_paid, 0) as total_paid,
  inv.invoice_total - coalesce(pay.total_paid, 0) as balance
FROM inv
LEFT JOIN pay ON pay.target_id = inv.id
ORDER BY balance DESC

Overdue invoices

Find invoices past their due date that haven't been paid yet.

SELECT
  data.invoice_number.:String as invoice_number,
  data.due_at.:String as due_at,
  arraySum(CAST(data.line_items[].amount, 'Array(Float64)')) as total
FROM records FINAL
WHERE workspace_id = {workspace_id:String}
  AND collection = 'invoices'
  AND deleted = false
  AND data.status.:String IN ('sent', 'overdue')
  AND CAST(data.due_at.:String, 'Date') < today()
ORDER BY due_at ASC

Quick Start

Create the collections using the Rekor CLI:

rekor collections upsert invoices --workspace billing --schema @invoices.json

Full Schema (JSON)

Copy the complete template definition:

{
  "collections": [
    {
      "id": "clients",
      "name": "Clients",
      "description": "Billing clients and their details",
      "icon": "building",
      "color": "#3b82f6",
      "json_schema": {
        "type": "object",
        "required": [
          "name",
          "email"
        ],
        "properties": {
          "name": {
            "type": "string"
          },
          "email": {
            "type": "string",
            "format": "email"
          },
          "company": {
            "type": "string"
          },
          "address": {
            "type": "string"
          },
          "tax_id": {
            "type": "string"
          },
          "payment_terms_days": {
            "type": "integer",
            "default": 30
          }
        }
      }
    },
    {
      "id": "invoices",
      "name": "Invoices",
      "description": "Invoices issued to clients with embedded line items",
      "icon": "file-text",
      "color": "#22c55e",
      "json_schema": {
        "type": "object",
        "required": [
          "invoice_number",
          "currency",
          "status",
          "issued_at",
          "line_items"
        ],
        "properties": {
          "invoice_number": {
            "type": "string"
          },
          "currency": {
            "type": "string",
            "enum": [
              "USD",
              "EUR",
              "GBP",
              "BRL"
            ],
            "default": "USD"
          },
          "status": {
            "type": "string",
            "enum": [
              "draft",
              "sent",
              "paid",
              "overdue",
              "cancelled"
            ]
          },
          "issued_at": {
            "type": "string",
            "format": "date"
          },
          "due_at": {
            "type": "string",
            "format": "date"
          },
          "paid_at": {
            "type": "string",
            "format": "date"
          },
          "description": {
            "type": "string"
          },
          "notes": {
            "type": "string"
          },
          "line_items": {
            "type": "array",
            "items": {
              "type": "object",
              "required": [
                "description",
                "quantity",
                "unit_price",
                "amount"
              ],
              "properties": {
                "description": {
                  "type": "string"
                },
                "quantity": {
                  "type": "number",
                  "minimum": 0
                },
                "unit_price": {
                  "type": "number",
                  "minimum": 0
                },
                "amount": {
                  "type": "number",
                  "minimum": 0
                },
                "tax_rate": {
                  "type": "number",
                  "minimum": 0,
                  "maximum": 100
                }
              }
            }
          }
        }
      }
    },
    {
      "id": "payments",
      "name": "Payments",
      "description": "Payments received against invoices",
      "icon": "credit-card",
      "color": "#f59e0b",
      "json_schema": {
        "type": "object",
        "required": [
          "amount",
          "currency",
          "received_at",
          "method"
        ],
        "properties": {
          "amount": {
            "type": "number",
            "minimum": 0
          },
          "currency": {
            "type": "string",
            "enum": [
              "USD",
              "EUR",
              "GBP",
              "BRL"
            ],
            "default": "USD"
          },
          "received_at": {
            "type": "string",
            "format": "date"
          },
          "method": {
            "type": "string",
            "enum": [
              "bank_transfer",
              "credit_card",
              "paypal",
              "check",
              "cash",
              "other"
            ]
          },
          "reference": {
            "type": "string"
          },
          "notes": {
            "type": "string"
          }
        }
      }
    }
  ],
  "relationships": [
    {
      "type": "billed_to",
      "source": "invoices",
      "target": "clients",
      "description": "Invoice issued to client"
    },
    {
      "type": "payment_for",
      "source": "payments",
      "target": "invoices",
      "description": "Payment applied to invoice. A single payment can cover multiple invoices — each relationship carries the allocated amount.",
      "data_schema": {
        "type": "object",
        "properties": {
          "allocated": {
            "type": "number",
            "minimum": 0,
            "description": "Amount from this payment allocated to this invoice"
          }
        },
        "required": [
          "allocated"
        ]
      }
    }
  ]
}