Improve AI support email drafts with Gmail, OpenAI and PostgreSQL

Self-learning feedback loop for AI customer support email drafts with Gmail, OpenAI and PostgreSQL Automatically compare AI-generated email drafts against what your support team actually sent, learn from the differences, and improve future drafts over time — without any model fine-tuning. --- What this workflow does This is the second workflow in a two-part customer support automation system. The first workflow generates AI draft replies for incoming support emails. This workflow closes the loop — it runs every 3 hours, checks which drafts were reviewed and sent, compares them against the original AI output, and stores the human-edited versions as training examples. The more this workflow runs, the smarter the first workflow becomes. When generating future drafts, the similarity search surfaces past human-approved responses — so the AI progressively learns what good answers look like for your specific support context. --- How it works Step 1 — Watermark and scheduling Every run starts by fetching the lastprocessedsentat timestamp from the previous completed run. Only Gmail Sent emails newer than this timestamp are fetched, so nothing gets processed twice. On the first-ever run it defaults to 7 days ago. Step 2 — Fetch and loop Sent emails are fetched from Gmail and processed one at a time. For each email, the full message body is retrieved via the Gmail API (the list endpoint only returns a preview snippet). The sent email's thread ID is matched against the aidrafts table to find the corresponding AI draft. Step 3 — Match and skip logic Three things skip an email without processing: no matching AI draft found (the team sent something manually), the draft was already processed in a previous run, or the fetch returns no results. Only genuine unprocessed matches continue. Step 4 — AI comparison GPT-4o-mini compares the AI draft text against the human-sent text and returns a structured analysis: whether it was approved unchanged, the type of edit made (minor edits vs major rewrite), a plain English summary of what changed, and whether the edit implies missing or incorrect information in the knowledge base. Step 5 — Store the correction If the human made any edits, the pair (original email + human response) is embedded using OpenAI text-embedding-3-small and saved to the corrections table. This table is what the first workflow searches using vector cosine similarity when assembling future draft prompts. Step 6 — KB auto-update If the AI comparison flags that the human edit contained new information, the most relevant knowledge base entry for that category is fetched and rewritten by GPT-4o-mini to incorporate the new information. The previous answer is preserved in the previousanswer column for auditing. Step 7 — Run log Each run is logged to feedbackrunlog with counts of emails checked, corrections saved, KB updates made and any errors. This log also serves as the watermark source for the next run. --- Setup steps Prerequisites - Gmail account (same support inbox used by the main email workflow) - OpenAI API key - PostgreSQL database with pgvector extension and the full schema from Workflow 1 already applied - The main email automation workflow (Workflow 1) must be active and generating drafts 1. Apply the DB migration Run the following against your existing database to add the columns this workflow needs: sql ALTER TABLE aidrafts ADD COLUMN IF NOT EXISTS emailembedding vector(1536), ADD COLUMN IF NOT EXISTS feedbackprocessedat TIMESTAMPTZ, ADD COLUMN IF NOT EXISTS wasapprovedasis BOOLEAN DEFAULT FALSE; ALTER TABLE corrections ADD COLUMN IF NOT EXISTS source TEXT DEFAULT 'feedbackloop', ADD COLUMN IF NOT EXISTS kbupdated BOOLEAN DEFAULT FALSE; ALTER TABLE kbdata ADD COLUMN IF NOT EXISTS updatedby TEXT DEFAULT 'manual', ADD COLUMN IF NOT EXISTS previousanswer TEXT; CREATE TABLE IF NOT EXISTS feedbackrunlog ( id SERIAL PRIMARY KEY, runstartedat TIMESTAMPTZ DEFAULT NOW(), runcompletedat TIMESTAMPTZ, lastprocessedsentat TIMESTAMPTZ, e