How the Database Init Works

This directory contains initialization logic for the PostgreSQL container in our Docker Compose setup.

🧠 Why this exists

We want to:

Keep our database secure, by not using the postgres superuser for our backend app.
Automatically create a limited-privilege database user (app_user) on first-time startup of the container.

Allow reusability and configurability by using values from the .env file.

✅ Compose reads the .env file

Docker Compose loads environment variables defined in .env file. This includes credentials like:
```
DB_NAME=mydb
DB_APP_USER=app_user
DB_APP_PASSWORD=app_db_secret_password
```
🗑️ We mount a shell script into the init folder

In docker-compose.yml:
```
services:
db:
    volumes:
    - ./db/init.sh:/docker-entrypoint-initdb.d/init.template.sql
```
- init.template.sql is marked executable (chmod +x).
- It uses envsubst to substitute environment variables into init.sql.
- It generates a final SQL script init.sql with the real credentials.
🐘 PostgreSQL runs any init scripts found in /docker-entrypoint-initdb.d/

This happens only the first time the container is created — if the volume is already initialized, this is skipped.
💥 PostgreSQL executes init.sql

This final SQL script does the following:
- Creates the app_user with a password.
- Grants it basic privileges on the database and public schema.
- Ensures that future tables and sequences will also be accessible to this user.

File	Purpose
`init.sh`	Shell script run during container setup to process env variables into SQL
`init.template.sql`	SQL template file with `${VARIABLES}` placeholders
`init.sql`	Final SQL generated by the script and run by Postgres
`.env`	Holds DB credentials like `DB_APP_USER`, `DB_APP_PASSWORD`, etc. (not committed)
`.env.example`	Template for .env, committed for team use

init.sql is auto-generated — don't commit it to git. Ignored by Docker in .dockerignore.
To re-run the script, you'd have to delete the volume:
```
docker-compose down -v
```

Backend app (FastAPI) connects to DB using the limited app_user, not the postgres admin user:

SQLALCHEMY_DATABASE_URL=postgresql+psycopg2://${DB_APP_USER}:${DB_APP_PASSWORD}@{DB_HOST}:{DB_PORT}/{DB_NAME}

This ensures the app cannot drop tables or alter roles — only perform what it's allowed to.