This workflow follows the HTTP Request → Postgres recipe pattern — see all workflows that pair these two integrations.
The workflow JSON
Copy or download the full n8n JSON below. Paste it into a new n8n workflow, add your credentials, activate. Full import guide →
{
"name": "assistant",
"nodes": [
{
"parameters": {
"httpMethod": "POST",
"path": "assistant",
"responseMode": "responseNode",
"options": {}
},
"id": "webhook-assistant",
"name": "Assistant Webhook",
"type": "n8n-nodes-base.webhook",
"typeVersion": 2.1,
"position": [
260,
300
]
},
{
"parameters": {
"jsCode": "const workflowName = 'assistant';\nconst payload = ($json.body && typeof $json.body === 'object') ? $json.body : $json;\nconst rawQuery = typeof payload.message === 'string' ? payload.message : payload.query;\nconst rawProjectSlug = payload.project_slug;\nconst rawSessionId = payload.session_id;\nconst rawTopK = payload.top_k;\nconst runId = typeof payload.run_id === 'string' && payload.run_id.trim() !== ''\n ? payload.run_id.trim()\n : 'cb-assistant-' + Date.now() + '-' + Math.random().toString(36).slice(2, 8);\nconst correlationId = typeof payload.correlation_id === 'string' && payload.correlation_id.trim() !== ''\n ? payload.correlation_id.trim()\n : runId;\nconst slugPattern = /^[a-z0-9]+(?:[-_][a-z0-9]+)*$/;\nconst baseTrace = {\n run_id: runId,\n correlation_id: correlationId,\n workflow_name: workflowName,\n project_slug: null,\n source_type: 'assistant_request',\n filename: null,\n filepath: null,\n stage: 'received',\n status: 'received',\n error_code: null,\n error_message: null,\n timestamp: new Date().toISOString(),\n workflow_chain: [workflowName],\n stage_history: [],\n};\nconst withStage = (trace, stage, status, extra = {}) => {\n const timestamp = new Date().toISOString();\n const errorCode = Object.prototype.hasOwnProperty.call(extra, 'error_code') ? extra.error_code : (trace.error_code ?? null);\n const errorMessage = Object.prototype.hasOwnProperty.call(extra, 'error_message') ? extra.error_message : (trace.error_message ?? null);\n return {\n ...trace,\n ...extra,\n stage,\n status,\n error_code: errorCode,\n error_message: errorMessage,\n timestamp,\n stage_history: [\n ...(Array.isArray(trace.stage_history) ? trace.stage_history : []),\n {\n stage,\n status,\n timestamp,\n error_code: errorCode,\n error_message: errorMessage,\n },\n ],\n };\n};\nconst buildError = (code, message) => [{\n json: {\n ok: false,\n error: {\n code,\n message,\n classification: 'validation',\n retryable: false,\n },\n query: null,\n session_id: null,\n project_slug: null,\n top_k: null,\n retrieval: {\n strategy: 'project-first-fallback-general',\n project_match_count: 0,\n general_match_count: 0,\n memory_count: 0,\n strongest_similarity: null,\n similarity_threshold: 0.72,\n empty: true,\n },\n sources: [],\n context_preview: '',\n session: {\n turn_count_before: 0,\n history_used: false,\n stored: false,\n },\n trace: withStage(baseTrace, 'validation', 'rejected', {\n error_code: code,\n error_message: message,\n }),\n },\n}];\nif (typeof rawQuery !== 'string' || rawQuery.trim() === '') {\n return buildError('INVALID_INPUT', 'Missing or invalid query/message');\n}\nif (rawProjectSlug !== undefined && rawProjectSlug !== null && typeof rawProjectSlug !== 'string') {\n return buildError('INVALID_PROJECT_SLUG', 'project_slug must be a string when provided');\n}\nif (rawSessionId !== undefined && rawSessionId !== null && typeof rawSessionId !== 'string') {\n return buildError('INVALID_SESSION_ID', 'session_id must be a string when provided');\n}\nif (rawTopK !== undefined && rawTopK !== null && (!Number.isInteger(rawTopK) || rawTopK < 1 || rawTopK > 8)) {\n return buildError('INVALID_TOP_K', 'top_k must be an integer between 1 and 8');\n}\nconst query = rawQuery.trim();\nconst projectSlug = typeof rawProjectSlug === 'string' ? rawProjectSlug.trim() : '';\nif (projectSlug && !slugPattern.test(projectSlug)) {\n return buildError('INVALID_PROJECT_SLUG', 'project_slug must use lowercase slug characters only');\n}\nconst sessionId = typeof rawSessionId === 'string' && rawSessionId.trim() !== ''\n ? rawSessionId.trim()\n : 'crispybrain-session-' + Date.now() + '-' + Math.random().toString(36).slice(2, 8);\nconst topK = Number.isInteger(rawTopK) ? rawTopK : 5;\nreturn [{\n json: {\n request_ok: true,\n query,\n session_id: sessionId,\n project_slug: projectSlug || null,\n top_k: topK,\n retrieval_strategy: 'project-first-fallback-general',\n requested_at: new Date().toISOString(),\n trace: withStage({\n ...baseTrace,\n project_slug: projectSlug || null,\n }, 'normalized', 'accepted', {\n project_slug: projectSlug || null,\n }),\n },\n}];"
},
"id": "code-normalize-assistant-request",
"name": "Normalize Assistant Request",
"type": "n8n-nodes-base.code",
"typeVersion": 2,
"position": [
560,
300
]
},
{
"parameters": {
"conditions": {
"options": {
"caseSensitive": true,
"leftValue": "",
"typeValidation": "strict",
"version": 2
},
"conditions": [
{
"id": "condition-request-ok",
"leftValue": "={{ $json.request_ok === true }}",
"rightValue": true,
"operator": {
"type": "boolean",
"operation": "true",
"singleValue": true
}
}
],
"combinator": "and"
},
"options": {}
},
"id": "if-request-is-valid",
"name": "Request Is Valid?",
"type": "n8n-nodes-base.if",
"typeVersion": 2.2,
"position": [
860,
300
]
},
{
"parameters": {
"operation": "executeQuery",
"query": "SELECT COALESCE(\n jsonb_agg(row_to_json(turns) ORDER BY turns.created_at),\n '[]'::jsonb\n) AS session_turns\nFROM (\n SELECT role, message_text, project_slug, created_at, metadata_json\n FROM (\n SELECT role, message_text, project_slug, created_at, metadata_json\n FROM openbrain_chat_turns\n WHERE session_id = $1::text\n ORDER BY created_at DESC\n LIMIT 6\n ) recent\n ORDER BY created_at ASC\n) turns;",
"options": {
"queryReplacement": "={{ [$json.session_id] }}"
}
},
"id": "postgres-load-session-turns",
"name": "Load Session Turns",
"type": "n8n-nodes-base.postgres",
"typeVersion": 2.6,
"position": [
1160,
160
],
"credentials": {
"postgres": {
"name": "<your credential>"
}
}
},
{
"parameters": {
"method": "POST",
"url": "http://host.docker.internal:11434/api/embed",
"sendBody": true,
"specifyBody": "json",
"jsonBody": "={{ JSON.stringify({ model: 'nomic-embed-text', input: $('Normalize Assistant Request').first().json.query, truncate: true }) }}",
"options": {
"response": {
"response": {
"fullResponse": true,
"neverError": true,
"responseFormat": "json"
}
},
"timeout": 120000
}
},
"id": "http-generate-query-embedding",
"name": "Generate Query Embedding",
"type": "n8n-nodes-base.httpRequest",
"typeVersion": 4.4,
"position": [
1460,
160
],
"retryOnFail": true,
"maxTries": 3,
"waitBetweenTries": 2000
},
{
"parameters": {
"jsCode": "const request = $('Normalize Assistant Request').first().json;\nconst sessionTurns = $('Load Session Turns').first().json.session_turns ?? [];\nconst statusCode = $json.statusCode ?? 0;\nconst body = $json.body ?? {};\nconst embeddings = body.embeddings;\nconst stopTerms = new Set(['what', 'when', 'where', 'which', 'who', 'how', 'this', 'that', 'with', 'from', 'into', 'your', 'about', 'does', 'tell', 'note', 'for', 'before']);\nconst uniqueTerms = (values) => Array.from(new Set(values.filter(Boolean)));\nconst detectAnchorHeuristics = (query) => {\n const value = typeof query === 'string' ? query.trim() : '';\n const exactFilenameQueryMatch = value.match(/^Which note is named (.+?)\\?\\s*$/i);\n const quotedTitlePhrases = Array.from(value.matchAll(/\"([^\"\\n]{3,})\"/g))\n .map((match) => match[1].trim().toLowerCase())\n .filter(Boolean);\n const filenameLikeTokens = Array.from(value.matchAll(/\\b[a-z0-9][a-z0-9._-]*\\.[a-z0-9]{2,8}\\b/gi))\n .map((match) => match[0].toLowerCase());\n const slugLikeTokens = Array.from(value.matchAll(/\\b[a-z0-9]+(?:-[a-z0-9]+){2,}\\b/gi))\n .map((match) => match[0].toLowerCase())\n .filter((token) => token.length >= 12);\n const mixedAnchorTokens = Array.from(value.matchAll(/\\b[a-z0-9_-]{6,}\\b/gi))\n .map((match) => match[0].toLowerCase())\n .filter((token) => (/[a-z]/.test(token) && /\\d/.test(token)) || token.includes('_') || token.includes('-'));\n const allCapsCodeTokens = Array.from(value.matchAll(/\\b[A-Z]{2,}[A-Z0-9._/-]{1,}\\b/g))\n .map((match) => match[0].toLowerCase());\n const anchorTokens = uniqueTerms([\n ...filenameLikeTokens,\n ...slugLikeTokens,\n ...mixedAnchorTokens,\n ...allCapsCodeTokens,\n ]);\n return {\n ranking_mode: exactFilenameQueryMatch || quotedTitlePhrases.length > 0 || anchorTokens.length > 0 ? 'anchor' : 'semantic',\n requested_filename: exactFilenameQueryMatch ? exactFilenameQueryMatch[1].trim() : '',\n quoted_title_phrases: quotedTitlePhrases,\n anchor_tokens: anchorTokens,\n };\n};\nconst buildQueryTerms = (query, anchorDetails) => {\n const value = typeof query === 'string' ? query.trim() : '';\n const requestedFilename = typeof anchorDetails?.requested_filename === 'string' ? anchorDetails.requested_filename.trim().toLowerCase() : '';\n const identifierLikeTokens = Array.from(value.matchAll(/\\b[a-z0-9]+(?:[_:/.-][a-z0-9]+)+\\b/gi))\n .map((match) => match[0].toLowerCase());\n const protocolHintTokens = Array.from(value.matchAll(/\\b(?:protocol|anchor|code|id|identifier)\\s+([a-z0-9._/-]{2,})\\b/gi))\n .map((match) => match[1].toLowerCase());\n const generalTerms = Array.from(value.toLowerCase().matchAll(/\\b[a-z0-9][a-z0-9._/-]{2,}\\b/g))\n .map((match) => match[0])\n .filter((term) => !stopTerms.has(term));\n const queryTerms = uniqueTerms(generalTerms);\n const strongQueryTokens = uniqueTerms([\n requestedFilename,\n ...(Array.isArray(anchorDetails?.quoted_title_phrases) ? anchorDetails.quoted_title_phrases : []),\n ...(Array.isArray(anchorDetails?.anchor_tokens) ? anchorDetails.anchor_tokens : []),\n ...identifierLikeTokens,\n ...protocolHintTokens,\n ]).filter((term) => term.length >= 2);\n const lexicalQueryTerms = uniqueTerms([\n ...queryTerms,\n ...strongQueryTokens,\n ]).filter((term) => term.length >= 4 || /\\d/.test(term));\n return {\n query_terms: queryTerms,\n lexical_query_terms: lexicalQueryTerms.slice(0, 16),\n strong_query_tokens: strongQueryTokens.slice(0, 12),\n };\n};\nconst failureIntentTerms = new Set(['failure', 'failures', 'problem', 'problems', 'issue', 'issues', 'bug', 'bugs', 'breakdown', 'breakdowns', 'broke', 'mistake', 'mistakes', 'regression', 'regressions', 'incident', 'incidents']);\nconst failureIntentPhrases = ['went wrong', 'weak point', 'weak points'];\nconst detectFailureIntent = (query) => {\n const value = typeof query === 'string' ? query.trim().toLowerCase() : '';\n if (value === '') return false;\n const tokens = new Set(value.match(/[a-z0-9]+/g) ?? []);\n if ([...failureIntentTerms].some((term) => tokens.has(term))) return true;\n return failureIntentPhrases.some((phrase) => value.includes(phrase));\n};\nconst withStage = (trace, stage, status, extra = {}) => {\n const timestamp = new Date().toISOString();\n const errorCode = Object.prototype.hasOwnProperty.call(extra, 'error_code') ? extra.error_code : (trace.error_code ?? null);\n const errorMessage = Object.prototype.hasOwnProperty.call(extra, 'error_message') ? extra.error_message : (trace.error_message ?? null);\n return {\n ...trace,\n ...extra,\n stage,\n status,\n error_code: errorCode,\n error_message: errorMessage,\n timestamp,\n stage_history: [\n ...(Array.isArray(trace.stage_history) ? trace.stage_history : []),\n { stage, status, timestamp, error_code: errorCode, error_message: errorMessage },\n ],\n };\n};\nconst sessionTrace = withStage(request.trace, 'session_loaded', 'accepted', {\n project_slug: request.project_slug ?? null,\n});\nif (statusCode < 200 || statusCode >= 300 || !Array.isArray(embeddings) || !Array.isArray(embeddings[0])) {\n return [{\n json: {\n ...request,\n session_turns: Array.isArray(sessionTurns) ? sessionTurns : [],\n embedding_ok: false,\n error: {\n code: 'OLLAMA_EMBEDDING_FAILED',\n message: 'Ollama embedding request failed',\n status: statusCode || null,\n details: body.error ?? null,\n classification: 'transient',\n retryable: true,\n },\n trace: withStage(sessionTrace, 'embedding_failed', 'failed', {\n error_code: 'OLLAMA_EMBEDDING_FAILED',\n error_message: 'Ollama embedding request failed',\n }),\n },\n }];\n}\nconst vector = embeddings[0];\nconst anchorDetails = detectAnchorHeuristics(request.query);\nconst queryTermDetails = buildQueryTerms(request.query, anchorDetails);\nconst isFailureIntent = detectFailureIntent(request.query);\nconst uncertaintyLexiconPattern = /\\b(uncertain|uncertainty|not well documented|incomplete|not fully know|missing from the record|not documented)\\b/i;\nconst applyQueryLexicon = (details, query) => {\n const extraLexicalTerms = [];\n const extraStrongTokens = [];\n const value = typeof query === 'string' ? query : '';\n if (isFailureIntent) {\n extraLexicalTerms.push('failure', 'failures', 'problem', 'problems', 'issue', 'issues', 'bug', 'bugs', 'breakdown', 'breakdowns', 'weakness', 'weak point', 'weak points', 'mistake', 'mistakes', 'incident', 'incidents', 'regression', 'regressions', 'went wrong', 'broke');\n extraStrongTokens.push('failures', 'problems', 'issues', 'bugs', 'breakdowns', 'weakness', 'mistakes', 'incidents', 'regressions', 'broke');\n }\n if (uncertaintyLexiconPattern.test(value)) {\n extraLexicalTerms.push('uncertain', 'incomplete', 'not documented', 'not well documented', 'early development');\n extraStrongTokens.push('uncertain', 'incomplete', 'not documented');\n }\n return {\n query_terms: uniqueTerms(details.query_terms || []),\n lexical_query_terms: uniqueTerms([...(details.lexical_query_terms || []), ...extraLexicalTerms]).slice(0, 24),\n strong_query_tokens: uniqueTerms([...(details.strong_query_tokens || []), ...extraStrongTokens]).slice(0, 18),\n };\n};\nconst enrichedQueryTermDetails = applyQueryLexicon(queryTermDetails, request.query);\nconst candidateLimit = anchorDetails.ranking_mode === 'anchor'\n ? Math.min(Math.max(request.top_k * 14, request.top_k + 4), 80)\n : Math.min(Math.max(request.top_k * 6, request.top_k + 4), 48);\nconst lexicalCandidateLimit = Math.min(Math.max(request.top_k * 4, request.top_k + 3), 32);\nreturn [{\n json: {\n ...request,\n session_turns: Array.isArray(sessionTurns) ? sessionTurns : [],\n embedding_ok: true,\n anchor_details: anchorDetails,\n is_failure_intent: isFailureIntent,\n query_terms: enrichedQueryTermDetails.query_terms,\n lexical_query_terms: enrichedQueryTermDetails.lexical_query_terms,\n strong_query_tokens: enrichedQueryTermDetails.strong_query_tokens,\n candidate_limit: candidateLimit,\n lexical_candidate_limit: lexicalCandidateLimit,\n vector_literal: '[' + vector.join(',') + ']',\n trace: withStage(sessionTrace, 'embedding_ready', 'accepted'),\n },\n}];"
},
"id": "code-prepare-retrieval-input",
"name": "Prepare Retrieval Input",
"type": "n8n-nodes-base.code",
"typeVersion": 2,
"position": [
1760,
160
]
},
{
"parameters": {
"conditions": {
"options": {
"caseSensitive": true,
"leftValue": "",
"typeValidation": "strict",
"version": 2
},
"conditions": [
{
"id": "condition-embedding-ready",
"leftValue": "={{ $json.embedding_ok === true }}",
"rightValue": true,
"operator": {
"type": "boolean",
"operation": "true",
"singleValue": true
}
}
],
"combinator": "and"
},
"options": {}
},
"id": "if-embedding-ready",
"name": "Embedding Ready?",
"type": "n8n-nodes-base.if",
"typeVersion": 2.2,
"position": [
2060,
160
]
},
{
"parameters": {
"jsCode": "const withStage = (trace, stage, status, extra = {}) => {\n const timestamp = new Date().toISOString();\n const errorCode = Object.prototype.hasOwnProperty.call(extra, 'error_code') ? extra.error_code : (trace?.error_code ?? null);\n const errorMessage = Object.prototype.hasOwnProperty.call(extra, 'error_message') ? extra.error_message : (trace?.error_message ?? null);\n return {\n ...(trace || {}),\n ...extra,\n stage,\n status,\n error_code: errorCode,\n error_message: errorMessage,\n timestamp,\n stage_history: [\n ...((trace && Array.isArray(trace.stage_history)) ? trace.stage_history : []),\n { stage, status, timestamp, error_code: errorCode, error_message: errorMessage },\n ],\n };\n};\nconst grounding = {\n status: 'none',\n weak_grounding: true,\n note: 'Grounding is unavailable because the embedding step failed before retrieval.',\n reasons: ['embedding_failed'],\n supporting_source_count: 0,\n reviewed_source_count: 0,\n strongest_similarity: null,\n similarity_threshold: 0.72,\n ranking_mode: null,\n evidence_strength: 'none',\n overall_trust_band: 'low',\n primary_memory_ids: [],\n primary_chunk_indexes: [],\n};\nreturn [{\n json: {\n ok: false,\n error: $json.error,\n query: $json.query,\n session_id: $json.session_id,\n project_slug: $json.project_slug ?? null,\n top_k: $json.top_k,\n retrieval: {\n strategy: $json.retrieval_strategy,\n project_match_count: 0,\n general_match_count: 0,\n lexical_project_match_count: 0,\n lexical_general_match_count: 0,\n lexical_all_match_count: 0,\n memory_count: 0,\n strongest_similarity: null,\n similarity_threshold: 0.72,\n empty: true,\n },\n trust: {\n overall_band: 'low',\n evidence_strength: 'none',\n reviewed_source_count: 0,\n unreviewed_source_count: 0,\n scope_match_count: 0,\n high_trust_source_count: 0,\n medium_trust_source_count: 0,\n low_trust_source_count: 0,\n uncertainty_indicator: true,\n uncertainty_reasons: ['embedding_failed'],\n },\n grounding,\n sources: [],\n selected_sources: [],\n retrieved_candidates: [],\n answer_mode: 'insufficient',\n conflict_flag: false,\n conflict_severity: null,\n conflict_details: [],\n claim_support_counts: [],\n claim_support_counts_raw: [],\n claim_support_counts_deduped: [],\n claim_weighted_support: [],\n claim_independent_support: [],\n claim_independence_adjusted_support: [],\n dominant_claim_status: null,\n dominant_claim_basis: null,\n claim_confidence: null,\n conflict_summary_hint: null,\n most_supported_claim: null,\n most_recent_claim: null,\n source_quality_breakdown: [],\n source_independence_breakdown: [],\n evidence_clusters: [],\n entity_focus: null,\n filtered_candidate_count: 0,\n context_preview: '',\n session: {\n turn_count_before: Array.isArray($json.session_turns) ? $json.session_turns.length : 0,\n history_used: Array.isArray($json.session_turns) ? $json.session_turns.length > 0 : false,\n stored: false,\n },\n trace: withStage($json.trace, 'response_ready', 'failed', {\n error_code: $json.error?.code ?? 'OLLAMA_EMBEDDING_FAILED',\n error_message: $json.error?.message ?? 'Ollama embedding request failed',\n grounding_status: grounding.status,\n weak_grounding: grounding.weak_grounding,\n answer_mode: 'insufficient',\n conflict_flag: false,\n }),\n },\n}];"
},
"id": "code-build-embedding-failure-response",
"name": "Build Embedding Failure Response",
"type": "n8n-nodes-base.code",
"typeVersion": 2,
"position": [
2360,
460
]
},
{
"parameters": {
"operation": "executeQuery",
"query": "WITH lexical_terms AS (\n SELECT DISTINCT lower(value) AS term\n FROM jsonb_array_elements_text(COALESCE($4::jsonb, '[]'::jsonb)) AS value\n WHERE btrim(value) <> ''\n),\nstrong_terms AS (\n SELECT DISTINCT lower(value) AS term\n FROM jsonb_array_elements_text(COALESCE($5::jsonb, '[]'::jsonb)) AS value\n WHERE btrim(value) <> ''\n)\nSELECT\n COALESCE((\n SELECT jsonb_agg(row_to_json(project_rows) ORDER BY project_rows.review_priority, project_rows.distance, project_rows.created_at DESC NULLS LAST, project_rows.id DESC)\n FROM (\n SELECT id, created_at, title, source, category, content, metadata_json,\n COALESCE(NULLIF(metadata_json->>'project_slug', ''), NULL) AS project_slug,\n COALESCE(NULLIF(metadata_json->>'review_status', ''), 'unreviewed') AS review_status,\n CASE COALESCE(NULLIF(metadata_json->>'review_status', ''), 'unreviewed')\n WHEN 'reviewed' THEN 0\n WHEN 'unreviewed' THEN 1\n WHEN 'suspect' THEN 2\n ELSE 3\n END AS review_priority,\n ROUND((1 - (embedding <=> $1::vector))::numeric, 6) AS similarity,\n (embedding <=> $1::vector) AS distance\n FROM memories\n WHERE embedding IS NOT NULL\n AND COALESCE(NULLIF(metadata_json->>'review_status', ''), 'unreviewed') <> 'suppressed'\n AND $2::text IS NOT NULL\n AND NULLIF(COALESCE(metadata_json->>'project_slug', ''), '') = $2::text\n ORDER BY review_priority ASC, distance ASC, created_at DESC NULLS LAST, id DESC\n LIMIT $3::int\n ) project_rows\n ), '[]'::jsonb) AS project_memories,\n COALESCE((\n SELECT jsonb_agg(row_to_json(general_rows) ORDER BY general_rows.review_priority, general_rows.distance, general_rows.created_at DESC NULLS LAST, general_rows.id DESC)\n FROM (\n SELECT id, created_at, title, source, category, content, metadata_json,\n COALESCE(NULLIF(metadata_json->>'project_slug', ''), NULL) AS project_slug,\n COALESCE(NULLIF(metadata_json->>'review_status', ''), 'unreviewed') AS review_status,\n CASE COALESCE(NULLIF(metadata_json->>'review_status', ''), 'unreviewed')\n WHEN 'reviewed' THEN 0\n WHEN 'unreviewed' THEN 1\n WHEN 'suspect' THEN 2\n ELSE 3\n END AS review_priority,\n ROUND((1 - (embedding <=> $1::vector))::numeric, 6) AS similarity,\n (embedding <=> $1::vector) AS distance\n FROM memories\n WHERE embedding IS NOT NULL\n AND COALESCE(NULLIF(metadata_json->>'review_status', ''), 'unreviewed') <> 'suppressed'\n AND $2::text IS NULL\n AND COALESCE(NULLIF(metadata_json->>'project_slug', ''), '') = ''\n ORDER BY review_priority ASC, distance ASC, created_at DESC NULLS LAST, id DESC\n LIMIT $3::int\n ) general_rows\n ), '[]'::jsonb) AS general_memories,\n COALESCE((\n SELECT jsonb_agg(row_to_json(all_rows) ORDER BY all_rows.review_priority, all_rows.distance, all_rows.created_at DESC NULLS LAST, all_rows.id DESC)\n FROM (\n SELECT id, created_at, title, source, category, content, metadata_json,\n COALESCE(NULLIF(metadata_json->>'project_slug', ''), NULL) AS project_slug,\n COALESCE(NULLIF(metadata_json->>'review_status', ''), 'unreviewed') AS review_status,\n CASE COALESCE(NULLIF(metadata_json->>'review_status', ''), 'unreviewed')\n WHEN 'reviewed' THEN 0\n WHEN 'unreviewed' THEN 1\n WHEN 'suspect' THEN 2\n ELSE 3\n END AS review_priority,\n ROUND((1 - (embedding <=> $1::vector))::numeric, 6) AS similarity,\n (embedding <=> $1::vector) AS distance\n FROM memories\n WHERE embedding IS NOT NULL\n AND COALESCE(NULLIF(metadata_json->>'review_status', ''), 'unreviewed') <> 'suppressed'\n AND $2::text IS NULL\n ORDER BY review_priority ASC, distance ASC, created_at DESC NULLS LAST, id DESC\n LIMIT $3::int\n ) all_rows\n ), '[]'::jsonb) AS all_memories,\n COALESCE((\n SELECT jsonb_agg(row_to_json(project_lex_rows) ORDER BY project_lex_rows.strong_token_hits DESC, project_lex_rows.title_lexical_hits DESC, project_lex_rows.lexical_overlap DESC, project_lex_rows.review_priority ASC, project_lex_rows.created_at DESC NULLS LAST, project_lex_rows.id DESC)\n FROM (\n SELECT *\n FROM (\n SELECT id, created_at, title, source, category, content, metadata_json,\n COALESCE(NULLIF(metadata_json->>'project_slug', ''), NULL) AS project_slug,\n COALESCE(NULLIF(metadata_json->>'review_status', ''), 'unreviewed') AS review_status,\n CASE COALESCE(NULLIF(metadata_json->>'review_status', ''), 'unreviewed')\n WHEN 'reviewed' THEN 0\n WHEN 'unreviewed' THEN 1\n WHEN 'suspect' THEN 2\n ELSE 3\n END AS review_priority,\n CASE WHEN embedding IS NOT NULL THEN ROUND((1 - (embedding <=> $1::vector))::numeric, 6) ELSE NULL END AS similarity,\n CASE WHEN embedding IS NOT NULL THEN (embedding <=> $1::vector) ELSE NULL END AS distance,\n (SELECT COUNT(*) FROM lexical_terms lt WHERE searchable.haystack LIKE '%' || lt.term || '%') AS lexical_overlap,\n (SELECT COUNT(*) FROM lexical_terms lt WHERE searchable.title_haystack LIKE '%' || lt.term || '%') AS title_lexical_hits,\n (SELECT COUNT(*) FROM strong_terms st WHERE searchable.haystack LIKE '%' || st.term || '%') AS strong_token_hits,\n true AS lexical_match\n FROM memories\n CROSS JOIN LATERAL (\n SELECT lower(concat_ws(E'\\n', COALESCE(title, ''), COALESCE(content, ''), COALESCE(metadata_json->>'filename', ''), COALESCE(metadata_json->>'filepath', ''))) AS haystack,\n lower(concat_ws(E'\\n', COALESCE(title, ''), COALESCE(metadata_json->>'filename', ''))) AS title_haystack\n ) searchable\n WHERE COALESCE(NULLIF(metadata_json->>'review_status', ''), 'unreviewed') <> 'suppressed'\n AND $2::text IS NOT NULL\n AND NULLIF(COALESCE(metadata_json->>'project_slug', ''), '') = $2::text\n ) project_lex_seed\n WHERE project_lex_seed.lexical_overlap > 0 OR project_lex_seed.strong_token_hits > 0\n ORDER BY project_lex_seed.strong_token_hits DESC, project_lex_seed.title_lexical_hits DESC, project_lex_seed.lexical_overlap DESC, project_lex_seed.review_priority ASC, project_lex_seed.created_at DESC NULLS LAST, project_lex_seed.id DESC\n LIMIT $6::int\n ) project_lex_rows\n ), '[]'::jsonb) AS lexical_project_memories,\n COALESCE((\n SELECT jsonb_agg(row_to_json(general_lex_rows) ORDER BY general_lex_rows.strong_token_hits DESC, general_lex_rows.title_lexical_hits DESC, general_lex_rows.lexical_overlap DESC, general_lex_rows.review_priority ASC, general_lex_rows.created_at DESC NULLS LAST, general_lex_rows.id DESC)\n FROM (\n SELECT *\n FROM (\n SELECT id, created_at, title, source, category, content, metadata_json,\n COALESCE(NULLIF(metadata_json->>'project_slug', ''), NULL) AS project_slug,\n COALESCE(NULLIF(metadata_json->>'review_status', ''), 'unreviewed') AS review_status,\n CASE COALESCE(NULLIF(metadata_json->>'review_status', ''), 'unreviewed')\n WHEN 'reviewed' THEN 0\n WHEN 'unreviewed' THEN 1\n WHEN 'suspect' THEN 2\n ELSE 3\n END AS review_priority,\n CASE WHEN embedding IS NOT NULL THEN ROUND((1 - (embedding <=> $1::vector))::numeric, 6) ELSE NULL END AS similarity,\n CASE WHEN embedding IS NOT NULL THEN (embedding <=> $1::vector) ELSE NULL END AS distance,\n (SELECT COUNT(*) FROM lexical_terms lt WHERE searchable.haystack LIKE '%' || lt.term || '%') AS lexical_overlap,\n (SELECT COUNT(*) FROM lexical_terms lt WHERE searchable.title_haystack LIKE '%' || lt.term || '%') AS title_lexical_hits,\n (SELECT COUNT(*) FROM strong_terms st WHERE searchable.haystack LIKE '%' || st.term || '%') AS strong_token_hits,\n true AS lexical_match\n FROM memories\n CROSS JOIN LATERAL (\n SELECT lower(concat_ws(E'\\n', COALESCE(title, ''), COALESCE(content, ''), COALESCE(metadata_json->>'filename', ''), COALESCE(metadata_json->>'filepath', ''))) AS haystack,\n lower(concat_ws(E'\\n', COALESCE(title, ''), COALESCE(metadata_json->>'filename', ''))) AS title_haystack\n ) searchable\n WHERE COALESCE(NULLIF(metadata_json->>'review_status', ''), 'unreviewed') <> 'suppressed'\n AND $2::text IS NULL\n AND COALESCE(NULLIF(metadata_json->>'project_slug', ''), '') = ''\n ) general_lex_seed\n WHERE general_lex_seed.lexical_overlap > 0 OR general_lex_seed.strong_token_hits > 0\n ORDER BY general_lex_seed.strong_token_hits DESC, general_lex_seed.title_lexical_hits DESC, general_lex_seed.lexical_overlap DESC, general_lex_seed.review_priority ASC, general_lex_seed.created_at DESC NULLS LAST, general_lex_seed.id DESC\n LIMIT $6::int\n ) general_lex_rows\n ), '[]'::jsonb) AS lexical_general_memories,\n COALESCE((\n SELECT jsonb_agg(row_to_json(all_lex_rows) ORDER BY all_lex_rows.strong_token_hits DESC, all_lex_rows.title_lexical_hits DESC, all_lex_rows.lexical_overlap DESC, all_lex_rows.review_priority ASC, all_lex_rows.created_at DESC NULLS LAST, all_lex_rows.id DESC)\n FROM (\n SELECT *\n FROM (\n SELECT id, created_at, title, source, category, content, metadata_json,\n COALESCE(NULLIF(metadata_json->>'project_slug', ''), NULL) AS project_slug,\n COALESCE(NULLIF(metadata_json->>'review_status', ''), 'unreviewed') AS review_status,\n CASE COALESCE(NULLIF(metadata_json->>'review_status', ''), 'unreviewed')\n WHEN 'reviewed' THEN 0\n WHEN 'unreviewed' THEN 1\n WHEN 'suspect' THEN 2\n ELSE 3\n END AS review_priority,\n CASE WHEN embedding IS NOT NULL THEN ROUND((1 - (embedding <=> $1::vector))::numeric, 6) ELSE NULL END AS similarity,\n CASE WHEN embedding IS NOT NULL THEN (embedding <=> $1::vector) ELSE NULL END AS distance,\n (SELECT COUNT(*) FROM lexical_terms lt WHERE searchable.haystack LIKE '%' || lt.term || '%') AS lexical_overlap,\n (SELECT COUNT(*) FROM lexical_terms lt WHERE searchable.title_haystack LIKE '%' || lt.term || '%') AS title_lexical_hits,\n (SELECT COUNT(*) FROM strong_terms st WHERE searchable.haystack LIKE '%' || st.term || '%') AS strong_token_hits,\n true AS lexical_match\n FROM memories\n CROSS JOIN LATERAL (\n SELECT lower(concat_ws(E'\\n', COALESCE(title, ''), COALESCE(content, ''), COALESCE(metadata_json->>'filename', ''), COALESCE(metadata_json->>'filepath', ''))) AS haystack,\n lower(concat_ws(E'\\n', COALESCE(title, ''), COALESCE(metadata_json->>'filename', ''))) AS title_haystack\n ) searchable\n WHERE COALESCE(NULLIF(metadata_json->>'review_status', ''), 'unreviewed') <> 'suppressed'\n AND $2::text IS NULL\n ) all_lex_seed\n WHERE all_lex_seed.lexical_overlap > 0 OR all_lex_seed.strong_token_hits > 0\n ORDER BY all_lex_seed.strong_token_hits DESC, all_lex_seed.title_lexical_hits DESC, all_lex_seed.lexical_overlap DESC, all_lex_seed.review_priority ASC, all_lex_seed.created_at DESC NULLS LAST, all_lex_seed.id DESC\n LIMIT $6::int\n ) all_lex_rows\n ), '[]'::jsonb) AS lexical_all_memories;",
"options": {
"queryReplacement": "={{ [$json.vector_literal, $json.project_slug, $json.candidate_limit, JSON.stringify($json.lexical_query_terms || []), JSON.stringify($json.strong_query_tokens || []), $json.lexical_candidate_limit] }}"
}
},
"id": "postgres-retrieve-candidate-memories",
"name": "Retrieve Candidate Memories",
"type": "n8n-nodes-base.postgres",
"typeVersion": 2.6,
"position": [
2360,
20
],
"credentials": {
"postgres": {
"name": "<your credential>"
}
}
},
{
"parameters": {
"jsCode": "const base = $('Prepare Retrieval Input').first().json;\nconst rawProjectMemories = Array.isArray($json.project_memories) ? $json.project_memories : [];\nconst rawGeneralMemories = Array.isArray($json.general_memories) ? $json.general_memories : [];\nconst rawAllMemories = Array.isArray($json.all_memories) ? $json.all_memories : [];\nconst rawLexicalProjectMemories = Array.isArray($json.lexical_project_memories) ? $json.lexical_project_memories : [];\nconst rawLexicalGeneralMemories = Array.isArray($json.lexical_general_memories) ? $json.lexical_general_memories : [];\nconst rawLexicalAllMemories = Array.isArray($json.lexical_all_memories) ? $json.lexical_all_memories : [];\nconst requestedProjectSlug = typeof base.project_slug === 'string' && base.project_slug.trim() !== '' ? base.project_slug.trim() : null;\nconst matchesRequestedProjectScope = (memory) => {\n if (!memory) return false;\n if (!requestedProjectSlug) return true;\n const metadata = memory.metadata_json && typeof memory.metadata_json === 'object' ? memory.metadata_json : {};\n const candidateProjectSlug = typeof memory.project_slug === 'string' && memory.project_slug.trim() !== ''\n ? memory.project_slug.trim()\n : (typeof metadata.project_slug === 'string' && metadata.project_slug.trim() !== '' ? metadata.project_slug.trim() : null);\n return candidateProjectSlug === requestedProjectSlug;\n};\nconst projectMemories = rawProjectMemories.filter(matchesRequestedProjectScope);\nconst generalMemories = requestedProjectSlug ? [] : rawGeneralMemories.filter(matchesRequestedProjectScope);\nconst allMemories = requestedProjectSlug ? [] : rawAllMemories.filter(matchesRequestedProjectScope);\nconst lexicalProjectMemories = rawLexicalProjectMemories.filter(matchesRequestedProjectScope);\nconst lexicalGeneralMemories = requestedProjectSlug ? [] : rawLexicalGeneralMemories.filter(matchesRequestedProjectScope);\nconst lexicalAllMemories = requestedProjectSlug ? [] : rawLexicalAllMemories.filter(matchesRequestedProjectScope);\nconst topK = base.top_k;\nconst candidateLimit = Number.isInteger(base.candidate_limit) ? base.candidate_limit : topK;\nconst lexicalCandidateLimit = Number.isInteger(base.lexical_candidate_limit) ? base.lexical_candidate_limit : Math.max(topK, 4);\nconst similarityThreshold = 0.72;\nconst stopTerms = new Set(['what', 'when', 'where', 'which', 'who', 'how', 'this', 'that', 'with', 'from', 'into', 'your', 'about', 'does', 'tell', 'note', 'for', 'before']);\nconst queryTerms = Array.isArray(base.query_terms)\n ? base.query_terms.map((value) => String(value).trim().toLowerCase()).filter(Boolean)\n : Array.from(new Set(((base.query || '').toLowerCase().match(/[a-z0-9]{3,}/g) ?? []).filter((term) => !stopTerms.has(term))));\nconst lexicalQueryTerms = Array.isArray(base.lexical_query_terms)\n ? base.lexical_query_terms.map((value) => String(value).trim().toLowerCase()).filter(Boolean)\n : queryTerms;\nconst strongQueryTokens = Array.isArray(base.strong_query_tokens)\n ? base.strong_query_tokens.map((value) => String(value).trim().toLowerCase()).filter(Boolean)\n : [];\nconst anchorDetails = base.anchor_details && typeof base.anchor_details === 'object' ? base.anchor_details : {};\nconst requestedFilename = typeof anchorDetails.requested_filename === 'string' ? anchorDetails.requested_filename.trim() : '';\nconst requestedFilenameLower = requestedFilename.toLowerCase();\nconst quotedTitlePhrases = Array.isArray(anchorDetails.quoted_title_phrases)\n ? anchorDetails.quoted_title_phrases.map((value) => String(value).trim().toLowerCase()).filter(Boolean)\n : [];\nconst anchorTokens = Array.isArray(anchorDetails.anchor_tokens)\n ? anchorDetails.anchor_tokens.map((value) => String(value).trim().toLowerCase()).filter(Boolean)\n : [];\nconst requestedRankingMode = anchorDetails.ranking_mode === 'anchor' ? 'anchor' : 'semantic';\nconst isFailureIntent = base.is_failure_intent === true;\nconst failureDomainTerms = ['problems', 'failures'];\nconst failureDomainPathHint = 'openbrain-history/04-problems-and-failures';\nconst matchesFailureDomain = (titleHaystack, filenameText, filepathText) => {\n if (!isFailureIntent) return false;\n if (filepathText.includes(failureDomainPathHint)) return true;\n if (failureDomainTerms.some((term) => titleHaystack.includes(term))) return true;\n return failureDomainTerms.some((term) => filenameText.includes(term));\n};\nconst normalizeReviewStatus = (value) => {\n const normalized = typeof value === 'string' && value.trim() !== '' ? value.trim() : 'unreviewed';\n if (['reviewed', 'suspect', 'suppressed', 'unreviewed'].includes(normalized)) return normalized;\n return 'unreviewed';\n};\nconst reviewPriority = (value) => {\n const normalized = normalizeReviewStatus(value);\n if (normalized === 'reviewed') return 0;\n if (normalized === 'unreviewed') return 1;\n if (normalized === 'suspect') return 2;\n return 3;\n};\nconst parseTimestamp = (value) => {\n if (typeof value !== 'string' || value.trim() === '') return null;\n const parsed = new Date(value);\n if (Number.isNaN(parsed.getTime())) return null;\n return parsed.toISOString();\n};\nconst timestampValue = (value) => {\n const normalized = parseTimestamp(value);\n return normalized ? Date.parse(normalized) : Number.NEGATIVE_INFINITY;\n};\nconst uniqueValues = (values) => Array.from(new Set(values.filter(Boolean)));\nconst normalizeTopicPhrase = (value) => String(value || '').toLowerCase().replace(/[\"']/g, '').replace(/[()\\[\\]{}]/g, ' ').replace(/\\b(the|a|an|this|that|note|memory)\\b/g, ' ').replace(/\\s+/g, ' ').trim();\nconst inferEntityFocus = (query) => {\n const raw = typeof query === 'string' ? query.trim() : '';\n if (raw === '') return null;\n const patterns = [\n { regex: /\\bwhat\\s+(protocol|anchor|status|owner|definition|meaning)\\s+does\\s+(.+?)\\s+(?:use|have|mean|map(?:\\s+to)?|point(?:\\s+to)?)\\b/i, map: (match) => ({ property: match[1], entity: match[2] }) },\n { regex: /\\bwhat\\s+does\\s+(.+?)\\s+say\\b/i, map: (match) => ({ property: 'statement', entity: match[1] }) },\n { regex: /\\bhow\\s+does\\s+(?:the\\s+)?(.+?)\\s+improve\\b/i, map: (match) => ({ property: 'improve', entity: match[1] }) },\n { regex: /\\bwhat\\s+is\\s+the\\s+(.+?)\\s+(protocol|anchor|status|owner|definition|meaning)\\b/i, map: (match) => ({ entity: match[1], property: match[2] }) },\n { regex: /\\bfind\\s+the\\s+note\\s+with\\s+(anchor|code|id|identifier)\\s+(.+)$/i, map: (match) => ({ property: match[1], terms: [normalizeTopicPhrase(match[2])] }) },\n ];\n let entity = null;\n let property = null;\n let terms = [];\n for (const pattern of patterns) {\n const match = raw.match(pattern.regex);\n if (!match) continue;\n const extracted = pattern.map(match);\n entity = normalizeTopicPhrase(extracted.entity || '');\n property = normalizeTopicPhrase(extracted.property || '');\n terms = uniqueValues((entity ? entity.split(' ') : []).filter((term) => term.length >= 3 && !stopTerms.has(term)));\n if (terms.length === 0 && Array.isArray(extracted.terms)) {\n terms = uniqueValues(extracted.terms.flatMap((value) => normalizeTopicPhrase(value).split(' ')).filter((term) => term.length >= 3 && !stopTerms.has(term)));\n }\n break;\n }\n if (terms.length === 0) return null;\n return {\n entity: entity || null,\n property: property || null,\n terms: terms.slice(0, 6),\n };\n};\nconst entityFocus = inferEntityFocus(base.query);\nconst entityFocusTerms = Array.isArray(entityFocus?.terms) ? entityFocus.terms : [];\nconst entityFocusLabel = typeof entityFocus?.entity === 'string' ? entityFocus.entity : '';\nconst propertyFocus = typeof entityFocus?.property === 'string' ? entityFocus.property : '';\nconst factualQueryPattern = /\\b(what|which|find|list|walk|describe|explain|summarize|outline|show|compare)\\b/i;\nconst conversationalFactualPattern = /\\b(tell me|walk me through|show me)\\b/i;\nconst uncertaintyQueryPattern = /\\b(uncertain|uncertainty|incomplete|not well documented|not documented|not fully know|unknown|history incomplete|partial evidence|missing from the record)\\b/i;\nconst contradictionQueryPattern = /\\b(contradict(?:ion|ory|ions)|conflicting claims|disagree|incompatible claims)\\b/i;\nconst uncertaintyQueryIntent = uncertaintyQueryPattern.test(base.query || '');\nconst contradictionQueryIntent = contradictionQueryPattern.test(base.query || '');\nconst isFactualQuery = requestedRankingMode === 'anchor' || strongQueryTokens.length > 0 || Boolean(propertyFocus) || factualQueryPattern.test(base.query || '') || conversationalFactualPattern.test(base.query || '');\nconst countContains = (haystack, needles) => needles.reduce((count, needle) => count + (needle && haystack.includes(needle) ? 1 : 0), 0);\nconst wordCount = (value) => (typeof value === 'string' ? (value.toLowerCase().match(/[a-z0-9]+/g) ?? []).length : 0);\nconst structuredSignalCount = (memory) => {\n const metadata = memory?.metadata_json && typeof memory.metadata_json === 'object' ? memory.metadata_json : {};\n const haystack = [memory?.title, memory?.content, metadata.filename, metadata.filepath].filter((value) => typeof value === 'string' && value.trim() !== '').join('\\n').toLowerCase();\n const explicitTokens = uniqueValues([...anchorTokens, ...strongQueryTokens]);\n const explicitMatches = countContains(haystack, explicitTokens);\n return explicitMatches;\n};\nconst isUsableContent = (memory) => {\n const value = typeof memory?.content === 'string' ? memory.content.trim() : '';\n if (value.length < 8) return false;\n if (value.includes('\\uFFFD')) return false;\n const alphaCount = (value.match(/[A-Za-z]/g) ?? []).length;\n const structuredSignals = structuredSignalCount(memory);\n if (alphaCount < 4 && structuredSignals === 0) return false;\n const safeCount = (value.match(/[A-Za-z0-9\\s.,:;!?()'\"_\\/-]/g) ?? []).length;\n return safeCount / value.length >= 0.6;\n};\nconst recencyBand = (timestamp) => {\n if (!timestamp) return 'unknown';\n const ageMs = Date.now() - new Date(timestamp).getTime();\n if (ageMs <= 24 * 60 * 60 * 1000) return 'recent_24h';\n if (ageMs <= 7 * 24 * 60 * 60 * 1000) return 'recent_7d';\n if (ageMs <= 30 * 24 * 60 * 60 * 1000) return 'recent_30d';\n return 'older_than_30d';\n};\nconst chunkSizeBand = (length) => {\n if (length < 80) return 'small';\n if (length < 220) return 'medium';\n if (length < 600) return 'large';\n return 'xlarge';\n};\nconst summarizeMemory = (memory, scopeProjectSlug = base.project_slug) => {\n const metadata = memory?.metadata_json && typeof memory?.metadata_json === 'object' ? memory.metadata_json : {};\n return {\n id: memory?.id ?? null,\n title: memory?.title ?? null,\n project_slug: memory?.project_slug ?? null,\n review_status: normalizeReviewStatus(memory?.review_status ?? metadata.review_status),\n source_quality: classifySourceQuality(memory, scopeProjectSlug),\n source_quality_weight: sourceQualityWeight(classifySourceQuality(memory, scopeProjectSlug)),\n similarity: typeof memory?.similarity === 'number' ? Number(memory.similarity.toFixed(6)) : null,\n retrieval_score: typeof memory?.retrieval_score === 'number' ? Number(memory.retrieval_score.toFixed(6)) : null,\n lexical_overlap: memory?.lexical_overlap ?? 0,\n strong_token_hits: memory?.strong_token_hits ?? 0,\n structured_token_hits: memory?.structured_token_hits ?? 0,\n short_note_boost: typeof memory?.short_note_boost === 'number' ? Number(memory.short_note_boost.toFixed(6)) : 0,\n lexical_match: memory?.lexical_match === true,\n passes_relevance: memory?.passes_relevance === true,\n created_at: parseTimestamp(memory?.created_at) ?? parseTimestamp(metadata.ingested_at),\n project_match: scopeProjectSlug ? memory?.project_slug === scopeProjectSlug : !memory?.project_slug,\n anchor_matched: memory?.anchor_matched ?? null,\n entity_focus_hits: memory?.entity_focus_hits ?? 0,\n property_focus_hits: memory?.property_focus_hits ?? 0,\n entity_focus_match: memory?.entity_focus_match === true,\n generic_runtime_noise: memory?.generic_runtime_noise === true,\n intent_domain_match: memory?.intent_domain_match === true,\n intent_domain_boost: typeof memory?.intent_domain_boost === 'number' ? Number(memory.intent_domain_boost.toFixed(6)) : 0,\n };\n};\nconst semanticSeed = [];\nconst semanticSeen = new Set();\nconst addSemanticSeed = (memory) => {\n if (!memory || semanticSeen.has(memory.id) || semanticSeed.length >= candidateLimit) return;\n semanticSeen.add(memory.id);\n semanticSeed.push(memory);\n};\nif (requestedProjectSlug) {\n for (const memory of projectMemories) addSemanticSeed(memory);\n} else {\n for (const memory of allMemories) addSemanticSeed(memory);\n}\nconst lexicalSeed = [];\nconst lexicalSeen = new Set();\nconst addLexicalSeed = (memory) => {\n if (!memory || lexicalSeen.has(memory.id) || lexicalSeed.length >= lexicalCandidateLimit) return;\n lexicalSeen.add(memory.id);\n lexicalSeed.push(memory);\n};\nif (requestedProjectSlug) {\n for (const memory of lexicalProjectMemories) addLexicalSeed(memory);\n} else {\n for (const memory of lexicalAllMemories) addLexicalSeed(memory);\n}\nconst enrichMemory = (memory, lexicalFallbackActive) => {\n const metadata = memory.metadata_json && typeof memory.metadata_json === 'object' ? memory.metadata_json : {};\n const titleText = typeof memory.title === 'string' ? memory.title.trim() : '';\n const contentText = typeof memory.content === 'string' ? memory.content.trim() : '';\n const filenameText = typeof metadata.filename === 'string' ? metadata.filename.trim().toLowerCase() : '';\n const filepathText = typeof metadata.filepath === 'string' ? metadata.filepath.trim().toLowerCase() : '';\n const titleHaystack = [titleText, filenameText].filter(Boolean).join('\\n').toLowerCase();\n const haystack = [titleText, contentText, filenameText, filepathText].filter(Boolean).join('\\n').toLowerCase();\n const lexicalOverlap = Math.max(Number(memory.lexical_overlap ?? 0), lexicalQueryTerms.filter((term) => haystack.includes(term)).length);\n const titleLexicalHits = Math.max(Number(memory.title_lexical_hits ?? 0), lexicalQueryTerms.filter((term) => titleHaystack.includes(term)).length);\n const strongTokenHits = Math.max(Number(memory.strong_token_hits ?? 0), strongQueryTokens.filter((term) => haystack.includes(term)).length);\n const similarity = typeof memory.similarity === 'number' ? memory.similarity : 0;\n const reviewStatus = normalizeReviewStatus(memory.review_status ?? metadata.review_status);\n const createdAt = parseTimestamp(memory.created_at) ?? parseTimestamp(metadata.ingested_at);\n const exactTitleMatch = requestedFilenameLower !== '' && titleHaystack.includes(requestedFilenameLower);\n const titleQuotedPhraseHits = countContains(titleHaystack, quotedTitlePhrases);\n const quotedPhraseHits = countContains(haystack, quotedTitlePhrases);\n const titleAnchorTokenHits = countContains(titleHaystack, anchorTokens);\n const anchorTokenHits = countContains(haystack, anchorTokens);\n const structuredTokenHits = structuredSignalCount(memory);\n const contentWordCount = Math.max(wordCount(contentText), 1);\n const contentLength = contentText.length;\n const shortNoteBoost = contentLength <= 120 ? 0.045 : (contentLength <= 220 ? 0.025 : (contentLength <= 360 ? 0.01 : 0));\n const signalDensity = Math.min((lexicalOverlap + strongTokenHits + structuredTokenHits) / contentWordCount, 0.4);\n const densityBoost = signalDensity * 0.12;\n const lexicalBoost = Math.min(lexicalOverlap, 4) * 0.0125;\n const structuredBoost = Math.min(strongTokenHits + structuredTokenHits, 4) * 0.015;\n const titleBoost = Math.min(titleLexicalHits + titleAnchorTokenHits, 3) * 0.0125;\n const anchorMatched = exactTitleMatch || titleQuotedPhraseHits > 0 || quotedPhraseHits > 0 || titleAnchorTokenHits > 0 || anchorTokenHits > 0;\n const entityFocusMatch = entityFocusLabel !== '' && haystack.includes(entityFocusLabel);\n const entityFocusHits = entityFocusTerms.filter((term) => haystack.includes(term)).length;\n const propertyFocusHits = propertyFocus && haystack.includes(propertyFocus) ? 1 : 0;\n const subjectRelevance = entityFocusMatch || entityFocusHits > 0 || strongTokenHits > 0 || anchorMatched;\n const topicRelevance = subjectRelevance || propertyFocusHits > 0;\n const genericRuntimeNoise = ['build-context', 'runtime-fix', 'runtime-final', 'test-no-project', 'seed-memories', 'openbrain-seed-memories'].some((token) => titleHaystack.includes(token) || haystack.includes(token));\n const genericNoiseEntityMismatch = entityFocusTerms.length > 0 && !entityFocusMatch && entityFocusHits < Math.min(2, entityFocusTerms.length);\n const fallbackBoost = lexicalFallbackActive && (memory.lexical_match === true || lexicalOverlap > 0 || strongTokenHits > 0) ? 0.02 : 0;\n const entityBoost = entityFocusMatch ? 0.07 : Math.min(entityFocusHits, 3) * 0.018;\n const propertyBoost = propertyFocusHits > 0 ? 0.01 : 0;\n const intentDomainMatch = matchesFailureDomain(titleHaystack, filenameText, filepathText);\n const intentDomainBoost = intentDomainMatch ? 0.09 : 0;\n const genericNoisePenalty = isFactualQuery && genericRuntimeNoise && (!subjectRelevance || genericNoiseEntityMismatch) ? 0.22 : 0;\n const offTopicPenalty = isFactualQuery && entityFocusTerms.length > 0 && !subjectRelevance\n ? (propertyFocusHits > 0 ? 0.04 : 0.08)\n : 0;\n const passesSemanticRelevance = similarity >= similarityThreshold || lexicalOverlap >= 1;\n const passesLexicalFallback = (memory.lexical_match === true || lexicalOverlap > 0 || strongTokenHits > 0)\n && (strongTokenHits > 0 || titleLexicalHits > 0 || lexicalOverlap >= 2 || structuredTokenHits > 0 || exactTitleMatch || entityFocusHits > 0 || propertyFocusHits > 0);\n const filteredOut = isFactualQuery && genericRuntimeNoise && ((!subjectRelevance && lexicalOverlap <= 1) || genericNoiseEntityMismatch);\n return {\n ...memory,\n review_status: reviewStatus,\n review_priority: reviewPriority(reviewStatus),\n exact_title_match: exactTitleMatch,\n title_quoted_phrase_hits: titleQuotedPhraseHits,\n quoted_phrase_hits: quotedPhraseHits,\n title_anchor_token_hits: titleAnchorTokenHits,\n anchor_token_hits: anchorTokenHits,\n anchor_matched: anchorMatched,\n created_at_sort_value: timestampValue(createdAt),\n lexical_overlap: lexicalOverlap,\n title_lexical_hits: titleLexicalHits,\n strong_token_hits: strongTokenHits,\n structured_token_hits: structuredTokenHits,\n content_word_count: contentWordCount,\n signal_density: signalDensity,\n short_note_boost: shortNoteBoost,\n lexical_match: memory.lexical_match === true || lexicalOverlap > 0 || strongTokenHits > 0,\n entity_focus_hits: entityFocusHits,\n property_focus_hits: propertyFocusHits,\n entity_focus_match: entityFocusMatch,\n generic_runtime_noise: genericRuntimeNoise,\n intent_domain_match: intentDomainMatch,\n intent_domain_boost: intentDomainBoost,\n filtered_out: filteredOut,\n filter_reason: filteredOut ? 'generic_runtime_noise' : null,\n topic_relevance_score: (entityFocusMatch ? 3 : 0) + entityFocusHits + propertyFocusHits,\n passes_semantic_relevance: passesSemanticRelevance,\n passes_relevance: !filteredOut && (passesSemanticRelevance || (lexicalFallbackActive && passesLexicalFallback)),\n retrieval_score: similarity + shortNoteBoost + densityBoost + lexicalBoost + structuredBoost + titleBoost + fallbackBoost + entityBoost + propertyBoost + intentDomainBoost - genericNoisePenalty - offTopicPenalty,\n };\n};\nconst semanticUsable = semanticSeed.filter((memory) => isUsableContent(memory));\nconst semanticPreview = semanticUsable.map((memory) => enrichMemory(memory, false));\nconst semanticSortedPreview = [...semanticPreview].sort((left, right) => {\n if (base.project_slug) {\n const leftProject = left.project_slug === base.project_slug ? 1 : 0;\n const rightProject = right.project_slug === base.project_slug ? 1 : 0;\n if (rightProject !== leftProject) return rightProject - leftProject;\n }\n if (isFailureIntent) {\n const leftDomain = left.intent_domain_match === true ? 1 : 0;\n const rightDomain = right.intent_domain_match === true ? 1 : 0;\n if (rightDomain !== leftDomain) return rightDomain - leftDomain;\n }\n if (left.review_priority !== right.review_priority) return left.review_priority - right.review_priority;\n if ((right.retrieval_score ?? 0) !== (left.retrieval_score ?? 0)) return (right.retrieval_score ?? 0) - (left.retrieval_score ?? 0);\n if ((right.similarity ?? 0) !== (left.similarity ?? 0)) return (right.similarity ?? 0) - (left.similarity ?? 0);\n if (right.created_at_sort_value !== left.created_at_sort_value) return right.created_at_sort_value - left.created_at_sort_value;\n return (right.id ?? 0) - (left.id ?? 0);\n});\nconst semanticStrongPreview = semanticSortedPreview.filter((memory) => memory.passes_semantic_relevance).slice(0, Math.max(topK, 2));\nconst semanticTopSimilarity = semanticStrongPreview[0]?.similarity ?? 0;\nconst semanticWeakOrSparse = semanticStrongPreview.length === 0 || semanticTopSimilarity < similarityThreshold + 0.03 || (semanticStrongPreview.length < 2 && semanticTopSimilarity < similarityThreshold + 0.06);\nconst shouldUseLexicalFallback = requestedRankingMode === 'anchor' || strongQueryTokens.length > 0 || semanticWeakOrSparse;\nconst candidateById = new Map();\nconst addCandidate = (memory, origin) => {\n if (!memory || memory.id == null) return;\n const existing = candidateById.get(memory.id);\n const merged = {\n ...(existing || {}),\n ...memory,\n similarity: memory.similarity ?? existing?.similarity ?? null,\n distance: memory.distance ?? existing?.distance ?? null,\n lexical_overlap: Math.max(Number(existing?.lexical_overlap ?? 0), Number(memory?.lexical_overlap ?? 0)),\n title_lexical_hits: Math.max(Number(existing?.title_lexical_hits ?? 0), Number(memory?.title_lexical_hits ?? 0)),\n strong_token_hits: Math.max(Number(existing?.strong_token_hits ?? 0), Number(memory?.strong_token_hits ?? 0)),\n lexical_match: Boolean(existing?.lexical_match) || Boolean(memory?.lexical_match),\n semantic_seed: Boolean(existing?.semantic_seed) || origin === 'semantic',\n lexical_seed: Boolean(existing?.lexical_seed) || origin === 'lexical',\n };\n candidateById.set(memory.id, merged);\n};\nfor (const memory of semanticSeed) addCandidate(memory, 'semantic');\nif (shouldUseLexicalFallback) for (const memory of lexicalSeed) addCandidate(memory, 'lexical');\nconst selected = Array.from(candidateById.values());\nconst usableSelected = selected.filter((memory) => isUsableContent(memory));\nconst suspectMemoryCount = selected.length - usableSelected.length;\nconst scoredSelected = usableSelected.map((memory) => enrichMemory(memory, shouldUseLexicalFallback));\nconst filteredSelected = scoredSelected.filter((memory) => memory.filtered_out !== true);\nconst filteredCandidateCount = scoredSelected.length - filteredSelected.length;\nconst semanticSorted = [...filteredSelected].sort((left, right) => {\n if (base.project_slug) {\n const leftProject = left.project_slug === base.project_slug ? 1 : 0;\n const rightProject = right.project_slug === base.project_slug ? 1 : 0;\n if (rightProject !== leftProject) return rightProject - leftProject;\n }\n if (isFailureIntent) {\n const leftDomain = left.intent_domain_match === true ? 1 : 0;\n const rightDomain = right.intent_domain_match === true ? 1 : 0;\n if (rightDomain !== leftDomain) return rightDomain - leftDomain;\n }\n if (left.review_priority !== right.review_priority) return left.review_priority - right.review_priority;\n if ((right.retrieval_score ?? 0) !== (left.retrieval_score ?? 0)) return (right.retrieval_score ?? 0) - (left.retrieval_score ?? 0);\n if ((right.similarity ?? 0) !== (left.similarity ?? 0)) return (right.similarity ?? 0) - (left.similarity ?? 0);\n if (right.created_at_sort_value !== left.created_at_sort_value) return right.created_at_sort_value - left.created_at_sort_value;\n return (right.id ?? 0) - (left.id ?? 0);\n});\nconst anchorSorted = [...filteredSelected].filter((memory) => memory.anchor_matched || memory.strong_token_hits > 0 || memory.title_lexical_hits > 0).sort((left, right) => {\n if (left.exact_title_match !== right.exact_title_match) return left.exact_title_match ? -1 : 1;\n if (right.title_quoted_phrase_hits !== left.title_quoted_phrase_hits) return right.title_quoted_phrase_hits - left.title_quoted_phrase_hits;\n if (right.quoted_phrase_hits !== left.quoted_phrase_hits) return right.quoted_phrase_hits - left.quoted_phrase_hits;\n if (right.title_anchor_token_hits !== left.title_anchor_token_hits) return right.title_anchor_token_hits - left.title_anchor_token_hits;\n if (right.anchor_token_hits !== left.anchor_token_hits) return right.anchor_token_hits - left.anchor_token_hits;\n if (right.strong_token_hits !== left.strong_token_hits) return right.strong_token_hits - left.strong_token_hits;\n if (right.title_lexical_hits !== left.title_lexical_hits) return right.title_lexical_hits - left.title_lexical_hits;\n if (isFailureIntent) {\n const leftDomain = left.intent_domain_match === true ? 1 : 0;\n const rightDomain = right.intent_domain_match === true ? 1 : 0;\n if (rightDomain !== leftDomain) return rightDomain - leftDomain;\n }\n if (left.review_priority !== right.review_priority) return left.review_priority - right.review_priority;\n if ((right.retrieval_score ?? 0) !== (left.retrieval_score ?? 0)) return (right.retrieval_score ?? 0) - (left.retrieval_score ?? 0);\n if (right.created_at_sort_value !== left.created_at_sort_value) return right.created_at_sort_value - left.created_at_sort_value;\n return (right.id ?? 0) - (left.id ?? 0);\n});\nconst rankingMode = requestedRankingMode === 'anchor' && anchorSorted.length > 0 ? 'anchor' : 'semantic';\nconst rankedStrongPool = (rankingMode === 'anchor' ? anchorSorted : semanticSorted).filter((memory) => memory.passes_relevance);\nconst selectCompetingMemories = (memories) => {\n if (memories.length === 0) return [];\n const baseLimit = Math.min(Math.max(topK + 2, topK), Math.max(topK + 4, 6));\n const chosen = memories.slice(0, baseLimit);\n const cutoff = chosen[chosen.length - 1]?.retrieval_score ?? null;\n for (const memory of memories.slice(baseLimit)) {\n if (chosen.length >= Math.min(baseLimit + 2, candidateLimit)) break;\n const closeScore = cutoff !== null && Math.abs((memory.retrieval_score ?? 0) - cutoff) <= 0.025;\n const p
Credentials you'll need
Each integration node will prompt for credentials when you import. We strip credential IDs before publishing — you'll add your own.
postgres
Pro
For the full experience including quality scoring and batch install features for each workflow upgrade to Pro
About this workflow
assistant. Uses postgres, httpRequest. Webhook trigger; 18 nodes.
Source: https://github.com/Crispy-Biscuits-AI/crispybrain/blob/f6bfa58df50e78c236ebde113422cc01522b88c7/workflows/assistant.json — original creator credit. Request a take-down →
Related workflows
Workflows that share integrations, category, or trigger type with this one. All free to copy and import.
CMM. Uses httpRequest, postgres, redis. Webhook trigger; 90 nodes.
HTTP Request, Postgres, Redis
Scraping. Uses httpRequest, postgres, @apify/n8n-nodes-apify, respondToWebhook. Webhook trigger; 61 nodes.
HTTP Request, Postgres, @Apify/N8N Nodes Apify
Workflow B — AI Listing Engine. Uses httpRequest, postgres, errorTrigger. Webhook trigger; 47 nodes.
HTTP Request, Postgres, Error Trigger
LogSentinel Workflow. Uses postgres, emailSend, httpRequest. Webhook trigger; 44 nodes.
Postgres, Email Send, HTTP Request
Post-Prayer. Uses postgres, httpRequest. Webhook trigger; 44 nodes.
Postgres, HTTP Request