Hypertegrity Node-RED Endpoint Nodes

A collection of Node-RED nodes that publish curated datasets as public OpenData endpoints.

📝 Introduction

This package provides the PostgreSQL CSV API node, which turns configurable PostgreSQL datasets into CSV downloads behind an OpenData endpoint. Node-RED acts as the data pipeline, APISIX as the external API gateway.

The node enables you to:

• Register models — declare exactly which schema, table and timestamp column may be published.
• Control the payload — exclude columns and entities, rename public CSV headers, add PostGIS coordinates.
• Serve CSV — expose a single, read-only HTTP endpoint that queries PostgreSQL live.

Included Nodes

| Node | Description | |------|-------------| | postgresql-csv-api | Publishes configurable PostgreSQL datasets as CSV via GET <path prefix>/:model.csv (default /api/opendata/v1); the exportable models and the database connection are configured in the editor |

The node has no input and is therefore not configured by message (msg.config); the export is controlled by the time parameters ?month=YYYY-MM / ?year=YYYY.

Notes on upgrading to 0.3.2

• Exports are streamed from PostgreSQL; large periods no longer need memory for the whole result. The CSV content is unchanged. If a database error occurs after the CSV has started, the download is aborted instead of HTTP 500.
• date and timestamp (without time zone) no longer depend on the time zone of the Node-RED process; they are written as before on a UTC server (e.g. 2024-05-01T00:00:00.000Z). On servers with another time zone the values were shifted before.
• With excluded entities, rows without an entity ID are exported (they were dropped before); entity columns of type integer/uuid work. The entity column is configurable (default entity_id).
• Periods without data return the full CSV header also for materialized views.
• An empty port falls back to DB_PORT. Identifiers are quoted, so reserved names such as user work.
• New optional settings: path prefix of the API node, operating limits of the registry (time limit, concurrent exports) and "Excel compatible" per model. All are off or unchanged by default.
• Invalid models no longer stop the registry: they are skipped with an error, all valid models are served.
• The cause of a failed export (HTTP 500/504) is passed to Catch nodes with a message (msg.topic = model, msg.payload with model, status code, method, path and query); before it was only logged.
• The endpoint has no authentication of its own; protect it with the API gateway or httpNodeMiddleware. Database SSL/TLS is not configurable yet.
• Loading entity IDs in the editor reads table contents and now requires the flows.write permission; values of a column marked as excluded are not loaded.
• "Excel compatible": only complete numbers such as -1.5 stay unprefixed; text such as -1+1 gets ' as well.
• numeric[]: values beyond JavaScript number precision keep their exact PostgreSQL digits (e.g. [1.1,12345678901234567890] instead of [1.1,12345678901234567000]); usual values are written as before. Numbers inside json/jsonb, bytea and interval are written as before (see help).
• Excluding own longitude/latitude table columns no longer drops the coordinates computed from the Point column.
• ?month=0000-MM / ?year=0000 return HTTP 400 (HTTP 500 before); the file name always has a four-digit year (e.g. -0999.csv).
• The time limit per export is capped at 2147483 s with a warning; larger values aborted every export at once.

Installation

Install via the Node-RED Palette Manager or using npm:

npm install @htag/node-red-contrib-hypertegrity-endpoints

🌍 About the Project / Über das Projekt

📍 Über dieses Projekt / About this Project

DE: Dieses Open Source Projekt wurde von der Stadt Menden (Sauerland) im Rahmen ihres Smart-City-Vorhabens beauftragt. Es ist nicht im Rahmen des Projekts Südwestcluster (SWCL) entstanden – im Unterschied zu den übrigen Paketen dieser Node-RED-Bibliothek.

EN: This open source project was commissioned by the City of Menden (Sauerland) as part of its smart city programme. It was not created within the Südwestcluster (SWCL) project — unlike the other packages of this Node-RED library.

💡 Hintergrund / Background

DE: Die Entwicklung wurde durch die Stadt Menden (Sauerland) finanziert. Dadurch können wir diese Lösung der gesamten Community als Open Source zur Verfügung stellen.

EN: The development was funded by the City of Menden (Sauerland). This allows us to make this solution available to the entire community as open source.

🌐 Open Source Philosophie / Open Source Philosophy

DE: Wir unterstützen aktiv die OSS-Community und fördern kollaborative Entwicklung. Durch die Veröffentlichung als Open Source profitieren alle Beteiligten:

• Community: Freier Zugang zu moderner Smart City Technologie
• Transparenz: Nachvollziehbare und überprüfbare Lösungen
• Innovation: Gemeinsame Weiterentwicklung durch verschiedene Akteure
• Nachhaltigkeit: Langfristige Verfügbarkeit und Wartbarkeit

EN: We actively support the OSS community and promote collaborative development. Releasing it as open source benefits everyone involved:

• Community: Free access to modern Smart City technology
• Transparency: Traceable and verifiable solutions
• Innovation: Collaborative development by various actors
• Sustainability: Long-term availability and maintainability

🙏 Danksagung / Acknowledgements

DE: Ein besonderer Dank gilt der Stadt Menden (Sauerland) für die Beauftragung und die Freigabe dieser Lösung als Open Source.

EN: Special thanks goes to the City of Menden (Sauerland) for commissioning this work and for releasing this solution as open source.

📜 License & Contact / Lizenz & Kontakt

© 2026 HYPERTEGRITY AG. Alle Rechte vorbehalten. / All rights reserved.

⚖️ License Compatibility / Lizenz-Kompatibilität

DE: Details zur Lizenzierung und zu den Nutzungsrechten finden Sie in der beigefügten LICENSE Datei.

EN: Details on licensing and usage rights can be found in the attached LICENSE file.

📧 Contact / Kontakt: [email protected] 🌐 Website: www.hypertegrity.de