footing 0.6.5

Feature-gated foundations for Rust backend services
Documentation
use std::{fmt, time::Duration};

use sqlx::SqlitePool;
use time::OffsetDateTime;

use crate::db::sqlite::SqlitePoolError;

use super::{
    OnlineUser, OnlineUsers, PresenceError, validate_config, validate_limit, validate_user_id,
};

/// Frozen initial SQLite schema to copy into the host's versioned migrations.
///
/// Creates `main.footing_presence` and its index. Include it once using the
/// application's migration version; it is not executed automatically and is
/// intentionally not idempotent. Keep the copied migration unchanged. Future
/// schema changes require separate incremental migrations, not edits to this
/// initial schema.
///
/// This backend uses SQLite 3.42 or newer for its millisecond database clock.
/// The `presence-sqlite` feature uses the bundled SQLite supplied by SQLx.
pub const SQLITE_SCHEMA: &str = include_str!("sqlite_v1.sql");

/// SQLite presence operations for one trusted scope and online timeout.
///
/// Clones share the pool and configuration. Construction performs no I/O.
/// Apply [`SQLITE_SCHEMA`] through the host's migrations before using the store.
///
/// Multiple connections or processes on the same machine can share a local
/// database file. SQLite is not a shared network database; use PostgreSQL for
/// service instances on different machines. The host configures pool size,
/// journal mode, busy timeout, and request deadlines. WAL can let readers
/// coexist with a writer, but SQLite still permits only one writer at a time.
/// Lock contention can make an operation wait or fail; this store adds no
/// retries or background work.
///
/// Timestamps are Unix milliseconds sampled by SQLite while executing the
/// statement, using the database host's wall clock. Unlike PostgreSQL's
/// statement-start timestamps, this does not exclude time spent waiting for
/// SQLite to begin the write. Pool waits also precede the timestamp. Clock
/// adjustments can affect expiration; heartbeats never decrease an existing
/// timestamp. Use the same database file for writes and queries.
#[derive(Clone)]
pub struct SqlitePresence {
    pool: SqlitePool,
    scope: String,
    online_timeout: Duration,
}

impl fmt::Debug for SqlitePresence {
    fn fmt(&self, formatter: &mut fmt::Formatter<'_>) -> fmt::Result {
        formatter
            .debug_struct("SqlitePresence")
            .field("online_timeout", &self.online_timeout)
            .finish_non_exhaustive()
    }
}

impl SqlitePresence {
    /// Creates a scoped store without accessing SQLite.
    ///
    /// Scope and user IDs must contain 1–256 UTF-8 bytes and no control
    /// characters. Values are preserved without trimming or case folding.
    /// The timeout must be a positive whole number of milliseconds, at most
    /// [`MAX_ONLINE_TIMEOUT`](super::MAX_ONLINE_TIMEOUT). All stores sharing a
    /// scope, including cleanup jobs, must use the same timeout.
    pub fn new(
        pool: SqlitePool,
        scope: impl Into<String>,
        online_timeout: Duration,
    ) -> Result<Self, PresenceError> {
        let scope = scope.into();
        validate_config(&scope, online_timeout)?;
        Ok(Self {
            pool,
            scope,
            online_timeout,
        })
    }

    /// Returns the store's exact scope.
    pub fn scope(&self) -> &str {
        &self.scope
    }

    /// Returns the online window shared by queries and cleanup.
    pub const fn online_timeout(&self) -> Duration {
        self.online_timeout
    }

    /// Records a heartbeat for a user authenticated by the host application.
    ///
    /// A single atomic UPSERT records database time and preserves any newer
    /// existing timestamp. Concurrent tabs, devices, and connections share one
    /// row. The return value is the stored timestamp, not a client timestamp.
    ///
    /// Identity must come from verified authentication. The host controls
    /// authorization and ensures heartbeats do not extend login sessions.
    /// An interrupted write can have an unknown commit outcome; clients should
    /// send the next regular heartbeat rather than replay buffered requests.
    pub async fn heartbeat(&self, user_id: &str) -> Result<OffsetDateTime, PresenceError> {
        validate_user_id(user_id)?;
        // Drain RETURNING through SQLITE_DONE so a final write/commit failure is
        // observed instead of treating the first returned row as success.
        let timestamps: Vec<i64> = sqlx::query_scalar(
            "INSERT INTO main.footing_presence (scope, user_id, last_seen)
             VALUES (?1, ?2, CAST(round(unixepoch('subsec') * 1000) AS INTEGER))
             ON CONFLICT (scope, user_id) DO UPDATE
             SET last_seen = max(footing_presence.last_seen, excluded.last_seen)
             RETURNING last_seen",
        )
        .bind(&self.scope)
        .bind(user_id)
        .fetch_all(&self.pool)
        .await
        .map_err(SqlitePoolError::from)?;
        decode_timestamp(
            timestamps
                .into_iter()
                .next()
                .ok_or(sqlx::Error::RowNotFound)
                .map_err(SqlitePoolError::from)?,
        )
    }

    /// Counts users whose last heartbeat is newer than this query's cutoff.
    ///
    /// Each scoped user counts once regardless of tabs or devices. Independent
    /// count and list queries can differ as users arrive or expire.
    pub async fn count_online(&self) -> Result<i64, PresenceError> {
        Ok(sqlx::query_scalar(
            "SELECT count(*) FROM main.footing_presence
             WHERE scope = ?1
               AND last_seen > CAST(round(unixepoch('subsec') * 1000) AS INTEGER) - ?2",
        )
        .bind(&self.scope)
        .bind(self.timeout_millis())
        .fetch_one(&self.pool)
        .await
        .map_err(SqlitePoolError::from)?)
    }

    /// Lists online users after an optional exclusive user-ID boundary.
    ///
    /// `limit` must be between 1 and [`MAX_BATCH_SIZE`](super::MAX_BATCH_SIZE).
    /// IDs use SQLite's bytewise `BINARY` collation in the database's encoding.
    /// Ordering can differ from PostgreSQL or another SQLite database using a
    /// different encoding; continue a cursor only in the same scope and database.
    /// Successive pages observe a changing set: users can expire, and newly
    /// active IDs before the boundary require restarting pagination. An empty
    /// next page is valid.
    pub async fn list_online(
        &self,
        after_user_id: Option<&str>,
        limit: u32,
    ) -> Result<OnlineUsers, PresenceError> {
        validate_limit(limit)?;
        if let Some(after) = after_user_id {
            validate_user_id(after)?;
        }
        let rows: Vec<(String, i64)> = sqlx::query_as(
            "SELECT user_id, last_seen FROM main.footing_presence
             WHERE scope = ?1
               AND last_seen > CAST(round(unixepoch('subsec') * 1000) AS INTEGER) - ?2
               AND user_id > ?3 COLLATE BINARY
             ORDER BY user_id LIMIT ?4",
        )
        .bind(&self.scope)
        .bind(self.timeout_millis())
        // Valid IDs are nonempty, making this the first-page lower bound.
        .bind(after_user_id.unwrap_or(""))
        .bind(i64::from(limit) + 1)
        .fetch_all(&self.pool)
        .await
        .map_err(SqlitePoolError::from)?;
        let users = rows
            .into_iter()
            .map(|(user_id, last_seen)| {
                decode_timestamp(last_seen).map(|last_seen| OnlineUser { user_id, last_seen })
            })
            .collect::<Result<Vec<_>, _>>()?;
        Ok(OnlineUsers::from_users(users, limit))
    }

    /// Deletes at most `limit` expired records in this scope, returning the count.
    ///
    /// `limit` must be between 1 and [`MAX_BATCH_SIZE`](super::MAX_BATCH_SIZE).
    /// Selection and deletion happen in one write statement; a concurrent
    /// heartbeat cannot renew a selected row before deletion. SQLite serializes writers, so cleanup can
    /// wait or fail under contention rather than skipping locked rows. A later
    /// heartbeat can recreate a deleted row. Queries exclude expired records
    /// even when cleanup is never called.
    pub async fn prune_expired(&self, limit: u32) -> Result<u64, PresenceError> {
        validate_limit(limit)?;
        Ok(sqlx::query(
            "DELETE FROM main.footing_presence
             WHERE scope = ?1 AND user_id IN (
                 SELECT user_id FROM main.footing_presence
                 WHERE scope = ?1
                   AND last_seen <= CAST(round(unixepoch('subsec') * 1000) AS INTEGER) - ?2
                 ORDER BY last_seen, user_id LIMIT ?3
             )",
        )
        .bind(&self.scope)
        .bind(self.timeout_millis())
        .bind(i64::from(limit))
        .execute(&self.pool)
        .await
        .map_err(SqlitePoolError::from)?
        .rows_affected())
    }

    fn timeout_millis(&self) -> i64 {
        // Configuration validation bounds this to i32::MAX seconds.
        self.online_timeout.as_millis() as i64
    }
}

fn decode_timestamp(milliseconds: i64) -> Result<OffsetDateTime, PresenceError> {
    OffsetDateTime::from_unix_timestamp_nanos(i128::from(milliseconds) * 1_000_000).map_err(
        |error| {
            SqlitePoolError::from(sqlx::Error::ColumnDecode {
                index: "last_seen".into(),
                source: Box::new(error),
            })
            .into()
        },
    )
}