[BUG] RecordManager Does Not Properly Cleanup Vector Store's OLD VALUES

[dkindlund]

Describe the bug
In Flowise, I've created a new Document Store where an Airtable Document Loader feeds data into a Postgres Vector Store. We're also using Postgres to store the Record Manager records. Whenever I update an existing record in Airtable and then proceed to perform the Upsert operation, I see the new vector store record created but the OLD vector store record still EXISTS. The OLD vector store record does not get properly deleted!

To Reproduce
Steps to reproduce the behavior:

1. Create a new Document Store
2. Specify an Airtable Document Loader
3. Specify a Postgres Vector Store and Record Manager (with Cleanup = FULL)
4. Perform initial Upsert
5. Change a single existing Airtable record
6. Perform second Upsert
7. Verify that BOTH the NEW and the OLD vector store records STILL exist in the vector store

Expected behavior
I would expect that Upserting would purge the OLD vector store record.

Screenshots
If applicable, add screenshots to help explain your problem.

Flow
If applicable, add exported flow in order to help replicating the problem.

Setup

• Installation: docker
• Flowise Version: 2.1.2
• OS: Google Cloud Run Linux
• Browser: chrome

Additional context
So my vector store table is named mechanisms_vec_v1 and my record manager table is named mechanisms_rm_v1. I THINK the way the deletion logic is supposed to work is a match on where mechanisms_vec_v1.id = mechanisms_rm_v1.key. HOWEVER, in my case, there is NO OVERLAP IN VALUES. For some reason, when a vector store document is inserted, that record's mechanisms_vec_v1.id IS NEVER THE SAME VALUE as it's corresponding mechanisms_rm_v1.key value.

Because of this, I think there's some sort of BUG between when the Airtable data gets loaded to when it gets added to the vector store -- specifically, I think the mechanisms_vec_v1.id computed value might NOT be getting set the same way as the mechanisms_rm_v1.key value.

It's exceptionally hard to figure out exactly where the bug is located, because of all the layers of indirection and use of langchain built-in libraries, but I hope these clues help, @HenryHengZJ . Thanks in advance!

[dkindlund]

Here's what my RecordManager looks like:

[HenryHengZJ]

can you help me confirm 2 things:

• does it happen only for airtable?
• does it happen only for FULL, what bout incremental?

[dkindlund]

Hey @HenryHengZJ , let me upgrade to the latest version to confirm if this bug still exists. Will get you another update with your answers after that. Thanks!

[BenMushen]

Hey @dkindlund , having the exact same issue. Did you manage to get sorted or figure a way around it?

Have also updated to the latest update and still having issues.

[iristhiawl95]

Hi @dkindlund and @BenMushen,
I'm also having the same issue using Postgres Vector Store, Postgres Record Manager and File Loader.
I tested using Pinecone Vector Store and Postgres Record Manager, it works fine.

Did you manage to figure a way around using Postgres vector store?

[dkindlund]

can you help me confirm 2 things:

Hey @HenryHengZJ apologies for the delay on this.

• does it happen only for airtable?

I don't think that's the case -- as others have commented this problem exists with other data sources (non-Airtable)

• does it happen only for FULL, what bout incremental?

It doesn't matter if it's FULL or INCREMENTAL -- the same problem exists from what I see. The stale vec records still exist.

I think it's because there's no match between the vec.id and rm.key values -- but I'm not an expert here.

[dkindlund]

Looking at the Postgres table schemas more closely:

flowise-data=> \d mechanisms_vec_v1
                 Table "public.mechanisms_vec_v1"
   Column    |  Type  | Collation | Nullable |      Default
-------------+--------+-----------+----------+--------------------
 id          | uuid   |           | not null | uuid_generate_v4()
 pageContent | text   |           |          |
 metadata    | jsonb  |           |          |
 embedding   | vector |           |          |
Indexes:
    "mechanisms_vec_v1_pkey" PRIMARY KEY, btree (id)

flowise-data=> \d mechanisms_rm_v1
                     Table "public.mechanisms_rm_v1"
   Column   |       Type       | Collation | Nullable |      Default
------------+------------------+-----------+----------+-------------------
 uuid       | uuid             |           | not null | gen_random_uuid()
 key        | text             |           | not null |
 namespace  | text             |           | not null |
 updated_at | double precision |           | not null |
 group_id   | text             |           |          |
Indexes:
    "mechanisms_rm_v1_pkey" PRIMARY KEY, btree (uuid)
    "mechanisms_rm_v1_key_namespace_key" UNIQUE CONSTRAINT, btree (key, namespace)

I'm thinking there has to be some sort of linkage between the vec.id field and the rm.key field, but the data types aren't exactly the same....

[dkindlund]

Okay, after reviewing the code more deeply, I think I have some theories as to what is happening here...

The code is split between the indexing subsystem and the PostgreSQL RecordManager implementation:

Flowise/packages/components/src/indexing.ts

Line 310 in c0a7478

await recordManager.update(

Flowise/packages/components/nodes/recordmanager/PostgresRecordManager/PostgresRecordManager.ts

Line 278 in c0a7478

async update(keys: string[], updateOptions?: UpdateOptions): Promise<void> {

Here's my raw notes so far:

When you see that a document’s uid (used as the record manager’s key) doesn’t match the vector store’s id, it indicates that somewhere in the insertion workflow the intended identifier isn’t “flowing through” as expected. Here are some theories and scenarios that might explain this discrepancy:

1. Vector Store Ignores Supplied IDs

• What Happens:
  The indexing code calls vectorStore.addDocuments(docsToIndex, { ids: uids }) expecting that the provided uids (generated from document hashing) will be used as the document’s id.

• Possible Issue:
  Some vector store implementations—especially those built on PostgreSQL—have the id column defined with a default like uuid_generate_v4(). This means that even if you pass in custom ids, the database might ignore them and generate a new UUID automatically.

• Consequence:
  The record manager’s key (set to the external uid) will differ from the vector store’s auto-generated id.

2. Schema Definition and Default Values

• What Happens:
  The vector store table is often defined with something like: id UUID NOT NULL DEFAULT uuid_generate_v4()

• Possible Issue:
  If the insertion process doesn’t override this default (or if the vector store’s insertion logic doesn’t support custom ids properly), the database will always use uuid_generate_v4() to populate the id field.

• Consequence:
  Even if your code passes in a uid, the stored id ends up being a newly generated UUID, leading to a mismatch with the record manager’s key.

3. Bug or Misimplementation in the Vector Store Insertion Method

• What Happens:
  The indexing code expects that the ids parameter provided to vectorStore.addDocuments will be used for the inserted document records.

• Possible Issue:
  A bug in the implementation or a misinterpretation of the API could cause the supplied ids to be ignored. The vector store might be defaulting to its own id generation mechanism instead of using the provided uid.

• Consequence:
  The record manager gets the externally computed uid (which becomes the rm.key), but the vector store’s document record gets an auto-generated id.

4. Post-Insertion Transformations or Race Conditions

• What Happens:
  In some systems, additional processing steps might be applied to the document after insertion.

• Possible Issue:
  Although less common, if there is a transformation or a re-hashing step applied to the document before it is fully stored, the final identifier might be altered. Similarly, if multiple processes are writing concurrently, a race condition might cause an update to overwrite the intended id.

• Consequence:
  This could lead to an inconsistency between the vector store id and the record manager key, although this is typically less likely than the issues with defaults and API behavior.

Summary

The most likely theories are:

• Vector Store Implementation: It’s auto-generating ids (via a default function) and ignoring your provided uids.
• Schema Defaults: The vector store table’s schema forces the id to be generated automatically, leading to a mismatch.
• API Bug/Misconfiguration: The parameter for custom ids isn’t being honored in the vector store insertion call.

In any of these cases, the intended one-to-one mapping (i.e. vec.id should equal rm.key) breaks down, which in turn disrupts cleanup logic that relies on matching these identifiers. The remedy usually involves either configuring the vector store to accept externally supplied ids or adjusting the workflow to update the record manager after retrieving the actual inserted id from the vector store.

[dkindlund]

After further analysis, I think there's some sort of subtle bug specific to the vectorStore.addDocuments() function:

Flowise/packages/components/src/indexing.ts

Line 300 in c0a7478

await vectorStore.addDocuments(docsToIndex, { ids: uids })

The vec.id field is intended to be set by passing the computed document uid (from _HashedDocument.fromDocument) as the ids parameter in the call to vectorStore.addDocuments(). The underlying vector store driver should then insert these ids into the id column of the vec table. However, if the driver fails to override the table’s default auto-generation of the id, PostgreSQL will generate a new id instead—causing the mismatch between vec.id and rm.key.

Still investigating...

[dkindlund]

Okay, so because we're using the typeorm driver, the underlying code that powers the vectorStore.addDocuments call can be found here:

https://github.com/langchain-ai/langchainjs/blob/d303e909d6b73da0bda4c0b7065ff974a980e5b0/libs/langchain-community/src/vectorstores/typeorm.ts#L114-L149

In this implementation, the vector store’s table (the vec table) is defined so that its id column is automatically generated. Here’s how it works step-by-step:

1. Entity Schema Definition:When the TypeORMVectorStore is constructed, it creates an entity schema for documents.

Notice the column definition for id:

const TypeORMDocumentEntity = new EntitySchema<TypeORMVectorStoreDocument>({
  name: fields.tableName ?? defaultDocumentTableName,
  columns: {
    id: {
      generated: "uuid",
      type: "uuid",
      primary: true,
    },
    pageContent: { type: String },
    metadata: { type: "jsonb" },
    embedding: { type: String },
  },
});

Here, the id field is marked with generated: "uuid", which tells TypeORM that this field should be auto-generated as a UUID. It’s not expecting any value to be provided for id.

2. Database Table Creation:

In the ensureTableInDatabase method, the table is created with a SQL statement that includes:

CREATE TABLE IF NOT EXISTS ${this.tableName} (
  "id" uuid NOT NULL DEFAULT uuid_generate_v4() PRIMARY KEY,
  "pageContent" text,
  metadata jsonb,
  embedding vector
);

This means that if an insert does not explicitly provide a value for id, PostgreSQL will automatically generate one using uuid_generate_v4().

3. Document Insertion Flow:

When documents are added, the following happens:

• Embedding Calculation:
  The documents’ page content is embedded, and the resulting vectors are prepared.

• Mapping to Rows:
  In the addVectors method, each document is converted to a row object:

const documentRow = {
  pageContent: documents[idx].pageContent,
  embedding: embeddingString,
  metadata: documents[idx].metadata,
};

WARNING: Notice that no id is provided in this object.

• Saving Rows:
  The code then calls:

await documentRepository.save(chunk);

THEREFORE: Because the row object doesn’t include an id, PostgreSQL uses the default value (uuid_generate_v4()) to generate a new UUID for the id field.

In summary:
The vec.id field is set by the database automatically. The insertion code does not provide an explicit id when calling documentRepository.save(chunk), so the default behavior in the table definition takes over—using uuid_generate_v4() to generate a new UUID. This means that even if the indexing process computes a UID for each document (to be used in the Record Manager), that UID is not passed along to the vector store insertion. Instead, the vector store ends up with auto-generated ids that do not necessarily match the externally computed UIDs.

This is the crux of the problem, I believe.

[dkindlund]

Pinpointing the exact location of the code where this problem exists:

Flowise/packages/components/src/indexing.ts

Line 300 in c0a7478

await vectorStore.addDocuments(docsToIndex, { ids: uids })

See that second parameter that is getting passed into vectorStore.addDocuments:

await vectorStore.addDocuments(docsToIndex, { ids: uids })
                                            ^^^^^^^^^^^^^

If we look at the typeorm implementation of addDocuments, we see this from:
https://github.com/langchain-ai/langchainjs/blob/d303e909d6b73da0bda4c0b7065ff974a980e5b0/libs/langchain-community/src/vectorstores/typeorm.ts#L121

async addDocuments(documents: Document[]): Promise<void> {

Notice how the typeorm implementation DOES NOT ACCEPT the second argument of { ids: uids }.

Therefore, the supplied ids by the Flowise code are getting SILENTLY DROPPED and IGNORED upon insert into the vector store.

JavaScript functions are inherently variadic, which means that if you pass more arguments than the function declares, the extra arguments are simply ignored rather than causing an error. In this case, the TypeORM implementation of addDocuments only declares a single parameter for the documents array, so when Flowise passes the second argument ({ ids: uids }), it’s silently dropped because the function doesn’t reference it. This behavior is standard in JavaScript and isn’t considered an error unless the function explicitly checks for unexpected parameters.

@HenryHengZJ , I'm not sure how to fix this issue but at least I think I've pinpointed the exact problem. Does this help?

[dkindlund]

Further note -- this appears to be a bug specific to the typeorm implementation. Let me explain:

https://github.com/langchain-ai/langchainjs/blob/d303e909d6b73da0bda4c0b7065ff974a980e5b0/libs/langchain-community/src/vectorstores/pgvector.ts#L350

This PGVectorStore implementation does not suffer the same problem. Here’s why:

1. Explicit ids Parameter Handling:

The addDocuments method in PGVectorStore is defined to accept an optional second argument:

async addDocuments(
  documents: Document[],
  options?: { ids?: string[] }
): Promise<void> { ... }

It then passes these ids down to addVectors.

2. Rows Construction in addVectors:

In the addVectors method, if the ids option is provided, it appends the corresponding id (from the ids array) to each row:

if (ids) {
  values.push(ids[i]);
}

So each row will contain the document’s content, embedding, metadata, (optionally a collection id), and finally the provided id.

3. Dynamic Query Construction in buildInsertQuery:
   The buildInsertQuery method checks whether the rows have an extra element (the id) and, if so, adds the id column to the list of columns being inserted:

if (rows.length !== 0 && columns.length === rows[0].length - 1) {
  columns.push(this.idColumnName);
}

This dynamically includes the id column (e.g. "id") in the INSERT query.

4. Outcome:
   Because the provided ids are explicitly appended to the row values and the insert query is built to include the id column when they are present, the PGVectorStore implementation honors the supplied ids. This means that instead of relying on the database default (which would auto-generate a new uuid), the document’s id in the vector store (vec.id) is set to the value provided in the ids array.

Conclusion:
Unlike the TypeORM driver where the extra argument { ids: uids } is silently ignored, the PGVectorStore implementation is designed to accept and use custom ids. Therefore, if you supply ids via the second argument, those values will be inserted into the id field of the table, avoiding the mismatch problem.

[dkindlund]

A decent workaround here might be to try to use the PGVector driver instead of the TypeORM driver -- however, I see that the PGVector driver was recently disabled for some reason... I'm not sure why...

[dkindlund]

Okay, from this commit, I think I see why it was disabled:
a2a475b#diff-4a1a4686f399cfa5448e01ad9b44f53e26ff79e5438d8748ae77771f25ac4c7eR2

This comment explains why in v2.2.5:

Temporary disabled due to increasing open connections without releasing them
Use TypeORM instead

So while the workaround to use PGVector might work -- it's unfortunately disabled in v2.2.5 because of some other presumably PGVector-specific bug...