openbb_sec Form 4 fetcher's shared on-disk cache corrupts under concurrent calls

Summary

openbb_sec/utils/form4.py::download_data() uses a single, shared on-disk cache file (sec_form4.db / sec_form4.db.gz, one file total, not keyed per symbol or per call) with a lifecycle that decompresses it at the start of every call and compresses-then-deletes the raw file at the end of every call. Under concurrent calls (e.g. fetching insider-trading data for several symbols at once from separate threads/tasks), one call's end-of-lifecycle close_db() (which sorts the table, closes the SQLite connection, gzips the file, and deletes the raw .db) can run while another call is still mid-flight against the same file, corrupting it. Once corrupted, every subsequent call fails.

Environment

• openbb 4.7.2
• openbb-core 1.6.13 (latest available on PyPI at time of filing)
• openbb-sec 1.6.7 (latest available on PyPI at time of filing)
• Python 3.12, Linux

Root cause

download_data()'s cache lifecycle, every single call:

if use_cache is True:
    db_dir = f"{get_user_cache_directory()}/sql"
    db_path = f"{db_dir}/sec_form4.db"
    if os.path.exists(f"{db_path}.gz"):
        decompress_db(db_path)   # reads .db.gz, (re)writes .db
    ...
    conn = sqlite3.connect(db_path)
    setup_database(conn)
    ...
...
if use_cache is True:
    close_db(conn, db_path)      # sorts, closes, gzips .db -> .db.gz, then os.remove(db_path)

close_db():

def close_db(conn, db_path):
    conn.execute("CREATE TABLE IF NOT EXISTS form4_data_sorted AS SELECT * FROM form4_data ORDER BY filing_date")
    conn.execute("DROP TABLE form4_data")
    conn.execute("ALTER TABLE form4_data_sorted RENAME TO form4_data")
    conn.commit()
    conn.close()
    compress_db(db_path)   # reads raw .db, writes .db.gz
    os.remove(db_path)     # deletes raw .db

This is a single shared file path with no locking around it at all. Two calls running concurrently -- e.g. download_data() invoked for two different symbols' insider-trading data at roughly the same time -- interleave in ways that corrupt the file: one call's os.remove(db_path) can delete the .db file another call still has an open sqlite3.connect() handle to; one call's compress_db() can read a .db file another call is mid-write on; a decompress_db() on one call's start can race a compress_db()/os.remove() on another call's end for the same .gz path. Once either file is left in a partial/inconsistent state, decompress_db()'s gzip.open() fails on read, or the SQLite file itself is left malformed -- and because conn is only assigned after the decompress step succeeds, a failure there hits the except Exception as e: handler's own close_db(conn, db_path) call with conn never having been assigned, raising UnboundLocalError: cannot access local variable 'conn' instead of the original error. Once the cache file is corrupted on disk, every subsequent call fails the same way, including fully sequential/non-concurrent ones, until the corrupted file is manually deleted.

Reproduction / evidence

Running insider-trading fetches for several symbols concurrently (e.g. via a thread pool, one obb.equity.ownership.insider_trading(symbol, provider="sec") call per thread) reliably corrupts the cache within a few runs. Confirmed directly on the resulting files:

$ python3 -c "
import gzip
with gzip.open('sec_form4.db.gz', 'rb') as f:
    f.read()
"
BadGzipFile: Not a gzipped file (b'\xffb')

$ python3 -c "
import sqlite3
conn = sqlite3.connect('sec_form4.db')
conn.execute('select count(*) from form4_data').fetchone()
"
sqlite3.DatabaseError: database disk image is malformed

Both files existing simultaneously (sec_form4.db and sec_form4.db.gz) is itself a sign of an interrupted lifecycle -- a healthy end state should only ever have the .gz, since close_db() always deletes the raw file after compressing it.

Suggested direction

Serialize the entire cache-file lifecycle (decompress-at-start through close/compress/delete-at-end) behind a lock shared across concurrent callers within a process, so only one caller ever has the file open/being rewritten at a time. A plain threading.Lock (not asyncio.Lock) is appropriate if concurrent callers may be running in separate threads/event loops rather than sharing one. The per-URL SEC fetches themselves (the asyncio.gather over chunks of the URL list) can stay concurrent as before; only the shared-file open/close boundary needs serializing. Separately, consider making download_data() resilient to an already-corrupted cache file (e.g. catching the decompress/connect failure and starting fresh rather than propagating an UnboundLocalError), since a corrupted cache currently blocks the fetcher entirely rather than degrading to an uncached fetch.

Happy to provide more detail if useful.