Sync PostgreSQL data to a Pinecone vector knowledge base using Gemini embeddings

PostgreSQL Auto-Discovery to Pinecone Vector Knowledge Base Turn existing PostgreSQL databases into AI-searchable Pinecone vector knowledge bases without manually defining every table and column. This template discovers your PostgreSQL schema, selects text-rich tables, builds readable embedding documents from rows, generates Google Gemini embeddings, upserts vectors into Pinecone, and tracks per-table sync state for incremental indexing. It also includes a companion semantic search webhook workflow that embeds a user query, queries Pinecone, and returns source row metadata for RAG and AI assistant use cases. Features - Dynamic PostgreSQL schema discovery using informationschema. - No hard-coded schema, table, or column names. - Primary key detection. - Timestamp column detection for incremental sync. - Text-rich column detection for embedding. - Default sensitive-column exclusion. - Configurable allowed schemas, allowed tables, excluded tables, excluded columns, and max rows per table. - Google Gemini embeddings with gemini-embedding-001. - Pinecone upsert using namespaces. - Per-table sync state stored in n8n workflow static data. - Fail-fast error behavior so PostgreSQL, Gemini, or Pinecone errors stop execution. - Companion semantic search webhook for testing RAG retrieval. Architecture The main workflow runs through these stages: 1. Manual or scheduled trigger. 2. Configuration. 3. PostgreSQL metadata discovery. 4. Table analysis and filtering. 5. Dynamic row fetching for selected tables. 6. Row-to-document transformation. 7. Gemini embedding batch preparation. 8. Gemini embedding generation. 9. Pinecone vector batch preparation. 10. Pinecone upsert. 11. Sync state update and summary output. How Auto-Discovery Works The workflow inspects PostgreSQL metadata, groups columns by table, and chooses indexable tables based on: - Allowed and excluded schema/table settings. - Presence of a primary key, if required. - Presence of text or JSON columns. - Presence of timestamp columns, unless full-sync fallback is enabled. - Sensitive-column filtering. Security Filtering The workflow excludes sensitive columns before documents are built. By default, it filters column names containing: - password - passwd - pwd - token - secret - apikey - apikey - accesskey - refreshtoken - resettoken - otp - pin - session - cookie - hash - salt - privatekey - clientsecret You can add more column names in the excludedColumns setting. Important: review your database schema and compliance requirements before enabling indexing in production. This workflow provides safe defaults, but it cannot understand every business-specific sensitive field. Supported PostgreSQL Column Types The workflow treats these as text-rich embedding candidates: - text - varchar - character varying - char - character - json - jsonb When includeScalarContextColumns is enabled, the document also includes useful scalar context from: - integers and numeric types - booleans - dates - timestamps - UUIDs PostgreSQL Setup Create or select a PostgreSQL credential in n8n with read-only access. Recommended permissions: sql GRANT USAGE ON SCHEMA public TO yourn8nuser; GRANT SELECT ON ALL TABLES IN SCHEMA public TO yourn8nuser; ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO yourn8nuser; For production, prefer a dedicated read-only user and restrict schemas with the workflow allowedSchemas setting. Pinecone Setup 1. Create a Pinecone index. 2. Use the same vector dimension as the embedding model. 3. This workflow requests 1536 dimensions from Gemini so it can use a Pinecone index configured for 1536-dimensional vectors. 4. Copy the index host, for example your-index-abc123.svc.us-east-1-aws.pinecone.io. 5. Create an n8n HTTP Header Auth credential: - Header name: Api-Key - Header value: your Pinecone API key. 6. Set pineconeIndexHost in the workflow configuration. The workflow uses Pinecone namespaces in this format: text postgres-{schema}.{table} Example: text postgres-pu