FastAPI Soft Delete 구현하기 by PostgreSQL trigger

def upgrade() -> None:
    op.create_table(
        "users",
        sa.Column("id", sa.Integer, primary_key=True, index=True),
        sa.Column("email", sa.String, unique=True, index=True),
        sa.Column("password", sa.String),
        sa.Column("is_active", sa.Boolean, default=True),
        sa.Column("is_superuser", sa.Boolean, default=False),
        sa.Column("is_removable", sa.Boolean, default=False),
        sa.Column("created_at", sa.DateTime, server_default=sa.func.now()),
        sa.Column(
            "updated_at",
            sa.DateTime,
            server_default=sa.func.now(),
            onupdate=sa.func.now(),
        ),
        sa.Column("deleted_at", sa.DateTime, nullable=True),
    )
 
    op.execute("""
        CREATE OR REPLACE FUNCTION "public"._pgtrigger_should_ignore(
            trigger_name NAME
        )
        RETURNS BOOLEAN AS $$
            DECLARE
                _pgtrigger_ignore TEXT[];
                _result BOOLEAN;
            BEGIN
                BEGIN
                    SELECT INTO _pgtrigger_ignore
                        CURRENT_SETTING('pgtrigger.ignore');
                    EXCEPTION WHEN OTHERS THEN
                END;
                IF _pgtrigger_ignore IS NOT NULL THEN
                    SELECT trigger_name = ANY(_pgtrigger_ignore)
                    INTO _result;
                    RETURN _result;
                ELSE
                    RETURN FALSE;
                END IF;
            END;
        $$ LANGUAGE plpgsql;
        """)
 
    # 활성화된 사용자에 대해서만 트리거가 실행되도록 함
    op.execute("""
        CREATE OR REPLACE FUNCTION pgtrigger_soft_delete_78625()
        RETURNS TRIGGER AS $$
            BEGIN
                IF ("public"._pgtrigger_should_ignore(TG_NAME) IS TRUE) THEN
                    IF (TG_OP = 'DELETE') THEN
                        RETURN OLD;
                    ELSE
                        RETURN NEW;
                    END IF;
                END IF;
                IF (OLD.is_removable IS TRUE) THEN
                    RETURN OLD;
                END IF;
                UPDATE "users" SET is_active = FALSE, is_removable = TRUE, deleted_at = now()  WHERE "id" = OLD."id" AND is_removable = FALSE; RETURN NULL;
            END;
        $$ LANGUAGE plpgsql;
 
        DROP TRIGGER IF EXISTS pgtrigger_soft_delete_78625 ON "users";
        CREATE TRIGGER pgtrigger_soft_delete_78625
            BEFORE DELETE ON "users"
 
            FOR EACH ROW
            EXECUTE PROCEDURE pgtrigger_soft_delete_78625();
 
        COMMENT ON TRIGGER pgtrigger_soft_delete_78625 ON "users" IS '6f7f1bbad00e7d3167219959189d38b83e5f7668';
        """)

from sqlalchemy import delete
...
 
async def delete_user(self, db, user_id):
    # self.model은 User 모델임
    query = delete(self.model).where(self.model.id == user_id)
    await db.execute(query)
    await db.commit()
    return