//! Convert every table from bigint auto-increment PKs to UUID PKs.
//!
//! Strategy (single transaction):
//! 1. Add `id_uuid UUID NOT NULL DEFAULT gen_random_uuid()` to each parent
//! table — populates existing rows with random UUIDs.
//! 2. Add a nullable `*_uuid` shadow column for every FK; backfill it by
//! joining old int FK against the parent's new uuid column.
//! 3. Drop old FK constraints (they reference old int PKs).
//! 4. Drop old int PKs / FKs, rename the new uuid columns into place.
//! 5. Re-create PRIMARY KEY constraints, FK constraints, and the indexes
//! that used to live on the old int columns.
//! 6. Drop the now-orphaned bigint identity sequences.
//!
//! Existing rows end up with random v4 UUIDs (cheap, native to Postgres);
//! the application generates v7 UUIDs going forward via `Uuid::now_v7()`,
//! so new rows are time-sortable. Mixing v4 (legacy) and v7 (new) is fine
//! — UUIDs are opaque outside ordering, and creation order is already
//! authoritative via `created_at` / `date_uploaded`.
use sea_orm_migration::prelude::*;
use sea_orm_migration::sea_orm::{ConnectionTrait, Statement};
#[derive(DeriveMigrationName)]
pub struct Migration;
const SQL_UP: &str = r#"
-- 1. Add id_uuid to every table.
ALTER TABLE users ADD COLUMN id_uuid UUID NOT NULL DEFAULT gen_random_uuid();
ALTER TABLE registration_tokens ADD COLUMN id_uuid UUID NOT NULL DEFAULT gen_random_uuid();
ALTER TABLE user_sessions ADD COLUMN id_uuid UUID NOT NULL DEFAULT gen_random_uuid();
ALTER TABLE uploads ADD COLUMN id_uuid UUID NOT NULL DEFAULT gen_random_uuid();
ALTER TABLE tags ADD COLUMN id_uuid UUID NOT NULL DEFAULT gen_random_uuid();
ALTER TABLE upload_tags ADD COLUMN id_uuid UUID NOT NULL DEFAULT gen_random_uuid();
ALTER TABLE ocr_results ADD COLUMN id_uuid UUID NOT NULL DEFAULT gen_random_uuid();
ALTER TABLE ai_descriptions ADD COLUMN id_uuid UUID NOT NULL DEFAULT gen_random_uuid();
ALTER TABLE similar_uploads ADD COLUMN id_uuid UUID NOT NULL DEFAULT gen_random_uuid();
ALTER TABLE upload_jobs ADD COLUMN id_uuid UUID NOT NULL DEFAULT gen_random_uuid();
ALTER TABLE api_tokens ADD COLUMN id_uuid UUID NOT NULL DEFAULT gen_random_uuid();
-- 2. Add nullable shadow FK columns and backfill from parent uuid columns.
ALTER TABLE registration_tokens ADD COLUMN created_by_user_id_uuid UUID NULL;
ALTER TABLE registration_tokens ADD COLUMN used_by_user_id_uuid UUID NULL;
UPDATE registration_tokens r SET created_by_user_id_uuid = u.id_uuid
FROM users u WHERE r.created_by_user_id = u.id;
UPDATE registration_tokens r SET used_by_user_id_uuid = u.id_uuid
FROM users u WHERE r.used_by_user_id = u.id;
ALTER TABLE user_sessions ADD COLUMN user_id_uuid UUID NULL;
UPDATE user_sessions s SET user_id_uuid = u.id_uuid FROM users u WHERE s.user_id = u.id;
ALTER TABLE user_sessions ALTER COLUMN user_id_uuid SET NOT NULL;
ALTER TABLE uploads ADD COLUMN user_id_uuid UUID NULL;
UPDATE uploads up SET user_id_uuid = u.id_uuid FROM users u WHERE up.user_id = u.id;
ALTER TABLE uploads ALTER COLUMN user_id_uuid SET NOT NULL;
ALTER TABLE upload_tags ADD COLUMN upload_id_uuid UUID NULL;
ALTER TABLE upload_tags ADD COLUMN tag_id_uuid UUID NULL;
UPDATE upload_tags ut SET upload_id_uuid = up.id_uuid FROM uploads up WHERE ut.upload_id = up.id;
UPDATE upload_tags ut SET tag_id_uuid = t.id_uuid FROM tags t WHERE ut.tag_id = t.id;
ALTER TABLE upload_tags ALTER COLUMN upload_id_uuid SET NOT NULL;
ALTER TABLE upload_tags ALTER COLUMN tag_id_uuid SET NOT NULL;
ALTER TABLE ocr_results ADD COLUMN upload_id_uuid UUID NULL;
UPDATE ocr_results o SET upload_id_uuid = up.id_uuid FROM uploads up WHERE o.upload_id = up.id;
ALTER TABLE ocr_results ALTER COLUMN upload_id_uuid SET NOT NULL;
ALTER TABLE ai_descriptions ADD COLUMN upload_id_uuid UUID NULL;
UPDATE ai_descriptions a SET upload_id_uuid = up.id_uuid FROM uploads up WHERE a.upload_id = up.id;
ALTER TABLE ai_descriptions ALTER COLUMN upload_id_uuid SET NOT NULL;
ALTER TABLE similar_uploads ADD COLUMN upload_id1_uuid UUID NULL;
ALTER TABLE similar_uploads ADD COLUMN upload_id2_uuid UUID NULL;
UPDATE similar_uploads s SET upload_id1_uuid = up.id_uuid FROM uploads up WHERE s.upload_id1 = up.id;
UPDATE similar_uploads s SET upload_id2_uuid = up.id_uuid FROM uploads up WHERE s.upload_id2 = up.id;
ALTER TABLE similar_uploads ALTER COLUMN upload_id1_uuid SET NOT NULL;
ALTER TABLE similar_uploads ALTER COLUMN upload_id2_uuid SET NOT NULL;
ALTER TABLE upload_jobs ADD COLUMN upload_id_uuid UUID NULL;
UPDATE upload_jobs j SET upload_id_uuid = up.id_uuid FROM uploads up WHERE j.upload_id = up.id;
ALTER TABLE upload_jobs ALTER COLUMN upload_id_uuid SET NOT NULL;
ALTER TABLE api_tokens ADD COLUMN user_id_uuid UUID NULL;
UPDATE api_tokens a SET user_id_uuid = u.id_uuid FROM users u WHERE a.user_id = u.id;
ALTER TABLE api_tokens ALTER COLUMN user_id_uuid SET NOT NULL;
-- 3. Drop old FK constraints (they reference int PKs we're about to drop).
ALTER TABLE registration_tokens DROP CONSTRAINT IF EXISTS fk_registration_tokens_created_by_user_id;
ALTER TABLE registration_tokens DROP CONSTRAINT IF EXISTS fk_registration_tokens_used_by_user_id;
ALTER TABLE user_sessions DROP CONSTRAINT IF EXISTS fk_user_sessions_user_id;
ALTER TABLE uploads DROP CONSTRAINT IF EXISTS fk_uploads_user_id;
ALTER TABLE upload_tags DROP CONSTRAINT IF EXISTS fk_upload_tags_upload_id;
ALTER TABLE upload_tags DROP CONSTRAINT IF EXISTS fk_upload_tags_tag_id;
ALTER TABLE ocr_results DROP CONSTRAINT IF EXISTS fk_ocr_results_upload_id;
ALTER TABLE ai_descriptions DROP CONSTRAINT IF EXISTS fk_ai_descriptions_upload_id;
ALTER TABLE similar_uploads DROP CONSTRAINT IF EXISTS fk_similar_uploads_upload_id_1;
ALTER TABLE similar_uploads DROP CONSTRAINT IF EXISTS fk_similar_uploads_upload_id_2;
ALTER TABLE upload_jobs DROP CONSTRAINT IF EXISTS fk_upload_jobs_upload_id;
ALTER TABLE api_tokens DROP CONSTRAINT IF EXISTS fk_api_tokens_user_id;
-- 4. Drop old int columns and rename uuid columns into place.
-- Dropping a column with the bigint identity sequence cleans the
-- sequence up automatically.
ALTER TABLE users DROP COLUMN id; ALTER TABLE users RENAME COLUMN id_uuid TO id;
ALTER TABLE registration_tokens DROP COLUMN id; ALTER TABLE registration_tokens RENAME COLUMN id_uuid TO id;
ALTER TABLE user_sessions DROP COLUMN id; ALTER TABLE user_sessions RENAME COLUMN id_uuid TO id;
ALTER TABLE uploads DROP COLUMN id; ALTER TABLE uploads RENAME COLUMN id_uuid TO id;
ALTER TABLE tags DROP COLUMN id; ALTER TABLE tags RENAME COLUMN id_uuid TO id;
ALTER TABLE upload_tags DROP COLUMN id; ALTER TABLE upload_tags RENAME COLUMN id_uuid TO id;
ALTER TABLE ocr_results DROP COLUMN id; ALTER TABLE ocr_results RENAME COLUMN id_uuid TO id;
ALTER TABLE ai_descriptions DROP COLUMN id; ALTER TABLE ai_descriptions RENAME COLUMN id_uuid TO id;
ALTER TABLE similar_uploads DROP COLUMN id; ALTER TABLE similar_uploads RENAME COLUMN id_uuid TO id;
ALTER TABLE upload_jobs DROP COLUMN id; ALTER TABLE upload_jobs RENAME COLUMN id_uuid TO id;
ALTER TABLE api_tokens DROP COLUMN id; ALTER TABLE api_tokens RENAME COLUMN id_uuid TO id;
ALTER TABLE registration_tokens DROP COLUMN created_by_user_id; ALTER TABLE registration_tokens RENAME COLUMN created_by_user_id_uuid TO created_by_user_id;
ALTER TABLE registration_tokens DROP COLUMN used_by_user_id; ALTER TABLE registration_tokens RENAME COLUMN used_by_user_id_uuid TO used_by_user_id;
ALTER TABLE user_sessions DROP COLUMN user_id; ALTER TABLE user_sessions RENAME COLUMN user_id_uuid TO user_id;
ALTER TABLE uploads DROP COLUMN user_id; ALTER TABLE uploads RENAME COLUMN user_id_uuid TO user_id;
ALTER TABLE upload_tags DROP COLUMN upload_id; ALTER TABLE upload_tags RENAME COLUMN upload_id_uuid TO upload_id;
ALTER TABLE upload_tags DROP COLUMN tag_id; ALTER TABLE upload_tags RENAME COLUMN tag_id_uuid TO tag_id;
ALTER TABLE ocr_results DROP COLUMN upload_id; ALTER TABLE ocr_results RENAME COLUMN upload_id_uuid TO upload_id;
ALTER TABLE ai_descriptions DROP COLUMN upload_id; ALTER TABLE ai_descriptions RENAME COLUMN upload_id_uuid TO upload_id;
ALTER TABLE similar_uploads DROP COLUMN upload_id1; ALTER TABLE similar_uploads RENAME COLUMN upload_id1_uuid TO upload_id1;
ALTER TABLE similar_uploads DROP COLUMN upload_id2; ALTER TABLE similar_uploads RENAME COLUMN upload_id2_uuid TO upload_id2;
ALTER TABLE upload_jobs DROP COLUMN upload_id; ALTER TABLE upload_jobs RENAME COLUMN upload_id_uuid TO upload_id;
ALTER TABLE api_tokens DROP COLUMN user_id; ALTER TABLE api_tokens RENAME COLUMN user_id_uuid TO user_id;
-- 5. Re-create PRIMARY KEY constraints. New rows must supply their own
-- UUID (the application uses Uuid::now_v7), so we drop the default
-- that we used during backfill -- this would otherwise silently swap
-- in a v4 if app code forgot to set the column.
ALTER TABLE users ADD PRIMARY KEY (id); ALTER TABLE users ALTER COLUMN id DROP DEFAULT;
ALTER TABLE registration_tokens ADD PRIMARY KEY (id); ALTER TABLE registration_tokens ALTER COLUMN id DROP DEFAULT;
ALTER TABLE user_sessions ADD PRIMARY KEY (id); ALTER TABLE user_sessions ALTER COLUMN id DROP DEFAULT;
ALTER TABLE uploads ADD PRIMARY KEY (id); ALTER TABLE uploads ALTER COLUMN id DROP DEFAULT;
ALTER TABLE tags ADD PRIMARY KEY (id); ALTER TABLE tags ALTER COLUMN id DROP DEFAULT;
ALTER TABLE upload_tags ADD PRIMARY KEY (id); ALTER TABLE upload_tags ALTER COLUMN id DROP DEFAULT;
ALTER TABLE ocr_results ADD PRIMARY KEY (id); ALTER TABLE ocr_results ALTER COLUMN id DROP DEFAULT;
ALTER TABLE ai_descriptions ADD PRIMARY KEY (id); ALTER TABLE ai_descriptions ALTER COLUMN id DROP DEFAULT;
ALTER TABLE similar_uploads ADD PRIMARY KEY (id); ALTER TABLE similar_uploads ALTER COLUMN id DROP DEFAULT;
ALTER TABLE upload_jobs ADD PRIMARY KEY (id); ALTER TABLE upload_jobs ALTER COLUMN id DROP DEFAULT;
ALTER TABLE api_tokens ADD PRIMARY KEY (id); ALTER TABLE api_tokens ALTER COLUMN id DROP DEFAULT;
-- 6. Re-create FK constraints (same names, new column types).
ALTER TABLE registration_tokens
ADD CONSTRAINT fk_registration_tokens_created_by_user_id
FOREIGN KEY (created_by_user_id) REFERENCES users(id) ON UPDATE CASCADE ON DELETE SET NULL;
ALTER TABLE registration_tokens
ADD CONSTRAINT fk_registration_tokens_used_by_user_id
FOREIGN KEY (used_by_user_id) REFERENCES users(id) ON UPDATE CASCADE ON DELETE SET NULL;
ALTER TABLE user_sessions
ADD CONSTRAINT fk_user_sessions_user_id
FOREIGN KEY (user_id) REFERENCES users(id) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE uploads
ADD CONSTRAINT fk_uploads_user_id
FOREIGN KEY (user_id) REFERENCES users(id) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE upload_tags
ADD CONSTRAINT fk_upload_tags_upload_id
FOREIGN KEY (upload_id) REFERENCES uploads(id) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE upload_tags
ADD CONSTRAINT fk_upload_tags_tag_id
FOREIGN KEY (tag_id) REFERENCES tags(id) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE ocr_results
ADD CONSTRAINT fk_ocr_results_upload_id
FOREIGN KEY (upload_id) REFERENCES uploads(id) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE ai_descriptions
ADD CONSTRAINT fk_ai_descriptions_upload_id
FOREIGN KEY (upload_id) REFERENCES uploads(id) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE similar_uploads
ADD CONSTRAINT fk_similar_uploads_upload_id_1
FOREIGN KEY (upload_id1) REFERENCES uploads(id) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE similar_uploads
ADD CONSTRAINT fk_similar_uploads_upload_id_2
FOREIGN KEY (upload_id2) REFERENCES uploads(id) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE upload_jobs
ADD CONSTRAINT fk_upload_jobs_upload_id
FOREIGN KEY (upload_id) REFERENCES uploads(id) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE api_tokens
ADD CONSTRAINT fk_api_tokens_user_id
FOREIGN KEY (user_id) REFERENCES users(id) ON UPDATE CASCADE ON DELETE CASCADE;
-- 7. Re-create the secondary indexes that were on FK columns. (Postgres
-- drops these automatically when their underlying column is dropped,
-- so they need recreating against the new uuid columns.) Composite
-- UNIQUE indexes that bind two columns are also recreated here.
CREATE INDEX IF NOT EXISTS idx_user_sessions_user_id ON user_sessions(user_id);
CREATE INDEX IF NOT EXISTS idx_uploads_user_id ON uploads(user_id);
CREATE INDEX IF NOT EXISTS idx_upload_tags_upload_id ON upload_tags(upload_id);
CREATE INDEX IF NOT EXISTS idx_upload_tags_tag_id ON upload_tags(tag_id);
CREATE INDEX IF NOT EXISTS idx_ocr_results_upload_id ON ocr_results(upload_id);
CREATE INDEX IF NOT EXISTS idx_ai_descriptions_upload_id ON ai_descriptions(upload_id);
CREATE INDEX IF NOT EXISTS idx_similar_uploads_upload1 ON similar_uploads(upload_id1);
CREATE INDEX IF NOT EXISTS idx_similar_uploads_upload2 ON similar_uploads(upload_id2);
CREATE INDEX IF NOT EXISTS idx_api_tokens_user_id ON api_tokens(user_id);
CREATE UNIQUE INDEX IF NOT EXISTS unique_file_hash_user ON uploads(file_hash, user_id);
CREATE UNIQUE INDEX IF NOT EXISTS unique_upload_tag ON upload_tags(upload_id, tag_id);
CREATE UNIQUE INDEX IF NOT EXISTS unique_upload_job_kind ON upload_jobs(upload_id, kind);
CREATE UNIQUE INDEX IF NOT EXISTS unique_similarity_pair ON similar_uploads(upload_id1, upload_id2);
"#;
#[async_trait::async_trait]
impl MigrationTrait for Migration {
async fn up(&self, manager: &SchemaManager) -> Result<(), DbErr> {
// Postgres' prepared-statement protocol only accepts one statement
// per Execute, so split on `;` and run each individually. The whole
// migration still runs in a single transaction (sea-orm-migration
// wraps `up()` in one).
let db = manager.get_connection();
let backend = db.get_database_backend();
// Strip line comments first; otherwise a `--` block sitting at the
// top of a chunk after split-on-`;` would shadow the SQL on the
// following line and silently skip a real statement.
let cleaned: String = SQL_UP
.lines()
.map(|l| match l.find("--") {
Some(i) => &l[..i],
None => l,
})
.collect::<Vec<_>>()
.join("\n");
for stmt in cleaned.split(';') {
let trimmed = stmt.trim();
if trimmed.is_empty() {
continue;
}
db.execute_raw(Statement::from_string(backend, trimmed.to_string()))
.await?;
}
Ok(())
}
async fn down(&self, _manager: &SchemaManager) -> Result<(), DbErr> {
// No-op: reversing a swap-in-place PK migration would mean
// re-inventing bigint surrogates and rewriting every FK back. We
// squash forward only — to roll back, drop the database and
// re-run earlier migrations to a fresh state.
Err(DbErr::Custom(
"uuid_primary_keys is forward-only; restore from backup or drop+remigrate".into(),
))
}
}
⭐
Log in to mark this repository.
15.6 KiB · text
History
c20d4f7