1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
//! Database improvements migration
//!
//! This migration adds:
//! - `created` column to the user table for tracking user creation time
//! - Performance indices for common query patterns
use sea_orm_migration::prelude::*;
use crate::iden::{CrateIden, OAuth2StateIden, OwnerIden, SessionIden, UserIden};
#[derive(DeriveMigrationName)]
pub struct Migration;
#[async_trait::async_trait]
impl MigrationTrait for Migration {
async fn up(&self, manager: &SchemaManager) -> Result<(), DbErr> {
// ========================================
// 1. Add `created` column to user table
// ========================================
manager
.alter_table(
Table::alter()
.table(UserIden::Table)
.add_column(
ColumnDef::new(UserIden::Created)
.text()
.not_null()
.default(""),
)
.to_owned(),
)
.await?;
// ========================================
// 2. Add performance indices
// ========================================
// Index on krate.name for crate lookups by name
// Note: krate.name has unique_key which creates implicit index in SQLite,
// but explicit index ensures consistent behavior across databases
manager
.create_index(
Index::create()
.if_not_exists()
.name("idx_krate_name")
.table(CrateIden::Table)
.col(CrateIden::Name)
.to_owned(),
)
.await?;
// Index on owner.crate_fk for efficient ownership queries
// (complements the existing composite unique index idx-owner)
manager
.create_index(
Index::create()
.if_not_exists()
.name("idx_owner_crate_fk")
.table(OwnerIden::Table)
.col(OwnerIden::CrateFk)
.to_owned(),
)
.await?;
// Index on session.token for session validation
// Note: session.token has unique_key, but explicit index ensures PostgreSQL performance
manager
.create_index(
Index::create()
.if_not_exists()
.name("idx_session_token")
.table(SessionIden::Table)
.col(SessionIden::Token)
.to_owned(),
)
.await?;
// Index on oauth2_state.state for OAuth2 flow lookups
// Note: oauth2_state.state has unique_key, explicit index for consistency
manager
.create_index(
Index::create()
.if_not_exists()
.name("idx_oauth2_state_state")
.table(OAuth2StateIden::Table)
.col(OAuth2StateIden::State)
.to_owned(),
)
.await?;
// Index on oauth2_state.created for TTL cleanup queries
manager
.create_index(
Index::create()
.if_not_exists()
.name("idx_oauth2_state_created")
.table(OAuth2StateIden::Table)
.col(OAuth2StateIden::Created)
.to_owned(),
)
.await?;
Ok(())
}
async fn down(&self, manager: &SchemaManager) -> Result<(), DbErr> {
// Drop indices in reverse order
manager
.drop_index(
Index::drop()
.name("idx_oauth2_state_created")
.table(OAuth2StateIden::Table)
.to_owned(),
)
.await?;
manager
.drop_index(
Index::drop()
.name("idx_oauth2_state_state")
.table(OAuth2StateIden::Table)
.to_owned(),
)
.await?;
manager
.drop_index(
Index::drop()
.name("idx_session_token")
.table(SessionIden::Table)
.to_owned(),
)
.await?;
manager
.drop_index(
Index::drop()
.name("idx_owner_crate_fk")
.table(OwnerIden::Table)
.to_owned(),
)
.await?;
manager
.drop_index(
Index::drop()
.name("idx_krate_name")
.table(CrateIden::Table)
.to_owned(),
)
.await?;
// Drop created column from user table
manager
.alter_table(
Table::alter()
.table(UserIden::Table)
.drop_column(UserIden::Created)
.to_owned(),
)
.await?;
Ok(())
}
}