ChiveBot Build

How was the ChiveBot implemented?

Archive Indexing

match_chivebot_entries returns days inside the logging window only — Archive Start (today if unset) through today, then organization Date Active through the day before Date Inactive.

src/lib/chivebot.ts builds the text that later goes into chivebot_entry_embedding:

One row per filled daily entry field. Empty fields are skipped, so an auto-generated shell with nothing filled in has no rows.
saveDay() calls syncDayEmbeddings(). A field whose text is unchanged and already has a vector skips Gemini, and a field that was cleared is deleted.
Clearing a date range calls deleteDayEmbeddings() — deleting a day, a work row, a citation, or an organization drops matching rows by foreign key.

sendChivebotMessage() calls backfillEntryEmbeddings() before it retrieves:

Gemini (gemini-embedding-001, 768 dimensions, RETRIEVAL_DOCUMENT) turns field text into vectors in batches of 16. Text is clipped at 8000 characters. src/lib/gemini.ts sends those requests with GEMINI_API_KEY.
A matching stored text that already has a vector skips Gemini and the write.
If Gemini quota fails on save, the Archive write still stands. Retrieve uses rows that already have a vector.

Chatbot Interface

src/app/actions/chivebot.ts:

Restores message history when the browser has a chivebot_session cookie.
Looks up matching fields in chivebot_entry_embedding, then asks Gemini to write a reply from those excerpts and an archive overview on Send.
Clears the message history by dropping the chivebot_session cookie and deleting chat sessions when Clear Chat is clicked.

Gemini (gemini-embedding-001, 768 dimensions, RETRIEVAL_QUERY) turns the question into a vector so fields can match by meaning.
That vector is stored on chivebot_query_embedding (scripts/chivebot.sql), and the question text stays on chivebot_message. Row-level security is on with no anon / authenticated policies — server actions read and write to those rows with SUPABASE_SECRET_KEY.
Postgres then runs match_chivebot_entries (cosine distance) and keeps the closest 16 fields that already sit inside the logging window. sendChivebotMessage() applies that same window again so a day outside it cannot be quoted.
loadArchiveOverview() adds every organization, including hidden ones and ones with no days in this window, plus filled-in days and empty logs, newest first, up to 80 of each.
If the question cannot be embedded, Send stops. The panel shows the Gemini quota message, or that ChiveBot could not answer.

Gemini (gemini-flash-lite-latest) writes the reply from the overview and those field excerpts.
If the excerpts do not contain the answer, ChiveBot says it could not find anything in the archive.
Answers are also instructed not invent entries, fabricate information, or provide general information unrelated to the logged CareerChive archive entries or organizations.

Chat History

chivebot_session, chivebot_message, and chivebot_query_embedding in Supabase, created by scripts/chivebot.sql, store one chat thread, its turns, and the question vectors.
The browser never talks to these tables — row-level security is on with no anon / authenticated policies. sendChivebotMessage() reads and writes them with SUPABASE_SECRET_KEY.

chivebot_session is one conversation — cookie chivebot_session (httpOnly, path /, one year) stores that row's ID so opening ChiveBot again restores the same thread.
Send creates that session when the cookie is missing or stale, and writes the question immediately, then the reply after Gemini finishes.
Clear Chat deletes that cookie and every session row.

chivebot_message is each turn in that conversation — the user's question or the assistant's reply (role), the wording (content), and for replies the Archive day links used in the text (citations).
Deleting a session deletes associated messages.

chivebot_query_embedding is the question vector — one row per user turn (message_id), the session id, and a 768-dimension embedding. session_id is repeated so Clear Chat can cascade without walking messages.
Send writes it after the question row and before retrieve.
Clear Chat deletes these rows with the session. Archive field embeddings in chivebot_entry_embedding stay until the log field, day, or organization is removed.