Skip to content

Solution for long term memory for agent coding CLIs and to facilitate handoff between different agent vendors

Rust
35.8 KB
Raw Blame
//! User identity SQL plus human-auth lifecycle.
//!
//! Native API-key hashing stays SHA-256(token || ":" || pepper) for the
//! hot-path UNIQUE lookup. Human passwords never live in this module's
//! KDF — see [`crate::password`].

use ai_memory_core::{NewUser, User, UserId, UserRole};
use jiff::Timestamp;
use rusqlite::{Connection, OptionalExtension, params};
use sha2::{Digest, Sha256};
use subtle::ConstantTimeEq;

use crate::error::{StoreError, StoreResult};
use crate::web_sessions;

/// Length of a SHA-256 digest in bytes.
pub const TOKEN_HASH_LEN: usize = 32;

/// Length of a raw token in bytes (256 bits of entropy).
pub const TOKEN_RAW_LEN: usize = 32;

/// Per-server token pepper, newtyped so it is not mixed with other secrets.
#[derive(Debug, Clone)]
pub struct TokenPepper {
    inner: String,
}

impl TokenPepper {
    /// Wrap a config-loaded pepper.
    #[must_use]
    pub fn new(s: impl Into<String>) -> Self {
        Self { inner: s.into() }
    }

    fn as_bytes(&self) -> &[u8] {
        self.inner.as_bytes()
    }
}

/// `SHA-256(token || ":" || pepper)`.
#[must_use]
pub fn hash_token(token: &str, pepper: &TokenPepper) -> [u8; TOKEN_HASH_LEN] {
    let mut hasher = Sha256::new();
    hasher.update(token.as_bytes());
    hasher.update(b":");
    hasher.update(pepper.as_bytes());
    hasher.finalize().into()
}

/// Constant-time comparison of two hashes.
#[must_use]
pub fn constant_time_eq(a: &[u8], b: &[u8]) -> bool {
    a.ct_eq(b).into()
}

/// Login lookup: public user plus the stored PHC (never serialised).
#[derive(Debug, Clone)]
pub struct LoginUser {
    /// Public identity.
    pub user: User,
    /// Argon2id PHC, if the human has a password.
    pub password_hash: Option<String>,
}

/// Insert a human identity. `password_hash` is already Argon2id PHC (or
/// `None` for API-only brownfield-style rows). No API credential is created.
///
/// # Errors
/// Duplicate username/email, SQL.
pub fn insert_human_user(
    conn: &Connection,
    new_user: &NewUser,
    role: UserRole,
    password_hash: Option<&str>,
    must_change_password: bool,
) -> StoreResult<UserId> {
    let id = UserId::new();
    let now = Timestamp::now().as_microsecond();
    conn.execute(
        "INSERT INTO users \
         (id, username, name, email, created_at, role, password_hash, must_change_password) \
         VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8)",
        params![
            id.as_bytes(),
            &new_user.username,
            &new_user.name,
            &new_user.email,
            now,
            role.as_str(),
            password_hash,
            i64::from(must_change_password),
        ],
    )
    .map_err(map_unique_violation)?;
    Ok(id)
}

/// One-shot root bootstrap. No-op error when already completed — the
/// caller must not invoke this after the marker is set.
///
/// # Errors
/// Duplicate / SQL / already completed.
pub fn bootstrap_root(
    conn: &mut Connection,
    username: &str,
    name: Option<&str>,
    email: Option<&str>,
    password_hash: &str,
) -> StoreResult<UserId> {
    let tx = conn.transaction()?;
    if bootstrap_completed(&tx)? {
        return Err(StoreError::InvalidState(
            "bootstrap already completed".into(),
        ));
    }
    let now = Timestamp::now().as_microsecond();
    let id = if let Some(existing) = find_user_by_username(&tx, username)? {
        tx.execute(
            "UPDATE users SET role = 'root', password_hash = ?1, must_change_password = 1, \
             disabled_at = NULL, name = COALESCE(?2, name), email = COALESCE(?3, email) \
             WHERE id = ?4",
            params![password_hash, name, email, existing.id.as_bytes()],
        )?;
        existing.id
    } else {
        let id = UserId::new();
        tx.execute(
            "INSERT INTO users \
             (id, username, name, email, created_at, role, password_hash, must_change_password) \
             VALUES (?1, ?2, ?3, ?4, ?5, 'root', ?6, 1)",
            params![id.as_bytes(), username, name, email, now, password_hash],
        )
        .map_err(map_unique_violation)?;
        id
    };
    set_bootstrap_completed(&tx, true)?;
    tx.commit()?;
    Ok(id)
}

/// Break-glass root reset: create/promote `username`, write PHC with
/// `must_change_password=false`, revoke that identity's web sessions,
/// mark bootstrap complete. Does not touch API credentials.
///
/// # Errors
/// Duplicate / SQL.
pub fn recover_root(
    conn: &mut Connection,
    username: &str,
    name: Option<&str>,
    email: Option<&str>,
    password_hash: &str,
) -> StoreResult<UserId> {
    let tx = conn.transaction()?;
    let now = Timestamp::now().as_microsecond();
    let id = if let Some(existing) = find_user_by_username(&tx, username)? {
        tx.execute(
            "UPDATE users SET role = 'root', password_hash = ?1, must_change_password = 0, \
             disabled_at = NULL, name = COALESCE(?2, name), email = COALESCE(?3, email) \
             WHERE id = ?4",
            params![password_hash, name, email, existing.id.as_bytes()],
        )?;
        existing.id
    } else {
        let id = UserId::new();
        tx.execute(
            "INSERT INTO users \
             (id, username, name, email, created_at, role, password_hash, must_change_password) \
             VALUES (?1, ?2, ?3, ?4, ?5, 'root', ?6, 0)",
            params![id.as_bytes(), username, name, email, now, password_hash],
        )
        .map_err(map_unique_violation)?;
        id
    };
    web_sessions::revoke_user_sessions(&tx, id, None)?;
    set_bootstrap_completed(&tx, true)?;
    tx.commit()?;
    Ok(id)
}

/// Replace the stored PHC, optionally force a password change, and revoke
/// sessions. Used by admin reset-password.
///
/// # Errors
/// Not found / SQL.
pub fn reset_human_password(
    conn: &mut Connection,
    user_id: UserId,
    password_hash: &str,
    must_change_password: bool,
) -> StoreResult<bool> {
    let tx = conn.transaction()?;
    let rows = tx.execute(
        "UPDATE users SET password_hash = ?1, must_change_password = ?2, disabled_at = disabled_at \
         WHERE id = ?3",
        params![
            password_hash,
            i64::from(must_change_password),
            user_id.as_bytes()
        ],
    )?;
    if rows == 0 {
        return Ok(false);
    }
    web_sessions::revoke_user_sessions(&tx, user_id, None)?;
    tx.commit()?;
    Ok(true)
}

/// Change password for the current live session: write PHC, clear
/// must_change, revoke other sessions, and rotate this session's hashes.
///
/// # Errors
/// Invalid state when the PHC changed, the user was disabled, or the
/// current session was revoked/expired concurrently.
pub fn change_password(
    conn: &mut Connection,
    user_id: UserId,
    expected_password_hash: &str,
    new_password_hash: &str,
    session_id: uuid::Uuid,
    new_session_hash: &[u8; TOKEN_HASH_LEN],
    new_csrf_hash: &[u8; TOKEN_HASH_LEN],
) -> StoreResult<bool> {
    let tx = conn.transaction()?;
    let current: Option<(Option<String>, Option<i64>)> = tx
        .query_row(
            "SELECT password_hash, disabled_at FROM users WHERE id = ?1",
            params![user_id.as_bytes()],
            |row| Ok((row.get(0)?, row.get(1)?)),
        )
        .optional()?;
    match current {
        Some((Some(phc), None)) if phc == expected_password_hash => {}
        _ => {
            return Err(StoreError::InvalidState(
                "credentials changed concurrently".into(),
            ));
        }
    }
    let now = Timestamp::now().as_microsecond();
    let session_live: bool = tx.query_row(
        "SELECT EXISTS(SELECT 1 FROM web_sessions \
         WHERE id = ?1 AND user_id = ?2 AND revoked_at IS NULL AND expires_at > ?3)",
        params![session_id.as_bytes(), user_id.as_bytes(), now],
        |row| row.get(0),
    )?;
    if !session_live {
        return Err(StoreError::InvalidState(
            "session changed concurrently".into(),
        ));
    }
    tx.execute(
        "UPDATE users SET password_hash = ?1, must_change_password = 0 WHERE id = ?2",
        params![new_password_hash, user_id.as_bytes()],
    )?;
    web_sessions::revoke_user_sessions(&tx, user_id, Some(session_id))?;
    if !web_sessions::rotate_web_session_secrets(&tx, session_id, new_session_hash, new_csrf_hash)?
    {
        return Err(StoreError::InvalidState(
            "session changed concurrently".into(),
        ));
    }
    tx.commit()?;
    Ok(true)
}

/// Recheck stored PHC/role/disabled then insert a session.
///
/// # Errors
/// Invalid state when the row no longer matches the verified PHC.
pub fn issue_web_session(
    conn: &mut Connection,
    user_id: UserId,
    expected_password_hash: &str,
    expected_role: UserRole,
    expected_must_change: bool,
    session_hash: &[u8; TOKEN_HASH_LEN],
    csrf_hash: &[u8; TOKEN_HASH_LEN],
) -> StoreResult<web_sessions::WebSession> {
    let tx = conn.transaction()?;
    let row = tx.query_row(
        "SELECT password_hash, role, must_change_password, disabled_at FROM users WHERE id = ?1",
        params![user_id.as_bytes()],
        |r| {
            Ok((
                r.get::<_, Option<String>>(0)?,
                r.get::<_, String>(1)?,
                r.get::<_, bool>(2)?,
                r.get::<_, Option<i64>>(3)?,
            ))
        },
    );
    let (phc, role, must_change, disabled_at) = match row {
        Ok(v) => v,
        Err(rusqlite::Error::QueryReturnedNoRows) => {
            return Err(StoreError::NotFound("user".into()));
        }
        Err(e) => return Err(e.into()),
    };
    if disabled_at.is_some()
        || phc.as_deref() != Some(expected_password_hash)
        || UserRole::parse(&role)? != expected_role
        || must_change != expected_must_change
    {
        return Err(StoreError::InvalidState(
            "credentials no longer valid".into(),
        ));
    }
    let session = web_sessions::insert_web_session(&tx, user_id, session_hash, csrf_hash)?;
    tx.commit()?;
    Ok(session)
}

/// Enable or disable human login. Disable revokes sessions. Never touches
/// API credentials. Refuses to disable the last recoverable root.
///
/// # Errors
/// Last-root invariant / not found / SQL.
pub fn set_user_disabled(
    conn: &mut Connection,
    user_id: UserId,
    disabled: bool,
) -> StoreResult<bool> {
    let tx = conn.transaction()?;
    let Some(user) = find_user_by_id(&tx, user_id)? else {
        return Ok(false);
    };
    if disabled && user.role == UserRole::Root && user.has_password && user.disabled_at.is_none() {
        ensure_not_last_recoverable_root(&tx, user_id)?;
    }
    let now = Timestamp::now().as_microsecond();
    let rows = if disabled {
        tx.execute(
            "UPDATE users SET disabled_at = COALESCE(disabled_at, ?1) WHERE id = ?2",
            params![now, user_id.as_bytes()],
        )?
    } else {
        tx.execute(
            "UPDATE users SET disabled_at = NULL WHERE id = ?1",
            params![user_id.as_bytes()],
        )?
    };
    if disabled {
        web_sessions::revoke_user_sessions(&tx, user_id, None)?;
    }
    tx.commit()?;
    Ok(rows > 0)
}

/// Patch name/email/role with a last-root recheck.
///
/// # Errors
/// Last-root invariant / not found / duplicate email / SQL.
pub fn patch_user(
    conn: &mut Connection,
    user_id: UserId,
    name: Option<Option<String>>,
    email: Option<Option<String>>,
    role: Option<UserRole>,
) -> StoreResult<bool> {
    let tx = conn.transaction()?;
    let Some(user) = find_user_by_id(&tx, user_id)? else {
        return Ok(false);
    };
    if let Some(UserRole::User) = role
        && user.role == UserRole::Root
        && user.has_password
        && user.disabled_at.is_none()
    {
        ensure_not_last_recoverable_root(&tx, user_id)?;
    }
    if let Some(name) = name {
        tx.execute(
            "UPDATE users SET name = ?1 WHERE id = ?2",
            params![name, user_id.as_bytes()],
        )?;
    }
    if let Some(email) = email {
        tx.execute(
            "UPDATE users SET email = ?1 WHERE id = ?2",
            params![email, user_id.as_bytes()],
        )
        .map_err(map_unique_violation)?;
    }
    if let Some(role) = role {
        tx.execute(
            "UPDATE users SET role = ?1 WHERE id = ?2",
            params![role.as_str(), user_id.as_bytes()],
        )?;
    }
    tx.commit()?;
    Ok(true)
}

/// Update `last_seen_at = now()`.
///
/// # Errors
/// SQL failures.
pub fn touch_user_last_seen(conn: &Connection, id: UserId) -> StoreResult<bool> {
    let now = Timestamp::now().as_microsecond();
    let rows = conn.execute(
        "UPDATE users SET last_seen_at = ?1 WHERE id = ?2",
        params![now, id.as_bytes()],
    )?;
    Ok(rows > 0)
}

/// Look up by username.
///
/// # Errors
/// SQL failures.
pub fn find_user_by_username(conn: &Connection, username: &str) -> StoreResult<Option<User>> {
    conn.query_row(
        "SELECT id, username, name, email, created_at, last_seen_at, \
                role, must_change_password, disabled_at, password_hash \
         FROM users WHERE username = ?1",
        params![username],
        |row| row_to_user(row, 0),
    )
    .optional()
    .map_err(StoreError::from)
}

/// Look up by id, including disabled / password-less rows.
///
/// # Errors
/// SQL failures.
pub fn find_user_by_id(conn: &Connection, id: UserId) -> StoreResult<Option<User>> {
    conn.query_row(
        "SELECT id, username, name, email, created_at, last_seen_at, \
                role, must_change_password, disabled_at, password_hash \
         FROM users WHERE id = ?1",
        params![id.as_bytes()],
        |row| row_to_user(row, 0),
    )
    .optional()
    .map_err(StoreError::from)
}

/// Login lookup including the PHC.
///
/// # Errors
/// SQL failures.
pub fn find_login_user_by_username(
    conn: &Connection,
    username: &str,
) -> StoreResult<Option<LoginUser>> {
    conn.query_row(
        "SELECT id, username, name, email, created_at, last_seen_at, \
                role, must_change_password, disabled_at, password_hash \
         FROM users WHERE username = ?1",
        params![username],
        |row| {
            let password_hash: Option<String> = row.get(9)?;
            Ok(LoginUser {
                user: row_to_user(row, 0)?,
                password_hash,
            })
        },
    )
    .optional()
    .map_err(StoreError::from)
}

/// All users, created_at ascending.
///
/// # Errors
/// SQL failures.
pub fn list_users(conn: &Connection) -> StoreResult<Vec<User>> {
    let mut stmt = conn.prepare(
        "SELECT id, username, name, email, created_at, last_seen_at, \
                role, must_change_password, disabled_at, password_hash \
         FROM users ORDER BY created_at ASC",
    )?;
    let rows = stmt
        .query_map([], |row| row_to_user(row, 0))?
        .collect::<Result<Vec<_>, _>>()?;
    Ok(rows)
}

/// Whether any user row exists.
///
/// # Errors
/// SQL failures.
pub fn users_exist(conn: &Connection) -> StoreResult<bool> {
    conn.query_row("SELECT EXISTS(SELECT 1 FROM users)", [], |row| row.get(0))
        .map_err(StoreError::from)
}

/// Whether bootstrap has been marked complete.
///
/// # Errors
/// SQL failures.
pub fn bootstrap_completed(conn: &Connection) -> StoreResult<bool> {
    conn.query_row(
        "SELECT bootstrap_completed FROM human_auth_state WHERE id = 1",
        [],
        |row| {
            let v: i64 = row.get(0)?;
            Ok(v != 0)
        },
    )
    .map_err(StoreError::from)
}

/// Persist the bootstrap marker.
///
/// # Errors
/// SQL failures.
pub fn set_bootstrap_completed(conn: &Connection, completed: bool) -> StoreResult<()> {
    conn.execute(
        "UPDATE human_auth_state SET bootstrap_completed = ?1 WHERE id = 1",
        params![i64::from(completed)],
    )?;
    Ok(())
}

/// True when any user has a password hash.
///
/// # Errors
/// SQL failures.
pub fn any_password_hash(conn: &Connection) -> StoreResult<bool> {
    conn.query_row(
        "SELECT EXISTS(SELECT 1 FROM users WHERE password_hash IS NOT NULL)",
        [],
        |row| row.get(0),
    )
    .map_err(StoreError::from)
}

/// Recoverable roots: `role=root AND password_hash IS NOT NULL AND disabled_at IS NULL`.
///
/// # Errors
/// SQL failures.
pub fn count_recoverable_roots(conn: &Connection) -> StoreResult<i64> {
    conn.query_row(
        "SELECT COUNT(*) FROM users \
         WHERE role = 'root' AND password_hash IS NOT NULL AND disabled_at IS NULL",
        [],
        |row| row.get(0),
    )
    .map_err(StoreError::from)
}

fn ensure_not_last_recoverable_root(conn: &Connection, user_id: UserId) -> StoreResult<()> {
    let count = count_recoverable_roots(conn)?;
    if count <= 1 {
        let is_recoverable: bool = conn.query_row(
            "SELECT EXISTS(SELECT 1 FROM users WHERE id = ?1 \
             AND role = 'root' AND password_hash IS NOT NULL AND disabled_at IS NULL)",
            params![user_id.as_bytes()],
            |row| row.get(0),
        )?;
        if is_recoverable {
            return Err(StoreError::InvalidState(
                "cannot demote or disable the last recoverable root".into(),
            ));
        }
    }
    Ok(())
}

/// Map SQLite UNIQUE-violation errors to a typed `Duplicate`.
pub(crate) fn map_unique_violation(e: rusqlite::Error) -> StoreError {
    if let rusqlite::Error::SqliteFailure(sqlite_err, msg) = &e
        && sqlite_err.code == rusqlite::ErrorCode::ConstraintViolation
    {
        let text = msg.clone().unwrap_or_else(|| sqlite_err.to_string());
        if text.contains("users.username") {
            return StoreError::Duplicate("username already taken".into());
        }
        if text.contains("users.email") {
            return StoreError::Duplicate("email already in use".into());
        }
        if text.contains("token_hash") {
            return StoreError::Duplicate("token hash collision".into());
        }
        return StoreError::Duplicate(text);
    }
    StoreError::from(e)
}

/// Decode a user row starting at `offset`. Relative column 9 is
/// `password_hash`, read only to derive `has_password`.
pub(crate) fn row_to_user(row: &rusqlite::Row<'_>, offset: usize) -> rusqlite::Result<User> {
    let id_bytes: Vec<u8> = row.get(offset)?;
    let id = UserId::from_slice(&id_bytes).map_err(|e| {
        rusqlite::Error::FromSqlConversionFailure(
            offset,
            rusqlite::types::Type::Blob,
            Box::new(std::io::Error::other(e.to_string())),
        )
    })?;
    let role_s: String = row.get(offset + 6)?;
    let role = UserRole::parse(&role_s).map_err(|e| {
        rusqlite::Error::FromSqlConversionFailure(
            offset + 6,
            rusqlite::types::Type::Text,
            Box::new(std::io::Error::other(e.to_string())),
        )
    })?;
    let must: i64 = row.get(offset + 7)?;
    let password_hash: Option<String> = row.get(offset + 9)?;
    Ok(User {
        id,
        username: row.get(offset + 1)?,
        name: row.get(offset + 2)?,
        email: row.get(offset + 3)?,
        created_at: row.get(offset + 4)?,
        last_seen_at: row.get(offset + 5)?,
        role,
        must_change_password: must != 0,
        disabled_at: row.get(offset + 8)?,
        has_password: password_hash.is_some(),
    })
}

#[cfg(test)]
mod tests {
    use super::*;
    use crate::api_credentials;
    use ai_memory_core::NewUser;
    use rusqlite::Connection;

    fn fresh_conn() -> Connection {
        let mut conn = Connection::open_in_memory().unwrap();
        conn.pragma_update(None, "foreign_keys", "ON").unwrap();
        crate::migrations::run(&mut conn).expect("migrations::run");
        conn
    }

    fn sample(name: &str) -> NewUser {
        NewUser {
            username: name.to_string(),
            name: None,
            email: None,
        }
    }

    fn schema_version(conn: &Connection) -> i64 {
        conn.query_row(
            "SELECT MAX(version) FROM refinery_schema_history",
            [],
            |row| row.get(0),
        )
        .unwrap()
    }

    fn assert_foreign_keys_clean(conn: &Connection) {
        let mut stmt = conn.prepare("PRAGMA foreign_key_check").unwrap();
        let violations: Vec<(String, i64, String, i64)> = stmt
            .query_map([], |row| {
                Ok((row.get(0)?, row.get(1)?, row.get(2)?, row.get(3)?))
            })
            .unwrap()
            .collect::<Result<Vec<_>, _>>()
            .unwrap();
        assert!(
            violations.is_empty(),
            "PRAGMA foreign_key_check: {violations:?}"
        );
    }

    #[test]
    fn hash_token_is_deterministic_and_isolates_pepper() {
        let p = TokenPepper::new("test-pepper-1234567890abcdef");
        let h1 = hash_token("hunter2", &p);
        let h2 = hash_token("hunter2", &p);
        assert_eq!(h1, h2);
        let p2 = TokenPepper::new("different-pepper");
        assert_ne!(h1, hash_token("hunter2", &p2));
        assert_ne!(h1, hash_token("hunter3", &p));
    }

    #[test]
    fn insert_human_user_has_no_api_credential() {
        let conn = fresh_conn();
        let mut nu = sample("alice");
        nu.validate().unwrap();
        let id = insert_human_user(&conn, &nu, UserRole::User, Some("phc"), true).unwrap();
        let found = find_user_by_username(&conn, "alice").unwrap().unwrap();
        assert_eq!(found.id, id);
        assert!(found.has_password);
        assert!(found.must_change_password);
        assert_eq!(found.role, UserRole::User);
        assert!(
            api_credentials::list_api_credentials_for_user(&conn, id)
                .unwrap()
                .is_empty()
        );
    }

    #[test]
    fn last_recoverable_root_cannot_be_disabled_or_demoted() {
        let mut conn = fresh_conn();
        let mut nu = sample("root");
        nu.validate().unwrap();
        let id = insert_human_user(&conn, &nu, UserRole::Root, Some("phc"), false).unwrap();
        let err = set_user_disabled(&mut conn, id, true).unwrap_err();
        assert!(
            matches!(&err, StoreError::InvalidState(m) if m.contains("last recoverable root")),
            "{err:?}"
        );
        let err = patch_user(&mut conn, id, None, None, Some(UserRole::User)).unwrap_err();
        assert!(matches!(err, StoreError::InvalidState(_)));
    }

    #[test]
    fn second_root_allows_demoting_the_first() {
        let mut conn = fresh_conn();
        let mut a = sample("root");
        a.validate().unwrap();
        let first = insert_human_user(&conn, &a, UserRole::Root, Some("phc"), false).unwrap();
        let mut b = sample("boss");
        b.validate().unwrap();
        insert_human_user(&conn, &b, UserRole::Root, Some("phc2"), false).unwrap();
        assert!(patch_user(&mut conn, first, None, None, Some(UserRole::User)).unwrap());
        assert_eq!(
            find_user_by_id(&conn, first).unwrap().unwrap().role,
            UserRole::User
        );
    }

    #[test]
    fn recovery_creates_root_and_marks_bootstrap() {
        let mut conn = fresh_conn();
        assert!(!bootstrap_completed(&conn).unwrap());
        let id = recover_root(&mut conn, "root", None, None, "phc").unwrap();
        let user = find_user_by_id(&conn, id).unwrap().unwrap();
        assert_eq!(user.role, UserRole::Root);
        assert!(user.has_password);
        assert!(!user.must_change_password);
        assert!(bootstrap_completed(&conn).unwrap());
    }

    #[test]
    fn bootstrap_root_is_one_shot() {
        let mut conn = fresh_conn();
        bootstrap_root(&mut conn, "root", None, None, "phc-one").unwrap();
        let err = bootstrap_root(&mut conn, "root", None, None, "phc-two").unwrap_err();
        assert!(
            matches!(&err, StoreError::InvalidState(m) if m.contains("already completed")),
            "{err:?}"
        );
        let login = find_login_user_by_username(&conn, "root").unwrap().unwrap();
        assert_eq!(login.password_hash.as_deref(), Some("phc-one"));
        assert!(login.user.must_change_password);
    }

    #[test]
    fn token_only_row_is_not_a_recoverable_root() {
        let mut conn = fresh_conn();
        let mut nu = sample("legacy");
        nu.validate().unwrap();
        let id = insert_human_user(&conn, &nu, UserRole::Root, None, false).unwrap();
        let found = find_user_by_id(&conn, id).unwrap().unwrap();
        assert!(!found.has_password);
        assert_eq!(count_recoverable_roots(&conn).unwrap(), 0);
        assert!(set_user_disabled(&mut conn, id, true).unwrap());
    }

    #[test]
    fn password_change_rejects_a_revoked_current_session_without_updating_phc() {
        let mut conn = fresh_conn();
        let mut nu = sample("alice");
        nu.validate().unwrap();
        let id = insert_human_user(&conn, &nu, UserRole::User, Some("old-phc"), true).unwrap();
        let session =
            web_sessions::insert_web_session(&conn, id, &[1; TOKEN_HASH_LEN], &[2; TOKEN_HASH_LEN])
                .unwrap();
        web_sessions::revoke_session_by_hash(&conn, &[1; TOKEN_HASH_LEN]).unwrap();

        assert!(
            change_password(
                &mut conn,
                id,
                "old-phc",
                "new-phc",
                session.id,
                &[3; TOKEN_HASH_LEN],
                &[4; TOKEN_HASH_LEN],
            )
            .is_err()
        );
        let login = find_login_user_by_username(&conn, "alice")
            .unwrap()
            .unwrap();
        assert_eq!(login.password_hash.as_deref(), Some("old-phc"));
        assert!(login.user.must_change_password);
    }

    #[test]
    fn session_issuance_rechecks_role_and_must_change_state() {
        let mut conn = fresh_conn();
        let mut nu = sample("alice");
        nu.validate().unwrap();
        let id = insert_human_user(&conn, &nu, UserRole::User, Some("phc"), true).unwrap();

        conn.execute(
            "UPDATE users SET role = 'root' WHERE id = ?1",
            params![id.as_bytes()],
        )
        .unwrap();
        assert!(
            issue_web_session(
                &mut conn,
                id,
                "phc",
                UserRole::User,
                true,
                &[1; TOKEN_HASH_LEN],
                &[2; TOKEN_HASH_LEN],
            )
            .is_err()
        );

        conn.execute(
            "UPDATE users SET role = 'user', must_change_password = 0 WHERE id = ?1",
            params![id.as_bytes()],
        )
        .unwrap();
        assert!(
            issue_web_session(
                &mut conn,
                id,
                "phc",
                UserRole::User,
                true,
                &[3; TOKEN_HASH_LEN],
                &[4; TOKEN_HASH_LEN],
            )
            .is_err()
        );
        assert!(
            issue_web_session(
                &mut conn,
                id,
                "phc",
                UserRole::User,
                false,
                &[5; TOKEN_HASH_LEN],
                &[6; TOKEN_HASH_LEN],
            )
            .is_ok()
        );
    }

    #[test]
    fn human_auth_v54_migrates_token_only_user() {
        let mut conn = Connection::open_in_memory().unwrap();
        conn.pragma_update(None, "foreign_keys", "ON").unwrap();
        crate::migrations::run_to(&mut conn, 53).unwrap();
        let id = UserId::new();
        let hash = [7u8; 32];
        conn.execute(
            "INSERT INTO users (id, username, name, email, token_hash, created_at) \
             VALUES (?1, 'alice', NULL, NULL, ?2, 1)",
            params![id.as_bytes(), hash.as_slice()],
        )
        .unwrap();
        crate::migrations::run_to(&mut conn, 54).unwrap();
        let (username, role, token, bootstrap): (String, String, Vec<u8>, i64) = conn
            .query_row(
                "SELECT u.username, u.role, u.token_hash, s.bootstrap_completed \
                 FROM users u, human_auth_state s WHERE u.username = 'alice'",
                [],
                |row| Ok((row.get(0)?, row.get(1)?, row.get(2)?, row.get(3)?)),
            )
            .unwrap();
        assert_eq!(username, "alice");
        assert_eq!(role, "user");
        assert_eq!(token, hash);
        assert_eq!(bootstrap, 0);
        conn.pragma_update(None, "foreign_keys", "OFF").unwrap();
        crate::migrations::run(&mut conn).unwrap();
        conn.pragma_update(None, "foreign_keys", "ON").unwrap();
        let user = find_user_by_id(&conn, id).unwrap().unwrap();
        assert!(!user.has_password);
        assert_eq!(user.username, "alice");
    }

    #[test]
    fn human_auth_v54_empty_store_leaves_bootstrap_incomplete() {
        let mut conn = Connection::open_in_memory().unwrap();
        conn.pragma_update(None, "foreign_keys", "ON").unwrap();
        crate::migrations::run_to(&mut conn, 53).unwrap();
        // SQLite ignores PRAGMA foreign_keys inside refinery's transaction;
        // Store::open disables FKs around migrations for the same reason.
        conn.pragma_update(None, "foreign_keys", "OFF").unwrap();
        crate::migrations::run_to(&mut conn, 54).unwrap();
        conn.pragma_update(None, "foreign_keys", "ON").unwrap();
        assert_eq!(schema_version(&conn), 54);
        assert!(!users_exist(&conn).unwrap());
        assert!(!bootstrap_completed(&conn).unwrap());
        assert_foreign_keys_clean(&conn);
        conn.pragma_update(None, "foreign_keys", "OFF").unwrap();
        crate::migrations::run(&mut conn).unwrap();
        conn.pragma_update(None, "foreign_keys", "ON").unwrap();
        assert!(!bootstrap_completed(&conn).unwrap());
        assert!(!users_exist(&conn).unwrap());
        assert_foreign_keys_clean(&conn);
    }

    #[test]
    fn human_auth_v54_rejects_orphaned_page_author_ids() {
        let mut conn = Connection::open_in_memory().unwrap();
        conn.pragma_update(None, "foreign_keys", "ON").unwrap();
        crate::migrations::run_to(&mut conn, 53).unwrap();
        conn.pragma_update(None, "foreign_keys", "OFF").unwrap();

        let workspace = [1u8; 16];
        let project = [2u8; 16];
        let page = [3u8; 16];
        let missing_author = UserId::new();
        conn.execute(
            "INSERT INTO workspaces (id, name, created_at) VALUES (?1, 'ws', 1)",
            params![workspace.as_slice()],
        )
        .unwrap();
        conn.execute(
            "INSERT INTO projects (id, workspace_id, name, created_at) \
             VALUES (?1, ?2, 'p', 1)",
            params![project.as_slice(), workspace.as_slice()],
        )
        .unwrap();
        conn.execute(
            "INSERT INTO pages \
             (id, workspace_id, project_id, path, title, tier, body, body_sha256, \
              frontmatter_json, is_latest, pinned, created_at, updated_at, author_id) \
             VALUES (?1, ?2, ?3, 'notes/orphan.md', 'title', 'semantic', 'body', ?4, '{}', 1, 0, 1, 1, ?5)",
            params![
                page.as_slice(),
                workspace.as_slice(),
                project.as_slice(),
                [0u8; 32].as_slice(),
                missing_author.as_bytes(),
            ],
        )
        .unwrap();

        let error = crate::migrations::run_to(&mut conn, 54)
            .expect_err("V52 must abort rather than preserve an orphaned page author");
        assert!(
            error.to_string().contains("CHECK constraint failed"),
            "unexpected V52 guard error: {error}"
        );
        assert_eq!(schema_version(&conn), 53, "failed V54 must roll back");
    }

    #[test]
    fn human_auth_v54_ignores_unrelated_preexisting_foreign_key_violations() {
        let mut conn = Connection::open_in_memory().unwrap();
        conn.pragma_update(None, "foreign_keys", "ON").unwrap();
        crate::migrations::run_to(&mut conn, 53).unwrap();
        conn.pragma_update(None, "foreign_keys", "OFF").unwrap();
        conn.execute(
            "INSERT INTO projects (id, workspace_id, name, created_at) \
             VALUES (?1, ?2, 'preexisting-orphan', 1)",
            params![[1u8; 16].as_slice(), [2u8; 16].as_slice()],
        )
        .unwrap();

        crate::migrations::run_to(&mut conn, 54)
            .expect("V52 must guard only references affected by its users-table rebuild");
        assert_eq!(schema_version(&conn), 54);
    }

    #[test]
    fn human_auth_v54_preserves_page_and_audit_author_ids() {
        let mut conn = Connection::open_in_memory().unwrap();
        conn.pragma_update(None, "foreign_keys", "ON").unwrap();
        crate::migrations::run_to(&mut conn, 53).unwrap();
        let author = UserId::new();
        let hash = [9u8; 32];
        conn.execute(
            "INSERT INTO users (id, username, name, email, token_hash, created_at) \
             VALUES (?1, 'alice', NULL, NULL, ?2, 1)",
            params![author.as_bytes(), hash.as_slice()],
        )
        .unwrap();
        let workspace = [1u8; 16];
        let project = [2u8; 16];
        let page = [3u8; 16];
        let body_hash = [0u8; 32];
        conn.execute(
            "INSERT INTO workspaces (id, name, created_at) VALUES (?1, 'ws', 1)",
            params![workspace.as_slice()],
        )
        .unwrap();
        conn.execute(
            "INSERT INTO projects (id, workspace_id, name, created_at) \
             VALUES (?1, ?2, 'p', 1)",
            params![project.as_slice(), workspace.as_slice()],
        )
        .unwrap();
        conn.execute(
            "INSERT INTO pages \
             (id, workspace_id, project_id, path, title, tier, body, body_sha256, \
              frontmatter_json, is_latest, pinned, created_at, updated_at, author_id) \
             VALUES (?1, ?2, ?3, 'notes/a.md', 'title', 'semantic', 'body', ?4, '{}', 1, 0, 1, 1, ?5)",
            params![
                page.as_slice(),
                workspace.as_slice(),
                project.as_slice(),
                body_hash.as_slice(),
                author.as_bytes(),
            ],
        )
        .unwrap();
        conn.execute(
            "INSERT INTO audit_log (at, op, workspace_id, project_id, page_id, detail, author_id) \
             VALUES (1, 'upsert', ?1, ?2, ?3, '{}', ?4)",
            params![
                workspace.as_slice(),
                project.as_slice(),
                page.as_slice(),
                author.as_bytes(),
            ],
        )
        .unwrap();

        conn.pragma_update(None, "foreign_keys", "OFF").unwrap();
        crate::migrations::run_to(&mut conn, 54).unwrap();
        conn.pragma_update(None, "foreign_keys", "ON").unwrap();
        assert_eq!(schema_version(&conn), 54);
        let (user_id, role): (Vec<u8>, String) = conn
            .query_row(
                "SELECT id, role FROM users WHERE username = 'alice'",
                [],
                |row| Ok((row.get(0)?, row.get(1)?)),
            )
            .unwrap();
        assert_eq!(user_id, author.as_bytes());
        assert_eq!(role, "user");
        let page_author: Vec<u8> = conn
            .query_row(
                "SELECT author_id FROM pages WHERE id = ?1",
                params![page.as_slice()],
                |row| row.get(0),
            )
            .unwrap();
        let audit_author: Vec<u8> = conn
            .query_row(
                "SELECT author_id FROM audit_log WHERE page_id = ?1",
                params![page.as_slice()],
                |row| row.get(0),
            )
            .unwrap();
        assert_eq!(page_author, author.as_bytes());
        assert_eq!(audit_author, author.as_bytes());
        assert_foreign_keys_clean(&conn);

        conn.pragma_update(None, "foreign_keys", "OFF").unwrap();
        crate::migrations::run(&mut conn).unwrap();
        conn.pragma_update(None, "foreign_keys", "ON").unwrap();
        let page_author: Vec<u8> = conn
            .query_row(
                "SELECT author_id FROM pages WHERE id = ?1",
                params![page.as_slice()],
                |row| row.get(0),
            )
            .unwrap();
        let audit_author: Vec<u8> = conn
            .query_row(
                "SELECT author_id FROM audit_log WHERE page_id = ?1",
                params![page.as_slice()],
                |row| row.get(0),
            )
            .unwrap();
        assert_eq!(page_author, author.as_bytes());
        assert_eq!(audit_author, author.as_bytes());
        let user = find_user_by_id(&conn, author).unwrap().unwrap();
        assert_eq!(user.id, author);
        assert_eq!(user.username, "alice");
        assert_foreign_keys_clean(&conn);
    }
}