about summary refs log tree commit diff
diff options
context:
space:
mode:
authorFrederick Muriuki Muriithi <fredmanglis@gmail.com>2026-11-11 17:14:45 +0000
committerFrederick Muriuki Muriithi <fredmanglis@gmail.com>2026-11-11 13:12:20 -0600
commit1cb56e96f9d748ff7a18a5e845a246ff4ee53b41 (patch)
tree908fee52ef724c752dffe838d7ce8458aab5dc39
parentcf930d1335d894b468e8e0d8f600a50515ecee36 (diff)
downloadgn-libs-1cb56e96f9d748ff7a18a5e845a246ff4ee53b41.tar.gz
Index the jobs metadata by key and value
Add a `(metadata_key, metadata_value)` index to `jobs_metadata` so jobs can be
looked up by their metadata without scanning the table.

Co-authored-by: pi coding agent <pi@pi.dev>
-rw-r--r--gn_libs/jobs/migrations.py5
-rw-r--r--tests/unit/test_jobs.py37
2 files changed, 42 insertions, 0 deletions
diff --git a/gn_libs/jobs/migrations.py b/gn_libs/jobs/migrations.py
index 2af16ae..a30a3fd 100644
--- a/gn_libs/jobs/migrations.py
+++ b/gn_libs/jobs/migrations.py
@@ -32,6 +32,11 @@ def __create_table_jobs_metadata__(cursor: DbCursor):
         CREATE INDEX IF NOT EXISTS idx_tbl_jobs_metadata_cols_job_id
         ON jobs_metadata(job_id)
         """)
+    cursor.execute(
+        """
+        CREATE INDEX IF NOT EXISTS idx_tbl_jobs_metadata_cols_key_value
+        ON jobs_metadata(metadata_key, metadata_value)
+        """)
 
 
 def __create_table_jobs_output_streams__(cursor: DbCursor):
diff --git a/tests/unit/test_jobs.py b/tests/unit/test_jobs.py
new file mode 100644
index 0000000..c949dcf
--- /dev/null
+++ b/tests/unit/test_jobs.py
@@ -0,0 +1,37 @@
+"""Tests for background jobs storage helpers."""
+import time
+import uuid
+
+import pytest
+
+from gn_libs import sqlite3
+from gn_libs.jobs import jobs
+from gn_libs.jobs.migrations import run_migrations
+
+
+@pytest.fixture()
+def jobsdb(tmp_path):
+    """Create a migrated jobs database."""
+    db = tmp_path / "jobs.db"
+    run_migrations(str(db))
+    return db
+
+
+def _job(conn, job_type, **metadata):
+    """Create a job with the given type and metadata."""
+    return jobs.initialise_job(
+        conn, str(uuid.uuid4()), ["true"], job_type, extra_meta=metadata)
+
+
+@pytest.mark.unit_test
+def test_jobs_metadata_has_key_value_index(jobsdb):
+    """
+    GIVEN: a migrated jobs database
+    WHEN: the schema is inspected
+    THEN: the metadata key/value index exists
+    """
+    with sqlite3.connection(str(jobsdb)) as conn, sqlite3.cursor(conn) as cursor:
+        cursor.execute(
+            "SELECT name FROM sqlite_master WHERE type='index' "
+            "AND name='idx_tbl_jobs_metadata_cols_key_value'")
+        assert cursor.fetchone() is not None