Database Setup for Multi-Chat Feature#

Overview#

The EngiAI now uses a database (PostgreSQL or SQLite) to persist chat conversations. This allows you to:

Create multiple named conversations
Switch between conversations seamlessly
Automatically save all messages and agent state
Load previous conversations when you restart the app

Quick Start (SQLite - Default for Local Development)#

By default, the application uses SQLite for local development, which requires no setup. Just run the app and it will automatically create a local database file at data/conversations.db. (Docker deployments default to PostgreSQL — see Docker Deployment.)

streamlit run src/ui/streamlit_app.py

That’s it! Your conversations are automatically saved locally.

Using PostgreSQL (Optional)#

For production use or when you need to share conversations across multiple users, you can use PostgreSQL.

1. Install PostgreSQL#

macOS (using Homebrew):

brew install postgresql@17
brew services start postgresql@17

Ubuntu/Debian:

sudo apt update
sudo apt install postgresql postgresql-contrib
sudo systemctl start postgresql

Windows: Download and install from postgresql.org

2. Create Database#

# Connect to PostgreSQL
psql postgres

# Create database and user
CREATE DATABASE engineer_assistant;
CREATE USER engiai_user WITH PASSWORD 'your_password';
GRANT ALL PRIVILEGES ON DATABASE engineer_assistant TO engiai_user;

# Exit psql
\q

3. Configure Environment Variable#

Create or update your .env file:

# Copy example file
cp .env.example .env

# Edit .env and set:
DATABASE_URL=postgresql://engiai_user:your_password@localhost:5432/engineer_assistant

4. Install Dependencies#

pip install -e .

This will install psycopg2-binary and sqlalchemy from pyproject.toml.

5. Run the Application#

streamlit run src/ui/streamlit_app.py

The database tables will be created automatically on first run.

Database Schema#

The application creates four tables:

1. `conversations`#

Stores conversation metadata:

id (String/UUID): Primary key, session ID
name (String): Conversation name
created_at (DateTime): Creation timestamp
updated_at (DateTime): Last update timestamp
message_count (Integer): Number of messages
pinned (Boolean): Whether conversation is pinned to top
voice_id (String): Voice name (e.g., “George” for ElevenLabs, “alloy” for OpenAI)
voice_provider (String): Voice provider (“elevenlabs” or “openai”)

2. `messages`#

Stores individual messages:

id (Integer): Auto-increment primary key
conversation_id (String): Foreign key to conversations
role (String): ‘user’ or ‘assistant’
content (Text): Message content
images (JSON): List of image data (base64 encoded)
suggested_prompts (JSON): Follow-up prompt suggestions
audio (JSON): Audio attachment data
created_at (DateTime): Creation timestamp

3. `conversation_states`#

Stores LangGraph agent state:

conversation_id (String): Primary key, foreign key to conversations
agent_state (JSON): Agent messages and state
config (JSON): LangGraph configuration
waiting_for_confirmation (Integer): Boolean flag (0 or 1)
updated_at (DateTime): Last update timestamp

4. `settings`#

Stores user settings as key-value pairs:

id (Integer): Auto-increment primary key
key (String): Setting name (unique)
value (JSON): Setting value
updated_at (DateTime): Last update timestamp

How It Works#

Create Conversation: User enters a name in the sidebar and clicks “Create New Conversation”
- Generates a unique UUID as session_id
- Creates entry in conversations table
- Initializes empty state in conversation_states
Send Message: User types a message
- Message saved to messages table
- Agent processes and responds
- Response saved to messages table
- Agent state saved to conversation_states
- Conversation updated_at timestamp updated
Switch Conversation: User selects a different conversation
- Current conversation state saved to database
- Selected conversation loaded from database
- Messages displayed in chat interface
Load on Startup: When app starts
- All conversations loaded from database
- Most recently updated conversation becomes active
- Messages and state restored

Two things read `DATABASE_URL`#

DATABASE_URL configures two unrelated subsystems, which parse it separately and can end up in different states from the same value:

Chat history — the SQLAlchemy store above (src/ui/database.py). Works on both SQLite and PostgreSQL; this is what the tables on this page describe.
The LangGraph checkpointer (src/checkpoint.py) — PostgresSaver only when the URL starts with postgresql:// or postgres://. Every other value, SQLite included, gets MemorySaver, which is in-process and does not survive a restart. There is no SQLite saver.

What that actually costs on SQLite#

Less than it sounds, but it is not nothing.

Not lost: conversation history. Messages and agent state are written to the messages and conversation_states tables and replayed into the graph when a chat is reopened, so “SQLite is fine for local development” remains true for everyday use.

Lost: a pending human-in-the-loop interrupt. The graphs compile with interrupt_before=["cli_agent"] and interrupt_before=["tool_node"], and that paused position lives only in the checkpointer. Restart the app while a confirmation prompt is open and the graph resumes with nothing pending — the awaiting tool call is dropped rather than re-offered.

Warning

PostgreSQL does not guarantee a durable checkpointer either. If building PostgresSaver raises for any reason — connection refused, bad credentials, a failed setup() — src/checkpoint.py catches it and falls back to MemorySaver. The only signal is a WARNING log line; the app starts normally. If durable checkpoints matter, check the startup log rather than assuming the URL was honoured.

Environment Variables#

# SQLite (default - no setup required)
DATABASE_URL=sqlite:///data/conversations.db

# PostgreSQL (local)
DATABASE_URL=postgresql://username:password@localhost:5432/database_name

# PostgreSQL (remote - e.g., Heroku, AWS RDS)
DATABASE_URL=postgresql://username:password@host:port/database_name

# PostgreSQL (with SSL)
DATABASE_URL=postgresql://username:password@host:port/database_name?sslmode=require

Migrating from Pickle Files#

If you were using the previous version with pickle files (data/chats/*.pkl), those files are no longer used. To migrate:

Option 1: Start fresh (conversations will be empty)
- Just start using the new version
- Old pickle files can be deleted
Option 2: Manual migration (if you need old conversations)
- This requires writing a custom migration script
- Contact support or check for migration tools in the repository

Backup and Recovery#

SQLite Backup#

# Backup
cp data/conversations.db data/conversations.backup.db

# Restore
cp data/conversations.backup.db data/conversations.db

PostgreSQL Backup#

# Backup
pg_dump -U engiai_user engineer_assistant > backup.sql

# Restore
psql -U engiai_user engineer_assistant < backup.sql

Troubleshooting#

“No module named ‘psycopg2’”#

pip install psycopg2-binary

“could not connect to server”#

Check that PostgreSQL is running:

# macOS
brew services list

# Linux
sudo systemctl status postgresql

# Check if port is open
psql -U postgres -h localhost -p 5432

“relation does not exist”#

Tables are created automatically. If you get this error:

Ensure the database user has CREATE privileges
Try manually creating tables by running the app once
Check the database URL is correct

Performance Issues#

For SQLite:

Consider using PostgreSQL for production
SQLite is great for development but may be slower with many conversations

For PostgreSQL:

Add indexes if needed (not required for typical usage)
Consider connection pooling for high traffic

Production Deployment#

Using Heroku#

Add Heroku Postgres addon
Heroku automatically sets DATABASE_URL
Deploy your app

Using Railway/Render#

Create PostgreSQL database
Copy connection string to DATABASE_URL
Deploy app

Using Docker#

# In Dockerfile
ENV DATABASE_URL=postgresql://user:pass@db:5432/engineer_assistant

# In docker-compose.yml
services:
  db:
    image: postgres:17
    environment:
      POSTGRES_DB: engineer_assistant
      POSTGRES_USER: engiai_user
      POSTGRES_PASSWORD: your_password

Upgrading the PostgreSQL major version#

A major bump (17 → 18, say) cannot reuse the existing data directory. Postgres refuses to start against files written by a different major and says so:

FATAL: database files are incompatible with server

Either run pg_upgrade, or — if the conversation history is expendable — wipe the volume and let the new server initialise a fresh one:

./docker-compose-wrapper.sh down
docker volume rm engiai_postgres_data
./docker-compose-wrapper.sh up -d postgres

The demo stack keeps its own volume and fails the same way. If you have ever run it, wipe that one too:

docker compose -f docker-compose.demo.yml down
docker volume rm engiai_demo_data

The schema is recreated automatically on first connection: DatabaseManager calls Base.metadata.create_all(), and the LangGraph checkpointer runs its own setup(). There is no migration step to run.

Warning

Going to 18 also means changing the mount path. The official image moved PGDATA from /var/lib/postgresql/data to /var/lib/postgresql/18/docker, and its declared volume from the former to /var/lib/postgresql. Bump the tag without moving the mount and Postgres initialises outside the volume — the database then lives in the container’s writable layer and disappears on the next docker compose up --force-recreate, with no error at any point.

Security Notes#

⚠️ Important Security Considerations:

Never commit .env file - It contains sensitive credentials
Use strong passwords for PostgreSQL
Use SSL/TLS for remote PostgreSQL connections
Restrict database access - Only allow connections from your app server
Regular backups - Schedule automated backups of your database
Environment-specific configs - Use different databases for dev/staging/prod

Support#

For issues or questions:

Check the troubleshooting section above
Review the database schema section above
Open an issue on GitHub with error logs

Database Setup for Multi-Chat Feature

Contents

Database Setup for Multi-Chat Feature#

Overview#

Quick Start (SQLite - Default for Local Development)#

Using PostgreSQL (Optional)#

1. Install PostgreSQL#

2. Create Database#

3. Configure Environment Variable#

4. Install Dependencies#

5. Run the Application#

Database Schema#

1. conversations#

2. messages#

3. conversation_states#

4. settings#

How It Works#

Two things read DATABASE_URL#

What that actually costs on SQLite#

Environment Variables#

Migrating from Pickle Files#

Backup and Recovery#

SQLite Backup#

PostgreSQL Backup#

Troubleshooting#

“No module named ‘psycopg2’”#

“could not connect to server”#

“relation does not exist”#

Performance Issues#

Production Deployment#

Using Heroku#

Using Railway/Render#

Using Docker#

Upgrading the PostgreSQL major version#

Security Notes#

Support#

1. `conversations`#

2. `messages`#

3. `conversation_states`#

4. `settings`#

Two things read `DATABASE_URL`#