fix(database): unify PostgreSQL selection with legacy indexing connections #45

Activity

1. spelech commented on Oct 27, 2026

   

   spelech

   on Oct 27, 2026

   OwnerAuthor

   More actions

   Reconfirmed in an isolated full-PostgreSQL deployment (2026-10-27)

   Tested source revision 41ec093a745f1c0277893dc8f758b96b216c970f in a fresh Docker Compose project with separate application/PostgreSQL volumes, PostgreSQL 16 plus pgvector 0.8.7, DATABASE_URL selecting PostgreSQL, VECTOR_STORE_PROVIDER=pgvector, and AUTH_ENABLED=true. No existing SQLite schema was copied or initialized as a workaround.

   What worked

   • PostgreSQL initialized the application schema.
   • pgvector initialized its document table and HNSW index.
   • /admin/api/vector-store reported the selected pgvector backend healthy.
   • PostgreSQL-backed admin-key bootstrap, viewer-key issuance, viewer denial on admin-key management, revocation, and rejection of invalid/revoked keys worked.

   Confirmed blocker

   Authenticated GET /admin/api/repos returned HTTP 500:

   {"error":"Failed to fetch repositories."}

   Application diagnostics:

   Error fetching repos: no such table: git_repositories
   Error during full indexing: no such table: indexed_paths
   Error in poller worker cycle: no such table: git_repositories
   Failed to list git host credentials: no such table: git_host_credentials
   

   An unrelated cache SQLite file was created with zero tables, while the PostgreSQL schema existed. This reproduces the selected-database/legacy-connection split described in this issue.

   Root-cause location: get_db_connection() always creates a sqlite3 connection.

   Additional observations: /health continued returning healthy, /admin/api/stats returned HTTP 200 with zero metadata counts, and /admin/api/settings/hosts returned HTTP 200 with [] despite these failures. Those error-reporting behaviors are being tracked separately rather than duplicating this database-migration issue.

   Full PostgreSQL was not marked working. End-to-end PostgreSQL PDF ingestion/OCR and the remaining editor-role checks were deferred behind this blocker. No source changes or SQLite fallback were applied.

   Separate follow-up issues: #50 (readiness), #49 (stats query failure reporting), and #48 (host-credential query failure reporting).
2. spelech commented on Oct 27, 2026

   

   spelech

   on Oct 27, 2026

   OwnerAuthor

   More actions

   Follow-up configuration sweep: direct pgvector ingestion and semantic retrieval succeed independently of the metadata migration, but collection scoping is broken (#54) and unsupported lexical/hybrid controls are silently ignored (#56). Additional isolated auth/PDF and embedding lifecycle findings are tracked in #51, #52, #53, #55, #57, #58, #59, and #60. Full PostgreSQL application operation remains blocked by this issue; no SQLite fallback or source fix was applied.
3. spelech commented on Oct 27, 2026

   

   spelech

   on Oct 27, 2026

   OwnerAuthor

   More actions

   Triage & Implementation Brief

   1. Scope & Effort

   • Effort: Large (Pervasive relational query abstraction)
   • Component: Backend Database / PostgreSQL & SQLite Engine

   2. Root Cause & Context

   The application configures PostgreSQL via SQLAlchemy in app/services/database/session.py, but legacy indexing, repo, credential, and AST routes query SQLite directly via app/services/database/connection.py::get_db_connection(). Consequently, when DATABASE_URL specifies PostgreSQL, repositories and indexed files target an empty SQLite database.

   3. Implementation Plan

   • Refactor get_db_connection() to inspect the active configuration/DATABASE_URL:
     • When PostgreSQL is configured, provide a connection from a connection pool (via psycopg or SQLAlchemy raw engine connection).
     • When SQLite is configured, maintain the existing SQLite file connection.
   • Parameterize SQL queries across app/services/database/*.py (abstracting SQLite ? vs PostgreSQL %s placeholders, AUTOINCREMENT vs SERIAL, and dialect differences).
   • Ensure database migrations initialize both PostgreSQL schemas (with pgvector extensions) and SQLite schemas cleanly.

   4. Acceptance Criteria

   • Setting DATABASE_URL=postgresql://... ensures all repository registrations, file indexing, credentials, and AST relationships write to and query PostgreSQL.
   • No fallback SQLite database is created or queried when PostgreSQL is active.
   • Existing SQLite standalone operation remains fully functional without regressions.
4. spelech

   added

   julesgoogle-jules-botgoogle-jules-bot

   on Oct 27, 2026
5. google-labs-jules commented on Oct 27, 2026

   

   google-labs-julesBot

   on Oct 27, 2026 – with Google Labs Jules

   Contributor

   More actions

   Jules is on it. When finished, you will see another comment and be able to review a PR.
6. google-labs-jules commented on Oct 27, 2026

   

   google-labs-julesBot

   on Oct 27, 2026 – with Google Labs Jules

   Contributor

   More actions

   Ready for a review! A PR has been created.
7. spelech

   closed this as completedin #72on Oct 28, 2026