-- ============================================================================
-- AI Software Factory — PostgreSQL bootstrap extensions
--
-- Mounted at /docker-entrypoint-initdb.d/01-extensions.sql by docker-compose
-- and executed ONCE, only when the pgdata volume is EMPTY (first bootstrap).
-- On a re-created volume (docker compose down -v) this runs again.
-- ============================================================================

-- citext: case-insensitive text columns (used for unique email addresses,
-- slugs, and other case-insensitive keys).
CREATE EXTENSION IF NOT EXISTS citext;

-- pg_trgm: trigram indexes for fast "contains"-style text search fallbacks
-- and similarity queries (used alongside OpenSearch, not instead of it).
CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- btree_gist: enables GiST indexes for range types and exclusion constraints
-- (useful for interval-style date-range properties in databases).
CREATE EXTENSION IF NOT EXISTS btree_gist;

-- ----------------------------------------------------------------------------
-- pgvector (vector) — intentionally NOT installed yet.
-- The AI/RAG phase (Phase 12, docs/tasks/phase-12-ai.md) will introduce
-- embeddings + vector similarity search. When that lands, add here:
--
--   CREATE EXTENSION IF NOT EXISTS vector;
--
-- ...and update the postgres image tag in docker-compose.yml to a build that
-- ships the extension, e.g. pgvector/pgvector:pg16-alpine (or install it in a
-- custom image). Until then the extension is deliberately absent so we do not
-- pin the image to an unneeded vendor build.
-- ----------------------------------------------------------------------------

-- Confirm what got installed (shows in `docker compose logs postgres`):
SELECT extname, extversion
FROM pg_extension
WHERE extname IN ('citext', 'pg_trgm', 'btree_gist');