PostgreSQL Database for Stok AI Spine

Containerized PostgreSQL database for the Stok AI knowledge base.

📦 What's Included

• PostgreSQL 15 Alpine - Lightweight, production-ready
• init.sql - Complete schema for spine database
• seed-data.sql - Optional demo data
• migrate-sqlite-to-postgres.py - Migration script from SQLite
• Health checks - Auto-monitoring

🗄️ Database Schema

Organizations

• org_id, name, context, language

Workstreams (Macro-level planning)

• workstream_id, org_id, title, status, priority, dates, tags

Meetings

• meeting_id, org_id, title, timestamps, participants

Facts (Knowledge from meetings)

• fact_id, org_id, meeting_id, fact_type, payload, confidence

Linking Tables

• meeting_workstream_links
• fact_workstream_links

Supporting Tables

• agenda_proposals
• transcripts

🚀 Local Testing

# Start PostgreSQL locally
docker build -t stok-postgres ./database
docker run -d \
  --name stok-postgres \
  -e POSTGRES_PASSWORD=mysecretpassword \
  -p 5432:5432 \
  stok-postgres

# Connect to database
docker exec -it stok-postgres psql -U adminuser -d spine_db

# Test connection
psql postgresql://adminuser:mysecretpassword@localhost:5432/spine_db

🔐 Environment Variables

Variable	Default	Description
POSTGRES_DB	spine_db	Database name
POSTGRES_USER	adminuser	Database user
POSTGRES_PASSWORD	changeme	User password (⚠️ change in production!)

📊 Connection String Format

postgresql://adminuser:password@host:5432/spine_db

Local:

postgresql://adminuser:mysecretpassword@localhost:5432/spine_db

Azure Container Apps (internal DNS):

postgresql://adminuser:${DB_PASSWORD}@postgres-db:5432/spine_db

🛠️ Useful Commands

# View logs
docker logs stok-postgres

# Backup database
docker exec stok-postgres pg_dump -U adminuser spine_db > backup.sql

# Restore database
docker exec -i stok-postgres psql -U adminuser spine_db < backup.sql

# List tables
docker exec -it stok-postgres psql -U adminuser -d spine_db -c '\dt'

# Check table sizes
docker exec -it stok-postgres psql -U adminuser -d spine_db -c '\dt+'

🔧 Schema Modifications

To modify the schema:

1. Edit init.sql
2. Rebuild container: docker build -t stok-postgres ./database
3. Stop old container: docker stop stok-postgres && docker rm stok-postgres
4. Start new container: docker run -d --name stok-postgres ...

Note: init.sql only runs on first startup. For existing databases, use migrations.

🔄 Migrating from SQLite

If you have existing SQLite data in meeting-agent/spine_dev.sqlite3:

# Install dependencies
pip install psycopg2-binary

# Start PostgreSQL
docker-compose up postgres -d

# Run migration
cd database
python migrate-sqlite-to-postgres.py \
  --sqlite-path ../meeting-agent/spine_dev.sqlite3 \
  --postgres-url postgresql://adminuser:devpassword123@localhost:5432/spine_db

# Verify migration
docker exec -it postgres-db psql -U adminuser -d spine_db -c "SELECT COUNT(*) FROM facts;"

The migration script handles:

• ✅ All table relationships
• ✅ Foreign key constraints
• ✅ Automatic verification
• ✅ Dry-run mode support

📈 Monitoring

Health check endpoint:

docker exec stok-postgres pg_isready -U adminuser -d spine_db

🔒 Security Notes

• ⚠️ Change default password before deploying!
• Use secrets management (Azure Key Vault) in production
• Enable SSL/TLS connections
• Restrict network access (internal only in Container Apps)

💾 Persistence

Local:

• Data persists in Docker volume
• Volume name: postgres-data

Azure Container Apps:

• Use Azure Files for persistence
• Or accept ephemeral storage (data resets on restart)

🚀 Deployment to Azure

See deploy-postgres.sh for deployment script.

bash deploy-postgres.sh

🆘 Troubleshooting

Container won't start:

docker logs stok-postgres

Can't connect:

• Check password matches
• Verify port 5432 is exposed
• Ensure PostgreSQL is ready: pg_isready

Schema not created:

• init.sql only runs on first startup
• Delete volume and restart: docker volume rm postgres-data

---

Need help? Check the main DEPLOYMENT_GUIDE.md