POSTGRES_SCHEMA is ignored by the asyncpg pool: startup crashes and the JSONB migration is skipped #299

Summary

When POSTGRES_SCHEMA is set (the schema-isolation feature added in #289), the app fails to start and the cmetadata JSON→JSONB migration is silently skipped.

Root cause

POSTGRES_SCHEMA is applied only to the SQLAlchemy engine, via connect_args={"options": "-csearch_path=<schema>,public"} in get_vector_store — so pgvector's tables (langchain_pg_embedding, …) are created in the custom schema.

But the asyncpg pool in app/services/database.py is built from the bare DSN:

cls.pool = await asyncpg.create_pool(dsn=DSN)

so it runs with the default search_path (public). At startup ensure_vector_indexes() runs over this pool with unqualified table names plus a current_schema()-guarded JSON→JSONB migration:

CREATE INDEX IF NOT EXISTS idx_langchain_pg_embedding_custom_id ON langchain_pg_embedding (custom_id);
...
WHERE table_name = 'langchain_pg_embedding' AND table_schema = current_schema() ...

Because the table lives in <schema> but the pool looks in public:

• the unqualified CREATE INDEX raises UndefinedTableError: relation "langchain_pg_embedding" does not exist → the lifespan startup fails and the container never becomes healthy; or, if a stale langchain_pg_embedding happens to exist in public, the indexes/migration silently hit the wrong table;
• the migration guard table_schema = current_schema() is always public ≠ <schema>, so the JSON→JSONB migration (and the GIN jsonb_path_ops index it enables) never runs against the real table.

Reproduction

Same asyncpg pool, a throwaway schema, an unqualified CREATE INDEX:

BARE  current_schema(): public
BARE  unqualified CREATE INDEX -> FAILED: UndefinedTableError | relation "t" does not exist
BARE  migration-guard matches (table in current_schema): False
FIXED current_schema(): repro_schema
FIXED unqualified CREATE INDEX -> SUCCEEDED
FIXED migration-guard matches (table in current_schema): True

(FIXED = pool created with server_settings={"search_path": "repro_schema,public"}.)

Impact

POSTGRES_SCHEMA deployments can't start; even where a coincidental public table exists, the performance indexes and JSONB migration don't apply to the real data. Default deployments (POSTGRES_SCHEMA unset) are unaffected.

Fix

Give the asyncpg pool the same search_path as the SQLAlchemy engine via asyncpg server_settings. PR to follow.