**Vaessl: Spring Boot and database setup** This app will use the current latest version 4.0.4 of Spring Boot and the latest OpenJDK 25 LTS. The dependencies are chosen to specifically work with Spring AI and PostgreSQL/pgvector. For the AI function OpenAI is chosen since it is known to work with LiteLLM. The PostgreSQL dependency makes sure to include PostGreSQL support for self hosted databases. # Spring Initializr settings **Build System: Gradle - Kotlin** Gradel Kotlin is chosen to make mobile/Android app development easier. **Spring Boot: 4.0.4** **Packaging: Jar** **Configuration: YAML** **Java: 25** **Dependencies** - Lombok - Spring Boot DevTools - Spring Web - Spring Security - Spring Data JPA - OpenAI AI - PostgreSQL Driver - Validation # Project Settings PostGreSQL and OpenAI need an initial setup so that the local instance is able to start. I will comment out Spring Security since user management is an issue for a later iteration of the app. The build.gradle.kts will look something like this: ``` plugins { java id("org.springframework.boot") version "4.0.4" id("io.spring.dependency-management") version "1.1.7" } group = "com.vaessl" version = "0.0.1-SNAPSHOT" java { toolchain { languageVersion = JavaLanguageVersion.of(25) } } configurations { compileOnly { extendsFrom(configurations.annotationProcessor.get()) } } repositories { mavenCentral() } extra["springAiVersion"] = "2.0.0-M3" dependencies { implementation("org.springframework.boot:spring-boot-starter-data-jpa") // implementation("org.springframework.boot:spring-boot-starter-security") implementation("org.springframework.boot:spring-boot-starter-validation") implementation("org.springframework.boot:spring-boot-starter-webmvc") implementation("org.springframework.ai:spring-ai-starter-model-openai") compileOnly("org.projectlombok:lombok") developmentOnly("org.springframework.boot:spring-boot-devtools") runtimeOnly("org.postgresql:postgresql") annotationProcessor("org.projectlombok:lombok") testImplementation("org.springframework.boot:spring-boot-starter-data-jpa-test") // testImplementation("org.springframework.boot:spring-boot-starter-security-test") testImplementation("org.springframework.boot:spring-boot-starter-validation-test") testImplementation("org.springframework.boot:spring-boot-starter-webmvc-test") testRuntimeOnly("org.junit.platform:junit-platform-launcher") testImplementation("org.springframework.boot:spring-boot-starter-test") } dependencyManagement { imports { mavenBom("org.springframework.ai:spring-ai-bom:${property("springAiVersion")}") } } tasks.withType { useJUnitPlatform() } ``` To configure OpenAI (which I will use instead of LiteLLM initially since I have an OpenAI Api key and can get going quickly) and PostGreSQL I will create a .env.local file in the root dir, fill all credentials and add it to .gitignore ``` DB_URL=jdbc:postgresql://192.168.1.208:5433/vaessl DB_TEST_URL=jdbc:postgresql://192.168.1.208:5434/vaessl_test DB_USERNAME=myusername DB_PASSWORD=mypw OPENAI_KEY=myapikey OPENAI_BASE_URL=https://api.openai.com PG_DRIVER_CLASS_NAME=org.postgresql.Driver ``` The initial application.yaml in the resources folder will look like this: ``` spring: application: name: vaessl config: import: "optional:file:.env.local[.properties]" datasource: url : ${DB_URL} username: ${DB_USERNAME} password: ${DB_PASSWORD} driver-class-name: ${PG_DRIVER_CLASS_NAME} jpa: hibernate: ddl-auto: update show-sql: true ai: openai: base-url: ${OPENAI_BASE_URL} api-key: ${OPENAI_KEY} chat: options: model: gpt-4o-mini logging: level: org.springframework.boot.context.config: TRACE ``` The Docker Compose file for code-server will look something like this: ``` --- services: code-server: image: code-server-dev:latest container_name: code-server environment: - PUID=1000 - PGID=1000 - TZ=Europe/Vienna # - PASSWORD= #optional - HASHED_PASSWORD=hashedpw # - SUDO_PASSWORD_HASH= #optional - PROXY_DOMAIN=code-server.your.website - DEFAULT_WORKSPACE=/config/workspace volumes: - /home/pi/docker/vscode:/config - /home/pi/docker/vscode/workspace:/workspace ports: - 8443:8443 - 8124:8080 - 5173:5173 restart: unless-stopped ``` Note that I'm using my own locally hosted PostgreSQL instances for the main and test database. Just add databases via SQL or PgAdmin and install the pgvector extension to each database manually. There is an official ready-made pgvector docker image but if you already host a PostgreSQL database you need to add the extension yourself. ## Installing pgvector on a self-hosted PostgreSQL container pgvector is a PostgreSQL extension that adds a `vector` data type. It does not create a new database — it adds a `vector_store` table to your existing database. The shared library (`vector.so`) must be installed on the PostgreSQL server itself. **Do not install it manually inside a running container.** Manual installations do not survive container recreation (e.g. after a `docker compose up --force-recreate` or image update). Instead, bake it into a custom Docker image. ### Step 1: Create a custom Dockerfile for PostgreSQL Create a `Dockerfile.postgres` next to your `docker-compose.yaml`: ```dockerfile FROM postgres:18.4 RUN apt-get update && apt-get install -y --no-install-recommends \ ca-certificates git build-essential postgresql-server-dev-18 \ && git clone --branch v0.8.3 https://github.com/pgvector/pgvector.git \ && cd pgvector && make && make install \ && cd .. && rm -rf pgvector \ && apt-get purge -y ca-certificates git build-essential postgresql-server-dev-18 \ && apt-get autoremove -y \ && rm -rf /var/lib/apt/lists/* ``` - `FROM postgres:18.4` — this is still the standard postgres image, not the pgvector image - `ca-certificates` — required for git to verify GitHub's SSL certificate during build - `postgresql-server-dev-18` — provides the PostgreSQL header files needed to compile pgvector ### Step 2: Update docker-compose.yaml to use the custom image ```yaml services: db: container_name: postgres labels: - "com.centurylinklabs.watchtower.enable=false" build: context: . dockerfile: Dockerfile.postgres network: host restart: always environment: POSTGRES_USER: ${DB_USER} POSTGRES_PASSWORD: ${DB_PASSWORD} POSTGRES_DB: ${DB_NAME} networks: - pg_network volumes: - /home/pi/docker/postgresql:/var/lib/postgresql ports: - "5432:5432" networks: pg_network: external: true ``` ### Step 3: Build the image and recreate the container Before recreating, back up your databases: ```bash docker exec -it bash su - postgres -c "pg_dumpall > /tmp/backup.sql" exit docker cp :/tmp/backup.sql . ``` Build the image. Use `--network=host` to ensure the build container can reach GitHub: ```bash docker build --network=host -t postgres-pgvector -f Dockerfile.postgres . ``` Recreate the container ```bash docker compose up -d --force-recreate ``` ### Step 4: Enable the extensions in each database Run this once per database that needs pgvector: ```bash docker exec -it psql -U -d ``` ```sql CREATE EXTENSION IF NOT EXISTS vector; CREATE EXTENSION IF NOT EXISTS "uuid-ossp"; ``` `uuid-ossp` is also required — Spring AI's `vector_store` table uses `uuid_generate_v4()` for its primary key. ### Step 5: Add pgvector config to application.yaml Spring AI's pgvector auto-configuration requires these properties: ```yaml spring: ai: vectorstore: pgvector: dimensions: 1536 # must match your embedding model output size distance-type: COSINE_DISTANCE index-type: HNSW initialize-schema: true # auto-creates the vector_store table on startup ``` `dimensions` depends on the embedding model: - `text-embedding-ada-002` / `text-embedding-3-small` → 1536 - `text-embedding-3-large` → 3072 `initialize-schema: true` means Spring AI will create the `vector_store` table automatically on first startup — no manual SQL needed. # Appendix: Additional config for developing in Code-Server When using the code-server container there are additional config steps to mind: - Assign the 8080 port to a different port if it is used by another docker container by adding a port variable in the docker-compose.yaml. I assigned it to 8124. ``` ports: - 8443:8443 - 8124:8080 ``` - define a proxy domain for automatic Url generations when starting localhost ``` environment: - PROXY_DOMAIN=code-server.your.website ``` This makes sure port 8080 is reachable via https://8080.code-server.your.website as per code-server documentation for using subdomains: https://coder.com/docs/code-server/guide#using-a-subdomain - make sure to add the subdomain in your proxy platform like Cloudflare Zero Trust Tunnel or Pangolin and point it to the local ip in my case http://192.168.208:8124