Vectorizing Medical Procedure Codes (TUSS) using PGVector and Gemini - n8n Workflow

Healthcare IT professionals managing large regulatory databases (like TUSS).
Data engineers requiring robust ETL pipelines for vector database ingestion.
Developers building Retrieval-Augmented Generation (RAG) systems for medical queries.
Users seeking powerful, customizable n8n templates for advanced database operations.

Handling vast tables of regulatory medical codes, such as the TUSS (Terminologia Unificada da Saúde Suplementar), is essential for many healthcare systems. Traditional searching using exact keywords often fails to capture the true intent behind a user's query. This sophisticated n8n workflow solves this by creating a semantic search index. It automates the entire vectorization process, extracting codes and descriptions from an Oracle database, utilizing the Google Gemini embedding model to generate high-quality vectors, and storing these vectors reliably in a PostgreSQL database using the PGVector extension. This entire n8n workflow acts as a powerful data ingestion engine, providing the foundation for highly accurate and context-aware medical knowledge retrieval.

This efficient n8n workflow begins with the VECTORIZE TUSS TABLE n8n trigger, configured as a manual execution point.

1. Data Extraction: The flow immediately connects to the ORACLE DATABASE CONNECTION n8n node, which executes a custom query to fetch the CDITEM (code) and DSITEM (description) for all relevant medical procedures.


2. Data Structuring: The COLLECTION OF MEDICAL PROCEDURES Code n8n node processes the Oracle output, extracting the array of rows for iterative processing.


3. Batch Iteration: The FOR - MEDICAL PROCEDURES n8n node (Split In Batches) ensures that data is processed item by item, managing resource usage and ensuring robustness during the high-load embedding process.


4. Data Preparation (Loader): The Data Loader n8n node formats each procedure into a document, combining the code and description into a structured text string. Crucially, it attaches the original cd_item and procedureName as metadata, vital for lookup after semantic search.


5. Embedding Generation: The Embeddings Google Gemini n8n node takes the prepared text and generates a vector embedding. The n8n node uses the specified Gemini credentials to connect to the Google service.


6. Vector Storage: Finally, the Postgres PGVector Store2 n8n node performs the insertion. This LangChain-based n8n node receives both the document data and the vector embedding, seamlessly inserting them into the configured PostgreSQL table, completing this complex n8n workflow. The presence of the Token Splitter n8n node shows the capability to handle larger documents by chunking them before embedding, though the main flow is optimized for the current structure.

To deploy this powerful n8n workflow, follow these steps:

1. Import: Copy the provided JSON data and import it directly into your n8n instance as a new n8n template.


2. Oracle Credentials: Configure the ORACLE DATABASE CONNECTION n8n node with your specific connection details (credentials named 'PROTHEUS - PRODUÇÃO (APPN8N)' in the template).


3. Gemini Credentials: Configure the Embeddings Google Gemini n8n node using your Google Palm API key (credentials named 'GEMINI - PRODUÇÃO' in the template).


4. Postgres Credentials: Set up credentials for the Postgres PGVector Store2 n8n node, ensuring your connection points to a PostgreSQL instance with the PGVector extension enabled.


5. Configuration Check: Update the ORACLE DATABASE CONNECTION query parameter to reflect your actual TUSS table name ({sua tabela aqui }).


6. Table Name: Update the Postgres PGVector Store2 n8n node's table name parameter ({yourtablename_here}) to match your target vector table structure. This ensures the n8n template functions correctly.


7. Activate and Execute: Save the n8n workflow, set it to Active, and run the manual n8n trigger to start the vectorization process.

VECTORIZE TUSS TABLE (Manual Trigger): Serves as the starting point, initiating the entire data extraction and vectorization sequence of this n8n workflow.
ORACLE DATABASE CONNECTION (Oracle Database with Parameterization):
Function: Connects to the Oracle database to extract raw TUSS procedure codes and descriptions.
Key Configuration: Query configured to SELECT CDITEM, DSITEM FROM {sua tabela aqui }.
COLLECTION OF MEDICAL PROCEDURES (Code n8n node):
Function: Extracts the result set (rows) from the Oracle query output, preparing the list for iteration.
FOR - MEDICAL PROCEDURES (Split In Batches n8n node):
Function: Iterates over the list of procedures, ensuring each item is processed individually.
Data Loader (Document Default Data Loader n8n node):
Function: Formats the procedure data into a LangChain document structure.
Key Configuration: Text data includes both code and description, with metadata fields cditem and procedureName dynamically set using expressions.
Embeddings Google Gemini (Embeddings Google Gemini n8n node):
Function: Interacts with the Google Gemini API to transform the textual procedure descriptions into dense vector representations.
Postgres PGVector Store2 (PGVector Store n8n node):
Function: The final action in this n8n workflow, responsible for inserting the generated vectors and their corresponding documents into the PGVector table for semantic search.
Key Configuration: Uses the insert mode and targets a user-defined table ({yourtablenamehere}).