Skip to content

armgit / macro

Public Imported from GitHub
0

Macro is a unified workspace for teams: email, chat, docs, tasks, agents, calls, and CRM — @-linked together with shared AI memory.

81.2 KB
Raw Blame
//! Postgres implementation of the agent session and agent session log
//! repositories, the fold's log source, and the audience a streamed frame is
//! addressed to.

pub mod search;

use messages::domain::models::MessageParent;
use sqlx::types::Json;
#[cfg(test)]
mod test;

mod agent_conversation;
mod auto_archive;
mod pull_request;
mod queue;
mod recovery;
mod session_task;
mod sharing;
mod source;
mod turn_state;
mod work_registration;
mod working_branch;

use crate::domain::coding_preferences::CodingPreferences;
use crate::domain::error::{AgentSessionError, Result};
use crate::domain::model::{
    AgentMcpServers, AgentSession, AgentSessionId, AgentSessionLog, AgentSessionPreviewData,
    ClaimOutcome, CreateAgentSessionParams, ExternalSession, LeaseView, ManagerFence, Message,
    ReplicaAddress, ReplicaId, SandboxSize, SessionArchive, SessionBot, SessionClaim,
    SessionManager, SessionPreviewCandidate, SessionSource, SessionStatus, SessionVisibility,
    StoredAgentSessionLog, StoredQueuedAction, ThreadSession, TurnPrompter, cursor_run_checkpoint,
    session_owner_user,
};
use crate::domain::ports::{
    AgentSessionLogRepo, AgentSessionRepo, ExternalSessionRepo, REPLICA_STALE_AFTER,
    SessionOwnership,
};
use crate::domain::sharing::{SessionBotOwnership, originating_channel_access};
use crate::domain::turn_state::moves_session_list;
use crate::domain::updates::{CodingAgentUpdatesRepo, UpdateCandidate, UpdateTurnState};
use crate::outbound::connection_gateway_realtime::SessionAudience;
use agent_client_protocol::schema::v1::SessionId;
use agent_runtime_protocol::domain::schema::v0::{SystemEvent, ToRuntimeMessage, ToServerMessage};
use anyhow::Context;
use bots::domain::models::BotId;
use bots::domain::ports::BotRepo;
use bots::outbound::pg_bots_repo::PgBotsRepo;
use chrono::{DateTime, Utc};
use entity_access_db_utils::{
    EntityAccessSourceType, EntityType, delete_entity_access_rows, insert_entity_access_row,
};
use entity_registry::BotFacts;
use entity_registry_db_utils::{
    EntityRegistryError, NewEntityRecord, OwnedEntityRegistrar, RegisteredEntityType, WriteOutcome,
    delete_entity, touch_updated,
};
use macro_user_id::user_id::MacroUserIdStr;
use macro_uuid::Uuid;
use model_owner::Owner;
use sqlx::PgPool;
use std::num::NonZeroUsize;

/// Postgres implementation of [`AgentSessionRepo`] and [`AgentSessionLogRepo`].
#[derive(Clone)]
pub struct PgAgentSessionRepo<B> {
    pool: PgPool,
    registrar: OwnedEntityRegistrar<B>,
    list_policy: crate::domain::list_visibility::SessionListPolicy,
}

impl<B: BotFacts + 'static> CodingAgentUpdatesRepo for PgAgentSessionRepo<B> {
    async fn candidates(
        &self,
        owner: MacroUserIdStr<'static>,
    ) -> std::result::Result<Vec<UpdateCandidate>, crate::domain::coding_agents::CodingAgentError>
    {
        let rows = sqlx::query!(
            r#"
            SELECT s.id, s.name, s.harness, s.pull_request_url, s.turn_state,
                   s.archived_by IS NOT NULL AS "archived!", GREATEST(s.modified_at, recent.created_at) AS "last_activity!"
            FROM agent_session s
            LEFT JOIN agent_configs config ON config.bot_id = s.bot_id
            LEFT JOIN LATERAL (
                SELECT created_at FROM agent_session_log
                WHERE agent_session_id = s.id ORDER BY created_at DESC LIMIT 1
            ) recent ON true
            WHERE s.owner_id = $1 AND s.archived_by IS NULL
              AND COALESCE(config.is_coding, s.harness NOT IN ('in-memory', 'macro-inmem'))
              AND (s.turn_state = 'blocked' OR
                   (s.turn_state = 'running' AND GREATEST(s.modified_at, recent.created_at) <= now() - interval '20 minutes'))
            ORDER BY GREATEST(s.modified_at, recent.created_at) DESC, s.id DESC
            LIMIT 26
            "#,
            owner.as_ref(),
        ).fetch_all(&self.pool).await.map_err(|_| crate::domain::coding_agents::CodingAgentError::OperationFailed)?;
        Ok(rows
            .into_iter()
            .filter_map(|row| {
                Some(UpdateCandidate {
                    id: AgentSessionId::new_from_uuid(row.id),
                    name: row.name,
                    harness: row.harness,
                    pull_request_url: row.pull_request_url,
                    turn_state: match row.turn_state.as_deref()? {
                        "blocked" => UpdateTurnState::Blocked,
                        "running" => UpdateTurnState::Running,
                        _ => return None,
                    },
                    archived: row.archived,
                    last_activity: row.last_activity,
                })
            })
            .collect())
    }

    async fn log(
        &self,
        id: AgentSessionId,
    ) -> std::result::Result<
        Vec<StoredAgentSessionLog>,
        crate::domain::coding_agents::CodingAgentError,
    > {
        AgentSessionLogRepo::list_by_session(self, id)
            .await
            .map_err(|_| crate::domain::coding_agents::CodingAgentError::OperationFailed)
    }
}

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

impl<B: BotFacts> PgAgentSessionRepo<B> {
    /// Create a Postgres agent session repository that registers owners
    /// through `registrar`.
    pub fn new(pool: PgPool, registrar: OwnedEntityRegistrar<B>) -> Self {
        Self {
            pool,
            registrar,
            list_policy: Default::default(),
        }
    }

    /// Configure the session domain's staged DM visibility policy.
    pub fn with_list_policy(
        mut self,
        policy: crate::domain::list_visibility::SessionListPolicy,
    ) -> Self {
        self.list_policy = policy;
        self
    }
}

/// The wire name for a [`SessionStatus`] and, for `SessionStatus::Event`, the
/// system event name to store alongside it.
fn status_columns(status: &SessionStatus) -> (&str, Option<String>) {
    let event_name = match status {
        SessionStatus::Event(event) => Some(event.as_str().to_owned()),
        SessionStatus::NoMessages | SessionStatus::Disconnected => None,
    };
    (status.as_ref(), event_name)
}

/// Reverse of [`status_columns`].
fn parse_status(status: &str, event_name: Option<String>) -> anyhow::Result<SessionStatus> {
    match status {
        "no_messages" => Ok(SessionStatus::NoMessages),
        "disconnected" => Ok(SessionStatus::Disconnected),
        "event" => {
            let name = event_name
                .context("agent_session row has status = 'event' with no status_event_name")?;
            let event: SystemEvent = serde_json::from_value(serde_json::Value::String(name))
                .context("failed to parse agent_session status_event_name")?;
            Ok(SessionStatus::Event(event))
        }
        other => anyhow::bail!("unknown agent_session status {other:?}"),
    }
}

fn parse_sandbox_size(value: &str) -> anyhow::Result<SandboxSize> {
    value
        .parse()
        .map_err(|_| anyhow::anyhow!("unknown agent_session sandbox_size {value:?}"))
}

/// The wire direction and JSON payload for a [`Message`].
fn message_columns(message: &Message) -> anyhow::Result<(&'static str, serde_json::Value)> {
    let (direction, mut content) = match message {
        Message::Notification(message) => ("notification", serde_json::to_value(message)?),
        Message::ToServer(message) => ("to_server", serde_json::to_value(message)?),
        Message::ToRuntime(message) => ("to_runtime", serde_json::to_value(message)?),
    };
    // Postgres `jsonb` rejects U+0000, and a failed log insert stops the session.
    strip_json_nuls(&mut content);
    Ok((direction, content))
}

/// Remove U+0000 from every string in `value`, including object keys.
fn strip_json_nuls(value: &mut serde_json::Value) {
    match value {
        serde_json::Value::String(text) => {
            if text.contains('\0') {
                text.retain(|character| character != '\0');
            }
        }
        serde_json::Value::Array(items) => {
            for item in items {
                strip_json_nuls(item);
            }
        }
        serde_json::Value::Object(map) => {
            if map.keys().any(|key| key.contains('\0')) {
                let entries = std::mem::take(map);
                for (key, mut child) in entries {
                    strip_json_nuls(&mut child);
                    map.insert(key.replace('\0', ""), child);
                }
            } else {
                for child in map.values_mut() {
                    strip_json_nuls(child);
                }
            }
        }
        serde_json::Value::Null | serde_json::Value::Bool(_) | serde_json::Value::Number(_) => {}
    }
}

/// Reverse of [`message_columns`].
fn parse_message(direction: &str, content: serde_json::Value) -> anyhow::Result<Message> {
    match direction {
        "to_server" => Ok(Message::ToServer(
            serde_json::from_value::<ToServerMessage>(content)?,
        )),
        "notification" => Ok(Message::Notification(serde_json::from_value(content)?)),
        "to_runtime" => Ok(Message::ToRuntime(serde_json::from_value::<
            ToRuntimeMessage,
        >(content)?)),
        other => anyhow::bail!("unknown agent_session_log direction {other:?}"),
    }
}

/// Record that `user_id` accessed the session in their history.
///
/// The `itemType` is the [`EntityType::AgentSession`] wire name, which is
/// also what `POST /history/agent_session/{id}` writes when the session is
/// opened later, so both paths land on the same row.
async fn upsert_user_history(
    tx: &mut sqlx::Transaction<'_, sqlx::Postgres>,
    user_id: &str,
    session_id: &Uuid,
) -> anyhow::Result<()> {
    sqlx::query!(
        r#"
        INSERT INTO "UserHistory" ("userId", "itemId", "itemType", "createdAt", "updatedAt")
        VALUES ($1, $2, $3, NOW(), NOW())
        ON CONFLICT ("userId", "itemId", "itemType") DO UPDATE
        SET "updatedAt" = NOW()
        "#,
        user_id,
        session_id.to_string(),
        EntityType::AgentSession.as_ref(),
    )
    .execute(tx.as_mut())
    .await?;
    Ok(())
}

fn registry_unknown(
    error: rootcause::Report<entity_registry_db_utils::EntityRegistryError>,
    context: &'static str,
) -> AgentSessionError {
    AgentSessionError::Unknown(anyhow::anyhow!("{error}").context(context))
}

async fn touch_entity_updated(
    tx: &mut sqlx::Transaction<'_, sqlx::Postgres>,
    id: AgentSessionId,
    modified_at: Option<DateTime<Utc>>,
) -> Result<()> {
    let Some(modified_at) = modified_at else {
        return Ok(());
    };
    match touch_updated(tx, id.as_uuid(), modified_at).await {
        Ok(WriteOutcome::Applied | WriteOutcome::NotFound) => Ok(()),
        Err(error) => Err(registry_unknown(
            error,
            "failed to touch agent session entity",
        )),
    }
}

struct AgentSessionRow {
    id: Uuid,
    name: String,
    archived_by: Option<String>,
    archived_at: Option<DateTime<Utc>>,
    owner_id: String,
    thread_id: Option<Uuid>,
    thread_parent: Option<Json<MessageParent>>,
    originating_message_id: Option<Uuid>,
    bot_id: Uuid,
    model: String,
    harness: String,
    repo_url: Option<String>,
    repo_branch: Option<String>,
    pull_request_url: Option<String>,
    task_id: Option<String>,
    source: String,
    source_row: Option<Json<source::SourceRow>>,
    visibility: String,
    workspace: String,
    sandbox_size: String,
    instructions: Option<String>,
    mcp_scope: String,
    mcp_servers: serde_json::Value,
    acp_session_id: Option<String>,
    external_provider: Option<String>,
    external_id: Option<String>,
    external_name: Option<String>,
    external_url: Option<String>,
    external_last_run_id: Option<String>,
    status: String,
    status_event_name: Option<String>,
    created_at: DateTime<Utc>,
    modified_at: DateTime<Utc>,
}

impl TryFrom<AgentSessionRow> for AgentSession {
    type Error = anyhow::Error;

    fn try_from(row: AgentSessionRow) -> anyhow::Result<Self> {
        let status = parse_status(&row.status, row.status_event_name)?;
        Ok(Self {
            id: AgentSessionId::new_from_uuid(row.id),
            name: row.name,
            archived: match (row.archived_by, row.archived_at) {
                (Some(by), Some(at)) => Some(SessionArchive {
                    by: by
                        .parse()
                        .context("agent session has an unknown archive cause")?,
                    at,
                }),
                _ => None,
            },
            owner_id: Owner::from_principal_str(&row.owner_id)
                .context("agent session has an unparseable owner")?,
            thread_id: row.thread_id,
            thread_parent: row.thread_parent.map(|parent| parent.0),
            originating_message_id: row.originating_message_id,
            bot_id: BotId::new_from_uuid(row.bot_id),
            model: row.model,
            harness: row.harness,
            repo_url: row.repo_url,
            repo_branch: row
                .repo_branch
                .map(crate::domain::repository_branch::RepositoryBranch::parse)
                .transpose()
                .map_err(anyhow::Error::msg)?,
            pull_request_url: row.pull_request_url,
            task_id: row.task_id,
            source: row
                .source
                .parse()
                .map_err(|_| anyhow::anyhow!("unknown agent_session source {:?}", row.source))?,
            origin: row
                .source_row
                .map(|Json(source)| source.into_origin())
                .unwrap_or_default(),
            visibility: row.visibility.parse().map_err(|_| {
                anyhow::anyhow!("unknown agent_session visibility {:?}", row.visibility)
            })?,
            workspace: row.workspace,
            sandbox_size: parse_sandbox_size(&row.sandbox_size)?,
            instructions: row.instructions,
            mcp_servers: AgentMcpServers::from_columns(
                &row.mcp_scope,
                serde_json::from_value(row.mcp_servers)
                    .context("agent session has unparseable mcp servers")?,
            )?,
            acp_session_id: row.acp_session_id.map(Into::into),
            external: row
                .external_provider
                .zip(row.external_id)
                .map(|(provider, external_id)| ExternalSession {
                    provider,
                    external_id,
                    external_name: row.external_name,
                    external_url: row.external_url,
                    last_run_id: row.external_last_run_id,
                }),
            status,
            created_at: row.created_at,
            modified_at: row.modified_at,
        })
    }
}

impl<B: BotFacts + 'static> AgentSessionRepo for PgAgentSessionRepo<B> {
    async fn create(&self, params: CreateAgentSessionParams) -> Result<AgentSession> {
        let CreateAgentSessionParams {
            purpose,
            id,
            owner_id,
            bot_id,
            thread_id,
            originating_message_id,
            model,
            harness,
            repo_url,
            repo_branch,
            workspace,
            sandbox_size,
            instructions,
            mcp_servers,
            egress_token_hash,
            origin,
        } = params;
        let mcp_servers_json = serde_json::to_value(mcp_servers.servers())
            .context("serialize agent session mcp servers")?;
        // The owner's grant is a user access row, and the session lands in
        // the owner's history: this store still holds user-owned sessions,
        // even though the denormalized owner_id no longer references "User".
        let owner_user = session_owner_user(&owner_id)?;

        // The session row and its access grants land together: a crash between
        // the two would leave a session nobody - not even its owner -
        // could open.
        let mut transaction = self
            .pool
            .begin()
            .await
            .context("begin agent session create")?;

        let known_owner = sqlx::query_scalar!(
            r#"SELECT id FROM "User" WHERE id = $1"#,
            owner_user.as_ref(),
        )
        .fetch_optional(&mut *transaction)
        .await
        .context("failed to confirm the session owner")?;
        if known_owner.is_none() {
            return Err(AgentSessionError::UnknownOwner);
        }

        let source_id = source::insert_source(&mut transaction, &origin).await?;
        let (status, status_event_name) = status_columns(&SessionStatus::NoMessages);
        let conversation = sqlx::query_scalar!(
            "SELECT EXISTS (SELECT 1 FROM agent_conversation_sessions WHERE session_id = $1) AS \"exists!\"",
            id.as_uuid(),
        )
        .fetch_one(&mut *transaction)
        .await
        .context("read session creation provenance")?;
        let purpose = purpose.for_creation(
            conversation,
            thread_id.is_some() || originating_message_id.is_some(),
        )?;
        let visibility = purpose.visibility(thread_id, self.list_policy.hide_agent_dms);
        let warm = purpose == crate::domain::model::SessionPurpose::Warm;
        let row = sqlx::query_as!(
            AgentSessionRow,
            r#"
            INSERT INTO agent_session (
                id, owner_id, thread_id, originating_message_id, bot_id, model,
                harness, repo_url, workspace, sandbox_size, instructions,
                acp_session_id, status, status_event_name, egress_token_hash,
                mcp_scope, mcp_servers, repo_branch, for_warming, source, visibility, source_id
            )
            VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21, $22)
            RETURNING
                id, name, archived_by::TEXT AS "archived_by?", archived_at, owner_id, thread_id, originating_message_id, bot_id,
                model, harness, repo_url, repo_branch, pull_request_url, task_id, source, visibility, workspace, sandbox_size, instructions,
                mcp_scope, mcp_servers, acp_session_id, status,
                status_event_name, created_at, modified_at,
                (SELECT jsonb_build_object('type', parent_entity_type, 'id', parent_entity_id)
                 FROM comms_messages WHERE id = agent_session.thread_id)
                    AS "thread_parent?: Json<MessageParent>",
                (SELECT to_jsonb(origin_row) FROM agent_session_source origin_row
                 WHERE origin_row.id = agent_session.source_id)
                    AS "source_row?: Json<source::SourceRow>",
                -- A row being created cannot have an external identity yet.
                NULL::TEXT AS "external_provider?", NULL::TEXT AS "external_id?",
                NULL::TEXT AS "external_name?", NULL::TEXT AS "external_url?",
                NULL::TEXT AS "external_last_run_id?"
            "#,
            id.as_uuid(),
            owner_user.as_ref(),
            thread_id,
            originating_message_id,
            bot_id.as_uuid(),
            model,
            harness,
            repo_url,
            workspace,
            sandbox_size.as_str(),
            instructions,
            None::<String>,
            status,
            status_event_name,
            egress_token_hash,
            mcp_servers.scope_str(),
            mcp_servers_json,
            repo_branch.as_ref().map(|branch| branch.as_str()),
            warm,
            <&str>::from(purpose.source()),
            <&str>::from(visibility),
            source_id,
        )
        .fetch_one(&mut *transaction)
        .await
        .map_err(
            |error| match error.as_database_error().and_then(|e| e.constraint()) {
                Some("agent_session_pkey") => AgentSessionError::SessionIdTaken(id),
                Some("agent_session_thread_bot_unique") => AgentSessionError::ThreadSessionExists,
                _ => AgentSessionError::Unknown(
                    anyhow::Error::new(error).context("failed to create agent session"),
                ),
            },
        )?;

        self.registrar
            .register_owned_entity(
                &mut transaction,
                NewEntityRecord::new(
                    id.as_uuid(),
                    RegisteredEntityType::AgentSession,
                    owner_id.clone(),
                ),
            )
            .await
            .map_err(|error| match *error.current_context() {
                EntityRegistryError::RegistrationConflict => AgentSessionError::SessionIdTaken(id),
                _ => registry_unknown(error, "failed to register the agent session"),
            })?;

        // The channel the bot was invoked in gets the session, at the level
        // `originating_channel_access` grants. Read from the message rather
        // than taken from the caller, so the channel is always the one the
        // message actually sits in. A session created without a message -
        // directly, rather than from a channel - is its owner's alone.
        let origin_channel_id = match originating_message_id {
            Some(message_id) => sqlx::query_scalar!(
                r#"SELECT parent_entity_id::uuid AS "channel_id!" FROM comms_messages WHERE parent_entity_type = 'channel' AND id = $1"#,
                message_id,
            )
            .fetch_optional(&mut *transaction)
            .await
            .context("failed to read the originating message's channel")?,
            None => None,
        };

        if let Some(channel_id) = origin_channel_id {
            // Read in the transaction, so the grant matches the bot's owner
            // at creation. System bots have no row.
            let owned_by_user = sqlx::query_scalar!(
                r#"SELECT owner_user_id IS NOT NULL AS "owned_by_user!" FROM bots WHERE id = $1"#,
                bot_id.as_uuid(),
            )
            .fetch_optional(&mut *transaction)
            .await
            .context("failed to read the session bot's owner")?
            .unwrap_or(false);
            let bot = if owned_by_user {
                SessionBotOwnership::User
            } else {
                SessionBotOwnership::Shared
            };
            insert_entity_access_row(
                &mut transaction,
                &id.as_uuid(),
                EntityType::AgentSession,
                &channel_id.to_string(),
                EntityAccessSourceType::Channel,
                originating_channel_access(bot),
            )
            .await
            .context("failed to grant the originating channel access to the agent session")?;
        }

        // Creating a session counts as viewing it, as it does for chats: the
        // row is what Soup's `viewed_at` and the frecency ranking read, so
        // without it a brand-new session would rank below everything the
        // owner has ever opened.
        if !warm {
            upsert_user_history(&mut transaction, owner_user.as_ref(), &id.as_uuid())
                .await
                .context("failed to record the agent session in the owner's history")?;
        }

        transaction
            .commit()
            .await
            .context("commit agent session create")?;

        Ok(row.try_into()?)
    }

    async fn get(&self, id: AgentSessionId) -> Result<AgentSession> {
        self.find(id).await?.ok_or(AgentSessionError::NotFound(id))
    }

    async fn find(&self, id: AgentSessionId) -> Result<Option<AgentSession>> {
        let row = sqlx::query_as!(
            AgentSessionRow,
            r#"
            SELECT
                id, name, archived_by::TEXT AS "archived_by?", archived_at, owner_id, thread_id, originating_message_id, bot_id,
                model, harness, repo_url, repo_branch, pull_request_url, task_id, source, visibility, workspace, sandbox_size, instructions,
                mcp_scope, mcp_servers, acp_session_id, status,
                status_event_name, agent_session.created_at, modified_at,
                (SELECT jsonb_build_object('type', parent_entity_type, 'id', parent_entity_id)
                 FROM comms_messages WHERE id = agent_session.thread_id)
                    AS "thread_parent?: Json<MessageParent>",
                (SELECT to_jsonb(origin_row) FROM agent_session_source origin_row
                 WHERE origin_row.id = agent_session.source_id)
                    AS "source_row?: Json<source::SourceRow>",
                ext.provider AS "external_provider?", ext.external_id AS "external_id?",
                ext.external_name AS "external_name?", ext.external_url AS "external_url?",
                ext.last_run_id AS "external_last_run_id?"
            FROM agent_session
            LEFT JOIN external_agent_session AS ext ON ext.agent_session_id = agent_session.id
            WHERE id = $1
            "#,
            id.as_uuid(),
        )
        .fetch_optional(&self.pool)
        .await
        .context("failed to get agent session")?;

        Ok(row.map(AgentSession::try_from).transpose()?)
    }

    async fn preview(
        &self,
        viewer: &MacroUserIdStr<'static>,
        ids: &[AgentSessionId],
    ) -> Result<Vec<SessionPreviewCandidate>> {
        let uuids: Vec<Uuid> = ids.iter().map(AgentSessionId::as_uuid).collect();
        // The viewer's grant sources - themselves, the channels they are still
        // in, their teams - are the same three the Soup leg and the access
        // extractor resolve, so a preview agrees with what a read would do.
        // Access inherited from an originating document has no row; the
        // thread parent is returned so the service can ask for it.
        let rows = sqlx::query!(
            r#"
            WITH viewer_source_ids AS (
                SELECT cp.channel_id::text AS source_id
                FROM comms_channel_participants cp
                WHERE cp.user_id = $2 AND cp.left_at IS NULL
                UNION ALL
                SELECT t.team_id::text FROM team_user t WHERE t.user_id = $2
                UNION ALL
                SELECT $2
            )
            SELECT
                s.id,
                s.name,
                s.owner_id,
                s.bot_id,
                s.status,
                s.status_event_name,
                s.created_at,
                s.modified_at,
                EXISTS (
                    SELECT 1 FROM entity_access ea
                    WHERE ea.entity_id = s.id
                      AND ea.entity_type = 'agent_session'
                      AND ea.source_id IN (SELECT source_id FROM viewer_source_ids)
                ) AS "has_access!",
                (SELECT jsonb_build_object('type', parent_entity_type, 'id', parent_entity_id)
                 FROM comms_messages WHERE id = s.thread_id)
                    AS "thread_parent?: Json<MessageParent>"
            FROM agent_session s
            WHERE s.id = ANY($1)
            "#,
            &uuids,
            viewer.as_ref(),
        )
        .fetch_all(&self.pool)
        .await
        .context("failed to preview agent sessions")?;

        rows.into_iter()
            .map(|row| {
                let id = AgentSessionId::new_from_uuid(row.id);
                Ok(SessionPreviewCandidate {
                    data: AgentSessionPreviewData {
                        bot: None,
                        id,
                        name: row.name,
                        owner_id: Owner::from_principal_str(&row.owner_id)
                            .context("agent session has an unparseable owner")?,
                        bot_id: BotId::new_from_uuid(row.bot_id),
                        status: parse_status(&row.status, row.status_event_name)?,
                        created_at: row.created_at,
                        modified_at: row.modified_at,
                    },
                    has_grant: row.has_access,
                    thread_parent: row.thread_parent.map(|parent| parent.0),
                })
            })
            .collect::<anyhow::Result<Vec<_>>>()
            .map_err(Into::into)
    }

    async fn find_by_egress_token_hash(
        &self,
        egress_token_hash: &str,
    ) -> Result<Option<AgentSession>> {
        // Matched in SQL rather than fetched and compared: the unique partial
        // index on this column does the work, and nothing secret-derived is
        // compared byte by byte in this process.
        let row = sqlx::query_as!(
            AgentSessionRow,
            r#"
            SELECT
                id, name, archived_by::TEXT AS "archived_by?", archived_at, owner_id, thread_id, originating_message_id, bot_id,
                model, harness, repo_url, repo_branch, pull_request_url, task_id, source, visibility, workspace, sandbox_size, instructions,
                mcp_scope, mcp_servers, acp_session_id, status,
                status_event_name, agent_session.created_at, modified_at,
                (SELECT jsonb_build_object('type', parent_entity_type, 'id', parent_entity_id)
                 FROM comms_messages WHERE id = agent_session.thread_id)
                    AS "thread_parent?: Json<MessageParent>",
                (SELECT to_jsonb(origin_row) FROM agent_session_source origin_row
                 WHERE origin_row.id = agent_session.source_id)
                    AS "source_row?: Json<source::SourceRow>",
                ext.provider AS "external_provider?", ext.external_id AS "external_id?",
                ext.external_name AS "external_name?", ext.external_url AS "external_url?",
                ext.last_run_id AS "external_last_run_id?"
            FROM agent_session
            LEFT JOIN external_agent_session AS ext ON ext.agent_session_id = agent_session.id
            WHERE egress_token_hash = $1
            "#,
            egress_token_hash,
        )
        .fetch_optional(&self.pool)
        .await
        .context("failed to find agent session by egress token hash")?;

        Ok(match row {
            Some(row) => Some(row.try_into()?),
            None => None,
        })
    }

    async fn find_for_thread(
        &self,
        thread_id: Option<Uuid>,
        bot_id: Option<BotId>,
    ) -> Result<ThreadSession> {
        // Both are required to match: a session is only reachable from the
        // thread it was created from, by the bot that runs it. NULL params
        // match nothing rather than everything.
        let (Some(thread_id), Some(bot_id)) = (thread_id, bot_id) else {
            return Ok(ThreadSession::None);
        };
        let row = sqlx::query_as!(
            AgentSessionRow,
            r#"
            SELECT
                id, name, archived_by::TEXT AS "archived_by?", archived_at, owner_id, thread_id, originating_message_id, bot_id,
                model, harness, repo_url, repo_branch, pull_request_url, task_id, source, visibility, workspace, sandbox_size, instructions,
                mcp_scope, mcp_servers, acp_session_id, status,
                status_event_name, agent_session.created_at, modified_at,
                (SELECT jsonb_build_object('type', parent_entity_type, 'id', parent_entity_id)
                 FROM comms_messages WHERE id = agent_session.thread_id)
                    AS "thread_parent?: Json<MessageParent>",
                (SELECT to_jsonb(origin_row) FROM agent_session_source origin_row
                 WHERE origin_row.id = agent_session.source_id)
                    AS "source_row?: Json<source::SourceRow>",
                ext.provider AS "external_provider?", ext.external_id AS "external_id?",
                ext.external_name AS "external_name?", ext.external_url AS "external_url?",
                ext.last_run_id AS "external_last_run_id?"
            FROM agent_session
            LEFT JOIN external_agent_session AS ext ON ext.agent_session_id = agent_session.id
            WHERE thread_id = $1 AND bot_id = $2
            ORDER BY agent_session.created_at DESC
            LIMIT 1
            "#,
            thread_id,
            bot_id.as_uuid(),
        )
        .fetch_optional(&self.pool)
        .await
        .context("failed to find agent session for channel context")?;

        Ok(match row {
            Some(row) => ThreadSession::CreatedFromThread(row.try_into()?),
            None => ThreadSession::None,
        })
    }

    async fn find_all_for_thread(&self, thread_id: Uuid) -> Result<Vec<AgentSession>> {
        let rows = sqlx::query_as!(
            AgentSessionRow,
            r#"
            SELECT
                id, name, archived_by::TEXT AS "archived_by?", archived_at, owner_id, thread_id, originating_message_id, bot_id,
                model, harness, repo_url, repo_branch, pull_request_url, task_id, source, visibility, workspace, sandbox_size, instructions,
                mcp_scope, mcp_servers, acp_session_id, status,
                status_event_name, agent_session.created_at, modified_at,
                (SELECT jsonb_build_object('type', parent_entity_type, 'id', parent_entity_id)
                 FROM comms_messages WHERE id = agent_session.thread_id)
                    AS "thread_parent?: Json<MessageParent>",
                (SELECT to_jsonb(origin_row) FROM agent_session_source origin_row
                 WHERE origin_row.id = agent_session.source_id)
                    AS "source_row?: Json<source::SourceRow>",
                ext.provider AS "external_provider?", ext.external_id AS "external_id?",
                ext.external_name AS "external_name?", ext.external_url AS "external_url?",
                ext.last_run_id AS "external_last_run_id?"
            FROM agent_session
            LEFT JOIN external_agent_session AS ext ON ext.agent_session_id = agent_session.id
            WHERE thread_id = $1
            ORDER BY agent_session.created_at DESC
            "#,
            thread_id,
        )
        .fetch_all(&self.pool)
        .await
        .context("failed to find agent sessions for thread")?;

        Ok(rows
            .into_iter()
            .map(AgentSession::try_from)
            .collect::<anyhow::Result<Vec<_>>>()?)
    }

    async fn recent_for_owner(
        &self,
        owner: &MacroUserIdStr<'_>,
        limit: NonZeroUsize,
    ) -> Result<Vec<AgentSession>> {
        let rows = sqlx::query_as!(
            AgentSessionRow,
            r#"
            SELECT
                id, name, archived_by::TEXT AS "archived_by?", archived_at, owner_id, thread_id, originating_message_id, bot_id,
                model, harness, repo_url, repo_branch, pull_request_url, task_id, source, visibility, workspace, sandbox_size, instructions,
                mcp_scope, mcp_servers, acp_session_id, status,
                status_event_name, agent_session.created_at, modified_at,
                (SELECT jsonb_build_object('type', parent_entity_type, 'id', parent_entity_id)
                 FROM comms_messages WHERE id = agent_session.thread_id)
                    AS "thread_parent?: Json<MessageParent>",
                (SELECT to_jsonb(origin_row) FROM agent_session_source origin_row
                 WHERE origin_row.id = agent_session.source_id)
                    AS "source_row?: Json<source::SourceRow>",
                ext.provider AS "external_provider?", ext.external_id AS "external_id?",
                ext.external_name AS "external_name?", ext.external_url AS "external_url?",
                ext.last_run_id AS "external_last_run_id?"
            FROM agent_session
            LEFT JOIN external_agent_session AS ext ON ext.agent_session_id = agent_session.id
            WHERE owner_id = $1 AND visibility <> 'hidden'
            ORDER BY agent_session.created_at DESC, id DESC
            LIMIT $2
            "#,
            owner.as_ref(),
            i64::try_from(limit.get()).unwrap_or(i64::MAX),
        )
        .fetch_all(&self.pool)
        .await
        .context("failed to list the owner's recent agent sessions")?;

        Ok(rows
            .into_iter()
            .map(AgentSession::try_from)
            .collect::<anyhow::Result<Vec<_>>>()?)
    }

    async fn session_bot(&self, id: BotId) -> Result<SessionBot> {
        // Delegated to the bots hex rather than a bespoke query: this is
        // exactly bot id -> bot, and `get_bot` already excludes deleted bots -
        // which is what this wants, since a deleted bot's old messages should
        // render from the "Agent" fallback below, not its stale name.
        let bot = PgBotsRepo::new(self.pool.clone())
            .get_bot(id)
            .await
            .context("failed to read the session's bot")?;

        // A deleted (or never-existing) bot still has messages in the
        // channel. Falling back to a generic name renders those as from an
        // unknown agent rather than failing the whole log.
        Ok(match bot {
            Some(bot) => SessionBot {
                id,
                name: bot.name,
                handle: bot.handle,
                avatar_url: bot.avatar_url,
            },
            None => SessionBot {
                id,
                name: "Agent".to_owned(),
                handle: "agent".to_owned(),
                avatar_url: None,
            },
        })
    }

    async fn set_acp_session_id(
        &self,
        id: AgentSessionId,
        acp_session_id: SessionId,
    ) -> Result<()> {
        let acp_session_id = acp_session_id.to_string();
        let result = sqlx::query!(
            r#"
            UPDATE agent_session
            SET acp_session_id = $2
            WHERE id = $1
            "#,
            id.as_uuid(),
            acp_session_id,
        )
        .execute(&self.pool)
        .await
        .context("failed to persist ACP session id")?;

        if result.rows_affected() == 0 {
            return Err(anyhow::anyhow!("agent session not found").into());
        }
        Ok(())
    }

    async fn set_egress_token_hash(&self, id: AgentSessionId, hash: &str) -> Result<()> {
        sqlx::query!(
            "UPDATE agent_session SET egress_token_hash = $2 WHERE id = $1",
            id.as_uuid(),
            hash
        )
        .execute(&self.pool)
        .await
        .context("failed to rotate session credential")?;
        Ok(())
    }

    async fn set_turn_prompter(&self, id: AgentSessionId, prompter: &TurnPrompter) -> Result<()> {
        sqlx::query!(
            "UPDATE agent_session SET turn_action_id = $2, turn_prompter = $3, turn_prompter_kind = $4 WHERE id = $1",
            id.as_uuid(),
            prompter.action_id.as_uuid(),
            prompter.user.as_ref().filter(|_| prompter.kind != crate::domain::model::PrompterKind::System).map(|user| user.as_ref()),
            prompter.kind.as_ref(),
        )
        .execute(&self.pool)
        .await
        .context("failed to record the turn prompter")?;
        Ok(())
    }

    async fn add_follower(&self, id: AgentSessionId, user: &MacroUserIdStr<'static>) -> Result<()> {
        sqlx::query!(
            r#"
            INSERT INTO agent_session_follower (agent_session_id, user_id)
            VALUES ($1, $2)
            ON CONFLICT DO NOTHING
            "#,
            id.as_uuid(),
            user.as_ref(),
        )
        .execute(&self.pool)
        .await
        .context("failed to record the session's follower")?;
        Ok(())
    }

    async fn turn_prompter(&self, id: AgentSessionId) -> Result<Option<TurnPrompter>> {
        let row = sqlx::query!(
            r#"
            SELECT turn_action_id, turn_prompter_kind, turn_prompter AS "turn_prompter: MacroUserIdStr<'static>"
            FROM agent_session
            WHERE id = $1
            "#,
            id.as_uuid(),
        )
        .fetch_optional(&self.pool)
        .await
        .context("failed to read the turn prompter")?;
        Ok(row.and_then(|row| {
            row.turn_action_id.map(|action_id| TurnPrompter {
                kind: if row.turn_prompter_kind == "system" {
                    crate::domain::model::PrompterKind::System
                } else {
                    crate::domain::model::PrompterKind::User
                },
                action_id: agent_runtime_protocol::domain::action::AgentActionId::from_uuid(
                    action_id,
                ),
                user: row.turn_prompter,
            })
        }))
    }

    async fn set_repo_url(&self, id: AgentSessionId, repo_url: Option<String>) -> Result<()> {
        let result = sqlx::query!(
            r#"
            UPDATE agent_session
            SET repo_url = $2,
                working_branch = NULL,
                modified_at = NOW()
            WHERE id = $1
              AND repo_url IS DISTINCT FROM $2
            "#,
            id.as_uuid(),
            repo_url,
        )
        .execute(&self.pool)
        .await
        .context("failed to persist agent session repository")?;
        tracing::debug!(%id, changed = result.rows_affected() > 0, "agent session repository set");
        Ok(())
    }

    async fn set_model(&self, id: AgentSessionId, model: &str) -> Result<()> {
        let mut transaction = self
            .pool
            .begin()
            .await
            .context("begin agent session set_model")?;
        let modified_at = sqlx::query_scalar!(
            r#"
            UPDATE agent_session
            SET model = $2,
                modified_at = NOW()
            WHERE id = $1
              AND model IS DISTINCT FROM $2
            RETURNING modified_at
            "#,
            id.as_uuid(),
            model,
        )
        .fetch_optional(&mut *transaction)
        .await
        .context("failed to persist agent session model")?;
        touch_entity_updated(&mut transaction, id, modified_at).await?;
        transaction
            .commit()
            .await
            .context("commit agent session set_model")?;
        Ok(())
    }

    async fn set_origin(
        &self,
        id: AgentSessionId,
        source: SessionSource,
        visibility: SessionVisibility,
    ) -> Result<()> {
        let mut transaction = self
            .pool
            .begin()
            .await
            .context("begin agent session set_origin")?;
        let modified_at = sqlx::query_scalar!(
            r#"
            UPDATE agent_session
            SET source = $2,
                visibility = $3,
                modified_at = NOW()
            WHERE id = $1
              AND (source <> $2 OR visibility <> $3)
            RETURNING modified_at
            "#,
            id.as_uuid(),
            <&str>::from(source),
            <&str>::from(visibility),
        )
        .fetch_optional(&mut *transaction)
        .await
        .context("failed to persist agent session origin")?;
        // Lists re-read the session, so an unlisted run leaves the ones that
        // exclude unlisted sessions.
        touch_entity_updated(&mut transaction, id, modified_at).await?;
        transaction
            .commit()
            .await
            .context("commit agent session set_origin")?;
        Ok(())
    }

    async fn set_name(&self, id: AgentSessionId, name: &str) -> Result<()> {
        let mut transaction = self
            .pool
            .begin()
            .await
            .context("begin agent session set_name")?;
        let row = sqlx::query!(
            r#"
            WITH previous AS (
                SELECT modified_at
                FROM agent_session
                WHERE id = $1
                FOR UPDATE
            )
            UPDATE agent_session
            SET name = $2,
                name_is_generated = false,
                modified_at = CASE
                    WHEN name IS DISTINCT FROM $2 THEN NOW()
                    ELSE modified_at
                END
            WHERE id = $1
            RETURNING
                modified_at,
                (modified_at IS DISTINCT FROM (SELECT modified_at FROM previous)) AS "bumped!"
            "#,
            id.as_uuid(),
            name,
        )
        .fetch_optional(&mut *transaction)
        .await
        .context("failed to persist agent session name")?;

        let Some(row) = row else {
            return Err(anyhow::anyhow!("agent session not found").into());
        };
        touch_entity_updated(&mut transaction, id, row.bumped.then_some(row.modified_at)).await?;
        transaction
            .commit()
            .await
            .context("commit agent session set_name")?;
        Ok(())
    }

    async fn set_archived(&self, id: AgentSessionId, archived: bool) -> Result<()> {
        let mut transaction = self
            .pool
            .begin()
            .await
            .context("begin agent session set_archived")?;
        // Archiving an archived session keeps who archived it; restoring clears it.
        let modified_at = sqlx::query_scalar!(
            r#"
            UPDATE agent_session
            SET archived_by = CASE
                    WHEN $2 THEN COALESCE(archived_by, 'user'::agent_session_archive_cause)
                END,
                archived_at = CASE WHEN $2 THEN COALESCE(archived_at, NOW()) END,
                modified_at = CASE
                    WHEN (archived_by IS NOT NULL) IS DISTINCT FROM $2 THEN NOW()
                    ELSE modified_at
                END
            WHERE id = $1
            RETURNING modified_at
            "#,
            id.as_uuid(),
            archived,
        )
        .fetch_optional(&mut *transaction)
        .await
        .context("failed to persist agent session archive state")?;
        let Some(modified_at) = modified_at else {
            return Err(anyhow::anyhow!("agent session not found").into());
        };
        touch_entity_updated(&mut transaction, id, Some(modified_at)).await?;
        if archived {
            work_registration::end_work_registrations_on_archive(&mut transaction, id).await?;
        }
        transaction
            .commit()
            .await
            .context("commit agent session set_archived")?;
        Ok(())
    }

    async fn is_current_conversation_session(&self, id: AgentSessionId) -> Result<bool> {
        Ok(sqlx::query_scalar!(
            r#"
            SELECT EXISTS (
                SELECT 1
                FROM agent_conversation_sessions
                WHERE session_id = $1
                  AND is_current
            ) AS "current!"
            "#,
            id.as_uuid(),
        )
        .fetch_one(&self.pool)
        .await
        .context("failed to read whether the session answers a conversation")?)
    }

    async fn set_name_if_default(&self, id: AgentSessionId, name: &str) -> Result<bool> {
        let mut transaction = self
            .pool
            .begin()
            .await
            .context("begin agent session set_name_if_default")?;
        let modified_at = sqlx::query_scalar!(
            r#"
            UPDATE agent_session
            SET name = $2,
                name_is_generated = true,
                modified_at = NOW()
            WHERE id = $1
              AND name = $3
              AND archived_by IS NULL
            RETURNING modified_at
            "#,
            id.as_uuid(),
            name,
            crate::domain::model::DEFAULT_AGENT_SESSION_NAME,
        )
        .fetch_optional(&mut *transaction)
        .await
        .context("failed to persist generated agent session name")?;
        touch_entity_updated(&mut transaction, id, modified_at).await?;
        transaction
            .commit()
            .await
            .context("commit agent session set_name_if_default")?;
        Ok(modified_at.is_some())
    }

    async fn generated_name(&self, id: AgentSessionId) -> Result<Option<String>> {
        Ok(sqlx::query_scalar!(
            r#"
            SELECT name
            FROM agent_session
            WHERE id = $1
              AND name_is_generated
              AND archived_by IS NULL
            "#,
            id.as_uuid(),
        )
        .fetch_optional(&self.pool)
        .await
        .context("failed to read generated agent session name")?)
    }

    async fn replace_generated_name(
        &self,
        id: AgentSessionId,
        current: &str,
        name: &str,
    ) -> Result<bool> {
        let mut transaction = self
            .pool
            .begin()
            .await
            .context("begin agent session replace_generated_name")?;
        let modified_at = sqlx::query_scalar!(
            r#"
            UPDATE agent_session
            SET name = $3,
                modified_at = NOW()
            WHERE id = $1
              AND name = $2
              AND name_is_generated
              AND archived_by IS NULL
            RETURNING modified_at
            "#,
            id.as_uuid(),
            current,
            name,
        )
        .fetch_optional(&mut *transaction)
        .await
        .context("failed to persist retitled agent session name")?;
        touch_entity_updated(&mut transaction, id, modified_at).await?;
        transaction
            .commit()
            .await
            .context("commit agent session replace_generated_name")?;
        Ok(modified_at.is_some())
    }

    async fn set_sandbox_size(&self, id: AgentSessionId, size: SandboxSize) -> Result<()> {
        sqlx::query!(
            r#"
            UPDATE agent_session
            SET sandbox_size = $2,
                modified_at = NOW()
            WHERE id = $1
              AND sandbox_size IS DISTINCT FROM $2
            "#,
            id.as_uuid(),
            size.as_str(),
        )
        .execute(&self.pool)
        .await
        .context("failed to persist agent session sandbox size")?;
        Ok(())
    }

    async fn user_sandbox_size(&self, user_id: &MacroUserIdStr<'static>) -> Result<SandboxSize> {
        let size = sqlx::query_scalar!(
            r#"
            SELECT sandbox_size
            FROM user_agent_sandbox_size
            WHERE user_id = $1
            "#,
            user_id.as_ref(),
        )
        .fetch_optional(&self.pool)
        .await
        .context("failed to read user sandbox size")?;

        match size {
            Some(value) => Ok(parse_sandbox_size(&value)?),
            None => Ok(SandboxSize::Default),
        }
    }

    async fn set_user_sandbox_size(
        &self,
        user_id: &MacroUserIdStr<'static>,
        size: SandboxSize,
    ) -> Result<()> {
        sqlx::query!(
            r#"
            INSERT INTO user_agent_sandbox_size (user_id, sandbox_size, modified_at)
            VALUES ($1, $2, NOW())
            ON CONFLICT (user_id) DO UPDATE
            SET sandbox_size = EXCLUDED.sandbox_size,
                modified_at = NOW()
            "#,
            user_id.as_ref(),
            size.as_str(),
        )
        .execute(&self.pool)
        .await
        .context("failed to persist user sandbox size")?;
        Ok(())
    }

    async fn user_coding_preferences(
        &self,
        user_id: &MacroUserIdStr<'static>,
    ) -> Result<CodingPreferences> {
        let preferences = sqlx::query!(
            r#"
            SELECT create_tasks, open_pull_requests
            FROM user_agent_coding_preferences
            WHERE user_id = $1
            "#,
            user_id.as_ref(),
        )
        .fetch_optional(&self.pool)
        .await
        .context("failed to read user coding preferences")?;
        Ok(preferences
            .map(|row| CodingPreferences {
                create_tasks: row.create_tasks,
                open_pull_requests: row.open_pull_requests,
            })
            .unwrap_or_default())
    }

    async fn set_user_coding_preferences(
        &self,
        user_id: &MacroUserIdStr<'static>,
        preferences: CodingPreferences,
    ) -> Result<()> {
        sqlx::query!(
            r#"
            INSERT INTO user_agent_coding_preferences
                (user_id, create_tasks, open_pull_requests, modified_at)
            VALUES ($1, $2, $3, NOW())
            ON CONFLICT (user_id) DO UPDATE
            SET create_tasks = EXCLUDED.create_tasks,
                open_pull_requests = EXCLUDED.open_pull_requests,
                modified_at = NOW()
            "#,
            user_id.as_ref(),
            preferences.create_tasks,
            preferences.open_pull_requests,
        )
        .execute(&self.pool)
        .await
        .context("failed to persist user coding preferences")?;
        Ok(())
    }

    async fn list_queued_actions(&self, id: AgentSessionId) -> Result<Vec<StoredQueuedAction>> {
        self.load_queued_actions(id).await
    }

    async fn replace_queued_actions(
        &self,
        id: AgentSessionId,
        entries: &[StoredQueuedAction],
    ) -> Result<()> {
        self.store_queued_actions(id, entries).await
    }

    async fn delete(&self, id: AgentSessionId) -> Result<()> {
        let mut transaction = self
            .pool
            .begin()
            .await
            .context("begin agent session delete")?;

        // `entity_access.entity_id` is polymorphic and so cannot carry a
        // foreign key: nothing reaps these rows when the session goes, and
        // they would accumulate forever.
        delete_entity_access_rows(&mut transaction, &id.as_uuid(), EntityType::AgentSession)
            .await
            .context("failed to delete agent session entity access rows")?;

        match delete_entity(&mut transaction, id.as_uuid()).await {
            Ok(WriteOutcome::Applied | WriteOutcome::NotFound) => {}
            Err(error) => {
                return Err(registry_unknown(
                    error,
                    "failed to delete the agent session entity",
                ));
            }
        }

        // Same story for history: `"UserHistory"."itemId"` is polymorphic
        // text, so the session's rows in every viewer's history go here.
        sqlx::query!(
            r#"DELETE FROM "UserHistory" WHERE "itemId" = $1 AND "itemType" = $2"#,
            id.as_uuid().to_string(),
            EntityType::AgentSession.as_ref(),
        )
        .execute(&mut *transaction)
        .await
        .context("failed to delete agent session history rows")?;

        // A session old enough to have owned a dedicated channel leaves it
        // behind: it holds the history that channel renders, and is not this
        // operation's to destroy.
        let permission = sqlx::query!(
            r#"
            DELETE FROM agent_session
            WHERE id = $1
            RETURNING share_permission_id, source_id
            "#,
            id.as_uuid(),
        )
        .fetch_optional(&mut *transaction)
        .await
        .context("failed to delete agent session")?;

        if let Some(source_id) = permission.as_ref().and_then(|row| row.source_id) {
            source::delete_source(&mut transaction, source_id).await?;
        }

        if let Some(permission_id) = permission.and_then(|row| row.share_permission_id) {
            sqlx::query!(
                r#"DELETE FROM "SharePermission" WHERE id = $1"#,
                permission_id
            )
            .execute(&mut *transaction)
            .await
            .context("failed to delete agent session sharing settings")?;
        }

        transaction
            .commit()
            .await
            .context("commit agent session delete")?;

        Ok(())
    }
}

struct AgentSessionLogRow {
    id: Uuid,
    agent_session_id: Uuid,
    user_id: Option<MacroUserIdStr<'static>>,
    direction: String,
    content: serde_json::Value,
    created_at: DateTime<Utc>,
}

impl TryFrom<AgentSessionLogRow> for StoredAgentSessionLog {
    type Error = anyhow::Error;

    fn try_from(row: AgentSessionLogRow) -> anyhow::Result<Self> {
        Ok(Self {
            id: row.id,
            created_at: row.created_at,
            entry: AgentSessionLog {
                agent_session_id: AgentSessionId::new_from_uuid(row.agent_session_id),
                user_id: row.user_id,
                content: parse_message(&row.direction, row.content)?,
                logged_at: Some(row.created_at),
            },
        })
    }
}

impl<B: BotFacts + 'static> ExternalSessionRepo for PgAgentSessionRepo<B> {
    #[tracing::instrument(skip(self), err)]
    async fn upsert(&self, id: AgentSessionId, external: ExternalSession) -> Result<()> {
        sqlx::query!(
            r#"
            INSERT INTO external_agent_session (
                agent_session_id, provider, external_id, external_name, external_url
            )
            VALUES ($1, $2, $3, $4, $5)
            ON CONFLICT (agent_session_id) DO UPDATE SET
                provider = EXCLUDED.provider,
                external_id = EXCLUDED.external_id,
                external_name = EXCLUDED.external_name,
                external_url = EXCLUDED.external_url,
                updated_at = now()
            "#,
            id.as_uuid(),
            external.provider,
            external.external_id,
            external.external_name,
            external.external_url,
        )
        .execute(&self.pool)
        .await
        .context("upsert external agent session")?;
        Ok(())
    }

    #[tracing::instrument(skip(self), err)]
    async fn get(&self, id: AgentSessionId) -> Result<Option<ExternalSession>> {
        let row = sqlx::query!(
            r#"
            SELECT provider, external_id, external_name, external_url, last_run_id
            FROM external_agent_session
            WHERE agent_session_id = $1
            "#,
            id.as_uuid(),
        )
        .fetch_optional(&self.pool)
        .await
        .context("get external agent session")?;
        Ok(row.map(|row| ExternalSession {
            provider: row.provider,
            external_id: row.external_id,
            external_name: row.external_name,
            external_url: row.external_url,
            last_run_id: row.last_run_id,
        }))
    }

    #[tracing::instrument(skip(self), err)]
    async fn delete(&self, id: AgentSessionId) -> Result<()> {
        sqlx::query!(
            "DELETE FROM external_agent_session WHERE agent_session_id = $1",
            id.as_uuid(),
        )
        .execute(&self.pool)
        .await
        .context("delete external agent session")?;
        Ok(())
    }
}

impl<B: BotFacts + 'static> AgentSessionLogRepo for PgAgentSessionRepo<B> {
    async fn create(&self, log: AgentSessionLog) -> Result<StoredAgentSessionLog> {
        self.create_projected(log, None, None, None).await
    }

    async fn create_fenced(
        &self,
        log: AgentSessionLog,
        claim: &SessionClaim,
    ) -> Result<StoredAgentSessionLog> {
        self.create_fenced_with_boundary(log, claim, None).await
    }

    async fn create_batch_fenced(
        &self,
        entries: Vec<StoredAgentSessionLog>,
        claim: &SessionClaim,
    ) -> Result<Vec<StoredAgentSessionLog>> {
        if entries.is_empty() {
            return Ok(entries);
        }
        if entries
            .iter()
            .any(|stored| stored.entry.agent_session_id != claim.session)
        {
            return Err(AgentSessionError::FencedOut(claim.session));
        }
        let session = claim.session;

        let mut ids = Vec::with_capacity(entries.len());
        let mut user_ids: Vec<Option<String>> = Vec::with_capacity(entries.len());
        let mut directions: Vec<String> = Vec::with_capacity(entries.len());
        let mut contents = Vec::with_capacity(entries.len());
        for stored in &entries {
            let (direction, content) = message_columns(&stored.entry.content)?;
            ids.push(stored.id);
            user_ids.push(
                stored
                    .entry
                    .user_id
                    .as_ref()
                    .map(|user_id| user_id.as_ref().to_owned()),
            );
            directions.push(direction.to_owned());
            contents.push(content);
        }

        let mut transaction = self
            .pool
            .begin()
            .await
            .context("begin fenced agent session log batch create")?;

        // Hold the session row through commit, as the single-frame write
        // does: a takeover updates this same row, so it cannot supersede the
        // claim between our check and the insert.
        let locked_session = sqlx::query_scalar!(
            r#"
            SELECT id
            FROM agent_session
            WHERE id = $1 AND manager_replica_id = $2 AND manager_fence = $3
            FOR UPDATE
            "#,
            session.as_uuid(),
            claim.replica.as_uuid(),
            claim.fence.0,
        )
        .fetch_optional(&mut *transaction)
        .await
        .context("lock fenced agent session for batch")?;
        if locked_session.is_none() {
            return Err(AgentSessionError::FencedOut(session));
        }

        // Start after the durable tail while holding the session lock, then
        // spread the batch over consecutive microseconds. Transaction start
        // time can predate a lock wait or a previous batch's synthetic tail;
        // readers must see append order rather than UUID tie-breaking.
        let stamped = sqlx::query!(
            r#"
            INSERT INTO agent_session_log (id, agent_session_id, user_id, direction, content, created_at)
            SELECT frame.id, $1, frame.user_id, frame.direction, frame.content,
                   stamp.created_at + (frame.ordinality - 1) * interval '1 microsecond'
            FROM UNNEST($2::uuid[], $3::text[], $4::text[], $5::jsonb[])
                WITH ORDINALITY AS frame(id, user_id, direction, content, ordinality)
            CROSS JOIN (
                SELECT GREATEST(statement_timestamp(), (
                    SELECT created_at + interval '1 microsecond'
                    FROM agent_session_log WHERE agent_session_id = $1
                    ORDER BY created_at DESC, id DESC LIMIT 1
                )) AS created_at
            ) AS stamp
            RETURNING id, created_at
            "#,
            session.as_uuid(),
            &ids,
            &user_ids as &[Option<String>],
            &directions,
            &contents,
        )
        .fetch_all(&mut *transaction)
        .await
        .context("failed to create fenced agent session log batch")?;
        transaction
            .commit()
            .await
            .context("commit fenced agent session log batch create")?;

        let stamps: std::collections::HashMap<Uuid, chrono::DateTime<chrono::Utc>> = stamped
            .into_iter()
            .map(|row| (row.id, row.created_at))
            .collect();
        entries
            .into_iter()
            .map(|stored| {
                let created_at = stamps.get(&stored.id).copied().ok_or_else(|| {
                    anyhow::anyhow!("batch insert returned no row for log entry {}", stored.id)
                })?;
                Ok(StoredAgentSessionLog {
                    created_at,
                    ..stored
                })
            })
            .collect()
    }

    async fn create_fenced_with_boundary(
        &self,
        log: AgentSessionLog,
        claim: &SessionClaim,
        boundary: Option<crate::domain::model::HistoryBoundary>,
    ) -> Result<StoredAgentSessionLog> {
        self.create_projected(log, Some(claim), boundary, None)
            .await
    }

    async fn create_projected(
        &self,
        log: AgentSessionLog,
        claim: Option<&SessionClaim>,
        boundary: Option<crate::domain::model::HistoryBoundary>,
        turn_state: Option<agent_fold::domain::model::TurnState>,
    ) -> Result<StoredAgentSessionLog> {
        if claim.is_some_and(|claim| claim.session != log.agent_session_id)
            || (boundary.is_some() && claim.is_none())
        {
            return Err(AgentSessionError::FencedOut(log.agent_session_id));
        }
        let event_status = match &log.content {
            Message::ToServer(ToServerMessage::Event { event }) => {
                Some(SessionStatus::Event(event.clone()))
            }
            _ => None,
        };
        let checkpoint = cursor_run_checkpoint(&log.content);
        let (direction, content) = message_columns(&log.content)?;
        let mut transaction = self
            .pool
            .begin()
            .await
            .context("begin fenced agent session log create")?;

        if let Some(claim) = claim {
            // Hold the session row through commit. A takeover updates this same
            // row, so it cannot supersede the claim between our check and append.
            let locked_session = sqlx::query_scalar!(
                r#"
            SELECT id
            FROM agent_session
            WHERE id = $1 AND manager_replica_id = $2 AND manager_fence = $3
            FOR UPDATE
            "#,
                log.agent_session_id.as_uuid(),
                claim.replica.as_uuid(),
                claim.fence.0,
            )
            .fetch_optional(&mut *transaction)
            .await
            .context("lock fenced agent session")?;
            if locked_session.is_none() {
                return Err(AgentSessionError::FencedOut(log.agent_session_id));
            }
        }

        let id = macro_uuid::generate_uuid_v7();
        let created_at = sqlx::query_scalar!(
            r#"
            INSERT INTO agent_session_log (id, agent_session_id, user_id, direction, content, created_at)
            VALUES ($1, $2, $3, $4, $5, GREATEST(statement_timestamp(), (
                SELECT created_at + interval '1 microsecond'
                FROM agent_session_log WHERE agent_session_id = $2
                ORDER BY created_at DESC, id DESC LIMIT 1
            )))
            RETURNING created_at
            "#,
            id,
            log.agent_session_id.as_uuid(),
            log.user_id.as_ref().map(|user_id| user_id.as_ref()),
            direction,
            content,
        )
        .fetch_one(&mut *transaction)
        .await
        .context("failed to create fenced agent session log entry")?;

        if let (Some(boundary), Some(claim)) = (boundary, claim) {
            let updated = sqlx::query!(
                r#"
                UPDATE agent_session AS session
                SET history_start_log_id = boundary.id
                FROM agent_session_log AS boundary
                WHERE session.id = $1
                  AND session.manager_replica_id = $2 AND session.manager_fence = $3
                  AND boundary.id = $4 AND boundary.agent_session_id = session.id
                  AND (boundary.created_at, boundary.id) <= ($5, $6)
                "#,
                log.agent_session_id.as_uuid(),
                claim.replica.as_uuid(),
                claim.fence.0,
                boundary.initialization_log_id,
                created_at,
                id,
            )
            .execute(&mut *transaction)
            .await
            .context("select successful load history boundary")?;
            if updated.rows_affected() != 1 {
                return Err(AgentSessionError::Handshake(
                    "invalid history boundary".into(),
                ));
            }
        }

        if let (Some(run_id), Some(claim)) = (checkpoint, claim) {
            let updated = sqlx::query!(
                r#"
                UPDATE external_agent_session AS external
                SET last_run_id = $2,
                    updated_at = now()
                FROM agent_session AS session
                WHERE external.agent_session_id = $1
                  AND session.id = external.agent_session_id
                  AND session.manager_replica_id = $3
                  AND session.manager_fence = $4
                  AND external.provider = 'cursor'
                "#,
                log.agent_session_id.as_uuid(),
                run_id,
                claim.replica.as_uuid(),
                claim.fence.0,
            )
            .execute(&mut *transaction)
            .await
            .context("checkpoint cursor run with fenced log entry")?;
            if updated.rows_affected() == 0 {
                return Err(AgentSessionError::FencedOut(log.agent_session_id));
            }
        }

        if let Some(status) = event_status {
            let (status, status_event_name) = status_columns(&status);
            sqlx::query!(
                r#"
                UPDATE agent_session
                SET status = $2,
                    status_event_name = $3
                WHERE id = $1
                "#,
                log.agent_session_id.as_uuid(),
                status,
                status_event_name,
            )
            .execute(&mut *transaction)
            .await
            .context("failed to update agent session status from fenced log entry")?;
        }

        if let Some(turn_state) = turn_state {
            sqlx::query!(
                r#"
                UPDATE agent_session
                SET turn_state = $2,
                    modified_at = CASE WHEN $3 THEN now() ELSE modified_at END
                WHERE id = $1 AND turn_state IS DISTINCT FROM $2
                "#,
                log.agent_session_id.as_uuid(),
                turn_state.as_ref(),
                moves_session_list(turn_state),
            )
            .execute(&mut *transaction)
            .await
            .context("project agent session turn state with log entry")?;
        }

        transaction
            .commit()
            .await
            .context("commit fenced agent session log create")?;

        Ok(StoredAgentSessionLog {
            id,
            created_at,
            entry: log,
        })
    }

    async fn list_by_session(
        &self,
        agent_session_id: AgentSessionId,
    ) -> Result<Vec<StoredAgentSessionLog>> {
        let rows = sqlx::query_as!(
            AgentSessionLogRow,
            r#"
            SELECT
                log.id,
                log.agent_session_id,
                log.user_id AS "user_id: MacroUserIdStr",
                log.direction,
                log.content,
                log.created_at
            FROM agent_session_log AS log
            WHERE log.agent_session_id = $1
              AND (log.created_at, log.id) >= (
                  SELECT COALESCE(boundary.created_at, '-infinity'::timestamptz),
                         COALESCE(boundary.id, '00000000-0000-0000-0000-000000000000'::uuid)
                  FROM agent_session AS session
                  LEFT JOIN LATERAL (
                    SELECT id, created_at FROM agent_session_log
                    WHERE id = session.history_start_log_id
                      AND agent_session_id = $1
                      AND NOT EXISTS (
                          SELECT 1 FROM agent_session_log AS trusted
                          WHERE trusted.agent_session_id = $1
                            AND trusted.direction IN ('notification', 'to_runtime')
                            AND trusted.content->'params'->'_meta' ? 'macro.dev/notifications'
                      )
                    LIMIT 1
                  ) AS boundary ON true
                  WHERE session.id = $1
              )
            ORDER BY log.created_at ASC, log.id ASC
            "#,
            agent_session_id.as_uuid(),
        )
        .fetch_all(&self.pool)
        .await
        .context("failed to list agent session log entries")?;

        Ok(rows
            .into_iter()
            .map(TryInto::try_into)
            .collect::<anyhow::Result<Vec<_>>>()?)
    }

    async fn list_action_history(
        &self,
        agent_session_id: AgentSessionId,
        action: agent_runtime_protocol::domain::action::AgentActionId,
    ) -> Result<Vec<StoredAgentSessionLog>> {
        let rows = sqlx::query_as!(
            AgentSessionLogRow,
            r#"
            SELECT id, agent_session_id, user_id AS "user_id: MacroUserIdStr",
                   direction, content, created_at
            FROM agent_session_log
            WHERE agent_session_id = $1
              AND EXISTS (
                  SELECT 1 FROM agent_session_log AS prompt
                  WHERE prompt.agent_session_id = $1
                    AND prompt.direction IN ('to_runtime', 'notification')
                    AND prompt.content->>'method' = 'session/prompt'
                    AND prompt.content->>'id' = $2
              )
            ORDER BY created_at, id
            "#,
            agent_session_id.as_uuid(),
            action.to_string(),
        )
        .fetch_all(&self.pool)
        .await
        .context("failed to read durable action history")?;
        Ok(rows
            .into_iter()
            .map(TryInto::try_into)
            .collect::<anyhow::Result<Vec<_>>>()?)
    }

    async fn participants(
        &self,
        agent_session_id: AgentSessionId,
    ) -> Result<Vec<MacroUserIdStr<'static>>> {
        let users = sqlx::query_scalar!(
            r#"
            SELECT DISTINCT log.user_id AS "user_id!: MacroUserIdStr"
            FROM agent_session_log AS log
            WHERE log.agent_session_id = $1
              AND log.user_id IS NOT NULL
            "#,
            agent_session_id.as_uuid(),
        )
        .fetch_all(&self.pool)
        .await
        .context("failed to list agent session participants")?;
        Ok(users)
    }
}

impl<B: BotFacts + 'static> SessionOwnership for PgAgentSessionRepo<B> {
    async fn claim(&self, session: AgentSessionId, replica: ReplicaId) -> Result<ClaimOutcome> {
        // One statement: the replica's heartbeat row is upserted in the CTE
        // (a claim can never reference a replica the store has not seen),
        // then the lease itself is a compare-and-swap - taken only from
        // nobody, ourselves, or a stale holder, and every take bumps the
        // fence so a superseded holder's fenced writes stop matching.
        let fence = sqlx::query_scalar!(
            r#"
            WITH replica AS (
                INSERT INTO harness_replica (id, last_heartbeat_at)
                VALUES ($2, now())
                ON CONFLICT (id) DO UPDATE SET last_heartbeat_at = now()
                RETURNING id
            )
            UPDATE agent_session
            SET manager_replica_id = $2,
                manager_fence = manager_fence + 1
            FROM replica
            WHERE agent_session.id = $1 AND replica.id = $2
              AND (
                manager_replica_id IS NULL
                OR manager_replica_id = $2
                OR NOT EXISTS (
                    SELECT 1 FROM harness_replica live
                    WHERE live.id = agent_session.manager_replica_id
                      AND live.last_heartbeat_at > now() - make_interval(secs => $3)
                      AND live.draining_at IS NULL
                )
              )
            RETURNING manager_fence
            "#,
            session.as_uuid(),
            replica.as_uuid(),
            REPLICA_STALE_AFTER.as_secs_f64(),
        )
        .fetch_optional(&self.pool)
        .await
        .context("failed to claim agent session management")?;

        if let Some(fence) = fence {
            return Ok(ClaimOutcome::Claimed(SessionClaim {
                session,
                replica,
                fence: ManagerFence(fence),
            }));
        }

        // The swap matched nothing: either a live replica holds the lease, or
        // the session row is gone. A deleted session reads as Unknown so the
        // caller does not forward commands toward a row that no longer exists.
        let holder = sqlx::query_scalar!(
            r#"SELECT manager_replica_id FROM agent_session WHERE id = $1"#,
            session.as_uuid(),
        )
        .fetch_optional(&self.pool)
        .await
        .context("failed to read the agent session manager")?;
        match holder {
            Some(Some(holder)) => Ok(ClaimOutcome::ManagedElsewhere(ReplicaId::from_uuid(holder))),
            Some(None) | None => Err(AgentSessionError::Unknown(anyhow::anyhow!(
                "agent session {session} claim matched nothing yet no live replica holds it"
            ))),
        }
    }

    async fn release(&self, claim: &SessionClaim) -> Result<()> {
        // Conditional on both holder and fence: a release arriving after a
        // successor claimed must not free the successor's lease, and a
        // deleted session matches nothing, which is the asked-for state.
        sqlx::query!(
            r#"
            UPDATE agent_session
            SET manager_replica_id = NULL
            WHERE id = $1 AND manager_replica_id = $2 AND manager_fence = $3
            "#,
            claim.session.as_uuid(),
            claim.replica.as_uuid(),
            claim.fence.0,
        )
        .execute(&self.pool)
        .await
        .context("failed to release agent session management")?;
        Ok(())
    }

    async fn heartbeat(&self, replica: ReplicaId, address: Option<&ReplicaAddress>) -> Result<()> {
        // COALESCE keeps a previously published address when a beat carries
        // none, so a claim-created row filled in by one heartbeat is not
        // blanked by the next.
        sqlx::query!(
            r#"
            INSERT INTO harness_replica (id, last_heartbeat_at, address)
            VALUES ($1, now(), $2)
            ON CONFLICT (id) DO UPDATE
            SET last_heartbeat_at = now(),
                address = COALESCE(EXCLUDED.address, harness_replica.address)
            "#,
            replica.as_uuid(),
            address.map(ReplicaAddress::as_str),
        )
        .execute(&self.pool)
        .await
        .context("failed to heartbeat harness replica")?;
        // Housekeeping on the writer that is already here: rows a week past
        // their last heartbeat are boots nothing can still reference usefully
        // (their claims were stealable within seconds); the FK sets any
        // stragglers' claims to NULL.
        sqlx::query!(
            r#"DELETE FROM harness_replica WHERE last_heartbeat_at < now() - interval '7 days'"#,
        )
        .execute(&self.pool)
        .await
        .context("failed to prune stale harness replicas")?;
        Ok(())
    }

    async fn lease_view(&self, session: AgentSessionId, replica: ReplicaId) -> Result<LeaseView> {
        // One statement for both halves: the holder comes from the session's
        // lease, the asking replica's own drain from its heartbeat row, and
        // reading them apart could straddle the moment this replica started
        // draining and route a command to itself anyway. Built outward from
        // the asked-for id rather than from `agent_session`, so a session
        // that does not exist still answers the drain half.
        let row = sqlx::query!(
            r#"
            SELECT
                live.id AS "replica_id?",
                live.address AS "address?",
                (live.draining_at IS NOT NULL) AS "holder_draining?",
                EXISTS (
                    SELECT 1 FROM harness_replica me
                    WHERE me.id = $3 AND me.draining_at IS NOT NULL
                ) AS "asking_replica_draining!"
            FROM (SELECT $1::uuid AS id) AS asked
            LEFT JOIN agent_session ON agent_session.id = asked.id
            LEFT JOIN harness_replica live
                ON live.id = agent_session.manager_replica_id
               AND live.last_heartbeat_at > now() - make_interval(secs => $2)
            "#,
            session.as_uuid(),
            REPLICA_STALE_AFTER.as_secs_f64(),
            replica.as_uuid(),
        )
        .fetch_one(&self.pool)
        .await
        .context("failed to read the agent session's lease")?;
        Ok(LeaseView {
            holder: row.replica_id.map(|replica_id| SessionManager {
                replica: ReplicaId::from_uuid(replica_id),
                address: row.address.map(ReplicaAddress::new),
                draining: row.holder_draining.unwrap_or(false),
            }),
            asking_replica_draining: row.asking_replica_draining,
        })
    }

    async fn begin_draining(&self, replica: ReplicaId) -> Result<()> {
        // Upsert, not update: a replica that has not heartbeated yet still
        // has to be able to say it is leaving, and the row it creates is
        // stale from birth - which is exactly what it means.
        sqlx::query!(
            r#"
            INSERT INTO harness_replica (id, draining_at)
            VALUES ($1, now())
            ON CONFLICT (id) DO UPDATE
            SET draining_at = COALESCE(harness_replica.draining_at, now())
            "#,
            replica.as_uuid(),
        )
        .execute(&self.pool)
        .await
        .context("failed to publish that the harness replica is draining")?;
        Ok(())
    }
}

/// Folding reads the log through `agent_fold`'s own port; this adapter
/// already speaks [`AgentSessionLogRepo`], so bridging is one line.
impl<B: BotFacts + 'static> agent_fold::domain::ports::LogRepo for PgAgentSessionRepo<B> {
    async fn list_by_session(
        &self,
        session: AgentSessionId,
    ) -> std::result::Result<std::collections::VecDeque<AgentSessionLog>, rootcause::Report> {
        let log = AgentSessionLogRepo::list_by_session(self, session)
            .await
            .map_err(|error| rootcause::report!(error))?;
        Ok(log.into_iter().map(|stored| stored.entry).collect())
    }
}

/// Who a channel's frames go to: everyone still in it.
///
/// A participant who has left keeps their row, with `left_at` set - so the
/// filter is what stops a former member being sent a session they can no
/// longer open.
impl<B: BotFacts + 'static> SessionAudience for PgAgentSessionRepo<B> {
    async fn viewers(
        &self,
        agent_session_id: AgentSessionId,
    ) -> std::result::Result<Vec<MacroUserIdStr<'static>>, rootcause::Report> {
        // The owner, which is who the dedicated channel's participant
        // list used to resolve to: `create` only ever wrote the one owner
        // row. Widens to a real grant lookup when sessions grow shared
        // access.
        let viewers = sqlx::query_scalar!(
            r#"
            SELECT owner_id AS "owner_id: MacroUserIdStr"
            FROM agent_session
            WHERE id = $1
            "#,
            agent_session_id.as_uuid(),
        )
        .fetch_all(&self.pool)
        .await
        .map_err(|error| rootcause::report!(error))?;

        Ok(viewers)
    }
}

mod list_visibility;
mod warm;