HubfiscalStoreBillpt

Description

Stores the output of BillptCreateInvoiceFromShopify (or any Bill.pt document node) in the Hub Fiscal centralized MySQL/MariaDB database. Designed to be placed immediately after the Bill.pt creation step, whether the invoice succeeded or failed.

Key features:

Upsert: If the order already has a record it updates it, otherwise inserts. Prevents duplicates on retries.
Full traceability: Stores atcud, fiscal_doc_number, totals, contact data, and the raw JSON payload.
Atomic transaction: Inserts into documents, fiscal_status and logs in one transaction — rolls back fully on error.
Failure recording: Stores failed attempts with status: failed and the error message, incrementing retry_count.

Configuration

Field	Type	Required	Description
credentials_id	credentials	Yes	Hub Fiscal MySQL/MariaDB credential (`sqlQuery` type)
country_iso	text	No	Country ISO code (PT, ES, AO…). Defaults to the Bill.pt contact country. Default: `PT`
entity_source	text	No	Order source: `shopify`, `manual`, `api`. Default: `shopify`
document_type	select	No	Fallback document type if not present in the Bill.pt response: FT, FR, ORC, NC. Default: `FT`
save_payload	select	No	Save full JSON payload (up to 50KB) in `payload_json`. Default: Yes

Credentials

Requires a sqlQuery credential (MySQL/MariaDB) with:

host: Database host (e.g. 127.0.0.1)
user: Database user (e.g. user_hubfiscal)
password: User password
database: Hub Fiscal database name (e.g. hubfiscal)
port: Port (default: 3306 or 3307 for dev)

Database Schema

The Hub Fiscal database requires 5 tables. The SQL schema is at modules/hubfiscalStoreBillpt/schema.sql.

Table	Purpose
documents	One record per fiscal document — status, ATCUD, totals, payload
fiscal_status	Each fiscalization attempt with full request/response
logs	System events and errors
countries	Countries enabled for fiscalization
reports_cache	Pre-calculated report cache

The documents table has a unique constraint on (entity_id, entity_source, type) to prevent duplicates.

Input

Expected input: the output of BillptCreateInvoiceFromShopify or any Bill.pt document node:

{
  "success": true,
  "document_id": 789,
  "document_number": "FT FHPT/3",
  "shopify_order_id": 6684429451477,
  "shopify_order_number": 14522,
  "document": {
    "id": 789,
    "tipificacao": "FT",
    "invoice_number": "FT FHPT/3",
    "atcud": "J6X8WR2G-3",
    "net_total": 63.56,
    "tax_total": 14.62,
    "gross_total": 78.18,
    "moeda": "EUR_€",
    "invoice_date": "2026-04-20",
    "contato": {
      "nome": "John Doe",
      "nif": "999999990",
      "email": "customer@example.com",
      "pais": "PT"
    }
  }
}

Output

{
  "success": true,
  "hub_document_id": 15,
  "entity_id": "6684429451477",
  "order_number": "14522",
  "country_iso": "PT",
  "type": "FT",
  "status": "completed",
  "fiscal_provider": "billpt",
  "fiscal_doc_id": "789",
  "fiscal_doc_number": "FT FHPT/3",
  "atcud": "J6X8WR2G-3",
  "total_gross": 78.18,
  "currency": "EUR",
  "message": "Documento registrado en Hub Fiscal (ID: 15, ATCUD: J6X8WR2G-3)"
}

Field Mapping

The node auto-maps fields from the Bill.pt response:

Hub Fiscal field	Source
`entity_id`	`data.shopify_order_id` → `data.entity_id` → `document.id`
`order_number`	`data.shopify_order_number` → `data.order_number`
`country_iso`	config → `document.contato.pais` → `PT`
`type`	`document.tipificacao` → config `document_type` → `FT`
`fiscal_doc_id`	`data.document_id` → `document.id`
`fiscal_doc_number`	`document.invoice_number` → `data.document_number`
`atcud`	`document.atcud`
`currency`	`document.moeda` (strips `_€` suffix)
`issued_at`	`document.invoice_date` → `document.system_entry_date` → today
`contact_name`	`document.contato.nome`
`contact_nif`	`document.contato.nif`
`contact_email`	`document.contato.email` → `data.email`

Failure Recording

When the upstream Bill.pt node returns success: false, the document is still stored with status: failed. On each retry, retry_count is incremented. This allows monitoring pending or failed fiscalizations from the Hub Fiscal database.

-- Find failed documents
SELECT entity_id, order_number, error_message, retry_count
FROM documents
WHERE status = 'failed' AND fiscal_provider = 'billpt'
ORDER BY updated_at DESC;

Notes

The ATCUD (atcud) field is required by Portuguese law (AT regulation) and stored as a dedicated column for easy querying and audit.
currency is normalized from Bill.pt’s EUR_€ format to standard ISO EUR.
payload_json stores the full input up to 50KB. Set save_payload: false to store only fiscal data.
The action field in fiscal_status is set to void for NC (Credit Note) documents and create for all others.
MariaDB users: the credential test (sqlQuery type) includes a GSSAPI auth workaround. Use mysql_native_password for the database user to avoid authentication errors.

BillptCreateInvoiceFromShopify - Creates the Bill.pt invoice from Shopify
HubfiscalGetDocuments - Query and filter stored documents
HubfiscalUpdateDocument - Update an existing document record
BillptSendDocumentEmail - Send the invoice by email after storing