Skip to content

armgit / dbx

Public Imported from GitHub
0

25 MB lightweight cross-platform database client for 100+ databases, including MySQL, PostgreSQL, SQLite, Redis, MongoDB, DuckDB, SQL Server, and Dameng. Built-in AI, MCP Server, CLI, desktop and Docker. | 轻量级跨平台数据库管理工具,支持 MySQL、PostgreSQL、SQLite、Redis、MongoDB、达梦等 100+ 数据库,提供桌面端、Docker、CLI、内置 AI 助手和 MCP。

58.7 KB
Raw Blame
use std::collections::{HashMap, HashSet};
use std::path::{Path, PathBuf};
use std::pin::Pin;
use std::sync::atomic::{AtomicBool, AtomicU64, Ordering};
use std::sync::Arc;
use std::time::Duration;

use dbx_sqlite_worker::{WorkerBody, WorkerOp, WorkerRequest, WorkerResponse};
use russh::client::Handle;
use russh::ChannelMsg;
use sha2::{Digest, Sha256};
use tokio::io::{AsyncBufRead, AsyncBufReadExt, AsyncRead, AsyncReadExt, AsyncWrite, AsyncWriteExt, BufReader};
use tokio::process::{Child, ChildStdin};
use tokio::sync::Mutex as AsyncMutex;

use crate::agent_manager::{AgentManager, SQLITE_WORKER_DRIVER_KEY};
use crate::db::http_tunnel::HttpTunnelManager;
use crate::db::proxy_tunnel::ProxyTunnelManager;
use crate::db::ssh_prompt::{self, SshPromptAnswer, SshPromptKind, SshPromptRequest};
use crate::db::ssh_tunnel::{self, SshClient, TunnelManager};
use crate::db::transport_layer_tunnel;
use crate::models::connection::{ConnectionConfig, DatabaseType, SshTunnelConfig, TransportLayerConfig};
use crate::types::QueryResult;

const WORKER_PATH_ENV: &str = "DBX_SQLITE_WORKER_PATH";
const DEFAULT_PERSIST_DIR: &str = "~/.cache/dbx/sqlite-worker";
const CONSENT_FILE_NAME: &str = "sqlite-worker-consent.json";
const SQLITE_WORKER_MAX_RESPONSE_BYTES: usize = 16 * 1024 * 1024;
static SQLITE_SSH_RUNTIME_ENABLED: AtomicBool = AtomicBool::new(false);
// ponytail: one mutex per host+path for the process lifetime. Opening a table
// prewarms and queries at once; both used to `cat` the same `.part` and publish
// a corrupt worker. Upgrade path: evict idle keys if the map ever matters.
static SQLITE_WORKER_INSTALLS: std::sync::OnceLock<AsyncMutex<HashMap<String, Arc<AsyncMutex<()>>>>> =
    std::sync::OnceLock::new();

pub fn sqlite_worker_chain_id(connection_id: &str) -> String {
    format!("{connection_id}:sqlite-worker")
}

pub fn enable_sqlite_ssh_runtime(_app_version: impl Into<String>) {
    SQLITE_SSH_RUNTIME_ENABLED.store(true, Ordering::SeqCst);
}

pub fn sqlite_ssh_runtime_enabled() -> bool {
    SQLITE_SSH_RUNTIME_ENABLED.load(Ordering::SeqCst)
}

#[derive(Debug, Clone, Copy, PartialEq, Eq)]
pub enum SqliteWorkerPlacement {
    Session,
    Persist,
    Preplaced,
}

pub fn sqlite_remote_worker_requested(config: &ConnectionConfig) -> bool {
    config.db_type == DatabaseType::Sqlite && config.has_effective_transport_layers()
}

fn sqlite_worker_placement(config: &ConnectionConfig) -> SqliteWorkerPlacement {
    match url_param(config.url_params.as_deref(), "dbx_sqlite_worker").as_deref() {
        Some("persist") => SqliteWorkerPlacement::Persist,
        Some("preplaced") => SqliteWorkerPlacement::Preplaced,
        _ => SqliteWorkerPlacement::Session,
    }
}

fn removes_remote_worker_on_disconnect(placement: SqliteWorkerPlacement) -> bool {
    matches!(placement, SqliteWorkerPlacement::Session)
}

fn session_worker_path(connection_id: &str, digest: &str) -> String {
    format!("{DEFAULT_PERSIST_DIR}/session-{connection_id}-{digest}-{}", uuid::Uuid::new_v4().simple())
}

pub fn sqlite_worker_remote_path(config: &ConnectionConfig) -> String {
    url_param(config.url_params.as_deref(), "dbx_sqlite_worker_path").unwrap_or_default()
}

fn url_param(params: Option<&str>, key: &str) -> Option<String> {
    params?.trim().trim_start_matches('?').split('&').find_map(|part| {
        let (raw_key, raw_value) = part.split_once('=')?;
        (raw_key == key).then(|| {
            percent_encoding::percent_decode_str(&raw_value.replace('+', " ")).decode_utf8_lossy().into_owned()
        })
    })
}

trait WorkerStream: AsyncRead + AsyncWrite + Send + Unpin {}
impl<T> WorkerStream for T where T: AsyncRead + AsyncWrite + Send + Unpin {}
type DynStream = Pin<Box<dyn WorkerStream>>;

pub struct SqliteWorkerClient {
    io: AsyncMutex<WorkerIo>,
    next_id: AtomicU64,
    ssh_session: Option<Arc<Handle<SshClient>>>,
    /// Session placement only: delete this uploaded file after the worker exits.
    remove_remote_path: Option<String>,
}

struct SqliteWorkerStartup {
    session: Option<Arc<Handle<SshClient>>>,
    remove_paths: Vec<String>,
}

impl SqliteWorkerStartup {
    fn cleanup(&mut self) -> Option<tokio::task::JoinHandle<()>> {
        let session = self.session.take()?;
        let paths = std::mem::take(&mut self.remove_paths);
        let Ok(runtime) = tokio::runtime::Handle::try_current() else {
            log::warn!("SQLite worker SSH cleanup is unconfirmed: async runtime unavailable");
            return None;
        };
        Some(runtime.spawn(async move {
            let deadline = tokio::time::Instant::now() + Duration::from_secs(5);
            // Unlink the upload part before its destination to prevent a late promotion after cleanup.
            for path in paths.into_iter().rev() {
                let result = tokio::time::timeout_at(
                    deadline,
                    ssh_exec_with_stdin(session.as_ref(), remove_session_worker_command(&path), tokio::io::empty()),
                )
                .await;
                if !matches!(result, Ok(Ok(()))) {
                    log::warn!("SQLite worker private file cleanup is unconfirmed: {path}");
                }
            }
            let result = tokio::time::timeout_at(
                deadline,
                session.disconnect(russh::Disconnect::ByApplication, "SQLite worker closed", "en"),
            )
            .await;
            if !matches!(result, Ok(Ok(()))) {
                log::warn!("SQLite worker SSH disconnect request is unconfirmed");
            }
            match Arc::try_unwrap(session) {
                Ok(session) => {
                    if !matches!(tokio::time::timeout_at(deadline, session).await, Ok(Ok(()))) {
                        log::warn!("SQLite worker local SSH task exit is unconfirmed");
                    }
                }
                Err(_) => log::warn!("SQLite worker local SSH task exit is unconfirmed: handle still shared"),
            }
        }))
    }
}

impl Drop for SqliteWorkerStartup {
    fn drop(&mut self) {
        self.cleanup();
    }
}

enum WorkerIo {
    #[allow(dead_code)]
    Process {
        child: Box<Child>,
        stdin: Box<ChildStdin>,
        stdout: Box<BufReader<tokio::process::ChildStdout>>,
    },
    Ssh {
        stream: BufReader<DynStream>,
    },
    Closed,
}

/// Holds the worker I/O lock so consecutive requests, such as the statements of
/// one transaction, cannot interleave with other callers on the worker's single connection.
pub struct SqliteWorkerSession<'a> {
    client: &'a SqliteWorkerClient,
    io: tokio::sync::MutexGuard<'a, WorkerIo>,
}

impl SqliteWorkerSession<'_> {
    pub async fn query(&mut self, sql: &str, max_rows: Option<usize>) -> Result<QueryResult, String> {
        match self.roundtrip(WorkerOp::Query { sql: sql.to_string(), max_rows }).await? {
            WorkerBody::Ok { columns, column_types, rows, affected_rows, truncated, .. } => Ok(QueryResult {
                columns: columns.unwrap_or_default(),
                column_types: column_types.unwrap_or_default(),
                deferred_large_value_cells: Vec::new(),
                column_sortables: vec![],
                spatial_columns: vec![],
                raw_key_cells: Vec::new(),
                spatial_values: vec![],
                rows: rows.unwrap_or_default(),
                affected_rows: affected_rows.unwrap_or(0),
                execution_time_ms: 0,
                server_execute_time_us: None,
                query_timings_ms: None,
                truncated: truncated.unwrap_or(false),
                session_id: None,
                has_more: false,
                elasticsearch_raw_body: None,
                messages: Vec::new(),
            }),
            WorkerBody::Err { error } => Err(error),
        }
    }

    async fn roundtrip(&mut self, op: WorkerOp) -> Result<WorkerBody, String> {
        let id = self.client.next_id.fetch_add(1, Ordering::SeqCst);
        let mut encoded = serde_json::to_vec(&WorkerRequest { id, op }).map_err(|e| e.to_string())?;
        encoded.push(b'\n');
        match &mut *self.io {
            WorkerIo::Process { stdin, stdout, .. } => {
                stdin.write_all(&encoded).await.map_err(|e| e.to_string())?;
                stdin.flush().await.map_err(|e| e.to_string())?;
                let mut line = String::new();
                stdout.read_line(&mut line).await.map_err(|e| e.to_string())?;
                parse_response(id, &line)
            }
            WorkerIo::Closed => Err("SQLite worker session is closed".to_string()),
            WorkerIo::Ssh { stream, .. } => {
                stream.write_all(&encoded).await.map_err(|e| e.to_string())?;
                stream.flush().await.map_err(|e| e.to_string())?;
                parse_response(id, &read_jsonl_line(stream).await?)
            }
        }
    }
}

impl SqliteWorkerClient {
    #[cfg(any(test, feature = "test-support"))]
    pub fn from_test_stream(stream: impl AsyncRead + AsyncWrite + Send + Unpin + 'static) -> Self {
        Self {
            io: AsyncMutex::new(WorkerIo::Ssh { stream: BufReader::new(Box::pin(stream)) }),
            next_id: AtomicU64::new(1),
            ssh_session: None,
            remove_remote_path: None,
        }
    }

    pub async fn session(&self) -> SqliteWorkerSession<'_> {
        SqliteWorkerSession { client: self, io: self.io.lock().await }
    }

    pub async fn query(&self, sql: &str, max_rows: Option<usize>) -> Result<QueryResult, String> {
        self.session().await.query(sql, max_rows).await
    }

    pub async fn backup(&self, dest: &str) -> Result<(), String> {
        match self.roundtrip(WorkerOp::Backup { dest: dest.to_string() }).await? {
            WorkerBody::Ok { .. } => Ok(()),
            WorkerBody::Err { error } => Err(error),
        }
    }

    pub async fn restore(&self, src: &str) -> Result<(), String> {
        match self.roundtrip(WorkerOp::Restore { src: src.to_string() }).await? {
            WorkerBody::Ok { .. } => Ok(()),
            WorkerBody::Err { error } => Err(error),
        }
    }

    pub async fn backup_to_local_path(&self, dest: &Path) -> Result<(), String> {
        let session = self.ssh_session().await?;
        let remote = remote_xfer_path(session.as_ref()).await?;
        let transfer = async {
            self.backup(&remote).await?;
            ssh_download_file(session.as_ref(), &remote, dest).await
        }
        .await;
        if transfer.is_ok() {
            let _ = ssh_remove_file(session.as_ref(), &remote).await;
        }
        transfer
    }

    pub async fn restore_from_local_path(&self, src: &Path) -> Result<(), String> {
        let session = self.ssh_session().await?;
        let remote = remote_xfer_path(session.as_ref()).await?;
        let transfer = async {
            ssh_upload_file(session.as_ref(), src, &remote).await?;
            self.restore(&remote).await
        }
        .await;
        let _ = ssh_remove_file(session.as_ref(), &remote).await;
        transfer
    }

    async fn ssh_session(&self) -> Result<Arc<Handle<SshClient>>, String> {
        self.ssh_session.clone().ok_or_else(|| "SQLite worker file transfer requires an SSH session".to_string())
    }

    pub async fn shutdown(&self) {
        self.close_io().await;
        self.remove_uploaded_session_worker_best_effort().await;
    }

    async fn close_io(&self) {
        let previous = {
            let mut io = self.io.lock().await;
            std::mem::replace(&mut *io, WorkerIo::Closed)
        };
        match previous {
            WorkerIo::Ssh { mut stream } => {
                let _ = stream.get_mut().shutdown().await;
            }
            WorkerIo::Process { mut child, .. } => {
                let _ = child.start_kill();
            }
            WorkerIo::Closed => {}
        }
    }

    async fn remove_uploaded_session_worker_best_effort(&self) {
        let Some(path) = self.remove_remote_path.as_deref() else {
            return;
        };
        let Some(session) = self.ssh_session.as_ref() else {
            return;
        };
        remove_uploaded_session_worker(session.as_ref(), path).await;
    }

    async fn open_database(&self, path: &str) -> Result<(), String> {
        match self.roundtrip(WorkerOp::Open { path: path.to_string() }).await? {
            WorkerBody::Ok { .. } => Ok(()),
            WorkerBody::Err { error } => Err(error),
        }
    }

    async fn roundtrip(&self, op: WorkerOp) -> Result<WorkerBody, String> {
        self.session().await.roundtrip(op).await
    }
}

impl Drop for SqliteWorkerClient {
    fn drop(&mut self) {
        if let Ok(mut io) = self.io.try_lock() {
            match std::mem::replace(&mut *io, WorkerIo::Closed) {
                WorkerIo::Process { mut child, .. } => {
                    let _ = child.start_kill();
                }
                WorkerIo::Ssh { stream } => drop(stream),
                WorkerIo::Closed => {}
            }
        }
        let Some(session) = self.ssh_session.take() else {
            return;
        };
        drop(SqliteWorkerStartup {
            session: Some(session),
            remove_paths: self.remove_remote_path.take().into_iter().collect(),
        });
    }
}

fn parse_response(id: u64, line: &str) -> Result<WorkerBody, String> {
    let response: WorkerResponse =
        serde_json::from_str(line.trim()).map_err(|e| format!("invalid worker response: {e}"))?;
    if response.id != id {
        return Err(format!("SQLite worker response id {} did not match {id}", response.id));
    }
    Ok(response.body)
}

async fn read_jsonl_line<R: AsyncBufRead + Unpin>(reader: &mut R) -> Result<String, String> {
    let mut line = Vec::new();
    loop {
        let buf = reader.fill_buf().await.map_err(|e| format!("SQLite worker closed the SSH session: {e}"))?;
        if buf.is_empty() {
            return Err("SQLite worker closed the SSH session".to_string());
        }
        if let Some(newline) = buf.iter().position(|&byte| byte == b'\n') {
            if line.len() + newline > SQLITE_WORKER_MAX_RESPONSE_BYTES {
                return Err("SQLite worker response exceeded 16 MiB".to_string());
            }
            line.extend_from_slice(&buf[..=newline]);
            reader.consume(newline + 1);
            break;
        }
        if line.len() + buf.len() > SQLITE_WORKER_MAX_RESPONSE_BYTES {
            return Err("SQLite worker response exceeded 16 MiB".to_string());
        }
        let consumed = buf.len();
        line.extend_from_slice(buf);
        reader.consume(consumed);
    }
    Ok(String::from_utf8_lossy(&line).into_owned())
}

pub async fn connect_sqlite_worker(
    tunnels: &TunnelManager,
    proxy_tunnels: &ProxyTunnelManager,
    http_tunnels: &HttpTunnelManager,
    agent_manager: &AgentManager,
    data_dir: &Path,
    connection_id: &str,
    config: &ConnectionConfig,
    transport_layers: &[TransportLayerConfig],
) -> Result<Arc<SqliteWorkerClient>, String> {
    let route = prepare_sqlite_worker_route(config, transport_layers)?;
    let chain_id = format!("{}:{}", sqlite_worker_chain_id(connection_id), uuid::Uuid::new_v4().simple());
    let result = connect_sqlite_worker_with_route(
        tunnels,
        proxy_tunnels,
        http_tunnels,
        agent_manager,
        data_dir,
        connection_id,
        config,
        &route,
        &chain_id,
    )
    .await;
    if result.is_err() {
        stop_sqlite_transport_layers(&chain_id, tunnels, proxy_tunnels, http_tunnels).await;
    }
    result
}

pub fn prepare_sqlite_worker_route(
    config: &ConnectionConfig,
    transport_layers: &[TransportLayerConfig],
) -> Result<ResolvedSqliteWorkerRoute, String> {
    if !sqlite_ssh_runtime_enabled() {
        return Err("Remote SQLite over SSH is only available in the DBX Desktop app".to_string());
    }
    if !config.password.is_empty() {
        return Err("Remote SQLite over SSH does not support SQLCipher in v1".to_string());
    }
    if sqlite_has_extensions(config.url_params.as_deref()) {
        return Err("Remote SQLite over SSH does not support loadable extensions in v1".to_string());
    }
    if !config.attached_databases.is_empty() {
        return Err("Remote SQLite over SSH does not support attached databases in v1".to_string());
    }

    let route = sqlite_worker_route(transport_layers)?;
    let db_path = config.host.trim();
    if db_path.is_empty() {
        return Err("Remote SQLite path is empty".to_string());
    }
    if !is_remote_linux_path_absolute_or_home_relative(db_path) {
        return Err("Remote SQLite path must be an absolute path on the final SSH hop".to_string());
    }
    validate_remote_path(db_path)?;

    let prefix = transport_layer_tunnel::ResolvedTransportLayers::resolve(&route.prefix_layers)?;
    let mut final_ssh = route.final_ssh;
    final_ssh.id.clear();
    final_ssh.name.clear();
    final_ssh.profile_id.clear();
    final_ssh.enabled = true;
    final_ssh.expose_lan = false;
    final_ssh.allow_exec_channel_proxy = false;
    final_ssh.connect_timeout_secs = ssh_tunnel::effective_hop_timeout(&final_ssh);
    Ok(ResolvedSqliteWorkerRoute { prefix, final_ssh })
}

#[derive(Clone, PartialEq, Eq)]
pub struct ResolvedSqliteWorkerRoute {
    prefix: transport_layer_tunnel::ResolvedTransportLayers,
    final_ssh: SshTunnelConfig,
}

impl ResolvedSqliteWorkerRoute {
    pub fn prefix(&self) -> &transport_layer_tunnel::ResolvedTransportLayers {
        &self.prefix
    }
}

#[allow(clippy::too_many_arguments)]
pub async fn connect_sqlite_worker_with_route(
    tunnels: &TunnelManager,
    proxy_tunnels: &ProxyTunnelManager,
    http_tunnels: &HttpTunnelManager,
    agent_manager: &AgentManager,
    data_dir: &Path,
    connection_id: &str,
    config: &ConnectionConfig,
    route: &ResolvedSqliteWorkerRoute,
    chain_id: &str,
) -> Result<Arc<SqliteWorkerClient>, String> {
    let db_path = config.host.trim();

    let placement = sqlite_worker_placement(config);
    let remove_remote_on_close = removes_remote_worker_on_disconnect(placement);
    let configured_path = sqlite_worker_remote_path(config);
    let session = Arc::new(
        open_final_hop_session(tunnels, proxy_tunnels, http_tunnels, chain_id, &route.prefix, &route.final_ssh).await?,
    );
    let mut startup = SqliteWorkerStartup { session: Some(session.clone()), remove_paths: Vec::new() };
    let start_worker = async {
        let remote_home = ssh_capture(&session, "printf %s \"$HOME\"").await?;
        let expand_home = |path: &str| {
            if let Some(rest) = path.strip_prefix("~/") {
                format!("{}/{}", remote_home.trim_end_matches('/'), rest)
            } else if path == "~" {
                remote_home.clone()
            } else {
                path.to_string()
            }
        };

        let platform = remote_linux_platform(&session).await?;
        // Only the remote host's architecture matters: an offline import of that one
        // platform is a complete installation and must not trigger a download, while
        // a missing binary for this host still gets fetched when it can be (#8987).
        if std::env::var_os(WORKER_PATH_ENV).is_none() && !agent_manager.sqlite_worker_platform_installed(&platform) {
            crate::agent_service::ensure_sqlite_worker_driver_ready(agent_manager).await?;
        }
        let local_worker = resolve_local_worker(agent_manager, &platform).await?;
        let digest = local_worker.digest.clone();
        let identity = hop_identity(&route.final_ssh);

        let remote_path = match placement {
            SqliteWorkerPlacement::Preplaced => {
                if configured_path.trim().is_empty() {
                    return Err("Pre-placed SQLite worker path is required".to_string());
                }
                validate_remote_path(&configured_path)?;
                expand_home(&configured_path)
            }
            SqliteWorkerPlacement::Persist => {
                let path = if configured_path.trim().is_empty() {
                    format!("{DEFAULT_PERSIST_DIR}/{digest}")
                } else {
                    validate_remote_path(&configured_path)?;
                    configured_path
                };
                expand_home(&path)
            }
            SqliteWorkerPlacement::Session => expand_home(&session_worker_path(connection_id, &digest)),
        };

        let expanded_db = expand_home(db_path);
        if remove_remote_on_close {
            startup.remove_paths.push(remote_path.clone());
        }
        ensure_remote_sqlite_file_exists(session.as_ref(), &expanded_db).await?;
        if placement != SqliteWorkerPlacement::Preplaced {
            ensure_worker_consent(data_dir, &identity, &remote_path, &digest).await?;
        }
        let _install = remote_worker_install_lock(&identity, &remote_path).await;
        if placement != SqliteWorkerPlacement::Preplaced
            && verify_remote_digest(session.as_ref(), &remote_path, &digest).await.is_err()
        {
            let part_path = worker_part_path(&remote_path);
            startup.remove_paths.push(part_path.clone());
            upload_worker(session.as_ref(), &remote_path, &part_path, &local_worker.bytes).await?;
            startup.remove_paths.retain(|path| path != &part_path);
        }
        verify_remote_digest(session.as_ref(), &remote_path, &digest).await?;
        let channel = session.channel_open_session().await.map_err(|e| format!("SSH session channel failed: {e}"))?;
        channel
            .exec(true, format!("exec {}", shell_quote(&remote_path)))
            .await
            .map_err(|e| format!("failed to start SQLite worker: {e}"))?;
        let stream: DynStream = Box::pin(channel.into_stream());
        let client = SqliteWorkerClient {
            io: AsyncMutex::new(WorkerIo::Ssh { stream: BufReader::new(stream) }),
            next_id: AtomicU64::new(1),
            ssh_session: None,
            remove_remote_path: None,
        };
        client.open_database(&expanded_db).await?;
        Ok((client, remove_remote_on_close.then_some(remote_path)))
    };
    let result = start_worker.await;
    drop(session);
    match result {
        Ok((mut client, remove_remote_path)) => {
            client.ssh_session = startup.session.take();
            client.remove_remote_path = remove_remote_path;
            startup.remove_paths.clear();
            Ok(Arc::new(client))
        }
        Err(error) => {
            if let Some(cleanup) = startup.cleanup() {
                let _ = cleanup.await;
            }
            Err(error)
        }
    }
}

struct LocalWorker {
    bytes: Vec<u8>,
    digest: String,
}

async fn resolve_local_worker(agent_manager: &AgentManager, platform: &str) -> Result<LocalWorker, String> {
    if let Ok(path) = std::env::var(WORKER_PATH_ENV) {
        let bytes = tokio::fs::read(&path).await.map_err(|e| format!("failed to read {WORKER_PATH_ENV}: {e}"))?;
        return Ok(LocalWorker { digest: sha256_hex(&bytes), bytes });
    }
    let path = agent_manager.driver_native_platform_path(SQLITE_WORKER_DRIVER_KEY, platform);
    if path.is_file() {
        let bytes = tokio::fs::read(&path).await.map_err(|e| e.to_string())?;
        return Ok(LocalWorker { digest: sha256_hex(&bytes), bytes });
    }
    Err(format!(
        "{SQLITE_WORKER_DRIVER_KEY} driver for remote platform '{platform}' is not installed. Import the matching \
dbx-agent-{SQLITE_WORKER_DRIVER_KEY} package from the Driver Manager, or set {WORKER_PATH_ENV}."
    ))
}

async fn ensure_worker_consent(data_dir: &Path, identity: &str, dest: &str, digest: &str) -> Result<(), String> {
    if consent_remembered(data_dir, identity, digest) {
        return Ok(());
    }
    let hostport = identity.rsplit_once('@').map(|(_, value)| value).unwrap_or(identity);
    let (host, port) = hostport.rsplit_once(':').unwrap_or((hostport, "22"));
    let request = SshPromptRequest {
        id: uuid::Uuid::new_v4().to_string(),
        kind: SshPromptKind::WorkerUploadConsent,
        host: host.to_string(),
        port: port.parse().unwrap_or(22),
        key_type: Some("sha256".to_string()),
        fingerprint: Some(digest.to_string()),
        previous_fingerprint: None,
        prompt: Some(dest.to_string()),
        echo: false,
        source: None,
        title: None,
        default_value: None,
        options: Vec::new(),
    };
    let Some(rx) = ssh_prompt::request_ssh_prompt(request) else {
        return Err("Uploading a SQLite worker requires explicit consent in the Desktop UI".to_string());
    };
    match tokio::time::timeout(Duration::from_secs(300), rx).await {
        Ok(Ok(SshPromptAnswer::Accept { remember })) => {
            if remember {
                remember_consent(data_dir, identity, digest);
            }
            Ok(())
        }
        Ok(Ok(SshPromptAnswer::Reject)) => Err("SQLite worker upload was declined".to_string()),
        Ok(Ok(_)) => Err("SQLite worker upload received an invalid response".to_string()),
        Ok(Err(_)) => Err("SQLite worker upload prompt was dismissed".to_string()),
        Err(_) => Err("SQLite worker upload prompt timed out".to_string()),
    }
}

fn consent_file(data_dir: &Path) -> PathBuf {
    data_dir.join(CONSENT_FILE_NAME)
}

fn consent_remembered(data_dir: &Path, identity: &str, digest: &str) -> bool {
    let Ok(text) = std::fs::read_to_string(consent_file(data_dir)) else {
        return false;
    };
    let Ok(entries) = serde_json::from_str::<HashSet<String>>(&text) else {
        return false;
    };
    entries.contains(&format!("{identity}|{digest}"))
}

fn remember_consent(data_dir: &Path, identity: &str, digest: &str) {
    let path = consent_file(data_dir);
    if let Some(parent) = path.parent() {
        let _ = std::fs::create_dir_all(parent);
    }
    let mut entries = std::fs::read_to_string(&path)
        .ok()
        .and_then(|text| serde_json::from_str::<HashSet<String>>(&text).ok())
        .unwrap_or_default();
    entries.insert(format!("{identity}|{digest}"));
    let _ = std::fs::write(path, serde_json::to_vec_pretty(&entries).unwrap_or_default());
}

async fn open_final_hop_session(
    tunnels: &TunnelManager,
    proxy_tunnels: &ProxyTunnelManager,
    http_tunnels: &HttpTunnelManager,
    chain_id: &str,
    prefix_layers: &transport_layer_tunnel::ResolvedTransportLayers,
    final_ssh: &SshTunnelConfig,
) -> Result<Handle<SshClient>, String> {
    if prefix_layers.layers().is_empty() {
        return ssh_tunnel::connect_and_authenticate(
            &final_ssh.host,
            final_ssh.port,
            &final_ssh.host,
            final_ssh.port,
            &final_ssh.user,
            &final_ssh.password,
            &final_ssh.key_path,
            &final_ssh.key_passphrase,
            final_ssh.use_ssh_agent,
            &final_ssh.ssh_agent_sock_path,
            &final_ssh.ssh_agent_key_fingerprint,
            &final_ssh.auth_method,
            ssh_tunnel::effective_hop_timeout(final_ssh),
            tunnels.known_hosts_path(),
            &final_ssh.proxy_command,
        )
        .await;
    }
    let local_port =
        prefix_layers.start(chain_id, &final_ssh.host, final_ssh.port, tunnels, proxy_tunnels, http_tunnels).await?;
    ssh_tunnel::connect_and_authenticate(
        "127.0.0.1",
        local_port,
        &final_ssh.host,
        final_ssh.port,
        &final_ssh.user,
        &final_ssh.password,
        &final_ssh.key_path,
        &final_ssh.key_passphrase,
        final_ssh.use_ssh_agent,
        &final_ssh.ssh_agent_sock_path,
        &final_ssh.ssh_agent_key_fingerprint,
        &final_ssh.auth_method,
        ssh_tunnel::effective_hop_timeout(final_ssh),
        tunnels.known_hosts_path(),
        &final_ssh.proxy_command,
    )
    .await
}

async fn stop_sqlite_transport_layers(
    chain_id: &str,
    tunnels: &TunnelManager,
    proxy_tunnels: &ProxyTunnelManager,
    http_tunnels: &HttpTunnelManager,
) {
    tunnels.stop_tunnels_with_prefix(chain_id).await;
    proxy_tunnels.stop_tunnels_with_prefix(chain_id).await;
    http_tunnels.stop_tunnels_with_prefix(chain_id).await;
}

async fn remote_linux_platform(session: &Handle<SshClient>) -> Result<String, String> {
    linux_platform_from_uname(ssh_capture(session, "uname -m").await?.trim())
}

async fn ensure_remote_sqlite_file_exists(session: &Handle<SshClient>, path: &str) -> Result<(), String> {
    let output = ssh_capture(session, &remote_sqlite_exists_command(path)).await?;
    remote_sqlite_exists_from_output(path, &output)
}

fn remote_sqlite_exists_command(path: &str) -> String {
    format!("test -f {} && echo ok", shell_quote(path))
}

fn remote_sqlite_exists_from_output(path: &str, output: &str) -> Result<(), String> {
    if output.trim() == "ok" {
        Ok(())
    } else {
        Err(format!("File does not exist: {path}"))
    }
}

fn linux_platform_from_uname(machine: &str) -> Result<String, String> {
    match machine {
        "x86_64" | "amd64" => Ok("linux-x64".to_string()),
        "aarch64" | "arm64" => Ok("linux-aarch64".to_string()),
        other => Err(format!("Remote SQLite over SSH supports Linux amd64/arm64 only, found {other}")),
    }
}

async fn ssh_capture(session: &Handle<SshClient>, command: &str) -> Result<String, String> {
    let channel = session.channel_open_session().await.map_err(|e| e.to_string())?;
    channel.exec(true, command).await.map_err(|e| e.to_string())?;
    let mut stream = channel.into_stream();
    let mut out = String::new();
    stream.read_to_string(&mut out).await.map_err(|e| e.to_string())?;
    Ok(out)
}

async fn remote_xfer_path(session: &Handle<SshClient>) -> Result<String, String> {
    let home = ssh_capture(session, "printf %s \"$HOME\"").await?;
    let home = home.trim().trim_end_matches('/');
    if home.is_empty() {
        return Err("Remote HOME is empty".to_string());
    }
    let parent = format!("{}/.cache/dbx/sqlite-worker", home);
    ssh_capture(session, &format!("mkdir -p {}", shell_quote(&parent))).await?;
    Ok(format!("{parent}/xfer-{}", uuid::Uuid::new_v4()))
}

async fn ssh_download_file(session: &Handle<SshClient>, remote: &str, dest: &Path) -> Result<(), String> {
    if let Some(parent) = dest.parent() {
        if !parent.as_os_str().is_empty() {
            tokio::fs::create_dir_all(parent).await.map_err(|e| e.to_string())?;
        }
    }
    let mut channel = session.channel_open_session().await.map_err(|e| e.to_string())?;
    channel.exec(true, format!("cat {}", shell_quote(remote))).await.map_err(|e| e.to_string())?;
    let mut file =
        tokio::fs::File::create(dest).await.map_err(|e| format!("failed to create local backup file: {e}"))?;
    let copied = {
        let mut stream = channel.make_reader();
        tokio::io::copy(&mut stream, &mut file).await.map_err(|e| format!("failed to download SQLite backup: {e}"))?
    };
    file.flush().await.map_err(|e| e.to_string())?;
    let mut exit_status = None;
    loop {
        match channel.wait().await {
            Some(ChannelMsg::ExitStatus { exit_status: status }) => exit_status = Some(status),
            Some(ChannelMsg::Close) | None => break,
            Some(ChannelMsg::Eof | ChannelMsg::Data { .. } | ChannelMsg::ExtendedData { .. }) => {}
            Some(_) => {}
        }
    }
    validate_remote_download(copied, exit_status)
}

async fn ssh_upload_file(session: &Handle<SshClient>, src: &Path, remote: &str) -> Result<(), String> {
    let quoted = shell_quote(remote);
    let command = format!("mkdir -p \"$(dirname {quoted})\" && cat > {quoted}.part && mv {quoted}.part {quoted}");
    let mut file = tokio::fs::File::open(src).await.map_err(|e| format!("failed to read local restore file: {e}"))?;
    ssh_exec_with_stdin(session, command, &mut file).await
}

async fn ssh_remove_file(session: &Handle<SshClient>, path: &str) -> Result<(), String> {
    ssh_capture(session, &format!("rm -f {}", shell_quote(path))).await?;
    Ok(())
}

fn empty_cache_dirs_to_remove(worker_path: &str) -> Vec<String> {
    let path = Path::new(worker_path.trim_end_matches('/'));
    let Some(sqlite_worker_dir) = path.parent() else {
        return Vec::new();
    };
    if sqlite_worker_dir.file_name().is_none_or(|name| name != "sqlite-worker") {
        return Vec::new();
    }
    let mut dirs = vec![sqlite_worker_dir.to_string_lossy().into_owned()];
    if let Some(dbx_dir) = sqlite_worker_dir.parent() {
        if dbx_dir.file_name().is_some_and(|name| name == "dbx") {
            dirs.push(dbx_dir.to_string_lossy().into_owned());
        }
    }
    dirs
}

fn remove_session_worker_command(path: &str) -> String {
    let mut command = format!("rm -f {}; status=$?", shell_quote(path));
    for dir in empty_cache_dirs_to_remove(path) {
        command.push_str("; rmdir ");
        command.push_str(&shell_quote(&dir));
        command.push_str(" 2>/dev/null");
    }
    command.push_str("; exit \"$status\"");
    command
}

async fn remove_uploaded_session_worker(session: &Handle<SshClient>, path: &str) {
    if let Err(err) = ssh_exec_with_stdin(session, remove_session_worker_command(path), tokio::io::empty()).await {
        log::warn!("Failed to remove session SQLite worker at {path}: {err}");
    }
}

fn worker_part_path(dest: &str) -> String {
    format!("{dest}.part-{}", uuid::Uuid::new_v4().simple())
}

fn worker_upload_command(dest: &str, part: &str, byte_len: usize) -> String {
    let quoted = shell_quote(dest);
    // Unique part file: two connects must not `cat` into the same path.
    let part = shell_quote(part);
    format!(
        "mkdir -p \"$(dirname {quoted})\" && cat > {part} && test \"$(wc -c < {part})\" -eq {byte_len} && chmod 700 {part} && mv {part} {quoted}"
    )
}

async fn remote_worker_install_lock(identity: &str, remote_path: &str) -> tokio::sync::OwnedMutexGuard<()> {
    let key = format!("{identity}\n{remote_path}");
    let installs = SQLITE_WORKER_INSTALLS.get_or_init(|| AsyncMutex::new(HashMap::new()));
    let slot = {
        let mut installs = installs.lock().await;
        installs.entry(key).or_insert_with(|| Arc::new(AsyncMutex::new(()))).clone()
    };
    slot.lock_owned().await
}

async fn upload_worker(session: &Handle<SshClient>, dest: &str, part: &str, bytes: &[u8]) -> Result<(), String> {
    let command = worker_upload_command(dest, part, bytes.len());
    ssh_exec_with_stdin(session, command, bytes).await
}

async fn ssh_exec_with_stdin<R: tokio::io::AsyncRead + Unpin>(
    session: &Handle<SshClient>,
    command: String,
    stdin: R,
) -> Result<(), String> {
    let mut channel = session.channel_open_session().await.map_err(|e| e.to_string())?;
    channel.exec(true, command).await.map_err(|e| e.to_string())?;
    channel.data(stdin).await.map_err(|e| e.to_string())?;
    channel.eof().await.map_err(|e| e.to_string())?;
    let mut exit_status = None;
    let mut stderr = Vec::new();
    loop {
        match channel.wait().await {
            Some(ChannelMsg::ExitStatus { exit_status: status }) => exit_status = Some(status),
            Some(ChannelMsg::Close) | None => break,
            Some(ChannelMsg::ExtendedData { data, .. }) => stderr.extend_from_slice(data.as_ref()),
            Some(ChannelMsg::Eof | ChannelMsg::Data { .. }) => {}
            Some(_) => {}
        }
    }
    remote_exec_status(exit_status, &String::from_utf8_lossy(&stderr))
}

fn remote_exec_status(exit_status: Option<u32>, stderr: &str) -> Result<(), String> {
    match exit_status {
        Some(0) => Ok(()),
        Some(code) => Err(remote_command_failure(format!("remote command exited with status {code}"), stderr)),
        None => Err(remote_command_failure("remote command closed without an exit status".to_string(), stderr)),
    }
}

fn remote_command_failure(message: String, stderr: &str) -> String {
    let detail = stderr.split_whitespace().collect::<Vec<_>>().join(" ");
    let detail = detail.chars().take(400).collect::<String>();
    if detail.is_empty() {
        message
    } else {
        format!("{message}: {detail}")
    }
}

fn validate_remote_download(copied: u64, exit_status: Option<u32>) -> Result<(), String> {
    // Check exit status first: a failed `cat` with empty output is better
    // explained by the nonzero status than by the emptiness it caused.
    remote_exec_status(exit_status, "")?;
    if copied == 0 {
        return Err("Downloaded SQLite backup was empty".to_string());
    }
    Ok(())
}

async fn verify_remote_digest(session: &Handle<SshClient>, path: &str, digest: &str) -> Result<(), String> {
    let command = format!("sha256sum {} | awk '{{print $1}}'", shell_quote(path));
    let remote = ssh_capture(session, &command).await?;
    let remote = remote.trim();
    if remote != digest {
        return Err(format!("Remote SQLite worker digest {remote} does not match {digest}"));
    }
    Ok(())
}

#[derive(Debug)]
struct SqliteWorkerRoute {
    prefix_layers: Vec<TransportLayerConfig>,
    final_ssh: SshTunnelConfig,
}

fn sqlite_worker_route(layers: &[TransportLayerConfig]) -> Result<SqliteWorkerRoute, String> {
    if layers.iter().any(|layer| matches!(layer, TransportLayerConfig::HttpTunnel(_))) {
        return Err("Remote SQLite does not support HTTP tunnel layers".to_string());
    }
    let Some((last, prefix_layers)) = layers.split_last() else {
        return Err("Remote SQLite requires at least one SSH hop".to_string());
    };
    let TransportLayerConfig::Ssh(final_ssh) = last else {
        return Err("Remote SQLite transport must end with an SSH hop".to_string());
    };
    let final_ssh = crate::ssh_config::resolve_ssh_tunnel_config(final_ssh);
    if final_ssh.host.trim().is_empty() {
        return Err("SSH host is required.".to_string());
    }
    let prefix_layers = prefix_layers
        .iter()
        .cloned()
        .map(|mut layer| {
            if let TransportLayerConfig::Ssh(ssh) = &mut layer {
                ssh.expose_lan = false;
            }
            layer
        })
        .collect();
    Ok(SqliteWorkerRoute { prefix_layers, final_ssh })
}

fn hop_identity(hop: &SshTunnelConfig) -> String {
    format!("{}@{}:{}", hop.user, hop.host, hop.port)
}

fn sqlite_has_extensions(params: Option<&str>) -> bool {
    crate::db::sqlite::sqlite_extension_specs_from_url_params(params).into_iter().any(|spec| !spec.path.is_empty())
}

fn validate_remote_path(path: &str) -> Result<(), String> {
    if path.contains('\0') || path.contains('\n') || path.contains('\r') {
        return Err("Remote path contains invalid characters".to_string());
    }
    Ok(())
}

fn is_remote_linux_path_absolute_or_home_relative(path: &str) -> bool {
    // The remote host is Linux even when the DBX Desktop client runs on Windows.
    path.starts_with('/') || path.starts_with("~/")
}

fn shell_quote(value: &str) -> String {
    format!("'{}'", value.replace('\'', "'\\''"))
}

fn sha256_hex(bytes: &[u8]) -> String {
    let mut hasher = Sha256::new();
    hasher.update(bytes);
    format!("{:x}", hasher.finalize())
}

#[cfg(test)]
mod tests {
    use super::*;
    use russh::server::{self, Server};

    #[derive(Clone)]
    struct WorkerSshEvents {
        commands: Arc<std::sync::Mutex<Vec<String>>>,
        home_entered: Arc<tokio::sync::Notify>,
        closed: Arc<AtomicBool>,
        block_home: bool,
        block_auth: bool,
        auth_entered: Arc<tokio::sync::Notify>,
        release_auth: Arc<tokio::sync::Notify>,
    }

    struct WorkerSshServer(WorkerSshEvents);
    struct WorkerSshHandler(WorkerSshEvents);

    impl Server for WorkerSshServer {
        type Handler = WorkerSshHandler;

        fn new_client(&mut self, _peer: Option<std::net::SocketAddr>) -> Self::Handler {
            WorkerSshHandler(self.0.clone())
        }
    }

    impl Drop for WorkerSshHandler {
        fn drop(&mut self) {
            self.0.closed.store(true, Ordering::SeqCst);
        }
    }

    impl server::Handler for WorkerSshHandler {
        type Error = russh::Error;

        async fn auth_none(&mut self, _user: &str) -> Result<server::Auth, Self::Error> {
            self.0.auth_entered.notify_one();
            if self.0.block_auth {
                self.0.release_auth.notified().await;
            }
            Ok(server::Auth::Accept)
        }

        async fn channel_open_session(
            &mut self,
            _channel: russh::Channel<server::Msg>,
            _session: &mut server::Session,
        ) -> Result<bool, Self::Error> {
            Ok(true)
        }

        async fn exec_request(
            &mut self,
            channel: russh::ChannelId,
            data: &[u8],
            session: &mut server::Session,
        ) -> Result<(), Self::Error> {
            let command = String::from_utf8(data.to_vec()).unwrap();
            self.0.commands.lock().unwrap().push(command.clone());
            session.channel_success(channel)?;
            if command == "printf %s \"$HOME\"" {
                self.0.home_entered.notify_one();
                if self.0.block_home {
                    return Ok(());
                }
                session.data(channel, b"/fixture/home".to_vec())?;
            } else if command == "uname -m" {
                session.data(channel, b"unsupported-fixture-platform".to_vec())?;
            }
            session.exit_status_request(channel, 0)?;
            session.eof(channel)?;
            session.close(channel)?;
            Ok(())
        }
    }

    async fn worker_ssh_fixture(
        block_home: bool,
        block_auth: bool,
    ) -> (u16, russh::keys::ssh_key::PublicKey, WorkerSshEvents, tokio::task::JoinHandle<()>) {
        let pem = openssl::rsa::Rsa::generate(2048).unwrap().private_key_to_pem().unwrap();
        let key = russh::keys::decode_secret_key(std::str::from_utf8(&pem).unwrap(), None).unwrap();
        let public_key = key.public_key().clone();
        let config = Arc::new(server::Config { keys: vec![key], ..Default::default() });
        let listener = tokio::net::TcpListener::bind(("127.0.0.1", 0)).await.unwrap();
        let port = listener.local_addr().unwrap().port();
        let events = WorkerSshEvents {
            commands: Arc::new(std::sync::Mutex::new(Vec::new())),
            home_entered: Arc::new(tokio::sync::Notify::new()),
            closed: Arc::new(AtomicBool::new(false)),
            block_home,
            block_auth,
            auth_entered: Arc::new(tokio::sync::Notify::new()),
            release_auth: Arc::new(tokio::sync::Notify::new()),
        };
        let mut server = WorkerSshServer(events.clone());
        let task = tokio::spawn(async move {
            let _ = server.run_on_socket(config, &listener).await;
        });
        (port, public_key, events, task)
    }

    async fn wait_for_worker_ssh_exit(events: &WorkerSshEvents) {
        tokio::time::timeout(Duration::from_secs(5), async {
            while !events.closed.load(Ordering::SeqCst) {
                tokio::time::sleep(Duration::from_millis(10)).await;
            }
        })
        .await
        .expect("original SSH server session must observe disconnect");
    }

    #[tokio::test]
    async fn transport_worker_frozen_start_and_early_error_close_original_session() {
        enable_sqlite_ssh_runtime("fixture");
        let (port, key, events, server) = worker_ssh_fixture(false, false).await;
        let directory = tempfile::tempdir().unwrap();
        let tunnels = TunnelManager::new(directory.path().to_path_buf());
        crate::db::ssh_host_key::HostKeyVerifier::new(tunnels.known_hosts_path().to_path_buf())
            .learn("127.0.0.1", port, &key)
            .unwrap();
        let mut config = empty_config();
        let mut layer = ssh_layer("127.0.0.1", "fixture-user");
        let TransportLayerConfig::Ssh(ssh) = &mut layer else { unreachable!() };
        ssh.port = port;
        ssh.auth_method = "none".into();
        config.transport_layers = vec![layer];
        let route = prepare_sqlite_worker_route(&config, &config.transport_layers).unwrap();
        config.transport_layers = vec![ssh_layer("unresolvable.invalid", "changed-user")];
        let result = connect_sqlite_worker_with_route(
            &tunnels,
            &ProxyTunnelManager::new(),
            &HttpTunnelManager::new(),
            &AgentManager::new_with_base_dir(directory.path().join("agents")),
            directory.path(),
            "original",
            &config,
            &route,
            "original:sqlite-worker",
        )
        .await;
        assert!(result.err().unwrap().contains("unsupported-fixture-platform"));
        wait_for_worker_ssh_exit(&events).await;
        assert_eq!(events.commands.lock().unwrap().as_slice(), ["printf %s \"$HOME\"", "uname -m"]);
        server.abort();
    }

    #[tokio::test]
    async fn transport_worker_cancelled_home_read_closes_original_session() {
        enable_sqlite_ssh_runtime("fixture");
        let (port, key, events, server) = worker_ssh_fixture(true, false).await;
        let directory = tempfile::tempdir().unwrap();
        let tunnels = TunnelManager::new(directory.path().to_path_buf());
        crate::db::ssh_host_key::HostKeyVerifier::new(tunnels.known_hosts_path().to_path_buf())
            .learn("127.0.0.1", port, &key)
            .unwrap();
        let config = empty_config();
        let mut layer = ssh_layer("127.0.0.1", "fixture-user");
        let TransportLayerConfig::Ssh(ssh) = &mut layer else { unreachable!() };
        ssh.port = port;
        ssh.auth_method = "none".into();
        let route = prepare_sqlite_worker_route(&config, &[layer]).unwrap();
        let starting = tokio::spawn(async move {
            connect_sqlite_worker_with_route(
                &tunnels,
                &ProxyTunnelManager::new(),
                &HttpTunnelManager::new(),
                &AgentManager::new_with_base_dir(directory.path().join("agents")),
                directory.path(),
                "cancelled",
                &config,
                &route,
                "cancelled:sqlite-worker",
            )
            .await
        });
        tokio::time::timeout(Duration::from_secs(5), events.home_entered.notified()).await.unwrap();
        starting.abort();
        assert!(starting.await.is_err_and(|error| error.is_cancelled()));
        wait_for_worker_ssh_exit(&events).await;
        assert_eq!(events.commands.lock().unwrap().as_slice(), ["printf %s \"$HOME\""]);
        server.abort();
    }

    #[tokio::test]
    async fn transport_worker_cleanup_keeps_original_private_paths_and_observes_session_exit() {
        let (port, key, events, server) = worker_ssh_fixture(false, false).await;
        let directory = tempfile::tempdir().unwrap();
        let known_hosts = directory.path().join("known_hosts");
        crate::db::ssh_host_key::HostKeyVerifier::new(known_hosts.clone()).learn("127.0.0.1", port, &key).unwrap();
        let session = ssh_tunnel::connect_and_authenticate(
            "127.0.0.1",
            port,
            "127.0.0.1",
            port,
            "fixture-user",
            "",
            "",
            "",
            false,
            "",
            "",
            "none",
            5,
            &known_hosts,
            "",
        )
        .await
        .unwrap();
        let mut startup = SqliteWorkerStartup {
            session: Some(Arc::new(session)),
            remove_paths: vec!["/fixture/session-original".into(), "/fixture/cache.part-original".into()],
        };
        let cleanup = startup.cleanup().unwrap();
        drop(startup);
        cleanup.await.unwrap();
        wait_for_worker_ssh_exit(&events).await;
        let commands = events.commands.lock().unwrap();
        assert_eq!(commands.len(), 2, "original owner must not run cleanup twice");
        assert_eq!(commands[0], "rm -f '/fixture/cache.part-original'; status=$?; exit \"$status\"");
        assert_eq!(commands[1], "rm -f '/fixture/session-original'; status=$?; exit \"$status\"");
        server.abort();
    }

    #[tokio::test]
    async fn transport_worker_authentication_cancellation_closes_acquired_handle() {
        let (port, key, events, server) = worker_ssh_fixture(false, true).await;
        let directory = tempfile::tempdir().unwrap();
        let known_hosts = directory.path().join("known_hosts");
        crate::db::ssh_host_key::HostKeyVerifier::new(known_hosts.clone()).learn("127.0.0.1", port, &key).unwrap();
        let starting = tokio::spawn(async move {
            ssh_tunnel::connect_and_authenticate(
                "127.0.0.1",
                port,
                "127.0.0.1",
                port,
                "fixture-user",
                "",
                "",
                "",
                false,
                "",
                "",
                "none",
                5,
                &known_hosts,
                "",
            )
            .await
        });
        tokio::time::timeout(Duration::from_secs(5), events.auth_entered.notified()).await.unwrap();
        starting.abort();
        assert!(starting.await.is_err_and(|error| error.is_cancelled()));
        events.release_auth.notify_one();
        wait_for_worker_ssh_exit(&events).await;
        assert!(events.commands.lock().unwrap().is_empty());
        server.abort();
    }

    #[test]
    fn default_placement_is_session() {
        let mut config = empty_config();
        assert_eq!(sqlite_worker_placement(&config), SqliteWorkerPlacement::Session);
        config.url_params = Some("dbx_sqlite_worker=persist".into());
        assert_eq!(sqlite_worker_placement(&config), SqliteWorkerPlacement::Persist);
        config.url_params = Some("dbx_sqlite_worker=preplaced&dbx_sqlite_worker_path=%2Fopt%2Fworker".into());
        assert_eq!(sqlite_worker_placement(&config), SqliteWorkerPlacement::Preplaced);
        assert_eq!(sqlite_worker_remote_path(&config), "/opt/worker");
    }

    #[test]
    fn session_placement_removes_worker_on_disconnect() {
        assert!(removes_remote_worker_on_disconnect(SqliteWorkerPlacement::Session));
        assert!(!removes_remote_worker_on_disconnect(SqliteWorkerPlacement::Persist));
        assert!(!removes_remote_worker_on_disconnect(SqliteWorkerPlacement::Preplaced));
        assert!(
            session_worker_path("conn-1", "abc123").starts_with("~/.cache/dbx/sqlite-worker/session-conn-1-abc123-")
        );
        assert_eq!(
            empty_cache_dirs_to_remove("/home/u/.cache/dbx/sqlite-worker/session-conn-1-abc123"),
            vec!["/home/u/.cache/dbx/sqlite-worker".to_string(), "/home/u/.cache/dbx".to_string()]
        );
        assert!(empty_cache_dirs_to_remove("/opt/dbx/dbx-sqlite-worker").is_empty());
        assert!(remove_session_worker_command("/home/u/.cache/dbx/sqlite-worker/session-conn-1-abc123")
            .contains("rmdir '/home/u/.cache/dbx/sqlite-worker' 2>/dev/null; rmdir '/home/u/.cache/dbx' 2>/dev/null"));
    }

    #[test]
    fn transport_worker_session_paths_are_private_to_each_candidate() {
        let first = session_worker_path("shared-connection", "same-binary");
        let second = session_worker_path("shared-connection", "same-binary");
        assert_ne!(first, second, "two candidates can delete or replace each other's Session worker file");
    }

    #[test]
    fn transport_worker_frozen_route_compares_only_consumed_final_ssh_fields() {
        enable_sqlite_ssh_runtime("fixture");
        let mut config = empty_config();
        config.host = "/fixture/data.db".into();
        let mut layer = ssh_layer("203.0.113.10", "fixture-user");
        let original = prepare_sqlite_worker_route(&config, &[layer.clone()]).unwrap();
        let TransportLayerConfig::Ssh(ssh) = &mut layer else { unreachable!() };
        ssh.id = "renamed".into();
        ssh.name = "Display change".into();
        ssh.expose_lan = true;
        ssh.allow_exec_channel_proxy = true;
        let same = prepare_sqlite_worker_route(&config, &[layer.clone()]).unwrap();
        assert!(original == same);
        let TransportLayerConfig::Ssh(ssh) = &mut layer else { unreachable!() };
        ssh.password = "changed-password".into();
        let changed = prepare_sqlite_worker_route(&config, &[layer]).unwrap();
        assert!(original != changed);
    }

    #[test]
    fn maps_remote_uname_to_agent_linux_platforms() {
        assert_eq!(linux_platform_from_uname("x86_64").unwrap(), "linux-x64");
        assert_eq!(linux_platform_from_uname("amd64").unwrap(), "linux-x64");
        assert_eq!(linux_platform_from_uname("aarch64").unwrap(), "linux-aarch64");
        assert_eq!(linux_platform_from_uname("arm64").unwrap(), "linux-aarch64");
        assert!(linux_platform_from_uname("ppc64le").unwrap_err().contains("ppc64le"));
    }

    #[test]
    fn sqlite_worker_chain_id_is_distinct_from_connection_id() {
        assert_eq!(sqlite_worker_chain_id("conn-1"), "conn-1:sqlite-worker");
    }

    #[test]
    fn worker_upload_uses_a_private_part_file_and_checks_size() {
        let part = worker_part_path("/home/u/.cache/dbx/sqlite-worker/abc");
        let command = worker_upload_command("/home/u/.cache/dbx/sqlite-worker/abc", &part, 12);
        assert!(command.contains("mkdir -p"));
        assert!(command.contains(".part-"));
        assert!(!command.contains(".part &&"));
        assert!(command.contains("test \"$(wc -c < "));
        assert!(command.contains("-eq 12"));
        assert!(command.contains("&& mv "));
        assert!(command.contains(&shell_quote(&part)));
        assert_ne!(worker_part_path("/tmp/worker"), worker_part_path("/tmp/worker"));
    }

    #[test]
    fn remote_command_failure_keeps_stderr() {
        assert_eq!(remote_exec_status(Some(0), "noise").unwrap(), ());
        assert_eq!(
            remote_exec_status(Some(1), "cannot execute binary file").unwrap_err(),
            "remote command exited with status 1: cannot execute binary file"
        );
        assert!(remote_exec_status(None, "").unwrap_err().contains("without an exit status"));
    }

    #[test]
    fn remote_download_requires_a_successful_non_empty_transfer() {
        assert!(validate_remote_download(1, Some(0)).is_ok());
        assert!(validate_remote_download(1, Some(1)).unwrap_err().contains("status 1"));
        assert!(validate_remote_download(1, None).unwrap_err().contains("without an exit status"));
        assert!(validate_remote_download(0, Some(0)).unwrap_err().contains("was empty"));
    }

    #[test]
    fn remote_sqlite_exists_requires_a_regular_file() {
        assert_eq!(remote_sqlite_exists_command("/remote/data/app.db"), "test -f '/remote/data/app.db' && echo ok");
        assert!(remote_sqlite_exists_from_output("/remote/data/app.db", "ok\n").is_ok());
        let error = remote_sqlite_exists_from_output("/remote/data/app.db", "").unwrap_err();
        assert!(error.contains("File does not exist"), "{error}");
    }

    #[test]
    fn remote_sqlite_path_uses_linux_semantics_on_every_desktop_platform() {
        assert!(is_remote_linux_path_absolute_or_home_relative("/volume1/music/library.db"));
        assert!(is_remote_linux_path_absolute_or_home_relative("~/data/library.db"));
        assert!(!is_remote_linux_path_absolute_or_home_relative("volume1/music/library.db"));
        assert!(!is_remote_linux_path_absolute_or_home_relative(r"C:\data\library.db"));
    }

    #[tokio::test]
    async fn read_jsonl_line_stops_at_newline_and_enforces_size_cap() {
        let mut reader = BufReader::new(&b"{\"id\":1}\nleftover"[..]);
        let line = read_jsonl_line(&mut reader).await.unwrap();
        assert_eq!(line, "{\"id\":1}\n");

        let mut oversized = vec![b'a'; SQLITE_WORKER_MAX_RESPONSE_BYTES + 1];
        oversized.push(b'\n');
        let mut reader = BufReader::new(oversized.as_slice());
        let error = read_jsonl_line(&mut reader).await.unwrap_err();
        assert!(error.contains("16 MiB"), "{error}");
    }

    #[test]
    fn remote_sqlite_requires_a_transport_layer() {
        let config = empty_config();
        assert!(!sqlite_remote_worker_requested(&config));
    }

    #[test]
    fn sqlite_worker_route_uses_resolved_final_ssh() {
        let layers = [ssh_layer("203.0.113.10", "testuser")];
        let route = sqlite_worker_route(&layers).unwrap();
        assert!(route.prefix_layers.is_empty());
        assert_eq!(route.final_ssh.host, "203.0.113.10");
        assert_eq!(route.final_ssh.user, "testuser");
    }

    #[test]
    fn sqlite_worker_route_rejects_unresolved_profile_stubs() {
        let error = sqlite_worker_route(&[ssh_layer("", "")]).unwrap_err();
        assert!(error.contains("SSH host is required"), "{error}");
    }

    #[test]
    fn sqlite_worker_route_accepts_proxy_before_final_ssh() {
        let layers = [proxy_layer("proxy-1"), ssh_layer("203.0.113.10", "testuser")];
        let route = sqlite_worker_route(&layers).unwrap();
        assert_eq!(route.prefix_layers, layers[..1]);
        assert_eq!(route.final_ssh.host, "203.0.113.10");
    }

    #[test]
    fn sqlite_worker_route_keeps_earlier_ssh_hops_in_the_prefix() {
        let mut jump = ssh_layer("203.0.113.10", "jump");
        if let TransportLayerConfig::Ssh(ssh) = &mut jump {
            ssh.expose_lan = true;
        }
        let layers = [jump, ssh_layer("203.0.113.11", "target")];
        let route = sqlite_worker_route(&layers).unwrap();
        assert_eq!(route.prefix_layers.len(), 1);
        let TransportLayerConfig::Ssh(prefix) = &route.prefix_layers[0] else { panic!("expected SSH prefix") };
        assert_eq!(prefix.host, "203.0.113.10");
        assert!(!prefix.expose_lan);
        assert_eq!(route.final_ssh.host, "203.0.113.11");
    }

    #[test]
    fn sqlite_worker_route_rejects_proxy_only() {
        let error = sqlite_worker_route(&[proxy_layer("proxy-1")]).unwrap_err();
        assert!(error.contains("must end with an SSH hop"), "{error}");
    }

    #[test]
    fn sqlite_worker_route_rejects_ssh_followed_by_proxy() {
        let error = sqlite_worker_route(&[ssh_layer("203.0.113.10", "testuser"), proxy_layer("proxy-1")]).unwrap_err();
        assert!(error.contains("must end with an SSH hop"), "{error}");
    }

    #[test]
    fn sqlite_worker_route_rejects_http_tunnel_layers() {
        let layer = serde_json::from_value(serde_json::json!({
            "type": "http_tunnel",
            "id": "http-1",
            "enabled": true,
            "url": "https://example.com/tunnel.php"
        }))
        .unwrap();
        let error = sqlite_worker_route(&[layer, ssh_layer("203.0.113.10", "testuser")]).unwrap_err();
        assert!(error.contains("does not support HTTP tunnel"), "{error}");
    }

    #[test]
    fn proxy_only_sqlite_uses_remote_worker_validation() {
        let mut config = empty_config();
        config.transport_layers = vec![proxy_layer("proxy-1")];
        assert!(sqlite_remote_worker_requested(&config));
    }

    fn proxy_layer(id: &str) -> TransportLayerConfig {
        serde_json::from_value(serde_json::json!({
            "type": "proxy",
            "id": id,
            "enabled": true,
            "host": "203.0.113.10",
            "port": 1080
        }))
        .unwrap()
    }

    fn ssh_layer(host: &str, user: &str) -> TransportLayerConfig {
        serde_json::from_value(serde_json::json!({
            "type": "ssh",
            "id": "hop-1",
            "enabled": true,
            "host": host,
            "port": 22,
            "user": user
        }))
        .unwrap()
    }

    fn empty_config() -> ConnectionConfig {
        serde_json::from_value(serde_json::json!({
            "id": "id",
            "name": "n",
            "db_type": "sqlite",
            "host": "/tmp/a.db",
            "port": 0,
            "username": "",
            "password": ""
        }))
        .unwrap()
    }
}