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

◆ main oxygenhub / crates / hub / src / db / proposals.rs

14.7 KiB · Rust · last changed New repository page with board and starting files; assetlinks.json; hung tests stopped; changelog on the WASM tier (closes #360, closes #365, closes #370) 1 day 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();
    let row: ProposalRow = 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?;
    // Onto the boards the repository is linked to (#360).
    super::projects::opened(pool, row.repo_id, "proposal", row.number, &row.author).await?;
    Ok(row)
}

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 22 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();
40a43921 Andy McCall 1 day ago ↶	128	let row: ProposalRow = sqlx::query_as(
de505df5 Andy McCall 1 month ago ↶	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)
40a43921 Andy McCall 1 day ago ↶	147	.await?;
	148	// Onto the boards the repository is linked to (#360).
	149	super::projects::opened(pool, row.repo_id, "proposal", row.number, &row.author).await?;
	150	Ok(row)
de505df5 Andy McCall 1 month ago ↶	151	}
	152
	153	pub async fn get(pool: &Pool, repo_id: i64, number: i64) -> sqlx::Result<Option<ProposalRow>> {
5d90e0e0 Andy McCall 20 days ago	154	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 ↶	155	}
	156
	157	pub async fn list(pool: &Pool, repo_id: i64, states: &[&str]) -> sqlx::Result<Vec<ProposalRow>> {
	158	let all: Vec<ProposalRow> =
5d90e0e0 Andy McCall 20 days ago	159	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 ↶	160	Ok(all.into_iter().filter(\|p\| states.is_empty() \|\| states.contains(&p.state.as_str())).collect())
	161	}
	162
	163	/// Open proposals of every repository, for the front page.
	164	pub async fn open_for_author(pool: &Pool, author_id: i64) -> sqlx::Result<Vec<ProposalRow>> {
5d90e0e0 Andy McCall 20 days ago	165	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 ↶	166	.bind(author_id)
	167	.fetch_all(pool)
	168	.await
	169	}
	170
	171	pub async fn set_projection(
	172	pool: &Pool,
	173	id: i64,
	174	state: &str,
	175	heads: &str,
	176	target_state: &str,
	177	changes: &str,
	178	conflicted: bool,
	179	) -> sqlx::Result<()> {
5d90e0e0 Andy McCall 20 days ago	180	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 ↶	181	.bind(state)
	182	.bind(heads)
	183	.bind(target_state)
	184	.bind(changes)
	185	.bind(conflicted)
	186	.bind(id)
	187	.execute(pool)
	188	.await?;
	189	Ok(())
	190	}
	191
	192	pub async fn set_state(pool: &Pool, id: i64, state: &str, landed_state: &str) -> sqlx::Result<()> {
5d90e0e0 Andy McCall 20 days ago	193	sqlx::query("UPDATE proposals SET state = $1, landed_state = $2, updated_ms = $3 WHERE id = $4")
de505df5 Andy McCall 1 month ago ↶	194	.bind(state)
	195	.bind(landed_state)
	196	.bind(now_ms())
	197	.bind(id)
	198	.execute(pool)
	199	.await?;
1000b40a Andy McCall 10 days ago ↶	200	if state == "closed" \|\| state == "landed" {
	201	super::issues::finished(pool, "proposals", id).await?;
	202	}
0b20f1ec Andy McCall 1 month ago ↶	203	Ok(())
	204	}
	205
	206	/// Mark a proposal landed, with who landed it and how.
	207	pub async fn set_landed(pool: &Pool, id: i64, landed_state: &str, by: &str, via: &str) -> sqlx::Result<()> {
	208	let now = now_ms();
	209	sqlx::query(
5d90e0e0 Andy McCall 20 days ago	210	"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 ↶	211	)
	212	.bind(landed_state)
	213	.bind(by)
	214	.bind(via)
	215	.bind(now)
	216	.bind(now)
	217	.bind(id)
	218	.execute(pool)
	219	.await?;
1000b40a Andy McCall 10 days ago ↶	220	super::issues::finished(pool, "proposals", id).await
de505df5 Andy McCall 1 month ago ↶	221	}
	222
	223	pub async fn edit(pool: &Pool, id: i64, title: &str, body: &str, target: &str) -> sqlx::Result<()> {
5d90e0e0 Andy McCall 20 days ago	224	sqlx::query("UPDATE proposals SET title = $1, body = $2, target = $3, updated_ms = $4 WHERE id = $5")
de505df5 Andy McCall 1 month ago ↶	225	.bind(title)
	226	.bind(body)
	227	.bind(target)
	228	.bind(now_ms())
	229	.bind(id)
	230	.execute(pool)
	231	.await?;
	232	Ok(())
	233	}
	234
	235	pub async fn touch(pool: &Pool, id: i64) -> sqlx::Result<()> {
5d90e0e0 Andy McCall 20 days ago	236	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 ↶	237	Ok(())
	238	}
	239
	240	pub async fn comments(pool: &Pool, proposal_id: i64) -> sqlx::Result<Vec<CommentRow>> {
5d90e0e0 Andy McCall 20 days ago	241	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 ↶	242	}
	243
	244	pub async fn add_comment(
	245	pool: &Pool,
	246	proposal_id: i64,
	247	author_id: Option<i64>,
	248	author: &str,
	249	body: &str,
	250	path: &str,
	251	anchor: Option<(&str, i64)>,
	252	) -> sqlx::Result<CommentRow> {
	253	let row = sqlx::query_as(
5d90e0e0 Andy McCall 20 days ago	254	"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 ↶	255	)
	256	.bind(proposal_id)
	257	.bind(author_id)
	258	.bind(author)
	259	.bind(body)
	260	.bind(path)
	261	.bind(anchor.map(\|a\| a.0).unwrap_or(""))
	262	.bind(anchor.map(\|a\| a.1).unwrap_or(0))
	263	.bind(now_ms())
ffb67a81 Andy McCall 22 days ago	264	.fetch_one(pool)
	265	.await?;
	266	touch(pool, proposal_id).await?;
	267	Ok(row)
	268	}
	269
	270	/// A conversation comment an AI assistant (`via`) wrote for `author`,
	271	/// who asked it.
	272	pub async fn add_comment_via(
	273	pool: &Pool,
	274	proposal_id: i64,
	275	author_id: i64,
	276	author: &str,
	277	body: &str,
	278	via: &str,
	279	) -> sqlx::Result<CommentRow> {
	280	let row = sqlx::query_as(
5d90e0e0 Andy McCall 20 days ago	281	"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 22 days ago	282	)
	283	.bind(proposal_id)
	284	.bind(author_id)
	285	.bind(author)
	286	.bind(body)
	287	.bind(now_ms())
	288	.bind(via)
de505df5 Andy McCall 1 month ago ↶	289	.fetch_one(pool)
	290	.await?;
	291	touch(pool, proposal_id).await?;
	292	Ok(row)
	293	}
	294
	295	pub async fn set_comment_resolved(pool: &Pool, id: i64, resolved: bool) -> sqlx::Result<()> {
5d90e0e0 Andy McCall 20 days ago	296	sqlx::query("UPDATE proposal_comments SET resolved = $1 WHERE id = $2").bind(resolved).bind(id).execute(pool).await?;
de505df5 Andy McCall 1 month ago ↶	297	Ok(())
	298	}
	299
	300	pub async fn reviews(pool: &Pool, proposal_id: i64) -> sqlx::Result<Vec<ReviewRow>> {
5d90e0e0 Andy McCall 20 days ago	301	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 ↶	302	}
	303
	304	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 ↶	305	add_review_via(pool, proposal_id, author_id, author, verdict, body, "").await
	306	}
	307
	308	/// A review answered by an AI assistant (`via`) for `author`, who asked.
	309	pub async fn add_review_via(
	310	pool: &Pool,
	311	proposal_id: i64,
	312	author_id: i64,
	313	author: &str,
	314	verdict: &str,
	315	body: &str,
	316	via: &str,
	317	) -> sqlx::Result<ReviewRow> {
de505df5 Andy McCall 1 month ago ↶	318	let row = sqlx::query_as(
5d90e0e0 Andy McCall 20 days ago	319	"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 ↶	320	)
	321	.bind(proposal_id)
	322	.bind(author_id)
	323	.bind(author)
	324	.bind(verdict)
	325	.bind(body)
	326	.bind(now_ms())
7d2eb590 Andy McCall 1 month ago ↶	327	.bind(via)
de505df5 Andy McCall 1 month ago ↶	328	.fetch_one(pool)
	329	.await?;
	330	touch(pool, proposal_id).await?;
	331	Ok(row)
	332	}
	333
	334	pub async fn events(pool: &Pool, proposal_id: i64) -> sqlx::Result<Vec<EventRow>> {
5d90e0e0 Andy McCall 20 days ago	335	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 ↶	336	.bind(proposal_id)
	337	.fetch_all(pool)
	338	.await
	339	}
	340
8bb7baeb Andy McCall 10 days ago ↶	341	/// Whether change `hash` (its short form) already references it (#318):
	342	/// a change is recorded once, whichever bookmarks it is pushed on.
	343	pub async fn referenced_by(pool: &Pool, proposal_id: i64, hash: &str) -> sqlx::Result<bool> {
	344	let n: i64 = sqlx::query_scalar(
	345	"SELECT COUNT(*) FROM proposal_events WHERE proposal_id = $1 AND event = 'referenced by change' AND (detail = $2 OR detail LIKE $3)",
	346	)
	347	.bind(proposal_id)
	348	.bind(hash)
	349	.bind(format!("{hash} %"))
	350	.fetch_one(pool)
	351	.await?;
	352	Ok(n > 0)
	353	}
	354
de505df5 Andy McCall 1 month ago ↶	355	pub async fn add_event(pool: &Pool, proposal_id: i64, actor: &str, event: &str, detail: &str) -> sqlx::Result<()> {
5d90e0e0 Andy McCall 20 days ago	356	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 ↶	357	.bind(proposal_id)
	358	.bind(now_ms())
	359	.bind(actor)
	360	.bind(event)
	361	.bind(detail)
	362	.execute(pool)
	363	.await?;
	364	Ok(())
	365	}
	366
	367	/// Landing requirements of a repository.
	368	#[derive(Clone, Copy, Debug, Default, FromRow)]
	369	pub struct Requirements {
	370	pub required_approvals: i64,
	371	pub require_owners: bool,
	372	pub allow_conflicts: bool,
	373	}
	374
	375	pub async fn requirements(pool: &Pool, repo_id: i64) -> sqlx::Result<Requirements> {
5d90e0e0 Andy McCall 20 days ago	376	sqlx::query_as("SELECT required_approvals, require_owners, allow_conflicts FROM repositories WHERE id = $1")
de505df5 Andy McCall 1 month ago ↶	377	.bind(repo_id)
	378	.fetch_one(pool)
	379	.await
	380	}
	381
	382	pub async fn set_requirements(pool: &Pool, repo_id: i64, r: Requirements) -> sqlx::Result<()> {
5d90e0e0 Andy McCall 20 days ago	383	sqlx::query("UPDATE repositories SET required_approvals = $1, require_owners = $2, allow_conflicts = $3 WHERE id = $4")
de505df5 Andy McCall 1 month ago ↶	384	.bind(r.required_approvals)
	385	.bind(r.require_owners)
	386	.bind(r.allow_conflicts)
	387	.bind(repo_id)
	388	.execute(pool)
	389	.await?;
	390	Ok(())
	391	}
cc1cd197 Andy McCall 1 month ago ↶	392
	393	// ------------------------------------------------------- review requests
	394
	395	/// Ask an account to review a proposal; false when it was already asked.
	396	pub async fn request_review(pool: &Pool, proposal_id: i64, account_id: i64, by: &str) -> sqlx::Result<bool> {
	397	let r = sqlx::query(
d1bf2d69 Andy McCall 20 days ago ↶	398	"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 ↶	399	)
	400	.bind(proposal_id)
	401	.bind(account_id)
	402	.bind(by)
	403	.bind(now_ms())
	404	.execute(pool)
	405	.await?;
	406	Ok(r.rows_affected() == 1)
	407	}
	408
	409	/// Withdraw a request, or mark it answered; false when there was none.
	410	pub async fn unrequest_review(pool: &Pool, proposal_id: i64, account_id: i64) -> sqlx::Result<bool> {
5d90e0e0 Andy McCall 20 days ago	411	let r = sqlx::query("DELETE FROM proposal_review_requests WHERE proposal_id = $1 AND account_id = $2")
cc1cd197 Andy McCall 1 month ago ↶	412	.bind(proposal_id)
	413	.bind(account_id)
	414	.execute(pool)
	415	.await?;
	416	Ok(r.rows_affected() == 1)
	417	}
	418
	419	/// The usernames asked to review a proposal and not yet reviewing, in the
	420	/// order they were asked.
	421	pub async fn review_requests(pool: &Pool, proposal_id: i64) -> sqlx::Result<Vec<String>> {
	422	sqlx::query_scalar(
	423	"SELECT a.username FROM proposal_review_requests r JOIN accounts a ON a.id = r.account_id \
5d90e0e0 Andy McCall 20 days ago	424	WHERE r.proposal_id = $1 ORDER BY r.requested_ms, a.username",
cc1cd197 Andy McCall 1 month ago ↶	425	)
cc1cd197 Andy McCall 1 month ago ↶	426	.bind(proposal_id)
86a69b53 Andy McCall 17 days ago ↶	427	.fetch_all(pool)
	428	.await
	429	}
	430
	431	/// Set, or with `by` empty clear, landing when checks pass (#261).
	432	pub async fn set_auto_land(pool: &Pool, id: i64, by: &str) -> sqlx::Result<()> {
	433	sqlx::query("UPDATE proposals SET auto_land_by = $1, auto_land_ms = $2 WHERE id = $3")
	434	.bind(by)
	435	.bind((!by.is_empty()).then(now_ms))
	436	.bind(id)
	437	.execute(pool)
	438	.await?;
	439	Ok(())
	440	}
	441
	442	/// A repository's open proposals set to land when their checks pass, on
	443	/// projected state `state`.
	444	pub async fn auto_landing(pool: &Pool, repo_id: i64, state: &str) -> sqlx::Result<Vec<ProposalRow>> {
	445	sqlx::query_as(
	446	"SELECT * FROM proposals WHERE repo_id = $1 AND state = 'open' AND auto_land_by != '' AND projected_state = $2 ORDER BY number",
	447	)
	448	.bind(repo_id)
	449	.bind(state)
cc1cd197 Andy McCall 1 month ago ↶	450	.fetch_all(pool)
	451	.await
	452	}

Keyboard