Tryint to implement soft locking mechanism but failing..

Using Streamlit
1

I have the following code which tries to lock a record currently beign edited through a flag on that row (updated_by) and immediately removing the flag after a user edit is sent. That bit seems to work ok, however subsequently navigating through the recordset updates the locked_by flag each time the user moves to a different record i.e. the previous one becomes locked (the flag is updated to their username).Can anyone tell what is going wrong here, tried various rewrites of this but not having much luck:

import streamlit as st
import pandas as pd
import sqlite3
import os

# === SETTINGS ===
DB_PATH = os.path.join(r"G:\my-stuff\projects\data-dictionary\ds-tesco-cde-tool-env", "suppliers.db")
CURRENT_USER = os.getenv("USERNAME") or "default_user"

# === DB INITIAL SETUP ===
def init_db():
    conn = sqlite3.connect(DB_PATH)
    c = conn.cursor()
    c.execute('''
        CREATE TABLE IF NOT EXISTS suppliers (
            id INTEGER PRIMARY KEY,
            name TEXT NOT NULL,
            region TEXT,
            is_active INTEGER,
            locked_by TEXT
        )
    ''')
    c.execute("SELECT COUNT(*) FROM suppliers")
    if c.fetchone()[0] == 0:
        suppliers = [
            ("Alpha Ltd", "UK", 1),
            ("Beta Inc", "US", 1),
            ("Gamma GmbH", "DE", 1),
            ("Delta Pty", "AU", 1),
            ("Epsilon Co", "JP", 1),
        ]
        c.executemany("INSERT INTO suppliers (name, region, is_active) VALUES (?, ?, ?)", suppliers)
    conn.commit()
    conn.close()

# === GET DATA ===
def get_data():
    conn = sqlite3.connect(DB_PATH)
    df = pd.read_sql_query("SELECT * FROM suppliers", conn)
    conn.close()
    return df

# === LOCK RECORD ===
def lock_record(record_id):
    conn = sqlite3.connect(DB_PATH)
    c = conn.cursor()
    c.execute("SELECT locked_by FROM suppliers WHERE id=?", (record_id,))
    current_lock = c.fetchone()[0]
    if current_lock and current_lock != CURRENT_USER:
        conn.close()
        return False
    c.execute("UPDATE suppliers SET locked_by=? WHERE id=?", (CURRENT_USER, record_id))
    conn.commit()
    conn.close()
    return True

# === UPDATE RECORD ===
def update_record(record_id, name, region, is_active):
    conn = sqlite3.connect(DB_PATH)
    c = conn.cursor()
    c.execute("""
        UPDATE suppliers
        SET name=?, region=?, is_active=?, locked_by=NULL
        WHERE id=?
    """, (name, region, is_active, record_id))
    conn.commit()
    conn.close()

# === STREAMLIT UI ===
st.set_page_config(page_title="Supplier Editor", layout="centered")
st.title("Supplier Master Editor")

if "just_updated_id" not in st.session_state:
    st.session_state.just_updated_id = None

if "previous_id" not in st.session_state:
    st.session_state.previous_id = None

init_db()
df = get_data()

st.subheader("📋 Current Supplier Records")
st.dataframe(df, use_container_width=True)

supplier_display = {
    row["id"]: f'{row["id"]}: {row["name"]}'
    for _, row in df.iterrows()
}

selected_id = st.selectbox(
    "Select a supplier to edit:",
    options=list(supplier_display.keys()),
    format_func=lambda x: supplier_display.get(x, str(x))
)

# === UNLOCK previous record if navigated away ===
if st.session_state.previous_id is not None and st.session_state.previous_id != selected_id:
    conn = sqlite3.connect(DB_PATH)
    c = conn.cursor()
    c.execute("UPDATE suppliers SET locked_by=NULL WHERE id=?", (st.session_state.previous_id,))
    conn.commit()
    conn.close()

# === Update session state AFTER unlocking ===
st.session_state.previous_id = selected_id

# === Only lock if not just updated ===
if st.session_state.just_updated_id == selected_id:
    st.session_state.just_updated_id = None
else:
    if not lock_record(selected_id):
        st.error("This record is currently being edited by another user.")
        st.stop()

# === Display form ===
record = df[df['id'] == selected_id].iloc[0]
st.write(f"### Editing Record ID {selected_id}")
name = st.text_input("Name", record["name"])
region = st.text_input("Region", record["region"])
is_active = st.checkbox("Is Active", value=bool(record["is_active"]))

# === Update handler ===
if st.button("Update Record", key=f"update_{selected_id}"):
    if not lock_record(selected_id):
        st.error("This record is currently being edited by another user.")
        st.stop()

    update_record(selected_id, name, region, int(is_active))
    st.success("Record updated.")
    st.session_state.just_updated_id = selected_id

    st.rerun()