Never leave an organisation without an owner

How to do it

Check inside the same transaction as the change, with the organisation row locked, so two owners cannot demote each other at the same moment. Tell the person what to do instead, such as making someone else owner first or deleting the organisation. Suggest at least two owners on the access page.

Example

export async function changeRole(db, organisationId, userId, newRole) {
  await db.tx(async tx => {
    await tx.none('SELECT 1 FROM organisations WHERE id = $1 FOR UPDATE', [organisationId]);
    const { owners } = await tx.one(
      "SELECT count(*)::int AS owners FROM memberships WHERE organisation_id = $1 AND role = 'owner' AND user_id <> $2",
      [organisationId, userId]);
    if (newRole !== 'owner' && owners === 0) throw new ConflictError('Make someone else owner first.');
    await tx.none('UPDATE memberships SET role = $1 WHERE organisation_id = $2 AND user_id = $3', [newRole, organisationId, userId]);
  });
}

def change_role(conn, organisation_id: str, user_id: str, new_role: str) -> None:
    with conn.transaction():
        conn.execute("SELECT 1 FROM organisations WHERE id = %s FOR UPDATE", (organisation_id,))
        (other_owners,) = conn.execute(
            "SELECT count(*) FROM memberships WHERE organisation_id = %s AND role = 'owner' AND user_id <> %s",
            (organisation_id, user_id)).fetchone()
        if new_role != "owner" and other_owners == 0:
            raise Conflict("Make someone else owner first.")
        conn.execute("UPDATE memberships SET role = %s WHERE organisation_id = %s AND user_id = %s",
                     (new_role, organisation_id, user_id))

How to check

In a test organisation with one owner, try to demote, remove and delete that owner. Each answers 409. With two owners, demote both in parallel. One succeeds and the other answers 409.