[BUG] PostgresRecordManager Inserts NaN into updated_at on Azure PostgreSQL, Breaking Cleanup Modes

[Nek-11]

Describe the bug When using the Postgres Record Manager node with an Azure PostgreSQL Flexible Server database, the updated_at column (double precision type) in the specified record manager table (e.g., upsertion_records) gets populated with NaN instead of a valid epoch timestamp number. This prevents the Incremental and Full cleanup modes from working correctly because the timestamps cannot be compared to determine stale records. As a result, all vectors in the target vector store (e.g., Pinecone) are deleted immediately after the upsert operation finishes, effectively preventing persistent storage when cleanup is enabled. Setting the Cleanup mode to None allows vectors to be added and persist, but the underlying NaN insertion into the updated_at column still occurs.

To Reproduce Steps to reproduce the behavior:

1. Configure Flowise to use Azure PostgreSQL Flexible Server as the main database (ensure pgcrypto and uuid-ossp extensions are enabled and created in the database).
2. Create a Document Store upsert process (e.g., using a simple file loader and Pinecone as the Vector Store).
3. Add the Postgres Record Manager node to the Document Store configuration.
4. Configure the Postgres Record Manager node:

• Point it to the same Azure PostgreSQL instance used by Flowise (correct host, port, database name 'production', user, password).
• Specify a table name (e.g., upsertion_records).
• Ensure SSL is enabled for the connection (using the POSTGRES_RECORDMANAGER_SSL=true environment variable, as there is no SSL toggle in the node UI).
• Set the Cleanup mode to Incremental or Full.
• Set the Source Id Key (e.g., source).

5. Run the upsert operation with a document that has the specified sourceIdKey in its metadata.
6. Observe the process: vectors are added to the target vector store (Pinecone) but are immediately deleted.
7. (Optional) Connect to the PostgreSQL database via psql and run SELECT key, updated_at FROM upsertion_records;. Observe that the updated_at column contains NaN.

Expected behavior The updated_at column in the record manager table (upsertion_records) should contain a valid numeric epoch timestamp (double precision). The Incremental or Full cleanup modes should correctly compare these timestamps against subsequent upsert runs and only delete vectors corresponding to stale or removed source documents, not all vectors on every run.

Screenshots Here's the NaNs in my upsertion_records database

Setup Installation: Docker deployed on Azure App Service (1 Main App + 5 Worker Apps, initially tested queue mode, later confirmed error persists in main mode on main app with workers stopped) Flowise Version: [2.2.7-patch.1] OS: Linux (Azure App Service Standard Plan) Browser: Chrome (irrelevant) Database: Azure PostgreSQL Flexible Server Vector Store: Pinecone (Irrelevant: the error occurs during the Record Manager's interaction with PostgreSQL, before/after Pinecone interaction)

Additional context Extensive troubleshooting confirmed the following:

• Database is correctly configured: Direct psql tests confirm:
• pgcrypto and uuid-ossp extensions are installed and functions (gen_random_uuid, uuid_generate_v4) are available and executable by the Flowise DB user (db_flowisechatbot_p).
• The record manager table (upsertion_records) exists with the correct schema (updated_at double precision, uuid UUID PRIMARY KEY DEFAULT gen_random_uuid()).
• Direct SQL command SELECT EXTRACT(EPOCH FROM CURRENT_TIMESTAMP) returns a valid number.
• Direct SQL INSERT and UPDATE statements setting updated_at = EXTRACT(EPOCH FROM CURRENT_TIMESTAMP) work correctly and store a valid number (NOT NaN).
• The Flowise DB user has SELECT, INSERT, UPDATE, DELETE permissions on the upsertion_records table.
• The DB user's search_path includes public.
• Environment Variables are correct: Confirmed via Azure CLI appsettings list and printenv inside the container that POSTGRES_RECORDMANAGER_SSL=true and other DB connection variables are correctly set for the Flowise apps.

Likely Code Bug Identified:

The issue appears to stem from the getTime() function within packages/server/src/nodes/recordmanagers/PostgresRecordManager/PostgresRecordManager.ts. The code executes: const res = await queryRunner.manager.query('SELECT EXTRACT(EPOCH FROM CURRENT_TIMESTAMP)'); It then attempts to parse the result using: return Number.parseFloat(res[0].extract); However, the standard PostgreSQL output column name for EXTRACT(...) is date_part, not extract. See screenshot:

Therefore, res[0].extract is likely undefined. Number.parseFloat(undefined) correctly returns NaN in JavaScript. This NaN value is then passed to the update() function and inserted into the updated_at column, causing the downstream cleanup logic to fail.

Suggested (simple) Fix: Modify the getTime() function in PostgresRecordManager.ts to correctly access the result column, for example:

• Change return Number.parseFloat(res[0].extract); to return Number.parseFloat(res[0].date_part);
• OR alias the column in the SQL query: query('SELECT EXTRACT(EPOCH FROM CURRENT_TIMESTAMP) AS current_epoch') and use res[0].current_epoch.

Current Workaround: Setting the Cleanup mode to None prevents data loss but does not fix the underlying NaN insertion.