fix: database lock issues fixed using litequeue

[Davda-James]

Proposed change

• Queue-based Architecture: Database operations are enqueued and processed by a separate writer thread
• Persistent Storage: Uses litequeue for disk-backed job persistence across process restarts
• Fallback Support: Falls back to in-memory queues and synchronous writes when litequeue is unavailable
• WAL Mode: Enabled SQLite WAL mode for improved concurrent access
• CLI Worker: Added standalone database worker process for production deployments

Closes issue: #609

Type of change

Feat + Fix

• [x] New or existing module/payload change
• [x] Dependency upgrade
• [x] Written test for new change

Files Changed

writer.py
db.py
db_worker.py
pyproject.toml
test_writer.py

Note

pyproject.toml has been updated with litequeue (^0.9), hence poetry.lock

Checklist

• [x] I've followed the [contributing guidelines][contributing-guidelines]
• [x] I've run make pre-commit, it didn't generate any changes
• [x] I've run make test, all tests passed locally

[coderabbitai[bot]]

Summary by CodeRabbit

• New Features

  • Added a background database writer for faster, non-blocking data persistence with batching and graceful shutdown.
  • Introduced a CLI command to drain or monitor the writer queue, with options for one-time drain, batch size, interval, max items, and summary output.

• Tests

  • Added end-to-end tests verifying queued writes are persisted correctly.

• Chores

  • Registered the new CLI entry point in the package scripts.
  • Added a new dependency to support the queue backend.

Walkthrough

Adds a DBWriter batching subsystem and CLI worker, integrates writer enqueueing into three DB submit functions with direct-write fallback, registers a CLI script and litequeue dependency, and adds end-to-end tests validating persisted report and host-log writes.

Changes

Cohort / File(s)	Summary of changes
Writer subsystem & CLI nettacker/database/writer.py, nettacker/cli/db_worker.py	New DBWriter class and helpers (get_writer, get_writer_configured, get_stats) implementing batched, background writes using litequeue or multiprocessing Queue; enqueue API, start/stop, drain_once, stats, and a CLI (db_worker) with args --once, --batch-size, --interval, --max-items, --summary and signal handling.
DB integration nettacker/database/db.py	submit_report_to_db, submit_logs_to_db, and submit_temp_logs_to_db now try to enqueue jobs to the writer and, on enqueue failure/exception, fall back to the existing direct SQLAlchemy write path.
Project config pyproject.toml	Adds nettacker-db-worker script entry pointing to nettacker.cli.db_worker:run and adds dependency litequeue = "^0.9".
Tests tests/database/test_writer.py	New end-to-end test that configures a DBWriter against a temporary SQLite DB, enqueues insert_report and insert_hostslog jobs, drains the writer, and asserts persisted rows in reports and scan_events tables.

Estimated code review effort

🎯 3 (Moderate) | ⏱️ ~25 minutes

Pre-merge checks and finishing touches

❌ Failed checks (1 warning)

Check name	Status	Explanation	Resolution
Docstring Coverage	⚠️ Warning	Docstring coverage is 25.00% which is insufficient. The required threshold is 80.00%.	You can run @coderabbitai generate docstrings to improve docstring coverage.

✅ Passed checks (2 passed)

Check name	Status	Explanation
Title Check	✅ Passed	The title clearly summarizes the primary change by stating that database lock issues are addressed using litequeue, which directly reflects the new queue-based writer architecture introduced in this PR.
Description Check	✅ Passed	The description thoroughly outlines the new queue-based architecture, litequeue integration, fallback strategy, WAL mode, CLI worker, and the linked issue, all of which align with the actual changes in the PR.

✨ Finishing touches

• [ ] 📝 Generate docstrings

🧪 Generate unit tests (beta)

• [ ] Create PR with unit tests
• [ ] Post copyable unit tests in a comment

---

Thanks for using CodeRabbit! It's free for OSS, and your support helps us grow. If you like it, consider giving us a shout-out.

❤️ Share

• X
• Mastodon
• Reddit
• LinkedIn

_{Comment @coderabbitai help to get the list of available commands and usage tips.}