use sea_orm_migration::prelude::*;
#[derive(DeriveMigrationName)]
pub struct Migration;
#[async_trait::async_trait]
impl MigrationTrait for Migration {
async fn up(&self, manager: &SchemaManager) -> Result<(), DbErr> {
// SQLite cannot drop NOT NULL constraints, so we rebuild the table.
// This follows the pattern from migration 000019 (ap_reactions rebuild).
//
// Key changes:
// - creator_id becomes nullable (for remote-created tickets)
// - Add remote_actor_id (FK to ap_remote_actors, nullable)
// - Add remote_offer_id (unique, for idempotency on inbound Offers)
// - Add resolve_activity_url (for Undo(Resolve) correlation)
// - CHECK constraint: exactly one of creator_id or remote_actor_id
//
// The FTS5 triggers must be dropped before the rebuild and recreated after.
// The ticket_search FTS index is rebuilt from the new table.
manager
.get_connection()
.execute_unprepared(
r#"
-- Drop FTS triggers before rebuild
DROP TRIGGER IF EXISTS tickets_search_after_insert;
DROP TRIGGER IF EXISTS tickets_search_after_update;
DROP TRIGGER IF EXISTS tickets_search_after_delete;
-- Create new table with federation columns
CREATE TABLE tickets_new (
id INTEGER PRIMARY KEY AUTOINCREMENT,
uuid TEXT NOT NULL UNIQUE,
repository_id INTEGER NOT NULL,
creator_id INTEGER,
remote_actor_id INTEGER,
remote_offer_id TEXT UNIQUE,
resolve_activity_url TEXT,
number INTEGER NOT NULL,
summary TEXT NOT NULL,
description TEXT,
state TEXT NOT NULL DEFAULT 'open',
created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (repository_id) REFERENCES repositories(id) ON DELETE CASCADE,
FOREIGN KEY (creator_id) REFERENCES users(id) ON DELETE SET NULL,
FOREIGN KEY (remote_actor_id) REFERENCES ap_remote_actors(id) ON DELETE CASCADE,
CHECK ((creator_id IS NOT NULL) != (remote_actor_id IS NOT NULL))
);
-- Copy data from old table
INSERT INTO tickets_new (
id, uuid, repository_id, creator_id, number, summary, description,
state, created_at, updated_at
)
SELECT id, uuid, repository_id, creator_id, number, summary, description,
state, created_at, updated_at
FROM tickets;
-- Drop old table
DROP TABLE tickets;
-- Rename new table
ALTER TABLE tickets_new RENAME TO tickets;
-- Recreate unique index
CREATE UNIQUE INDEX index_tickets_on_repository_id_and_number
ON tickets (repository_id, number);
-- Recreate FTS triggers
CREATE TRIGGER tickets_search_after_insert
AFTER INSERT ON tickets
BEGIN
INSERT INTO ticket_search(rowid, summary, description)
VALUES (new.id, new.summary, new.description);
END;
CREATE TRIGGER tickets_search_after_update
AFTER UPDATE ON tickets
BEGIN
INSERT INTO ticket_search(ticket_search, rowid, summary, description)
VALUES ('delete', old.id, old.summary, old.description);
INSERT INTO ticket_search(rowid, summary, description)
VALUES (new.id, new.summary, new.description);
END;
CREATE TRIGGER tickets_search_after_delete
AFTER DELETE ON tickets
BEGIN
INSERT INTO ticket_search(ticket_search, rowid, summary, description)
VALUES ('delete', old.id, old.summary, old.description);
END;
-- Rebuild FTS index from new table
INSERT INTO ticket_search(ticket_search) VALUES('rebuild');
"#,
)
.await?;
Ok(())
}
async fn down(&self, manager: &SchemaManager) -> Result<(), DbErr> {
// Revert to original schema (creator_id NOT NULL, no federation columns)
manager
.get_connection()
.execute_unprepared(
r#"
-- Drop FTS triggers
DROP TRIGGER IF EXISTS tickets_search_after_insert;
DROP TRIGGER IF EXISTS tickets_search_after_update;
DROP TRIGGER IF EXISTS tickets_search_after_delete;
-- Recreate original table
CREATE TABLE tickets_old (
id INTEGER PRIMARY KEY AUTOINCREMENT,
uuid TEXT NOT NULL UNIQUE,
repository_id INTEGER NOT NULL,
creator_id INTEGER NOT NULL,
number INTEGER NOT NULL,
summary TEXT NOT NULL,
description TEXT,
state TEXT NOT NULL DEFAULT 'open',
created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (repository_id) REFERENCES repositories(id) ON DELETE CASCADE,
FOREIGN KEY (creator_id) REFERENCES users(id) ON DELETE SET NULL
);
-- Copy only locally-created tickets (creator_id IS NOT NULL)
INSERT INTO tickets_old (
id, uuid, repository_id, creator_id, number, summary, description,
state, created_at, updated_at
)
SELECT id, uuid, repository_id, creator_id, number, summary, description,
state, created_at, updated_at
FROM tickets
WHERE creator_id IS NOT NULL;
-- Drop new table
DROP TABLE tickets;
-- Rename back
ALTER TABLE tickets_old RENAME TO tickets;
-- Recreate index
CREATE UNIQUE INDEX index_tickets_on_repository_id_and_number
ON tickets (repository_id, number);
-- Recreate FTS triggers
CREATE TRIGGER tickets_search_after_insert
AFTER INSERT ON tickets
BEGIN
INSERT INTO ticket_search(rowid, summary, description)
VALUES (new.id, new.summary, new.description);
END;
CREATE TRIGGER tickets_search_after_update
AFTER UPDATE ON tickets
BEGIN
INSERT INTO ticket_search(ticket_search, rowid, summary, description)
VALUES ('delete', old.id, old.summary, old.description);
INSERT INTO ticket_search(rowid, summary, description)
VALUES (new.id, new.summary, new.description);
END;
CREATE TRIGGER tickets_search_after_delete
AFTER DELETE ON tickets
BEGIN
INSERT INTO ticket_search(ticket_search, rowid, summary, description)
VALUES ('delete', old.id, old.summary, old.description);
END;
INSERT INTO ticket_search(ticket_search) VALUES('rebuild');
"#,
)
.await?;
Ok(())
}
}