Saltar al contenido principal

Accounts Payable Dashboards

This document describes the Accounts Payable dashboards to build in Metabase using the AP reporting views. These dashboards are created in the Metabase UI; there is no automated export/import in this repo.

Prerequisites​

  • Run AP migrations to create the reporting views:
    • accounts_payable_bill_balance_v
    • accounts_payable_aging_v
    • accounts_payable_payments_v
  • Ensure Metabase is connected to the application database.

Dashboard: AP Overview​

Use accounts_payable_bill_balance_v and accounts_payable_aging_v.

Widgets:

  • Total AP balance: sum outstanding from accounts_payable_bill_balance_v.
  • Overdue balance: sum balance_due where due_date < CURRENT_DATE and due_date IS NOT NULL.
  • Bills due this week: sum balance_due where due_date between CURRENT_DATE and CURRENT_DATE + 7.
  • Bills due next 30 days: sum balance_due where due_date between CURRENT_DATE and CURRENT_DATE + 30.

Filters:

  • business_id (required if multi-tenant).
  • Optional supplier_id, status, currency_code.

Null due_date handling:

  • Exclude due_date IS NULL from overdue and due-range widgets.
  • Show bucket = 'No due date' in a separate widget if needed.

Dashboard: Cash Planning​

Use accounts_payable_bill or accounts_payable_aging_v.

Widgets:

  • Upcoming scheduled payments: bills with status = 'scheduled' and due_date in a selectable range.
  • Cash required by date: daily sum of balance_due where due_date = :date and status IN ('approved', 'scheduled').

Filters:

  • business_id (required if multi-tenant).
  • Date range for due_date.

Dashboard: Supplier Risk​

Use accounts_payable_bill_balance_v and base tables as needed.

Widgets:

  • AP balance by supplier: sum outstanding grouped by supplier_id.
  • DPO (Days Payable Outstanding): average days between purchase_date and the most recent posted payment per bill. Default filter: last 12 months.

Filters:

  • business_id (required if multi-tenant).
  • Optional supplier_id, date range for purchase_date.

Notes​

  • FX deviation checks are not implemented yet; if needed, add a Metabase question that compares exchange_rate to your reference rate table.
  • Approval threshold and role-based permissions are handled in the API and are not enforced in Metabase queries.