Faturas

finance

Gerencie faturas, pagamentos e clientes para cobrança e contas a receber.

Coleções

Clients

Billing clients and their details

Campos do Schema

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

Invoices

Invoices issued to clients with embedded line items

Campos do Schema

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

Payments

Payments received against invoices

Campos do Schema

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

Relacionamentos

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.

Metadados

allocated:number

Consultas de Exemplo

Consulte os dados deste template usando o endpoint SQL.

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

Início Rápido

Crie as coleções usando o Rekor CLI:

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

Schema Completo (JSON)

Copie a definição completa do template:

{
  "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"
        ]
      }
    }
  ]
}