Documentation
Database & Migrations
Database & Migrations
Infra0 uses PostgreSQL as its primary database, managed with Drizzle ORM.
Database Setup
- PostgreSQL 14+ is required
- Connection is configured via DATABASE_URL environment variable
- SSL support is configurable via DATABASE_SSL
- Connection pool size is configurable via DATABASE_POOL_SIZE
Running Migrations
After deploying for the first time or after an upgrade:
# Generate migration files from schema changes npm run db:generate
# Apply pending migrations to the database npm run db:migrate
# Or using Docker
code
docker compose exec infra0-app npm run db:migrate
Seeding Initial Data
Required to set up RBAC roles and permissions:
npm run db:seed
The seed script creates
- Default roles: Admin, Manager, Editor, Viewer
- All system permissions
- Role-permission assignments
Database Schema Overview
Key tables in the Infra0 database:
- users: User accounts
- sessions: Active authentication sessions
- accounts: OAuth account links
- projects: Infrastructure projects
- workspaces: Terraform workspaces
- conversations: AI chat conversations
- messages: Individual chat messages
- cloud_provider_credentials: AWS/Azure/GCP credentials (encrypted)
- app_credentials: Application credentials (encrypted)
- credential_type: Credential type templates
- roles: RBAC role definitions
- permissions: RBAC permission definitions
- user_roles: User-role assignment join table
- role_permissions: Role-permission join table
- access_requests: Access request workflow
- activity_stream: Audit log
- tickets: Infrastructure tickets
- module_registry: Terraform modules
- drift_detection: Drift check history
Backups
- Back up the database regularly using pg_dump
- Store backups in a separate location from the application server
- Test restores periodically to verify backup integrity
Example backup command
code
docker compose exec postgres pg_dump -U infra0_user infra0 > backup_$(date +%Y%m%d).sql