# Database Migrations
This directory contains versioned SQL migrations for the Pierre MCP Server database schema.
## Overview
We use **sqlx-cli** for database migrations, which provides:
- ✅ Version tracking via `_sqlx_migrations` table
- ✅ Automatic rollback on migration failures (transactional)
- ✅ Idempotent migrations (safe to re-run)
- ✅ Support for both SQLite and PostgreSQL
- ✅ Compile-time query validation
## Migration Files
Migrations are numbered sequentially and named descriptively:
| File | Description |
|------|-------------|
| `20250120000001_users_schema.sql` | User accounts, profiles, and OAuth app credentials |
| `20250120000002_api_keys_schema.sql` | API key management and usage tracking |
| `20250120000003_analytics_schema.sql` | Analytics, goals, insights, and request logs |
| `20250120000004_a2a_schema.sql` | Agent-to-Agent protocol tables |
| `20250120000005_admin_schema.sql` | Admin tokens and system secrets |
| `20250120000006_oauth_tokens_schema.sql` | Per-user OAuth tokens |
| `20250120000007_oauth_notifications_schema.sql` | OAuth completion notifications |
| `20250120000008_oauth2_schema.sql` | OAuth2 server (RFC 7591) |
| `20250120000009_tenant_management_schema.sql` | Multi-tenant infrastructure |
| `20250120000010_fitness_configurations_schema.sql` | Fitness-specific configurations |
| `20250120000011_expand_oauth_provider_constraints.sql` | Add garmin, whoop, terra to OAuth provider constraints |
| `20250120000012_user_roles_permissions.sql` | Role-based permissions, impersonation sessions, user MCP tokens |
| `20250120000013_system_settings_schema.sql` | System settings key-value store for admin configuration |
| `20250120000014_add_missing_foreign_keys.sql` | Add FK constraints to a2a_clients and user_configurations |
| `20250120000015_remove_legacy_user_token_columns.sql` | Remove legacy OAuth columns from users, add last_sync |
| `20250120000017_chat_schema.sql` | Chat conversations and messages for AI assistant |
| `20250120000018_firebase_auth.sql` | Firebase authentication (firebase_uid, auth_provider) |
| `20250120000019_recipes_schema.sql` | Recipes and ingredients with USDA nutrition validation |
| `20250120000020_admin_config_schema.sql` | Runtime config overrides and audit logging |
| `20250120000021_add_config_categories.sql` | Additional config categories for providers, caching, MCP |
## How Migrations Work
### Automatic Migrations (Production)
The application automatically runs migrations on startup via `Database::migrate()`:
```rust
let database = Database::connect(&config.database_url).await?;
// Migrations run automatically during initialization
```
### Manual Migration Management
#### List Migration Status
```bash
export DATABASE_URL=sqlite:./data/users.db
sqlx migrate info
```
#### Apply Pending Migrations
```bash
sqlx migrate run
```
#### Revert Last Migration
```bash
sqlx migrate revert
```
## Creating New Migrations
### Method 1: Using sqlx-cli (Recommended)
```bash
# Create a new migration file
sqlx migrate add add_user_preferences_table
# Edit the generated file
vim migrations/YYYYMMDDHHMMSS_add_user_preferences_table.sql
```
### Method 2: Manual Creation
1. Create a new file: `migrations/<timestamp>_<description>.sql`
2. Use timestamp format: `YYYYMMDDHHMMSS` (e.g., `20250120120000`)
3. Write SQL using SQLite syntax (currently)
Example:
```sql
-- Description of what this migration does
CREATE TABLE IF NOT EXISTS user_preferences (
id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(16)))),
user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
theme TEXT NOT NULL DEFAULT 'light',
language TEXT NOT NULL DEFAULT 'en',
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_user_preferences_user_id
ON user_preferences(user_id);
```
## Migration Best Practices
### ✅ Do:
- Use `CREATE TABLE IF NOT EXISTS` for safety
- Add descriptive comments at the top of each migration
- Create indexes for foreign keys and frequently queried columns
- Use transactions implicitly (sqlx wraps each migration in a transaction)
- Test migrations on a staging database first
### ❌ Don't:
- Remove `IF NOT EXISTS` clauses (defeats idempotency)
- Modify existing migrations after they've been deployed
- Skip version numbers
- Use database-specific features without testing on all supported databases
## Database Support
### Cross-Database Compatible Migrations
All migrations use **portable SQL syntax** that works on both SQLite and PostgreSQL:
| Type | SQL Type | Notes |
|------|----------|-------|
| Strings | `TEXT` | Works on both databases |
| Numbers | `INTEGER` | Works on both databases |
| Booleans | `INTEGER` | Stored as 0/1 (portable) |
| Timestamps | `TEXT` | ISO8601 format (app-generated) |
| UUIDs/IDs | `TEXT PRIMARY KEY` | App-generated UUIDs |
### Design Principles
1. **No database-generated defaults for timestamps** - Application provides all `created_at`/`updated_at` values
2. **No AUTOINCREMENT** - All IDs are TEXT UUIDs generated by the application
3. **INTEGER for booleans** - Both databases support 0/1 semantics
4. **TEXT for timestamps** - ISO8601 strings are portable and sortable
5. **No triggers** - Logic handled in application code
### Switching Databases
To switch between SQLite and PostgreSQL:
1. Update `Cargo.toml` feature flags:
```toml
# For SQLite (default)
default = ["sqlite"]
# For PostgreSQL
cargo build --features postgresql
```
2. Set the appropriate `DATABASE_URL`:
```bash
# SQLite
DATABASE_URL=sqlite:./data/users.db
# PostgreSQL
DATABASE_URL=postgres://user:pass@localhost/pierre
```
3. Run migrations (same files work for both):
```bash
sqlx migrate run
```
## CI/CD Integration
### GitHub Actions Example
```yaml
- name: Run migrations
run: sqlx migrate run
env:
DATABASE_URL: ${{ secrets.DATABASE_URL }}
- name: Verify schema matches queries
run: cargo sqlx prepare --check
```
## Troubleshooting
### Migration Failed Mid-Run
Migrations are transactional - if one fails, all changes are rolled back automatically.
### "Migration X already applied"
This is normal - sqlx tracks applied migrations in `_sqlx_migrations` table.
### Need to Undo Last Migration
```bash
sqlx migrate revert
```
### Reset Database (Development Only)
```bash
rm data/users.db
sqlx migrate run
```
## Production Deployment Checklist
Before deploying migrations to production:
1. ✅ **Backup database**
```bash
sqlite3 data/users.db ".backup users_backup_$(date +%Y%m%d).db"
```
2. ✅ **Test on staging**
```bash
export DATABASE_URL=sqlite:./staging.db
sqlx migrate run
```
3. ✅ **Review migration**
```bash
sqlx migrate info
cat migrations/<latest_migration>.sql
```
4. ✅ **Apply to production**
```bash
export DATABASE_URL=sqlite:./data/users.db
sqlx migrate run
```
5. ✅ **Verify application starts**
```bash
cargo run --release
```
## Support
For migration issues:
1. Check logs: `tracing::info!("Database migrations...")`
2. Inspect `_sqlx_migrations` table
3. Review https://github.com/launchbadge/sqlx/tree/main/sqlx-cli
## Version History
- **v0.3.0** (2025-12): Added chat, recipes, admin config, Firebase auth, role-based permissions
- **v0.2.0** (2025-01-20): Migrated from embedded migrations to sqlx-cli
- 20 migration files covering 40+ tables across 15 functional domains
