Projects: boards of issues, proposals and notes across an owner's repositories, with columns, cards moved without JavaScript, private projects, and the projects shown on issues and proposals e1ec9fbf538e

-- Projects: boards of issues, proposals and notes across one owner's

-- repositories (hub spec §8.2).

CREATE TABLE projects (

    id          INTEGER PRIMARY KEY,

    owner       TEXT NOT NULL,

    number      INTEGER NOT NULL,

    title       TEXT NOT NULL,

    description TEXT NOT NULL DEFAULT '',

    public      INTEGER NOT NULL DEFAULT 1,

    closed      INTEGER NOT NULL DEFAULT 0,

    created_ms  INTEGER NOT NULL,

    updated_ms  INTEGER NOT NULL,

    UNIQUE (owner, number)

);

CREATE TABLE project_columns (

    id         INTEGER PRIMARY KEY,

    project_id INTEGER NOT NULL REFERENCES projects(id) ON DELETE CASCADE,

    name       TEXT NOT NULL,

    position   INTEGER NOT NULL

);

CREATE INDEX project_columns_by_project ON project_columns (project_id, position);

CREATE TABLE project_cards (

    id         INTEGER PRIMARY KEY,

    project_id INTEGER NOT NULL REFERENCES projects(id) ON DELETE CASCADE,

    column_id  INTEGER NOT NULL REFERENCES project_columns(id) ON DELETE CASCADE,

    position   INTEGER NOT NULL,

    kind       TEXT NOT NULL CHECK (kind IN ('issue', 'proposal', 'note')),

    repo_id    INTEGER REFERENCES repositories(id) ON DELETE CASCADE,

    number     INTEGER,

    note       TEXT NOT NULL DEFAULT '',

    added_by   TEXT NOT NULL DEFAULT '',

    created_ms INTEGER NOT NULL

);

CREATE INDEX project_cards_by_column ON project_cards (column_id, position);

CREATE UNIQUE INDEX project_cards_once ON project_cards (project_id, repo_id, number) WHERE kind <> 'note';

//! Projects, their columns and cards (hub spec §8.2).

use super::{now_ms, Pool};

use sqlx::FromRow;

#[derive(Clone, Debug, FromRow)]

pub struct Project {

    pub id: i64,

    pub owner: String,

    pub number: i64,

    pub title: String,

    pub description: String,

    pub public: bool,

    pub closed: bool,

    pub created_ms: i64,

    pub updated_ms: i64,

}

#[derive(Clone, Debug, FromRow)]

pub struct Column {

    pub id: i64,

    pub project_id: i64,

    pub name: String,

    pub position: i64,

}

#[derive(Clone, Debug, FromRow)]

pub struct Card {

    pub id: i64,

    pub project_id: i64,

    pub column_id: i64,

    pub position: i64,

    pub kind: String,

    pub repo_id: Option<i64>,

    pub number: Option<i64>,

    pub note: String,

    pub added_by: String,

    pub created_ms: i64,

}

pub const DEFAULT_COLUMNS: [&str; 3] = ["Backlog", "Doing", "Done"];

pub async fn create(pool: &Pool, owner: &str, title: &str, description: &str, public: bool) -> sqlx::Result<Project> {

    let mut tx = pool.begin().await?;

    let number: i64 =

        sqlx::query_scalar("SELECT COALESCE(MAX(number), 0) + 1 FROM projects WHERE owner = ?").bind(owner).fetch_one(&mut *tx).await?;

    let now = now_ms();

    let p: Project = sqlx::query_as(

        "INSERT INTO projects (owner, number, title, description, public, created_ms, updated_ms) VALUES (?, ?, ?, ?, ?, ?, ?) RETURNING *",

    )

    .bind(owner)

    .bind(number)

    .bind(title)

    .bind(description)

    .bind(public)

    .bind(now)

    .bind(now)

    .fetch_one(&mut *tx)

    .await?;

    for (i, name) in DEFAULT_COLUMNS.iter().enumerate() {

        sqlx::query("INSERT INTO project_columns (project_id, name, position) VALUES (?, ?, ?)")

            .bind(p.id)

            .bind(name)

            .bind(i as i64)

            .execute(&mut *tx)

            .await?;

    }

    tx.commit().await?;

    Ok(p)

}

pub async fn of_owner(pool: &Pool, owner: &str) -> sqlx::Result<Vec<Project>> {

    sqlx::query_as("SELECT * FROM projects WHERE owner = ? ORDER BY closed, updated_ms DESC").bind(owner).fetch_all(pool).await

}

pub async fn get(pool: &Pool, owner: &str, number: i64) -> sqlx::Result<Option<Project>> {

    sqlx::query_as("SELECT * FROM projects WHERE owner = ? AND number = ?").bind(owner).bind(number).fetch_optional(pool).await

}

pub async fn set(pool: &Pool, id: i64, title: &str, description: &str, public: bool, closed: bool) -> sqlx::Result<()> {

    sqlx::query("UPDATE projects SET title = ?, description = ?, public = ?, closed = ?, updated_ms = ? WHERE id = ?")

        .bind(title)

        .bind(description)

        .bind(public)

        .bind(closed)

        .bind(now_ms())

        .bind(id)

        .execute(pool)

        .await?;

    Ok(())

}

pub async fn delete(pool: &Pool, id: i64) -> sqlx::Result<()> {

    sqlx::query("DELETE FROM projects WHERE id = ?").bind(id).execute(pool).await?;

    Ok(())

}

async fn touch(pool: &Pool, id: i64) -> sqlx::Result<()> {

    sqlx::query("UPDATE projects SET updated_ms = ? WHERE id = ?").bind(now_ms()).bind(id).execute(pool).await?;

    Ok(())

}

pub async fn columns(pool: &Pool, project_id: i64) -> sqlx::Result<Vec<Column>> {

    sqlx::query_as("SELECT * FROM project_columns WHERE project_id = ? ORDER BY position, id").bind(project_id).fetch_all(pool).await

}

pub async fn cards(pool: &Pool, project_id: i64) -> sqlx::Result<Vec<Card>> {

    sqlx::query_as("SELECT * FROM project_cards WHERE project_id = ? ORDER BY position, id").bind(project_id).fetch_all(pool).await

}

pub async fn add_column(pool: &Pool, project_id: i64, name: &str) -> sqlx::Result<()> {

    let pos: i64 = sqlx::query_scalar("SELECT COALESCE(MAX(position), -1) + 1 FROM project_columns WHERE project_id = ?")

        .bind(project_id)

        .fetch_one(pool)

        .await?;

    sqlx::query("INSERT INTO project_columns (project_id, name, position) VALUES (?, ?, ?)")

        .bind(project_id)

        .bind(name)

        .bind(pos)

        .execute(pool)

        .await?;

    touch(pool, project_id).await

}

pub async fn rename_column(pool: &Pool, project_id: i64, column_id: i64, name: &str) -> sqlx::Result<()> {

    sqlx::query("UPDATE project_columns SET name = ? WHERE id = ? AND project_id = ?")

        .bind(name)

        .bind(column_id)

        .bind(project_id)

        .execute(pool)

        .await?;

    touch(pool, project_id).await

}

/// Swap a column with its neighbour (`-1` left, `1` right).

pub async fn shift_column(pool: &Pool, project_id: i64, column_id: i64, by: i64) -> sqlx::Result<()> {

    let cols = columns(pool, project_id).await?;

    let Some(i) = cols.iter().position(|c| c.id == column_id) else { return Ok(()) };

    let j = i as i64 + by;

    if j < 0 || j as usize >= cols.len() {

        return Ok(());

    }

    let mut ids: Vec<i64> = cols.iter().map(|c| c.id).collect();

    ids.swap(i, j as usize);

    for (pos, id) in ids.iter().enumerate() {

        sqlx::query("UPDATE project_columns SET position = ? WHERE id = ?").bind(pos as i64).bind(id).execute(pool).await?;

    }

    touch(pool, project_id).await

}

/// Remove a column; its cards go to the first remaining column. The last column stays.

pub async fn remove_column(pool: &Pool, project_id: i64, column_id: i64) -> sqlx::Result<bool> {

    let cols = columns(pool, project_id).await?;

    let Some(to) = cols.iter().find(|c| c.id != column_id) else { return Ok(false) };

    if !cols.iter().any(|c| c.id == column_id) {

        return Ok(false);

    }

    let mut tx = pool.begin().await?;

    let base: i64 = sqlx::query_scalar("SELECT COALESCE(MAX(position), -1) + 1 FROM project_cards WHERE column_id = ?")

        .bind(to.id)

        .fetch_one(&mut *tx)

        .await?;

    sqlx::query("UPDATE project_cards SET column_id = ?, position = position + ? WHERE column_id = ?")

        .bind(to.id)

        .bind(base)

        .bind(column_id)

        .execute(&mut *tx)

        .await?;

    sqlx::query("DELETE FROM project_columns WHERE id = ?").bind(column_id).execute(&mut *tx).await?;

    tx.commit().await?;

    touch(pool, project_id).await?;

    Ok(true)

}

pub struct NewCard<'a> {

    pub column_id: i64,

    pub kind: &'a str,

    pub repo_id: Option<i64>,

    pub number: Option<i64>,

    pub note: &'a str,

    pub added_by: &'a str,

}

/// Add a card at the bottom of a column; `Ok(false)` when that issue or

/// proposal is already on the project.

pub async fn add_card(pool: &Pool, project_id: i64, c: NewCard<'_>) -> sqlx::Result<bool> {

    let pos: i64 = sqlx::query_scalar("SELECT COALESCE(MAX(position), -1) + 1 FROM project_cards WHERE column_id = ?")

        .bind(c.column_id)

        .fetch_one(pool)

        .await?;

    let r = sqlx::query(

        "INSERT OR IGNORE INTO project_cards (project_id, column_id, position, kind, repo_id, number, note, added_by, created_ms) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)",

    )

    .bind(project_id)

    .bind(c.column_id)

    .bind(pos)

    .bind(c.kind)

    .bind(c.repo_id)

    .bind(c.number)

    .bind(c.note)

    .bind(c.added_by)

    .bind(now_ms())

    .execute(pool)

    .await?;

    touch(pool, project_id).await?;

    Ok(r.rows_affected() == 1)

}

/// Move a card to the bottom of another column of the same project.

pub async fn move_card(pool: &Pool, project_id: i64, card_id: i64, column_id: i64) -> sqlx::Result<()> {

    let ok: i64 = sqlx::query_scalar("SELECT COUNT(*) FROM project_columns WHERE id = ? AND project_id = ?")

        .bind(column_id)

        .bind(project_id)

        .fetch_one(pool)

        .await?;

    if ok == 0 {

        return Ok(());

    }

    let pos: i64 = sqlx::query_scalar("SELECT COALESCE(MAX(position), -1) + 1 FROM project_cards WHERE column_id = ?")

        .bind(column_id)

        .fetch_one(pool)

        .await?;

    sqlx::query("UPDATE project_cards SET column_id = ?, position = ? WHERE id = ? AND project_id = ?")

        .bind(column_id)

        .bind(pos)

        .bind(card_id)

        .bind(project_id)

        .execute(pool)

        .await?;

    touch(pool, project_id).await

}

/// Swap a card with its neighbour in its column (`-1` up, `1` down).

pub async fn shift_card(pool: &Pool, project_id: i64, card_id: i64, by: i64) -> sqlx::Result<()> {

    let Some(card): Option<Card> = sqlx::query_as("SELECT * FROM project_cards WHERE id = ? AND project_id = ?")

        .bind(card_id)

        .bind(project_id)

        .fetch_optional(pool)

        .await?

    else {

        return Ok(());

    };

    let col: Vec<Card> = sqlx::query_as("SELECT * FROM project_cards WHERE column_id = ? ORDER BY position, id")

        .bind(card.column_id)

        .fetch_all(pool)

        .await?;

    let Some(i) = col.iter().position(|c| c.id == card_id) else { return Ok(()) };

    let j = i as i64 + by;

    if j < 0 || j as usize >= col.len() {

        return Ok(());

    }

    let mut ids: Vec<i64> = col.iter().map(|c| c.id).collect();

    ids.swap(i, j as usize);

    for (pos, id) in ids.iter().enumerate() {

        sqlx::query("UPDATE project_cards SET position = ? WHERE id = ?").bind(pos as i64).bind(id).execute(pool).await?;

    }

    touch(pool, project_id).await

}

pub async fn remove_card(pool: &Pool, project_id: i64, card_id: i64) -> sqlx::Result<()> {

    sqlx::query("DELETE FROM project_cards WHERE id = ? AND project_id = ?").bind(card_id).bind(project_id).execute(pool).await?;

    touch(pool, project_id).await

}

/// The projects (and column names) an issue or proposal is on.

pub async fn placements(pool: &Pool, repo_id: i64, number: i64) -> sqlx::Result<Vec<(Project, String)>> {

    let rows: Vec<(i64, String)> = sqlx::query_as(

        "SELECT c.project_id, col.name FROM project_cards c JOIN project_columns col ON col.id = c.column_id WHERE c.repo_id = ? AND c.number = ? AND c.kind <> 'note'",

    )

    .bind(repo_id)

    .bind(number)

    .fetch_all(pool)

    .await?;

    let mut out = Vec::new();

    for (pid, col) in rows {

        if let Some(p) = sqlx::query_as::<_, Project>("SELECT * FROM projects WHERE id = ?").bind(pid).fetch_optional(pool).await? {

            out.push((p, col));

        }

    }

    Ok(out)

}

//! Projects (hub spec §8.2): boards of issues, proposals and notes across

//! the repositories of one person or organisation.

use super::settings::csrf;

use super::{filters, Error, Page, Shell};

use crate::auth::{roles, Access, Viewer};

use crate::db::{self, projects as pdb};

use crate::Shared;

use askama::Template;

use axum::extract::{Form, Path, State};

use axum::response::{IntoResponse, Redirect, Response};

use serde::Deserialize;

/// What the viewer may do with an owner's projects.

struct Rights {

    /// Sees private projects.

    member: bool,

    /// Creates and changes projects.

    edit: bool,

}

async fn rights(app: &Shared, shell: &Shell, viewer: &Viewer, owner: &str) -> Result<Rights, Error> {

    let e = |e| Error::from_db(shell.clone(), e);

    let admin = viewer.is_admin();

    if let Some(a) = db::accounts::account_by_name(&app.db, owner).await.map_err(e)? {

        let me = viewer.account().is_some_and(|v| v.id == a.id);

        return Ok(Rights { member: me || admin, edit: me || admin });

    }

    if let Some(o) = db::accounts::org_by_name(&app.db, owner).await.map_err(e)? {

        let member = match viewer.account() {

            Some(v) => db::accounts::org_role(&app.db, o.id, v.id).await.map_err(e)?.is_some(),

            None => false,

        };

        return Ok(Rights { member: member || admin, edit: member || admin });

    }

    Err(Error::not_found(shell.clone()))

}

#[derive(Template)]

#[template(path = "projects.html")]

pub struct ProjectsPage {

    shell: Shell,

    owner: String,

    projects: Vec<pdb::Project>,

    can_edit: bool,

    error: Option<String>,

}

pub async fn list(State(app): State<Shared>, viewer: Viewer, Path(owner): Path<String>) -> Result<Page<ProjectsPage>, Error> {

    let shell = Shell::new(&app, &format!("/projects/{owner}"), &viewer);

    let r = rights(&app, &shell, &viewer, &owner).await?;

    let projects = pdb::of_owner(&app.db, &owner)

        .await

        .map_err(|e| Error::from_db(shell.clone(), e))?

        .into_iter()

        .filter(|p| p.public || r.member)

        .collect();

    Ok(Page(ProjectsPage { shell, owner, projects, can_edit: r.edit, error: None }))

}

#[derive(Deserialize)]

pub struct NewProjectForm {

    #[serde(rename = "_csrf")]

    csrf_token: String,

    title: String,

    #[serde(default)]

    description: String,

    #[serde(default)]

    private: Option<String>,

}

pub async fn create(

    State(app): State<Shared>,

    viewer: Viewer,

    Path(owner): Path<String>,

    Form(f): Form<NewProjectForm>,

) -> Result<Response, Error> {

    let shell = Shell::new(&app, &format!("/projects/{owner}"), &viewer);

    csrf(&viewer, &f.csrf_token, &shell)?;

    let r = rights(&app, &shell, &viewer, &owner).await?;

    if !r.edit {

        return Err(Error::not_found(shell));

    }

    let title = f.title.trim();

    if title.is_empty() || title.len() > 100 {

        let projects = pdb::of_owner(&app.db, &owner).await.map_err(|e| Error::from_db(shell.clone(), e))?;

        return Ok(Page(ProjectsPage {

            shell,

            owner,

            projects,

            can_edit: true,

            error: Some("A project needs a title of up to 100 characters.".into()),

        })

        .into_response());

    }

    let p = pdb::create(&app.db, &owner, title, f.description.trim(), f.private.is_none())

        .await

        .map_err(|e| Error::from_db(shell.clone(), e))?;

    db::accounts::audit(&app.db, viewer.account(), "project.created", &format!("{owner}/{}", p.number), title).await.ok();

    Ok(Redirect::to(&format!("/projects/{owner}/{}", p.number)).into_response())

}

/// A card as the board shows it.

pub struct CardView {

    pub card: pdb::Card,

    /// `owner/name#N`, for issue and proposal cards.

    pub reference: String,

    pub url: String,

    pub title: String,

    /// `open`, `closed`, `landed`, `draft`… for issues and proposals.

    pub state: String,

}

pub struct ColumnView {

    pub column: pdb::Column,

    pub cards: Vec<CardView>,

}

#[derive(Template)]

#[template(path = "project.html")]

pub struct BoardPage {

    shell: Shell,

    project: pdb::Project,

    columns: Vec<ColumnView>,

    /// The owner's repositories the viewer can read, for adding cards.

    repos: Vec<String>,

    /// Changes cards and columns (an open project).

    can_edit: bool,

    /// Changes the settings (to reopen a closed project too).

    can_manage: bool,

    notice: Option<String>,

    error: Option<String>,

}

async fn board(

    app: &Shared,

    shell: Shell,

    viewer: &Viewer,

    owner: &str,

    number: i64,

    notice: Option<String>,

    error: Option<String>,

) -> Result<Page<BoardPage>, Error> {

    let e = |e| Error::from_db(shell.clone(), e);

    let r = rights(app, &shell, viewer, owner).await?;

    let project = pdb::get(&app.db, owner, number)

        .await

        .map_err(e)?

        .filter(|p| p.public || r.member)

        .ok_or_else(|| Error::not_found(shell.clone()))?;

    let mut readable: std::collections::HashMap<i64, db::repos::RepoRow> = Default::default();

    for row in db::repos::list_by_owner(&app.db, owner).await.map_err(e)? {

        let role = roles::role_for(&app.db, viewer.account(), &row).await.map_err(e)?;

        if roles::allows(role, Access::Read) {

            readable.insert(row.id, row);

        }

    }

    let cards = pdb::cards(&app.db, project.id).await.map_err(e)?;

    let mut columns = Vec::new();

    for column in pdb::columns(&app.db, project.id).await.map_err(e)? {

        let mut views = Vec::new();

        for card in cards.iter().filter(|c| c.column_id == column.id) {

            match (card.kind.as_str(), card.repo_id, card.number) {

                ("note", _, _) => views.push(CardView {

                    card: card.clone(),

                    reference: String::new(),

                    url: String::new(),

                    title: card.note.clone(),

                    state: String::new(),

                }),

                (kind, Some(rid), Some(n)) => {

                    // Cards of repositories the viewer cannot read are not shown at all.

                    let Some(row) = readable.get(&rid) else { continue };

                    let (title, state) = if kind == "issue" {

                        match db::issues::get(&app.db, rid, n).await.map_err(e)? {

                            Some(i) => (i.title, i.state),

                            None => continue,

                        }

                    } else {

                        match db::proposals::get(&app.db, rid, n).await.map_err(e)? {

                            Some(p) => (p.title, p.state),

                            None => continue,

                        }

                    };

                    let slug = format!("{}/{}", row.owner, row.name);

                    views.push(CardView {

                        card: card.clone(),

                        reference: format!("{slug}#{n}"),

                        url: format!("/{slug}/{kind}/{n}"),

                        title,

                        state,

                    });

                }

                _ => {}

            }

        }

        columns.push(ColumnView { column, cards: views });

    }

    let mut repos: Vec<String> = readable.values().map(|r| r.name.clone()).collect();

    repos.sort();

    let can_edit = r.edit && !project.closed;

    Ok(Page(BoardPage { shell, project, columns, repos, can_edit, can_manage: r.edit, notice, error }))

}

pub async fn show(State(app): State<Shared>, viewer: Viewer, Path((owner, number)): Path<(String, i64)>) -> Result<Page<BoardPage>, Error> {

    let shell = Shell::new(&app, &format!("/projects/{owner}/{number}"), &viewer);

    board(&app, shell, &viewer, &owner, number, None, None).await

}

#[derive(Deserialize)]

pub struct BoardForm {

    #[serde(rename = "_csrf")]

    csrf_token: String,

    action: String,

    #[serde(default)]

    card: Option<i64>,

    #[serde(default)]

    column: Option<i64>,

    #[serde(default)]

    name: String,

    /// `repo#12`, or `#12` with `repo`.

    #[serde(default)]

    reference: String,

    #[serde(default)]

    repo: String,

    #[serde(default)]

    note: String,

    #[serde(default)]

    title: String,

    #[serde(default)]

    description: String,

    #[serde(default)]

    private: Option<String>,

    #[serde(default)]

    closed: Option<String>,

}

/// `repo#12` or `#12` (with a repository chosen) to (repository, number).

fn parse_reference(reference: &str, repo: &str) -> Option<(String, i64)> {

    let r = reference.trim();

    let (name, n) = r.split_once('#')?;

    let name = if name.is_empty() { repo.trim() } else { name.trim() };

    let n: i64 = n.trim().parse().ok()?;

    (!name.is_empty() && n > 0 && !name.contains('/')).then(|| (name.to_string(), n))

}

pub async fn act(

    State(app): State<Shared>,

    viewer: Viewer,

    Path((owner, number)): Path<(String, i64)>,

    Form(f): Form<BoardForm>,

) -> Result<Response, Error> {

    let shell = Shell::new(&app, &format!("/projects/{owner}/{number}"), &viewer);

    csrf(&viewer, &f.csrf_token, &shell)?;

    let r = rights(&app, &shell, &viewer, &owner).await?;

    let e = |e| Error::from_db(shell.clone(), e);

    let project = pdb::get(&app.db, &owner, number)

        .await

        .map_err(e)?

        .filter(|p| p.public || r.member)

        .ok_or_else(|| Error::not_found(shell.clone()))?;

    if !r.edit {

        return Err(Error::not_found(shell));

    }

    if project.closed && !matches!(f.action.as_str(), "settings" | "delete") {

        return Ok(board(&app, shell, &viewer, &owner, number, None, Some("This project is closed; reopen it to change it.".into()))

            .await?

            .into_response());

    }

    let actor = viewer.account().map(|a| a.username.clone()).unwrap_or_default();

    let columns = pdb::columns(&app.db, project.id).await.map_err(e)?;

    let column = f.column.filter(|c| columns.iter().any(|x| x.id == *c)).or(columns.first().map(|c| c.id));

    let mut notice = None;

    let mut error = None;

    match f.action.as_str() {

        "add" => {

            let Some(column_id) = column else { return Err(Error::bad(shell, "no column")) };

            if !f.note.trim().is_empty() {

                let note: String = f.note.trim().chars().take(500).collect();

                pdb::add_card(

                    &app.db,

                    project.id,

                    pdb::NewCard { column_id, kind: "note", repo_id: None, number: None, note: &note, added_by: &actor },

                )

                .await

                .map_err(e)?;

            } else {

                match parse_reference(&f.reference, &f.repo) {

                    None => error = Some("Name an issue or proposal as repo#12, or write a note.".to_string()),

                    Some((repo, n)) => match db::repos::get(&app.db, &owner, &repo).await.map_err(e)? {

                        None => error = Some(format!("{owner} has no repository {repo}.")),

                        Some(row) => {

                            let role = roles::role_for(&app.db, viewer.account(), &row).await.map_err(e)?;

                            let (issues, proposals) = db::issues::numbers(&app.db, row.id).await.map_err(e)?;

                            let kind = if issues.contains(&n) {

                                Some("issue")

                            } else if proposals.contains(&n) {

                                Some("proposal")

                            } else {

                                None

                            };

                            match (roles::allows(role, Access::Read), kind) {

                                (true, Some(kind)) => {

                                    let added = pdb::add_card(

                                        &app.db,

                                        project.id,

                                        pdb::NewCard {

                                            column_id,

                                            kind,

                                            repo_id: Some(row.id),

                                            number: Some(n),

                                            note: "",

                                            added_by: &actor,

                                        },

                                    )

                                    .await

                                    .map_err(e)?;

                                    if !added {

                                        error = Some(format!("{repo}#{n} is already on this project."));

                                    }

                                }

                                _ => error = Some(format!("{repo} has no issue or proposal #{n}.")),

                            }

                        }

                    },

                }

            }

        }

        "move" => {

            if let (Some(card), Some(col)) = (f.card, f.column) {

                pdb::move_card(&app.db, project.id, card, col).await.map_err(e)?;

            }

        }

        "up" | "down" => {

            if let Some(card) = f.card {

                pdb::shift_card(&app.db, project.id, card, if f.action == "up" { -1 } else { 1 }).await.map_err(e)?;

            }

        }

        "remove" => {

            if let Some(card) = f.card {

                pdb::remove_card(&app.db, project.id, card).await.map_err(e)?;

            }

        }

        "add_column" => {

            let name = f.name.trim();

            if name.is_empty() || name.len() > 40 {

                error = Some("A column has a name of up to 40 characters.".into());

            } else {

                pdb::add_column(&app.db, project.id, name).await.map_err(e)?;

            }

        }

        "rename_column" => {

            if let (Some(col), name) = (f.column, f.name.trim()) {

                if !name.is_empty() && name.len() <= 40 {

                    pdb::rename_column(&app.db, project.id, col, name).await.map_err(e)?;

                }

            }

        }

        "left" | "right" => {

            if let Some(col) = f.column {

                pdb::shift_column(&app.db, project.id, col, if f.action == "left" { -1 } else { 1 }).await.map_err(e)?;

            }

        }

        "remove_column" => {

            if let Some(col) = f.column {

                if !pdb::remove_column(&app.db, project.id, col).await.map_err(e)? {

                    error = Some("A project keeps at least one column.".into());

                }

            }

        }

        "settings" => {

            let title = if f.title.trim().is_empty() { project.title.clone() } else { f.title.trim().chars().take(100).collect() };

            pdb::set(&app.db, project.id, &title, f.description.trim(), f.private.is_none(), f.closed.is_some()).await.map_err(e)?;

            notice = Some("Saved.".into());

        }

        "delete" => {

            pdb::delete(&app.db, project.id).await.map_err(e)?;

            db::accounts::audit(&app.db, viewer.account(), "project.deleted", &format!("{owner}/{number}"), &project.title).await.ok();

            return Ok(Redirect::to(&format!("/projects/{owner}")).into_response());

        }

        _ => return Err(Error::bad(shell, "unknown action")),

    }

    if notice.is_none() && error.is_none() {

        // Back to the board, so a reload does not repeat the action.

        return Ok(Redirect::to(&format!("/projects/{owner}/{number}")).into_response());

    }

    Ok(board(&app, shell, &viewer, &owner, number, notice, error).await?.into_response())

}

/// The projects an issue or proposal is on that the viewer may see, with

/// their column.

pub async fn placements_for(app: &Shared, viewer: &Viewer, repo: &db::repos::RepoRow, number: i64) -> Vec<(pdb::Project, String)> {

    let Ok(all) = pdb::placements(&app.db, repo.id, number).await else { return vec![] };

    let mut out = Vec::new();

    for (p, col) in all {

        let visible = p.public

            || viewer.is_admin()

            || match viewer.account() {

                Some(v) if v.username == p.owner => true,

                Some(v) => match db::accounts::org_by_name(&app.db, &p.owner).await.ok().flatten() {

                    Some(o) => db::accounts::org_role(&app.db, o.id, v.id).await.ok().flatten().is_some(),

                    None => false,

                },

                None => false,

            };

        if visible {

            out.push((p, col));

        }

    }

    out

}

#[cfg(test)]

mod tests {

    use super::parse_reference;

    #[test]

    fn references() {

        assert_eq!(parse_reference("oxygen#12", ""), Some(("oxygen".into(), 12)));

        assert_eq!(parse_reference("#7", "hub"), Some(("hub".into(), 7)));

        assert_eq!(parse_reference(" hub # 3 ", ""), Some(("hub".into(), 3)));

        assert_eq!(parse_reference("#7", ""), None, "a repository is needed");

        assert_eq!(parse_reference("other/repo#1", ""), None, "only the owner's repositories");

        assert_eq!(parse_reference("hub#0", ""), None);

        assert_eq!(parse_reference("hub", ""), None);

    }

}

{% extends "base.html" %}

{% block title %}{{ project.title }} · Projects · {{ project.owner }}{% endblock %}

{% block mainclass %} class="wide"{% endblock %}

{% block content %}

<div class="head"><h1>{{ project.title }} <small><a href="/projects/{{ project.owner }}">{{ project.owner }}'s projects</a> · #{{ project.number }}{% if !project.public %} · private{% endif %}{% if project.closed %} · closed{% endif %}</small></h1>

{% if !project.description.is_empty() %}<p class="mute">{{ project.description }}</p>{% endif %}</div>

{% if let Some(n) = notice %}<p class="note">{{ n }}</p>{% endif %}

{% if let Some(e) = error %}<p class="error">{{ e }}</p>{% endif %}

<div class="board" role="list" aria-label="Columns">

{% for col in columns %}

<section class="board-col" role="listitem" aria-labelledby="col-{{ col.column.id }}">

  <header><h2 id="col-{{ col.column.id }}">{{ col.column.name }} <span class="count-chip">{{ col.cards.len() }}</span></h2>

  {% if can_edit %}<form class="tiny" method="post" action="/projects/{{ project.owner }}/{{ project.number }}"><input type="hidden" name="_csrf" value="{{ shell.csrf }}"><input type="hidden" name="column" value="{{ col.column.id }}"><button name="action" value="left" title="Move the column left" aria-label="Move {{ col.column.name }} left">←</button><button name="action" value="right" title="Move the column right" aria-label="Move {{ col.column.name }} right">→</button></form>{% endif %}</header>

  <ul class="cards">

  {% for c in col.cards %}

    <li class="card-item{% if c.card.kind == "note" %} note{% endif %}">

      {% if c.card.kind == "note" %}<p>{{ c.title }}</p>

      {% else %}<a href="{{ c.url }}">{{ c.title }}</a>

      <p class="small mute"><span class="glyph" aria-hidden="true">{% if c.card.kind == "issue" %}◇{% else %}⇄{% endif %}</span> {{ c.reference }} · <span class="state-{{ c.state }}">{{ c.state }}</span></p>{% endif %}

      {% if can_edit %}

      <form class="tiny card-actions" method="post" action="/projects/{{ project.owner }}/{{ project.number }}"><input type="hidden" name="_csrf" value="{{ shell.csrf }}"><input type="hidden" name="card" value="{{ c.card.id }}">

        <label class="visually-hidden" for="mv-{{ c.card.id }}">Column</label>

        <select id="mv-{{ c.card.id }}" name="column">{% for o in columns %}<option value="{{ o.column.id }}"{% if o.column.id == col.column.id %} selected{% endif %}>{{ o.column.name }}</option>{% endfor %}</select>

        <button name="action" value="move">move</button><button name="action" value="up" aria-label="Up">↑</button><button name="action" value="down" aria-label="Down">↓</button><button name="action" value="remove" aria-label="Remove from the project">✕</button>

      </form>{% endif %}

    </li>

  {% endfor %}

  </ul>

  {% if can_edit %}

  <details class="add-card"><summary class="small">add a card</summary>

    <form method="post" action="/projects/{{ project.owner }}/{{ project.number }}"><input type="hidden" name="_csrf" value="{{ shell.csrf }}"><input type="hidden" name="column" value="{{ col.column.id }}"><input type="hidden" name="action" value="add">

      <label for="ref-{{ col.column.id }}">Issue or proposal</label>

      <div class="inline-fields"><select name="repo" aria-label="Repository">{% for r in repos %}<option>{{ r }}</option>{% endfor %}</select><input type="text" id="ref-{{ col.column.id }}" name="reference" placeholder="#12 or repo#12"></div>

      <label for="note-{{ col.column.id }}">or a note</label><textarea id="note-{{ col.column.id }}" name="note" rows="2" maxlength="500"></textarea>

      <button type="submit" class="small">ADD</button>

    </form>

  </details>

  <details class="add-card"><summary class="small">column</summary>

    <form method="post" action="/projects/{{ project.owner }}/{{ project.number }}"><input type="hidden" name="_csrf" value="{{ shell.csrf }}"><input type="hidden" name="column" value="{{ col.column.id }}">

      <input type="text" name="name" value="{{ col.column.name }}" aria-label="Column name" maxlength="40"><button name="action" value="rename_column" class="small">RENAME</button> <button name="action" value="remove_column" class="small danger-btn" onclick="return confirm('Remove this column? Its cards move to the first column.')">REMOVE</button>

    </form>

  </details>

  {% endif %}

</section>

{% endfor %}

{% if can_edit %}

<section class="board-col add-col"><form method="post" action="/projects/{{ project.owner }}/{{ project.number }}"><input type="hidden" name="_csrf" value="{{ shell.csrf }}"><label for="newcol">New column</label><input type="text" id="newcol" name="name" maxlength="40" required><button name="action" value="add_column" class="small">ADD COLUMN</button></form></section>

{% endif %}

</div>

{% if can_manage %}

<details class="panel"><summary>Settings</summary>

<form class="card" method="post" action="/projects/{{ project.owner }}/{{ project.number }}"><input type="hidden" name="_csrf" value="{{ shell.csrf }}">

  <label for="ptitle">Title</label><input type="text" id="ptitle" name="title" value="{{ project.title }}" maxlength="100">

  <label for="pdesc">Description</label><input type="text" id="pdesc" name="description" value="{{ project.description }}">

  <div class="choice"><label><input type="checkbox" name="private" value="1"{% if !project.public %} checked{% endif %}> private</label> <label><input type="checkbox" name="closed" value="1"{% if project.closed %} checked{% endif %}> closed (read-only)</label></div>

  <button name="action" value="settings">SAVE</button> <button name="action" value="delete" class="danger-btn" onclick="return confirm('Delete this project? Issues and proposals are not touched.')">DELETE PROJECT</button>

</form></details>

{% endif %}

{% endblock %}

{% extends "base.html" %}

{% block title %}Projects · {{ owner }}{% endblock %}

{% block content %}

<div class="head"><h1>Projects <small><a href="/{{ owner }}">{{ owner }}</a></small></h1></div>

{% if let Some(e) = error %}<p class="error">{{ e }}</p>{% endif %}

{% if projects.is_empty() %}<p class="empty">No projects yet.{% if can_edit %} A project is a board of issues, proposals and notes from {{ owner }}'s repositories.{% endif %}</p>{% else %}

<ul class="rows">

{% for p in projects %}

  <li><div><a class="name" href="/projects/{{ owner }}/{{ p.number }}">{{ p.title }}</a>{% if !p.public %} <span class="tag">private</span>{% endif %}{% if p.closed %} <span class="tag dim">closed</span>{% endif %}

  <p class="small mute">#{{ p.number }}{% if !p.description.is_empty() %} · {{ p.description }}{% endif %} · updated <time title="{{ p.updated_ms|when_i }}">{{ p.updated_ms|ago_i }}</time></p></div></li>

{% endfor %}

</ul>

{% endif %}

{% if can_edit %}

<form class="card" method="post" action="/projects/{{ owner }}">

  <input type="hidden" name="_csrf" value="{{ shell.csrf }}">

  <h2>New project</h2>

  <label for="title">Title</label><input type="text" id="title" name="title" maxlength="100" required>

  <label for="description">Description</label><input type="text" id="description" name="description">

  <div class="choice"><label><input type="checkbox" name="private" value="1"> private (only {% if owner == shell.user.clone().unwrap_or_default() %}you{% else %}members of {{ owner }}{% endif %})</label></div>

  <button type="submit">CREATE</button>

  <p class="small mute">It starts with the columns Backlog, Doing and Done.</p>

</form>

{% endif %}

{% endblock %}

pub mod projects;

    "projects",

    /// Projects this issue is on that the viewer may see, with the column.

    projects: Vec<(crate::db::projects::Project, String)>,

    let projects = super::projects::placements_for(app, viewer, &row, i.number).await;

        projects,

mod projects;

        .route("/projects/{owner}", get(projects::list).post(projects::create))

        .route("/projects/{owner}/{number}", get(projects::show).post(projects::act))

    /// Projects this proposal is on that the viewer may see, with the column.

    projects: Vec<(db::projects::Project, String)>,

    let projects = super::projects::placements_for(app, viewer, &row, p.number).await;

        projects,

#[tokio::test(flavor = "multi_thread")]

async fn project_boards() {

    let h = hub().await;

    let app = &h.app;

    let andy = register(app, "andy").await;

    let bob = register(app, "bob").await;

    for (name, vis) in [("demo", hub_api::Visibility::Public), ("secret", hub_api::Visibility::Private)] {

        app.repos.create(&RepoName::parse(&format!("andy/{name}")).unwrap(), None).unwrap();

        let row = db::repos::create(&app.db, "andy", name, "", vis).await.unwrap();

        db::issues::create(

            &app.db,

            db::issues::NewIssue { repo_id: row.id, title: &format!("{name} issue"), body: "", author_id: None, author: "andy" },

        )

        .await

        .unwrap();

    }

    let csrf = csrf_of(&get(app, "/projects/andy", Some(&andy)).await.body);

    let r = post(app, "/projects/andy", Some(&andy), &format!("_csrf={csrf}&title=Roadmap&description=Next+steps")).await;

    assert_eq!(r.location.as_deref(), Some("/projects/andy/1"), "{}", r.body);

    let board = get(app, "/projects/andy/1", Some(&andy)).await.body;

    for col in ["Backlog", "Doing", "Done"] {

        assert!(board.contains(&format!(">{col} <span")), "{col}");

    }

    let cols: Vec<i64> = sqlx::query_scalar("SELECT id FROM project_columns ORDER BY position").fetch_all(&app.db).await.unwrap();

    let act = |form: String| {

        let andy = andy.clone();

        async move { post(app, "/projects/andy/1", Some(&andy), &form).await }

    };

    // Cards: by reference, by repository and number, a note; duplicates and strangers refused.

    assert_eq!(

        act(format!("_csrf={csrf}&action=add&column={}&reference=demo%231", cols[0])).await.location.as_deref(),

        Some("/projects/andy/1")

    );

    act(format!("_csrf={csrf}&action=add&column={}&repo=secret&reference=%231", cols[0])).await;

    act(format!("_csrf={csrf}&action=add&column={}&note=Write+the+docs", cols[1])).await;

    assert!(act(format!("_csrf={csrf}&action=add&column={}&reference=demo%231", cols[1])).await.body.contains("already on this project"));

    assert!(act(format!("_csrf={csrf}&action=add&column={}&reference=demo%2399", cols[0])).await.body.contains("no issue or proposal #99"));

    assert!(act(format!("_csrf={csrf}&action=add&column={}&reference=nothere%231", cols[0])).await.body.contains("no repository nothere"));

    // Move demo#1 to Done.

    let demo_card: i64 =

        sqlx::query_scalar("SELECT c.id FROM project_cards c JOIN repositories r ON r.id = c.repo_id WHERE r.name = 'demo'")

            .fetch_one(&app.db)

            .await

            .unwrap();

    act(format!("_csrf={csrf}&action=move&card={demo_card}&column={}", cols[2])).await;

    let issue = get(app, "/andy/demo/issue/1", None).await.body;

    assert!(issue.contains("href=\"/projects/andy/1\">Roadmap</a> <span class=\"mute\">· Done"), "the issue shows where it is");

    // Readers see public cards, never those of repositories they cannot read.

    let anon = get(app, "/projects/andy/1", None).await.body;

    assert!(anon.contains("demo issue") && anon.contains("Write the docs") && !anon.contains("secret issue"), "{anon}");

    assert!(!anon.contains("name=\"action\""), "and cannot change anything");

    assert!(get(app, "/projects/andy/1", Some(&andy)).await.body.contains("secret issue"));

    // Only the owner changes it; a private project is invisible to others.

    let csrf_b = csrf_of(&get(app, "/settings", Some(&bob)).await.body);

    assert_eq!(

        post(app, "/projects/andy/1", Some(&bob), &format!("_csrf={csrf_b}&action=remove&card={demo_card}")).await.status,

        StatusCode::NOT_FOUND

    );

    act(format!("_csrf={csrf}&action=settings&title=Roadmap&description=&private=1")).await;

    assert_eq!(get(app, "/projects/andy/1", Some(&bob)).await.status, StatusCode::NOT_FOUND);

    assert!(!get(app, "/projects/andy", None).await.body.contains("Roadmap"), "not even listed");

    assert!(!get(app, "/andy/demo/issue/1", None).await.body.contains("Roadmap"), "nor on the issue");

    // Columns: removing one moves its cards to the first column; the last one stays.

    act(format!("_csrf={csrf}&action=remove_column&column={}", cols[1])).await;

    let note_col: i64 = sqlx::query_scalar("SELECT column_id FROM project_cards WHERE kind = 'note'").fetch_one(&app.db).await.unwrap();

    assert_eq!(note_col, cols[0]);

    act(format!("_csrf={csrf}&action=remove_column&column={}", cols[2])).await;

    assert!(act(format!("_csrf={csrf}&action=remove_column&column={}", cols[0])).await.body.contains("at least one column"));

    // Closed projects are read-only until reopened; then deleted, the issues remain.

    act(format!("_csrf={csrf}&action=settings&title=Roadmap&closed=1")).await;

    assert!(act(format!("_csrf={csrf}&action=add&column={}&note=late", cols[0])).await.body.contains("This project is closed"));

    let r = act(format!("_csrf={csrf}&action=delete")).await;

    assert_eq!(r.location.as_deref(), Some("/projects/andy"));

    assert_eq!(get(app, "/projects/andy/1", Some(&andy)).await.status, StatusCode::NOT_FOUND);

    assert_eq!(get(app, "/andy/demo/issue/1", None).await.status, StatusCode::OK);

    assert_eq!(get(app, "/projects/nobody", None).await.status, StatusCode::NOT_FOUND);

}

| `/projects/{owner}`, `/projects/{owner}/{n}` | Projects of a person or organisation (§8.2) |

### 8.2 Projects

A **project** is a board that plans work across the repositories of one owner (a person or an organisation): a number per owner, a title, a description, public or private, open or closed, and ordered **columns** (new projects start with Backlog, Doing and Done). A column holds ordered **cards**; a card is an issue or proposal of one of the owner's repositories, or a note.

- Who: anyone may see a public project; a private one only its owner (for an organisation, its members) and site administrators. The owner, an organisation's members and administrators may change it. A card for an issue or proposal whose repository the viewer cannot read is not shown at all.

- The board works without JavaScript: each card has a column picker and up and down buttons; cards are added by reference (`#12` in a named repository, or `repo#12`) or as a note; an issue or proposal is on a project at most once. Issue and proposal pages list the projects they are on, with their column.

- Columns can be added, renamed, moved and removed (removing one moves its cards to the first column). Closing a project keeps it read-only.

  <h3>Projects</h3>

  {% if projects.is_empty() %}<p class="mute small">None. <a href="/projects/{{ repo.owner }}">{{ repo.owner }}'s projects</a></p>{% else %}<ul class="plain small">{% for (p, col) in projects %}<li><a href="/projects/{{ p.owner }}/{{ p.number }}">{{ p.title }}</a> <span class="mute">· {{ col }}</span></li>{% endfor %}</ul>{% endif %}

    <p><a class="go small" href="/projects/{{ org.name }}">Projects</a></p>

    <p><a class="go small" href="/projects/{{ account.username }}">Projects</a></p>

  <h3>Projects</h3>

  {% if projects.is_empty() %}<p class="mute small">None.</p>{% else %}<ul class="plain small">{% for (pr, col) in projects %}<li><a href="/projects/{{ pr.owner }}/{{ pr.number }}">{{ pr.title }}</a> <span class="mute">· {{ col }}</span></li>{% endfor %}</ul>{% endif %}

/* Projects (hub spec §8.2). */

.board { display: flex; gap: 12px; align-items: flex-start; overflow-x: auto; padding-bottom: 8px; }

.board-col { flex: 0 0 290px; background: var(--soft); border: 1px solid var(--line); padding: 8px; }

.board-col > header { display: flex; justify-content: space-between; align-items: center; gap: 6px; }

.board-col h2 { font-size: 14px; margin: 2px 0 8px; }

.board-col ul.cards { list-style: none; margin: 0; padding: 0; display: flex; flex-direction: column; gap: 8px; min-height: 12px; }

.card-item { background: var(--panel); border: 1px solid var(--line); padding: 8px 10px; }

.card-item.note { border-left: 3px solid var(--warn); }

.card-item p { margin: 4px 0 0; }

.card-item .card-actions { margin-top: 6px; display: flex; gap: 4px; flex-wrap: wrap; align-items: center; }

.card-item .card-actions select { width: auto; margin: 0; padding: 1px 4px; font-size: 12px; }

.board .add-card { margin-top: 8px; }

.board .add-card form { display: flex; flex-direction: column; gap: 4px; margin-top: 6px; }

.board .inline-fields { display: flex; gap: 4px; }

.board .inline-fields select { width: 40%; }

.board .add-col form { display: flex; flex-direction: column; gap: 6px; }

.state-open { color: var(--ok); } .state-closed, .state-landed { color: var(--acc); }

.visually-hidden { position: absolute; width: 1px; height: 1px; overflow: hidden; clip: rect(0 0 0 0); white-space: nowrap; }