directus-extension-bundle-postgres-function-report

Directus bundle for issuing downloadable reports from PostgreSQL functions.

The bundle includes:

• postgres-function-report-button: an interface extension that renders a Directus-native button inside an item form.
• postgres-function-report-issuer: an endpoint extension that calls a PostgreSQL function and exports the returned rows as XLSX, CSV, or JSON.

The endpoint root is:

/postgres-function-report-issuer

When to use it

Use this extension when your report logic already lives in PostgreSQL, or when you want report output to be produced by a controlled SQL function instead of custom Directus API code.

A common setup is:

1. Create a Directus collection for a report form.
2. Give the collection the same name as the PostgreSQL function that should issue the report, or set Report Function Name on the button interface.
3. Add normal Directus fields for each function argument.
4. Add an alias/presentation field that uses the Postgres Function Report Button interface.

For example, a Directus collection named:

issue_report_customer_attendance

could contain these fields:

customer
start_date
end_date

When the user clicks the button, the interface opens a URL like this:

/postgres-function-report-issuer/issue_report_customer_attendance?customer=17&start_date=2026-05-01&end_date=2026-05-31

By default, the collection name is used as the PostgreSQL function name. If Report Function Name is set, that value is used instead. The form values are passed as named function arguments using the field names.

Route format

GET /postgres-function-report-issuer/<function_name>?<field_name>=<value>&meta_type=<xlsx|csv|json>

Examples:

/postgres-function-report-issuer/issue_report_customer_attendance?customer=17&start_date=2026-05-01&end_date=2026-05-31
/postgres-function-report-issuer/issue_report_customer_attendance?customer=17&start_date=2026-05-01&end_date=2026-05-31&meta_type=csv
/postgres-function-report-issuer/issue_report_customer_attendance?customer=17&start_date=2026-05-01&end_date=2026-05-31&meta_type=json

Fields prefixed with meta_ are reserved for endpoint behavior. All other query string fields are forwarded to the PostgreSQL function as named arguments.

If meta_type is omitted, the endpoint returns xlsx.

For CSV and XLSX, header translation is enabled by default. The issuer first tries the report function name as the default collection for field translations and, when the function name does not already start with relatorio_, also tries relatorio_${functionName} before requiring meta_collection. The button only sends meta_collection when the current form collection differs from both default collection shapes. The issuer still falls back to the authenticated Directus user's language. If a translation is missing, the issuer formats the database column name by replacing underscores with spaces and capitalizing the first letter. When Acentuação automática is enabled, generic Portuguese accent and acronym replacements are applied to the final header, for example:

aluno_nome -> Aluno nome
convidado_instituicao_nome -> Convidado instituição nome
participante_funcao -> Participante função
codigo_recuperacao -> Código recuperação
opcoes -> Opções
id -> ID

The endpoint calls PostgreSQL with bound parameters:

SELECT * FROM public.issue_report_customer_attendance("customer" => ?, "start_date" => ?, "end_date" => ?);

Interface behavior

The button interface:

• reads live form values from the Directus form renderer;
• fetches the current collection field metadata from the Directus API;
• auto-translates the default button label for English, Portuguese, and Spanish;
• ignores hidden fields;
• ignores alias fields;
• ignores primary key fields;
• excludes the current button field automatically;
• excludes technical fields configured in Exclude Fields;
• uses Report Function Name as the PostgreSQL function name when set, otherwise uses the collection name;
• uses Directus field translations for CSV/XLSX headers by default;
• uses visible form field names as PostgreSQL function argument names;
• validates required fields before enabling the button;
• sends blank field values as null;
• copies the generated URL to the clipboard instead of opening it when the user clicks with Alt;
• always logs the generated URL and current timestamp to the browser console when clicked;
• opens the issued report in a new tab by default.

If Argument Fields is empty, the interface uses all visible non-alias fields sorted by form order from /fields/<collection>. The button stays disabled while field metadata is loading, and it also stays disabled if required metadata cannot be loaded.

If Argument Fields is set, the interface uses that comma-separated list instead:

customer,start_date,end_date

Interface options

| Option | Default | Description | | ---------------------- | -------------------------------------------------------: | ----------------------------------------------------------------------------------------------------- | | Button Label | empty | Button text. Empty means auto-translate from the Directus user language. | | Icon | picture_as_pdf | Directus/Material icon name. | | Endpoint Prefix | /postgres-function-report-issuer | Endpoint root used by the button. | | Report Function Name | empty | Optional PostgreSQL function name. Empty means use the collection name. | | Export Type | xlsx | One of xlsx, csv, json. | | Use Translated Headers | true | Uses Directus field translations for CSV/XLSX headers. Missing translations are formatted from names. | | Acentuação automática | true | Applies generic Portuguese accent and acronym replacements to CSV/XLSX headers. | | Argument Fields | empty | Optional comma-separated field list. Empty means infer from visible form order. | | Required Fields | empty | Optional comma-separated required field list. Empty means use Directus metadata. | | Exclude Fields | id,date_created,user_created,date_updated,user_updated | Fields ignored when inferring arguments. | | Open in New Tab | true | Opens the report in a new tab. | | Align Right | true | Aligns the button to the right side of the field row. | | Debug Console | false | Prints browser console diagnostics for troubleshooting. |

Debug Console can include current form values in the browser console. Keep it disabled unless you are actively troubleshooting.

The generated report URL is always printed on click with the browser timestamp, even when Debug Console is disabled. Use Alt while clicking the button to copy the generated URL to the clipboard instead of opening it.

When Button Label is empty, the default label is translated from the Directus user language:

| Language | Label | | ---------- | ------------------ | | English | Issue Report | | Portuguese | Emitir Relatório | | Spanish | Emitir informe |

Any custom Button Label value is used as-is.

When Use Translated Headers is off, CSV and XLSX header rows use the database column names exactly as returned by the PostgreSQL function. Acentuação automática only applies when translated/humanized headers are active.

Security model

By default, the endpoint:

• requires an authenticated Directus user;
• accepts any PostgreSQL-safe function name;
• only calls functions in schema public;
• escapes SQL identifiers;
• uses bound parameters for arguments;
• binds blank path arguments and null path arguments as PostgreSQL NULL;
• can restrict access to specific Directus role UUIDs;
• only exports xlsx, csv, or json.
• escapes spreadsheet formula prefixes in CSV and XLSX output.
• styles XLSX headers in bold and sizes columns from median content length while preserving native Excel cell types when possible.

Optional environment variables:

POSTGRES_FUNCTION_REPORT_SCHEMA=public
POSTGRES_FUNCTION_REPORT_ALLOWED_ROLES=11111111-1111-1111-1111-111111111111,22222222-2222-2222-2222-222222222222
POSTGRES_FUNCTION_REPORT_ALLOWED_FUNCTIONS=issue_report_customer_attendance,issue_report_invoice_summary

If POSTGRES_FUNCTION_REPORT_ALLOWED_ROLES is set, only users with one of those Directus role UUIDs can call the endpoint.

If POSTGRES_FUNCTION_REPORT_ALLOWED_FUNCTIONS is set, only listed functions are callable. When Report Function Name is used, add that PostgreSQL function name to the allow list.

For production, prefer an explicit allow list:

POSTGRES_FUNCTION_REPORT_ALLOWED_FUNCTIONS=issue_report_customer_attendance,issue_report_invoice_summary

PostgreSQL function requirements

The PostgreSQL function should return a tabular result.

Example:

CREATE OR REPLACE FUNCTION public.issue_report_customer_attendance(
    p_customer_id integer,
    p_start_date date,
    p_end_date date
)
RETURNS TABLE (
    customer_name text,
    course_name text,
    class_name text,
    session_date date,
    student_name text,
    attendance_percentage numeric,
    present boolean
)
LANGUAGE sql
STABLE
AS $$
    SELECT
        customer_name,
        course_name,
        class_name,
        session_date,
        student_name,
        attendance_percentage,
        present
    FROM public.report_customer_attendance
    WHERE customer_id = p_customer_id
      AND session_date >= p_start_date
      AND session_date <= p_end_date;
$$;

Install

npm install
npm run build

Install the built extension into your Directus extensions directory, then restart Directus.

Development

npm run dev

This runs the Directus extension builder in watch mode.

Using the Button with Custom Backend Routes

You can use the Postgres Function Report Button interface independently of the provided backend (postgres-function-report-issuer).

If your project uses a custom backend route or a different report issuer, simply set the Endpoint Prefix option on the button interface to your custom endpoint path. The button will generate URLs using this prefix and pass form values as query parameters, just as it does for the default issuer.

Example:

If your backend route is /custom-reports/certificado, set:

Endpoint Prefix: /custom-reports
Report Function Name: certificado

The button will generate URLs like:

/custom-reports/certificado?alunoId=1&turmaId=null&startDate=null&endDate=null

Notes:

• The backend part of this bundle is optional. You can use just the button interface with any compatible backend that accepts query parameters in the expected format.
• Features like header translation, meta_collection, and export type are only available if your backend implements them.
• The button always passes form values as query parameters and respects all interface options (argument fields, required fields, etc.).

This makes the button interface flexible for integration with custom reporting endpoints or legacy systems.