Replace Pinecone with Postgres

Summary

1. Modify job_chat and the related services search_docsite and embed_docs to use Postgres instead of Pinecone
2. Leverage the Postgres database to add keyword-based search for job_chat RAG.

Problems

• job_chat currently only uses vector search to fetch OpenFn documentation chunks to add to the assistant prompt.
  - The full/truncated, version-specific adaptor typescript definitions are copied into the job_chat prompt. These definitions are often very long and irrelevant.

Key results

1. Add keyword search: As this assistant searches through technical documentation, it would be especially useful to add more keyword-based search to the current vector similarity-based search, as this will allow searching for precise terms such as function names, and so balance the downsides of vector search. (see #284 for why Postgres)
   2) Improve the core prompt: Replace the adaptor typescript definitions with a list of available functions that the model can search for. (We will optimise the processing of the adaptor documentation and upload them to the database in #285.).
2. Consolidate databases: Ideally, we would only manage one database, so we will save our embeddings to Postgres instead of Pinecone.

There could be problems with vector search accuracy, speed etc in job_chat, so the implementation will need to be tested check it is as good as our existing solution with Pinecone.

What needs doing:

• Modify the embed_docs service to make it possible to save chunks and embeddings to the Postgres database. Try adjusting chunk size from 1k to 2-3k characters.
• Modify the search_docsite service to include keyword search
• Modify retrieve_docs.py in job_chat accordingly
  - Modify prompt.py in job_chat to remove the full adaptor docs, and replace with a list of available functions.
  - Check if the prompt.py search scope needs to be edited. Decide on whether this should all be under "adaptor docs" from the point of view of the model doing the search, or if it should be aware of the two documentation types.

Not essential, but something to consider:

A separate, structured docs database, that is fetched not with keywords, but by sub-section title. This is similar to how Claude Code searches through a codebase, and more like a human might search through the docs. We might have different search tools available to the agent, e.g.

search_docs(query)           # General search - uses hybrid internally
get_section(section_name)    # Direct navigation by name
find_code_examples()

Start by tracing the current Pinecone flow through the job_chat, search_docsite, and embed_docs services, then inspect retrieve_docs.py. The work is done when embeddings are stored in Postgres, search_docsite supports keyword search alongside vector search, retrieve_docs.py uses the new path, and results are checked against the existing Pinecone behavior.