Vaessl/docs/02-Preparation/02-Spring-Boot-and-database-setup.md

**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<Test> {
	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 offical ready-made pgvector docker image but if you already host a PostGreSQL database you need to add the extension yourself.

Check the name of your PostGreSQL container:
```
docker ps
```

Enter your container via bash:

```
docker exec -it 876fb382969f bash
```
Before working on your database backup your databases:
```
su - postgres -c "pg_dumpall > /tmp/backup200526.sql"

#exit the container and copy the backup file to local file system 
docker cp 876fb382969f:/tmp/backup200526.sql .
```

Install dependencies, build and install pgvector:
apt-get update
apt-get install -y build-essential git postgresql-server-dev-all
```
git clone https://github.com/pgvector/pgvector.git
cd pgvector
make
make install
docker restart 876fb382969f 
```
Enter PostGreSQL container and create pgvector extension for each databse:
```
docker exec -it <container-name> psql -h localhost -U <db-user> -d <db-name>

CREATE EXTENSION vector;
```

# 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