Facturas

finance

Gestione facturas, pagos y clientes para facturación y cuentas por cobrar.

Colecciones

Clients

Billing clients and their details

Campos del esquema

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

Invoices

Invoices issued to clients with embedded line items

Campos del esquema

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

Payments

Payments received against invoices

Campos del esquema

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

Relaciones

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.

Metadatos

allocated:number

Consultas de ejemplo

Consulte los datos de esta plantilla usando el 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

Inicio rápido

Cree las colecciones usando el Rekor CLI:

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

Esquema completo (JSON)

Copie la definición completa de la plantilla:

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