How to query invoice and fee data from the Lago database (self-hosted only)

The Lago UI includes a native CSV export for invoices and fees. For programmatic use you have three options: direct SQL queries against the Lago database, the GET /api/v1/invoices endpoint (which includes fee-level detail), or webhooks that deliver invoice data in real time on finalization. The two queries below cover the SQL route: one for fee line items, one for invoice totals.

This article applies to instances running Lago self-hosted. Cloud customers do not have direct database access and should contact Lago Support.

What you need to know about the schema

A few schema conventions explain the decodes in the queries below:

• All amounts are stored in cents.

• Fee types and payment statuses are stored as integers, so the queries decode them with CASE statements.

• Invoice status values are non-sequential: draft=0, finalized=1, voided=2, generating=3, failed=4, open=5, closed=6, pending=7. Both queries filter on status = 1 to return finalized invoices only.

Coupons, credit notes, and prepaid credits are applied at the invoice level, not as fee line items. Query 1 returns the individual fees; Query 2 returns the invoice totals where those discounts appear.

Query 1: fee line items with type and payment status

This returns one row per fee, joined to its invoice and (for usage-based fees) its billable metric.

SELECT
    fees.id AS fee_id,
    fees.created_at AS fee_created_at,
    invoices.issuing_date AS fee_issuing_date,
    fees.amount_currency AS fee_currency,
    fees.amount_cents AS fee_amount_cents,
    fees.taxes_amount_cents AS fee_taxes_amount_cents,
    fees.invoice_id AS invoice_id,
    invoices.number AS invoice_number,
    billable_metrics.name AS billable_metric_name,
    fees.invoiceable_type,
    CASE
        WHEN fees.fee_type = 0 THEN 'usage-based charge'
        WHEN fees.fee_type = 1 THEN 'add-on fee'
        WHEN fees.fee_type = 2 THEN 'subscription fee'
        WHEN fees.fee_type = 3 THEN 'credits purchased'
        WHEN fees.fee_type = 4 THEN 'commitment fee'
        WHEN fees.fee_type = 5 THEN 'fixed charge'
    END AS fee_type,
    CASE
        WHEN fees.payment_status = 0 THEN 'pending'
        WHEN fees.payment_status = 1 THEN 'succeeded'
        WHEN fees.payment_status = 2 THEN 'failed'
        WHEN fees.payment_status = 3 THEN 'refunded'
    END AS fee_payment_status,
    CASE
        WHEN invoices.payment_status = 0 THEN 'pending'
        WHEN invoices.payment_status = 1 THEN 'succeeded'
        WHEN invoices.payment_status = 2 THEN 'failed'
    END AS invoice_payment_status,
    (fees.properties ->> 'from_datetime')::timestamp AS service_period_from,
    (fees.properties ->> 'to_datetime')::timestamp AS service_period_to
FROM invoices
    LEFT JOIN fees ON fees.invoice_id = invoices.id
    LEFT JOIN charges ON charges.id = fees.charge_id
    LEFT JOIN billable_metrics ON billable_metrics.id = charges.billable_metric_id
WHERE invoices.status = 1  -- finalized invoices only
ORDER BY invoices.issuing_date DESC;

Query 2: invoice totals with discount breakdowns

This returns one row per invoice, including the coupon, credit note, and prepaid credit amounts that do not appear as fees in Query 1. Each of those columns reduces the invoice total.

SELECT
    invoices.id AS invoice_id,
    invoices.number AS invoice_number,
    invoices.created_at AS invoice_creation_date,
    invoices.issuing_date AS invoice_issuing_date,
    CASE
        WHEN invoices.invoice_type = 0 THEN 'subscription start or renewal'
        WHEN invoices.invoice_type = 1 THEN 'add-on'
        WHEN invoices.invoice_type = 2 THEN 'prepaid credits purchased'
        WHEN invoices.invoice_type = 3 THEN 'one-off invoice'
        WHEN invoices.invoice_type = 4 THEN 'in-advance charges'
        WHEN invoices.invoice_type = 5 THEN 'progressive billing'
    END AS invoice_reason,
    invoices.currency,
    invoices.fees_amount_cents AS total_fees_before_discounts,
    invoices.coupons_amount_cents,                        -- reduces total
    invoices.credit_notes_amount_cents,                   -- reduces total
    invoices.prepaid_credit_amount_cents,                 -- reduces total
    invoices.progressive_billing_credit_amount_cents,     -- reduces total
    invoices.taxes_amount_cents,
    invoices.total_amount_cents,
    invoices.total_paid_amount_cents,
    CASE
        WHEN invoices.payment_status = 0 THEN 'pending'
        WHEN invoices.payment_status = 1 THEN 'succeeded'
        WHEN invoices.payment_status = 2 THEN 'failed'
    END AS payment_status,
    invoices.payment_due_date
FROM invoices
WHERE invoices.status = 1  -- finalized invoices only
ORDER BY invoices.issuing_date DESC;

To filter by date range, add a clause such as AND invoices.issuing_date BETWEEN '2025-01-01' AND '2025-12-31' to the WHERE clause and adjust the dates.

This article reflects guidance drawn from customer case resolutions. It is not officially supported documentation and may not apply to all situations.