Hacktoberfest 2026: the issues maintainers tagged for October, open and beginner-friendly. Browse Hacktoberfest issues

[Architecture] Consolidate data access: raw SQL scattered across analyzer/jev/session, ad-hoc schema migrations in 4 modules

Open
  • Is something wrong?
#712 2 comments 0 reactions 0 assignees View on GitHub

Maintainers usually reply within 1 day

Nobody has claimed this yet.

Assessment

Difficulty
5/5
Estimated time
Over a week
Newbie friendliness
25/100
Issue type
Refactor
Clarity
Clearly specified
Activity status
Active
Tech stack
python, sqlite
Domain
backend, database

Research direction

Start by reading app/core/db.py, app/core/db_conn.py, and the named analyzer, history, and ledger modules to map existing queries, migrations, and write paths. Then inspect validate_architecture.py and docs/architecture.md for the proposed boundaries. Done means each DB file has one versioned migration list, the historical query appears once, and get_db_connection calls outside storage are limited to db_conn.py.

Written by the indexing model from the issue text.

Description

architecture enhancement ready-for-agent

Problem

app/core/db.py (Database, 2,228 lines) is meant to own the main DB, but other modules reach around it using db.db_path and get_db_connection directly.

Raw SQL outside the repository class

  • app/core/analyzer.py:260-272 and app/core/analyzer_strategies.py:2259-2270 run the same documents JOIN document_vectors query, copy-pasted. More raw access in analyzer.py:1189 and analyzer_strategies.py:2741, 2885.
  • app/core/jev_classifier.py:381-395 creates its own table jev_classification_cache inside whichever DB path it's given, and reads/writes it at 432, 460, 484 and 544.
  • app/core/session.py:64-90 and 417-422 query history.db directly. The first attempt uses stdlib sqlite3.
  • app/main.py:581 queries sessions directly in find_all_history_sessions.

Schema and migrations are ad hoc and per-module

  • db.py:59-178 uses PRAGMA user_version plus ALTER TABLE.
  • history.py:37-160 runs about 12 unconditional ALTER TABLE ... ADD COLUMN statements wrapped in try/except (history.py:70-126).
  • cache.py:317-327 and ledger.py:96-140 have their own CREATE TABLE and ALTER.
  • There is no single schema version per database file and no migration list.

Write serialization is only partly applied

  • Database routes writes through DBWorker.execute_write (e.g. db.py:547, 715, 740).
  • history.py has 36 INSERT/UPDATE/DELETE statements that mostly run on whatever thread calls them (e.g. log_step, history.py:812).
  • ledger.py writes directly.
  • Multiple DBWorker instances exist (see the composition-root issue).
  • get_db_connection keeps a global per-(path, thread) cache bounded at 50 with LRU eviction that closes evicted connections (db_conn.py:72-80, 139-150), regardless of whether the owning thread is still using them.

Impact

  • Schema knowledge is spread across 7+ modules, so a column rename means grepping SQL strings.
  • Duplicated queries drift. For example, the two copies already differ in LEFT JOIN vs JOIN and in selected columns.
  • There's no way to reason about which thread writes which DB.

Proposed approach

  • Introduce per-database repository modules: app/core/storage/main_db.py (documents, vectors, tf-idf, quarantine, jev cache), history_db.py (sessions, snapshots, step ledger) and ledger_db.py. Each owns its DDL and exposes typed methods. Database becomes a facade over main_db.
  • Add one migration runner: an ordered list of (version, callable) per DB file, keyed off PRAGMA user_version. Replace the try/except ALTER chains.
  • Move the analyzer/strategy historical-corpus query into a single repository method, for example get_verified_documents_with_vectors(base_dir).
  • Define the concurrency contract in one place: either one writer per DB file (shared DBWorker per path) or WAL plus short-lived connections. Document it in docs/architecture.md and extend validate_architecture.py to flag get_db_connection( outside app/core/storage/.

Acceptance criteria

  • grep -rn "get_db_connection(" app matches only files under the storage layer and db_conn.py.
  • Each DB file has exactly one migration list with monotonically increasing user_version. A test opens a DB at version 0 and migrates it to head.
  • The duplicated historical-corpus query exists once.
Dominant language
Python
Stars
1
Forks
0
Avg merge
9h 3m
Merged PRs (30d)
361

Getting set up

  • No Dockerfile or Docker Compose file
  • Has a pull request template
  • No contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

More from fderuiter/sortify

All issues in fderuiter/sortify

Similar issues

More Python issues

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.