crates/hub/src/db/proposals.rs at v0.9.21 · andymccall/oxygenhub

◆ v0.9.21 oxygenhub / crates / hub / src / db / proposals.rs

14.5 KiB · Rust · last changed A push to a landed proposal's bookmark says so; its page lists the changes since landing (closes #329); pin OxygenFlow main (oxygen's push_notes) 9 days ago source blame history raw

//! The `proposals`, `proposal_comments`, `proposal_reviews` and
//! `proposal_events` tables, and the per-repository number space.

use super::{now_ms, Pool};
use sqlx::FromRow;

#[derive(Clone, Debug, Default, FromRow)]
pub struct ProposalRow {
    pub id: i64,
    pub repo_id: i64,
    pub number: i64,
    pub title: String,
    pub body: String,
    pub author_id: Option<i64>,
    pub author: String,
    pub state: String,
    pub target: String,
    pub source_kind: String,
    pub source_repo_id: Option<i64>,
    pub source_bookmark: String,
    pub source_changes: String,
    pub projected_state: String,
    pub projected_heads: String,
    pub projected_target: String,
    pub projected_changes: String,
    pub conflicted: bool,
    pub landed_state: String,
    pub landed_by: String,
    pub landed_via: String,
    pub landed_ms: i64,
    pub created_ms: i64,
    pub updated_ms: i64,
    /// Who set it to land when its checks pass (#261); empty when off.
    #[sqlx(default)]
    pub auto_land_by: String,
    #[sqlx(default)]
    pub auto_land_ms: Option<i64>,
}

impl ProposalRow {
    pub fn projected_heads(&self) -> Vec<String> {
        self.projected_heads.split(',').filter(|s| !s.is_empty()).map(String::from).collect()
    }

    pub fn projected_changes(&self) -> Vec<String> {
        self.projected_changes.split(',').filter(|s| !s.is_empty()).map(String::from).collect()
    }

    pub fn source_changes(&self) -> Vec<String> {
        self.source_changes.split(',').filter(|s| !s.is_empty()).map(String::from).collect()
    }

    pub fn is_open(&self) -> bool {
        self.state == "open" || self.state == "draft"
    }
}

#[derive(Clone, Debug, FromRow)]
pub struct CommentRow {
    pub id: i64,
    pub proposal_id: i64,
    pub author_id: Option<i64>,
    pub author: String,
    pub body: String,
    pub path: String,
    pub anchor_change: String,
    pub anchor_index: i64,
    pub resolved: bool,
    pub created_ms: i64,
    /// The AI assistant that wrote it for the author (migration 0066),
    /// empty for the author's own comment.
    #[sqlx(default)]
    pub via: String,
}

#[derive(Clone, Debug, FromRow)]
pub struct ReviewRow {
    pub id: i64,
    pub proposal_id: i64,
    /// `None` once the author's account was deleted (its name stays).
    pub author_id: Option<i64>,
    pub author: String,
    pub verdict: String,
    pub body: String,
    pub created_ms: i64,
    /// The AI assistant that answered for the author (migration 0036),
    /// empty for the author's own review.
    #[sqlx(default)]
    pub via: String,
}

#[derive(Clone, Debug, FromRow)]
pub struct EventRow {
    pub id: i64,
    pub at_ms: i64,
    pub actor: String,
    pub event: String,
    pub detail: String,
}

/// Take the next number of a repository's shared number space.
pub async fn next_number(pool: &Pool, repo_id: i64) -> sqlx::Result<i64> {
    let mut tx = super::begin_write(pool).await?;
    sqlx::query("INSERT INTO numbers (repo_id, next) VALUES ($1, 1) ON CONFLICT DO NOTHING").bind(repo_id).execute(&mut *tx).await?;
    let n: i64 = sqlx::query_scalar("SELECT next FROM numbers WHERE repo_id = $1").bind(repo_id).fetch_one(&mut *tx).await?;
    sqlx::query("UPDATE numbers SET next = next + 1 WHERE repo_id = $1").bind(repo_id).execute(&mut *tx).await?;
    tx.commit().await?;
    Ok(n)
}

pub struct NewProposal<'a> {
    pub repo_id: i64,
    pub title: &'a str,
    pub body: &'a str,
    pub author_id: Option<i64>,
    pub author: &'a str,
    pub draft: bool,
    pub target: &'a str,
    pub source_kind: &'a str,
    pub source_repo_id: Option<i64>,
    pub source_bookmark: &'a str,
    pub source_changes: &'a str,
}

pub async fn create(pool: &Pool, p: NewProposal<'_>) -> sqlx::Result<ProposalRow> {
    let number = next_number(pool, p.repo_id).await?;
    let now = now_ms();
    sqlx::query_as(
        "INSERT INTO proposals (repo_id, number, title, body, author_id, author, state, target, source_kind, source_repo_id, source_bookmark, source_changes, created_ms, updated_ms) \
         VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14) RETURNING *",
    )
    .bind(p.repo_id)
    .bind(number)
    .bind(p.title)
    .bind(p.body)
    .bind(p.author_id)
    .bind(p.author)
    .bind(if p.draft { "draft" } else { "open" })
    .bind(p.target)
    .bind(p.source_kind)
    .bind(p.source_repo_id)
    .bind(p.source_bookmark)
    .bind(p.source_changes)
    .bind(now)
    .bind(now)
    .fetch_one(pool)
    .await
}

pub async fn get(pool: &Pool, repo_id: i64, number: i64) -> sqlx::Result<Option<ProposalRow>> {
    sqlx::query_as("SELECT * FROM proposals WHERE repo_id = $1 AND number = $2").bind(repo_id).bind(number).fetch_optional(pool).await
}

pub async fn list(pool: &Pool, repo_id: i64, states: &[&str]) -> sqlx::Result<Vec<ProposalRow>> {
    let all: Vec<ProposalRow> =
        sqlx::query_as("SELECT * FROM proposals WHERE repo_id = $1 ORDER BY number DESC").bind(repo_id).fetch_all(pool).await?;
    Ok(all.into_iter().filter(|p| states.is_empty() || states.contains(&p.state.as_str())).collect())
}

/// Open proposals of every repository, for the front page.
pub async fn open_for_author(pool: &Pool, author_id: i64) -> sqlx::Result<Vec<ProposalRow>> {
    sqlx::query_as("SELECT * FROM proposals WHERE author_id = $1 AND state IN ('open', 'draft') ORDER BY updated_ms DESC")
        .bind(author_id)
        .fetch_all(pool)
        .await
}

pub async fn set_projection(
    pool: &Pool,
    id: i64,
    state: &str,
    heads: &str,
    target_state: &str,
    changes: &str,
    conflicted: bool,
) -> sqlx::Result<()> {
    sqlx::query("UPDATE proposals SET projected_state = $1, projected_heads = $2, projected_target = $3, projected_changes = $4, conflicted = $5 WHERE id = $6")
        .bind(state)
        .bind(heads)
        .bind(target_state)
        .bind(changes)
        .bind(conflicted)
        .bind(id)
        .execute(pool)
        .await?;
    Ok(())
}

pub async fn set_state(pool: &Pool, id: i64, state: &str, landed_state: &str) -> sqlx::Result<()> {
    sqlx::query("UPDATE proposals SET state = $1, landed_state = $2, updated_ms = $3 WHERE id = $4")
        .bind(state)
        .bind(landed_state)
        .bind(now_ms())
        .bind(id)
        .execute(pool)
        .await?;
    if state == "closed" || state == "landed" {
        super::issues::finished(pool, "proposals", id).await?;
    }
    Ok(())
}

/// Mark a proposal landed, with who landed it and how.
pub async fn set_landed(pool: &Pool, id: i64, landed_state: &str, by: &str, via: &str) -> sqlx::Result<()> {
    let now = now_ms();
    sqlx::query(
        "UPDATE proposals SET state = 'landed', landed_state = $1, landed_by = $2, landed_via = $3, landed_ms = $4, updated_ms = $5 WHERE id = $6",
    )
    .bind(landed_state)
    .bind(by)
    .bind(via)
    .bind(now)
    .bind(now)
    .bind(id)
    .execute(pool)
    .await?;
    super::issues::finished(pool, "proposals", id).await
}

pub async fn edit(pool: &Pool, id: i64, title: &str, body: &str, target: &str) -> sqlx::Result<()> {
    sqlx::query("UPDATE proposals SET title = $1, body = $2, target = $3, updated_ms = $4 WHERE id = $5")
        .bind(title)
        .bind(body)
        .bind(target)
        .bind(now_ms())
        .bind(id)
        .execute(pool)
        .await?;
    Ok(())
}

pub async fn touch(pool: &Pool, id: i64) -> sqlx::Result<()> {
    sqlx::query("UPDATE proposals SET updated_ms = $1 WHERE id = $2").bind(now_ms()).bind(id).execute(pool).await?;
    Ok(())
}

pub async fn comments(pool: &Pool, proposal_id: i64) -> sqlx::Result<Vec<CommentRow>> {
    sqlx::query_as("SELECT * FROM proposal_comments WHERE proposal_id = $1 ORDER BY created_ms, id").bind(proposal_id).fetch_all(pool).await
}

pub async fn add_comment(
    pool: &Pool,
    proposal_id: i64,
    author_id: Option<i64>,
    author: &str,
    body: &str,
    path: &str,
    anchor: Option<(&str, i64)>,
) -> sqlx::Result<CommentRow> {
    let row = sqlx::query_as(
        "INSERT INTO proposal_comments (proposal_id, author_id, author, body, path, anchor_change, anchor_index, created_ms) VALUES ($1, $2, $3, $4, $5, $6, $7, $8) RETURNING *",
    )
    .bind(proposal_id)
    .bind(author_id)
    .bind(author)
    .bind(body)
    .bind(path)
    .bind(anchor.map(|a| a.0).unwrap_or(""))
    .bind(anchor.map(|a| a.1).unwrap_or(0))
    .bind(now_ms())
    .fetch_one(pool)
    .await?;
    touch(pool, proposal_id).await?;
    Ok(row)
}

/// A conversation comment an AI assistant (`via`) wrote for `author`,
/// who asked it.
pub async fn add_comment_via(
    pool: &Pool,
    proposal_id: i64,
    author_id: i64,
    author: &str,
    body: &str,
    via: &str,
) -> sqlx::Result<CommentRow> {
    let row = sqlx::query_as(
        "INSERT INTO proposal_comments (proposal_id, author_id, author, body, path, anchor_change, anchor_index, created_ms, via) VALUES ($1, $2, $3, $4, '', '', 0, $5, $6) RETURNING *",
    )
    .bind(proposal_id)
    .bind(author_id)
    .bind(author)
    .bind(body)
    .bind(now_ms())
    .bind(via)
    .fetch_one(pool)
    .await?;
    touch(pool, proposal_id).await?;
    Ok(row)
}

pub async fn set_comment_resolved(pool: &Pool, id: i64, resolved: bool) -> sqlx::Result<()> {
    sqlx::query("UPDATE proposal_comments SET resolved = $1 WHERE id = $2").bind(resolved).bind(id).execute(pool).await?;
    Ok(())
}

pub async fn reviews(pool: &Pool, proposal_id: i64) -> sqlx::Result<Vec<ReviewRow>> {
    sqlx::query_as("SELECT * FROM proposal_reviews WHERE proposal_id = $1 ORDER BY created_ms, id").bind(proposal_id).fetch_all(pool).await
}

pub async fn add_review(pool: &Pool, proposal_id: i64, author_id: i64, author: &str, verdict: &str, body: &str) -> sqlx::Result<ReviewRow> {
    add_review_via(pool, proposal_id, author_id, author, verdict, body, "").await
}

/// A review answered by an AI assistant (`via`) for `author`, who asked.
pub async fn add_review_via(
    pool: &Pool,
    proposal_id: i64,
    author_id: i64,
    author: &str,
    verdict: &str,
    body: &str,
    via: &str,
) -> sqlx::Result<ReviewRow> {
    let row = sqlx::query_as(
        "INSERT INTO proposal_reviews (proposal_id, author_id, author, verdict, body, created_ms, via) VALUES ($1, $2, $3, $4, $5, $6, $7) RETURNING *",
    )
    .bind(proposal_id)
    .bind(author_id)
    .bind(author)
    .bind(verdict)
    .bind(body)
    .bind(now_ms())
    .bind(via)
    .fetch_one(pool)
    .await?;
    touch(pool, proposal_id).await?;
    Ok(row)
}

pub async fn events(pool: &Pool, proposal_id: i64) -> sqlx::Result<Vec<EventRow>> {
    sqlx::query_as("SELECT id, at_ms, actor, event, detail FROM proposal_events WHERE proposal_id = $1 ORDER BY at_ms, id")
        .bind(proposal_id)
        .fetch_all(pool)
        .await
}

/// Whether change `hash` (its short form) already references it (#318):
/// a change is recorded once, whichever bookmarks it is pushed on.
pub async fn referenced_by(pool: &Pool, proposal_id: i64, hash: &str) -> sqlx::Result<bool> {
    let n: i64 = sqlx::query_scalar(
        "SELECT COUNT(*) FROM proposal_events WHERE proposal_id = $1 AND event = 'referenced by change' AND (detail = $2 OR detail LIKE $3)",
    )
    .bind(proposal_id)
    .bind(hash)
    .bind(format!("{hash} %"))
    .fetch_one(pool)
    .await?;
    Ok(n > 0)
}

pub async fn add_event(pool: &Pool, proposal_id: i64, actor: &str, event: &str, detail: &str) -> sqlx::Result<()> {
    sqlx::query("INSERT INTO proposal_events (proposal_id, at_ms, actor, event, detail) VALUES ($1, $2, $3, $4, $5)")
        .bind(proposal_id)
        .bind(now_ms())
        .bind(actor)
        .bind(event)
        .bind(detail)
        .execute(pool)
        .await?;
    Ok(())
}

/// Landing requirements of a repository.
#[derive(Clone, Copy, Debug, Default, FromRow)]
pub struct Requirements {
    pub required_approvals: i64,
    pub require_owners: bool,
    pub allow_conflicts: bool,
}

pub async fn requirements(pool: &Pool, repo_id: i64) -> sqlx::Result<Requirements> {
    sqlx::query_as("SELECT required_approvals, require_owners, allow_conflicts FROM repositories WHERE id = $1")
        .bind(repo_id)
        .fetch_one(pool)
        .await
}

pub async fn set_requirements(pool: &Pool, repo_id: i64, r: Requirements) -> sqlx::Result<()> {
    sqlx::query("UPDATE repositories SET required_approvals = $1, require_owners = $2, allow_conflicts = $3 WHERE id = $4")
        .bind(r.required_approvals)
        .bind(r.require_owners)
        .bind(r.allow_conflicts)
        .bind(repo_id)
        .execute(pool)
        .await?;
    Ok(())
}

// ------------------------------------------------------- review requests

/// Ask an account to review a proposal; false when it was already asked.
pub async fn request_review(pool: &Pool, proposal_id: i64, account_id: i64, by: &str) -> sqlx::Result<bool> {
    let r = sqlx::query(
        "INSERT INTO proposal_review_requests (proposal_id, account_id, requested_by, requested_ms) VALUES ($1, $2, $3, $4) ON CONFLICT DO NOTHING",
    )
    .bind(proposal_id)
    .bind(account_id)
    .bind(by)
    .bind(now_ms())
    .execute(pool)
    .await?;
    Ok(r.rows_affected() == 1)
}

/// Withdraw a request, or mark it answered; false when there was none.
pub async fn unrequest_review(pool: &Pool, proposal_id: i64, account_id: i64) -> sqlx::Result<bool> {
    let r = sqlx::query("DELETE FROM proposal_review_requests WHERE proposal_id = $1 AND account_id = $2")
        .bind(proposal_id)
        .bind(account_id)
        .execute(pool)
        .await?;
    Ok(r.rows_affected() == 1)
}

/// The usernames asked to review a proposal and not yet reviewing, in the
/// order they were asked.
pub async fn review_requests(pool: &Pool, proposal_id: i64) -> sqlx::Result<Vec<String>> {
    sqlx::query_scalar(
        "SELECT a.username FROM proposal_review_requests r JOIN accounts a ON a.id = r.account_id \
         WHERE r.proposal_id = $1 ORDER BY r.requested_ms, a.username",
    )
    .bind(proposal_id)
    .fetch_all(pool)
    .await
}

/// Set, or with `by` empty clear, landing when checks pass (#261).
pub async fn set_auto_land(pool: &Pool, id: i64, by: &str) -> sqlx::Result<()> {
    sqlx::query("UPDATE proposals SET auto_land_by = $1, auto_land_ms = $2 WHERE id = $3")
        .bind(by)
        .bind((!by.is_empty()).then(now_ms))
        .bind(id)
        .execute(pool)
        .await?;
    Ok(())
}

/// A repository's open proposals set to land when their checks pass, on
/// projected state `state`.
pub async fn auto_landing(pool: &Pool, repo_id: i64, state: &str) -> sqlx::Result<Vec<ProposalRow>> {
    sqlx::query_as(
        "SELECT * FROM proposals WHERE repo_id = $1 AND state = 'open' AND auto_land_by != '' AND projected_state = $2 ORDER BY number",
    )
    .bind(repo_id)
    .bind(state)
    .fetch_all(pool)
    .await
}

`/`	Search
`g` `c`	Code
`g` `h`	History
`g` `b`	Bookmarks
`g` `t`	Trust
`y`	Permalink to this state (files and trees)
`?`	This sheet

de505df5 Andy McCall 1 month ago ↶	1	//! The `proposals`, `proposal_comments`, `proposal_reviews` and
	2	//! `proposal_events` tables, and the per-repository number space.
	3
	4	use super::{now_ms, Pool};
	5	use sqlx::FromRow;
	6
668b2ad7 Andy McCall 9 days ago ↶	7	#[derive(Clone, Debug, Default, FromRow)]
de505df5 Andy McCall 1 month ago ↶	8	pub struct ProposalRow {
	9	pub id: i64,
	10	pub repo_id: i64,
	11	pub number: i64,
	12	pub title: String,
	13	pub body: String,
	14	pub author_id: Option<i64>,
	15	pub author: String,
	16	pub state: String,
	17	pub target: String,
	18	pub source_kind: String,
	19	pub source_repo_id: Option<i64>,
	20	pub source_bookmark: String,
	21	pub source_changes: String,
	22	pub projected_state: String,
	23	pub projected_heads: String,
	24	pub projected_target: String,
	25	pub projected_changes: String,
	26	pub conflicted: bool,
	27	pub landed_state: String,
0b20f1ec Andy McCall 1 month ago ↶	28	pub landed_by: String,
	29	pub landed_via: String,
	30	pub landed_ms: i64,
de505df5 Andy McCall 1 month ago ↶	31	pub created_ms: i64,
de505df5 Andy McCall 1 month ago ↶	32	pub updated_ms: i64,
86a69b53 Andy McCall 17 days ago ↶	33	/// Who set it to land when its checks pass (#261); empty when off.
	34	#[sqlx(default)]
	35	pub auto_land_by: String,
	36	#[sqlx(default)]
	37	pub auto_land_ms: Option<i64>,
de505df5 Andy McCall 1 month ago ↶	38	}
	39
	40	impl ProposalRow {
	41	pub fn projected_heads(&self) -> Vec<String> {
	42	self.projected_heads.split(',').filter(\|s\| !s.is_empty()).map(String::from).collect()
	43	}
	44
	45	pub fn projected_changes(&self) -> Vec<String> {
	46	self.projected_changes.split(',').filter(\|s\| !s.is_empty()).map(String::from).collect()
	47	}
	48
	49	pub fn source_changes(&self) -> Vec<String> {
	50	self.source_changes.split(',').filter(\|s\| !s.is_empty()).map(String::from).collect()
	51	}
	52
	53	pub fn is_open(&self) -> bool {
	54	self.state == "open" \|\| self.state == "draft"
	55	}
	56	}
	57
	58	#[derive(Clone, Debug, FromRow)]
	59	pub struct CommentRow {
	60	pub id: i64,
	61	pub proposal_id: i64,
	62	pub author_id: Option<i64>,
	63	pub author: String,
	64	pub body: String,
	65	pub path: String,
	66	pub anchor_change: String,
	67	pub anchor_index: i64,
	68	pub resolved: bool,
	69	pub created_ms: i64,
ffb67a81 Andy McCall 21 days ago	70	/// The AI assistant that wrote it for the author (migration 0066),
	71	/// empty for the author's own comment.
	72	#[sqlx(default)]
	73	pub via: String,
de505df5 Andy McCall 1 month ago ↶	74	}
	75
	76	#[derive(Clone, Debug, FromRow)]
	77	pub struct ReviewRow {
	78	pub id: i64,
	79	pub proposal_id: i64,
6a178055 Andy McCall 1 month ago ↶	80	/// `None` once the author's account was deleted (its name stays).
6a178055 Andy McCall 1 month ago ↶	81	pub author_id: Option<i64>,
de505df5 Andy McCall 1 month ago ↶	82	pub author: String,
	83	pub verdict: String,
	84	pub body: String,
	85	pub created_ms: i64,
7d2eb590 Andy McCall 1 month ago ↶	86	/// The AI assistant that answered for the author (migration 0036),
	87	/// empty for the author's own review.
	88	#[sqlx(default)]
	89	pub via: String,
de505df5 Andy McCall 1 month ago ↶	90	}
	91
	92	#[derive(Clone, Debug, FromRow)]
	93	pub struct EventRow {
	94	pub id: i64,
	95	pub at_ms: i64,
	96	pub actor: String,
	97	pub event: String,
	98	pub detail: String,
	99	}
	100
	101	/// Take the next number of a repository's shared number space.
	102	pub async fn next_number(pool: &Pool, repo_id: i64) -> sqlx::Result<i64> {
941abdd6 Andy McCall 1 month ago ↶	103	let mut tx = super::begin_write(pool).await?;
d1bf2d69 Andy McCall 20 days ago ↶	104	sqlx::query("INSERT INTO numbers (repo_id, next) VALUES ($1, 1) ON CONFLICT DO NOTHING").bind(repo_id).execute(&mut *tx).await?;
5d90e0e0 Andy McCall 20 days ago	105	let n: i64 = sqlx::query_scalar("SELECT next FROM numbers WHERE repo_id = $1").bind(repo_id).fetch_one(&mut *tx).await?;
5d90e0e0 Andy McCall 20 days ago	106	sqlx::query("UPDATE numbers SET next = next + 1 WHERE repo_id = $1").bind(repo_id).execute(&mut *tx).await?;
de505df5 Andy McCall 1 month ago ↶	107	tx.commit().await?;
	108	Ok(n)
	109	}
	110
	111	pub struct NewProposal<'a> {
	112	pub repo_id: i64,
	113	pub title: &'a str,
	114	pub body: &'a str,
	115	pub author_id: Option<i64>,
	116	pub author: &'a str,
	117	pub draft: bool,
	118	pub target: &'a str,
	119	pub source_kind: &'a str,
	120	pub source_repo_id: Option<i64>,
	121	pub source_bookmark: &'a str,
	122	pub source_changes: &'a str,
	123	}
	124
	125	pub async fn create(pool: &Pool, p: NewProposal<'_>) -> sqlx::Result<ProposalRow> {
	126	let number = next_number(pool, p.repo_id).await?;
	127	let now = now_ms();
	128	sqlx::query_as(
	129	"INSERT INTO proposals (repo_id, number, title, body, author_id, author, state, target, source_kind, source_repo_id, source_bookmark, source_changes, created_ms, updated_ms) \
5d90e0e0 Andy McCall 20 days ago	130	VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14) RETURNING *",
de505df5 Andy McCall 1 month ago ↶	131	)
	132	.bind(p.repo_id)
	133	.bind(number)
	134	.bind(p.title)
	135	.bind(p.body)
	136	.bind(p.author_id)
	137	.bind(p.author)
	138	.bind(if p.draft { "draft" } else { "open" })
	139	.bind(p.target)
	140	.bind(p.source_kind)
	141	.bind(p.source_repo_id)
	142	.bind(p.source_bookmark)
	143	.bind(p.source_changes)
	144	.bind(now)
	145	.bind(now)
	146	.fetch_one(pool)
	147	.await
	148	}
	149
	150	pub async fn get(pool: &Pool, repo_id: i64, number: i64) -> sqlx::Result<Option<ProposalRow>> {
5d90e0e0 Andy McCall 20 days ago	151	sqlx::query_as("SELECT * FROM proposals WHERE repo_id = $1 AND number = $2").bind(repo_id).bind(number).fetch_optional(pool).await
de505df5 Andy McCall 1 month ago ↶	152	}
	153
	154	pub async fn list(pool: &Pool, repo_id: i64, states: &[&str]) -> sqlx::Result<Vec<ProposalRow>> {
	155	let all: Vec<ProposalRow> =
5d90e0e0 Andy McCall 20 days ago	156	sqlx::query_as("SELECT * FROM proposals WHERE repo_id = $1 ORDER BY number DESC").bind(repo_id).fetch_all(pool).await?;
de505df5 Andy McCall 1 month ago ↶	157	Ok(all.into_iter().filter(\|p\| states.is_empty() \|\| states.contains(&p.state.as_str())).collect())
	158	}
	159
	160	/// Open proposals of every repository, for the front page.
	161	pub async fn open_for_author(pool: &Pool, author_id: i64) -> sqlx::Result<Vec<ProposalRow>> {
5d90e0e0 Andy McCall 20 days ago	162	sqlx::query_as("SELECT * FROM proposals WHERE author_id = $1 AND state IN ('open', 'draft') ORDER BY updated_ms DESC")
de505df5 Andy McCall 1 month ago ↶	163	.bind(author_id)
	164	.fetch_all(pool)
	165	.await
	166	}
	167
	168	pub async fn set_projection(
	169	pool: &Pool,
	170	id: i64,
	171	state: &str,
	172	heads: &str,
	173	target_state: &str,
	174	changes: &str,
	175	conflicted: bool,
	176	) -> sqlx::Result<()> {
5d90e0e0 Andy McCall 20 days ago	177	sqlx::query("UPDATE proposals SET projected_state = $1, projected_heads = $2, projected_target = $3, projected_changes = $4, conflicted = $5 WHERE id = $6")
de505df5 Andy McCall 1 month ago ↶	178	.bind(state)
	179	.bind(heads)
	180	.bind(target_state)
	181	.bind(changes)
	182	.bind(conflicted)
	183	.bind(id)
	184	.execute(pool)
	185	.await?;
	186	Ok(())
	187	}
	188
	189	pub async fn set_state(pool: &Pool, id: i64, state: &str, landed_state: &str) -> sqlx::Result<()> {
5d90e0e0 Andy McCall 20 days ago	190	sqlx::query("UPDATE proposals SET state = $1, landed_state = $2, updated_ms = $3 WHERE id = $4")
de505df5 Andy McCall 1 month ago ↶	191	.bind(state)
	192	.bind(landed_state)
	193	.bind(now_ms())
	194	.bind(id)
	195	.execute(pool)
	196	.await?;
1000b40a Andy McCall 10 days ago ↶	197	if state == "closed" \|\| state == "landed" {
	198	super::issues::finished(pool, "proposals", id).await?;
	199	}
0b20f1ec Andy McCall 1 month ago ↶	200	Ok(())
	201	}
	202
	203	/// Mark a proposal landed, with who landed it and how.
	204	pub async fn set_landed(pool: &Pool, id: i64, landed_state: &str, by: &str, via: &str) -> sqlx::Result<()> {
	205	let now = now_ms();
	206	sqlx::query(
5d90e0e0 Andy McCall 20 days ago	207	"UPDATE proposals SET state = 'landed', landed_state = $1, landed_by = $2, landed_via = $3, landed_ms = $4, updated_ms = $5 WHERE id = $6",
0b20f1ec Andy McCall 1 month ago ↶	208	)
	209	.bind(landed_state)
	210	.bind(by)
	211	.bind(via)
	212	.bind(now)
	213	.bind(now)
	214	.bind(id)
	215	.execute(pool)
	216	.await?;
1000b40a Andy McCall 10 days ago ↶	217	super::issues::finished(pool, "proposals", id).await
de505df5 Andy McCall 1 month ago ↶	218	}
	219
	220	pub async fn edit(pool: &Pool, id: i64, title: &str, body: &str, target: &str) -> sqlx::Result<()> {
5d90e0e0 Andy McCall 20 days ago	221	sqlx::query("UPDATE proposals SET title = $1, body = $2, target = $3, updated_ms = $4 WHERE id = $5")
de505df5 Andy McCall 1 month ago ↶	222	.bind(title)
	223	.bind(body)
	224	.bind(target)
	225	.bind(now_ms())
	226	.bind(id)
	227	.execute(pool)
	228	.await?;
	229	Ok(())
	230	}
	231
	232	pub async fn touch(pool: &Pool, id: i64) -> sqlx::Result<()> {
5d90e0e0 Andy McCall 20 days ago	233	sqlx::query("UPDATE proposals SET updated_ms = $1 WHERE id = $2").bind(now_ms()).bind(id).execute(pool).await?;
de505df5 Andy McCall 1 month ago ↶	234	Ok(())
	235	}
	236
	237	pub async fn comments(pool: &Pool, proposal_id: i64) -> sqlx::Result<Vec<CommentRow>> {
5d90e0e0 Andy McCall 20 days ago	238	sqlx::query_as("SELECT * FROM proposal_comments WHERE proposal_id = $1 ORDER BY created_ms, id").bind(proposal_id).fetch_all(pool).await
de505df5 Andy McCall 1 month ago ↶	239	}
	240
	241	pub async fn add_comment(
	242	pool: &Pool,
	243	proposal_id: i64,
	244	author_id: Option<i64>,
	245	author: &str,
	246	body: &str,
	247	path: &str,
	248	anchor: Option<(&str, i64)>,
	249	) -> sqlx::Result<CommentRow> {
	250	let row = sqlx::query_as(
5d90e0e0 Andy McCall 20 days ago	251	"INSERT INTO proposal_comments (proposal_id, author_id, author, body, path, anchor_change, anchor_index, created_ms) VALUES ($1, $2, $3, $4, $5, $6, $7, $8) RETURNING *",
de505df5 Andy McCall 1 month ago ↶	252	)
	253	.bind(proposal_id)
	254	.bind(author_id)
	255	.bind(author)
	256	.bind(body)
	257	.bind(path)
	258	.bind(anchor.map(\|a\| a.0).unwrap_or(""))
	259	.bind(anchor.map(\|a\| a.1).unwrap_or(0))
	260	.bind(now_ms())
ffb67a81 Andy McCall 21 days ago	261	.fetch_one(pool)
	262	.await?;
	263	touch(pool, proposal_id).await?;
	264	Ok(row)
	265	}
	266
	267	/// A conversation comment an AI assistant (`via`) wrote for `author`,
	268	/// who asked it.
	269	pub async fn add_comment_via(
	270	pool: &Pool,
	271	proposal_id: i64,
	272	author_id: i64,
	273	author: &str,
	274	body: &str,
	275	via: &str,
	276	) -> sqlx::Result<CommentRow> {
	277	let row = sqlx::query_as(
5d90e0e0 Andy McCall 20 days ago	278	"INSERT INTO proposal_comments (proposal_id, author_id, author, body, path, anchor_change, anchor_index, created_ms, via) VALUES ($1, $2, $3, $4, '', '', 0, $5, $6) RETURNING *",
ffb67a81 Andy McCall 21 days ago	279	)
	280	.bind(proposal_id)
	281	.bind(author_id)
	282	.bind(author)
	283	.bind(body)
	284	.bind(now_ms())
	285	.bind(via)
de505df5 Andy McCall 1 month ago ↶	286	.fetch_one(pool)
	287	.await?;
	288	touch(pool, proposal_id).await?;
	289	Ok(row)
	290	}
	291
	292	pub async fn set_comment_resolved(pool: &Pool, id: i64, resolved: bool) -> sqlx::Result<()> {
5d90e0e0 Andy McCall 20 days ago	293	sqlx::query("UPDATE proposal_comments SET resolved = $1 WHERE id = $2").bind(resolved).bind(id).execute(pool).await?;
de505df5 Andy McCall 1 month ago ↶	294	Ok(())
	295	}
	296
	297	pub async fn reviews(pool: &Pool, proposal_id: i64) -> sqlx::Result<Vec<ReviewRow>> {
5d90e0e0 Andy McCall 20 days ago	298	sqlx::query_as("SELECT * FROM proposal_reviews WHERE proposal_id = $1 ORDER BY created_ms, id").bind(proposal_id).fetch_all(pool).await
de505df5 Andy McCall 1 month ago ↶	299	}
	300
	301	pub async fn add_review(pool: &Pool, proposal_id: i64, author_id: i64, author: &str, verdict: &str, body: &str) -> sqlx::Result<ReviewRow> {
7d2eb590 Andy McCall 1 month ago ↶	302	add_review_via(pool, proposal_id, author_id, author, verdict, body, "").await
	303	}
	304
	305	/// A review answered by an AI assistant (`via`) for `author`, who asked.
	306	pub async fn add_review_via(
	307	pool: &Pool,
	308	proposal_id: i64,
	309	author_id: i64,
	310	author: &str,
	311	verdict: &str,
	312	body: &str,
	313	via: &str,
	314	) -> sqlx::Result<ReviewRow> {
de505df5 Andy McCall 1 month ago ↶	315	let row = sqlx::query_as(
5d90e0e0 Andy McCall 20 days ago	316	"INSERT INTO proposal_reviews (proposal_id, author_id, author, verdict, body, created_ms, via) VALUES ($1, $2, $3, $4, $5, $6, $7) RETURNING *",
de505df5 Andy McCall 1 month ago ↶	317	)
	318	.bind(proposal_id)
	319	.bind(author_id)
	320	.bind(author)
	321	.bind(verdict)
	322	.bind(body)
	323	.bind(now_ms())
7d2eb590 Andy McCall 1 month ago ↶	324	.bind(via)
de505df5 Andy McCall 1 month ago ↶	325	.fetch_one(pool)
	326	.await?;
	327	touch(pool, proposal_id).await?;
	328	Ok(row)
	329	}
	330
	331	pub async fn events(pool: &Pool, proposal_id: i64) -> sqlx::Result<Vec<EventRow>> {
5d90e0e0 Andy McCall 20 days ago	332	sqlx::query_as("SELECT id, at_ms, actor, event, detail FROM proposal_events WHERE proposal_id = $1 ORDER BY at_ms, id")
de505df5 Andy McCall 1 month ago ↶	333	.bind(proposal_id)
	334	.fetch_all(pool)
	335	.await
	336	}
	337
8bb7baeb Andy McCall 10 days ago ↶	338	/// Whether change `hash` (its short form) already references it (#318):
	339	/// a change is recorded once, whichever bookmarks it is pushed on.
	340	pub async fn referenced_by(pool: &Pool, proposal_id: i64, hash: &str) -> sqlx::Result<bool> {
	341	let n: i64 = sqlx::query_scalar(
	342	"SELECT COUNT(*) FROM proposal_events WHERE proposal_id = $1 AND event = 'referenced by change' AND (detail = $2 OR detail LIKE $3)",
	343	)
	344	.bind(proposal_id)
	345	.bind(hash)
	346	.bind(format!("{hash} %"))
	347	.fetch_one(pool)
	348	.await?;
	349	Ok(n > 0)
	350	}
	351
de505df5 Andy McCall 1 month ago ↶	352	pub async fn add_event(pool: &Pool, proposal_id: i64, actor: &str, event: &str, detail: &str) -> sqlx::Result<()> {
5d90e0e0 Andy McCall 20 days ago	353	sqlx::query("INSERT INTO proposal_events (proposal_id, at_ms, actor, event, detail) VALUES ($1, $2, $3, $4, $5)")
de505df5 Andy McCall 1 month ago ↶	354	.bind(proposal_id)
	355	.bind(now_ms())
	356	.bind(actor)
	357	.bind(event)
	358	.bind(detail)
	359	.execute(pool)
	360	.await?;
	361	Ok(())
	362	}
	363
	364	/// Landing requirements of a repository.
	365	#[derive(Clone, Copy, Debug, Default, FromRow)]
	366	pub struct Requirements {
	367	pub required_approvals: i64,
	368	pub require_owners: bool,
	369	pub allow_conflicts: bool,
	370	}
	371
	372	pub async fn requirements(pool: &Pool, repo_id: i64) -> sqlx::Result<Requirements> {
5d90e0e0 Andy McCall 20 days ago	373	sqlx::query_as("SELECT required_approvals, require_owners, allow_conflicts FROM repositories WHERE id = $1")
de505df5 Andy McCall 1 month ago ↶	374	.bind(repo_id)
	375	.fetch_one(pool)
	376	.await
	377	}
	378
	379	pub async fn set_requirements(pool: &Pool, repo_id: i64, r: Requirements) -> sqlx::Result<()> {
5d90e0e0 Andy McCall 20 days ago	380	sqlx::query("UPDATE repositories SET required_approvals = $1, require_owners = $2, allow_conflicts = $3 WHERE id = $4")
de505df5 Andy McCall 1 month ago ↶	381	.bind(r.required_approvals)
	382	.bind(r.require_owners)
	383	.bind(r.allow_conflicts)
	384	.bind(repo_id)
	385	.execute(pool)
	386	.await?;
	387	Ok(())
	388	}
cc1cd197 Andy McCall 1 month ago ↶	389
	390	// ------------------------------------------------------- review requests
	391
	392	/// Ask an account to review a proposal; false when it was already asked.
	393	pub async fn request_review(pool: &Pool, proposal_id: i64, account_id: i64, by: &str) -> sqlx::Result<bool> {
	394	let r = sqlx::query(
d1bf2d69 Andy McCall 20 days ago ↶	395	"INSERT INTO proposal_review_requests (proposal_id, account_id, requested_by, requested_ms) VALUES ($1, $2, $3, $4) ON CONFLICT DO NOTHING",
cc1cd197 Andy McCall 1 month ago ↶	396	)
	397	.bind(proposal_id)
	398	.bind(account_id)
	399	.bind(by)
	400	.bind(now_ms())
	401	.execute(pool)
	402	.await?;
	403	Ok(r.rows_affected() == 1)
	404	}
	405
	406	/// Withdraw a request, or mark it answered; false when there was none.
	407	pub async fn unrequest_review(pool: &Pool, proposal_id: i64, account_id: i64) -> sqlx::Result<bool> {
5d90e0e0 Andy McCall 20 days ago	408	let r = sqlx::query("DELETE FROM proposal_review_requests WHERE proposal_id = $1 AND account_id = $2")
cc1cd197 Andy McCall 1 month ago ↶	409	.bind(proposal_id)
	410	.bind(account_id)
	411	.execute(pool)
	412	.await?;
	413	Ok(r.rows_affected() == 1)
	414	}
	415
	416	/// The usernames asked to review a proposal and not yet reviewing, in the
	417	/// order they were asked.
	418	pub async fn review_requests(pool: &Pool, proposal_id: i64) -> sqlx::Result<Vec<String>> {
	419	sqlx::query_scalar(
	420	"SELECT a.username FROM proposal_review_requests r JOIN accounts a ON a.id = r.account_id \
5d90e0e0 Andy McCall 20 days ago	421	WHERE r.proposal_id = $1 ORDER BY r.requested_ms, a.username",
cc1cd197 Andy McCall 1 month ago ↶	422	)
cc1cd197 Andy McCall 1 month ago ↶	423	.bind(proposal_id)
86a69b53 Andy McCall 17 days ago ↶	424	.fetch_all(pool)
	425	.await
	426	}
	427
	428	/// Set, or with `by` empty clear, landing when checks pass (#261).
	429	pub async fn set_auto_land(pool: &Pool, id: i64, by: &str) -> sqlx::Result<()> {
	430	sqlx::query("UPDATE proposals SET auto_land_by = $1, auto_land_ms = $2 WHERE id = $3")
	431	.bind(by)
	432	.bind((!by.is_empty()).then(now_ms))
	433	.bind(id)
	434	.execute(pool)
	435	.await?;
	436	Ok(())
	437	}
	438
	439	/// A repository's open proposals set to land when their checks pass, on
	440	/// projected state `state`.
	441	pub async fn auto_landing(pool: &Pool, repo_id: i64, state: &str) -> sqlx::Result<Vec<ProposalRow>> {
	442	sqlx::query_as(
	443	"SELECT * FROM proposals WHERE repo_id = $1 AND state = 'open' AND auto_land_by != '' AND projected_state = $2 ORDER BY number",
	444	)
	445	.bind(repo_id)
	446	.bind(state)
cc1cd197 Andy McCall 1 month ago ↶	447	.fetch_all(pool)
	448	.await
	449	}

Keyboard