fraiseql_server/secrets/
schemas.rs

1// Phase 12.5 Cycle 2: Database Schema for Secrets & Keys - GREEN
2//! Database schema definitions for secrets management, encryption keys,
3//! external authentication providers, and OAuth sessions.
4
5use std::collections::HashMap;
6
7use chrono::{DateTime, Utc};
8use serde::{Deserialize, Serialize};
9
10/// Secret rotation audit record
11#[derive(Debug, Clone, Serialize, Deserialize, PartialEq, Eq)]
12pub struct SecretRotationAudit {
13    /// Unique audit record ID
14    pub id:                 String,
15    /// Secret name that was rotated
16    pub secret_name:        String,
17    /// When rotation occurred
18    pub rotation_timestamp: DateTime<Utc>,
19    /// User or system that performed rotation
20    pub rotated_by:         Option<String>,
21    /// ID of previous secret version
22    pub previous_secret_id: Option<String>,
23    /// ID of new secret version
24    pub new_secret_id:      Option<String>,
25    /// Rotation status: "success", "failed"
26    pub status:             String,
27    /// Error message if failed
28    pub error_message:      Option<String>,
29    /// Additional metadata (JSON)
30    pub metadata:           HashMap<String, String>,
31}
32
33impl SecretRotationAudit {
34    /// Create new secret rotation audit record
35    pub fn new(secret_name: impl Into<String>, status: impl Into<String>) -> Self {
36        Self {
37            id:                 uuid::Uuid::new_v4().to_string(),
38            secret_name:        secret_name.into(),
39            rotation_timestamp: Utc::now(),
40            rotated_by:         None,
41            previous_secret_id: None,
42            new_secret_id:      None,
43            status:             status.into(),
44            error_message:      None,
45            metadata:           HashMap::new(),
46        }
47    }
48
49    /// Set who performed the rotation
50    pub fn with_rotated_by(mut self, user_id: impl Into<String>) -> Self {
51        self.rotated_by = Some(user_id.into());
52        self
53    }
54
55    /// Set secret version IDs
56    pub fn with_secret_ids(
57        mut self,
58        previous_id: impl Into<String>,
59        new_id: impl Into<String>,
60    ) -> Self {
61        self.previous_secret_id = Some(previous_id.into());
62        self.new_secret_id = Some(new_id.into());
63        self
64    }
65
66    /// Set error message
67    pub fn with_error(mut self, error: impl Into<String>) -> Self {
68        self.error_message = Some(error.into());
69        self
70    }
71
72    /// Add metadata
73    pub fn add_metadata(mut self, key: impl Into<String>, value: impl Into<String>) -> Self {
74        self.metadata.insert(key.into(), value.into());
75        self
76    }
77
78    /// Check if rotation was successful
79    pub fn is_successful(&self) -> bool {
80        self.status == "success"
81    }
82}
83
84/// Encryption key record
85#[derive(Debug, Clone, Serialize, Deserialize, PartialEq, Eq)]
86pub struct EncryptionKey {
87    /// Unique key ID
88    pub id:                     String,
89    /// Key name (e.g., "fraiseql/database-encryption")
90    pub name:                   String,
91    /// Encrypted key material (stored as encrypted bytes)
92    pub key_material_encrypted: Vec<u8>,
93    /// Encryption algorithm (e.g., "AES-256-GCM")
94    pub algorithm:              String,
95    /// Version number (incremented on rotation)
96    pub version:                u16,
97    /// When key was created
98    pub created_at:             DateTime<Utc>,
99    /// When key was last rotated
100    pub rotated_at:             Option<DateTime<Utc>>,
101    /// Key status: "active", "rotating", "retired"
102    pub status:                 String,
103    /// Additional metadata (JSON)
104    pub metadata:               HashMap<String, String>,
105}
106
107impl EncryptionKey {
108    /// Create new encryption key
109    pub fn new(
110        name: impl Into<String>,
111        key_material_encrypted: Vec<u8>,
112        algorithm: impl Into<String>,
113    ) -> Self {
114        Self {
115            id: uuid::Uuid::new_v4().to_string(),
116            name: name.into(),
117            key_material_encrypted,
118            algorithm: algorithm.into(),
119            version: 1,
120            created_at: Utc::now(),
121            rotated_at: None,
122            status: "active".to_string(),
123            metadata: HashMap::new(),
124        }
125    }
126
127    /// Mark key as rotating
128    pub fn start_rotation(mut self) -> Self {
129        self.status = "rotating".to_string();
130        self
131    }
132
133    /// Complete rotation and increment version
134    pub fn complete_rotation(mut self, new_key_material: Vec<u8>) -> Self {
135        self.version += 1;
136        self.key_material_encrypted = new_key_material;
137        self.rotated_at = Some(Utc::now());
138        self.status = "active".to_string();
139        self
140    }
141
142    /// Retire key (for historical decryption only)
143    pub fn retire(mut self) -> Self {
144        self.status = "retired".to_string();
145        self
146    }
147
148    /// Check if key is active
149    pub fn is_active(&self) -> bool {
150        self.status == "active"
151    }
152
153    /// Check if key is rotating
154    pub fn is_rotating(&self) -> bool {
155        self.status == "rotating"
156    }
157
158    /// Add metadata
159    pub fn add_metadata(mut self, key: impl Into<String>, value: impl Into<String>) -> Self {
160        self.metadata.insert(key.into(), value.into());
161        self
162    }
163}
164
165/// External authentication provider
166#[derive(Debug, Clone, Serialize, Deserialize, PartialEq, Eq)]
167pub struct ExternalAuthProviderRecord {
168    /// Unique provider ID
169    pub id: String,
170    /// Tenant ID (for multi-tenancy)
171    pub tenant_id: String,
172    /// Provider type: "oauth2", "oidc"
173    pub provider_type: String,
174    /// Provider name: "auth0", "google", "microsoft", "okta"
175    pub provider_name: String,
176    /// Client ID from provider
177    pub client_id: String,
178    /// Vault path to client secret
179    pub client_secret_vault_path: String,
180    /// Provider configuration (JSON)
181    pub configuration: HashMap<String, String>,
182    /// Is provider enabled
183    pub enabled: bool,
184    /// When provider was configured
185    pub created_at: DateTime<Utc>,
186}
187
188impl ExternalAuthProviderRecord {
189    /// Create new external auth provider
190    pub fn new(
191        tenant_id: impl Into<String>,
192        provider_type: impl Into<String>,
193        provider_name: impl Into<String>,
194        client_id: impl Into<String>,
195        client_secret_vault_path: impl Into<String>,
196    ) -> Self {
197        Self {
198            id: uuid::Uuid::new_v4().to_string(),
199            tenant_id: tenant_id.into(),
200            provider_type: provider_type.into(),
201            provider_name: provider_name.into(),
202            client_id: client_id.into(),
203            client_secret_vault_path: client_secret_vault_path.into(),
204            configuration: HashMap::new(),
205            enabled: true,
206            created_at: Utc::now(),
207        }
208    }
209
210    /// Enable provider
211    pub fn enable(mut self) -> Self {
212        self.enabled = true;
213        self
214    }
215
216    /// Disable provider
217    pub fn disable(mut self) -> Self {
218        self.enabled = false;
219        self
220    }
221
222    /// Add configuration
223    pub fn add_configuration(mut self, key: impl Into<String>, value: impl Into<String>) -> Self {
224        self.configuration.insert(key.into(), value.into());
225        self
226    }
227
228    /// Check if provider is enabled
229    pub fn is_enabled(&self) -> bool {
230        self.enabled
231    }
232
233    /// Check if provider is OIDC
234    pub fn is_oidc(&self) -> bool {
235        self.provider_type == "oidc"
236    }
237
238    /// Check if provider is OAuth2
239    pub fn is_oauth2(&self) -> bool {
240        self.provider_type == "oauth2"
241    }
242}
243
244/// OAuth session record
245#[derive(Debug, Clone, Serialize, Deserialize)]
246pub struct OAuthSessionRecord {
247    /// Unique session ID
248    pub id: String,
249    /// Local user ID
250    pub user_id: String,
251    /// Provider type: "oauth2", "oidc"
252    pub provider_type: String,
253    /// Provider's user ID (e.g., "auth0|user123")
254    pub provider_user_id: String,
255    /// Encrypted access token
256    pub access_token_encrypted: String,
257    /// Encrypted refresh token (if available)
258    pub refresh_token_encrypted: Option<String>,
259    /// When access token expires
260    pub token_expiry: DateTime<Utc>,
261    /// When session was created
262    pub created_at: DateTime<Utc>,
263    /// When token was last refreshed
264    pub last_refreshed: Option<DateTime<Utc>>,
265}
266
267impl OAuthSessionRecord {
268    /// Create new OAuth session
269    pub fn new(
270        user_id: impl Into<String>,
271        provider_type: impl Into<String>,
272        provider_user_id: impl Into<String>,
273        access_token_encrypted: impl Into<String>,
274        token_expiry: DateTime<Utc>,
275    ) -> Self {
276        Self {
277            id: uuid::Uuid::new_v4().to_string(),
278            user_id: user_id.into(),
279            provider_type: provider_type.into(),
280            provider_user_id: provider_user_id.into(),
281            access_token_encrypted: access_token_encrypted.into(),
282            refresh_token_encrypted: None,
283            token_expiry,
284            created_at: Utc::now(),
285            last_refreshed: None,
286        }
287    }
288
289    /// Set refresh token
290    pub fn with_refresh_token(mut self, refresh_token: impl Into<String>) -> Self {
291        self.refresh_token_encrypted = Some(refresh_token.into());
292        self
293    }
294
295    /// Update tokens after refresh
296    pub fn refresh_tokens(
297        mut self,
298        access_token: impl Into<String>,
299        token_expiry: DateTime<Utc>,
300    ) -> Self {
301        self.access_token_encrypted = access_token.into();
302        self.token_expiry = token_expiry;
303        self.last_refreshed = Some(Utc::now());
304        self
305    }
306
307    /// Check if token is expired
308    pub fn is_token_expired(&self) -> bool {
309        self.token_expiry <= Utc::now()
310    }
311
312    /// Check if token will expire soon (within grace period)
313    pub fn is_token_expiring_soon(&self, grace_seconds: i64) -> bool {
314        let grace_deadline = Utc::now() + chrono::Duration::seconds(grace_seconds);
315        self.token_expiry <= grace_deadline
316    }
317}
318
319/// Database schema migration
320#[derive(Debug, Clone, Serialize, Deserialize)]
321pub struct SchemaMigration {
322    /// Migration file name (e.g., "0013_secrets_audit.sql")
323    pub filename:    String,
324    /// Full SQL migration script
325    pub sql_content: String,
326    /// When migration was created
327    pub created_at:  DateTime<Utc>,
328    /// Migration description
329    pub description: Option<String>,
330}
331
332impl SchemaMigration {
333    /// Create new schema migration
334    pub fn new(
335        filename: impl Into<String>,
336        sql_content: impl Into<String>,
337        description: Option<String>,
338    ) -> Self {
339        Self {
340            filename: filename.into(),
341            sql_content: sql_content.into(),
342            created_at: Utc::now(),
343            description,
344        }
345    }
346
347    /// Get migration 0013 for secrets audit schema
348    pub fn secrets_audit_migration() -> Self {
349        let sql = r#"
350CREATE TABLE IF NOT EXISTS secret_rotation_audit (
351    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
352    secret_name VARCHAR(255) NOT NULL,
353    rotation_timestamp TIMESTAMPTZ NOT NULL DEFAULT NOW(),
354    rotated_by VARCHAR(255),
355    previous_secret_id UUID,
356    new_secret_id UUID,
357    status VARCHAR(50),
358    error_message TEXT,
359    metadata JSONB
360);
361
362CREATE TABLE IF NOT EXISTS encryption_keys (
363    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
364    name VARCHAR(255) NOT NULL UNIQUE,
365    key_material_encrypted BYTEA NOT NULL,
366    algorithm VARCHAR(50),
367    version INTEGER NOT NULL DEFAULT 1,
368    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
369    rotated_at TIMESTAMPTZ,
370    status VARCHAR(50),
371    metadata JSONB
372);
373
374CREATE TABLE IF NOT EXISTS external_auth_providers (
375    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
376    tenant_id UUID NOT NULL,
377    provider_type VARCHAR(50) NOT NULL,
378    provider_name VARCHAR(255) NOT NULL,
379    client_id VARCHAR(255) NOT NULL,
380    client_secret_vault_path VARCHAR(255),
381    configuration JSONB,
382    enabled BOOLEAN DEFAULT true,
383    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
384    UNIQUE(tenant_id, provider_name)
385);
386
387CREATE TABLE IF NOT EXISTS oauth_sessions (
388    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
389    user_id UUID NOT NULL,
390    provider_type VARCHAR(50),
391    provider_user_id VARCHAR(255) NOT NULL,
392    access_token_encrypted VARCHAR(2048),
393    refresh_token_encrypted VARCHAR(2048),
394    token_expiry TIMESTAMPTZ,
395    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
396    last_refreshed TIMESTAMPTZ
397);
398
399CREATE INDEX IF NOT EXISTS idx_secret_rotation_audit_name
400    ON secret_rotation_audit(secret_name, rotation_timestamp DESC);
401
402CREATE INDEX IF NOT EXISTS idx_encryption_keys_status
403    ON encryption_keys(status);
404
405CREATE INDEX IF NOT EXISTS idx_external_auth_providers_tenant
406    ON external_auth_providers(tenant_id, enabled);
407
408CREATE INDEX IF NOT EXISTS idx_oauth_sessions_user
409    ON oauth_sessions(user_id);
410
411CREATE INDEX IF NOT EXISTS idx_oauth_sessions_provider_user
412    ON oauth_sessions(provider_user_id, provider_type);
413
414CREATE INDEX IF NOT EXISTS idx_oauth_sessions_expiry
415    ON oauth_sessions(token_expiry);
416"#;
417
418        Self {
419            filename:    "0013_secrets_audit.sql".to_string(),
420            sql_content: sql.to_string(),
421            created_at:  Utc::now(),
422            description: Some(
423                "Create secrets audit, encryption keys, auth providers, and OAuth sessions tables"
424                    .to_string(),
425            ),
426        }
427    }
428}
429
430#[cfg(test)]
431mod tests {
432    use super::*;
433
434    #[test]
435    fn test_secret_rotation_audit_creation() {
436        let audit = SecretRotationAudit::new("database/creds/fraiseql", "success");
437        assert_eq!(audit.secret_name, "database/creds/fraiseql");
438        assert_eq!(audit.status, "success");
439        assert!(audit.is_successful());
440    }
441
442    #[test]
443    fn test_secret_rotation_audit_with_ids() {
444        let audit = SecretRotationAudit::new("database/creds/fraiseql", "success")
445            .with_secret_ids("prev_id", "new_id");
446        assert_eq!(audit.previous_secret_id, Some("prev_id".to_string()));
447        assert_eq!(audit.new_secret_id, Some("new_id".to_string()));
448    }
449
450    #[test]
451    fn test_secret_rotation_audit_with_error() {
452        let audit = SecretRotationAudit::new("database/creds/fraiseql", "failed")
453            .with_error("Vault unavailable");
454        assert!(!audit.is_successful());
455        assert_eq!(audit.error_message, Some("Vault unavailable".to_string()));
456    }
457
458    #[test]
459    fn test_encryption_key_creation() {
460        let key = EncryptionKey::new("fraiseql/database-encryption", vec![1, 2, 3], "AES-256-GCM");
461        assert_eq!(key.name, "fraiseql/database-encryption");
462        assert_eq!(key.version, 1);
463        assert_eq!(key.status, "active");
464        assert!(key.is_active());
465    }
466
467    #[test]
468    fn test_encryption_key_rotation() {
469        let key = EncryptionKey::new("test-key", vec![1, 2, 3], "AES-256-GCM");
470        let rotated = key.complete_rotation(vec![4, 5, 6]);
471        assert_eq!(rotated.version, 2);
472        assert!(rotated.rotated_at.is_some());
473        assert!(rotated.is_active());
474    }
475
476    #[test]
477    fn test_encryption_key_retire() {
478        let key = EncryptionKey::new("test-key", vec![1, 2, 3], "AES-256-GCM");
479        let retired = key.retire();
480        assert_eq!(retired.status, "retired");
481        assert!(!retired.is_active());
482    }
483
484    #[test]
485    fn test_external_auth_provider_creation() {
486        let provider = ExternalAuthProviderRecord::new(
487            "tenant_id",
488            "oidc",
489            "auth0",
490            "client_id",
491            "vault/path/to/secret",
492        );
493        assert_eq!(provider.provider_name, "auth0");
494        assert!(provider.is_oidc());
495        assert!(provider.is_enabled());
496    }
497
498    #[test]
499    fn test_external_auth_provider_disable_enable() {
500        let provider = ExternalAuthProviderRecord::new(
501            "tenant_id",
502            "oauth2",
503            "google",
504            "client_id",
505            "vault/path",
506        );
507        let disabled = provider.disable();
508        assert!(!disabled.is_enabled());
509
510        let enabled = disabled.enable();
511        assert!(enabled.is_enabled());
512    }
513
514    #[test]
515    fn test_external_auth_provider_is_oauth2() {
516        let provider = ExternalAuthProviderRecord::new(
517            "tenant_id",
518            "oauth2",
519            "google",
520            "client_id",
521            "vault/path",
522        );
523        assert!(provider.is_oauth2());
524        assert!(!provider.is_oidc());
525    }
526
527    #[test]
528    fn test_oauth_session_creation() {
529        let session = OAuthSessionRecord::new(
530            "user_123",
531            "oidc",
532            "auth0|user_id",
533            "access_token",
534            Utc::now() + chrono::Duration::hours(1),
535        );
536        assert_eq!(session.user_id, "user_123");
537        assert!(!session.is_token_expired());
538    }
539
540    #[test]
541    fn test_oauth_session_with_refresh_token() {
542        let session = OAuthSessionRecord::new(
543            "user_123",
544            "oidc",
545            "auth0|user_id",
546            "access_token",
547            Utc::now() + chrono::Duration::hours(1),
548        )
549        .with_refresh_token("refresh_token");
550        assert_eq!(session.refresh_token_encrypted, Some("refresh_token".to_string()));
551    }
552
553    #[test]
554    fn test_oauth_session_token_refresh() {
555        let session = OAuthSessionRecord::new(
556            "user_123",
557            "oidc",
558            "auth0|user_id",
559            "old_token",
560            Utc::now() + chrono::Duration::hours(1),
561        );
562        let refreshed =
563            session.refresh_tokens("new_token", Utc::now() + chrono::Duration::hours(2));
564        assert_eq!(refreshed.access_token_encrypted, "new_token");
565        assert!(refreshed.last_refreshed.is_some());
566    }
567
568    #[test]
569    fn test_oauth_session_expiry_check() {
570        let expired = OAuthSessionRecord::new(
571            "user_123",
572            "oidc",
573            "auth0|user_id",
574            "access_token",
575            Utc::now() - chrono::Duration::hours(1),
576        );
577        assert!(expired.is_token_expired());
578    }
579
580    #[test]
581    fn test_oauth_session_expiring_soon() {
582        let session = OAuthSessionRecord::new(
583            "user_123",
584            "oidc",
585            "auth0|user_id",
586            "access_token",
587            Utc::now() + chrono::Duration::seconds(30),
588        );
589        assert!(session.is_token_expiring_soon(60));
590    }
591
592    #[test]
593    fn test_schema_migration_creation() {
594        let migration = SchemaMigration::secrets_audit_migration();
595        assert_eq!(migration.filename, "0013_secrets_audit.sql");
596        assert!(!migration.sql_content.is_empty());
597        assert!(migration.sql_content.contains("secret_rotation_audit"));
598    }
599
600    #[test]
601    fn test_schema_migration_contains_all_tables() {
602        let migration = SchemaMigration::secrets_audit_migration();
603        assert!(migration.sql_content.contains("secret_rotation_audit"));
604        assert!(migration.sql_content.contains("encryption_keys"));
605        assert!(migration.sql_content.contains("external_auth_providers"));
606        assert!(migration.sql_content.contains("oauth_sessions"));
607    }
608
609    #[test]
610    fn test_schema_migration_contains_indexes() {
611        let migration = SchemaMigration::secrets_audit_migration();
612        assert!(migration.sql_content.contains("CREATE INDEX"));
613        assert!(migration.sql_content.contains("idx_secret_rotation_audit_name"));
614        assert!(migration.sql_content.contains("idx_oauth_sessions_user"));
615    }
616}
fraiseql_server/secrets/schemas.rs

fraiseql_server/secrets/
schemas.rs
