Database Migration Workflow

This skill guides you through modifying the Lightfriend database schema using Diesel ORM.

Prerequisites

• Backend server should be stopped during migrations
• Backup database if modifying production data

Step-by-Step Process

1. Generate Migration

cd backend && diesel migration generate <descriptive_name>

Replace <descriptive_name> with a clear description like:

• add_user_preferences_table
• add_email_verified_column
• rename_credits_to_balance

This creates two files in backend/migrations/<timestamp>_<name>/:

• up.sql - Apply changes
• down.sql - Revert changes

2. Edit Migration Files

up.sql - Write SQL to apply your changes:

-- Example: Add new column
ALTER TABLE users ADD COLUMN email_verified BOOLEAN NOT NULL DEFAULT 0;

-- Example: Create new table
CREATE TABLE user_preferences (
    id INTEGER PRIMARY KEY NOT NULL,
    user_id INTEGER NOT NULL,
    theme TEXT NOT NULL DEFAULT 'light',
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- Example: Create index
CREATE INDEX idx_user_preferences_user_id ON user_preferences(user_id);

down.sql - Write SQL to revert your changes:

-- Example: Remove column
ALTER TABLE users DROP COLUMN email_verified;

-- Example: Drop table
DROP TABLE user_preferences;

-- Example: Drop index
DROP INDEX idx_user_preferences_user_id;

3. Run Migration

cd backend && diesel migration run

This applies the migration and updates the database.

4. Update Diesel Models

If you added/modified tables, update backend/src/models/user_models.rs:

#[derive(Queryable, Insertable, Debug)]
#[diesel(table_name = user_preferences)]
pub struct UserPreference {
    pub id: i32,
    pub user_id: i32,
    pub theme: String,
}

5. Regenerate Schema

CRITICAL: Always regenerate the schema after migrations:

cd backend && diesel print-schema > src/schema.rs

This updates backend/src/schema.rs with the new table/column definitions that Diesel uses for type checking.

6. Update Repository Code

Add repository methods for new tables/columns in the appropriate repository:

• repositories/user_core.rs - User authentication, core user data
• repositories/user_repository.rs - User features, integrations
• repositories/user_subscriptions.rs - Billing, subscriptions
• repositories/connection_auth.rs - OAuth connections

Example:

pub fn update_user_preference(
    conn: &mut SqliteConnection,
    user_id: i32,
    theme: &str,
) -> Result<(), diesel::result::Error> {
    diesel::update(user_preferences::table)
        .filter(user_preferences::user_id.eq(user_id))
        .set(user_preferences::theme.eq(theme))
        .execute(conn)?;
    Ok(())
}

7. Test Migration

cd backend && cargo test

8. Revert If Needed

If something goes wrong:

cd backend && diesel migration revert

This runs the down.sql script to undo the migration.

Common Patterns

Adding Encrypted Fields

For sensitive data (tokens, passwords), use TEXT fields and encrypt in application code:

ALTER TABLE connections ADD COLUMN access_token TEXT;

Then encrypt/decrypt using backend/src/utils/encryption.rs.

Adding Foreign Keys

CREATE TABLE events (
    id INTEGER PRIMARY KEY NOT NULL,
    user_id INTEGER NOT NULL,
    event_type TEXT NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

Adding Timestamps

Always use INTEGER for timestamps (Unix epoch seconds), never TEXT or datetime objects.

This keeps timestamps simple and ensures all dates are stored as UTC:

-- Add timestamp as INTEGER (Unix epoch seconds in UTC)
ALTER TABLE users ADD COLUMN updated_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now'));

In Rust, use chrono::Utc::now().timestamp() to get the current UTC timestamp as i64.

Important Notes

• Never edit schema.rs manually - always use diesel print-schema
• Always write down.sql - migrations should be reversible
• Test migrations on copy of data before production
• Migrations are sequential - order matters
• SQLite limitations: Some operations require table recreation (see Diesel docs)
• Avoid JSON fields: Prefer simple TEXT fields and parse to JSON in application code after retrieving from the database. This keeps the schema simple and gives more control over serialization.

Troubleshooting

"diesel: command not found"

cargo install diesel_cli --no-default-features --features sqlite

"schema.rs is out of sync"

cd backend && diesel print-schema > src/schema.rs

Migration fails

• Check SQL syntax in up.sql
• Ensure foreign key references exist
• Check for SQLite-specific limitations
• Use diesel migration revert to undo