Remove inline ALTER TABLE from postgres_search_repository

[phernandez]

Problem

PR #723 added inline ALTER TABLE statements in postgres_search_repository.py (lines ~324-360) that run at runtime during every vector sync initialization. These add entity_fingerprint and embedding_model columns to search_vector_chunks.

This causes deadlocks when two concurrent index jobs hit the same database — both try to ALTER TABLE simultaneously and Postgres detects the deadlock.

The alembic migration for these columns already exists: m6h7i8j9k0l1_add_vector_sync_fingerprints.py

Fix

Delete the inline DDL from postgres_search_repository.py. The alembic migration handles schema changes. Runtime code should never run ALTER TABLE.

File

src/basic_memory/repository/postgres_search_repository.py — remove the ALTER TABLE / ADD COLUMN IF NOT EXISTS block

[claude]

Claude finished @phernandez's task in 1m 25s —— View job • claude/issue-727-20260408-0443 • Create PR ➔

---

Triage & Fix: Remove inline ALTER TABLE from postgres_search_repository

Type: Bug | Priority: High | Component: Database | Complexity: Simple

• Analyze issue
• Read postgres_search_repository.py to identify the DDL block
• Remove inline ALTER TABLE statements (lines 322–361)
• Verify alembic migration m6h7i8j9k0l1_add_vector_sync_fingerprints.py covers the same DDL
• Apply bug label
• Commit and push changes

What was done

Removed the 5-statement inline DDL block from _ensure_vector_tables() in postgres_search_repository.py:

• ALTER TABLE search_vector_chunks ADD COLUMN IF NOT EXISTS entity_fingerprint TEXT
• ALTER TABLE search_vector_chunks ADD COLUMN IF NOT EXISTS embedding_model TEXT
• UPDATE search_vector_chunks SET entity_fingerprint = COALESCE(...), embedding_model = COALESCE(...)
• ALTER TABLE search_vector_chunks ALTER COLUMN entity_fingerprint SET NOT NULL
• ALTER TABLE search_vector_chunks ALTER COLUMN embedding_model SET NOT NULL

These are exactly what the alembic migration m6h7i8j9k0l1 already runs at deploy time. Running them again at runtime on every vector sync initialization was the source of the deadlock — two concurrent jobs would both try to acquire the AccessExclusiveLock on search_vector_chunks at the same time.

The CREATE TABLE IF NOT EXISTS search_vector_chunks block was left intact (it already defines both columns), so fresh installations remain unaffected.

|