Rust Web Server with Axum 0.8: Log In with GitHub (OAuth 2)

http://localhost:8080/auth/github/callback

let github_client_id = std::env::var("GITHUB_CLIENT_ID").expect("GITHUB_CLIENT_ID must be set");
let github_client_secret = std::env::var("GITHUB_CLIENT_SECRET").expect("GITHUB_CLIENT_SECRET must be set");
let github_redirect_url = std::env::var("GITHUB_REDIRECT_URL")
    .unwrap_or("http://localhost:8080/auth/github/callback".to_string());

cargo add oauth2
cargo add reqwest@0.12 --no-default-features --features json,rustls-tls

type GithubClient = BasicClient<EndpointSet, EndpointNotSet, EndpointNotSet, EndpointNotSet, EndpointSet>;

fn github_client() -> GithubClient {
    let cfg = config();
    BasicClient::new(ClientId::new(cfg.github_client_id.clone()))
        .set_client_secret(ClientSecret::new(cfg.github_client_secret.clone()))
        .set_auth_uri(AuthUrl::new("https://github.com/login/oauth/authorize".to_string()).unwrap())
        .set_token_uri(TokenUrl::new("https://github.com/login/oauth/access_token".to_string()).unwrap())
        .set_redirect_uri(RedirectUrl::new(cfg.github_redirect_url.clone()).unwrap())
}

pub async fn github_start(jar: CookieJar) -> (CookieJar, Redirect) {
    let (challenge, verifier) = PkceCodeChallenge::new_random_sha256();
    let (url, state) = github_client()
        .authorize_url(CsrfToken::new_random)
        .add_scope(Scope::new("read:user".to_string()))
        .set_pkce_challenge(challenge)
        .url();
    let cookie = Cookie::build(("oauth", format!("{}:{}", state.secret(), verifier.secret())))
        .http_only(true)
        .same_site(SameSite::Lax)
        .path("/auth/github")
        .max_age(time::Duration::minutes(10))
        .build();
    (jar.add(cookie), Redirect::to(url.as_str()))
}

pub async fn github_callback(jar: CookieJar, Query(cb): Query<Callback>) -> Result<Json<Value>, ApiError> {
    let saved = jar.get("oauth").ok_or(ApiError::BadOAuthState)?.value().to_string();
    let (state, verifier) = saved.split_once(':').ok_or(ApiError::BadOAuthState)?;
    if state != cb.state {
        return Err(ApiError::BadOAuthState);
    }
    let http = reqwest::ClientBuilder::new()
        .redirect(reqwest::redirect::Policy::none())
        .build()?;
    let token = github_client()
        .exchange_code(AuthorizationCode::new(cb.code))
        .set_pkce_verifier(PkceCodeVerifier::new(verifier.to_string()))
        .request_async(&http)
        .await
        .map_err(|e| ApiError::Upstream(e.to_string()))?;
    let gh: GithubUser = http
        .get("https://api.github.com/user")
        .bearer_auth(token.access_token().secret())
        .send()
        .await?
        .error_for_status()?
        .json()
        .await?;
    Ok(Json(json!({ "github_id": gh.id, "login": gh.login })))
}

.route("/auth/github", get(github::github_start))
.route("/auth/github/callback", get(github::github_callback))

cargo run

HTTP status client error (403 Forbidden) for url (https://api.github.com/user)

let http = reqwest::ClientBuilder::new()
    .redirect(reqwest::redirect::Policy::none())
    .user_agent("blog-api")
    .build()?;

sqlx migrate add add_github_id

ALTER TABLE users ADD COLUMN github_id INTEGER;
CREATE UNIQUE INDEX users_github_id ON users(github_id);

sqlx migrate run

async fn find_or_create_github_user(&self, github_id: i64, login: &str) -> sqlx::Result<User> {
    if let Some(user) = sqlx::query_as!(
        User,
        "SELECT id, email, role, created_at FROM users WHERE github_id = ?",
        github_id
    )
    .fetch_optional(&self.pool)
    .await?
    {
        return Ok(user);
    }
    let email = format!("{login}@users.noreply.github.com");
    sqlx::query_as!(
        User,
        "INSERT INTO users (email, password_hash, github_id) VALUES (?, '!', ?) RETURNING id, email, role, created_at",
        email,
        github_id
    )
    .fetch_one(&self.pool)
    .await
}

pub async fn github_callback<R: Repo>(State(repo): State<R>, jar: CookieJar, Query(cb): Query<Callback>) -> Result<(CookieJar, Json<Value>), ApiError> {
    // state check, code exchange and GitHub user as before ...
    let user = repo.find_or_create_github_user(gh.id, &gh.login).await?;
    let access = auth::make_token(user.id, &user.role, &config().jwt_secret)?;
    let refresh = auth::new_refresh_token();
    repo.store_refresh(&auth::hash_token(&refresh), user.id, auth::now() + auth::REFRESH_SECS).await?;
    Ok((jar.remove(Cookie::build("oauth").path("/auth/github")).add(refresh_cookie(refresh)), Json(json!({ "token": access }))))
}

cargo run

http://localhost:8080/auth/github/callback?code=x&state=wrong

HTTP/1.1 400 Bad Request

{"error":"invalid OAuth state"}

-- Add migration script here
ALTER TABLE users ADD COLUMN github_id INTEGER;
CREATE UNIQUE INDEX users_github_id ON users(github_id);

use std::sync::OnceLock;

pub struct Config {
    pub host: String,
    pub port: u16,
    pub database_url: String,
    pub jwt_secret: String,
    pub github_client_id: String,
    pub github_client_secret: String,
    pub github_redirect_url: String,
}

impl Config {
    fn from_env() -> Config {
        dotenvy::dotenv().ok();
        let host = std::env::var("HOST").unwrap_or("0.0.0.0".to_string());
        let port = std::env::var("PORT").unwrap_or("3000".to_string());
        let port = port
            .parse()
            .unwrap_or_else(|e| panic!("PORT {port:?} is not a port number: {e}"));
        let database_url = std::env::var("DATABASE_URL").unwrap_or("sqlite://blog.db".to_string());
        let jwt_secret = std::env::var("JWT_SECRET").expect("JWT_SECRET must be set");
        let github_client_id = std::env::var("GITHUB_CLIENT_ID").expect("GITHUB_CLIENT_ID must be set");
        let github_client_secret = std::env::var("GITHUB_CLIENT_SECRET").expect("GITHUB_CLIENT_SECRET must be set");
        let github_redirect_url = std::env::var("GITHUB_REDIRECT_URL")
            .unwrap_or("http://localhost:8080/auth/github/callback".to_string());
        Config { host, port, database_url, jwt_secret, github_client_id, github_client_secret, github_redirect_url }
    }
}

static CONFIG: OnceLock<Config> = OnceLock::new();

pub fn config() -> &'static Config {
    CONFIG.get_or_init(Config::from_env)
}

use axum::extract::rejection::{JsonRejection, QueryRejection};
use axum::http::StatusCode;
use axum::response::{IntoResponse, Response};
use axum::Json;
use serde_json::json;
use thiserror::Error;
use std::collections::BTreeMap;

#[derive(Debug, Error)]
pub enum ApiError {
    #[error("post {0} not found")]
    NotFound(i64),
    #[error(transparent)]
    BadJson(#[from] JsonRejection),
    #[error(transparent)]
    BadQuery(#[from] QueryRejection),
    #[error("database error")]
    Database(#[from] sqlx::Error),
    #[error("{0}")]
    Conflict(String),
    #[error("validation failed")]
    Validation(#[from] validator::ValidationErrors),
    #[error("internal error")]
    Hash(#[from] argon2::password_hash::Error),
    #[error("internal error")]
    Jwt(#[from] jsonwebtoken::errors::Error),
    #[error("invalid email or password")]
    Unauthorized,
    #[error("you are not allowed to do this")]
    Forbidden,
    #[error("invalid OAuth state")]
    BadOAuthState,
    #[error("login with GitHub failed")]
    Upstream(String),
    #[error("login with GitHub failed")]
    Http(#[from] reqwest::Error),
}

impl IntoResponse for ApiError {
    fn into_response(self) -> Response {
        tracing::warn!(error = %self, "request failed");
        let status = match &self {
            ApiError::NotFound(_) => StatusCode::NOT_FOUND,
            ApiError::Conflict(_) => StatusCode::CONFLICT,
            ApiError::Unauthorized => StatusCode::UNAUTHORIZED,
            ApiError::Forbidden => StatusCode::FORBIDDEN,
            ApiError::BadOAuthState => StatusCode::BAD_REQUEST,
            ApiError::Upstream(err) => {
                tracing::error!(%err, "github token exchange failed");
                StatusCode::BAD_GATEWAY
            }
            ApiError::Http(err) => {
                tracing::error!(%err, "github request failed");
                StatusCode::BAD_GATEWAY
            }
            ApiError::BadJson(rejection) => rejection.status(),
            ApiError::BadQuery(rejection) => rejection.status(),
            ApiError::Validation(errors) => {
                let fields: BTreeMap<_, Vec<String>> = errors
                    .field_errors()
                    .iter()
                    .map(|(field, errs)| {
                        let msgs = errs.iter().map(|e| e.message.clone().unwrap_or(e.code.clone()).to_string()).collect();
                        (field.to_string(), msgs)
                    })
                    .collect();
                return (StatusCode::UNPROCESSABLE_ENTITY, Json(json!({ "error": "validation failed", "fields": fields })))
                    .into_response();
            }
            ApiError::Hash(err) => {
                tracing::error!(?err, "password hashing failed");
                StatusCode::INTERNAL_SERVER_ERROR
            }
            ApiError::Jwt(err) => {
                tracing::error!(?err, "token error");
                StatusCode::INTERNAL_SERVER_ERROR
            }
            ApiError::Database(err) => {
                tracing::error!(?err, "database error");
                StatusCode::INTERNAL_SERVER_ERROR
            }
        };
        let message = match &self {
            ApiError::BadJson(rejection) => rejection.body_text(),
            ApiError::BadQuery(rejection) => rejection.body_text(),
            other => other.to_string(),
        };
        (status, Json(json!({ "error": message }))).into_response()
    }
}

use axum::extract::{Query, State};
use axum::response::Redirect;
use axum::Json;
use axum_extra::extract::cookie::{Cookie, CookieJar, SameSite};
use oauth2::basic::BasicClient;
use oauth2::{
    AuthUrl, AuthorizationCode, ClientId, ClientSecret, CsrfToken, EndpointNotSet, EndpointSet, PkceCodeChallenge,
    PkceCodeVerifier, RedirectUrl, Scope, TokenResponse, TokenUrl,
};
use serde::Deserialize;
use serde_json::{json, Value};

use crate::auth;
use crate::config::config;
use crate::error::ApiError;

use crate::handlers::refresh_cookie;
use crate::repo::Repo;
type GithubClient = BasicClient<EndpointSet, EndpointNotSet, EndpointNotSet, EndpointNotSet, EndpointSet>;

fn github_client() -> GithubClient {
    let cfg = config();
    BasicClient::new(ClientId::new(cfg.github_client_id.clone()))
        .set_client_secret(ClientSecret::new(cfg.github_client_secret.clone()))
        .set_auth_uri(AuthUrl::new("https://github.com/login/oauth/authorize".to_string()).unwrap())
        .set_token_uri(TokenUrl::new("https://github.com/login/oauth/access_token".to_string()).unwrap())
        .set_redirect_uri(RedirectUrl::new(cfg.github_redirect_url.clone()).unwrap())
}

pub async fn github_start(jar: CookieJar) -> (CookieJar, Redirect) {
    let (challenge, verifier) = PkceCodeChallenge::new_random_sha256();
    let (url, state) = github_client()
        .authorize_url(CsrfToken::new_random)
        .add_scope(Scope::new("read:user".to_string()))
        .set_pkce_challenge(challenge)
        .url();
    let cookie = Cookie::build(("oauth", format!("{}:{}", state.secret(), verifier.secret())))
        .http_only(true)
        .same_site(SameSite::Lax)
        .path("/auth/github")
        .max_age(time::Duration::minutes(10))
        .build();
    (jar.add(cookie), Redirect::to(url.as_str()))
}

#[derive(Deserialize)]
pub struct Callback {
    code: String,
    state: String,
}

#[derive(Deserialize)]
struct GithubUser {
    id: i64,
    login: String,
}

pub async fn github_callback<R: Repo>(State(repo): State<R>, jar: CookieJar, Query(cb): Query<Callback>) -> Result<(CookieJar, Json<Value>), ApiError> {
    let saved = jar.get("oauth").ok_or(ApiError::BadOAuthState)?.value().to_string();
    let (state, verifier) = saved.split_once(':').ok_or(ApiError::BadOAuthState)?;
    if state != cb.state {
        return Err(ApiError::BadOAuthState);
    }
    let http = reqwest::ClientBuilder::new()
        .redirect(reqwest::redirect::Policy::none())
        .user_agent("blog-api")
        .build()?;
    let token = github_client()
        .exchange_code(AuthorizationCode::new(cb.code))
        .set_pkce_verifier(PkceCodeVerifier::new(verifier.to_string()))
        .request_async(&http)
        .await
        .map_err(|e| ApiError::Upstream(e.to_string()))?;
    let gh: GithubUser = http
        .get("https://api.github.com/user")
        .bearer_auth(token.access_token().secret())
        .send()
        .await?
        .error_for_status()?
        .json()
        .await?;
    let user = repo.find_or_create_github_user(gh.id, &gh.login).await?;
    let access = auth::make_token(user.id, &user.role, &config().jwt_secret)?;
    let refresh = auth::new_refresh_token();
    repo.store_refresh(&auth::hash_token(&refresh), user.id, auth::now() + auth::REFRESH_SECS).await?;
    Ok((jar.remove(Cookie::build("oauth").path("/auth/github")).add(refresh_cookie(refresh)), Json(json!({ "token": access }))))
}

use axum::extract::{rejection::QueryRejection, Path, Query, State};
use axum::{http::StatusCode, Json};

use crate::error::ApiError;
use crate::extract::{AdminUser, AuthUser, Role, ValidJson};
use crate::models::{Comment, Hit, ListFilter, ListParams, NewComment, NewPost, NewUser, Page, Post, PostWithTags, SearchParams, User};
use crate::repo::Repo;
use crate::auth;
use crate::config::config;
use serde::Deserialize;
use serde_json::{json, Value};
use axum_extra::extract::cookie::{Cookie, CookieJar, SameSite};

#[tracing::instrument(skip_all)]
pub async fn create_post<R: Repo>(
    State(repo): State<R>,
    user: AuthUser,
    ValidJson(new): ValidJson<NewPost>,
) -> Result<(StatusCode, Json<PostWithTags>), ApiError> {
    let post = repo.create(user.id, new).await.map_err(|err| match err.as_database_error() {
        Some(e) if e.is_unique_violation() => ApiError::Conflict("a post with this title already exists".into()),
        _ => ApiError::Database(err),
    })?;
    tracing::debug!(id = post.post.id, "created");
    Ok((StatusCode::CREATED, Json(post)))
}

pub async fn get_post<R: Repo>(State(repo): State<R>, Path(id): Path<i64>) -> Result<Json<Post>, ApiError> {
    let post = repo.get(id).await?.ok_or(ApiError::NotFound(id))?;
    Ok(Json(post))
}

pub async fn list_posts<R: Repo>(
    State(repo): State<R>,
    params: Result<Query<ListParams>, QueryRejection>,
) -> Result<Json<Page<Post>>, ApiError> {
    let Query(p) = params?;
    let page = p.page.unwrap_or(1).max(1);
    let per_page = p.per_page.unwrap_or(10).clamp(1, 100);
    let (items, total) = repo.list(ListFilter { page, per_page, tag: p.tag, sort: p.sort }).await?;
    Ok(Json(Page { items, page, per_page, total }))
}

pub async fn update_post<R: Repo>(
    State(repo): State<R>,
    Path(id): Path<i64>,
    user: AuthUser,
    ValidJson(new): ValidJson<NewPost>,
) -> Result<Json<Post>, ApiError> {
    let post = repo.get(id).await?.ok_or(ApiError::NotFound(id))?;
    if !can_edit(&user, &post) {
        return Err(ApiError::Forbidden);
    }
    let post = repo.update(id, new).await?.ok_or(ApiError::NotFound(id))?;
    Ok(Json(post))
}

pub async fn delete_post<R: Repo>(State(repo): State<R>, Path(id): Path<i64>, user: AuthUser) -> Result<StatusCode, ApiError> {
    let post = repo.get(id).await?.ok_or(ApiError::NotFound(id))?;
    if !can_edit(&user, &post) {
        return Err(ApiError::Forbidden);
    }
    repo.delete(id).await?;
    Ok(StatusCode::NO_CONTENT)
}

pub async fn list_comments<R: Repo>(
    State(repo): State<R>,
    Path(id): Path<i64>,
) -> Result<Json<Vec<Comment>>, ApiError> {
    repo.get(id).await?.ok_or(ApiError::NotFound(id))?;
    Ok(Json(repo.list_comments(id).await?))
}

pub async fn add_comment<R: Repo>(
    State(repo): State<R>,
    Path(id): Path<i64>,
    _user: AuthUser,
    ValidJson(new): ValidJson<NewComment>,
) -> Result<(StatusCode, Json<Comment>), ApiError> {
    let comment = repo.add_comment(id, new).await.map_err(|err| {
        match err.as_database_error() {
            Some(db_err) if db_err.is_foreign_key_violation() => ApiError::NotFound(id),
            _ => ApiError::Database(err),
        }
    })?;
    Ok((StatusCode::CREATED, Json(comment)))
}

pub async fn search_posts<R: Repo>(
    State(repo): State<R>,
    Query(p): Query<SearchParams>,
) -> Result<Json<Vec<Hit>>, ApiError> {
    Ok(Json(repo.search(&p.q).await?))
}

pub async fn restore_post<R: Repo>(State(repo): State<R>, Path(id): Path<i64>) -> Result<StatusCode, ApiError> {
    if !repo.restore(id).await? {
        return Err(ApiError::NotFound(id));
    }
    Ok(StatusCode::OK)
}

pub async fn signup<R: Repo>(
    State(repo): State<R>,
    ValidJson(new): ValidJson<NewUser>,
) -> Result<(StatusCode, Json<User>), ApiError> {
    let hash = crate::auth::hash_password(new.password).await?;
    let user = repo.create_user(&new.email, &hash).await.map_err(|err| match err.as_database_error() {
        Some(e) if e.is_unique_violation() => ApiError::Conflict("email already registered".into()),
        _ => ApiError::Database(err),
    })?;
    Ok((StatusCode::CREATED, Json(user)))
}

#[derive(Deserialize)]
pub struct Login {
    pub email: String,
    pub password: String,
}

pub fn refresh_cookie(token: String) -> Cookie<'static> {
    Cookie::build(("refresh", token))
        .http_only(true)
        .secure(true)
        .same_site(SameSite::Strict)
        .path("/auth")
        .max_age(time::Duration::days(30))
        .build()
}

pub async fn login<R: Repo>(
    State(repo): State<R>,
    jar: CookieJar,
    Json(body): Json<Login>,
) -> Result<(CookieJar, Json<Value>), ApiError> {
    let user = repo.find_user_by_email(&body.email).await?.ok_or(ApiError::Unauthorized)?;
    if !auth::verify_password(body.password, user.password_hash).await? {
        return Err(ApiError::Unauthorized);
    }
    let access = auth::make_token(user.id, &user.role, &config().jwt_secret)?;
    let refresh = auth::new_refresh_token();
    repo.store_refresh(&auth::hash_token(&refresh), user.id, auth::now() + auth::REFRESH_SECS).await?;
    Ok((jar.add(refresh_cookie(refresh)), Json(json!({ "token": access }))))
}

pub async fn me<R: Repo>(State(repo): State<R>, user: AuthUser) -> Result<Json<User>, ApiError> {
    let user = repo.get_user(user.id).await?.ok_or(ApiError::Unauthorized)?;
    Ok(Json(user))
}

pub async fn refresh<R: Repo>(State(repo): State<R>, jar: CookieJar) -> Result<(CookieJar, Json<Value>), ApiError> {
    let old = jar.get("refresh").ok_or(ApiError::Unauthorized)?.value().to_string();
    let user_id = repo.take_refresh(&auth::hash_token(&old), auth::now()).await?.ok_or(ApiError::Unauthorized)?;
    let user = repo.get_user(user_id).await?.ok_or(ApiError::Unauthorized)?;
    let access = auth::make_token(user.id, &user.role, &config().jwt_secret)?;
    let new = auth::new_refresh_token();
    repo.store_refresh(&auth::hash_token(&new), user.id, auth::now() + auth::REFRESH_SECS).await?;
    Ok((jar.add(refresh_cookie(new)), Json(json!({ "token": access }))))
}

pub async fn logout<R: Repo>(State(repo): State<R>, jar: CookieJar) -> Result<(CookieJar, StatusCode), ApiError> {
    if let Some(cookie) = jar.get("refresh") {
        repo.delete_refresh(&auth::hash_token(cookie.value())).await?;
    }
    Ok((jar.remove(Cookie::build("refresh").path("/auth")), StatusCode::NO_CONTENT))
}

pub fn can_edit(user: &AuthUser, post: &Post) -> bool {
    user.role == Role::Admin || post.author_id == Some(user.id)
}

pub async fn list_users<R: Repo>(State(repo): State<R>, _admin: AdminUser) -> Result<Json<Vec<User>>, ApiError> {
    Ok(Json(repo.list_users().await?))
}

mod auth;
mod config;
mod error;
mod extract;
mod github;
mod handlers;
mod models;
mod repo;

use axum::{routing::{get, post}, Router};
use repo::{Repo, SqliteRepo};
use sqlx::sqlite::{SqliteConnectOptions, SqliteJournalMode, SqlitePoolOptions};
use std::str::FromStr;
use tower_http::trace::TraceLayer;
use tracing_subscriber::EnvFilter;

pub fn app<R: Repo>(repo: R) -> Router {
    Router::new()
        .route("/posts", get(handlers::list_posts::<R>).post(handlers::create_post::<R>))
        .route(
            "/posts/{id}",
            get(handlers::get_post::<R>).put(handlers::update_post::<R>).delete(handlers::delete_post::<R>),
        )
        .route("/posts/{id}/comments", get(handlers::list_comments::<R>).post(handlers::add_comment::<R>))
        .route("/search", get(handlers::search_posts::<R>))
        .route("/posts/{id}/restore", post(handlers::restore_post::<R>))
        .route("/signup", post(handlers::signup::<R>))
        .route("/login", post(handlers::login::<R>))
        .route("/me", get(handlers::me::<R>))
        .route("/admin/users", get(handlers::list_users::<R>))
        .route("/auth/refresh", post(handlers::refresh::<R>))
        .route("/auth/logout", post(handlers::logout::<R>))
        .route("/auth/github", get(github::github_start))
                .route("/auth/github/callback", get(github::github_callback::<R>))
        .with_state(repo)
        .layer(TraceLayer::new_for_http())
}

#[tokio::main]
async fn main() {
    let cfg = config::config();
    tracing_subscriber::fmt()
        .with_env_filter(EnvFilter::try_from_default_env().unwrap_or_else(|_| EnvFilter::new("info")))
        .init();
    let addr = format!("{}:{}", cfg.host, cfg.port);
    let options = SqliteConnectOptions::from_str(&cfg.database_url)
        .unwrap()
        .create_if_missing(true)
        .journal_mode(SqliteJournalMode::Wal);
    let pool = SqlitePoolOptions::new()
        .max_connections(5)
        .connect_with(options)
        .await
        .unwrap();
    sqlx::migrate!().run(&pool).await.unwrap();
    let app = app(SqliteRepo { pool });

    let listener = tokio::net::TcpListener::bind(&addr).await.unwrap();
    tracing::info!(%addr, "listening");
    axum::serve(listener, app).await.unwrap();
}

use std::future::Future;

use sqlx::{QueryBuilder, Sqlite, SqlitePool};

use crate::models::{slugify, Comment, Hit, ListFilter, NewComment, NewPost, Post, PostWithTags, Sort, User, UserWithHash};

pub trait Repo: Clone + Send + Sync + 'static {
    fn list(&self, f: ListFilter) -> impl Future<Output = sqlx::Result<(Vec<Post>, i64)>> + Send;
    fn get(&self, id: i64) -> impl Future<Output = sqlx::Result<Option<Post>>> + Send;
    fn create(&self, author_id: i64, new: NewPost) -> impl Future<Output = sqlx::Result<PostWithTags>> + Send;
    fn update(&self, id: i64, new: NewPost) -> impl Future<Output = sqlx::Result<Option<Post>>> + Send;
    fn delete(&self, id: i64) -> impl Future<Output = sqlx::Result<bool>> + Send;
    fn restore(&self, id: i64) -> impl Future<Output = sqlx::Result<bool>> + Send;
    fn list_comments(&self, post_id: i64) -> impl Future<Output = sqlx::Result<Vec<Comment>>> + Send;
    fn add_comment(&self, post_id: i64, new: NewComment) -> impl Future<Output = sqlx::Result<Comment>> + Send;
    fn search(&self, q: &str) -> impl Future<Output = sqlx::Result<Vec<Hit>>> + Send;
    fn create_user(&self, email: &str, password_hash: &str) -> impl Future<Output = sqlx::Result<User>> + Send;
    fn find_user_by_email(&self, email: &str) -> impl Future<Output = sqlx::Result<Option<UserWithHash>>> + Send;
    fn get_user(&self, id: i64) -> impl Future<Output = sqlx::Result<Option<User>>> + Send;
    fn find_or_create_github_user(&self, github_id: i64, login: &str) -> impl Future<Output = sqlx::Result<User>> + Send;
    fn store_refresh(&self, hash: &str, user_id: i64, expires_at: i64) -> impl Future<Output = sqlx::Result<()>> + Send;
    fn take_refresh(&self, hash: &str, now: i64) -> impl Future<Output = sqlx::Result<Option<i64>>> + Send;
    fn delete_refresh(&self, hash: &str) -> impl Future<Output = sqlx::Result<()>> + Send;
    fn list_users(&self) -> impl Future<Output = sqlx::Result<Vec<User>>> + Send;
}

#[derive(Clone)]
pub struct SqliteRepo {
    pub pool: SqlitePool,
}

impl Repo for SqliteRepo {
    async fn list(&self, f: ListFilter) -> sqlx::Result<(Vec<Post>, i64)> {
        let mut q = QueryBuilder::<Sqlite>::new("SELECT p.id, p.title, p.body, p.slug, p.created_at, p.author_id FROM posts p WHERE p.deleted_at IS NULL");
        if let Some(tag) = &f.tag {
            q.push(" AND p.id IN (SELECT pt.post_id FROM post_tags pt JOIN tags t ON t.id = pt.tag_id WHERE t.name = ")
                .push_bind(tag.clone())
                .push(")");
        }
        q.push(match f.sort {
            Sort::Newest => " ORDER BY p.id DESC",
            Sort::Oldest => " ORDER BY p.id ASC",
            Sort::Title => " ORDER BY p.title ASC",
        });
        q.push(" LIMIT ").push_bind(f.per_page as i64);
        q.push(" OFFSET ").push_bind(((f.page - 1) * f.per_page) as i64);
        let posts = q.build_query_as::<Post>().fetch_all(&self.pool).await?;

        let mut c = QueryBuilder::<Sqlite>::new("SELECT COUNT(*) FROM posts p WHERE p.deleted_at IS NULL");
        if let Some(tag) = &f.tag {
            c.push(" AND p.id IN (SELECT pt.post_id FROM post_tags pt JOIN tags t ON t.id = pt.tag_id WHERE t.name = ")
                .push_bind(tag.clone())
                .push(")");
        }
        let total: i64 = c.build_query_scalar().fetch_one(&self.pool).await?;
        Ok((posts, total))
    }

    async fn search(&self, q: &str) -> sqlx::Result<Vec<Hit>> {
        let q = fts_query(q);
        sqlx::query_as!(
            Hit,
            r#"SELECT p.id as "id!", p.title, snippet(posts_fts, 1, '[', ']', '...', 8) as "snippet!: String"
               FROM posts_fts JOIN posts p ON p.id = posts_fts.rowid
               WHERE posts_fts MATCH ? AND p.deleted_at IS NULL
               ORDER BY bm25(posts_fts) LIMIT 20"#,
            q
        )
        .fetch_all(&self.pool)
        .await
    }

    async fn get(&self, id: i64) -> sqlx::Result<Option<Post>> {
        sqlx::query_as!(Post, "SELECT id, title, body, slug, created_at, author_id FROM posts WHERE id = ? AND deleted_at IS NULL", id)
            .fetch_optional(&self.pool)
            .await
    }

    async fn create(&self, author_id: i64, new: NewPost) -> sqlx::Result<PostWithTags> {
        let slug = slugify(&new.title);
        let mut tx = self.pool.begin().await?;
        let post = sqlx::query_as!(
            Post,
            r#"INSERT INTO posts (title, body, slug, author_id) VALUES (?, ?, ?, ?) RETURNING id, title, body, slug, created_at, author_id"#,
            new.title,
            new.body,
            slug,
            author_id
        )
        .fetch_one(&mut *tx)
        .await?;
        for tag in &new.tags {
            sqlx::query!("INSERT INTO tags (name) VALUES (?) ON CONFLICT (name) DO NOTHING", tag)
                .execute(&mut *tx)
                .await?;
            sqlx::query!(
                "INSERT INTO post_tags (post_id, tag_id) SELECT ?, id FROM tags WHERE name = ?",
                post.id,
                tag
            )
            .execute(&mut *tx)
            .await?;
        }
        tx.commit().await?;
        Ok(PostWithTags { post, tags: new.tags })
    }

    async fn update(&self, id: i64, new: NewPost) -> sqlx::Result<Option<Post>> {
        sqlx::query_as!(
            Post,
            r#"UPDATE posts SET title = ?, body = ? WHERE id = ? AND deleted_at IS NULL RETURNING id, title, body, slug, created_at, author_id"#,
            new.title,
            new.body,
            id
        )
        .fetch_optional(&self.pool)
        .await
    }

    async fn delete(&self, id: i64) -> sqlx::Result<bool> {
        let result = sqlx::query!("UPDATE posts SET deleted_at = CURRENT_TIMESTAMP WHERE id = ? AND deleted_at IS NULL", id)
            .execute(&self.pool)
            .await?;
        Ok(result.rows_affected() > 0)
    }

    async fn restore(&self, id: i64) -> sqlx::Result<bool> {
        let result = sqlx::query!("UPDATE posts SET deleted_at = NULL WHERE id = ? AND deleted_at IS NOT NULL", id)
            .execute(&self.pool)
            .await?;
        Ok(result.rows_affected() > 0)
    }

    async fn create_user(&self, email: &str, password_hash: &str) -> sqlx::Result<User> {
        sqlx::query_as!(
            User,
            r#"INSERT INTO users (email, password_hash) VALUES (?, ?) RETURNING id, email, role, created_at"#,
            email,
            password_hash
        )
        .fetch_one(&self.pool)
        .await
    }

    async fn find_or_create_github_user(&self, github_id: i64, login: &str) -> sqlx::Result<User> {
        if let Some(user) = sqlx::query_as!(
            User,
            "SELECT id, email, role, created_at FROM users WHERE github_id = ?",
            github_id
        )
        .fetch_optional(&self.pool)
        .await?
        {
            return Ok(user);
        }
        let email = format!("{login}@users.noreply.github.com");
        sqlx::query_as!(
            User,
            "INSERT INTO users (email, password_hash, github_id) VALUES (?, '!', ?) RETURNING id, email, role, created_at",
            email,
            github_id
        )
        .fetch_one(&self.pool)
        .await
    }

    async fn find_user_by_email(&self, email: &str) -> sqlx::Result<Option<UserWithHash>> {
        sqlx::query_as!(
            UserWithHash,
            "SELECT id, email, password_hash, role FROM users WHERE email = ?",
            email
        )
        .fetch_optional(&self.pool)
        .await
    }

    async fn get_user(&self, id: i64) -> sqlx::Result<Option<User>> {
        sqlx::query_as!(User, "SELECT id, email, role, created_at FROM users WHERE id = ?", id)
            .fetch_optional(&self.pool)
            .await
    }

    async fn store_refresh(&self, hash: &str, user_id: i64, expires_at: i64) -> sqlx::Result<()> {
        sqlx::query!(
            "INSERT INTO refresh_tokens (token_hash, user_id, expires_at) VALUES (?, ?, ?)",
            hash,
            user_id,
            expires_at
        )
        .execute(&self.pool)
        .await?;
        Ok(())
    }

    async fn take_refresh(&self, hash: &str, now: i64) -> sqlx::Result<Option<i64>> {
        sqlx::query_scalar!(
            r#"DELETE FROM refresh_tokens WHERE token_hash = ? AND expires_at > ? RETURNING user_id as "user_id!""#,
            hash,
            now
        )
        .fetch_optional(&self.pool)
        .await
    }

    async fn delete_refresh(&self, hash: &str) -> sqlx::Result<()> {
        sqlx::query!("DELETE FROM refresh_tokens WHERE token_hash = ?", hash).execute(&self.pool).await?;
        Ok(())
    }

    async fn list_comments(&self, post_id: i64) -> sqlx::Result<Vec<Comment>> {
        sqlx::query_as!(
            Comment,
            "SELECT id, post_id, body, created_at FROM comments WHERE post_id = ? ORDER BY id",
            post_id
        )
        .fetch_all(&self.pool)
        .await
    }

    async fn add_comment(&self, post_id: i64, new: NewComment) -> sqlx::Result<Comment> {
        sqlx::query_as!(
            Comment,
            r#"INSERT INTO comments (post_id, body) VALUES (?, ?) RETURNING id as "id!", post_id, body, created_at"#,
            post_id,
            new.body
        )
        .fetch_one(&self.pool)
        .await
    }

    async fn list_users(&self) -> sqlx::Result<Vec<User>> {
        sqlx::query_as!(User, "SELECT id, email, role, created_at FROM users ORDER BY id")
            .fetch_all(&self.pool)
            .await
    }
}

fn fts_query(input: &str) -> String {
    input
        .split_whitespace()
        .map(|word| format!("\"{}\"", word.replace('"', "\"\"")))
        .collect::<Vec<_>>()
        .join(" ")
}