Skip to main content

auth_framework/migrations/
mod.rs

1//! Database migration system for auth-framework.
2//! This module provides tools for managing database schema changes
3//! and ensuring proper setup of authentication-related tables.
4#[cfg(any(feature = "cli", feature = "postgres-storage"))]
5use tokio_postgres::{Client, Error as PgError};
6
7/// The one `sessions` table: what `PostgresStorage::migrate()` creates and what the storage reads
8/// and writes. The CLI's migrations 3 and 6 run the same text, so whichever path reaches a
9/// database first it ends with this table (issue #147). A `sessions` table of any other shape (the
10/// pre-fix CLI one) is renamed to `sessions_legacy_v3`, with its indexes, rather than dropped:
11/// those rows were never readable through the storage, but they are the operator's to discard.
12/// Idempotent; run it in one transaction.
13#[cfg(any(feature = "cli", feature = "postgres-storage"))]
14pub(crate) const SESSIONS_SCHEMA_SQL: &str = r#"
15DO $$
16BEGIN
17    IF to_regclass('sessions') IS NOT NULL AND NOT EXISTS (
18        SELECT 1 FROM information_schema.columns
19        WHERE table_schema = current_schema()
20          AND table_name = 'sessions' AND column_name = 'session_id'
21    ) THEN
22        ALTER TABLE sessions RENAME TO sessions_legacy_v3;
23        ALTER INDEX IF EXISTS sessions_pkey RENAME TO sessions_legacy_v3_pkey;
24        ALTER INDEX IF EXISTS idx_sessions_user_id RENAME TO idx_sessions_legacy_v3_user_id;
25        ALTER INDEX IF EXISTS idx_sessions_expires_at RENAME TO idx_sessions_legacy_v3_expires_at;
26    END IF;
27END $$;
28CREATE TABLE IF NOT EXISTS sessions (
29    session_id VARCHAR(255) PRIMARY KEY,
30    user_id    VARCHAR(255) NOT NULL,
31    data       JSONB        NOT NULL,
32    expires_at TIMESTAMPTZ,
33    created_at TIMESTAMPTZ  DEFAULT NOW()
34);
35CREATE INDEX IF NOT EXISTS idx_sessions_user_id ON sessions (user_id);
36CREATE INDEX IF NOT EXISTS idx_sessions_expires_at ON sessions (expires_at);
37"#;
38
39/// Migration manager for database schema changes
40#[cfg(any(feature = "cli", feature = "postgres-storage"))]
41pub struct MigrationManager {
42    client: Client,
43}
44
45#[cfg(any(feature = "cli", feature = "postgres-storage"))]
46#[derive(Debug, Clone)]
47pub struct Migration {
48    pub version: i64,
49    pub name: String,
50    pub sql: String,
51}
52
53#[cfg(any(feature = "cli", feature = "postgres-storage"))]
54impl MigrationManager {
55    pub fn new(client: Client) -> Self {
56        Self { client }
57    }
58
59    /// Run all pending migrations
60    pub async fn migrate(&mut self) -> Result<(), MigrationError> {
61        // Ensure migrations table exists
62        self.ensure_migrations_table().await?;
63
64        let applied = self.get_applied_migrations().await?;
65        let available = self.get_available_migrations();
66
67        for migration in available {
68            if !applied.contains(&migration.version) {
69                tracing::info!(name = %migration.name, "Applying migration");
70                self.apply_migration(&migration).await?;
71            }
72        }
73
74        Ok(())
75    }
76
77    async fn ensure_migrations_table(&self) -> Result<(), PgError> {
78        self.client
79            .execute(
80                r#"
81            CREATE TABLE IF NOT EXISTS _auth_migrations (
82                version BIGINT PRIMARY KEY,
83                name VARCHAR(255) NOT NULL,
84                applied_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
85            )
86            "#,
87                &[],
88            )
89            .await?;
90        Ok(())
91    }
92
93    async fn get_applied_migrations(&self) -> Result<Vec<i64>, PgError> {
94        let rows = self
95            .client
96            .query("SELECT version FROM _auth_migrations ORDER BY version", &[])
97            .await?;
98
99        Ok(rows.iter().map(|row| row.get(0)).collect())
100    }
101
102    fn get_available_migrations(&self) -> Vec<Migration> {
103        vec![
104            Migration {
105                version: 1,
106                name: "create_users_table".to_string(),
107                sql: r#"
108                    CREATE TABLE IF NOT EXISTS users (
109                        id VARCHAR(36) PRIMARY KEY,
110                        username VARCHAR(255) UNIQUE NOT NULL,
111                        password_hash VARCHAR(255) NOT NULL,
112                        email VARCHAR(255) UNIQUE,
113                        created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
114                        updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
115                        is_active BOOLEAN DEFAULT true,
116                        last_login TIMESTAMP WITH TIME ZONE
117                    );
118                    CREATE INDEX IF NOT EXISTS idx_users_username ON users(username);
119                    CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);
120                "#.to_string(),
121            },
122            Migration {
123                version: 2,
124                name: "create_roles_permissions".to_string(),
125                sql: r#"
126                    CREATE TABLE IF NOT EXISTS roles (
127                        id VARCHAR(36) PRIMARY KEY,
128                        name VARCHAR(100) UNIQUE NOT NULL,
129                        description TEXT,
130                        created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
131                    );
132
133                    CREATE TABLE IF NOT EXISTS permissions (
134                        id VARCHAR(36) PRIMARY KEY,
135                        action VARCHAR(100) NOT NULL,
136                        resource VARCHAR(100) NOT NULL,
137                        instance VARCHAR(100),
138                        created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
139                    );
140
141                    CREATE TABLE IF NOT EXISTS user_roles (
142                        user_id VARCHAR(36) REFERENCES users(id),
143                        role_id VARCHAR(36) REFERENCES roles(id),
144                        PRIMARY KEY (user_id, role_id)
145                    );
146
147                    CREATE TABLE IF NOT EXISTS role_permissions (
148                        role_id VARCHAR(36) REFERENCES roles(id),
149                        permission_id VARCHAR(36) REFERENCES permissions(id),
150                        PRIMARY KEY (role_id, permission_id)
151                    );
152                "#.to_string(),
153            },
154            Migration {
155                version: 3,
156                name: "create_sessions_table".to_string(),
157                sql: SESSIONS_SCHEMA_SQL.to_string(),
158            },
159            Migration {
160                version: 4,
161                name: "create_audit_logs".to_string(),
162                sql: r#"
163                    CREATE TABLE IF NOT EXISTS audit_logs (
164                        id VARCHAR(36) PRIMARY KEY,
165                        event_type VARCHAR(50) NOT NULL,
166                        timestamp TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
167                        user_id VARCHAR(36),
168                        session_id VARCHAR(36),
169                        resource VARCHAR(100),
170                        action VARCHAR(100),
171                        success BOOLEAN NOT NULL,
172                        ip_address INET,
173                        user_agent TEXT,
174                        details JSONB
175                    );
176                    CREATE INDEX IF NOT EXISTS idx_audit_logs_timestamp ON audit_logs(timestamp);
177                    CREATE INDEX IF NOT EXISTS idx_audit_logs_user_id ON audit_logs(user_id);
178                    CREATE INDEX IF NOT EXISTS idx_audit_logs_event_type ON audit_logs(event_type);
179                "#.to_string(),
180            },
181            Migration {
182                version: 5,
183                name: "create_mfa_table".to_string(),
184                sql: r#"
185                    CREATE TABLE IF NOT EXISTS mfa_secrets (
186                        user_id VARCHAR(36) PRIMARY KEY REFERENCES users(id),
187                        totp_secret VARCHAR(255),
188                        backup_codes TEXT[],
189                        phone_number VARCHAR(20),
190                        email_verified BOOLEAN DEFAULT false,
191                        phone_verified BOOLEAN DEFAULT false,
192                        created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
193                    );
194
195                    CREATE TABLE IF NOT EXISTS mfa_challenges (
196                        id VARCHAR(36) PRIMARY KEY,
197                        user_id VARCHAR(36) REFERENCES users(id),
198                        challenge_type VARCHAR(20) NOT NULL,
199                        challenge_data TEXT,
200                        expires_at TIMESTAMP WITH TIME ZONE NOT NULL,
201                        created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
202                    );
203                    CREATE INDEX IF NOT EXISTS idx_mfa_challenges_expires_at ON mfa_challenges(expires_at);
204                "#.to_string(),
205            },
206            Migration {
207                version: 6,
208                name: "reconcile_sessions_schema".to_string(),
209                sql: SESSIONS_SCHEMA_SQL.to_string(),
210            },
211        ]
212    }
213
214    async fn apply_migration(&mut self, migration: &Migration) -> Result<(), MigrationError> {
215        let tx = self.client.transaction().await?;
216
217        // Execute migration SQL
218        tx.batch_execute(&migration.sql).await?;
219
220        // Record migration
221        use tokio_postgres::types::ToSql;
222        tx.execute(
223            "INSERT INTO _auth_migrations (version, name) VALUES ($1, $2)",
224            &[
225                &migration.version as &(dyn ToSql + Sync),
226                &migration.name.as_str() as &(dyn ToSql + Sync),
227            ],
228        )
229        .await?;
230
231        tx.commit().await?;
232        Ok(())
233    }
234
235    /// Check migration status
236    pub async fn status(&self) -> Result<MigrationStatus, MigrationError> {
237        let applied = self
238            .get_applied_migrations()
239            .await
240            .map_err(MigrationError::Database)?;
241        let available = self.get_available_migrations();
242
243        let pending: Vec<_> = available
244            .iter()
245            .filter(|m| !applied.contains(&m.version))
246            .collect();
247
248        Ok(MigrationStatus {
249            applied_count: applied.len(),
250            pending_count: pending.len(),
251            latest_applied: applied.last().copied(),
252            next_pending: pending.first().map(|m| m.version),
253        })
254    }
255
256    /// Create a new migration (for external use)
257    pub fn create_migration(version: i64, name: String, sql: String) -> Migration {
258        Migration { version, name, sql }
259    }
260
261    /// Get list of available migrations
262    pub fn list_available_migrations(&self) -> Vec<Migration> {
263        // Return cloned migrations to avoid lifetime issues
264        self.get_available_migrations()
265    }
266}
267
268#[derive(Debug)]
269pub struct MigrationStatus {
270    pub applied_count: usize,
271    pub pending_count: usize,
272    pub latest_applied: Option<i64>,
273    pub next_pending: Option<i64>,
274}
275
276#[cfg(any(feature = "cli", feature = "postgres-storage"))]
277#[derive(Debug, thiserror::Error)]
278pub enum MigrationError {
279    #[error("Database error: {0}")]
280    Database(PgError),
281    #[error("Migration not found: {0}")]
282    NotFound(i64),
283    #[error("Invalid migration order")]
284    InvalidOrder,
285}
286
287#[cfg(any(feature = "cli", feature = "postgres-storage"))]
288impl From<PgError> for MigrationError {
289    fn from(e: PgError) -> Self {
290        MigrationError::Database(e)
291    }
292}
293
294#[cfg(any(feature = "cli", feature = "postgres-storage"))]
295/// CLI tool for running migrations
296pub struct MigrationCli;
297
298#[cfg(any(feature = "cli", feature = "postgres-storage"))]
299impl MigrationCli {
300    pub async fn run(database_url: &str, command: &str) -> Result<(), Box<dyn std::error::Error>> {
301        let (client, connection) =
302            tokio_postgres::connect(database_url, tokio_postgres::NoTls).await?;
303        tokio::spawn(async move {
304            if let Err(e) = connection.await {
305                tracing::error!(error = %e, "Database connection error");
306            }
307        });
308        let mut manager = MigrationManager::new(client);
309
310        match command {
311            "migrate" => {
312                manager.migrate().await?;
313                tracing::info!("Migrations completed successfully");
314            }
315            "status" => {
316                let status = manager.status().await?;
317                tracing::info!(
318                    applied = status.applied_count,
319                    pending = status.pending_count,
320                    latest_applied = ?status.latest_applied,
321                    next_pending = ?status.next_pending,
322                    "Migration status"
323                );
324            }
325            _ => {
326                tracing::error!(
327                    command,
328                    "Unknown migration command. Available commands: migrate, status"
329                );
330            }
331        }
332
333        Ok(())
334    }
335}