Let’s get on to displaying chat histories on request. Expect this is going to involve a lot of changes and code.

Get User Id

I was originally planning that when Show conversation history is selected in the sidebar, I’d display a form to get the user id and the chat topic. But I have since reconsidered that approach. I am basically obtaining the user id as a proxy for the user logging in or creating a new account. For my present purposes, I certainly don’t need any real authentication.

So, to start I am going to display the introduction message (with some additional changes). And a text input requesting the user’s id. I had planned to put the input in the sidebar, but have decided to put it in the main window. And, because the user id input is a proxy for an actual login, I will add a log out option to the actions menu. Once the user id/name is provided, I will:

  • display user id/name at the top of the sidebar
  • display the available actions menu in the sidebar
  • check for suitable message histories in the database
  • if found, display the available topics
    • with the option to load that conversation and continue chatting with the LLM or to just view the history

More interface decisions to come and to code. But the above will be a good start. We will also need to make sure the user id and any selected topic is preserved over the Streamlit code reruns. Which occur pretty much any time an action is performed. I will, perhaps obviously, use session_state for this purpose.

Since this will be a significant bit of refactoring, I think I will just start a new module, chatbot_3.py. I will copy over all the stuff that likely won’t change during the refactor, or will change at some later time. This will hopefully allow me to test my changes from the get go.

I am not going to show any of the copied over code. All new or refactored code will be shown. Nor will I show the markdown file used for the instruction page. I am sure you can sort that out. But I will mention that Streamlit does provide some colour control. Something Markdown, currently, does not. So, in that markdown file, the red text was given by:

:red[**you must enter a user id**]

Function to Initiatlize Session State

Let’s get some basics out of the way. First a function to initialize my session state variables. It will take an ovr_wrt=False parameter. That way I can just call the function with the parameter set to True from the logout() function.

def init_sst(ovr_wrt=False):
  # put this in its own function as I expect there will be a mumber of state variables
  # it also makes logout() pretty simple
  if 'user_id' not in st.session_state or ovr_wrt:
    st.session_state.user_id = ""
  # topic for current or past conversation
  if 'topic' not in st.session_state or ovr_wrt:
    st.session_state.topic = ""
  # show sidebar content?
  if 'show_sb' not in st.session_state or ovr_wrt:
    st.session_state.show_sb = False
  # is user id acceptable
  if 'bad_uid' not in st.session_state or ovr_wrt:
    st.session_state.bad_uid = False


def logout():
  init_sst(ovr_wrt=True)

Get and Process User Id

I also plan to use a callback in the text_input widget to update the session state when a valid user id is entered. But first, let’s get the input displayed. What you are seeing below is the final version of the code. Originally I was not in any way limiting the user id. But I eventually decided to add a restriction and validation (practice you know). I am limiting the id to 3 to 8 characters. Not much validation, but…

The current set up clearly allows different people to use the same id. Something that would not be at all acceptable in a production situation. But, this is me playing around. Not writing anything resembling production code.

Which meant another state variable to track whether or not the id input by the user was valid. I also didn’t want the text input area stretching across the page. So, I used a Streamlit columns container to control the size of the area containing the input element.

The actions list will likely be changing down the road. I just don’t yet know how or why.

... ...
# initialize session state
  init_sst()
... ...
  # I want an empty sidebar to be displayed
  sb_ttl = st.sidebar.title("")
  # Only show the sidebar content if a valid user id provided
  if st.session_state.show_sb:
    # get rid of the empty title, its taking up space
    sb_ttl.empty()
    # show current user id
    st.sidebar.write(f"User: {st.session_state.user_id}")
    app_mode = st.sidebar.radio("What to do next?",
      ["Show instructions", "Show the source code", "Show conversation history", "Run the app", "Logout"])
  else:
    # otherwise show instruction page with login input field
    app_mode = "Show instructions"

  if app_mode == "Show instructions":
    if st.session_state.user_id == "":
      if st.session_state.bad_uid:
        st.warning('Please enter a valid user id (3 to 8 characters)', icon="⚠️")
        st.session_state.bad_uid = False
      # don't want text_input stretching across full width of page.
      c1, c2 = st.columns([1, 4])
      with c1:
        u_id = st.text_input(
          "**User ID/Name:**",
          placeholder="3 to 8 characters",
          on_change=updt_uid,
          key="u_id",
          label_visibility="visible",
        )
      with c2:
        st.write("")
      # if user id supplied, show in sidebar
      if st.session_state.user_id != "":
        st.sidebar.write(f"User: **{st.session_state.user_id}**")
  elif app_mode == "Logout":
    logout()
    # make sure go back to start page with login input after logout
    app_mode = "Show instructions"
... ...

And here’s that startup/login page as it currently stands.

current state of the streamlit apps starting page

Here’s the login area following an attempt to enter an user id that is too long.

a view of the input area on the page following an attempt to enter an unacceptable user id

And after a successful log in, the page currently looks like this.

a view of the starting page after a successful login

At this point selecting Logout fails to work as expected.

Fix Logout

Having taken a break after writing, and coding, the above, I was able to let the back of mind sort things out. Or at least I think it has. I currently believe this is once again Streamlit’s reruns and session state at work. Or perhaps, not at work.

Once the user is logged in I assign the output of the actions menu radio widget to a Python module variable, app_mode. But in the call to the widget’s method I do not specify a key parameter. So, Streamlit automatically generates a key for the widget. It uses this key to store the state of the radio widget (i.e. which button is currently selected) in the session state. It does so to make sure that the state of the radio buttons is preserved between script reruns.

In my code, when Logout is selected the sessions state variables are reset and I set the module variable, app_mode, to Show instructions. But when this causes a script rerun, the module variable is reset to the value that Streamlit has saved in session state.

At first I thought I could add the parameter key=app_mode. Change all references to app_mode to be st.session_state.app_mode. Figuring that when I set the session state variable to a specific action, the radio buttons would be altered accordingly.

But…

... ...
streamlit.errors.StreamlitAPIException: `st.session_state.app_mode` cannot be modified after the widget with key `app_mode` is instantiated.

So I changed the key to app_op and added a callback function that simply set the session state variable app_mode to the value of the session state variable app_op. That sort of worked, but not exactly as desired. The display was not returned to the starting state. There was still radio buttons in the side bar and the user id was still displayed. That is until I clicked one of the radio buttons. So, I am guessing the script rerun occurred before the app_mode variable was changed in session state. I added a command to force a script rerun to the end of the logout function. That seemed to do the trick. But that is apparently not considered best practice according to the Streamlit docs for rerun(). But it currently solves my problem.

Here are the relevant code changes.

def init_sst(ovr_wrt=False):
... ..
  # track actions menu selection
  if "app_mode" not in st.session_state or ovr_wrt:
    st.session_state.app_mode = "Show instructions"


def logout():
  init_sst(ovr_wrt=True)
  st.rerun()
  
... ...

def updt_mode():
  st.session_state.app_mode = st.session_state.app_op

... ...

       app_mode = st.sidebar.radio("What to do next?",
        ["Show instructions", "Show the source code", "Show conversation history", "Run the app", "Logout"],
        key="app_op", on_change=updt_mode)
... ...
  if  st.session_state.app_mode == "Show instructions":
... ...
  elif  st.session_state.app_mode == "Logout":
    logout()
  elif ... ....

But, I really need to sort out why there is apparently no automatic script rerun following the callback being executed.

Afterthought

During the night following my writing of the above, it occurred to me that clicking the Logout button was causing a script rerun. But all I did in the script was set a session state variable. During the rerun, I set a few variables. Which of course does not invoke a rerun. I figured, if I put the logout code in the callback function that would likely produce the result I was after. And without the need for the forced script rerun. That turned out to be correct. I removed the elif block checking for the Logout radio button being selected. And refactored the callback.

def updt_mode():
  st.session_state.app_mode = st.session_state.app_op
  if st.session_state.app_mode == "Logout":
    init_sst(ovr_wrt=True)

Much tidier m’thinks.

Get Conversations in Database

Okay, time to move on. Let’s work on getting the topics for any conversations, in the database, for the current user.

I am going to use the thread_id field to store the user id and conversation topic. Something like userid_topic. If topic is more than one word, there will be underscores between each word as well. That unfortunately is currently not the case in any of the current databases I have created.

Refactor thread_id

So, I am going to take two of the databases I saved during development and change the field accordingly using some basic SQL. Then I am going to attempt to merge the two databases so that I have a database with two topics in it. Those are going to be soup and pets.

The following code is pretty crappy. The database file names are hard coded and the new thread field values are hard coded. There are a number of if blocks to control things as I worked on this database update and merger. I changed the thread_id field before merging as I think it would have been truly messy, perhaps impossible, to do so after. They both had a thread_id of bark1. And, of course, lots of code duplication. But, I found the process somewhat interesting so am including it in the post.

if do_tid:
  if False:
    nw_tid = "bark_soup"
    db_fl = "history_2.sqlite"
  else:
    nw_tid = "bark_pets"
    db_fl = "history_pets.sqlite"

  try:
    conn = sqlite3.connect(db_fl)
    # Create cursor object
    cur = conn.cursor()
      
    # get names of tables in database file
    sql_query = "SELECT name FROM sqlite_master WHERE type='table';"
    tbls = cur.execute(sql_query).fetchall()
    # tbls is list of tuples, we only want table names
    # and in this case the second element of each tuple is empty
    # e.g. [('checkpoints',), ('writes',)]
    tbl_nms = sorted(list(zip(*tbls))[0])

    # update thread_id in both tables
    for tbl in tbl_nms:
      print(f"\nprocessing {tbl}")
      updt_qry = f'UPDATE {tbl} SET thread_id = "{nw_tid}"'
      thrds = cur.execute(updt_qry).fetchall()
      conn.commit()
      slct_qry = f'SELECT thread_id FROM {tbl}'
      nw_t = cur.execute(slct_qry).fetchall()
      print(f"\t{nw_t}")

  except sqlite3.Error as error:
    print("Failed to execute the above query", error)
  finally:
    # if cursor and/or connection is/are open, we need to close it/them
    if cur:
      cur.close()
    if conn:
        conn.close()

if do_mrg:
  try:
    conn1 = sqlite3.connect("history_2.sqlite")
    conn2 = sqlite3.connect("history_pets.sqlite")
    # Attach the second database to the first connection
    conn1.execute("ATTACH DATABASE 'history_pets.sqlite' AS db2")
    # Copy data from the second database to the first
    conn1.execute("INSERT INTO checkpoints SELECT * FROM db2.checkpoints")
    conn1.execute("INSERT INTO writes SELECT * FROM db2.writes")
    # Commit changes and close connections
    conn1.commit()
    conn1.close()
    conn2.close()
  except sqlite3.Error as error:
    print("Failed to execute the above query", error)
  finally:
    # if cursor and/or connection is/are open, we need to close it/them
    if conn1:
        conn1.close()
    if conn2:
        conn2.close()

I won’t bother with any of the terminal output. Suffice it to say, things appear to have worked. That said, there are apparently a great many things that can interfere when merging two databases with the same schema. So, perhaps I am lucky it worked.

Also note, I could have just attached the two modified databases in memory and extracted the topics and/or conversation based on topic. But, I thought it might be fun to try something a little more challenging.

Thought I would mention, that I did look at adding a topic column to the database. I was hoping SqliteSaver was written in such a way that it would automatically save the topic if I included it in the configuration object. A look at the code made it clear that would not happen. I did think about downloading the code and refactoring it. But decided against that at this time. Though out of curiousity, I may try to do so down the road.

Determine Conversation Topics for Current User

On to the nitty gritty of the current effort.

For now I will write a function, taking a cursor, that returns a list of the topics for the current user in the current database. I will save the list in session state to make it available to all app functions/modes. During development I will display the list in the sidebar. I have not figured out how or where I will make the list available to the user.

I was thinking about putting the list in a form in the side bar as radio buttons. Then using the selected/provided topic when they ask to see the stored conversation or resume using the conversation with the LLM. I would also add a text field for the user to enter a new topic to start discussing with the LLM. But not sure how intuitive that arrangement would be. (Never been any good at user interface design, my brain don’t work that way.) Actually not too sure how intuitive or useful the topic field is in general. But, it struck me as having some potential value.

Note the WHERE/LIKE clause in the query to get thread ids from the database. It is saying find occurences of thread_id that start with the current user’s id.

... ... # add variable for datbase file name
db_fl = "history_2.sqlite"
... ... # add couple  more session state variables
  # get list of topics
  if "get_topics" not in st.session_state or ovr_wrt:
    st.session_state.get_topics = True
  if "lst_topics" not in st.session_state or ovr_wrt:
    st.session_state.lst_topics = None
... ...
def get_topics(cur):
  sql_query = f"SELECT DISTINCT thread_id FROM checkpoints WHERE thread_id LIKE '{st.session_state.user_id}%';"
  tids = cur.execute(sql_query).fetchall()
  # the above returns a list of lists of one string
  tids = [tid[0] for tid in tids]
  t_ndx = len(st.session_state.user_id)
  # get rid of any remaining underscores, i.e. multi-word topic
  lst_tmp = [tid[t_ndx:].replace("_", " ") for tid in tids]
  lst_tmp.sort()
  st.session_state.lst_topics = lst_tmp
  st.session_state.get_topics = False


def show_topics():
  st.sidebar.subheader(f"Stored topics", divider="blue")
  t_md = f"  - {st.session_state.lst_topics[0]}"
  for tp in st.session_state.lst_topics[1:]:
    t_md = f"{t_md}\n  - {tp}"
    st.sidebar.markdown(t_md)
... ... # refactor sidebar display
    st.sidebar.subheader(f"Options:", divider="blue")
... ...
    app_mode = st.sidebar.radio("**What to do next?**",
      ["Show instructions", "Show the source code", "Show conversation history", "Run the app", "Logout"],
      key="app_op", on_change=updt_mode)
    if st.session_state.lst_topics:
      show_topics()
... ... # get the list of topics for current user from database and display in sidebar (for now)
    if st.session_state.get_topics and not st.session_state.user_id == "":
      with sqlite3.connect(db_fl, check_same_thread=False) as conn:
        cur = conn.cursor()
        get_topics(cur)
        cur.close()
      if st.session_state.lst_topics:
        show_topics()

And after a lot of messing around (it took me a while to sort out various bugs as I went along), here’s how the sidebar looks after I enter the user id bark. I did try to make things a little more presentable.

a view of the sidebar after a successful login showing the list of topics in the database for the current user

Done

I think that’s it for this post. Plenty long enough for me. I still need to sort out how I am going to handle the options to view conversation histories or run the app with an existing conversation or a new topic.

Until next time, may you enjoy your coding time as much as I have with this project. It has been more about learning how use Streamlit, SQLite and such than about coding a chatbot. But, learning is learning and I do quite enjoy experimenting with pretty much anything.

Resources

A bunch of SQL stuff. Been a while since I wrote any amount of SQL code. Needed some refreshing.